1 PACKAGE GME_MOBILE_LOVS AS
2 /* $Header: GMEMLOVS.pls 120.15.12000000.3 2007/02/22 17:24:54 svgonugu 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 );
321
322 PROCEDURE Lot_Lov(
323 x_lot_num_lov OUT NOCOPY t_genref
324 , p_lot_number IN VARCHAR2
325 , p_organization_id IN NUMBER
326 , p_item_id IN NUMBER
327 );
328
329 PROCEDURE Lot_LoV_Rsrv(
330 x_lot_num_lov OUT NOCOPY t_genref
331 , p_lot_number IN VARCHAR2
332 , p_organization_id IN NUMBER
333 , p_batch_id IN NUMBER
334 , p_material_detail_id IN NUMBER
335 , p_item_id IN NUMBER
336 , p_subinventory_code IN VARCHAR2
337 , p_locator_id IN NUMBER);
338
339 PROCEDURE Lot_LoV_Txn(
340 x_lot_num_lov OUT NOCOPY t_genref
341 , p_lot_number IN VARCHAR2
342 , p_organization_id IN NUMBER
343 , p_batch_id IN NUMBER
344 , p_material_detail_id IN NUMBER
345 , p_revision IN VARCHAR2
346 , p_line_type IN NUMBER
347 );
348
349 PROCEDURE UoM_LoV(
350 x_uom_lov OUT NOCOPY t_genref
351 , p_uom_code IN VARCHAR2
352 , p_organization_id IN NUMBER
353 , p_item_id IN NUMBER
354 );
355
356 PROCEDURE Reason_LoV
357 ( x_reason_cursor OUT NOCOPY t_genref
358 , p_reason_name IN VARCHAR2
359 );
363 , p_organization_id IN NUMBER
360
361 PROCEDURE Prod_Line_Type_LoV
362 ( x_line_type_cursor OUT NOCOPY t_genref
364 , p_batch_id IN NUMBER
365 , p_line_type IN VARCHAR2
366 , p_step_no IN VARCHAR2
367 );
368
369 PROCEDURE All_Line_Type_LoV
370 ( x_line_type_cursor OUT NOCOPY t_genref
371 , p_organization_id IN NUMBER
372 , p_batch_id IN NUMBER
373 , p_line_type IN VARCHAR2
374 , p_step_no IN VARCHAR2
375 );
376
377 PROCEDURE Material_Step_LoV
378 ( x_item_cursor OUT NOCOPY t_genref
379 , p_batch_id IN NUMBER
380 , p_line_no IN VARCHAR2
381 , p_item_no IN VARCHAR2
382 , p_step_no IN VARCHAR2
383 , p_line_type IN NUMBER
384 );
385
386 --nsinghi bug#5236906. Added procs Material_Lot_LoV and Step_Material_Lot_Line_LoV
387 PROCEDURE Material_Lot_LoV
388 ( x_item_cursor OUT NOCOPY t_genref
389 , p_batch_id IN NUMBER
390 , p_line_no IN VARCHAR2
391 , p_item_no IN VARCHAR2
392 , p_step_no IN VARCHAR2
393 , p_line_type IN NUMBER
394 );
395
396 PROCEDURE Step_Material_Lot_Line_LoV
397 ( x_line_cursor OUT NOCOPY t_genref
398 , p_batch_id IN NUMBER
399 , p_line_no IN VARCHAR2
400 , p_step_no IN VARCHAR2
401 , p_line_type IN NUMBER
402 );
403
404 PROCEDURE Step_Material_Line_LoV
405 ( x_line_cursor OUT NOCOPY t_genref
406 , p_batch_id IN NUMBER
407 , p_line_no IN VARCHAR2
408 , p_step_no IN VARCHAR2
409 , p_line_type IN NUMBER
410 );
411
412 PROCEDURE Step_For_Resource_LoV
413 ( x_step_cursor OUT NOCOPY t_genref
414 , p_batch_id IN NUMBER
415 , p_step_no IN VARCHAR2
416 );
417
418
419 PROCEDURE Subinventory_LoV_Rsrv(
420 x_sub_lov OUT NOCOPY t_genref
421 , p_organization_id IN NUMBER
422 , p_batch_id IN NUMBER
423 , p_material_detail_id IN NUMBER
424 , p_item_id IN NUMBER
425 , p_subinventory_code IN VARCHAR2
426 , p_locator_id IN NUMBER
427 , p_lot_number IN VARCHAR2
428 );
429
430 PROCEDURE Lot_LoV_Temp_Rsrv(
431 x_lot_num_lov OUT NOCOPY t_genref
432 , p_lot_number IN VARCHAR2
433 , p_organization_id IN NUMBER
434 , p_item_id IN NUMBER
435 , p_revision IN VARCHAR2
436 , p_subinv_code IN VARCHAR2
437 , p_locator_id IN NUMBER
438 );
439
440 PROCEDURE Lot_LoV_Dispense(
441 x_lot_num_lov OUT NOCOPY t_genref
442 , p_lot_number IN VARCHAR2
443 , p_subinv_code IN VARCHAR2
444 , p_locator_id IN NUMBER
445 );
446
447 PROCEDURE Subinventory_LoV_Dispense(x_sub_lov OUT NOCOPY t_genref,
448 p_organization_id IN NUMBER,
449 p_subinv_code IN VARCHAR);
450
451 PROCEDURE Locator_LoV_Dispense(
452 x_locator_lov OUT NOCOPY t_genref
453 , p_subinv_code IN VARCHAR2
454 , p_locator_code IN VARCHAR2
455 );
456
457 PROCEDURE Step_Material_Line_For_IB_LoV
458 ( x_line_cursor OUT NOCOPY t_genref
459 , p_batch_id IN NUMBER
460 , p_line_no IN VARCHAR2
461 , p_step_no IN VARCHAR2
462 , p_line_type IN NUMBER
463 );
464
465 PROCEDURE Material_Step_For_IB_LoV
466 ( x_item_cursor OUT NOCOPY t_genref
467 , p_batch_id IN NUMBER
468 , p_line_no IN VARCHAR2
469 , p_item_no IN VARCHAR2
470 , p_step_no IN VARCHAR2
471 , p_line_type IN NUMBER
472 );
473
474 PROCEDURE Revision_LoV
475 ( x_revision_cursor OUT NOCOPY t_genref
476 , p_org_id IN NUMBER
477 , p_inventory_item_id IN NUMBER
478 , p_revision IN VARCHAR2
479 );
480
481 PROCEDURE Resource_Instance_MsT_LoV
482 ( x_resource_cursor OUT NOCOPY t_genref
483 , p_organization_id IN NUMBER
484 , p_resource IN VARCHAR2
485 , p_instance IN VARCHAR2 );
486
487 PROCEDURE Resource_Instance_Txn_LoV
488 ( x_resource_cursor OUT NOCOPY t_genref
489 , p_batch_id IN NUMBER
490 , p_batchstep_resource_id IN NUMBER
491 , p_instance IN VARCHAR2);
492
493 PROCEDURE step_pending_lov
494 ( x_step_cursor OUT NOCOPY t_genref
495 , p_batch_id IN NUMBER
496 , p_step_no IN VARCHAR2
497 , p_date_format IN VARCHAR2);
498
499 PROCEDURE step_pending_wip_lov
500 ( x_step_cursor OUT NOCOPY t_genref
501 , p_batch_id IN NUMBER
502 , p_step_no IN VARCHAR2
503 , p_date_format IN VARCHAR2);
504
505 PROCEDURE Resource_Mst_LoV
506 ( x_resource_cursor OUT NOCOPY t_genref
507 , p_batch_id IN NUMBER
508 , p_step_no IN VARCHAR2
509 , p_activity_id IN NUMBER
510 , p_resource IN VARCHAR2
511 , p_date_format IN VARCHAR2);
512
513 PROCEDURE Resource_Txn_LoV
514 ( x_resource_cursor OUT NOCOPY t_genref
515 , p_batch_id IN NUMBER
516 , p_step_no IN VARCHAR2
517 , p_activity_id IN NUMBER
521 END GME_MOBILE_LOVS;
518 , p_resource IN VARCHAR2
519 , p_date_format IN VARCHAR2);
520