1 PACKAGE GME_MOBILE_LOVS AUTHID CURRENT_USER AS
2 /* $Header: GMEMLOVS.pls 120.19 2011/05/20 14:38:32 gmurator ship $ */
3 /*===========================================================================+
4 | Copyright (c) 2005 Oracle Corporation, Redwood Shores, CA, USA |
5 | All rights reserved. |
6 |===========================================================================|
7 | |
8 | PL/SQL Package to support the (Java) GME Mobile Application. |
9 | Contains PL/SQL cursors used to fetch data in the mobile LOVs. |
10 | |
11 +===========================================================================+
12 | HISTORY |
13 | |
14 | Date Who What |
15 | ==== === ==== |
16 | 26-Apr-05 Eddie Oumerretane First version |
17 | 09-Jun-06 Namit Singhi. Bug#5236906. Defined 2 new procs - |
18 | Step_Material_Lot_Line_LoV and Material_Lot_LoV. Show only|
19 | lot controlled items for Create/Pending Lots. |
20 | 21-Jun-06 Shrikant Nene Bug#5263908. Defined 1 new procs - |
21 | Revision_LoV. |
22 | 21-Aug-06 Shrikant Nene Bug#5263908. Defined 1 new procs - |
23 | Pending_Parent_Lot_LoV |
24 | 15-Sep-06 SivakumarG Bug#5261131 |
25 | Added procedure Lot_OnHand_Exp_LoV to filter based on |
26 | expiration date |
27 +===========================================================================*/
28
29 TYPE t_genref IS REF CURSOR;
30
31 /*
32 TYPE allocation_rec IS RECORD
33 ( trans_id NUMBER
34 , line_no NUMBER
35 , line_id NUMBER
36 , item_id NUMBER
37 , item_no VARCHAR2(32)
38 , trans_qty NUMBER
39 , trans_um VARCHAR2(4)
40 , trans_qty2 NUMBER
41 , trans_um2 NUMBER
42 , whse_code VARCHAR2(4)
43 , lot_id NUMBER
44 , lot_no VARCHAR2(32)
45 , sublot_no VARCHAR2(32)
46 , location VARCHAR2(32)
47 , reason_code VARCHAR2(4)
48 , completed_ind NUMBER(1)
49 , batch_id NUMBER
50 );
51
52 TYPE allocation_tab IS TABLE OF allocation_rec INDEX BY BINARY_INTEGER;
53
54 allocations allocation_tab;
55 */
56
57 PROCEDURE Batch_LoV
58 ( x_batch_cursor OUT NOCOPY t_genref
59 , p_org_id IN NUMBER
60 , p_statuses IN VARCHAR2
61 , p_batch_no IN VARCHAR2
62 );
63
64 PROCEDURE Ingredient_Line_Rsrv_LoV
65 ( x_line_cursor OUT NOCOPY t_genref
66 , p_batch_id IN NUMBER
67 , p_line_no IN VARCHAR2
68 , p_item_no IN VARCHAR2
69 );
70
71 PROCEDURE Step_Ingredient_Line_LoV
72 ( x_line_cursor OUT NOCOPY t_genref
73 , p_batch_id IN NUMBER
74 , p_line_no IN VARCHAR2
75 , p_step_no IN VARCHAR2
76 );
77
78 PROCEDURE Step_Ingredient_Lov
79 ( x_step_cursor OUT NOCOPY t_genref
80 , p_batch_id IN NUMBER
81 , p_step_no IN VARCHAR2
82 );
83
84 PROCEDURE Step_Prod_ByProd_LoV
85 ( x_step_cursor OUT NOCOPY t_genref
86 , p_batch_id IN NUMBER
87 , p_step_no IN VARCHAR2
88 );
89
90 PROCEDURE Step_Pend_Lot_LoV
91 ( x_step_cursor OUT NOCOPY t_genref
92 , p_batch_id IN NUMBER
93 , p_step_no IN VARCHAR2
94 );
95
96 PROCEDURE Step_All_Items_LoV
97 ( x_step_cursor OUT NOCOPY t_genref
98 , p_batch_id IN NUMBER
99 , p_step_no IN VARCHAR2
100 );
101
102 PROCEDURE Activity_LoV
103 ( x_activity_cursor OUT NOCOPY t_genref
104 , p_organization_id IN NUMBER
105 , p_batch_id IN NUMBER
106 , p_step_no IN VARCHAR2
107 , p_activity IN VARCHAR2
108 );
109
110 PROCEDURE Resource_LoV
111 ( x_resource_cursor OUT NOCOPY t_genref
112 , p_org_id IN NUMBER
113 , p_batch_id IN NUMBER
114 , p_step_no IN NUMBER
115 , p_activity_id IN NUMBER
116 , p_resource IN VARCHAR2
117 );
118
119 PROCEDURE Ingredient_LoV
120 ( x_item_cursor OUT NOCOPY t_genref
121 , p_batch_id IN NUMBER
122 , p_line_no IN VARCHAR2
123 , p_item_no IN VARCHAR2
124 );
125
126 PROCEDURE Ingredient_Rsrv_LoV
127 ( x_item_cursor OUT NOCOPY t_genref
128 , p_batch_id IN NUMBER
129 , p_line_no IN VARCHAR2
130 , p_item_no IN VARCHAR2
131 );
132
133 PROCEDURE Ingredient_Step_LoV
134 ( x_item_cursor OUT NOCOPY t_genref
135 , p_batch_id IN NUMBER
136 , p_line_no IN VARCHAR2
137 , p_item_no IN VARCHAR2
138 , p_step_no IN VARCHAR2
139 );
140
141 /* Bug#5663458 Begin
142 * Created the following procedure
143 */
144 PROCEDURE Rsrv_LoV(
145 x_batch_cursor OUT NOCOPY t_genref
146 ,p_org_id IN NUMBER
147 ,p_batch_id IN NUMBER
148 ,p_material_detail_id IN NUMBER
149 );
150
151 PROCEDURE Dispense_LoV(
152 x_batch_cursor OUT NOCOPY t_genref
153 );
154
155 PROCEDURE Subinventory_OnHand_LoV(
156 x_sub_lov OUT NOCOPY t_genref
157 ,p_organization_id IN NUMBER
158 ,p_subinventory_code IN VARCHAR2
159 ,p_inventory_item_id IN NUMBER
160 ,p_revision IN VARCHAR2
161 ,p_restrict_subinv IN NUMBER
162 );
163
164 PROCEDURE Locator_OnHand_LoV(
165 x_locators OUT NOCOPY t_genref
166 ,p_organization_id IN NUMBER
167 ,p_subinventory_code IN VARCHAR2
168 ,p_inventory_item_id IN NUMBER
169 ,p_locator IN VARCHAR2
170 ,p_revision IN VARCHAR2
171 ,p_restrict_locators IN NUMBER
172 );
173
174 PROCEDURE Ing_Locator_LoV_Rsrv(
175 x_locators OUT NOCOPY t_genref
176 ,p_organization_id IN NUMBER
177 ,p_batch_id IN NUMBER
178 ,p_material_detail_id IN NUMBER
179 ,p_item_id IN NUMBER
180 ,p_subinventory_code IN VARCHAR2
181 ,p_locator IN VARCHAR2
182 );
183
184 PROCEDURE PndLot_LoV(
185 x_lot_num_lov OUT NOCOPY t_genref
186 ,p_org_id IN NUMBER
187 ,p_batch_id IN NUMBER
188 ,p_material_detail_id IN NUMBER
189 );
190
191 PROCEDURE Ing_Subinventory_LoV_Rsrv(
192 x_sub_lov OUT NOCOPY t_genref
193 ,p_organization_id IN NUMBER
194 ,p_batch_id IN NUMBER
195 ,p_material_detail_id IN NUMBER
196 ,p_item_id IN NUMBER
197 ,p_subinventory_code IN VARCHAR2
198 );
199
200 PROCEDURE Revision_Txn_Lov
201 ( x_revision_cursor OUT NOCOPY t_genref
202 , p_org_id IN NUMBER
203 , p_batch_id IN NUMBER
204 , p_material_detail_id IN NUMBER
205 , p_revision IN VARCHAR2
206 , p_line_type IN NUMBER
207 );
208
209 PROCEDURE Revision_Rsrv_Lov
210 ( x_revision_cursor OUT NOCOPY t_genref
211 , p_org_id IN NUMBER
212 , p_batch_id IN NUMBER
213 , p_material_detail_id IN NUMBER
214 , p_revision IN VARCHAR2
215 );
216
217 PROCEDURE Revision_PndLot_Lov
218 ( x_revision_cursor OUT NOCOPY t_genref
219 , p_batch_id IN NUMBER
220 , p_material_detail_id IN NUMBER
221 , p_revision IN VARCHAR2
222 );
223
224 PROCEDURE Line_Step_LoV
225 ( x_item_cursor OUT NOCOPY t_genref
226 , p_batch_id IN NUMBER
227 , p_line_no IN VARCHAR2
228 , p_item_no IN VARCHAR2
229 , p_step_no IN VARCHAR2
230 , p_line_type IN NUMBER
231 );
232
233 PROCEDURE Pending_Rev_Lot_LoV(
234 x_lot_num_lov OUT NOCOPY t_genref
235 , p_lot_number IN VARCHAR2
236 , p_batch_id IN NUMBER
237 , p_material_detail_id IN NUMBER
238 , p_revision IN VARCHAR2
239 );
240 /* Bug#5663458 End */
241
242 PROCEDURE Subinventory_Lov_Temp_Rsrv(x_sub_lov OUT NOCOPY t_genref,
243 p_organization_id IN NUMBER,
244 p_item_id IN NUMBER,
245 p_revision IN VARCHAR2,
246 p_lot_number IN VARCHAR2,
247 p_subinv_code IN VARCHAR);
248
249 --Bug#5867209 added the p_restrict_subinv argument
250 PROCEDURE Subinventory_Lov(x_sub_lov OUT NOCOPY t_genref,
251 p_organization_id IN NUMBER,
252 p_subinv_code IN VARCHAR,
253 p_item_id IN NUMBER,
254 p_restrict_subinv IN NUMBER);
255
256
257 PROCEDURE Locator_LoV(
258 x_locators OUT NOCOPY t_genref
259 , p_organization_id IN NUMBER
260 , p_subinventory_code IN VARCHAR2
261 , p_inventory_item_id IN NUMBER
262 , p_concatenated_segments IN VARCHAR2
263 , p_restrict_locators IN NUMBER
264 );
265
266 PROCEDURE Locator_LoV_Rsrv(
267 x_locators OUT NOCOPY t_genref
268 , p_organization_id IN NUMBER
269 , p_batch_id IN NUMBER
270 , p_material_detail_id IN NUMBER
271 , p_item_id IN NUMBER
272 , p_subinventory_code IN VARCHAR2
273 , p_locator IN VARCHAR2
274 , p_lot_number IN VARCHAR2
275 );
276
277 PROCEDURE Locator_LoV_Temp_Rsrv(
278 x_locator_lov OUT NOCOPY t_genref
279 , p_lot_number IN VARCHAR2
280 , p_organization_id IN NUMBER
281 , p_item_id IN NUMBER
282 , p_revision IN VARCHAR2
283 , p_subinv_code IN VARCHAR2
284 , p_locator_code IN VARCHAR2
285 );
286
287 PROCEDURE Pending_Lot_LoV(
288 x_lot_num_lov OUT NOCOPY t_genref
289 , p_lot_number IN VARCHAR2
290 , p_batch_id IN NUMBER
291 , p_material_detail_id IN NUMBER
292 );
293
294 PROCEDURE Pending_Parent_Lot_LoV(
295 x_lot_num_lov OUT NOCOPY t_genref
296 , p_lot_number IN VARCHAR2
297 , p_batch_id IN NUMBER
298 , p_material_detail_id IN NUMBER
299 );
300
301 PROCEDURE Lot_Onhand_Lov(
302 x_lot_num_lov OUT NOCOPY t_genref
303 , p_lot_number IN VARCHAR2
304 , p_organization_id IN NUMBER
305 , p_item_id IN NUMBER
306 , p_subinventory_code IN VARCHAR2
307 , p_locator_id IN NUMBER
308 );
309
310 /*Bug#5261131 created the following procedure to filter out
311 expired lots */
312 PROCEDURE Lot_OnHand_Exp_LoV(
313 x_lot_num_lov OUT NOCOPY t_genref
314 , p_lot_number IN VARCHAR2
315 , p_organization_id IN NUMBER
316 , p_item_id IN NUMBER
317 , p_subinventory_code IN VARCHAR2
318 , p_locator_id IN NUMBER
319 , p_revision IN VARCHAR2 --Bug#5867209
320 , p_transaction_type IN NUMBER --Bug#8937132
321 );
322
323 PROCEDURE Lot_Lov(
324 x_lot_num_lov OUT NOCOPY t_genref
325 , p_lot_number IN VARCHAR2
326 , p_organization_id IN NUMBER
327 , p_item_id IN NUMBER
328 );
329
330 PROCEDURE Lot_LoV_Rsrv(
331 x_lot_num_lov OUT NOCOPY t_genref
332 , p_lot_number IN VARCHAR2
333 , p_organization_id IN NUMBER
334 , p_batch_id IN NUMBER
335 , p_material_detail_id IN NUMBER
336 , p_item_id IN NUMBER
337 , p_subinventory_code IN VARCHAR2
338 , p_locator_id IN NUMBER);
339
340 PROCEDURE Lot_LoV_Txn(
341 x_lot_num_lov OUT NOCOPY t_genref
342 , p_lot_number IN VARCHAR2
343 , p_organization_id IN NUMBER
344 , p_batch_id IN NUMBER
345 , p_material_detail_id IN NUMBER
346 , p_revision IN VARCHAR2
347 , p_line_type IN NUMBER
348 );
349
350 PROCEDURE UoM_LoV(
351 x_uom_lov OUT NOCOPY t_genref
352 , p_uom_code IN VARCHAR2
353 , p_organization_id IN NUMBER
354 , p_item_id IN NUMBER
355 );
356
357 PROCEDURE Reason_LoV
358 ( x_reason_cursor OUT NOCOPY t_genref
359 , p_reason_name IN VARCHAR2
360 );
361
362 PROCEDURE Prod_Line_Type_LoV
363 ( x_line_type_cursor OUT NOCOPY t_genref
364 , p_organization_id IN NUMBER
365 , p_batch_id IN NUMBER
366 , p_line_type IN VARCHAR2
367 , p_step_no IN VARCHAR2
368 );
369
370 PROCEDURE All_Line_Type_LoV
371 ( x_line_type_cursor OUT NOCOPY t_genref
372 , p_organization_id IN NUMBER
373 , p_batch_id IN NUMBER
374 , p_line_type IN VARCHAR2
375 , p_step_no IN VARCHAR2
376 );
377
378 PROCEDURE Material_Step_LoV
379 ( x_item_cursor OUT NOCOPY t_genref
380 , p_batch_id IN NUMBER
381 , p_line_no IN VARCHAR2
382 , p_item_no IN VARCHAR2
383 , p_step_no IN VARCHAR2
384 , p_line_type IN NUMBER
385 );
386
387 --nsinghi bug#5236906. Added procs Material_Lot_LoV and Step_Material_Lot_Line_LoV
388 PROCEDURE Material_Lot_LoV
389 ( x_item_cursor OUT NOCOPY t_genref
390 , p_batch_id IN NUMBER
391 , p_line_no IN VARCHAR2
392 , p_item_no IN VARCHAR2
393 , p_step_no IN VARCHAR2
394 , p_line_type IN NUMBER
395 );
396
397 PROCEDURE Step_Material_Lot_Line_LoV
398 ( x_line_cursor OUT NOCOPY t_genref
399 , p_batch_id IN NUMBER
400 , p_line_no IN VARCHAR2
401 , p_step_no IN VARCHAR2
402 , p_line_type IN NUMBER
403 );
404
405 PROCEDURE Step_Material_Line_LoV
406 ( x_line_cursor OUT NOCOPY t_genref
407 , p_batch_id IN NUMBER
408 , p_line_no IN VARCHAR2
409 , p_step_no IN VARCHAR2
410 , p_line_type IN NUMBER
411 );
412
413 PROCEDURE Step_For_Resource_LoV
414 ( x_step_cursor OUT NOCOPY t_genref
418
415 , p_batch_id IN NUMBER
416 , p_step_no IN VARCHAR2
417 );
419
420 PROCEDURE Subinventory_LoV_Rsrv(
421 x_sub_lov OUT NOCOPY t_genref
422 , p_organization_id IN NUMBER
423 , p_batch_id IN NUMBER
424 , p_material_detail_id IN NUMBER
425 , p_item_id IN NUMBER
426 , p_subinventory_code IN VARCHAR2
427 , p_locator_id IN NUMBER
428 , p_lot_number IN VARCHAR2
429 );
430
431 PROCEDURE Lot_LoV_Temp_Rsrv(
432 x_lot_num_lov OUT NOCOPY t_genref
433 , p_lot_number IN VARCHAR2
434 , p_organization_id IN NUMBER
435 , p_item_id IN NUMBER
436 , p_revision IN VARCHAR2
437 , p_subinv_code IN VARCHAR2
438 , p_locator_id IN NUMBER
439 );
440
441 PROCEDURE Lot_LoV_Dispense(
442 x_lot_num_lov OUT NOCOPY t_genref
443 , p_lot_number IN VARCHAR2
444 , p_subinv_code IN VARCHAR2
445 , p_locator_id IN NUMBER
446 );
447
448 PROCEDURE Subinventory_LoV_Dispense(x_sub_lov OUT NOCOPY t_genref,
449 p_organization_id IN NUMBER,
450 p_subinv_code IN VARCHAR);
451
452 PROCEDURE Locator_LoV_Dispense(
453 x_locator_lov OUT NOCOPY t_genref
454 , p_subinv_code IN VARCHAR2
455 , p_locator_code IN VARCHAR2
456 );
457
458 PROCEDURE Step_Material_Line_For_IB_LoV
459 ( x_line_cursor OUT NOCOPY t_genref
460 , p_batch_id IN NUMBER
461 , p_line_no IN VARCHAR2
462 , p_step_no IN VARCHAR2
463 , p_line_type IN NUMBER
464 );
465
466 PROCEDURE Material_Step_For_IB_LoV
467 ( x_item_cursor OUT NOCOPY t_genref
468 , p_batch_id IN NUMBER
469 , p_line_no IN VARCHAR2
470 , p_item_no IN VARCHAR2
471 , p_step_no IN VARCHAR2
472 , p_line_type IN NUMBER
473 );
474
475 PROCEDURE Revision_LoV
476 ( x_revision_cursor OUT NOCOPY t_genref
477 , p_org_id IN NUMBER
478 , p_inventory_item_id IN NUMBER
479 , p_revision IN VARCHAR2
480 );
481
482 PROCEDURE Resource_Instance_MsT_LoV
483 ( x_resource_cursor OUT NOCOPY t_genref
484 , p_organization_id IN NUMBER
485 , p_resource IN VARCHAR2
486 , p_instance IN VARCHAR2 );
487
488 PROCEDURE Resource_Instance_Txn_LoV
489 ( x_resource_cursor OUT NOCOPY t_genref
490 , p_batch_id IN NUMBER
491 , p_batchstep_resource_id IN NUMBER
492 , p_instance IN VARCHAR2);
493
494 PROCEDURE step_pending_lov
495 ( x_step_cursor OUT NOCOPY t_genref
496 , p_batch_id IN NUMBER
497 , p_step_no IN VARCHAR2
498 , p_date_format IN VARCHAR2);
499
500 PROCEDURE step_pending_wip_lov
501 ( x_step_cursor OUT NOCOPY t_genref
502 , p_batch_id IN NUMBER
503 , p_step_no IN VARCHAR2
504 , p_date_format IN VARCHAR2);
505
506 PROCEDURE Resource_Mst_LoV
507 ( x_resource_cursor OUT NOCOPY t_genref
508 , p_batch_id IN NUMBER
509 , p_step_no IN VARCHAR2
510 , p_activity_id IN NUMBER
511 , p_resource IN VARCHAR2
512 , p_date_format IN VARCHAR2);
513
514 PROCEDURE Resource_Txn_LoV
515 ( x_resource_cursor OUT NOCOPY t_genref
516 , p_batch_id IN NUMBER
517 , p_step_no IN VARCHAR2
518 , p_activity_id IN NUMBER
519 , p_resource IN VARCHAR2
520 , p_date_format IN VARCHAR2);
521
522 END GME_MOBILE_LOVS;
[Home] [Help]