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