1 PACKAGE inv_ui_item_sub_loc_lovs AS
2 /* $Header: INVITPSS.pls 120.10 2006/12/26 06:54:50 jrayarot noship $ */
3
4 TYPE t_genref IS REF CURSOR;
5
6 -- Name: GET_SUB_LOV_RCV
7 --
8 -- Input parameters:
9 -- p_Organization_Id which restricts LOV SQL to current org
10 -- p_sub which restricts LOV SQL to the user input text
11 -- e.g. FG%
12 -- p_item_id restrict to those item restricted sub
13 -- p_restrict_subinventories_code
14 --
15 -- Output parameters:
16 -- x_sub returns LOV rows as reference cursor
17 --
18 -- Functions: This API is to return subinventory for RECEIVING transaction only
19 -- It returns different LOV for item-restricted sub
20 --
21
22 PROCEDURE get_sub_lov_rcv(x_sub OUT NOCOPY t_genref
23 , p_organization_id IN NUMBER
24 , p_item_id IN NUMBER
25 , p_sub IN VARCHAR2
26 , p_restrict_subinventories_code IN NUMBER
27 , p_transaction_type_id IN NUMBER
28 , p_wms_installed IN VARCHAR2
29 , p_location_id IN NUMBER DEFAULT NULL
30 , p_lpn_context IN NUMBER DEFAULT NULL
31 , p_putaway_code IN NUMBER DEFAULT NULL
32 );
33
34 -- Name: GET_MO_FROMSUB_LOV
35 --
36 -- Input parameters:
37 -- p_organization_id OrgId
38 -- p_MOheader_id MoveOrder HeaderId
39 -- p_subinv_code SunInv Code
40 --
41 -- Output parameters:
42 -- x_fromsub_lov returns LOV rows as reference cursor
43 --
44 -- Functions: This API returns Transaction Reasons
45 --
46 PROCEDURE get_mo_fromsub_lov(x_fromsub_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_subinv_code IN VARCHAR);
47
48 -- Name: GET_MO_TOSUB_LOV
49 --
50 -- Input parameters:
51 -- p_organization_id OrgId
52 -- p_MOheader_id MoveOrder HeaderId
53 -- p_subinv_code SunInv Code
54 --
55 -- Output parameters:
56 -- x_tosub_lov returns LOV rows as reference cursor
57 --
58 -- Functions: This API returns Transaction Reasons
59 --
60 PROCEDURE get_mo_tosub_lov(x_tosub_lov OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_subinv_code IN VARCHAR);
61
62 -- Name: GET_LOC_LOV
63 --
64 -- Input parameters:
65 -- p_Organization_Id which restricts LOV SQL to current org
66 -- p_Concatenated_Segments which restricts LOV SQL to the user input text
67 -- e.g. 1-1%
68 -- p_Inventory_item_id restrict to those item restricted locators
69 -- p_Subinventory_Code restrict to this sub
70 -- p_restrict_Locators_code item restricted locator flag
71 --
72 -- Output parameters:
73 -- x_sub returns LOV rows as reference cursor
74 --
75 -- Functions: This API is to returns locator for given org and sub
76 -- It returns different LOVs for item-restricted locator
77 --
78 PROCEDURE get_loc_lov(
79 x_locators OUT NOCOPY t_genref
80 , p_organization_id IN NUMBER
81 , p_subinventory_code IN VARCHAR2
82 , p_restrict_locators_code IN NUMBER
83 , p_inventory_item_id IN NUMBER
84 , p_concatenated_segments IN VARCHAR2
85 , p_transaction_type_id IN NUMBER
86 , p_wms_installed IN VARCHAR2
87 );
88
89 -- Name: GET_LOC_LOV_PJM
90 --
91 -- Input parameters:
92 -- p_Organization_Id restrict LOV SQL to current org
93 -- p_Concatenated_Segments restrict LOV SQL to the user input text
94 -- e.g. 1-1%
95 -- p_Inventory_item_id restrict to those item restricted locators
96 -- p_Subinventory_Code restrict to this sub
97 -- p_restrict_Locators_code item restricted locator flag
98 --
99 -- Output parameters:
100 -- x_sub returns Physical LOV rows as reference cursor
101 -- The concatenated segments being returned in the cursor doesnt
102 -- contain SEGMENT 19 and 20.
103 --
104 -- Functions: This API is to return locator for given org and sub
105 -- without Segment 19 and 20.
106 -- It returns different LOVs for item-restricted locator
107 --
108 PROCEDURE get_loc_lov_pjm(
109 x_locators OUT NOCOPY t_genref
110 , p_organization_id IN NUMBER
111 , p_subinventory_code IN VARCHAR2
112 , p_restrict_locators_code IN NUMBER
113 , p_inventory_item_id IN NUMBER
114 , p_concatenated_segments IN VARCHAR2
115 , p_transaction_type_id IN NUMBER
116 , p_wms_installed IN VARCHAR2
117 );
118
119 -- This returns the locator id for an existing locator and if
120 -- it does not exist then it creates a new one.
121 PROCEDURE get_dynamic_locator(x_location_id OUT NOCOPY NUMBER, x_description OUT NOCOPY VARCHAR2, x_result OUT NOCOPY VARCHAR2, x_exist_or_create OUT NOCOPY VARCHAR2, p_org_id IN NUMBER, p_sub_code IN VARCHAR2, p_concat_segs IN VARCHAR2);
122
123 -- This validates a locator
124 PROCEDURE check_dynamic_locator(x_result OUT NOCOPY VARCHAR2, p_org_id IN NUMBER, p_sub_code IN VARCHAR2, p_inventory_location_id IN VARCHAR2);
125
126 --
127 --
128 ----------------------------------
129 -- Name: GET_INQ_LOC_LOV
130 -- To query locators of a sub and org without status check
131 -- filtered on project and task
132 -- Input Parameter:
133 -- p_organization_id: Organization ID
134 -- p_concatenated_segments LOV
135 -- p_Inventory_item_id Item ID
136 -- p_subinventory_code Sub
137 -- p_restrict_location_code locator restriction code
138 --
139 PROCEDURE get_inq_loc_lov(
140 x_locators OUT NOCOPY t_genref
141 , p_organization_id IN NUMBER
142 , p_subinventory_code IN VARCHAR2
143 , p_restrict_locators_code IN NUMBER
144 , p_inventory_item_id IN NUMBER
145 , p_concatenated_segments IN VARCHAR2
146 , p_project_id IN NUMBER := NULL
147 , p_task_id IN NUMBER := NULL
148 );
149 PROCEDURE get_inq_loc_lov(
150 x_locators OUT NOCOPY t_genref
151 , p_organization_id IN NUMBER
152 , p_subinventory_code IN VARCHAR2
153 , p_restrict_locators_code IN NUMBER
154 , p_inventory_item_id IN NUMBER
155 , p_concatenated_segments IN VARCHAR2
156 , p_project_id IN NUMBER := NULL
157 , p_task_id IN NUMBER := NULL
158 , p_alias IN VARCHAR2
159 );
160
161 -------------------------------------------------
162 PROCEDURE get_valid_to_locs(
163 x_locators OUT NOCOPY t_genref
164 , p_transaction_action_id IN NUMBER
165 , p_to_organization_id IN NUMBER
166 , p_organization_id IN NUMBER
167 , p_subinventory_code IN VARCHAR2
168 , p_restrict_locators_code IN NUMBER
169 , p_inventory_item_id IN NUMBER
170 , p_concatenated_segments IN VARCHAR2
171 , p_transaction_type_id IN NUMBER
172 , p_wms_installed IN VARCHAR2
173 );
174
175 -- Name: GET_MO_FROMLOC_LOV
176 --
177 -- Input parameters:
178 -- p_Organization_Id which restricts LOV SQL to current org
179 -- p_Concatenated_Segments which restricts LOV SQL to the user input text
180 -- e.g. 1-1%
181 --
182 -- Output parameters:
183 -- x_Locators returns LOV rows as reference cursor
184 --
185 -- Functions: This API is to return "src" locator for a given MO
186 --
187 PROCEDURE get_mo_fromloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_project_id IN NUMBER := NULL, p_task_id IN NUMBER := NULL);
188
189 PROCEDURE get_mo_fromloc_lov(
190 x_locators OUT NOCOPY t_genref,
191 p_organization_id IN NUMBER,
192 p_moheader_id IN NUMBER,
193 p_concatenated_segments IN VARCHAR2,
194 p_project_id IN NUMBER := NULL,
195 p_task_id IN NUMBER := NULL,
196 p_alias IN VARCHAR2
197 );
198 -- Name: GET_MO_TOLOC_LOV
199 --
200 -- Input parameters:
201 -- p_Organization_Id which restricts LOV SQL to current org
202 -- p_Concatenated_Segments which restricts LOV SQL to the user input text
203 -- e.g. 1-1%
204 --
205 -- Output parameters:
206 -- x_sub returns LOV rows as reference cursor
207 --
208 -- Functions: This API is to return "destination" locator for a given MO
209 --
210 PROCEDURE get_mo_toloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_moheader_id IN NUMBER, p_concatenated_segments IN VARCHAR2, p_project_id IN NUMBER := NULL, p_task_id IN NUMBER := NULL);
211
212 /**kkoothan*** Added two new default null parameters viz ***/
213 /***** Project_id and Task_id to the procedure. ***/
214 PROCEDURE get_loc_with_status(x_locators OUT NOCOPY t_genref,
215 p_organization_id IN NUMBER,
216 p_subinventory_code IN VARCHAR2,
217 p_concatenated_segments IN VARCHAR2,
218 p_project_id IN NUMBER DEFAULT NULL, -- PJM-WMS Integration
219 p_task_id IN NUMBER DEFAULT NULL); -- PJM-WMS Integration);
220 PROCEDURE get_loc_with_status(
221 x_locators OUT NOCOPY t_genref,
222 p_organization_id IN NUMBER,
223 p_subinventory_code IN VARCHAR2,
224 p_concatenated_segments IN VARCHAR2,
225 p_project_id IN NUMBER DEFAULT NULL, -- PJM-WMS Integration
226 p_task_id IN NUMBER DEFAULT NULL,-- PJM-WMS Integration);
227 p_alias IN VARCHAR2
228 );
229
230 PROCEDURE get_from_subs(
231 x_zones OUT NOCOPY t_genref
232 , p_organization_id IN NUMBER
233 , p_inventory_item_id IN NUMBER
234 , p_restrict_subinventories_code IN NUMBER
235 , p_secondary_inventory_name IN VARCHAR2
236 , p_transaction_action_id IN NUMBER
237 , p_transaction_type_id IN NUMBER
238 , p_wms_installed IN VARCHAR2
239 );
240
241 PROCEDURE get_to_sub(
242 x_to_sub OUT NOCOPY t_genref
243 , p_organization_id IN NUMBER
244 , p_inventory_item_id IN NUMBER
245 , p_from_secondary_name IN VARCHAR2
246 , p_restrict_subinventories_code IN NUMBER
247 , p_secondary_inventory_name IN VARCHAR2
248 , p_from_sub_asset_inventory IN VARCHAR2
249 , p_transaction_action_id IN NUMBER
250 , p_to_organization_id IN NUMBER
251 , p_serial_number_control_code IN NUMBER
252 , p_transaction_type_id IN NUMBER
253 , p_wms_installed IN VARCHAR2
254 );
255
256 PROCEDURE get_valid_subs(x_zones OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2);
257
258 PROCEDURE get_valid_subinvs(x_zones OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_txn_type_id IN NUMBER := 0, p_wms_installed IN VARCHAR2 := 'TRUE');
259
260 FUNCTION check_loc_existence(p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2)
261 RETURN NUMBER;
262
263 PROCEDURE get_sub_with_loc(x_zones OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2);
264
265 PROCEDURE get_sub_lov_ship(x_sub_lov OUT NOCOPY t_genref, p_txn_dock IN VARCHAR2, p_organization_id IN NUMBER, p_dock_appointment_id IN NUMBER, p_sub IN VARCHAR2);
266
267 PROCEDURE get_to_xsubs(x_to_xsubs OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2);
268
272 -- p_subinventory_code - restricts the subinventory to those like
269 -- Name: GET_PHYINV_SUBS
270 --
271 -- Input parameters:
273 -- the user inputted text if given
274 -- p_organization_id - restricts LOV SQL to current org
275 -- p_all_sub_flag - all subinventories flag which indicates
276 -- whether all the subs associated with the
277 -- org are used or only those that are defined
278 -- for that particular physical inventory
279 -- p_physical_inventory_id - The physical inventory for which we are
280 -- querying up the subs for
281 --
282 --
283 -- Output parameters:
284 -- x_phy_inv_sub_lov - Returns LOV rows as reference cursor
285 --
286 -- Functions: This API returns the valid subs associated with a
287 -- physical inventory
288 --
289 PROCEDURE get_phyinv_subs(x_phy_inv_sub_lov OUT NOCOPY t_genref, p_subinventory_code IN VARCHAR2, p_organization_id IN NUMBER, p_all_sub_flag IN NUMBER, p_physical_inventory_id IN NUMBER);
290
291 -- Name: GET_PHYINV_LOCS
292 --
293 -- Input parameters:
294 -- p_organization_id - restricts LOV SQL to current org
295 -- p_subinventory_code - restricts LOV to the current subinventory
296 -- p_concatenated_segments - restricts the locator to those that are
297 -- similar to the user inputted text.
298 -- locators are a key flex field so this
299 -- is how the user represents/identifies locators
300 -- p_dynamic_entry_flag - this flag determines whether or not
301 -- dynamic tag entries are allowed
302 -- p_physical_inventory_id - The physical inventory for which we are
303 -- querying up the locators for
304 --
305 -- p_project_id Restricts the locators for this project
306 --
307 -- p_task_id Restricts the locators for this task
308 --
309 -- Output parameters:
310 -- x_locators - Returns LOV rows as reference cursor
311 --
312 -- Functions: This API returns the valid locators associated with a
313 -- physical inventory
314 --
315 PROCEDURE get_phyinv_locs(
316 x_locators OUT NOCOPY t_genref
317 , p_organization_id IN NUMBER
318 , p_subinventory_code IN VARCHAR2
319 , p_concatenated_segments IN VARCHAR2
320 , p_dynamic_entry_flag IN NUMBER
321 , p_physical_inventory_id IN NUMBER
322 , p_project_id IN NUMBER := NULL
323 , p_task_id IN NUMBER := NULL
324 );
325
326 PROCEDURE get_phyinv_locs(
327 x_locators OUT NOCOPY t_genref
328 , p_organization_id IN NUMBER
329 , p_subinventory_code IN VARCHAR2
330 , p_concatenated_segments IN VARCHAR2
331 , p_dynamic_entry_flag IN NUMBER
332 , p_physical_inventory_id IN NUMBER
333 , p_project_id IN NUMBER := NULL
334 , p_task_id IN NUMBER := NULL
335 , p_alias IN VARCHAR2
336 );
337
338 -- Name: GET_CYC_SUBS
339 --
340 -- Input parameters:
341 -- p_subinventory_code - restricts the subinventory to those like
342 -- the user inputted text if given
343 -- p_organization_id - restricts LOV SQL to current org
344 -- p_orientation_code - orientation code which indicates
345 -- whether all the subs associated with the
346 -- org are used or only those that are defined
347 -- for that particular cycle count
348 -- p_cycle_count_header_id - The physical inventory for which we are
349 -- querying up the subs for
350 --
351 --
352 -- Output parameters:
353 -- x_cyc_sub_lov - Returns LOV rows as reference cursor
354 --
355 -- Functions: This API returns the valid subs associated with a
356 -- cycle count
357 --
358 PROCEDURE get_cyc_subs(x_cyc_sub_lov OUT NOCOPY t_genref, p_subinventory_code IN VARCHAR2, p_organization_id IN NUMBER, p_orientation_code IN NUMBER, p_cycle_count_header_id IN NUMBER);
359
360 -- Patchset I: WMS-PJM Integration
361 -- Name: GET_CYC_LOCS
362 --
363 -- Input parameters:
364 -- p_organization_id - restricts LOV SQL to current org
365 -- p_subinventory_code - restricts LOV to the current subinventory
366 -- p_concatenated_segments - restricts the locator to those that are
367 -- similar to the user inputted text.
368 -- locators are a key flex field so this
369 -- is how the user represents/identifies locators
370 -- p_unscheduled_entry - this flag determines whether or not
371 -- unscheduled count entries are allowed
372 -- p_cycle_count_header_id - The cycle count header for which we are
373 -- querying up the locators for.
374 -- p_project_id - restrict LOV SQL to this Project Id
378 -- Output parameters:
375 -- p_task_id - restrict LOV SQL to this Task Id
376 --
377 --
379 -- x_locators - Returns LOV rows as reference cursor
380 --
381 -- Functions: This API returns the valid locators associated with a
382 -- cycle count
383 --
384 PROCEDURE GET_CYC_LOCS(x_locators OUT NOCOPY t_genref ,
385 p_organization_id IN NUMBER ,
386 p_subinventory_code IN VARCHAR2 ,
387 p_concatenated_segments IN VARCHAR2 ,
388 p_unscheduled_entry IN NUMBER ,
389 p_cycle_count_header_id IN NUMBER ,
390 p_project_id IN NUMBER DEFAULT NULL ,
391 p_task_id IN NUMBER DEFAULT NULL);
392
393 PROCEDURE GET_CYC_LOCS(
394 x_locators OUT NOCOPY t_genref ,
395 p_organization_id IN NUMBER ,
396 p_subinventory_code IN VARCHAR2 ,
397 p_concatenated_segments IN VARCHAR2 ,
398 p_unscheduled_entry IN NUMBER ,
399 p_cycle_count_header_id IN NUMBER ,
400 p_project_id IN NUMBER DEFAULT NULL ,
401 p_task_id IN NUMBER DEFAULT NULL ,
402 p_alias IN VARCHAR2
403 );
404 -- Consignment and VMI Changes: Added Planning Org, TP Type, Owning Org and TP Type.
405 PROCEDURE get_valid_lpn_org_level(
406 x_lpns OUT NOCOPY t_genref
407 , p_organization_id IN NUMBER
408 , p_lpn_segments IN VARCHAR2
409 , p_planning_org_id IN NUMBER DEFAULT NULL
410 , p_planning_tp_type IN NUMBER DEFAULT NULL
411 , p_owning_org_id IN NUMBER DEFAULT NULL
412 , p_owning_tp_type IN NUMBER DEFAULT NULL
413 );
414
415 --Bug 5512205 Introduced a new overloaded procedure that validates the LPN status before populating the LPN LOV for sub xfer
416 PROCEDURE get_valid_lpn_org_level(
417 x_lpns OUT NOCOPY t_genref
418 , p_organization_id IN NUMBER
419 , p_lpn_segments IN VARCHAR2
420 , p_planning_org_id IN NUMBER
421 , p_planning_tp_type IN NUMBER
422 , p_owning_org_id IN NUMBER
423 , p_owning_tp_type IN NUMBER
424 , p_to_organization_id IN NUMBER
425 , p_transaction_type_id IN NUMBER
426 , p_wms_installed IN VARCHAR2
427 );
428 --End Bug 5512205
429
430 FUNCTION validate_lpn_for_toorg(p_lpn_id IN NUMBER
431 , p_to_organization_id IN NUMBER
432 , p_orgid IN NUMBER DEFAULT NULL
433 , p_transaction_type_id IN NUMBER DEFAULT NULL)
434 RETURN VARCHAR2;
435
436 PROCEDURE get_valid_lpn_tosubs(
437 x_to_sub OUT NOCOPY t_genref
438 , p_organization_id IN NUMBER
439 , p_lpn_id IN NUMBER
440 , p_from_secondary_name IN VARCHAR2
441 , p_from_sub_asset_inventory IN VARCHAR2
442 , p_transaction_action_id IN NUMBER
443 , p_to_organization_id IN NUMBER
444 , p_transaction_type_id IN NUMBER
445 , p_wms_installed IN VARCHAR2
446 , p_secondary_inventory_name IN VARCHAR2
447 );
448
449 FUNCTION vaildate_to_lpn_sub(p_lpn_id IN NUMBER, p_to_subinventory IN VARCHAR2, p_orgid IN NUMBER, p_from_sub_asset_inventory IN VARCHAR2, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
450 RETURN VARCHAR2;
451
452 FUNCTION vaildate_lpn_toloc(p_lpn_id IN NUMBER, p_to_subinventory IN VARCHAR2, p_orgid IN NUMBER, p_locator_id IN NUMBER, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
453 RETURN VARCHAR2;
454
455 PROCEDURE get_lpnloc_lov(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_lpn_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_transaction_type_id IN NUMBER, p_wms_installed IN VARCHAR2);
456
457 FUNCTION vaildate_lpn_status(p_lpn_id IN NUMBER, p_orgid IN NUMBER, p_to_org_id IN NUMBER, p_wms_installed IN VARCHAR2, p_transaction_type_id IN NUMBER)
458 RETURN VARCHAR2;
459
460 FUNCTION validate_sub_loc_status(p_lpn IN VARCHAR2, p_org_id IN NUMBER, p_sub IN VARCHAR2, p_loc_id IN NUMBER, p_not_lpn_id IN VARCHAR2 := NULL, p_parent_lpn_id IN VARCHAR2 := '0', p_txn_type_id IN NUMBER)
461 RETURN VARCHAR2;
462
463 -- Name: GET_CGUPDATE_SUBS
464 --
465 -- Input parameters:
466 -- p_subinventory_code - restricts the subinventory to those like
467 -- the user inputted text if given
468 -- p_organization_id - restricts LOV SQL to current org
469 -- p_inventory_item_id - restricts the subs to only those having
470 -- this item.
471 -- p_revision
472 --
473 -- Output parameters:
474 -- x_cgupdate_sub_lov - Returns LOV rows as reference cursor
475 --
476 -- Functions: This API returns the valid subs associated with
477 -- the Cost Group Update
478 --
479 PROCEDURE get_cgupdate_subs(x_cgupdate_sub_lov OUT NOCOPY t_genref, p_subinventory_code IN VARCHAR2, p_organization_id IN NUMBER, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2);
480
481 -- Name: GET_CGUPDATE_LOCS
482 --
483 -- Input parameters:
484 -- p_organization_id - restricts LOV SQL to current org
488 -- locators are a key flex field so this
485 -- p_subinventory_code - restricts LOV to the current subinventory
486 -- p_concatenated_segments - restricts the locator to those that are
487 -- similar to the user inputted text.
489 -- is how the user represents/identifies locators
490 -- p_inventory_item_id
491 -- p_revision
492
493 --
494 --
495 -- Output parameters:
496 -- x_locators - Returns LOV rows as reference cursor
497 --
498 -- Functions: This API returns the valid locators associated with a
499 -- cycle count
500 --
501 PROCEDURE get_cgupdate_locs(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2);
502
503 PROCEDURE get_cgupdate_locs(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2, p_inventory_item_id IN NUMBER, p_revision IN VARCHAR2, p_alias IN VARCHAR2);
504
505
506 PROCEDURE get_with_all_subs(x_zones OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2);
507
508 PROCEDURE get_with_all_loc(x_locators OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_concatenated_segments IN VARCHAR2);
509
510 /* Locator Alias Project - Added for bug # 5166308 */
511 PROCEDURE get_with_all_loc(x_locators OUT NOCOPY t_genref
512 , p_organization_id IN NUMBER
513 , p_subinventory_code IN VARCHAR2
514 , p_concatenated_segments IN VARCHAR2
515 , p_alias IN VARCHAR2);
516
517 PROCEDURE update_dynamic_locator(x_msg_count OUT NOCOPY NUMBER, x_msg_data OUT NOCOPY VARCHAR2, x_result OUT NOCOPY VARCHAR2, x_exist_or_create OUT NOCOPY VARCHAR2, p_locator_id IN NUMBER, p_org_id IN NUMBER, p_sub_code IN VARCHAR2);
518
519 --------------------------------------------------------------
520 -- Name : GET_VALID_LPN_CONTROLLED_SUBS
521 --Description: Procedure to fetch LPN Controlled subinventories
522 --------------------------------------------------------------
523 PROCEDURE get_valid_lpn_controlled_subs(x_zones OUT NOCOPY t_genref, p_organization_id IN NUMBER, p_subinventory_code IN VARCHAR2, p_txn_type_id IN NUMBER := 0, p_wms_installed IN VARCHAR2 := 'TRUE');
524
525 --------------------------------------------------------------
526 -- Name : GET_PRJ_FROM_LOC_LOV
527 --Description: Procedure to fetch valid from locators filtered
528 -- on sub, item, project and task
529 --------------------------------------------------------------
530 PROCEDURE get_prj_loc_lov(
531 x_locators OUT NOCOPY t_genref
532 , p_organization_id IN NUMBER
533 , p_subinventory_code IN VARCHAR2
534 , p_restrict_locators_code IN NUMBER
535 , p_inventory_item_id IN NUMBER
536 , p_concatenated_segments IN VARCHAR2
537 , p_transaction_type_id IN NUMBER
538 , p_wms_installed IN VARCHAR2
539 , p_project_id IN NUMBER
540 , p_task_id IN NUMBER
541 );
542
543 PROCEDURE get_prj_loc_lov(
544 x_locators OUT NOCOPY t_genref
545 , p_organization_id IN NUMBER
546 , p_subinventory_code IN VARCHAR2
547 , p_restrict_locators_code IN NUMBER
548 , p_inventory_item_id IN NUMBER
549 , p_concatenated_segments IN VARCHAR2
550 , p_transaction_type_id IN NUMBER
551 , p_wms_installed IN VARCHAR2
552 , p_project_id IN NUMBER
553 , p_task_id IN NUMBER
554 , p_alias IN VARCHAR2
555 );
556
557 PROCEDURE get_valid_prj_to_locs(
558 x_locators OUT NOCOPY t_genref
559 , p_transaction_action_id IN NUMBER
560 , p_to_organization_id IN NUMBER
561 , p_organization_id IN NUMBER
562 , p_subinventory_code IN VARCHAR2
563 , p_restrict_locators_code IN NUMBER
564 , p_inventory_item_id IN NUMBER
565 , p_concatenated_segments IN VARCHAR2
566 , p_transaction_type_id IN NUMBER
567 , p_wms_installed IN VARCHAR2
568 , p_project_id IN NUMBER
569 , p_task_id IN NUMBER
570 , p_alias IN VARCHAR2
571 );
572 PROCEDURE get_valid_prj_to_locs(
573 x_locators OUT NOCOPY t_genref
574 , p_transaction_action_id IN NUMBER
575 , p_to_organization_id IN NUMBER
576 , p_organization_id IN NUMBER
577 , p_subinventory_code IN VARCHAR2
578 , p_restrict_locators_code IN NUMBER
579 , p_inventory_item_id IN NUMBER
580 , p_concatenated_segments IN VARCHAR2
581 , p_transaction_type_id IN NUMBER
582 , p_wms_installed IN VARCHAR2
583 , p_project_id IN NUMBER
584 , p_task_id IN NUMBER
585 );
586
587 PROCEDURE get_prj_lpnloc_lov(
588 x_locators OUT NOCOPY t_genref
589 , p_organization_id IN NUMBER
590 , p_lpn_id IN NUMBER
591 , p_subinventory_code IN VARCHAR2
592 , p_concatenated_segments IN VARCHAR2
596 , p_task_id IN NUMBER
593 , p_transaction_type_id IN NUMBER
594 , p_wms_installed IN VARCHAR2
595 , p_project_id IN NUMBER
597 );
598 PROCEDURE get_prj_lpnloc_lov(
599 x_locators OUT NOCOPY t_genref
600 , p_organization_id IN NUMBER
601 , p_lpn_id IN NUMBER
602 , p_subinventory_code IN VARCHAR2
603 , p_concatenated_segments IN VARCHAR2
604 , p_transaction_type_id IN NUMBER
605 , p_wms_installed IN VARCHAR2
606 , p_project_id IN NUMBER
607 , p_task_id IN NUMBER
608 , p_alias IN VARCHAR2
609 );
610
611 ----------------------------------------------------------------------------------------
612 --Name:UPDATE_LOCATOR
613 --Description:Procedure to default the values of picking order,status and locator type
614 --from the org parameters whenever a dynamic locator is created.
615 -----------------------------------------------------------------------------------------
616
617 PROCEDURE update_locator(p_sub_code IN VARCHAR2, p_org_id IN NUMBER, p_locator_id IN NUMBER);
618
619
620 -- Patchset I: Sub LOV for User Directed LPN Putaway
621 -- Name: GET_USERPUT_SUBS
622 --
623 -- Input parameters:
624 -- p_organization_id - restricts LOV SQL to current org
625 -- p_subinventory_code - restricts the subinventory to those that are
626 -- similar to the user inputted text.
627 -- p_lpn_id - LPN ID for the LPN that is being put away
628 -- p_lpn_context - LPN Context for the putaway LPN
629 -- p_rcv_sub_only - Determines if only receiving subs
630 -- should be displayed in the LOV in the
631 -- case of an LPN that is in receiving.
632 -- 1 = Only RCV Subs
633 -- 2 = both RCV and INV Subs w/ no restrictions
634 -- 3 = Only INV Subs
635 -- 4 = Only INV Subs that are reservable AND lpn-controlled
636 -- 5 = Only INV Subs that are non-reservalbe AND non-lpn-controlled
637 -- 6 = Both RCV Subs and INV Subs that are reservable AND lpn_controlled
638 -- 7 = Both RCV Subs and INV Subs that are non-reservable AND non-lpn_controlled
639 -- Output parameters:
640 -- x_sub - Returns LOV rows as reference cursor
641 --
642 -- Functions: This API returns the valid subs associated with a
643 -- user directed LPN putaway. This will take care of
644 -- exploding the LPN and checking for item/sub
645 -- restrictions for all of the packed items. This will
646 -- also take care of checking the sub material status.
647 -- If the LPN is a receiving LPN, it will also show
648 -- receiving as well as inventory subs.
649 --
650 PROCEDURE get_userput_subs
651 (x_sub OUT NOCOPY t_genref ,
652 p_organization_id IN NUMBER ,
653 p_subinventory_code IN VARCHAR2 ,
654 p_lpn_id IN NUMBER ,
655 p_lpn_context IN NUMBER DEFAULT NULL,
656 p_rcv_sub_only IN NUMBER DEFAULT 2
657 );
658
659 -- Patchset I: Function used in get_userput_subs procedure
660 -- in the sub LOV for user directed putaway
661 -- Function Name: validate_lpn_sub
662 --
663 -- Input parameters:
664 -- p_organization_id - Organization for user putaway
665 -- p_subinventory_code - Subinventory being considered for user putaway
666 -- p_lpn_id - LPN ID for user putaway
667 --
668 -- Output value: 'Y' if validation passed
669 -- 'N' if validation failed
670 --
671 -- Functions: This function will validate the LPN for item/sub restrictions
672 -- and also for sub material status for each move order
673 -- line transaction. This function should only be
674 -- called from the procedure get_userput_subs in
675 -- this package: INV_UI_ITEM_SUB_LOC_LOVS
676 --
677 FUNCTION validate_lpn_sub(p_organization_id IN NUMBER ,
678 p_subinventory_code IN VARCHAR2 ,
679 p_lpn_id IN NUMBER)
680 RETURN VARCHAR2;
681
682 -- Patchset I: Loc LOV for User Directed LPN Putaway
683 -- Name: GET_USERPUT_LOCS
684 --
685 -- Input parameters:
686 -- p_organization_id - restricts LOV SQL to current org
687 -- p_subinventory_code - restricts LOV SQL to entered subinventory
688 -- p_concatenated_segments - retricts the locator to those that are
689 -- similar to the user inputted text.
690 -- p_project_id - restricts the locators for this project
691 -- p_task_id - restricts the locators for this task
692 -- p_lpn_id - LPN ID for the LPN that is being put away
693 --
694 -- Output parameters:
695 -- x_locators - Returns LOV rows as reference cursor
696 --
700 -- restrictions for all of the packed items. This will
697 -- Functions: This API returns the valid locators associated with a
698 -- user directed LPN putaway. This will take care of
699 -- exploding the LPN and checking for item/sub/loc
701 -- also take care of checking the locator material status
702 --
703 PROCEDURE get_userput_locs
704 (x_locators OUT NOCOPY t_genref ,
705 p_organization_id IN NUMBER ,
706 p_subinventory_code IN VARCHAR2 ,
707 p_concatenated_segments IN VARCHAR2 ,
708 p_project_id IN NUMBER ,
709 p_task_id IN NUMBER ,
710 p_lpn_id IN NUMBER
711 );
712
713 PROCEDURE get_userput_locs
714 (x_locators OUT NOCOPY t_genref ,
715 p_organization_id IN NUMBER ,
716 p_subinventory_code IN VARCHAR2 ,
717 p_concatenated_segments IN VARCHAR2 ,
718 p_project_id IN NUMBER ,
719 p_task_id IN NUMBER ,
720 p_lpn_id IN NUMBER ,
721 p_alias IN VARCHAR2
722 );
723
724 -- Patchset I: Function used in get_userput_locs procedure
725 -- in the locator LOV for user directed putaway
726 -- Function Name: validate_lpn_loc
727 --
728 -- Input parameters:
729 -- p_organization_id - Organization for user putaway
730 -- p_subinventory_code - Subinventory for user putaway
731 -- p_locator_id - Locator ID being considered for user putaway
732 -- p_lpn_id - LPN ID for user putaway
733 --
734 -- Output value: 'Y' if validation passed
735 -- 'N' if validation failed
736 --
737 -- Functions: This function will validate the LPN for item/sub/loc restrictions
738 -- and also for locator material status for each move order
739 -- line transaction. This function should only be
740 -- called from the procedure get_userput_locs in
741 -- this package: INV_UI_ITEM_SUB_LOC_LOVS
742 --
743 FUNCTION validate_lpn_loc(p_organization_id IN NUMBER ,
744 p_subinventory_code IN VARCHAR2 ,
745 p_locator_id IN NUMBER ,
746 p_lpn_id IN NUMBER)
747 RETURN VARCHAR2;
748
749 PROCEDURE get_pickload_loc_lov(
750 x_locators OUT NOCOPY t_genref
751 , p_organization_id IN NUMBER
752 , p_subinventory_code IN VARCHAR2
753 , p_restrict_locators_code IN NUMBER
754 , p_inventory_item_id IN NUMBER
755 , p_concatenated_segments IN VARCHAR2
756 , p_transaction_type_id IN NUMBER
757 , p_wms_installed IN VARCHAR2
758 , p_project_id IN NUMBER
759 , p_task_id IN NUMBER
760 , p_alias IN VARCHAR2
761 );
762 /* Added the following Following proceedure as a part of Bug 2769628
763 Used for Pick Load Page to list only those locators that are restricted by project and task.
764 */
765 PROCEDURE get_pickload_loc_lov(
766 x_locators OUT NOCOPY t_genref
767 , p_organization_id IN NUMBER
768 , p_subinventory_code IN VARCHAR2
769 , p_restrict_locators_code IN NUMBER
770 , p_inventory_item_id IN NUMBER
771 , p_concatenated_segments IN VARCHAR2
772 , p_transaction_type_id IN NUMBER
773 , p_wms_installed IN VARCHAR2
774 , p_project_id IN NUMBER
775 , p_task_id IN NUMBER);
776
777 /* Bug 4990550 : Added the following procedure, in order to handle the Locator text field bean of Pick Load page*/
778 PROCEDURE get_pickload_loc(
779 x_locators OUT NOCOPY t_genref
780 , p_organization_id IN NUMBER
781 , p_subinventory_code IN VARCHAR2
782 , p_restrict_locators_code IN NUMBER
783 , p_inventory_item_id IN NUMBER
784 , p_concatenated_segments IN VARCHAR2
785 , p_transaction_type_id IN NUMBER
786 , p_wms_installed IN VARCHAR2
787 , p_project_id IN NUMBER
788 , p_task_id IN NUMBER);
789
790
791 PROCEDURE validate_pickload_loc
792 (p_organization_id IN NUMBER,
793 p_subinventory_code IN VARCHAR2,
794 p_restrict_locators_code IN NUMBER,
795 p_inventory_item_id IN NUMBER,
796 p_locator IN VARCHAR2,
797 p_transaction_type_id IN NUMBER,
798 p_project_id IN NUMBER,
799 p_task_id IN NUMBER,
800 x_is_valid_locator OUT nocopy VARCHAR2,
801 x_locator_id OUT nocopy NUMBER);
802
803 /*Added the follwong procedure as a part of bug 2902336:*/
804 PROCEDURE get_inq_prj_loc_lov
805 (x_Locators OUT NOCOPY t_genref,
806 p_Organization_Id IN NUMBER,
807 p_Subinventory_Code IN VARCHAR2,
808 p_Restrict_Locators_Code IN NUMBER,
809 p_Inventory_Item_Id IN NUMBER,
810 p_Concatenated_Segments IN VARCHAR2,
814 PROCEDURE get_inq_prj_loc_lov
811 p_project_id IN NUMBER := NULL,
812 p_task_id IN NUMBER := NULL
813 );
815 (x_Locators OUT NOCOPY t_genref,
816 p_Organization_Id IN NUMBER,
817 p_Subinventory_Code IN VARCHAR2,
818 p_Restrict_Locators_Code IN NUMBER,
819 p_Inventory_Item_Id IN NUMBER,
820 p_Concatenated_Segments IN VARCHAR2,
821 p_project_id IN NUMBER := NULL,
822 p_task_id IN NUMBER := NULL,
823 p_alias IN VARCHAR2
824 );
825
826 PROCEDURE get_inq_prj_loc_lov_nvl
827 (x_Locators OUT NOCOPY t_genref,
828 p_Organization_Id IN NUMBER,
829 p_Subinventory_Code IN VARCHAR2,
830 p_Restrict_Locators_Code IN NUMBER,
831 p_Inventory_Item_Id IN NUMBER,
832 p_Concatenated_Segments IN VARCHAR2,
833 p_project_id IN NUMBER := NULL,
834 p_task_id IN NUMBER := NULL);
835
836 PROCEDURE get_inq_prj_loc_lov_nvl
837 (x_Locators OUT NOCOPY t_genref,
838 p_Organization_Id IN NUMBER,
839 p_Subinventory_Code IN VARCHAR2,
840 p_Restrict_Locators_Code IN NUMBER,
841 p_Inventory_Item_Id IN NUMBER,
842 p_Concatenated_Segments IN VARCHAR2,
843 p_project_id IN NUMBER := NULL,
844 p_task_id IN NUMBER := NULL,
845 p_alias IN VARCHAR2
846 );
847
848
849
850 /* Bug #3075665. ADDED IN PATCHSET J PROJECT ADVANCED PICKLOAD
851 * All the locators for the given org are selected, not restricting on the subinventory
852 */
853 -- Patchset J: Procedure used to get all the locs in the org
854 -- restricted by proj, task if passed and
855 -- NOT restricted by subinventory
856 -- Procedure Name: get_pickload_all_loc_lov
857 --
858 -- Input parameters:
859 -- p_organization_id - Organization Id
860 --
861 -- Output value:
862 -- x_locators Ref. cursor
863 --
864 PROCEDURE get_pickload_all_loc_lov
865 (
866 x_locators OUT NOCOPY t_genref
867 , p_organization_id IN NUMBER
868 , p_restrict_locators_code IN NUMBER
869 , p_inventory_item_id IN NUMBER
870 , p_concatenated_segments IN VARCHAR2
871 , p_transaction_type_id IN NUMBER
872 , p_wms_installed IN VARCHAR2
873 , p_project_id IN NUMBER
874 , p_task_id IN NUMBER
875 );
876
877 -- Bug #3075665. ADDED IN PATCHSET J PROJECT ADVANCED PICKLOAD
878 -- Patchset J: Procedure used to get the locs including project locs
879 -- Procedure Name: GET_APL_PRJ_LOC_LOV
880 --
881 -- Input parameters:
882 -- p_organization_id
883 -- p_subinventory_code
884 -- p_restrict_locators_code
885 -- p_inventory_item_id
886 -- p_concatenated_segments
887 -- p_transaction_type_id
888 -- p_wms_installed
889 -- p_project_id
890 -- p_task_id
891 -- Output value:
892 -- x_locators Ref. cursor
893 --
894 PROCEDURE GET_APL_PRJ_LOC_LOV(
895 x_locators OUT NOCOPY t_genref
896 , p_organization_id IN NUMBER
897 , p_subinventory_code IN VARCHAR2
898 , p_restrict_locators_code IN NUMBER
899 , p_inventory_item_id IN NUMBER
900 , p_concatenated_segments IN VARCHAR2
901 , p_transaction_type_id IN NUMBER
902 , p_wms_installed IN VARCHAR2
903 , p_project_id IN NUMBER
904 , p_task_id IN NUMBER);
905
906
907
908 -- Bug #3075665. ADDED IN PATCHSET J PROJECT ADVANCED PICKLOAD
909 -- Patchset J: Procedure used to get the locs including project locs
910 -- Procedure Name: get_pickload_loc_details
911 -- This procedure gets the locator details - concat segs, loc desc,
912 -- project, task, sub for a given org id, loc id.
913 -- The procedure also returns if the given locator exists or not.
914 -- Input parameters:
915 -- p_organization_id - Organization Id
916 -- p_inventory_location_id - Inventory Location Id
917 -- Output value:
918 -- x_subinventory_code - SubInventory Code
919 -- x_concatenated_segments - Locator concatenated segments
920 -- x_description - loc Description
921 -- x_project_id - Project Id
922 -- x_task_id - Task Id
923 -- x_loc_exists - boolean- Does the loc exists.
924 PROCEDURE get_pickload_loc_details(
925 p_organization_id IN NUMBER
926 , p_inventory_location_id IN NUMBER
927 , x_subinventory_code OUT NOCOPY VARCHAR2
928 , x_concatenated_segments OUT NOCOPY VARCHAR2
929 , x_description OUT NOCOPY VARCHAR2
930 , x_project_id OUT NOCOPY NUMBER
931 , x_task_id OUT NOCOPY NUMBER
932 , x_loc_exists OUT NOCOPY VARCHAR
933 , x_msg_count OUT NOCOPY NUMBER
934 , x_msg_data OUT NOCOPY VARCHAR2
935 , x_return_status OUT NOCOPY VARCHAR2 );
936
937 --
938 --
939 ----------------------------------
940 -- Name: GET_LOCATION_TYPE_LOCATORS
941 -- To query locators of a sub and org without status check
942 -- that is also filtered by mtl_item_locations.inventory_location_type
943 -- Input Parameter:
944 -- p_organization_id: Organization ID
945 -- p_subinventory_code Sub
946 -- p_inventory_location_type Location Type: Dock Door, Staging, Storage
947 -- p_concatenated_segments LOV
948 --
949 PROCEDURE Get_Location_Type_Locators(
950 x_locators OUT NOCOPY t_genref
951 , p_organization_id IN NUMBER
952 , p_subinventory_code IN VARCHAR2
953 , p_inventory_location_type IN NUMBER
954 , p_concatenated_segments IN VARCHAR2
955 );
956 PROCEDURE Get_Location_Type_Locators(
957 x_locators OUT NOCOPY t_genref
958 , p_organization_id IN NUMBER
959 , p_subinventory_code IN VARCHAR2
960 , p_inventory_location_type IN NUMBER
961 , p_concatenated_segments IN VARCHAR2
962 , p_alias IN VARCHAR2
963 );
964 PROCEDURE get_value_from_alias(
965 x_return_status OUT NOCOPY VARCHAR2
966 ,x_msg_data OUT NOCOPY VARCHAR2
967 ,x_msg_count OUT NOCOPY NUMBER
968 ,x_match OUT NOCOPY VARCHAR2
969 ,x_value OUT NOCOPY VARCHAR2
970 ,p_org_id IN NUMBER
971 ,p_sub_code IN VARCHAR2
972 ,p_alias IN VARCHAR2
973 ,p_suggested IN VARCHAR2
974 );
975
976 END inv_ui_item_sub_loc_lovs;
[Home] [Help]