1 PACKAGE GME_MOBILE_TXN AUTHID CURRENT_USER AS
2 /* $Header: GMEMOTXS.pls 120.17.12020000.2 2012/07/26 15:27:25 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 procedures used by mobile to transact material. |
10 | |
11 +===========================================================================+
12 | HISTORY |
13 | |
14 | Date Who What |
15 | ==== === ==== |
16 | 13-May-05 Eddie Oumerretane First version |
17 | 22-Aug-06 Shrikant Nene Bug 5456068 |
18 | Added parameter x_dispensed_count to Populate_Dispensing_Table |
19 | |
20 | 30-Mar-10 APMISHRA Bug 9367054. Added a new procedure |
21 | print_label to print labels for product |
22 | transactions |
23 | 15-Apr-10 APMISHRA Bug 9483781. Modified the procedure |
24 | Create_material_txn to process the LPN |
25 | details existing for a reservation |
26 +===========================================================================*/
27
28 /* Transaction types for GME defined in MaterialTransaction.java */
29 g_txn_source_type NUMBER := 5;
30 g_ing_issue NUMBER := 35;
31 g_ing_return NUMBER := 43;
32 g_prod_completion NUMBER := 44;
33 g_prod_return NUMBER := 17;
34 g_byprod_completion NUMBER := 1002;
35 g_byprod_return NUMBER := 1003;
36 l_reservations_tbl gme_common_pvt.reservations_tab;
37
38 HOUR_MIN_SEC_FORMAT_STRING CONSTANT VARCHAR2(11) := ' HH24:MI:SS';
39
40
41 TYPE t_genref IS REF CURSOR;
42
43 PROCEDURE Get_Stacked_Messages(x_message OUT NOCOPY VARCHAR2);
44
45 --
46 -- bug 9483781
47 -- Added a new parameter p_reservation_id to
48 -- fetch and process the LPN details against
49 -- it
50 --
51 PROCEDURE Create_Material_Txn(p_organization_id IN NUMBER,
52 p_batch_id IN NUMBER,
53 p_material_detail_id IN NUMBER,
54 p_item_id IN NUMBER,
55 p_revision IN VARCHAR2,
56 p_subinventory_code IN VARCHAR2,
57 p_locator_id IN NUMBER,
58 p_txn_qty IN NUMBER,
59 p_txn_uom_code IN VARCHAR2,
60 p_sec_txn_qty IN NUMBER,
61 p_sec_uom_code IN VARCHAR2,
62 p_primary_uom_code IN VARCHAR2,
63 p_txn_primary_qty IN NUMBER,
64 p_reason_id IN NUMBER,
65 p_txn_date IN DATE,
66 p_txn_type_id IN NUMBER,
67 p_phantom_type IN NUMBER,
68 p_user_id IN NUMBER,
69 p_login_id IN NUMBER,
70 p_dispense_id IN NUMBER,
71 p_phantom_line_id IN NUMBER,
72 x_txn_id OUT NOCOPY NUMBER,
73 x_txn_type_id OUT NOCOPY NUMBER,
74 x_txn_header_id OUT NOCOPY NUMBER,
75 x_return_status OUT NOCOPY VARCHAR2,
76 x_error_msg OUT NOCOPY VARCHAR2,
77 p_reservation_id IN NUMBER DEFAULT NULL);
78
79 -- Bug 6925025
80 -- Added parameters subinventory_code and locator_id
81 PROCEDURE Create_Lot_Txn(p_txn_id IN NUMBER,
82 p_txn_type_id IN NUMBER,
83 p_item_id IN NUMBER,
84 p_lot_number IN VARCHAR2,
85 p_txn_qty IN NUMBER,
86 p_txn_prim_qty IN NUMBER,
87 p_sec_txn_qty IN NUMBER,
88 p_user_id IN NUMBER,
89 p_login_id IN NUMBER,
90 p_subinventory_code IN VARCHAR2,
91 p_locator_id IN NUMBER,
92 x_return_status OUT NOCOPY VARCHAR2,
93 x_error_msg OUT NOCOPY VARCHAR2);
94
95 PROCEDURE Validate_Indiv_Lot_Txn(
96 p_organization_id IN NUMBER,
97 p_item_id IN NUMBER,
98 p_revision IN VARCHAR2,
99 p_subinventory_code IN VARCHAR2,
100 p_locator_id IN NUMBER,
101 p_lot_number IN VARCHAR2,
102 p_primary_lot_qty IN NUMBER,
103 p_transaction_type_id IN NUMBER,
104 x_return_status OUT NOCOPY VARCHAR2,
105 x_error_msg OUT NOCOPY VARCHAR2);
106
107 PROCEDURE Is_Material_Status_Applicable(
108 p_transaction_type_id IN NUMBER,
109 p_lot_status_enabled IN VARCHAR2,
110 p_organization_id IN NUMBER,
111 p_inventory_item_id IN NUMBER,
112 p_sub_code IN VARCHAR2,
113 p_locator_id IN NUMBER,
114 p_lot_number IN VARCHAR2,
115 p_object_type IN VARCHAR2,
116 x_return_status OUT NOCOPY VARCHAR2,
117 x_error_msg OUT NOCOPY VARCHAR2);
118
119 PROCEDURE Fetch_Material_Transactions(p_organization_id IN NUMBER,
120 p_batch_id IN NUMBER,
121 p_material_detail_id IN NUMBER,
122 p_txn_type_id IN NUMBER,
123 x_return_status OUT NOCOPY VARCHAR2,
124 x_error_msg OUT NOCOPY VARCHAR2,
125 x_txn_cursor OUT NOCOPY t_genref);
126
127 PROCEDURE Fetch_Lot_Transactions(
128 p_transaction_id IN NUMBER,
129 p_lot_number IN VARCHAR2,
130 x_return_status OUT NOCOPY VARCHAR2,
131 x_error_msg OUT NOCOPY VARCHAR2,
132 x_txn_cursor OUT NOCOPY t_genref);
133
134 PROCEDURE Process_Interface_Txn( p_txn_header_id IN NUMBER,
135 p_user_id IN NUMBER,
136 p_login_id IN NUMBER,
137 x_return_status OUT NOCOPY VARCHAR2,
138 x_error_msg OUT NOCOPY VARCHAR2);
139 PROCEDURE Validate_Child_Lot (
140 p_org_id IN NUMBER
141 , p_inventory_item_id IN NUMBER
142 , p_parent_lot_number IN VARCHAR2
143 , p_lot_number IN VARCHAR2
144 , x_return_status OUT NOCOPY VARCHAR2
145 , x_error_msg OUT NOCOPY VARCHAR2);
146
147 PROCEDURE Generate_Lot_Number(
148 p_org_id IN NUMBER
149 , p_inventory_item_id IN NUMBER
153 , x_error_msg OUT NOCOPY VARCHAR2);
150 , p_parent_lot_number IN VARCHAR2
151 , x_lot_number OUT NOCOPY VARCHAR2
152 , x_return_status OUT NOCOPY VARCHAR2
154
155
156
157 PROCEDURE Create_Lot(
158 p_org_id NUMBER,
159 p_inventory_item_id NUMBER,
160 p_lot_number IN VARCHAR2,
161 p_expiration_date IN DATE,
162 p_supplier_lot_number IN VARCHAR2,
163 p_grade_code IN VARCHAR2,
164 p_ORIGINATION_DATE IN DATE,
165 P_STATUS_ID IN NUMBER,
166 p_RETEST_DATE IN DATE,
167 P_MATURITY_DATE IN DATE,
168 P_LOT_ATTRIBUTE_CATEGORY IN VARCHAR2,
169 p_c_attribute1 IN VARCHAR2,
170 p_c_attribute2 IN VARCHAR2,
171 p_c_attribute3 IN VARCHAR2,
172 p_c_attribute4 IN VARCHAR2,
173 p_c_attribute5 IN VARCHAR2,
174 p_c_attribute6 IN VARCHAR2,
175 p_c_attribute7 IN VARCHAR2,
176 p_c_attribute8 IN VARCHAR2,
177 p_c_attribute9 IN VARCHAR2,
178 p_c_attribute10 IN VARCHAR2,
179 p_c_attribute11 IN VARCHAR2,
180 p_c_attribute12 IN VARCHAR2,
181 p_c_attribute13 IN VARCHAR2,
182 p_c_attribute14 IN VARCHAR2,
183 p_c_attribute15 IN VARCHAR2,
184 p_c_attribute16 IN VARCHAR2,
185 p_c_attribute17 IN VARCHAR2,
186 p_c_attribute18 IN VARCHAR2,
187 p_c_attribute19 IN VARCHAR2,
188 p_c_attribute20 IN VARCHAR2,
189 p_d_attribute1 IN DATE,
190 p_d_attribute2 IN DATE,
191 p_d_attribute3 IN DATE,
192 p_d_attribute4 IN DATE,
193 p_d_attribute5 IN DATE,
194 p_d_attribute6 IN DATE,
195 p_d_attribute7 IN DATE,
196 p_d_attribute8 IN DATE,
197 p_d_attribute9 IN DATE,
198 p_d_attribute10 IN DATE,
199 p_n_attribute1 IN NUMBER,
200 p_n_attribute2 IN NUMBER,
201 p_n_attribute3 IN NUMBER,
202 p_n_attribute4 IN NUMBER,
203 p_n_attribute5 IN NUMBER,
204 p_n_attribute6 IN NUMBER,
205 p_n_attribute7 IN NUMBER,
206 p_n_attribute8 IN NUMBER,
207 p_n_attribute9 IN NUMBER,
208 p_n_attribute10 IN NUMBER,
209 p_parent_lot_number IN VARCHAR2,
210 P_ORIGINATION_TYPE IN NUMBER,
211 P_EXPIRATION_ACTION_DATE IN DATE,
212 P_EXPIRATION_ACTION_CODE IN VARCHAR2,
213 P_HOLD_DATE IN DATE,
214 P_REASON_ID IN NUMBER,
215 P_ATTRIBUTE_CATEGORY IN VARCHAR2,
216 P_ATTRIBUTE1 IN VARCHAR2,
217 P_ATTRIBUTE2 IN VARCHAR2,
218 P_ATTRIBUTE3 IN VARCHAR2,
219 P_ATTRIBUTE4 IN VARCHAR2,
220 P_ATTRIBUTE5 IN VARCHAR2,
221 P_ATTRIBUTE6 IN VARCHAR2,
222 P_ATTRIBUTE7 IN VARCHAR2,
223 P_ATTRIBUTE8 IN VARCHAR2,
224 P_ATTRIBUTE9 IN VARCHAR2,
225 P_ATTRIBUTE10 IN VARCHAR2,
226 P_ATTRIBUTE11 IN VARCHAR2,
227 P_ATTRIBUTE12 IN VARCHAR2,
228 P_ATTRIBUTE13 IN VARCHAR2,
229 P_ATTRIBUTE14 IN VARCHAR2,
230 P_ATTRIBUTE15 IN VARCHAR2,
231 p_disable_flag IN NUMBER,
232 p_COLOR IN VARCHAR2,
233 p_VOLUME IN NUMBER,
234 p_VOLUME_UOM IN VARCHAR2,
235 p_PLACE_OF_ORIGIN IN VARCHAR2,
236 p_BEST_BY_DATE IN DATE,
237 p_LENGTH IN NUMBER,
238 p_LENGTH_UOM IN VARCHAR2,
239 p_RECYCLED_CONTENT IN NUMBER,
240 p_THICKNESS IN NUMBER,
241 p_THICKNESS_UOM IN VARCHAR2,
242 p_WIDTH IN NUMBER,
243 p_WIDTH_UOM IN VARCHAR2,
244 p_CURL_WRINKLE_FOLD IN VARCHAR2,
245 p_territory_code IN VARCHAR2,
246 p_date_code IN VARCHAR2,
247 p_change_date IN DATE,
248 p_age IN NUMBER,
249 p_item_size IN NUMBER,
250 p_vendor_name IN VARCHAR2,
251 x_return_status OUT NOCOPY VARCHAR2,
252 x_error_msg OUT NOCOPY VARCHAR2);
253
254 PROCEDURE Validate_Txn_Date(p_org_id IN NUMBER,
255 p_txn_date IN DATE,
256 x_period_id OUT NOCOPY NUMBER,
257 x_return_status OUT NOCOPY VARCHAR2,
258 x_error_msg OUT NOCOPY VARCHAR2);
259
260 PROCEDURE Fetch_Aggregated_Lot_Txns(p_org_id IN NUMBER,
261 p_batch_id IN NUMBER,
262 p_material_detail_id IN NUMBER,
263 p_lot_number IN VARCHAR2,
264 x_return_status OUT NOCOPY VARCHAR2,
265 x_error_msg OUT NOCOPY VARCHAR2,
266 x_txn_cursor OUT NOCOPY t_genref);
267
268 /* Bug 5451006: Added to used in return of the revision controlled plain items */
269 PROCEDURE Fetch_Aggregated_Rev_Txns(p_org_id IN NUMBER,
270 p_batch_id IN NUMBER,
271 p_material_detail_id IN NUMBER,
272 p_revision IN VARCHAR2,
273 x_aggr_txn_qty OUT NOCOPY NUMBER,
274 x_aggr_txn_sec_qty OUT NOCOPY NUMBER,
275 x_return_status OUT NOCOPY VARCHAR2,
276 x_error_msg OUT NOCOPY VARCHAR2);
277
278 PROCEDURE Fetch_Aggregated_Txns(p_org_id IN NUMBER,
279 p_batch_id IN NUMBER,
280 p_material_detail_id IN NUMBER,
281 x_aggr_txn_qty OUT NOCOPY NUMBER,
282 x_aggr_txn_sec_qty OUT NOCOPY NUMBER,
283 x_return_status OUT NOCOPY VARCHAR2,
284 x_error_msg OUT NOCOPY VARCHAR2);
285
286 PROCEDURE Validate_Batch_For_IB (p_organization_id IN NUMBER,
287 p_batch_id IN NUMBER,
288 x_return_status OUT NOCOPY VARCHAR2,
289 x_error_msg OUT NOCOPY VARCHAR2);
290
291 PROCEDURE Validate_Item_For_IB (p_organization_id IN NUMBER,
292 p_batch_id IN NUMBER,
293 p_material_detail_id IN NUMBER,
294 x_return_status OUT NOCOPY VARCHAR2,
295 x_error_msg OUT NOCOPY VARCHAR2);
296
297
298 PROCEDURE Backflush_Material (p_batch_id IN NUMBER,
299 p_material_detail_id IN NUMBER,
300 p_allow_neg_inv IN NUMBER,
301 p_qty IN NUMBER,
302 p_qty_type IN NUMBER,
303 p_trans_date IN DATE,
304 p_adjust_cmplt IN VARCHAR2,
305 p_login_id IN NUMBER,
306 p_user_id IN NUMBER,
307 p_org_code IN VARCHAR2,
308 x_return_status OUT NOCOPY VARCHAR2,
309 x_error_msg OUT NOCOPY VARCHAR2);
310
311 PROCEDURE Save_Resource_Usage( p_resource_id IN NUMBER
312 , p_usage IN VARCHAR2
313 , p_count IN VARCHAR2
314 , p_qty IN VARCHAR2
315 , p_uname IN VARCHAR2
316 , p_uid IN NUMBER
317 , x_result OUT NOCOPY NUMBER
318 , x_error_msg OUT NOCOPY VARCHAR2);
319
320 PROCEDURE Fetch_Product_Pending_Lots(p_batch_id IN NUMBER,
321 p_material_detail_id IN NUMBER,
322 p_lot_number IN VARCHAR2,
323 x_return_status OUT NOCOPY VARCHAR2,
324 x_error_msg OUT NOCOPY VARCHAR2,
325 x_lot_cursor OUT NOCOPY t_genref);
326
327 PROCEDURE Create_Product_Pending_Lot(p_batch_id IN NUMBER,
328 p_material_detail_id IN NUMBER,
329 p_lot_number IN VARCHAR2,
330 p_revision IN VARCHAR2,
331 p_sequence IN NUMBER,
332 p_qty IN NUMBER,
333 p_sec_qty IN NUMBER,
334 p_reason_id IN NUMBER,
335 p_user_id IN NUMBER,
336 p_login_id IN NUMBER,
337 p_org_code IN VARCHAR2,
338 p_create_lot IN VARCHAR2,
339 x_return_status OUT NOCOPY VARCHAR2,
340 x_error_msg OUT NOCOPY VARCHAR2);
341 PROCEDURE Update_Product_Pending_Lot(p_batch_id IN NUMBER,
342 p_material_detail_id IN NUMBER,
343 p_lot_id IN NUMBER,
344 p_lot_number IN VARCHAR2,
345 p_sequence IN NUMBER,
346 p_qty IN NUMBER,
347 p_sec_qty IN NUMBER,
348 p_reason_id IN NUMBER,
349 p_user_id IN NUMBER,
350 p_login_id IN NUMBER,
351 p_org_code IN VARCHAR2,
352 p_last_update_date IN VARCHAR2,
353 p_is_seq_changed IN VARCHAR2,
354 x_return_status OUT NOCOPY VARCHAR2,
355 x_error_msg OUT NOCOPY VARCHAR2);
356
357 PROCEDURE Populate_Dispensing_Table(
358 p_material_detail_id IN NUMBER,
359 x_dispensed_count OUT NOCOPY NUMBER,
360 x_return_status OUT NOCOPY VARCHAR2,
361 x_error_msg OUT NOCOPY VARCHAR2);
362
363 PROCEDURE Delete_Dispensing_Record(
364 p_dispense_id IN NUMBER,
365 x_return_status OUT NOCOPY VARCHAR2,
366 x_error_msg OUT NOCOPY VARCHAR2);
367
368 PROCEDURE Get_Dispensed_Lot_Count(
369 p_subinv_code IN VARCHAR2,
370 p_locator_id IN NUMBER,
371 x_lot_count OUT NOCOPY NUMBER,
372 x_return_status OUT NOCOPY VARCHAR2,
373 x_error_msg OUT NOCOPY VARCHAR2);
374
375 PROCEDURE Query_Qty_Tree (p_organization_id IN NUMBER,
376 p_inventory_item_id IN NUMBER,
377 p_revision IN VARCHAR2,
378 p_subinventory_code IN VARCHAR2,
379 p_locator_id IN NUMBER,
380 p_lot_number IN VARCHAR2,
381 p_revision_control IN VARCHAR2,
382 p_lot_control IN VARCHAR2,
383 p_tree_mode IN VARCHAR2,
384 x_qoh OUT NOCOPY NUMBER,
385 x_sqoh OUT NOCOPY NUMBER,
386 x_rqoh OUT NOCOPY NUMBER,
387 x_srqoh OUT NOCOPY NUMBER,
388 x_qr OUT NOCOPY NUMBER,
389 x_sqr OUT NOCOPY NUMBER,
390 x_qs OUT NOCOPY NUMBER,
391 x_sqs OUT NOCOPY NUMBER,
392 x_att OUT NOCOPY NUMBER,
393 x_satt OUT NOCOPY NUMBER,
394 x_atr OUT NOCOPY NUMBER,
395 x_satr OUT NOCOPY NUMBER,
396 x_return_status OUT NOCOPY VARCHAR2,
397 x_error_msg OUT NOCOPY VARCHAR2);
398
399 PROCEDURE Create_Qty_Tree (p_tree_mode IN NUMBER,
400 p_organization_id IN NUMBER,
401 --- p_batch_id IN NUMBER,
402 ---p_material_detail_id IN NUMBER,
403 p_inventory_item_id IN NUMBER,
404 p_revision IN VARCHAR2,
405 p_subinventory_code IN VARCHAR2,
406 p_locator_id IN NUMBER,
407 --- p_lot_number IN VARCHAR2,
408 p_revision_control IN VARCHAR2,
409 p_lot_control IN VARCHAR2,
410 x_tree_id OUT NOCOPY NUMBER,
411 x_return_status OUT NOCOPY VARCHAR2,
412 x_error_msg OUT NOCOPY VARCHAR2);
413
414
415 PROCEDURE Query_Qty_Tree (---p_organization_id IN NUMBER,
416 --- p_inventory_item_id IN NUMBER,
417 p_revision IN VARCHAR2,
418 p_subinventory_code IN VARCHAR2,
419 p_locator_id IN NUMBER,
423 ---p_tree_mode IN VARCHAR2,
420 p_lot_number IN VARCHAR2,
421 ---p_revision_control IN VARCHAR2,
422 ---p_lot_control IN VARCHAR2,
424 p_tree_id IN NUMBER,
425 x_qoh OUT NOCOPY NUMBER,
426 x_sqoh OUT NOCOPY NUMBER,
427 x_rqoh OUT NOCOPY NUMBER,
428 x_srqoh OUT NOCOPY NUMBER,
429 x_qr OUT NOCOPY NUMBER,
430 x_sqr OUT NOCOPY NUMBER,
431 x_qs OUT NOCOPY NUMBER,
432 x_sqs OUT NOCOPY NUMBER,
433 x_att OUT NOCOPY NUMBER,
434 x_satt OUT NOCOPY NUMBER,
435 x_atr OUT NOCOPY NUMBER,
436 x_satr OUT NOCOPY NUMBER,
437 x_return_status OUT NOCOPY VARCHAR2,
438 x_error_msg OUT NOCOPY VARCHAR2);
439
440 PROCEDURE Update_Qty_Tree ( p_tree_id IN NUMBER,
441 p_revision IN VARCHAR2,
442 p_subinventory_code IN VARCHAR2,
443 p_locator_id IN NUMBER,
444 p_lot_number IN VARCHAR2,
445 p_primary_qty IN NUMBER,
446 p_secondary_qty IN NUMBER,
447 p_quantity_type IN NUMBER,
448 x_return_status OUT NOCOPY VARCHAR2,
449 x_error_msg OUT NOCOPY VARCHAR2);
450
451 PROCEDURE Validate_Item_To_Issue(p_organization_id IN NUMBER,
452 p_batch_id IN NUMBER,
453 p_material_detail_id IN NUMBER,
454 p_item_id IN NUMBER,
455 x_return_status OUT NOCOPY VARCHAR2,
456 x_error_msg OUT NOCOPY VARCHAR2);
457
458 PROCEDURE Validate_Item_To_Return(p_organization_id IN NUMBER,
459 p_batch_id IN NUMBER,
460 p_material_detail_id IN NUMBER,
461 p_item_id IN NUMBER,
462 x_return_status OUT NOCOPY VARCHAR2,
463 x_error_msg OUT NOCOPY VARCHAR2);
464
465 PROCEDURE Validate_Prod_To_Yield (p_organization_id IN NUMBER,
466 p_batch_id IN NUMBER,
467 p_material_detail_id IN NUMBER,
468 p_item_id IN NUMBER,
469 x_return_status OUT NOCOPY VARCHAR2,
470 x_error_msg OUT NOCOPY VARCHAR2);
471
472 PROCEDURE Fetch_Issue_Transactions(
473 p_organization_id IN NUMBER,
474 p_batch_id IN NUMBER,
475 p_material_detail_id IN NUMBER,
476 p_lot_number IN VARCHAR2,
477 x_return_status OUT NOCOPY VARCHAR2,
478 x_error_msg OUT NOCOPY VARCHAR2,
479 x_txn_cursor OUT NOCOPY t_genref);
480
481 PROCEDURE Fetch_Yield_Transactions(
482 p_organization_id IN NUMBER,
483 p_batch_id IN NUMBER,
484 p_material_detail_id IN NUMBER,
485 p_lot_number IN VARCHAR2,
486 p_txn_type_id IN NUMBER,
487 x_return_status OUT NOCOPY VARCHAR2,
488 x_error_msg OUT NOCOPY VARCHAR2,
489 x_txn_cursor OUT NOCOPY t_genref);
490
491 PROCEDURE Create_Phantom_Txn (
492 p_mmti_trans_id IN NUMBER
493 ,x_return_status OUT NOCOPY VARCHAR2
494 ,x_error_msg OUT NOCOPY VARCHAR2);
495
496 PROCEDURE complete_step (p_batch_id IN NUMBER,
497 p_step_id IN NUMBER,
498 p_act_step_qty IN NUMBER,
499 p_act_strt_dt IN VARCHAR2,
500 p_act_complt_dt IN VARCHAR2,
501 p_date_format IN VARCHAR2,
502 p_uname IN VARCHAR2,
503 p_uid IN NUMBER,
504 x_return_status OUT NOCOPY VARCHAR2,
505 x_message_count OUT NOCOPY NUMBER,
506 x_message_list OUT NOCOPY VARCHAR2);
507
508 PROCEDURE End_Cmplt_Actual_Rsrc_Txn(
509 p_trans_id IN NUMBER
510 ,p_organization_id IN NUMBER
511 ,p_end_date IN DATE
512 ,p_reason_id IN NUMBER
513 ,p_instance_id IN NUMBER
514 ,p_trans_date IN DATE
515 ,p_uname IN VARCHAR2
516 ,p_uid IN NUMBER
517 ,x_trans_id OUT NOCOPY NUMBER
518 ,x_return_status OUT NOCOPY VARCHAR2
519 ,x_error_msg OUT NOCOPY VARCHAR2);
520
521 PROCEDURE get_ASQC ( p_batch_id IN NUMBER,
522 x_ASQC_status OUT NOCOPY NUMBER);
523 PROCEDURE get_system_date(p_date_format IN VARCHAR2,
524 x_sys_date OUT NOCOPY VARCHAR2);
525 PROCEDURE release_step ( p_batch_id IN NUMBER,
526 p_step_id IN NUMBER,
527 p_act_strt_dt IN VARCHAR2,
528 p_date_format IN VARCHAR2,
529 p_uname IN VARCHAR2,
530 p_uid IN NUMBER,
531 x_return_status OUT NOCOPY VARCHAR2,
532 x_message_count OUT NOCOPY NUMBER,
533 x_message_list OUT NOCOPY VARCHAR2);
534 PROCEDURE Start_Cmplt_Actual_Rsrc_Txn(
535 p_resource_id IN NUMBER
536 ,p_organization_id IN NUMBER
537 ,p_start_date IN DATE
538 ,p_instance_id IN NUMBER
539 ,p_trans_date IN DATE
540 ,p_reason_id IN NUMBER
541 ,p_uname IN VARCHAR2
542 ,p_uid IN NUMBER
543 ,x_trans_id OUT NOCOPY NUMBER
544 ,x_return_status OUT NOCOPY VARCHAR2
545 ,x_error_msg OUT NOCOPY VARCHAR2);
546
547 PROCEDURE Validate_Step_Completion_Date (p_start_date IN VARCHAR2,
548 p_complt_date IN VARCHAR2,
549 p_date_format IN VARCHAR2,
550 p_batch_id IN NUMBER,
551 x_return_status OUT NOCOPY VARCHAR2,
552 x_error_msg OUT NOCOPY VARCHAR2);
553 PROCEDURE Validate_Step_Qty ( p_step_qty IN VARCHAR2,
554 x_return_status OUT NOCOPY VARCHAR2,
555 x_error_msg OUT NOCOPY VARCHAR2);
556 PROCEDURE Validate_Step_Start_Date (p_start_date IN VARCHAR2,
557 p_date_format IN VARCHAR2,
558 p_batch_id IN NUMBER,
559 x_return_status OUT NOCOPY VARCHAR2,
560 x_error_msg OUT NOCOPY VARCHAR2);
561
562 PROCEDURE check_close_period(p_org_id IN NUMBER,
563 p_trans_date IN DATE,
564 x_return_status OUT NOCOPY VARCHAR2,
565 x_message OUT NOCOPY VARCHAR2);
566 PROCEDURE Load_resource_Txns (p_batch_id IN NUMBER,
567 x_row_count OUT NOCOPY NUMBER,
568 x_return_status OUT NOCOPY VARCHAR2);
569
570 /* Bug#5663458
571 * Created the following procedures
572 */
573 PROCEDURE relieve_resvns_pend_lots(p_rsrv_pndlot_id IN NUMBER,
574 p_relieve_qty IN NUMBER,
575 p_sec_qty IN NUMBER,
576 p_line_type IN NUMBER,
577 x_return_status OUT NOCOPY VARCHAR2,
578 x_error_msg OUT NOCOPY VARCHAR2);
579 /*
580 * Bug 13334961
581 * New procedure to relieve the reservations for dispense Items
582 *
583 */
584 PROCEDURE relieve_resvns_disp_items(p_rsrv_pndlot_id IN NUMBER,
585 p_relieve_qty IN NUMBER,
586 p_sec_qty IN NUMBER,
587 p_line_type IN NUMBER,
588 x_return_status OUT NOCOPY VARCHAR2,
589 x_error_msg OUT NOCOPY VARCHAR2);
590
591
592
593 PROCEDURE fetch_txn_date(p_material_detail_id IN NUMBER,
594 x_trans_date OUT NOCOPY VARCHAR2,
595 x_return_status OUT NOCOPY VARCHAR2,
596 x_error_msg OUT NOCOPY VARCHAR2);
597
598 PROCEDURE Validate_Subinv_Master(p_organization_id IN NUMBER,
599 p_subinventory_code IN VARCHAR2,
600 p_inventory_item_id IN NUMBER,
601 p_restrict_code IN NUMBER,
602 x_locator_type OUT NOCOPY VARCHAR2,
603 x_return_status OUT NOCOPY VARCHAR2,
604 x_error_message OUT NOCOPY VARCHAR2);
605
606 PROCEDURE Validate_Locator_Master(p_organization_id IN NUMBER,
607 p_subinventory_code IN VARCHAR2,
608 p_locator_code IN VARCHAR2,
609 p_inventory_item_id IN NUMBER,
610 p_restrict_code IN NUMBER,
611 x_locator_id OUT NOCOPY VARCHAR2,
612 x_return_status OUT NOCOPY VARCHAR2,
613 x_error_message OUT NOCOPY VARCHAR2);
614
615 PROCEDURE Fetch_Rev_Product_Pending_Lots(p_batch_id IN NUMBER,
616 p_material_detail_id IN NUMBER,
617 p_lot_number IN VARCHAR2,
618 p_rev_control IN NUMBER,
619 x_return_status OUT NOCOPY VARCHAR2,
620 x_error_msg OUT NOCOPY VARCHAR2,
621 x_lot_cursor OUT NOCOPY t_genref);
622 /* Bug#5663458 End */
623 --Bug#5867209 added restricted subinv code
624 PROCEDURE Fetch_subinv_locator(p_batch_id IN NUMBER,
625 p_material_detail_id IN NUMBER,
626 x_subinventory_code OUT NOCOPY VARCHAR2,
627 x_locator OUT NOCOPY VARCHAR2,
628 x_locator_id OUT NOCOPY VARCHAR2,
629 x_return_status OUT NOCOPY VARCHAR2,
630 x_error_msg OUT NOCOPY VARCHAR2);
631
632 -- nsinghi bug#5209065 START. Added following Procs.
633 PROCEDURE get_expiration_date
634 (
635 x_expiration_date OUT NOCOPY DATE
636 , x_return_status OUT NOCOPY VARCHAR2
637 ) ;
638
639 PROCEDURE get_exp_action_date
640 (
641 p_expiration_date IN DATE
642 , p_exp_act_interval IN NUMBER
643 , x_exp_act_date OUT NOCOPY DATE
644 , x_return_status OUT NOCOPY VARCHAR2
645 ) ;
646 -- nsinghi bug#5209065 END.
647
648 --
649 -- Bug 9367054
650 -- New procedure to print the labels for
651 -- product transactions
652 --
653 PROCEDURE print_label(p_txn_header_id IN NUMBER,
654 x_return_status OUT NOCOPY VARCHAR2,
655 x_error_msg OUT NOCOPY VARCHAR2);
656
657 END GME_MOBILE_TXN;
[Home] [Help]