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