[Home] [Help]
PACKAGE BODY: APPS.GME_LPN_MOBILE_TXN
Source
1 PACKAGE BODY GME_LPN_MOBILE_TXN AS
2 /* $Header: GMELMTXB.pls 120.5.12020000.2 2012/07/26 15:49:24 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. |
12 | HISTORY |
9 | Contains PL/SQL procedures used by mobile to transact material. |
10 | |
11 +===========================================================================+
13 | |
14 | Date Who What |
15 | ==== === ==== |
16 | 06-Oct-05 Namit Singhi First version |
17 | |
18 +===========================================================================*/
19
20
21 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
22
23 /*
24 PROCEDURE NAVIN_DEBUG (p_message VARCHAR2)
25 IS
26 i NUMBER ;
27 nxt_seq NUMBER;
28 PRAGMA AUTONOMOUS_TRANSACTION;
29 BEGIN
30
31 SELECT seq_dbg.NEXTVAL INTO nxt_seq FROM DUAL;
32 INSERT INTO nks_temp_table (STR, CREATE_DATE, SEQ) VALUES (p_message, sysdate, nxt_seq);
33 COMMIT;
34 RETURN;
35 END;
36
37 FUNCTION IS_MMTT_RECORD_PRESENT (p_lpn_id IN NUMBER,
38 txn_header_id OUT NUMBER,
39 txn_temp_id OUT NUMBER)
40 RETURN BOOLEAN
41 IS
42 BEGIN
43
44 SELECT mmtt.TRANSACTION_HEADER_ID, mmtt.TRANSACTION_TEMP_ID
45 INTO txn_header_id, txn_temp_id
46 FROM mtl_material_transactions_temp mmtt, mtl_txn_request_lines mtrl
47 WHERE move_order_line_id = mtrl.line_id
48 AND mtrl.lpn_id = p_lpn_id;
49 RETURN TRUE;
50
51 EXCEPTION
52 WHEN no_data_found THEN
53 -- NAVIN_DEBUG('IS_MMTT_RECORD_PRESENT : No MMTT record found');
54 RETURN FALSE;
55 WHEN too_many_rows THEN
56 -- NAVIN_DEBUG('IS_MMTT_RECORD_PRESENT : More than 1 row, need special handling ');
57 RETURN FALSE;
58 WHEN OTHERS THEN
59 -- NAVIN_DEBUG('IS_MMTT_RECORD_PRESENT : In others '||SQLERRM);
60 RETURN FALSE;
61 END;
62 */
63
64 /*+========================================================================+
65 | PROCEDURE NAME
66 | Lpn_LoV
67 |
68 | USAGE
69 |
70 | ARGUMENTS
71 | p_org_id - Organization Id
72 | p_lpn_no - License Plate Number
73 |
74 | RETURNS
75 | x_line_cursor - LPN Lov
76 |
77 | HISTORY
78 | Created 06-Oct-05 Nsinghi
79 |
80 +========================================================================+*/
81
82 PROCEDURE Lpn_LoV
83 ( x_line_cursor OUT NOCOPY t_genref
84 , p_org_id IN NUMBER
85 , p_lpn_no IN VARCHAR2
86 )
87 IS
88 BEGIN
89
90 OPEN x_line_cursor FOR
91 SELECT license_plate_number,
92 lpn_id
93 FROM wms_license_plate_numbers
94 WHERE organization_id = p_org_id
95 AND license_plate_number LIKE LTRIM(RTRIM('%'||p_lpn_no||'%'))
96 -- Bug 8603061: LPN Context should be 'Pre-generated', 'Resides in WIP' or 'Resides in Inventory'
97 AND lpn_context in (1, 2, 5)
98 ORDER BY lpad(license_plate_number, 30);
99
100 END Lpn_LoV;
101
102 /*+========================================================================+
103 | PROCEDURE NAME
104 | Get_Txn_Type
105 |
106 | USAGE
107 |
108 | ARGUMENTS
109 | p_transaction_type
110 |
111 | RETURNS
112 | transaction_type_id
113 |
114 | HISTORY
115 | Created 06-Oct-05 Nsinghi
116 |
117 +========================================================================+*/
118 FUNCTION Get_Txn_Type(p_transaction_type_id NUMBER) RETURN NUMBER IS
119 l_transaction_type_id NUMBER;
120 BEGIN
121
122 IF p_transaction_type_id = G_ING_ISSUE THEN
123 l_transaction_type_id := GME_COMMON_PVT.g_ing_issue;
124 ELSIF p_transaction_type_id = G_ING_RETURN THEN
125 l_transaction_type_id := GME_COMMON_PVT.g_ing_return;
126 ELSIF p_transaction_type_id = G_PROD_COMPLETION THEN
127 l_transaction_type_id := GME_COMMON_PVT.g_prod_completion;
128 ELSIF p_transaction_type_id = G_PROD_RETURN THEN
129 l_transaction_type_id := GME_COMMON_PVT.g_prod_return;
130 ELSIF p_transaction_type_id = G_BYPROD_COMPLETION THEN
131 l_transaction_type_id := GME_COMMON_PVT.g_byprod_completion;
132 ELSIF p_transaction_type_id = G_BYPROD_RETURN THEN
133 l_transaction_type_id := GME_COMMON_PVT.g_byprod_return;
134 END IF;
135
136 RETURN l_transaction_type_id;
137
138 END Get_Txn_Type;
139
140 /*+========================================================================+
141 | PROCEDURE NAME
142 | Create_Material_Txns
143 |
144 | USAGE
145 |
146 | ARGUMENTS
147 | p_organization_id
148 | p_batch_id
149 | p_material_detail_id
150 | p_item_id
151 | p_revision
152 | p_subinventory_code
153 | p_locator_id
154 | p_txn_qty
155 | p_txn_uom_code
156 | p_sec_txn_qty
157 | p_sec_uom_code
158 | p_primary_uom_code
159 | p_txn_primary_qty
160 | p_reason_id
161 | p_txn_date
162 | p_txn_type_id
163 | p_phantom_type
167 |
164 | p_user_id
165 | p_login_id
166 | p_dispense_id
168 | RETURNS
169 | x_message
170 |
171 | HISTORY
172 | Created 06-Oct-05 Nsinghi
173 |
174 +========================================================================+*/
175
176 PROCEDURE Create_Material_Txn(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_revision IN VARCHAR2,
181 p_subinventory_code IN VARCHAR2,
182 p_locator_id IN NUMBER,
183 p_txn_qty IN NUMBER,
184 p_txn_uom_code IN VARCHAR2,
185 p_sec_txn_qty IN NUMBER,
186 p_sec_uom_code IN VARCHAR2,
187 p_primary_uom_code IN VARCHAR2,
188 p_txn_primary_qty IN NUMBER,
189 p_reason_id IN NUMBER,
190 p_txn_date IN DATE,
191 p_txn_type_id IN NUMBER,
192 p_phantom_type IN NUMBER,
193 p_user_id IN NUMBER,
194 p_login_id IN NUMBER,
195 p_dispense_id IN NUMBER,
196 -- p_phantom_line_id IN NUMBER,
197 p_lpn_id IN NUMBER,
198 x_txn_id OUT NOCOPY NUMBER,
199 x_txn_type_id OUT NOCOPY NUMBER,
200 x_txn_header_id OUT NOCOPY NUMBER,
201 x_return_status OUT NOCOPY VARCHAR2,
202 x_error_msg OUT NOCOPY VARCHAR2)
203 IS
204 l_assign_phantom NUMBER;
205 l_mmti_rec_in mtl_transactions_interface%ROWTYPE;
206 l_mmti_rec_out mtl_transactions_interface%ROWTYPE;
207 BEGIN
208
209 -- Clearing the quantity cache
210 inv_quantity_tree_pub.clear_quantity_cache;
211
212 IF (g_debug IS NOT NULL) THEN
213 gme_debug.log_initialize ('MobileCreTxn');
214 END IF;
215
216 gme_common_pvt.g_user_ident := p_user_id;
217 gme_common_pvt.g_login_id := p_login_id;
218 gme_common_pvt.set_timestamp;
219
220 x_return_status := FND_API.G_RET_STS_SUCCESS;
221 x_error_msg := ' ';
222
223 l_mmti_rec_in.transaction_type_id := Get_Txn_Type(p_txn_type_id);
224
225 l_mmti_rec_in.transaction_source_id := p_batch_id;
226 l_mmti_rec_in.trx_source_line_id := p_material_detail_id;
227 l_mmti_rec_in.inventory_item_id := p_item_id;
228 l_mmti_rec_in.revision := p_revision;
229 l_mmti_rec_in.organization_id := p_organization_id;
230 l_mmti_rec_in.transaction_date := p_txn_date;
231 l_mmti_rec_in.transaction_quantity := p_txn_qty;
232 l_mmti_rec_in.primary_quantity := p_txn_primary_qty;
233 l_mmti_rec_in.reason_id := p_reason_id;
234 l_mmti_rec_in.secondary_transaction_quantity := p_sec_txn_qty;
235 l_mmti_rec_in.secondary_uom_code := p_sec_uom_code;
236 l_mmti_rec_in.transaction_uom := p_txn_uom_code;
237 l_mmti_rec_in.subinventory_code := p_subinventory_code;
238 l_mmti_rec_in.locator_id := p_locator_id;
239 l_mmti_rec_in.transaction_source_name := NULL;
240 l_mmti_rec_in.transaction_reference := p_dispense_id;
241 l_mmti_rec_in.transaction_action_id := NULL;
242 l_mmti_rec_in.transfer_lpn_id := p_lpn_id;
243
244
245 l_assign_phantom := 0;
246
247 /*
248 IF p_phantom_line_id IS NOT NULL THEN
249 -- This is a product of a phantom batch or a phantom ingredient
250 l_assign_phantom := 1;
251 END IF;
252 */
253 GME_TRANSACTIONS_PVT.Build_Txn_Inter_Hdr(
254 p_mmti_rec => l_mmti_rec_in,
255 -- p_assign_phantom => l_assign_phantom,
256 x_mmti_rec => l_mmti_rec_out,
257 x_return_status => x_return_status);
258
259
260 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
261 x_error_msg := fnd_message.get;
262 x_txn_id := -1;
263 x_txn_type_id := -1;
264 x_txn_header_id := -1;
265 ELSE
266 x_txn_id := l_mmti_rec_out.transaction_interface_id;
267 x_txn_type_id := l_mmti_rec_in.transaction_type_id;
268 x_txn_header_id := l_mmti_rec_out.transaction_header_id;
269 END IF;
270
271 EXCEPTION
272 WHEN OTHERS THEN
273 IF g_debug <= gme_debug.g_log_unexpected THEN
274 gme_debug.put_line('When others exception in Create MAterial Txn');
275 END IF;
276 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','create_material_txn');
277 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
278 x_error_msg := fnd_message.get;
279
280 END Create_Material_Txn;
281
282 /*+========================================================================+
286 | USAGE
283 | PROCEDURE NAME
284 | Update_MO_Line
285 |
287 |
288 | ARGUMENTS
289 | p_lpn_id - LPN id
290 | p_wms_process_flag - Process Flag to be updated to
291 |
292 | RETURNS
293 | x_return_status - S : If successful, E : If error
294 | x_msg_count - Message count
295 | x_msg_data - Message Data
296 |
297 | HISTORY
298 | Created 06-Oct-05 Nsinghi
299 |
300 +========================================================================+*/
301
302 PROCEDURE Update_MO_Line
303 (p_lpn_id IN NUMBER,
304 p_wms_process_flag IN NUMBER,
305 x_return_status OUT NOCOPY VARCHAR2)
306 IS
307 l_return_status VARCHAR2(1);
308
309 BEGIN
310 l_return_status:= FND_API.G_RET_STS_SUCCESS;
311
312 UPDATE mtl_txn_request_lines
313 SET wms_process_flag = p_wms_process_flag
314 WHERE lpn_id = p_lpn_id;
315
316 x_return_status:=l_return_status;
317
318 EXCEPTION
319
320 WHEN OTHERS THEN
321 x_return_status:=FND_API.G_RET_STS_ERROR;
322
323 END Update_MO_Line;
324
325 /*+========================================================================+
326 | PROCEDURE NAME
327 | Process_Interface_Txn
328 |
329 | USAGE
330 |
331 | ARGUMENTS
332 |
333 | RETURNS
334 |
335 | HISTORY
336 | Created 07-Oct-05 Namit Singhi
337 |
338 +========================================================================+*/
339 PROCEDURE Process_Interface_Txn( p_txn_header_id IN NUMBER,
340 p_user_id IN NUMBER,
341 p_login_id IN NUMBER,
342 x_return_status OUT NOCOPY VARCHAR2,
343 x_error_msg OUT NOCOPY VARCHAR2)
344 IS
345 l_msg_count NUMBER;
346 l_msg_data VARCHAR2 (2000);
347 l_trans_count NUMBER;
348
349 BEGIN
350
351 IF (g_debug IS NOT NULL) THEN
352 gme_debug.log_initialize ('MobileProcessTxn');
353 END IF;
354
355 gme_common_pvt.g_user_ident := p_user_id;
356 gme_common_pvt.g_login_id := p_login_id;
357 gme_common_pvt.set_timestamp;
358
359 GME_TRANSACTIONS_PVT.Process_Transactions
360 (p_api_version => 2.0,
361 p_init_msg_list => fnd_api.g_false,
362 p_commit => fnd_api.g_false,
363 p_validation_level => fnd_api.g_valid_level_full,
364 p_table => 1, -- Source table is Interface
365 p_header_id => p_txn_header_id,
366 x_return_status => x_return_status,
367 x_msg_count => l_msg_count,
368 x_msg_data => l_msg_data,
369 x_trans_count => l_trans_count);
370
371 IF (x_return_status <> fnd_api.g_ret_sts_success) THEN
372 --x_error_msg := fnd_message.get;
373 x_error_msg := l_msg_data;
374 END IF;
375
376 --- Reseting this global variable. I guess this should be done in
377 --- GME_TRANSACTIONS_PVT.Process_Transactions
378 GME_COMMON_PVT.g_transaction_header_id := NULL;
379
380 EXCEPTION
381 WHEN OTHERS THEN
382 IF g_debug <= gme_debug.g_log_unexpected THEN
383 gme_debug.put_line('When others exception in Process_Transactions');
384 END IF;
385 fnd_msg_pub.add_exc_msg('GME_MOBILE_TXN','process_transactions');
386 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
387 x_error_msg := fnd_message.get;
388
389 END Process_Interface_Txn;
390
391 /*+========================================================================+
392 | PROCEDURE NAME
393 | get_prod_count
394 |
395 | USAGE
396 |
397 | ARGUMENTS
398 |
399 | RETURNS
400 |
401 | HISTORY
402 | Created 07-Oct-05 Namit Singhi
403 |
404 +========================================================================+*/
405
406 PROCEDURE get_prod_count (p_batch_id IN NUMBER,
407 p_org_id IN NUMBER,
408 x_prod_count OUT NOCOPY NUMBER,
409 x_return_status OUT NOCOPY VARCHAR2)
410 IS
411 l_msg_count NUMBER;
412 l_msg_data VARCHAR2 (2000);
413 l_trans_count NUMBER;
414
415 BEGIN
416
417 SELECT COUNT(material_detail_id) INTO x_prod_count
418 FROM gme_material_details
419 WHERE batch_id = p_batch_id
420 AND organization_id = p_org_id
421 AND line_type IN (1, 2);
422
423 x_return_status := FND_API.G_RET_STS_SUCCESS;
424
425 EXCEPTION
426 WHEN OTHERS THEN
427 IF g_debug <= gme_debug.g_log_unexpected THEN
428 gme_debug.put_line('When others exception in get_prod_count ');
429 END IF;
430 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
431
432 END get_prod_count;
433
434 /*+========================================================================+
435 | PROCEDURE NAME
436 | get_subinv_loc
437 |
438 | USAGE
439 |
440 | ARGUMENTS
441 |
442 | RETURNS
443 |
444 | HISTORY
445 | Created 11-Nov-05 Namit Singhi
446 |
447 +========================================================================+*/
448
449 PROCEDURE get_subinv_loc(p_batch_id IN NUMBER
450 , p_org_id IN NUMBER
451 , p_material_dtl_id IN NUMBER
452 , x_subinventory OUT NOCOPY VARCHAR2
453 , x_locator OUT NOCOPY VARCHAR2
454 , x_locator_id OUT NOCOPY NUMBER
455 , x_return_status OUT NOCOPY VARCHAR2
456 , x_msg_data OUT NOCOPY VARCHAR2)
457 IS
458
459 CURSOR Cur_sub_loc IS
460 SELECT gbh.batch_no, msi.concatenated_segments, gmd.subinventory, mil.concatenated_segments, inventory_location_id
461 FROM gme_material_details gmd, mtl_item_locations_kfv mil, gme_batch_header gbh, mtl_system_items_kfv msi
462 WHERE gmd.organization_id = p_org_id
463 AND gmd.batch_id = p_batch_id
464 AND gmd.material_detail_id = p_material_dtl_id
465 AND gmd.locator_id = mil.inventory_location_id
466 AND gmd.organization_id = mil.organization_id
467 AND gmd.batch_id = gbh.batch_id
468 AND gmd.organization_id = gbh.organization_id
469 AND gmd.inventory_item_id = msi.inventory_item_id
470 AND gmd.organization_id = msi.organization_id;
471
472 l_batch_no VARCHAR2(32);
473 l_item VARCHAR2(240);
474 NO_DEF_SUB_LOC EXCEPTION;
475
476 BEGIN
477
478 IF (g_debug IS NOT NULL) THEN
479 gme_debug.log_initialize ('MobileGetSubLoc');
480 END IF;
481
482 x_return_status := FND_API.G_RET_STS_SUCCESS;
483 x_msg_data := ' ';
484
485 OPEN Cur_sub_loc;
486 FETCH Cur_sub_loc INTO l_batch_no, l_item, x_subinventory, x_locator, x_locator_id;
487 IF Cur_sub_loc%NOTFOUND THEN
488 CLOSE Cur_sub_loc;
489 RAISE NO_DEF_SUB_LOC;
490 END IF;
491 CLOSE Cur_sub_loc;
492
493 IF x_subinventory IS NULL OR x_locator_id IS NULL THEN
494 RAISE NO_DEF_SUB_LOC;
495 END IF;
496
497 EXCEPTION
498 WHEN NO_DEF_SUB_LOC THEN
499 IF g_debug <= gme_debug.g_log_unexpected THEN
500 gme_debug.put_line('When NO_DEF_SUB_LOC exception in get_subinv_loc ');
501 END IF;
502 FND_MESSAGE.SET_NAME('GME', 'GME_NO_DEF_SUB_LOC');
503 FND_MESSAGE.SET_TOKEN('BATCH_NO', l_batch_no);
504 FND_MESSAGE.SET_TOKEN('ITEM_NAME', l_item);
505 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
506 x_msg_data := FND_MESSAGE.GET;
507
508 WHEN OTHERS THEN
509 IF g_debug <= gme_debug.g_log_unexpected THEN
510 gme_debug.put_line('When others exception in get_subinv_loc ');
511 END IF;
512 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
513
514 END get_subinv_loc;
515
516 END gme_lpn_mobile_txn;