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