[Home] [Help]
PACKAGE BODY: APPS.GME_TRANSACTIONS_PVT
Source
1 PACKAGE BODY gme_transactions_pvt AS
2 /* $Header: GMEVPTXB.pls 120.47.12010000.1 2008/07/25 10:31:25 appldev ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_transactions_PVT';
5
6 /*
7 REM *********************************************************************
8 REM *
9 REM * FILE: GMEVPTXB.pls
10 REM * PURPOSE: Package Body for the GME batch transactions api
11 REM * AUTHOR: Pawan Kumar
12 REM * DATE: 2 May 2005
13 REM * HISTORY:
14 REM * ========
15 REM *
16 REM *
17 REM *
18 REM **********************************************************************
19 */
20
21 /*================================================================================
22
23
24 /* +==========================================================================+
25 | PROCEDURE NAME
26 | create_material_txn
27 |
28 | USAGE
29 | Inserts the transaction to interface table
30 |
31 | ARGUMENTS
32 | p_mmti_rec -- mtl_transaction_interface rowtype
33 | p_mmli_tbl -- table of mtl_trans_lots_inter_tbl as input
34 |
35 | RETURNS
36 | returns via x_status OUT parameters
37 |
38 | HISTORY
39 | Created 02-Feb-05 Pawan Kumar
40 |
41 +==========================================================================+ */
42 PROCEDURE create_material_txn (
43 p_mmti_rec IN mtl_transactions_interface%ROWTYPE
44 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
45 ,p_phantom_trans IN NUMBER DEFAULT 0
46 ,x_return_status OUT NOCOPY VARCHAR2)
47 IS
48 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MATERIAL_TXN';
49 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
50 l_mmti_rec mtl_transactions_interface%ROWTYPE;
51 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
52 m_mmti_rec mtl_transactions_interface%ROWTYPE;
53 m_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
54 x_mmti_rec mtl_transactions_interface%ROWTYPE;
55 x_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
56 l_mat_dtl_rec gme_material_details%ROWTYPE;
57 m_mat_dtl_rec gme_material_details%ROWTYPE;
58 l_ret NUMBER;
59 l_api_version NUMBER;
60 l_msg_count NUMBER;
61 l_msg_data VARCHAR2 (2000);
62 l_msg_index NUMBER (5);
63 l_txn_count NUMBER;
64 l_assign_phantom NUMBER;
65 l_cnt_int NUMBER;
66 l_cnt_temp NUMBER;
67 build_txn_inter_err EXCEPTION;
68 BEGIN
69 /* Initially let us assign the return status to success */
70 x_return_status := fnd_api.g_ret_sts_success;
71
72 IF (g_debug <= gme_debug.g_log_statement) THEN
73 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
74 || 'Entering');
75 END IF;
76
77 l_mmti_rec := p_mmti_rec;
78 l_mmli_tbl := p_mmli_tbl;
79 l_mat_dtl_rec.material_detail_id := l_mmti_rec.trx_source_line_id;
80
81 -- Now fetch the material detials for the mateiral
82 IF NOT gme_material_details_dbl.fetch_row
83 (p_material_detail => l_mat_dtl_rec
84 ,x_material_detail => l_mat_dtl_rec) THEN
85 RAISE fnd_api.g_exc_error;
86 END IF;
87
88 IF (g_debug <= gme_debug.g_log_statement) THEN
89 gme_debug.put_line ( g_pkg_name
90 || '.'
91 || l_api_name
92 || ':'
93 || 'material det_id'
94 || l_mat_dtl_rec.material_detail_id);
95 gme_debug.put_line ( g_pkg_name
96 || '.'
97 || l_api_name
98 || ':'
99 || 'batch_id'
100 || l_mat_dtl_rec.batch_id);
101 END IF;
102
103 IF p_phantom_trans <> 2 THEN
104 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL
105 AND (p_phantom_trans = 0) THEN
106 l_assign_phantom := 1;
107 END IF;
108
109 -- call for build procedure
110 build_txn_inter (p_mmti_rec => l_mmti_rec
111 ,p_mmli_tbl => l_mmli_tbl
112 ,p_assign_phantom => l_assign_phantom
113 ,x_mmti_rec => x_mmti_rec
114 ,x_mmli_tbl => x_mmli_tbl
115 ,x_return_status => l_return_status);
116 IF l_return_status <> fnd_api.g_ret_sts_success THEN
117 RAISE build_txn_inter_err;
118 END IF;
119 l_assign_phantom := 0;
120
121 IF (g_debug <= gme_debug.g_log_statement) THEN
122 gme_debug.put_line ( g_pkg_name
123 || '.'
124 || l_api_name
125 || ':'
126 || 'status after build : '
127 || l_return_status);
128 gme_debug.put_line ( g_pkg_name
129 || '.'
130 || l_api_name
131 || ':'
132 || 'x_mmti_rec.transaction_interface_id : '
133 || x_mmti_rec.transaction_interface_id);
134 gme_debug.put_line ( g_pkg_name
135 || '.'
136 || l_api_name
137 || ':'
138 || 'x_mmti_rec.transaction_reference : '
139 || x_mmti_rec.transaction_reference);
140 END IF;
141 END IF; --p_phantom_trans <> 2
142
143 -- For phantom
144 IF p_phantom_trans <> 1 THEN
145 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
146 m_mmti_rec := p_mmti_rec;
147 m_mmli_tbl := p_mmli_tbl;
148 m_mmti_rec.trx_source_line_id := l_mat_dtl_rec.phantom_line_id;
149 m_mat_dtl_rec.material_detail_id := m_mmti_rec.trx_source_line_id;
150
151 IF (g_debug <= gme_debug.g_log_statement) THEN
152 gme_debug.put_line ( g_pkg_name
153 || '.'
154 || l_api_name
155 || ':'
156 || 'for phantom line id:'
157 || l_mat_dtl_rec.phantom_line_id);
158 gme_debug.put_line ( g_pkg_name
159 || '.'
160 || l_api_name
161 || ':'
162 || ' original transaction_type_id :'
163 || l_mmti_rec.transaction_type_id);
164 END IF;
165
166 -- Now fetch the material detials for the phantom line
167 IF NOT gme_material_details_dbl.fetch_row
168 (p_material_detail => m_mat_dtl_rec
169 ,x_material_detail => m_mat_dtl_rec) THEN
170 RAISE fnd_api.g_exc_error;
171 END IF;
172
173 m_mmti_rec.transaction_source_id := m_mat_dtl_rec.batch_id;
174
175 IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
176 --(35)
177 m_mmti_rec.transaction_type_id :=
178 gme_common_pvt.g_prod_completion;
179 -- (44)
180 m_mmti_rec.transaction_action_id :=
181 gme_common_pvt.g_prod_comp_txn_action;
182 --(31)
183 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
184 --(43)
185 m_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
186 -- (17)
187 m_mmti_rec.transaction_action_id :=
188 gme_common_pvt.g_prod_ret_txn_action;
189 --(27)
190 ELSIF l_mmti_rec.transaction_type_id =
191 gme_common_pvt.g_prod_completion THEN
192 -- (44)
193 m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
194 -- (35)
195 m_mmti_rec.transaction_action_id :=
196 gme_common_pvt.g_ing_issue_txn_action;
197 --(1)
198 ELSIF l_mmti_rec.transaction_type_id =
199 gme_common_pvt.g_prod_return THEN
200 m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
201 -- (17)
202 m_mmti_rec.transaction_action_id :=
203 gme_common_pvt.g_ing_ret_txn_action;
204 --(27)
205 ELSIF l_mmti_rec.transaction_type_id =
206 gme_common_pvt.g_byprod_completion THEN
207 -- add for byprod type_id
208 m_mmti_rec.transaction_action_id :=
209 gme_common_pvt.g_byprod_ret_txn_action;
210 --(32)
211 ELSE
212 --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
213 -- add for byprod type_id
214 m_mmti_rec.transaction_action_id :=
215 gme_common_pvt.g_byprod_comp_txn_action;
216 --(31)
217 END IF;
218
219 IF (g_debug <= gme_debug.g_log_statement) THEN
220 gme_debug.put_line ( g_pkg_name
221 || '.'
222 || l_api_name
223 || ':'
224 || 'calling build for phantom');
225 gme_debug.put_line ( g_pkg_name
226 || '.'
227 || l_api_name
228 || ':'
229 || 'transaction_type_id for phantom :'
230 || m_mmti_rec.transaction_type_id);
231 gme_debug.put_line ( g_pkg_name
232 || '.'
233 || l_api_name
234 || ':'
235 || 'transaction_action_id for phantom :'
236 || m_mmti_rec.transaction_action_id);
237 gme_debug.put_line ( g_pkg_name
238 || '.'
239 || l_api_name
240 || ':'
241 || 'm_mat_dtl_rec.phantom_line_id :'
242 || m_mat_dtl_rec.phantom_line_id);
243 gme_debug.put_line ( g_pkg_name
244 || '.'
245 || l_api_name
246 || ':'
247 || 'x_mmti_rec.transaction_interface_id :'
248 || x_mmti_rec.transaction_interface_id);
249 gme_debug.put_line ( g_pkg_name
250 || '.'
251 || l_api_name
252 || ':'
253 || 'x_mmti_rec.transaction_reference :'
254 || x_mmti_rec.transaction_reference);
255 END IF;
256
257 IF (p_phantom_trans = 0) THEN
258 m_mmti_rec.transaction_reference :=
259 x_mmti_rec.transaction_interface_id;
260 l_assign_phantom := 0;
261 ELSE
262 l_assign_phantom := 0;
263 END IF;
264
265 -- calling build for phantom
266 build_txn_inter (p_mmti_rec => m_mmti_rec
267 ,p_mmli_tbl => m_mmli_tbl
268 ,p_assign_phantom => l_assign_phantom
269 ,x_mmti_rec => x_mmti_rec
270 ,x_mmli_tbl => x_mmli_tbl
271 ,x_return_status => l_return_status);
272
273 IF l_return_status <> fnd_api.g_ret_sts_success THEN
274 RAISE build_txn_inter_err;
275 END IF;
276
277 IF (g_debug <= gme_debug.g_log_statement) THEN
278 gme_debug.put_line ( g_pkg_name
279 || '.'
280 || l_api_name
281 || ':'
282 || 'status from build for phantom '
283 || l_return_status);
284 END IF;
285 END IF; -- for phantom
286 END IF; --p_phantom_trans <> 1 THEN
287
288 -- code for moving the data to temp
289 IF gme_common_pvt.g_move_to_temp = fnd_api.g_true THEN
290 IF (g_debug <= gme_debug.g_log_statement) THEN
291 gme_debug.put_line ( g_pkg_name
292 || '.'
293 || l_api_name
294 || ':'
295 || 'calling validate transactions:'
296 || gme_common_pvt.g_transaction_header_id);
297
298
299 select count(*)
300 into l_cnt_int
301 from mtl_transactions_interface
302 where transaction_header_id= gme_common_pvt.g_transaction_header_id;
303 gme_debug.put_line ( g_pkg_name
304 || '.'
305 || l_api_name
306 || ':'
307 || 'count from interface table:'
308 || l_cnt_int);
309
310 END IF;
311
312 /* Jalaj Srivastava Bug 5109154
313 pass additional parameter p_free_tree as false.
314 we wil free the tree while calling process transactions */
315
316 l_ret := inv_txn_manager_grp.validate_transactions
317 (p_api_version => l_api_version
318 ,p_init_msg_list => fnd_api.g_true
319 ,p_validation_level => fnd_api.g_valid_level_full
320 ,p_header_id => gme_common_pvt.g_transaction_header_id
321 ,x_return_status => l_return_status
322 ,x_msg_count => l_msg_count
323 ,x_msg_data => l_msg_data
324 ,x_trans_count => l_txn_count
325 ,p_free_tree => fnd_api.g_false);
326 IF (g_debug <= gme_debug.g_log_statement) THEN
327 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'after validate transactions:'|| l_ret);
328 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'x_trans_count:'|| l_txn_count);
329 select count(*) into l_cnt_temp
330 from mtl_material_transactions_temp
331 where transaction_header_id= gme_common_pvt.g_transaction_header_id;
332 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'count from temp table:'|| l_cnt_temp);
333 END IF;
334 IF l_ret < 0 THEN
335 IF (g_debug <= gme_debug.g_log_statement) THEN
336 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from validate transactions');
337 END IF;
338 /* Jalaj Srivastava Bug 5001915 add msg returned to stack */
339 IF (l_msg_data IS NOT NULL) THEN
340 IF (g_debug <= gme_debug.g_log_statement) THEN
341 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'l_msg_data is: '||l_msg_data);
342 END IF;
343 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
344 ,p_product_code => 'FND'
345 ,p_token1_name => 'MESSAGE'
346 ,p_token1_value => l_msg_data);
347 ELSE
348 /* Bug 5256543 Get messages from interface table and put on stack */
349 FOR get_msgs IN (SELECT error_explanation FROM mtl_transactions_interface
350 WHERE transaction_header_id = gme_common_pvt.g_transaction_header_id
351 AND error_explanation IS NOT NULL) LOOP
352 IF (g_debug <= gme_debug.g_log_statement) THEN
353 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'error_explanation is: '||get_msgs.error_explanation);
354 END IF;
355 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
356 ,p_product_code => 'FND'
357 ,p_token1_name => 'MESSAGE'
358 ,p_token1_value => get_msgs.error_explanation);
359 END LOOP;
360 END IF;
361 RAISE fnd_api.g_exc_error;
362 END IF;
363 END IF;
364 IF (g_debug <= gme_debug.g_log_statement) THEN
365 gme_debug.put_line ( g_pkg_name
366 || '.'
367 || l_api_name
368 || ':'
369 || 'Exiting with '
370 || x_return_status);
371 END IF;
372 EXCEPTION
373 WHEN build_txn_inter_err THEN
374 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'
375 ||' error from build_txn_inter');
376 x_return_status := l_return_status;
377 WHEN fnd_api.g_exc_error THEN
378 x_return_status := fnd_api.g_ret_sts_error;
379 WHEN fnd_api.g_exc_unexpected_error THEN
380 x_return_status := fnd_api.g_ret_sts_unexp_error;
381 WHEN OTHERS THEN
382 x_return_status := fnd_api.g_ret_sts_unexp_error;
383 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
384
385 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
386 gme_debug.put_line ( g_pkg_name
387 || '.'
388 || l_api_name
389 || ':'
390 || 'WHEN OTHERS:'
391 || SQLERRM);
392 END IF;
393 END create_material_txn;
394
395 /* +==========================================================================+
396 | PROCEDURE NAME
397 | update_material_txn
398 |
399 | USAGE
400 | update the transaction in interface table - it deletes all transactions
401 | of transaction_id passed. Creates new transactions as passed.
405 | p_mmti_rec -- mtl_transaction_interface rowtype
402 |
403 | ARGUMENTS
404 | p_transaction_id - transaction_id from mmt for deletion
406 | p_mmli_tbl -- table of mtl_transaction_lots_inumber_tbl as input
407 |
408 | RETURNS
409 | returns via x_status OUT parameters
410 |
411 | HISTORY
412 | Created 02-Feb-05 Pawan Kumar
413 |
414 +==========================================================================+ */
415 PROCEDURE update_material_txn (
416 p_transaction_id IN NUMBER
417 ,p_mmti_rec IN mtl_transactions_interface%ROWTYPE
418 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
419 ,x_return_status OUT NOCOPY VARCHAR2)
420 IS
421 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
422 l_transaction_id NUMBER;
423 l_mmt_rec mtl_material_transactions%ROWTYPE;
424 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
425 l_mmti_rec mtl_transactions_interface%ROWTYPE;
426 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
427 l_mat_dtl_rec gme_material_details%ROWTYPE;
428 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
429 create_material_txn_err EXCEPTION;
430 delete_material_txn_err EXCEPTION;
431 BEGIN
432 --Initially let us assign the return status to success
433 x_return_status := fnd_api.g_ret_sts_success;
434
435 IF (g_debug <= gme_debug.g_log_statement) THEN
436 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
437 || 'Entering');
438 END IF;
439
440 l_mmti_rec := p_mmti_rec;
441 l_mmli_tbl := p_mmli_tbl;
442
443 -- call to delete all the transactions for this transaction_id
444 IF (g_debug <= gme_debug.g_log_statement) THEN
445 gme_debug.put_line ( g_pkg_name
446 || '.'
447 || l_api_name
448 || ':'
449 || 'calling delete with :'
450 || p_transaction_id);
451 END IF;
452
453 delete_material_txn (p_transaction_id => p_transaction_id
454 ,x_return_status => l_return_status);
455
456 IF l_return_status <> fnd_api.g_ret_sts_success THEN
457 IF (g_debug <= gme_debug.g_log_statement) THEN
458 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
459 END IF;
460 RAISE delete_material_txn_err;
461 END IF;
462
463 IF (g_debug <= gme_debug.g_log_statement) THEN
464 gme_debug.put_line ( g_pkg_name
465 || '.'
466 || l_api_name
467 || ':'
468 || 'calling create in update with :'
469 || l_mmti_rec.transaction_interface_id);
470 END IF;
471
472 --NOw send the new transaction and lot tbl for create a new transactions
473 create_material_txn (p_mmti_rec => l_mmti_rec
474 ,p_mmli_tbl => l_mmli_tbl
475 ,x_return_status => l_return_status);
476
477 IF l_return_status <> fnd_api.g_ret_sts_success THEN
478 RAISE create_material_txn_err;
479 END IF;
480
481 IF (g_debug <= gme_debug.g_log_statement) THEN
482 gme_debug.put_line ( g_pkg_name
483 || '.'
484 || l_api_name
485 || ':'
486 || 'Exiting with '
487 || x_return_status);
488 END IF;
489 EXCEPTION
490 WHEN delete_material_txn_err THEN
491 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
492 x_return_status := l_return_status ;
493
494 WHEN create_material_txn_err THEN
495 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
496 x_return_status := l_return_status ;
497 WHEN fnd_api.g_exc_error THEN
498 x_return_status := fnd_api.g_ret_sts_error;
499 WHEN fnd_api.g_exc_unexpected_error THEN
500 x_return_status := fnd_api.g_ret_sts_unexp_error;
501 WHEN OTHERS THEN
502 x_return_status := fnd_api.g_ret_sts_unexp_error;
503 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
504
505 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
506 gme_debug.put_line ( g_pkg_name
507 || '.'
508 || l_api_name
509 || ':'
510 || 'WHEN OTHERS:'
511 || SQLERRM);
512 END IF;
513 END update_material_txn;
514
515 /* +==========================================================================+
516 | PROCEDURE NAME
517 | update_material_txn
518 |
519 | USAGE
520 | update the transaction in interface table - it deletes all transactions
524 | ARGUMENTS
521 | by getting transaction_id from the mmt record passed. Creates new transactions
522 | in interface by converting the mmt to mmti.
523 |
525 | p_mmt_rec -- mtl_material_transaction rowtype
526 | p_mmln_tbl -- table of mtl_transaction_lots_inumber_tbl as input
527 |
528 | RETURNS
529 | returns via x_status OUT parameters
530 |
531 | HISTORY
532 | Created 02-Feb-05 Pawan Kumar
533 |
534 +==========================================================================+ */
535 PROCEDURE update_material_txn (
536 p_mmt_rec IN mtl_material_transactions%ROWTYPE
537 ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
538 ,x_return_status OUT NOCOPY VARCHAR2)
539 IS
540 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN-2';
541 l_transaction_id NUMBER;
542 l_mmt_rec mtl_material_transactions%ROWTYPE;
543 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
544 l_mmti_rec mtl_transactions_interface%ROWTYPE;
545 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
546 l_mat_dtl_rec gme_material_details%ROWTYPE;
547 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
548 create_material_txn_err EXCEPTION;
549 delete_material_txn_err EXCEPTION;
550 BEGIN
551 -- Initially let us assign the return status to success
552 x_return_status := fnd_api.g_ret_sts_success;
553
554 IF (g_debug <= gme_debug.g_log_statement) THEN
555 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
556 || 'Entering');
557 END IF;
558
559 l_mmt_rec := p_mmt_rec;
560 l_mmln_tbl := p_mmln_tbl;
561 -- get the transaction_id from the mmt record for deleting it.
562 l_transaction_id := l_mmt_rec.transaction_id;
563
564 -- call to delete all the transactions for this transaction_id
565 IF (g_debug <= gme_debug.g_log_statement) THEN
566 gme_debug.put_line
567 ( g_pkg_name
568 || '.'
569 || l_api_name
570 || ':'
571 || 'calling delete transaction for transaction id'
572 || l_transaction_id);
573 END IF;
574
575 delete_material_txn (p_transaction_id => l_transaction_id
576 ,x_return_status => l_return_status);
577
578 IF l_return_status <> fnd_api.g_ret_sts_success THEN
579 IF (g_debug <= gme_debug.g_log_statement) THEN
580 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
581 END IF;
582 RAISE delete_material_txn_err;
583 END IF;
584
585 IF (g_debug <= gme_debug.g_log_statement) THEN
586 gme_debug.put_line ( g_pkg_name
587 || '.'
588 || l_api_name
589 || ':'
590 || 'calling construct from mmt to mmti :'
591 || l_mmt_rec.transaction_id);
592 END IF;
593
594 -- now call the construct procedure to populate the interfce for inserting new txns
595 construct_mmti (p_mmt_rec => l_mmt_rec
596 ,p_mmln_tbl => l_mmln_tbl
597 ,x_mmti_rec => l_mmti_rec
598 ,x_mmli_tbl => l_mmli_tbl
599 ,x_return_status => l_return_status);
600
601 IF l_return_status <> fnd_api.g_ret_sts_success THEN
602 IF (g_debug <= gme_debug.g_log_statement) THEN
603 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
604 END IF;
605 RAISE fnd_api.g_exc_error;
606 END IF;
607
608 IF (g_debug <= gme_debug.g_log_statement) THEN
609 gme_debug.put_line ( g_pkg_name
610 || '.'
611 || l_api_name
612 || ':'
613 || 'calling create trans with ='
614 || l_mmt_rec.transaction_id);
615 gme_debug.put_line ( g_pkg_name
616 || '.'
617 || l_api_name
618 || ':'
619 || 'calling create trans with ='
620 || l_mmt_rec.source_line_id);
621 END IF;
622
623 --NOw send the new transaction and lot tbl for create a new transactions
624 create_material_txn (p_mmti_rec => l_mmti_rec
625 ,p_mmli_tbl => l_mmli_tbl
626 ,x_return_status => l_return_status);
627
628 IF l_return_status <> fnd_api.g_ret_sts_success THEN
629 IF (g_debug <= gme_debug.g_log_statement) THEN
630 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
631 END IF;
632 RAISE create_material_txn_err;
633 END IF;
634
635 IF (g_debug <= gme_debug.g_log_statement) THEN
636 gme_debug.put_line ( g_pkg_name
637 || '.'
638 || l_api_name
642 END IF;
639 || ':'
640 || 'Exiting with '
641 || x_return_status);
643 EXCEPTION
644 WHEN delete_material_txn_err THEN
645 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
646 x_return_status := l_return_status ;
647 WHEN create_material_txn_err THEN
648 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
649 x_return_status := l_return_status ;
650 WHEN fnd_api.g_exc_error THEN
651 x_return_status := fnd_api.g_ret_sts_error;
652 WHEN fnd_api.g_exc_unexpected_error THEN
653 x_return_status := fnd_api.g_ret_sts_unexp_error;
654 WHEN OTHERS THEN
655 x_return_status := fnd_api.g_ret_sts_unexp_error;
656 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
657
658 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
659 gme_debug.put_line ( g_pkg_name
660 || '.'
661 || l_api_name
662 || ':'
663 || 'WHEN OTHERS:'
664 || SQLERRM);
665 END IF;
666 END update_material_txn;
667
668 /* +==========================================================================+
669 | PROCEDURE NAME
670 | delete_material_txn
671 |
672 | USAGE
673 | delete all transactions of transaction_id passed by creating reverse transaction.
674 |
675 | ARGUMENTS
676 | p_transaction_id -- transaction_id from mmt for deletion
677 |
678 |
679 | RETURNS
680 | returns via x_status OUT parameters
681 |
682 | HISTORY
683 | Created 02-Feb-05 Pawan Kumar
684 |
685 +==========================================================================+ */
686 PROCEDURE delete_material_txn (
687 p_transaction_id IN NUMBER
688 ,p_txns_pair IN NUMBER DEFAULT NULL
689 ,x_return_status OUT NOCOPY VARCHAR2)
690 IS
691 CURSOR cur_get_ph_txns (v_transaction_id NUMBER)
692 IS
693 SELECT transaction_id2
694 FROM gme_transaction_pairs
695 WHERE transaction_id1 = v_transaction_id
696 AND pair_type = gme_common_pvt.g_pairs_phantom_type;
697
698 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_TXN';
699 l_transaction_id NUMBER;
700 m_transaction_id NUMBER;
701 l_mmt_rec mtl_material_transactions%ROWTYPE;
702 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
703 l_mmti_rec mtl_transactions_interface%ROWTYPE;
704 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
705 l_mat_dtl_rec gme_material_details%ROWTYPE;
706 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
707 create_material_txn_err EXCEPTION;
708 delete_material_txn_err EXCEPTION;
709 get_trans_err EXCEPTION;
710 BEGIN
711 -- Initially let us assign the return status to success
712 x_return_status := fnd_api.g_ret_sts_success;
713
714 IF (g_debug <= gme_debug.g_log_statement) THEN
715 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
716 || 'Entering');
717 END IF;
718
719 l_transaction_id := p_transaction_id;
720
721 IF l_transaction_id IS NOT NULL THEN
722 IF (g_debug <= gme_debug.g_log_statement) THEN
723 gme_debug.put_line
724 ( g_pkg_name
725 || '.'
726 || l_api_name
727 || ':'
728 || 'getting all transaction for deletion with transaction id '
729 || l_transaction_id);
730 END IF;
731
732 get_transactions (p_transaction_id => l_transaction_id
733 ,x_mmt_rec => l_mmt_rec
734 ,x_mmln_tbl => l_mmln_tbl
735 ,x_return_status => l_return_status);
736
737 IF l_return_status <> fnd_api.g_ret_sts_success THEN
738 IF (g_debug <= gme_debug.g_log_statement) THEN
739 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get transactions');
740 END IF;
741 RAISE get_trans_err;
742 END IF;
743
744 END IF;
745
746 IF (g_debug <= gme_debug.g_log_statement) THEN
747 gme_debug.put_line ( g_pkg_name
748 || '.'
749 || l_api_name
750 || ':'
751 || 'calling construct from mmt to mmti :'
752 || l_mmt_rec.transaction_id);
753 gme_debug.put_line ( g_pkg_name
754 || '.'
755 || l_api_name
756 || ':'
757 || 'the mmt record source line id(null) ='
758 || l_mmt_rec.source_line_id);
759 END IF;
760
761 construct_mmti (p_mmt_rec => l_mmt_rec
762 ,p_mmln_tbl => l_mmln_tbl
766
763 ,x_mmti_rec => l_mmti_rec
764 ,x_mmli_tbl => l_mmli_tbl
765 ,x_return_status => l_return_status);
767 IF l_return_status <> fnd_api.g_ret_sts_success THEN
768 IF (g_debug <= gme_debug.g_log_statement) THEN
769 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
770 END IF;
771 RAISE fnd_api.g_exc_error;
772 END IF;
773
774 IF (g_debug <= gme_debug.g_log_statement) THEN
775 gme_debug.put_line ( g_pkg_name
776 || '.'
777 || l_api_name
778 || ':'
779 || 'getting material for '
780 || l_mmt_rec.trx_source_line_id);
781 END IF;
782
783 -- get the material details of the transaction
784 l_mat_dtl_rec.material_detail_id := l_mmt_rec.trx_source_line_id;
785
786 -- Now fetch the material detials for the mateiral
787 IF (g_debug <= gme_debug.g_log_statement) THEN
788 gme_debug.put_line ( g_pkg_name
789 || '.'
790 || l_api_name
791 || ':'
792 || 'getting material for '
793 || l_mat_dtl_rec.material_detail_id);
794 END IF;
795
796 IF NOT gme_material_details_dbl.fetch_row
797 (p_material_detail => l_mat_dtl_rec
798 ,x_material_detail => l_mat_dtl_rec) THEN
799 RAISE fnd_api.g_exc_error;
800 END IF;
801
802 IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
803 --(35)
804 l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
805 --(43)
806 l_mmti_rec.transaction_action_id :=
807 gme_common_pvt.g_ing_ret_txn_action;
808 --(27)
809 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
810 --(43)
811 l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
812 -- (35)
813 l_mmti_rec.transaction_action_id :=
814 gme_common_pvt.g_ing_issue_txn_action;
815 --(1)
816 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_completion THEN
817 -- (44)
818 l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
819 --(17)
820 l_mmti_rec.transaction_action_id :=
821 gme_common_pvt.g_prod_ret_txn_action;
822 --(27)
823 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return THEN
824 l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
825 l_mmti_rec.transaction_action_id :=
826 gme_common_pvt.g_prod_comp_txn_action;
827 --(31)
828 ELSIF l_mmti_rec.transaction_type_id =
829 gme_common_pvt.g_byprod_completion THEN
830 l_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_return;
831 l_mmti_rec.transaction_action_id :=
832 gme_common_pvt.g_byprod_ret_txn_action;
833 --(32)
834 ELSE
835 --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
836 l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
837 l_mmti_rec.transaction_action_id :=
838 gme_common_pvt.g_byprod_comp_txn_action;
839 --(31)
840 END IF;
841
842 IF (g_debug <= gme_debug.g_log_statement) THEN
843 gme_debug.put_line ( g_pkg_name
844 || '.'
845 || l_api_name
846 || ':'
847 || 'sending transaction_type_id:'
848 || l_mmti_rec.transaction_type_id);
849 gme_debug.put_line ( g_pkg_name
850 || '.'
851 || l_api_name
852 || ':'
853 || 'sending transaction_action_id:'
854 || l_mmti_rec.transaction_action_id);
855 END IF;
856
857 -- set for delete transaction
858 l_mmti_rec.source_line_id := l_mmt_rec.transaction_id;
859 gme_debug.put_line ( g_pkg_name
860 || '.'
861 || l_api_name
862 || ':'
863 || 'calling create trans with ='
864 || l_mmti_rec.source_line_id);
865 -- with the new rec- call the create txn
866 create_material_txn (p_mmti_rec => l_mmti_rec
867 ,p_mmli_tbl => l_mmli_tbl
868 ,p_phantom_trans => 1
869 ,x_return_status => l_return_status);
870
871 IF l_return_status <> fnd_api.g_ret_sts_success THEN
872 IF (g_debug <= gme_debug.g_log_statement) THEN
873 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
874 END IF;
875 RAISE create_material_txn_err;
876 END IF;
877
878 IF (g_debug <= gme_debug.g_log_statement) THEN
879 gme_debug.put_line ( g_pkg_name
880 || '.'
881 || l_api_name
882 || ':'
883 || 'status from create :'
884 || l_return_status);
885 END IF;
886
887 -- Insert into gme_transactions_pairs table
888 -- code need to added for INSERT INTO GME_TRANSACTION_PAIRS tables
889 -- which column will carry the material detail ld
890 IF (g_debug <= gme_debug.g_log_statement) THEN
891 gme_debug.put_line ( g_pkg_name
892 || '.'
893 || l_api_name
894 || ':'
895 || 'inserting into pairs table transaction_id:'
896 || l_transaction_id);
897 gme_debug.put_line ( g_pkg_name
898 || '.'
899 || l_api_name
900 || ':'
901 || 'inserting into pairs table batch_id:'
902 || l_mat_dtl_rec.batch_id);
903 gme_debug.put_line
904 ( g_pkg_name
905 || '.'
906 || l_api_name
907 || ':'
908 || 'inserting into pairs table material_detail_id:'
909 || l_mat_dtl_rec.material_detail_id);
910 gme_debug.put_line ( g_pkg_name
911 || '.'
912 || l_api_name
913 || ':'
914 || 'inserting into pairs table pair_type:'
915 || gme_common_pvt.g_pairs_reversal_type);
916 END IF;
917
918 INSERT INTO gme_transaction_pairs
919 (batch_id, material_detail_id
920 ,transaction_id1, transaction_id2
921 ,pair_type)
922 VALUES (l_mat_dtl_rec.batch_id, l_mat_dtl_rec.material_detail_id
923 ,l_mmt_rec.transaction_id, NULL
924 ,gme_common_pvt.g_pairs_reversal_type);
925
926 -- changes for phantom
927 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
928 IF (g_debug <= gme_debug.g_log_statement) THEN
929 gme_debug.put_line ( g_pkg_name
930 || '.'
931 || l_api_name
932 || ':'
933 || 'deleting for phantom:'
934 || l_mat_dtl_rec.phantom_line_id);
935 END IF;
936
937 OPEN cur_get_ph_txns (p_transaction_id);
938
939 FETCH cur_get_ph_txns
940 INTO m_transaction_id;
941
945 IF (g_debug <= gme_debug.g_log_statement) THEN
942 CLOSE cur_get_ph_txns;
943
944 IF m_transaction_id IS NOT NULL THEN
946 gme_debug.put_line ( g_pkg_name
947 || '.'
948 || l_api_name
949 || ':'
950 || 'calling delete txns for phantom:'
951 || m_transaction_id);
952 END IF;
953
954 delete_material_txn (p_transaction_id => m_transaction_id
955 ,p_txns_pair => 1
956 ,x_return_status => l_return_status);
957 IF l_return_status <> fnd_api.g_ret_sts_success THEN
958 IF (g_debug <= gme_debug.g_log_statement) THEN
959 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
960 END IF;
961 RAISE delete_material_txn_err;
962 END IF; -- ret status
963 ELSE
964 IF (g_debug <= gme_debug.g_log_statement) THEN
965 gme_debug.put_line ( g_pkg_name
966 || '.'
967 || l_api_name
968 || ':'
969 || 'no phantom txns found for '
970 || l_transaction_id);
971 END IF;
972 END IF; -- m_transaction_id is not null
973 END IF;
974
975 IF (g_debug <= gme_debug.g_log_statement) THEN
976 gme_debug.put_line ( g_pkg_name
977 || '.'
978 || l_api_name
979 || ':'
980 || 'Exiting with '
981 || x_return_status);
982 END IF;
983 EXCEPTION
984 WHEN delete_material_txn_err THEN
985 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
986 x_return_status := l_return_status ;
987 WHEN get_trans_err THEN
988 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_transactions');
989 x_return_status := l_return_status ;
990 WHEN create_material_txn_err THEN
991 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
992 x_return_status := l_return_status ;
993 WHEN fnd_api.g_exc_error THEN
994 x_return_status := fnd_api.g_ret_sts_error;
995 WHEN fnd_api.g_exc_unexpected_error THEN
996 IF (g_debug <= gme_debug.g_log_statement) THEN
997 gme_debug.put_line ( g_pkg_name
998 || '.'
999 || l_api_name
1000 || ':'
1001 || 'Unexpected');
1002 END IF;
1003
1004 x_return_status := fnd_api.g_ret_sts_unexp_error;
1005 WHEN OTHERS THEN
1006 x_return_status := fnd_api.g_ret_sts_unexp_error;
1007 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1008
1009 IF (g_debug <= gme_debug.g_log_statement) THEN
1010 gme_debug.put_line ( g_pkg_name
1011 || '.'
1012 || l_api_name
1013 || ':'
1014 || 'WHEN OTHERS:'
1015 || SQLERRM);
1016 END IF;
1017 END delete_material_txn;
1018
1019 /* +==========================================================================+
1020 | PROCEDURE NAME
1021 | build_txn_inter
1022 |
1023 | USAGE
1024 | Inserts the transaction to interface table
1025 |
1026 | ARGUMENTS
1027 | p_mmti_rec -- mtl_transaction_interface rowtype
1028 | p_mmli_tbl -- table of mtl_trans_lots_inter_tbl as input
1029 |
1030 | RETURNS
1031 | returns via x_status OUT parameters
1032 |
1033 | HISTORY
1034 | Created 02-Feb-05 Pawan Kumar
1035 |
1036 +==========================================================================+ */
1037 PROCEDURE build_txn_inter (
1038 p_mmti_rec IN mtl_transactions_interface%ROWTYPE
1039 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
1040 ,p_assign_phantom IN NUMBER DEFAULT 0
1041 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1042 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1043 ,x_return_status OUT NOCOPY VARCHAR2)
1044 IS
1045 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1046 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1047 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER';
1048 x_header_id NUMBER;
1049 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1050 l_insert_hdr BOOLEAN;
1051 build_txn_inter_err EXCEPTION;
1052 build_txn_inter_lot_err EXCEPTION;
1053 lot_expired_err EXCEPTION;
1054 insert_hdr_err EXCEPTION ;
1055 BEGIN
1056 -- Initially let us assign the return status to success
1057 x_return_status := fnd_api.g_ret_sts_success;
1058
1059 IF (g_debug <= gme_debug.g_log_statement) THEN
1063
1060 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1061 || 'Entering');
1062 END IF;
1064 l_mmti_rec := p_mmti_rec;
1065 l_mmli_tbl := p_mmli_tbl;
1066 /* Bug 4929610 Added code to pass parameter */
1067 IF (l_mmli_tbl.COUNT > 0) THEN
1068 l_insert_hdr := FALSE;
1069 ELSE
1070 l_insert_hdr := TRUE;
1071 END IF;
1072 build_txn_inter_hdr (p_mmti_rec => p_mmti_rec
1073 ,p_assign_phantom => p_assign_phantom
1074 ,x_mmti_rec => x_mmti_rec
1075 ,x_return_status => l_return_status
1076 ,p_insert_hdr => l_insert_hdr);
1077
1078 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1079 IF (g_debug <= gme_debug.g_log_statement) THEN
1080 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from build inter hdr');
1081 END IF;
1082 RAISE build_txn_inter_err;
1083 END IF;
1084
1085 IF (g_debug <= gme_debug.g_log_statement) THEN
1086 gme_debug.put_line ( g_pkg_name
1087 || '.'
1088 || l_api_name
1089 || ':'
1090 || 'after header- inserting lot');
1091 gme_debug.put_line ( g_pkg_name
1092 || '.'
1093 || l_api_name
1094 || ':'
1095 || 'after header- inter_id:'
1096 || x_mmti_rec.transaction_interface_id);
1097 gme_debug.put_line ( g_pkg_name
1098 || '.'
1099 || l_api_name
1100 || ':'
1101 || 'after header- header_id:'
1102 || x_mmti_rec.transaction_header_id);
1103 gme_debug.put_line ( g_pkg_name
1104 || '.'
1105 || l_api_name
1106 || ':'
1107 || 'after header- trasn_type:'
1108 || x_mmti_rec.transaction_type_id);
1109 END IF;
1110
1111 IF (l_mmli_tbl.COUNT > 0) THEN
1112 FOR i IN 1 .. l_mmli_tbl.COUNT LOOP
1113 IF (g_debug <= gme_debug.g_log_statement) THEN
1114 gme_debug.put_line ( g_pkg_name
1115 || '.'
1116 || l_api_name
1117 || ':'
1118 || 'lot_number '
1119 || l_mmli_tbl (i).lot_number);
1120 gme_debug.put_line ( g_pkg_name
1121 || '.'
1122 || l_api_name
1123 || ':'
1124 || 'transaction_quantity '
1125 || l_mmli_tbl (i).transaction_quantity);
1126 END IF;
1127 /* Bug 4929610 added lot expiry check */
1128 IF (x_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue) THEN
1129 IF (gme_transactions_pvt.is_lot_expired (p_organization_id => x_mmti_rec.organization_id,
1130 p_inventory_item_id => x_mmti_rec.inventory_item_id,
1131 p_lot_number => l_mmli_tbl(i).lot_number,
1132 p_date => x_mmti_rec.transaction_date)) THEN
1133 RAISE lot_expired_err;
1134 END IF;
1135 END IF;
1136 -- Bug 6925025
1137 -- Added parameters subinventory_code and locator_id
1138 build_txn_inter_lot
1139 (p_trans_inter_id => x_mmti_rec.transaction_interface_id
1140 ,p_transaction_type_id => x_mmti_rec.transaction_type_id
1141 ,p_inventory_item_id => x_mmti_rec.inventory_item_id
1142 ,p_subinventory_code => x_mmti_rec.subinventory_code
1143 ,p_locator_id => x_mmti_rec.locator_id
1144 ,p_mmli_rec => l_mmli_tbl (i)
1145 ,x_mmli_rec => x_mmli_tbl (i)
1146 ,x_return_status => l_return_status);
1147
1148 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1149 IF (g_debug <= gme_debug.g_log_statement) THEN
1150 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from build lot inter');
1151 END IF;
1152 RAISE build_txn_inter_lot_err;
1153 END IF;
1154 END LOOP;
1155 END IF; --l_mmli_tbl.count
1156 /* Bug 4929610 Added code to insert if not inserted originally */
1157 IF NOT(l_insert_hdr) THEN
1158 insert_txn_inter_hdr(p_mmti_rec => x_mmti_rec,
1159 x_return_status => l_return_status);
1160 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1161 RAISE insert_hdr_err;
1162 END IF;
1163 END IF;
1164
1165 IF (g_debug <= gme_debug.g_log_statement) THEN
1166 gme_debug.put_line ( g_pkg_name
1167 || '.'
1168 || l_api_name
1172 END IF;
1169 || ':'
1170 || 'Exiting with '
1171 || x_return_status);
1173 EXCEPTION
1174 WHEN insert_hdr_err THEN
1175 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'insert_hdr_err');
1176 x_return_status := l_return_status;
1177 WHEN lot_expired_err THEN
1178 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'lot_expired_err');
1179 x_return_status := 'T';
1180 WHEN build_txn_inter_lot_err THEN
1181 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'build_txn_inter_lot_err');
1182 x_return_status := l_return_status;
1183 WHEN build_txn_inter_err THEN
1184 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'build_txn_inter_err');
1185 x_return_status := l_return_status;
1186 WHEN fnd_api.g_exc_error THEN
1187 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'user defined error');
1188 x_return_status := fnd_api.g_ret_sts_error;
1189 WHEN fnd_api.g_exc_unexpected_error THEN
1190 gme_debug.put_line ( g_pkg_name
1191 || '.'
1192 || l_api_name
1193 || ':'
1194 || 'unexp'
1195 || SQLERRM);
1196 x_return_status := fnd_api.g_ret_sts_unexp_error;
1197 WHEN OTHERS THEN
1198 x_return_status := fnd_api.g_ret_sts_unexp_error;
1199 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1200
1201 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1202 gme_debug.put_line ( g_pkg_name
1203 || '.'
1204 || l_api_name
1205 || ':'
1206 || 'WHEN OTHERS:'
1207 || SQLERRM);
1208 END IF;
1209 END build_txn_inter;
1210
1211 /* +==========================================================================+
1212 | PROCEDURE NAME
1213 | build_txn_inter_hdr
1214 |
1215 | USAGE
1216 | Inserts the transaction to interface table
1217 |
1218 | ARGUMENTS
1219 | p_mmti_rec -- mtl_transaction_interface rowtype
1220 |
1221 |
1222 | RETURNS
1223 | returns via x_status OUT parameters
1224 |
1225 | HISTORY
1226 | Created 02-Feb-05 Pawan Kumar
1227 | 13-Sep-05 Namit Singhi - Modified to include insert into transfer_lpn_id.
1228 |
1229 +==========================================================================+ */
1230 PROCEDURE build_txn_inter_hdr (
1231 p_mmti_rec IN mtl_transactions_interface%ROWTYPE
1232 ,p_assign_phantom IN NUMBER DEFAULT 0
1233 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1234 ,x_return_status OUT NOCOPY VARCHAR2
1235 ,p_insert_hdr IN BOOLEAN DEFAULT TRUE)
1236 IS
1237 CURSOR get_location (v_org_id IN NUMBER
1238 ,v_sub_inv IN VARCHAR2
1239 ,v_loc_id IN NUMBER) IS
1240 SELECT substr(concatenated_segments,1,100)
1241 FROM wms_item_locations_kfv
1242 WHERE organization_id = v_org_id
1243 AND subinventory_code = v_sub_inv
1244 AND inventory_location_id (+) = v_loc_id;
1245 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1246 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_hdr';
1247 x_header_id NUMBER;
1248 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1249 l_item VARCHAR2(100);
1250 l_type VARCHAR2(100);
1251 l_locator VARCHAR2(100);
1252 material_status_err EXCEPTION ;
1253 insert_hdr_err EXCEPTION ;
1254 BEGIN
1255 -- Initially let us assign the return status to success
1256 x_return_status := fnd_api.g_ret_sts_success;
1257
1258 IF (g_debug <= gme_debug.g_log_statement) THEN
1259 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1260 || 'Entering');
1261 END IF;
1262
1263 l_mmti_rec := p_mmti_rec;
1264
1265 IF gme_common_pvt.g_transaction_header_id IS NULL THEN
1266 SELECT mtl_material_transactions_s.NEXTVAL
1267 INTO gme_common_pvt.g_transaction_header_id
1268 FROM DUAL;
1269
1270 IF (g_debug <= gme_debug.g_log_statement) THEN
1271 gme_debug.put_line ( g_pkg_name
1272 || '.'
1273 || l_api_name
1274 || ':'
1275 || 'GME_COMMON_PVT.g_transaction_header_id '
1276 || gme_common_pvt.g_transaction_header_id);
1277 END IF;
1278 END IF;
1279
1280 l_mmti_rec.transaction_header_id :=
1281 gme_common_pvt.g_transaction_header_id;
1282
1283 IF (g_debug <= gme_debug.g_log_statement) THEN
1284 gme_debug.put_line ( g_pkg_name
1285 || '.'
1286 || l_api_name
1287 || ':'
1288 || 'l_mmti_rec.transaction_header_id '
1289 || l_mmti_rec.transaction_header_id);
1293 INTO l_mmti_rec.transaction_interface_id
1290 END IF;
1291
1292 SELECT mtl_material_transactions_s.NEXTVAL
1294 FROM DUAL;
1295
1296 IF l_mmti_rec.transaction_type_id IN
1297 (gme_common_pvt.g_ing_return
1298 ,gme_common_pvt.g_prod_completion
1299 ,gme_common_pvt.g_byprod_completion) THEN
1300 l_mmti_rec.transaction_batch_seq := 1;
1301 l_mmti_rec.transaction_batch_id := l_mmti_rec.transaction_header_id ;
1302 l_mmti_rec.transaction_quantity :=
1303 ABS(l_mmti_rec.transaction_quantity);
1304 l_mmti_rec.secondary_transaction_quantity :=
1305 ABS(l_mmti_rec.secondary_transaction_quantity);
1306 ELSE
1307 l_mmti_rec.transaction_batch_seq := 100;
1308 l_mmti_rec.transaction_batch_id := l_mmti_rec.transaction_header_id ;
1309 l_mmti_rec.transaction_quantity :=
1310 (-1) * ABS(l_mmti_rec.transaction_quantity);
1311 l_mmti_rec.secondary_transaction_quantity :=
1312 (-1) * ABS(l_mmti_rec.secondary_transaction_quantity);
1313 END IF;
1314 -- Code for checking mateial status
1315 IF (inv_material_status_grp.is_status_applicable
1316 (p_wms_installed => NULL
1317 ,p_trx_status_enabled => NULL
1318 ,p_trx_type_id => l_mmti_rec.transaction_type_id
1319 ,p_lot_status_enabled => NULL
1320 ,p_serial_status_enabled => NULL
1321 ,p_organization_id => l_mmti_rec.organization_id
1322 ,p_inventory_item_id => l_mmti_rec.inventory_item_id
1323 ,p_sub_code => l_mmti_rec.subinventory_code
1324 ,p_locator_id => l_mmti_rec.locator_id
1325 ,p_lot_number => NULL
1326 ,p_serial_number => NULL
1327 ,p_object_type => 'A') = 'N') THEN
1328 IF (g_debug <= gme_debug.g_log_statement) THEN
1329 gme_debug.put_line ( g_pkg_name
1330 || '.'
1331 || l_api_name
1332 || ':'
1333 || 'material status check not valid for item '
1334 || l_mmti_rec.inventory_item_id);
1335 END IF;
1336 RAISE material_status_err;
1337 END IF; /* inv_material_status_grp.is_status_applicable */
1338 -- for a phantom transaction- asssign value to transaction_refernece
1339 IF (g_debug <= gme_debug.g_log_statement) THEN
1340 gme_debug.put_line ( g_pkg_name
1341 || '.'
1342 || l_api_name
1343 || 'Material Status is VALID');
1344 gme_debug.put_line ( g_pkg_name
1345 || '.'
1346 || l_api_name
1347 || 'p_assign_phantom: '
1348 || p_assign_phantom);
1349 END IF;
1350
1351 IF p_assign_phantom = 1 THEN
1352 l_mmti_rec.transaction_reference :=
1353 (l_mmti_rec.transaction_interface_id);
1354 END IF;
1355
1356 IF (g_debug <= gme_debug.g_log_statement) THEN
1357 gme_debug.put_line ( g_pkg_name
1358 || '.'
1359 || l_api_name
1360 || ':'
1361 || 'transaction_interface_id: '
1362 || l_mmti_rec.transaction_interface_id);
1363 gme_debug.put_line ( g_pkg_name
1364 || '.'
1365 || l_api_name
1366 || ':'
1367 || 'transaction_source_id: '
1368 || l_mmti_rec.transaction_source_id);
1369 gme_debug.put_line ( g_pkg_name
1370 || '.'
1371 || l_api_name
1372 || ':'
1373 || 'transaction_type_id: '
1374 || l_mmti_rec.transaction_type_id);
1375 gme_debug.put_line ( g_pkg_name
1376 || '.'
1377 || l_api_name
1378 || ':'
1379 || 'transaction_source_type_id: '
1380 || l_mmti_rec.transaction_source_type_id);
1381 gme_debug.put_line ( g_pkg_name
1382 || '.'
1383 || l_api_name
1384 || ':'
1385 || 'transaction_quantity: '
1386 || l_mmti_rec.transaction_quantity);
1387 gme_debug.put_line ( g_pkg_name
1388 || '.'
1389 || l_api_name
1390 || ':'
1391 || 'transaction_uom: '
1392 || l_mmti_rec.transaction_uom);
1393 gme_debug.put_line ( g_pkg_name
1394 || '.'
1395 || l_api_name
1396 || ':'
1397 || 'secondary_transaction_quantity: '
1401 || l_api_name
1398 || l_mmti_rec.secondary_transaction_quantity);
1399 gme_debug.put_line ( g_pkg_name
1400 || '.'
1402 || ':'
1403 || 'secondary_uom_code: '
1404 || l_mmti_rec.secondary_uom_code);
1405 gme_debug.put_line ( g_pkg_name
1406 || '.'
1407 || l_api_name
1408 || ':'
1409 || 'primary_quantity: '
1410 || l_mmti_rec.primary_quantity);
1411 gme_debug.put_line ( g_pkg_name
1412 || '.'
1413 || l_api_name
1414 || ':'
1415 || 'process_flag: '
1416 || l_mmti_rec.process_flag);
1417 gme_debug.put_line ( g_pkg_name
1418 || '.'
1419 || l_api_name
1420 || ':'
1421 || 'inventory_item_id: '
1422 || l_mmti_rec.inventory_item_id);
1423 gme_debug.put_line ( g_pkg_name
1424 || '.'
1425 || l_api_name
1426 || ':'
1427 || 'revision: '
1428 || l_mmti_rec.revision);
1429 gme_debug.put_line ( g_pkg_name
1430 || '.'
1431 || l_api_name
1432 || ':'
1433 || 'transfer_lpn_id: '
1434 || l_mmti_rec.transfer_lpn_id);
1435 gme_debug.put_line ( g_pkg_name
1436 || '.'
1437 || l_api_name
1438 || ':'
1439 || 'organization_id: '
1440 || l_mmti_rec.organization_id);
1441 gme_debug.put_line ( g_pkg_name
1442 || '.'
1443 || l_api_name
1444 || ':'
1445 || 'subinventory_code: '
1446 || TO_CHAR (l_mmti_rec.subinventory_code) );
1447 gme_debug.put_line ( g_pkg_name
1448 || '.'
1449 || l_api_name
1450 || ':'
1451 || 'locator_id:'
1452 || l_mmti_rec.locator_id);
1453 gme_debug.put_line ( g_pkg_name
1454 || '.'
1455 || l_api_name
1456 || ':'
1457 || 'source_line_id: '
1458 || l_mmti_rec.source_line_id);
1459 gme_debug.put_line ( g_pkg_name
1460 || '.'
1461 || l_api_name
1462 || ':'
1463 || 'trx_source_line_id: '
1464 || l_mmti_rec.trx_source_line_id);
1465 gme_debug.put_line ( g_pkg_name
1466 || '.'
1467 || l_api_name
1468 || ':'
1469 || 'source_header_id: '
1470 || l_mmti_rec.source_header_id);
1471 gme_debug.put_line ( g_pkg_name
1472 || '.'
1473 || l_api_name
1474 || ':'
1475 || 'transaction_source_name: '
1476 || l_mmti_rec.transaction_source_name);
1477 gme_debug.put_line ( g_pkg_name
1478 || '.'
1479 || l_api_name
1480 || ':'
1481 || 'transaction_mode: '
1482 || l_mmti_rec.transaction_mode);
1483 gme_debug.put_line ( g_pkg_name
1484 || '.'
1485 || l_api_name
1486 || ':'
1487 || 'last_updated_by: '
1488 || gme_common_pvt.g_user_ident);
1489 gme_debug.put_line ( g_pkg_name
1490 || '.'
1491 || l_api_name
1492 || ':'
1493 || 'transaction_reference: '
1494 || l_mmti_rec.transaction_reference);
1495 gme_debug.put_line ( g_pkg_name
1496 || '.'
1497 || l_api_name
1498 || ':'
1499 || 'transaction_batch_id: '
1500 || l_mmti_rec.transaction_batch_id);
1501 gme_debug.put_line ( g_pkg_name
1502 || '.'
1503 || l_api_name
1504 || ':'
1505 || 'transaction_batch_seq: '
1506 || l_mmti_rec.transaction_batch_seq);
1507 gme_debug.put_line ( g_pkg_name
1508 || '.'
1512 || l_mmti_rec.reservation_quantity);
1509 || l_api_name
1510 || ':'
1511 || 'reservation_quantity: '
1513 gme_debug.put_line ( g_pkg_name
1514 || '.'
1515 || l_api_name
1516 || ':'
1517 || 'transaction_sequence_id: '
1518 || l_mmti_rec.transaction_sequence_id);
1519 gme_debug.put_line ( g_pkg_name
1520 || '.'
1521 || l_api_name
1522 || ':'
1523 || 'reason_id: '
1524 || l_mmti_rec.reason_id);
1525 gme_debug.put_line ( g_pkg_name
1526 || '.'
1527 || l_api_name
1528 || ':'
1529 || 'transfer_lpn_id: '
1530 || l_mmti_rec.transfer_lpn_id);
1531 gme_debug.put_line ( g_pkg_name
1532 || '.'
1533 || l_api_name
1534 || ':'
1535 || 'transaction_date: '
1536 || to_char(l_mmti_rec.transaction_date,'YYYY-MON-DD HH24:MI:SS'));
1537 END IF;
1538 /* Bug 4929610 fixed */
1539 IF (p_insert_hdr) THEN
1540 insert_txn_inter_hdr(p_mmti_rec => l_mmti_rec,
1541 x_return_status => l_return_status);
1542 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1543 RAISE insert_hdr_err;
1544 END IF;
1545 END IF;
1546 x_mmti_rec := l_mmti_rec;
1547
1548 IF (g_debug <= gme_debug.g_log_statement) THEN
1549 gme_debug.put_line ( g_pkg_name
1550 || '.'
1551 || l_api_name
1552 || ':'
1553 || 'after inserting header with status:'||x_return_status);
1554
1555 END IF;
1556 EXCEPTION
1557 WHEN insert_hdr_err THEN
1558 x_return_status := l_return_status;
1559 WHEN material_status_err THEN
1560 SELECT substr(concatenated_segments,1,100)
1561 INTO l_item
1562 FROM mtl_system_items_kfv
1563 WHERE organization_id = l_mmti_rec.organization_id
1564 AND inventory_item_id = l_mmti_rec.inventory_item_id;
1565 OPEN get_location(l_mmti_rec.organization_id, l_mmti_rec.subinventory_code, l_mmti_rec.locator_id);
1566 FETCH get_location INTO l_locator;
1567 CLOSE get_location;
1568 SELECT transaction_type_name
1569 INTO l_type
1570 FROM mtl_transaction_types
1571 WHERE transaction_type_id = l_mmti_rec.transaction_type_id;
1572 IF l_locator IS NOT NULL THEN
1573 gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_SUB_LOC'
1574 ,'TRANSTYPE',l_type,'ITEM',l_item
1575 ,'SUBINV',l_mmti_rec.subinventory_code
1576 ,'LOCN',l_locator);
1577 ELSE
1578 gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_SUB'
1579 ,'TRANSTYPE',l_type,'ITEM',l_item
1580 ,'SUBINV',l_mmti_rec.subinventory_code);
1581 END IF;
1582 gme_debug.put_line ( g_pkg_name
1583 || '.'
1584 || l_api_name
1585 || ':'
1586 || 'material status invalid for item, subinventory, locator etc'
1587 );
1588 x_return_status := 'T';
1589 WHEN fnd_api.g_exc_unexpected_error THEN
1590 gme_debug.put_line ( g_pkg_name
1591 || '.'
1592 || l_api_name
1593 || ':'
1594 || 'unexp'
1595 || SQLERRM);
1596 x_return_status := fnd_api.g_ret_sts_unexp_error;
1597 WHEN OTHERS THEN
1598 x_return_status := fnd_api.g_ret_sts_unexp_error;
1599 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1600
1601 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1602 gme_debug.put_line ( g_pkg_name
1603 || '.'
1604 || l_api_name
1605 || ':'
1606 || 'WHEN OTHERS:'
1607 || SQLERRM);
1608 END IF;
1609 END build_txn_inter_hdr;
1610
1611 /* +==========================================================================+
1612 | PROCEDURE NAME
1613 | build_txn_inter_lot
1614 |
1615 | USAGE
1616 | Inserts the transaction to interface table
1617 |
1618 | ARGUMENTS
1619 |
1620 | p_mmli_rec -- table of mtl_trans_lots_inter_tbl as input
1621 |
1622 | RETURNS
1623 | returns via x_status OUT parameters
1624 |
1625 | HISTORY
1626 | Created 02-Feb-05 Pawan Kumar
1627 | Bug 6925025 11-Apr-2008 Archana Mundhe
1628 | Added parameters subinventory_code and locator_id.
1629 +==========================================================================+ */
1633 ,p_inventory_item_id IN NUMBER
1630 PROCEDURE build_txn_inter_lot (
1631 p_trans_inter_id IN NUMBER
1632 ,p_transaction_type_id IN NUMBER
1634 ,p_subinventory_code IN VARCHAR2
1635 ,p_locator_id IN NUMBER
1636 ,p_mmli_rec IN mtl_transaction_lots_interface%ROWTYPE
1637 ,x_mmli_rec OUT NOCOPY mtl_transaction_lots_interface%ROWTYPE
1638 ,x_return_status OUT NOCOPY VARCHAR2)
1639 IS
1640 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_LOT';
1641 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1642 l_mmli_rec mtl_transaction_lots_interface%ROWTYPE;
1643 l_transaction_type_id NUMBER;
1644 l_inventory_item_id NUMBER;
1645 l_item VARCHAR2(100);
1646 l_type VARCHAR2(100);
1647 -- Bug 6925025
1648 l_subinventory_code VARCHAR2(100);
1649 l_locator_id NUMBER;
1650 material_status_err EXCEPTION;
1651 BEGIN
1652 x_return_status := fnd_api.g_ret_sts_success;
1653
1654 IF (g_debug <= gme_debug.g_log_statement) THEN
1655 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1656 || 'Entering');
1657 END IF;
1658
1659 l_mmli_rec := p_mmli_rec;
1660 l_mmli_rec.transaction_interface_id := p_trans_inter_id;
1661 l_transaction_type_id := p_transaction_type_id;
1662 l_inventory_item_id := p_inventory_item_id ;
1663 -- Bug 6925025
1664 l_subinventory_code := p_subinventory_code;
1665 l_locator_id := p_locator_id;
1666
1667 IF l_transaction_type_id IN
1668 (gme_common_pvt.g_ing_return
1669 ,gme_common_pvt.g_prod_completion
1670 ,gme_common_pvt.g_byprod_completion) THEN
1671 l_mmli_rec.transaction_quantity :=
1672 ABS(l_mmli_rec.transaction_quantity);
1673 l_mmli_rec.secondary_transaction_quantity :=
1674 ABS(l_mmli_rec.secondary_transaction_quantity);
1675 ELSE
1676 l_mmli_rec.transaction_quantity :=
1677 (-1) * ABS(l_mmli_rec.transaction_quantity);
1678 l_mmli_rec.secondary_transaction_quantity :=
1679 (-1) * ABS(l_mmli_rec.secondary_transaction_quantity);
1680 END IF;
1681
1682 -- Bug 6925025
1683 -- pass subinventory_code and locator id.
1684 IF (inv_material_status_grp.is_status_applicable
1685 (p_wms_installed => NULL
1686 ,p_trx_status_enabled => NULL
1687 ,p_trx_type_id => l_transaction_type_id
1688 ,p_lot_status_enabled => NULL
1689 ,p_serial_status_enabled => NULL
1690 ,p_organization_id => gme_common_pvt.g_organization_id
1691 ,p_inventory_item_id => l_inventory_item_id
1692 ,p_sub_code => p_subinventory_code
1693 ,p_locator_id => p_locator_id
1694 ,p_lot_number => l_mmli_rec.lot_number
1695 ,p_serial_number => NULL
1696 ,p_object_type => 'A') = 'N') THEN
1697 IF (g_debug <= gme_debug.g_log_statement) THEN
1698 gme_debug.put_line ( g_pkg_name
1699 || '.'
1700 || l_api_name
1701 || ':'
1702 || 'material status check for lot NOT valid for lot '
1703 || l_mmli_rec.lot_number);
1704 END IF;
1705 RAISE material_status_err;
1706 END IF; /* inv_material_status_grp.is_status_applicable */
1707
1708 IF (g_debug <= gme_debug.g_log_statement) THEN
1709 gme_debug.put_line ( g_pkg_name
1710 || '.'
1711 || l_api_name
1712 || ':'
1713 || 'Material Status is VALID for lot '
1714 || l_mmli_rec.lot_number);
1715 gme_debug.put_line ( g_pkg_name
1716 || '.'
1717 || l_api_name
1718 || ':'
1719 || 'lot_qty is '
1720 || l_mmli_rec.transaction_quantity);
1721 END IF;
1722 INSERT INTO mtl_transaction_lots_interface
1723 (transaction_interface_id, last_update_date
1724 ,last_updated_by, last_update_login
1725 ,creation_date, created_by
1726 ,lot_number, transaction_quantity
1727 ,primary_quantity
1728 ,secondary_transaction_quantity)
1729 VALUES ( p_trans_inter_id --transaction_interface_id
1730 ,gme_common_pvt.g_timestamp --last_update_date
1731 ,gme_common_pvt.g_user_ident --last_updated_by
1732 ,gme_common_pvt.g_user_ident --last_update_login
1733 ,gme_common_pvt.g_timestamp --creation_date
1734 ,gme_common_pvt.g_user_ident --created_by
1735 ,l_mmli_rec.lot_number --lot_number
1736 ,l_mmli_rec.transaction_quantity --lot_quantity
1740 x_mmli_rec := l_mmli_rec;
1737 ,l_mmli_rec.primary_quantity
1738 ,l_mmli_rec.secondary_transaction_quantity);
1739
1741
1742 IF (g_debug <= gme_debug.g_log_statement) THEN
1743 gme_debug.put_line ( g_pkg_name
1744 || '.'
1745 || l_api_name
1746 || ':'
1747 || 'Exiting with '
1748 || x_return_status);
1749 END IF;
1750 EXCEPTION
1751 WHEN material_status_err THEN
1752 SELECT substr(concatenated_segments,1,100)
1753 INTO l_item
1754 FROM mtl_system_items_kfv
1755 WHERE organization_id = gme_common_pvt.g_organization_id
1756 AND inventory_item_id = l_inventory_item_id;
1757 SELECT transaction_type_name
1758 INTO l_type
1759 FROM mtl_transaction_types
1760 WHERE transaction_type_id = p_transaction_type_id;
1761 gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_LOT'
1762 ,'TRANSTYPE',l_type,'ITEM',l_item
1763 ,'LOT',l_mmli_rec.lot_number);
1764 gme_debug.put_line ( g_pkg_name
1765 || '.'
1766 || l_api_name
1767 || ':'
1768 || 'material status invalid for item, subinventory, locator etc');
1769 x_return_status := 'T';
1770 WHEN fnd_api.g_exc_error THEN
1771 gme_debug.put_line ( g_pkg_name
1772 || '.'
1773 || l_api_name
1774 || ':'
1775 || 'WHEN exe'
1776 || SQLERRM);
1777 x_return_status := fnd_api.g_ret_sts_error;
1778 WHEN fnd_api.g_exc_unexpected_error THEN
1779 gme_debug.put_line ( g_pkg_name
1780 || '.'
1781 || l_api_name
1782 || ':'
1783 || 'unexp'
1784 || SQLERRM);
1785 x_return_status := fnd_api.g_ret_sts_unexp_error;
1786 WHEN OTHERS THEN
1787 x_return_status := fnd_api.g_ret_sts_unexp_error;
1788 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1789
1790 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1791 gme_debug.put_line ( g_pkg_name
1792 || '.'
1793 || l_api_name
1794 || ':'
1795 || 'WHEN OTHERS:'
1796 || SQLERRM);
1797 END IF;
1798 END BUILD_TXN_INTER_LOT;
1799
1800 /* +==========================================================================+
1801 | PROCEDURE NAME
1802 | get_transactions
1803 |
1804 | USAGE
1805 | Gets all transactions from mmt based on transaction_id passed.
1806 |
1807 | ARGUMENTS
1808 | p_transaction_id -- transaction_id from mmt for fetch
1809 |
1810 | RETURNS
1811 |
1812 | returns via x_status OUT parameters
1813 | x_mmt_rec -- mtl_transaction_interface rowtype
1814 | x_mmln_tbl -- table of mtl_trans_lots_number_tbl
1815 | HISTORY
1816 | Created 02-Feb-05 Pawan Kumar
1817 | 09-Feb-06 Namit S. Bug4917213 Changed query for perf reasons.
1818 |
1819 +==========================================================================+ */
1820 PROCEDURE get_transactions (
1821 p_transaction_id IN NUMBER
1822 ,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
1823 ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
1824 ,x_return_status OUT NOCOPY VARCHAR2)
1825 IS
1826
1827 -- Namit S. Bug4917213. Changed query to add hint to improve sharable memory.
1828 -- Hint was provided by the apps perf team.
1829 -- Pawan Kumar bug 5483071 added order by clause
1830 -- donot change the order by clause it is done so that we reverse the outbound transaction first
1831
1832 CURSOR cur_get_transaction (v_transaction_id NUMBER, v_reversal_type NUMBER)
1833 IS
1834 SELECT *
1835 FROM mtl_material_transactions mmt
1836 WHERE transaction_id = v_transaction_id
1837 AND NOT EXISTS ( SELECT /*+ no_unnest */
1838 transaction_id1
1839 FROM gme_transaction_pairs
1840 WHERE transaction_id1 = mmt.transaction_id
1841 AND pair_type = v_reversal_type)
1842 ORDER BY mmt.transaction_quantity;
1843
1844 CURSOR cur_get_lot_transaction (v_transaction_id NUMBER)
1845 IS
1846 SELECT *
1847 FROM mtl_transaction_lot_numbers
1848 WHERE transaction_id = v_transaction_id;
1849
1850 l_api_name CONSTANT VARCHAR2 (30) := 'GET_TRANSACTIONS';
1851 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1852 l_transaction_id NUMBER;
1853 no_transaction_found EXCEPTION;
1854
1855 BEGIN
1856 x_return_status := fnd_api.g_ret_sts_success;
1857
1858 IF (g_debug <= gme_debug.g_log_statement) THEN
1859 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1863 IF p_transaction_id IS NULL THEN
1860 || 'Entering with transaction '||p_transaction_id);
1861 END IF;
1862
1864 gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
1865
1866 IF (g_debug <= gme_debug.g_log_statement) THEN
1867 gme_debug.put_line ( g_pkg_name
1868 || '.'
1869 || l_api_name
1870 || ':'
1871 || 'TRANSACTION ID NEEDED FOR RETRIEVAL');
1872 END IF;
1873 END IF;
1874
1875 l_transaction_id := p_transaction_id;
1876 -- Namit S. Bug4917213.
1877 OPEN cur_get_transaction (l_transaction_id, gme_common_pvt.g_pairs_reversal_type);
1878 FETCH cur_get_transaction
1879 INTO x_mmt_rec;
1880 IF cur_get_transaction%FOUND THEN
1881 IF (g_debug <= gme_debug.g_log_statement) THEN
1882 gme_debug.put_line ( g_pkg_name
1883 || '.'
1884 || l_api_name
1885 || ':'
1886 || 'TRANSACTIONS found for '
1887 || l_transaction_id);
1888 END IF;
1889 get_lot_trans (p_transaction_id => l_transaction_id
1890 ,x_mmln_tbl => x_mmln_tbl
1891 ,x_return_status => l_return_status);
1892
1893 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1894 IF (g_debug <= gme_debug.g_log_statement) THEN
1895 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1896 || 'error from get lot trans');
1897 END IF;
1898 RAISE fnd_api.g_exc_error;
1899 END IF;
1900 ELSE /* IF cur_get_transaction%FOUND THEN */
1901 CLOSE cur_get_transaction;
1902 gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
1903 RAISE fnd_api.g_exc_error;
1904 END IF;
1905 CLOSE cur_get_transaction;
1906
1907 IF (g_debug <= gme_debug.g_log_statement) THEN
1908 gme_debug.put_line ( g_pkg_name
1909 || '.'
1910 || l_api_name
1911 || ':'
1912 || 'TRANSACTION '
1913 || x_mmt_rec.transaction_id);
1914 END IF;
1915
1916 IF (g_debug <= gme_debug.g_log_statement) THEN
1917 gme_debug.put_line ( g_pkg_name
1918 || '.'
1919 || l_api_name
1920 || ':'
1921 || 'Exiting with '
1922 || x_return_status);
1923 END IF;
1924 EXCEPTION
1925 WHEN fnd_api.g_exc_error THEN
1926 x_return_status := fnd_api.g_ret_sts_error;
1927 WHEN fnd_api.g_exc_unexpected_error THEN
1928 x_return_status := fnd_api.g_ret_sts_unexp_error;
1929 WHEN OTHERS THEN
1930 x_return_status := fnd_api.g_ret_sts_unexp_error;
1931 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1932
1933 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
1934 gme_debug.put_line ( g_pkg_name
1935 || '.'
1936 || l_api_name
1937 || ':'
1938 || 'WHEN OTHERS:'
1939 || SQLERRM);
1940 END IF;
1941 END get_transactions;
1942
1943 /* +==========================================================================+
1944 | PROCEDURE NAME
1945 | construct_mmti
1946 |
1947 | USAGE
1948 | Construct interface table record based on mmt passed to it.
1949 |
1950 | ARGUMENTS
1951 | p_mmt_rec -- mtl_material_transaction rowtype
1952 | p_mmln_tbl -- table of mtl_trans_lots_num_tbl as input
1953 |
1954 | RETURNS
1955 | returns via x_status OUT parameters
1956 | x_mmti_rec mtl_transactions_interface rowtype
1957 | x_mmli_tbl table of mtl_trans_lots_inter_tbl
1958 | HISTORY
1959 | Created 02-Feb-05 Pawan Kumar
1960 |
1961 +==========================================================================+ */
1962 PROCEDURE construct_mmti (
1963 p_mmt_rec IN mtl_material_transactions%ROWTYPE
1964 ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
1965 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1966 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1967 ,x_return_status OUT NOCOPY VARCHAR2)
1968 IS
1969 l_mmt_rec mtl_material_transactions%ROWTYPE;
1970 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1971 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1972 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1973 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTRUCT_MMTI';
1974 BEGIN
1975 -- Initially let us assign the return status to success
1976 x_return_status := fnd_api.g_ret_sts_success;
1977
1978 IF (g_debug <= gme_debug.g_log_statement) THEN
1979 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1980 || 'Entering');
1984 l_mmln_tbl := p_mmln_tbl;
1981 END IF;
1982
1983 l_mmt_rec := p_mmt_rec;
1985 -- x_mmti_rec.transaction_mode := l_mmt_rec.transaction_mode;
1986 x_mmti_rec.source_code := l_mmt_rec.source_code;
1987 -- x_mmti_rec.source_header_id := l_mmt_rec.source_header_id ;
1988 x_mmti_rec.source_line_id := NVL (l_mmt_rec.source_line_id, -99);
1989 x_mmti_rec.transaction_source_id := l_mmt_rec.transaction_source_id;
1990 x_mmti_rec.trx_source_line_id := l_mmt_rec.trx_source_line_id;
1991 x_mmti_rec.last_updated_by := l_mmt_rec.last_updated_by;
1992 x_mmti_rec.last_update_login := l_mmt_rec.last_update_login;
1993 x_mmti_rec.last_update_date := l_mmt_rec.last_update_date;
1994 x_mmti_rec.creation_date := l_mmt_rec.creation_date;
1995 x_mmti_rec.created_by := l_mmt_rec.created_by;
1996 x_mmti_rec.inventory_item_id := l_mmt_rec.inventory_item_id;
1997 x_mmti_rec.revision := l_mmt_rec.revision;
1998 x_mmti_rec.organization_id := l_mmt_rec.organization_id;
1999 x_mmti_rec.acct_period_id := l_mmt_rec.acct_period_id;
2000 x_mmti_rec.transaction_date := l_mmt_rec.transaction_date;
2001 x_mmti_rec.transaction_type_id := l_mmt_rec.transaction_type_id;
2002 x_mmti_rec.transaction_action_id := l_mmt_rec.transaction_action_id;
2003 x_mmti_rec.transaction_quantity := l_mmt_rec.transaction_quantity;
2004 x_mmti_rec.primary_quantity := l_mmt_rec.primary_quantity;
2005 x_mmti_rec.secondary_transaction_quantity := l_mmt_rec.secondary_transaction_quantity;
2006 x_mmti_rec.secondary_uom_code := l_mmt_rec.secondary_uom_code ;
2007 x_mmti_rec.distribution_account_id := l_mmt_rec.distribution_account_id;
2008 x_mmti_rec.transaction_uom := l_mmt_rec.transaction_uom;
2009 x_mmti_rec.subinventory_code := l_mmt_rec.subinventory_code;
2010 x_mmti_rec.locator_id := l_mmt_rec.locator_id;
2011 x_mmti_rec.transaction_source_type_id := l_mmt_rec.transaction_source_type_id;
2012 x_mmti_rec.transaction_source_name := l_mmt_rec.transaction_source_name;
2013 x_mmti_rec.transaction_reference := l_mmt_rec.transaction_reference;
2014 x_mmti_rec.reason_id := l_mmt_rec.reason_id;
2015 --x_mmti_rec.reservation_quantity := l_mmt_rec.reservation_quantity;
2016 -- x_mmti_rec.transaction_sequence_id := l_mmt_rec.transaction_sequence_id;
2017 --x_mmti_rec.transaction_reference := l_mmt_rec.transaction_reference;
2018
2019 -- construct mtl_transaction_lots_interface
2020 IF (l_mmln_tbl.COUNT > 0) THEN
2021 FOR i IN 1 .. l_mmln_tbl.COUNT LOOP
2022 x_mmli_tbl (i).last_update_date :=
2023 l_mmln_tbl (i).last_update_date;
2024 x_mmli_tbl (i).last_updated_by := l_mmln_tbl (i).last_updated_by;
2025 x_mmli_tbl (i).creation_date := l_mmln_tbl (i).creation_date;
2026 x_mmli_tbl (i).created_by := l_mmln_tbl (i).created_by;
2027 x_mmli_tbl (i).lot_number := l_mmln_tbl (i).lot_number;
2028 x_mmli_tbl (i).transaction_quantity :=
2029 l_mmln_tbl (i).transaction_quantity;
2030 x_mmli_tbl (i).primary_quantity :=
2031 l_mmln_tbl (i).primary_quantity;
2032 x_mmli_tbl (i).secondary_transaction_quantity :=
2033 l_mmln_tbl (i).secondary_transaction_quantity;
2034 END LOOP;
2035 END IF; --l_mmln_tbl.count
2036
2037 IF (g_debug <= gme_debug.g_log_statement) THEN
2038 gme_debug.put_line ( g_pkg_name
2039 || '.'
2040 || l_api_name
2041 || ':'
2042 || 'Exiting with '
2043 || x_return_status);
2044 END IF;
2045 EXCEPTION
2046 WHEN fnd_api.g_exc_error THEN
2047 x_return_status := fnd_api.g_ret_sts_error;
2048 WHEN fnd_api.g_exc_unexpected_error THEN
2049 x_return_status := fnd_api.g_ret_sts_unexp_error;
2050 WHEN OTHERS THEN
2051 x_return_status := fnd_api.g_ret_sts_unexp_error;
2052 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2053
2054 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2055 gme_debug.put_line ( g_pkg_name
2056 || '.'
2057 || l_api_name
2058 || ':'
2059 || 'WHEN OTHERS:'
2060 || SQLERRM);
2061 END IF;
2062 END construct_mmti;
2063
2064 /* +==========================================================================+
2065 | PROCEDURE NAME
2066 | get_mat_trans
2067 |
2068 | USAGE
2069 | Gets all transactions from mmt based on material_detail_id and batch_id passed.
2070 |
2071 | ARGUMENTS
2072 | p_mat_det_id -- material_detail_id passed of material
2073 | p_batch_id -- batch_id to which the material belongs.
2074 |
2075 | RETURNS
2076 | returns via x_status OUT parameters
2080 | 09-Feb-06 Namit S. Bug4917213 Changed query for perf reasons.
2077 | x_mmt_tbl- gives back all transactions of the material
2078 | HISTORY
2079 | Created 02-Feb-05 Pawan Kumar
2081 |
2082 +==========================================================================+ */
2083 PROCEDURE get_mat_trans (
2084 p_mat_det_id IN NUMBER
2085 ,p_batch_id IN NUMBER
2086 ,x_mmt_tbl OUT NOCOPY gme_common_pvt.mtl_mat_tran_tbl
2087 ,x_return_status OUT NOCOPY VARCHAR2)
2088 IS
2089
2090 -- Namit S. Bug4917213. Changed query to add hint to improve sharable memory.
2091 -- Hint was provided by the apps perf team.
2092 -- Pawan Kumar bug 5483071 added order by clause
2093 -- donot change the order by clause it is done so that we reverse the outbound transaction first
2094
2095 CURSOR cur_get_trans (v_mat_det_id NUMBER, v_batch_id NUMBER,
2096 v_txn_source_type NUMBER, v_pairs_reversal_type NUMBER)
2097 IS
2098 SELECT *
2099 FROM mtl_material_transactions mmt
2100 WHERE trx_source_line_id = v_mat_det_id
2101 AND transaction_source_id = v_batch_id
2102 AND transaction_source_type_id = v_txn_source_type
2103 AND NOT EXISTS ( SELECT /*+ no_unnest */
2104 transaction_id1
2105 FROM gme_transaction_pairs
2106 WHERE transaction_id1 = mmt.transaction_id
2107 AND pair_type = v_pairs_reversal_type)
2108 ORDER BY mmt.transaction_quantity;
2109
2110 l_api_name CONSTANT VARCHAR2 (30) := 'GET_MAT_TRANS';
2111 BEGIN
2112 -- Initially let us assign the return status to success
2113 x_return_status := fnd_api.g_ret_sts_success;
2114
2115 IF (g_debug <= gme_debug.g_log_statement) THEN
2116 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2117 || 'Entering with mat/batch '||p_mat_det_id||'/'||p_batch_id);
2118 END IF;
2119
2120 IF p_mat_det_id IS NOT NULL AND p_batch_id IS NOT NULL THEN
2121 -- Namit S. Bug4917213.
2122 OPEN cur_get_trans (p_mat_det_id, p_batch_id,
2123 gme_common_pvt.g_txn_source_type, gme_common_pvt.g_pairs_reversal_type);
2124 FETCH cur_get_trans
2125 BULK COLLECT INTO x_mmt_tbl;
2126 CLOSE cur_get_trans;
2127 END IF;
2128 IF (g_debug <= gme_debug.g_log_statement) THEN
2129 gme_debug.put_line ( g_pkg_name
2130 || '.'
2131 || l_api_name
2132 || ':'
2133 || 'Exiting with '
2134 || x_return_status);
2135 END IF;
2136 EXCEPTION
2137 WHEN fnd_api.g_exc_error THEN
2138 x_return_status := fnd_api.g_ret_sts_error;
2139 WHEN fnd_api.g_exc_unexpected_error THEN
2140 x_return_status := fnd_api.g_ret_sts_unexp_error;
2141 WHEN OTHERS THEN
2142 x_return_status := fnd_api.g_ret_sts_unexp_error;
2143 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2144
2145 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2146 gme_debug.put_line ( g_pkg_name
2147 || '.'
2148 || l_api_name
2149 || ':'
2150 || 'WHEN OTHERS:'
2151 || SQLERRM);
2152 END IF;
2153 END get_mat_trans;
2154
2155 /* +==========================================================================+
2156 | PROCEDURE NAME
2157 | get_lot_trans
2158 |
2159 | USAGE
2160 | Gets all lot transactions from mmln for a given transaction_id.
2161 |
2162 | ARGUMENTS
2163 | p_transaction_id -- transaction_id for which all lot info is required.
2164 |
2165 | RETURNS
2166 | returns via x_status OUT parameters
2167 | x_mmln_tbl- all lot info for a given transaction_id.
2168 | HISTORY
2169 | Created 02-Feb-05 Pawan Kumar
2170 |
2171 +==========================================================================+ */
2172 PROCEDURE get_lot_trans (
2173 p_transaction_id IN NUMBER
2174 ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
2175 ,x_return_status OUT NOCOPY VARCHAR2)
2176 IS
2177 CURSOR cur_get_lot_trans (v_transaction_id NUMBER)
2178 IS
2179 SELECT *
2180 FROM mtl_transaction_lot_numbers
2181 WHERE transaction_id = v_transaction_id;
2182
2183 l_api_name CONSTANT VARCHAR2 (30) := 'GET_LOT_TRANS';
2184 BEGIN
2185 -- Initially let us assign the return status to success
2186 x_return_status := fnd_api.g_ret_sts_success;
2187
2188 IF (g_debug <= gme_debug.g_log_statement) THEN
2189 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2190 || 'Entering with transaction id '||p_transaction_id);
2191
2192 END IF;
2193
2194 IF p_transaction_id IS NOT NULL THEN
2195 OPEN cur_get_lot_trans (p_transaction_id);
2196 FETCH cur_get_lot_trans
2197 BULK COLLECT INTO x_mmln_tbl;
2198 CLOSE cur_get_lot_trans;
2199 END IF;
2200
2204 || l_api_name
2201 IF (g_debug <= gme_debug.g_log_statement) THEN
2202 gme_debug.put_line ( g_pkg_name
2203 || '.'
2205 || ':'
2206 || 'lot count '
2207 || x_mmln_tbl.COUNT);
2208 gme_debug.put_line ( g_pkg_name
2209 || '.'
2210 || l_api_name
2211 || ':'
2212 || 'Exiting with '
2213 || x_return_status);
2214 END IF;
2215 EXCEPTION
2216 WHEN fnd_api.g_exc_error THEN
2217 x_return_status := fnd_api.g_ret_sts_error;
2218 WHEN fnd_api.g_exc_unexpected_error THEN
2219 x_return_status := fnd_api.g_ret_sts_unexp_error;
2220 WHEN OTHERS THEN
2221 x_return_status := fnd_api.g_ret_sts_unexp_error;
2222
2223 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2224 gme_debug.put_line ( g_pkg_name
2225 || '.'
2226 || l_api_name
2227 || ':'
2228 || 'WHEN OTHERS:'
2229 || SQLERRM);
2230 END IF;
2231
2232 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2233 END get_lot_trans;
2234
2235 /* +==========================================================================+
2236 | PROCEDURE NAME
2237 | GME_PRE_PROCESS
2238 |
2239 | USAGE
2240 | Gets all pre-process validations based on header_id
2241 |
2242 | ARGUMENTS
2243 | p_transaction_hdr_id
2244 |
2245 |
2246 | RETURNS
2247 | returns via x_status OUT parameters
2248 |
2249 | HISTORY
2250 | Created 02-Feb-05 Pawan Kumar
2251 |
2252 +==========================================================================+ */
2253 PROCEDURE gme_pre_process (
2254 p_transaction_hdr_id IN NUMBER
2255 ,x_return_status OUT NOCOPY VARCHAR2)
2256 IS
2257 CURSOR cur_get_trans (v_hdr_id NUMBER)
2258 IS
2259 SELECT transaction_interface_id
2260 FROM mtl_transactions_interface
2261 WHERE transaction_header_id = v_hdr_id
2262 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
2263 AND wip_entity_type = gme_common_pvt.g_wip_entity_type_batch;
2264
2265 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
2266 l_number_tab gme_common_pvt.number_tab;
2267 no_transaction_found EXCEPTION;
2268 l_api_name CONSTANT VARCHAR2 (30) := 'GME_PRE_PROCESS';
2269 BEGIN
2270 -- Initially let us assign the return status to success
2271 x_return_status := fnd_api.g_ret_sts_success;
2272
2273 IF (g_debug <= gme_debug.g_log_statement) THEN
2274 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2275 || 'Entering with p_transaction_hdr_id '||p_transaction_hdr_id);
2276 END IF;
2277
2278 IF p_transaction_hdr_id IS NOT NULL THEN
2279 OPEN cur_get_trans (p_transaction_hdr_id);
2280 IF cur_get_trans%NOTFOUND THEN
2281 CLOSE cur_get_trans;
2282 gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
2283 RAISE fnd_api.g_exc_error;
2284 END IF;
2285 FETCH cur_get_trans
2286 BULK COLLECT INTO l_number_tab;
2287 CLOSE cur_get_trans;
2288 END IF;
2289
2290 FOR i IN 1 .. l_number_tab.COUNT LOOP
2291 IF (g_debug <= gme_debug.g_log_statement) THEN
2292 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2293 || 'calling pre_process_val with transaction_id '||l_number_tab (i));
2294 END IF;
2295 pre_process_val (p_transaction_interface_id => l_number_tab (i)
2296 ,x_return_status => l_return_status);
2297
2298 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2299 RAISE fnd_api.g_exc_error;
2300 END IF;
2301 END LOOP;
2302
2303 IF (g_debug <= gme_debug.g_log_statement) THEN
2304 gme_debug.put_line ( g_pkg_name
2305 || '.'
2306 || l_api_name
2307 || ':'
2308 || 'Exiting with '
2309 || x_return_status);
2310 END IF;
2311 EXCEPTION
2312 /* update mtl_transactions_interface
2313 set error_code = 'wip_mtlInterfaceProc_pub.processInterface()',
2314 error_explanation = l_errMessage,
2315 process_flag = wip_constants.mti_error
2316 where transaction_header_id = p_txnHdrID; */
2317 WHEN fnd_api.g_exc_error THEN
2318 x_return_status := fnd_api.g_ret_sts_error;
2319 WHEN fnd_api.g_exc_unexpected_error THEN
2320 x_return_status := fnd_api.g_ret_sts_unexp_error;
2321 WHEN OTHERS THEN
2322 x_return_status := fnd_api.g_ret_sts_unexp_error;
2323
2324 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2325 gme_debug.put_line ( g_pkg_name
2326 || '.'
2327 || l_api_name
2328 || ':'
2332
2329 || 'WHEN OTHERS:'
2330 || SQLERRM);
2331 END IF;
2333 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2334 END gme_pre_process;
2335
2336 /* +==========================================================================+
2337 | PROCEDURE NAME
2338 | get_returnable_qty
2339 |
2340 | USAGE
2341 | Gets net quantity that can be returned from mmt based on the details passed
2342 |
2343 | ARGUMENTS
2344 | p_mmti_rec -- mtl_transaction_interface rowtype
2345 | p_lot_number - Lot number
2346 | p_lot_control - 1 for plain 2 for lot control
2347 | RETURNS
2348 | x_return_status S for success, U for unexpected
2349 | x_available_qty Quantity that can be returned.
2350 | HISTORY
2351 | Created 20-Sep-05 Shrikant Nene
2352 |
2353 +==========================================================================+ */
2354
2355 PROCEDURE get_returnable_qty (
2356 p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
2357 p_lot_number IN VARCHAR2,
2358 p_lot_control IN NUMBER,
2359 x_available_qty OUT NOCOPY NUMBER,
2360 x_return_status OUT NOCOPY VARCHAR2
2361 ) IS
2362
2363 CURSOR cur_plain_item (
2364 v_item_id IN NUMBER,
2365 v_organization_id IN NUMBER,
2366 v_revision IN VARCHAR2,
2367 v_batch_id IN NUMBER,
2368 v_mat_det_id IN NUMBER,
2369 v_trans_uom IN VARCHAR2
2370 ) IS
2371 SELECT SUM (DECODE (v_trans_uom,
2372 t.transaction_uom, transaction_quantity,
2373 inv_convert.inv_um_convert (d.inventory_item_id,
2374 gme_common_pvt.g_precision,
2375 t.transaction_quantity,
2376 t.transaction_uom,
2377 v_trans_uom,
2378 NULL,
2379 NULL
2380 )
2381 )
2382 )
2383 FROM mtl_material_transactions t, gme_material_details d
2384 WHERE t.organization_id = v_organization_id
2385 AND t.inventory_item_id = v_item_id
2386 AND t.transaction_source_id = v_batch_id
2387 AND t.trx_source_line_id = v_mat_det_id
2388 AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
2389 AND t.trx_source_line_id = d.material_detail_id
2390 AND (t.revision IS NULL OR t.revision = v_revision)
2391 GROUP BY t.revision, t.inventory_item_id;
2392
2393 CURSOR cur_lot_qty (
2394 v_lot_number IN VARCHAR2,
2395 v_item_id IN NUMBER,
2396 v_organization_id IN NUMBER,
2397 v_revision IN VARCHAR2,
2398 v_batch_id IN NUMBER,
2399 v_mat_det_id IN NUMBER,
2400 v_trans_uom IN VARCHAR2
2401 ) IS
2402 SELECT lot_number,
2403 SUM (DECODE (v_trans_uom,
2404 m.transaction_uom, m.transaction_quantity,
2405 inv_convert.inv_um_convert (d.inventory_item_id,
2406 gme_common_pvt.g_precision,
2407 m.transaction_quantity,
2408 m.transaction_uom,
2409 v_trans_uom,
2410 NULL,
2411 NULL
2412 )
2413 )
2414 )
2415 FROM mtl_material_transactions m, mtl_transaction_lot_numbers l, gme_material_details d
2416 WHERE l.transaction_id = m.transaction_id
2417 AND m.trx_source_line_id = d.material_detail_id
2418 AND l.lot_number = v_lot_number
2419 AND l.inventory_item_id = v_item_id
2420 AND l.organization_id = v_organization_id
2421 AND l.transaction_source_id = v_batch_id
2422 AND m.trx_source_line_id = v_mat_det_id
2423 AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
2424 -- Pawan Kumar added for checking of revision bug 5451006- 5493370
2425 AND (m.revision IS NULL OR m.revision = v_revision)
2426 GROUP BY l.lot_number, l.inventory_item_id;
2427
2428 l_lot_no VARCHAR2 (80);
2429 l_api_name CONSTANT VARCHAR2 (30) := 'GET_RETURNABLE_QTY';
2430
2431 BEGIN
2432 -- Initially let us assign the return status to success
2433 x_return_status := fnd_api.g_ret_sts_success;
2434
2435 IF (g_debug <= gme_debug.g_log_statement) THEN
2436 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
2437 END IF;
2438 IF (g_debug <= gme_debug.g_log_statement) THEN
2439 gme_debug.put_line ( g_pkg_name
2440 || '.'
2444 || p_lot_number
2441 || l_api_name
2442 || ':'
2443 || 'p_lot_number: '
2445 || ' p_lot_control code: '
2446 || p_lot_control);
2447 END IF;
2448 IF p_lot_control = 1 THEN
2449 /* Plain Item */
2450 OPEN cur_plain_item (p_mmti_rec.inventory_item_id
2451 ,p_mmti_rec.organization_id
2452 ,p_mmti_rec.revision
2453 ,p_mmti_rec.transaction_source_id
2454 ,p_mmti_rec.trx_source_line_id
2455 ,p_mmti_rec.transaction_uom);
2456
2457 FETCH cur_plain_item
2458 INTO x_available_qty;
2459
2460 IF cur_plain_item%NOTFOUND THEN
2461 x_available_qty := 0;
2462 END IF;
2463
2464 CLOSE cur_plain_item;
2465 ELSE /* Lot control Item */
2466 OPEN cur_lot_qty (p_lot_number
2467 ,p_mmti_rec.inventory_item_id
2468 ,p_mmti_rec.organization_id
2469 ,p_mmti_rec.revision
2470 ,p_mmti_rec.transaction_source_id
2471 ,p_mmti_rec.trx_source_line_id
2472 ,p_mmti_rec.transaction_uom);
2473
2474 FETCH cur_lot_qty
2475 INTO l_lot_no, x_available_qty;
2476
2477 IF cur_lot_qty%NOTFOUND THEN
2478 x_available_qty := 0;
2479 END IF;
2480
2481 CLOSE cur_lot_qty;
2482 END IF;
2483 IF (g_debug <= gme_debug.g_log_statement) THEN
2484 gme_debug.put_line ( g_pkg_name
2485 || '.'
2486 || l_api_name
2487 || ':'
2488 || 'Exiting with return status '
2489 || x_return_status
2490 || ' Available Qty '
2491 || x_available_qty);
2492 END IF;
2493 EXCEPTION
2494 WHEN OTHERS THEN
2495 x_return_status := fnd_api.g_ret_sts_unexp_error;
2496 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2497
2498 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2499 gme_debug.put_line ( g_pkg_name
2500 || '.'
2501 || l_api_name
2502 || ':'
2503 || 'WHEN OTHERS:'
2504 || SQLERRM);
2505 END IF;
2506 END get_returnable_qty;
2507
2508 /* +==========================================================================+
2509 | PROCEDURE NAME
2510 | PRE_PROCESS_VAL
2511 |
2512 | USAGE
2513 | Gets all transactions from mmt based on material_detail_id and batch_id passed.
2514 |
2515 | ARGUMENTS
2516 | p_mmti_rec -- mtl_transaction_interface rowtype
2517 |
2518 |
2519 | RETURNS
2520 | returns via x_status OUT parameters
2521 |
2522 | HISTORY
2523 | Created 02-Feb-05 Pawan Kumar
2524 |
2525 +==========================================================================+ */
2526 PROCEDURE pre_process_val (
2527 p_transaction_interface_id IN NUMBER
2528 ,x_return_status OUT NOCOPY VARCHAR2)
2529 IS
2530 CURSOR cur_trans_detail (v_trans_inter_id NUMBER)
2531 IS
2532 SELECT *
2533 FROM mtl_transactions_interface
2534 WHERE transaction_interface_id = v_trans_inter_id;
2535
2536
2537 CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER)
2538 IS
2539 SELECT *
2540 FROM mtl_system_items_b
2541 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
2542 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER)
2543 IS
2544 SELECT step_status
2545 FROM gme_batch_steps s, gme_batch_step_items i
2546 WHERE s.batchstep_id = i.batchstep_id
2547 AND i.material_detail_id = v_matl_dtl_id;
2548
2549 CURSOR cur_lot_input (v_trans_inter_id NUMBER)
2550 IS
2551 SELECT lot_number, SUM (transaction_quantity) l_mtli_lot_qty
2552 FROM mtl_transaction_lots_interface
2553 WHERE transaction_interface_id = v_trans_inter_id
2554 GROUP BY lot_number;
2555
2556 l_mmti_rec mtl_transactions_interface%ROWTYPE;
2557 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
2558 l_mat_dtl_rec gme_material_details%ROWTYPE;
2559 l_batch_hdr_rec gme_batch_header%ROWTYPE;
2560 l_item_rec mtl_system_items_b%ROWTYPE;
2561 l_available_qty NUMBER;
2562 l_step_status NUMBER;
2563 l_rel_type NUMBER;
2564 l_return_status VARCHAR2(1);
2565 item_not_found EXCEPTION;
2566 not_valid_trans EXCEPTION;
2567 lot_val_err EXCEPTION;
2568 l_api_name CONSTANT VARCHAR2 (30) := 'PRE_PROCESS_VAL';
2572
2569 BEGIN
2570 -- Initially let us assign the return status to success
2571 x_return_status := fnd_api.g_ret_sts_success;
2573 IF (g_debug <= gme_debug.g_log_statement) THEN
2574 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2575 || 'Entering');
2576 END IF;
2577
2578 OPEN cur_trans_detail (p_transaction_interface_id);
2579
2580 FETCH cur_trans_detail
2581 INTO l_mmti_rec;
2582
2583 CLOSE cur_trans_detail;
2584
2585 IF (g_debug <= gme_debug.g_log_statement) THEN
2586 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_header_id: '||l_mmti_rec.transaction_header_id);
2587 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.subinventory_code: '||l_mmti_rec.subinventory_code);
2588 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_uom: '||l_mmti_rec.transaction_uom);
2589 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.inventory_item_id: '||l_mmti_rec.inventory_item_id);
2590 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.trx_source_line_id: '||l_mmti_rec.trx_source_line_id);
2591 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.revision: '||l_mmti_rec.revision);
2592 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_source_id: '||l_mmti_rec.transaction_source_id);
2593 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.locator_id: '||l_mmti_rec.locator_id);
2594 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_type_id: '||l_mmti_rec.transaction_type_id);
2595 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_transaction_interface_id: '||p_transaction_interface_id);
2596 END IF;
2597
2598 IF l_mmti_rec.transaction_source_id IS NOT NULL THEN
2599 l_batch_hdr_rec.batch_id := l_mmti_rec.transaction_source_id;
2600
2601 IF NOT gme_batch_header_dbl.fetch_row
2602 (p_batch_header => l_batch_hdr_rec
2603 ,x_batch_header => l_batch_hdr_rec) THEN
2604 RAISE fnd_api.g_exc_error;
2605 END IF;-- batch fetch
2606 ELSE
2607 RAISE fnd_api.g_exc_error;
2608 END IF; -- transaction_source_id IS NOT NULL
2609
2610 IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
2611 IF l_mmti_rec.trx_source_line_id IS NOT NULL THEN
2612 l_mat_dtl_rec.material_detail_id := l_mmti_rec.trx_source_line_id;
2613
2614 IF NOT gme_material_details_dbl.fetch_row
2615 (p_material_detail => l_mat_dtl_rec
2616 ,x_material_detail => l_mat_dtl_rec) THEN
2617 RAISE fnd_api.g_exc_error;
2618 END IF; -- material fetch
2619
2620 ELSE
2621 RAISE fnd_api.g_exc_error;
2622 END IF; -- trx_source_line_id IS NOT NULL
2623 IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
2624 IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
2625 gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
2626 RAISE fnd_api.g_exc_error;
2627 END IF;
2628 -- Check for step status in case the item is associated to a step.
2629 l_rel_type :=
2630 gme_common_pvt.is_material_auto_release
2631 (l_mat_dtl_rec.material_detail_id);
2632 IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN -- /*3*/
2633 OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
2634 FETCH Cur_associated_step INTO l_step_status;
2635 CLOSE Cur_associated_step;
2636 IF l_step_status NOT IN (2,3) THEN
2637 gme_common_pvt.log_message ('GME_API_INVALID_STEP_STATUS');
2638 RAISE fnd_api.g_exc_error;
2639 END IF;
2640 END IF; -- IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN
2641 -- check for item release type for products
2642 IF (l_rel_type = gme_common_pvt.g_mtl_auto_release )
2643 AND l_mat_dtl_rec.line_type IN (1,2)
2644 AND l_mat_dtl_rec.phantom_line_id IS NULL THEN
2645 IF l_batch_hdr_rec.batch_status <> 3 THEN
2646 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS');
2647 RAISE fnd_api.g_exc_error;
2648 END IF;
2649 END IF;
2650 END IF; -- gme_common_pvt.g_batch_status_check
2651 IF l_mmti_rec.transaction_type_id IN
2652 (gme_common_pvt.g_ing_issue, gme_common_pvt.g_ing_return) THEN
2653 IF l_mat_dtl_rec.line_type <> -1 THEN
2654 /* Bug 5141394 Changed message */
2655 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
2656 RAISE fnd_api.g_exc_error;
2657 END IF;
2658 ELSIF l_mmti_rec.transaction_type_id IN
2659 (gme_common_pvt.g_prod_completion
2660 ,gme_common_pvt.g_prod_return) THEN
2661 IF l_mat_dtl_rec.line_type <> 1 THEN
2662 /* Bug 5141394 Changed message */
2663 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
2664 RAISE fnd_api.g_exc_error;
2665 END IF;
2666 ELSIF l_mmti_rec.transaction_type_id IN
2667 (gme_common_pvt.g_byprod_completion
2671 --RLNAGARA Bug6873185 Moved below line which was setting message name inside the IF condition.
2668 ,gme_common_pvt.g_byprod_return) THEN
2669 IF l_mat_dtl_rec.line_type <> 2 THEN
2670 /* Bug 5141394 Changed message */
2672 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
2673 RAISE fnd_api.g_exc_error;
2674 END IF;
2675 END IF;
2676
2677 -- check for phantom
2678 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
2679 IF l_mmti_rec.transaction_header_id <>
2680 gme_common_pvt.g_transaction_header_id THEN
2681 gme_common_pvt.log_message ('GME_PHANTOM_NO_RETURN');
2682 RAISE fnd_api.g_exc_error;
2683 END IF;
2684 END IF;
2685
2686 -- get the item propertites
2687 OPEN cur_get_item_rec (l_mmti_rec.inventory_item_id, l_mmti_rec.organization_id);
2688 FETCH cur_get_item_rec INTO l_item_rec;
2689 IF cur_get_item_rec%NOTFOUND THEN
2690 CLOSE cur_get_item_rec;
2691 gme_common_pvt.log_message ('PM_INVALID_ITEM');
2692 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2693 gme_debug.put_line('Item cursor fetch no record in mtl_system_items_b: ');
2694 gme_debug.put_line('inventory_item_id = '|| TO_CHAR (l_mmti_rec.inventory_item_id));
2695 gme_debug.put_line('organization_id = '|| TO_CHAR (l_mmti_rec.organization_id));
2696 END IF;
2697 RAISE item_not_found;
2698 END IF;
2699 CLOSE cur_get_item_rec;
2700 IF (g_debug <= gme_debug.g_log_statement) THEN
2701 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item lot_control Code: '|| l_item_rec.lot_control_code);
2702 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item loct_control Code: '|| l_item_rec.location_control_code);
2703 END IF;
2704
2705 /* Bug 5358129 for ingredients lots should exist */
2706 IF (l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mat_dtl_rec.phantom_type = 0 AND l_item_rec.lot_control_code = 2) THEN
2707 FOR get_lots IN (SELECT DISTINCT lot_number FROM mtl_transaction_lots_interface WHERE transaction_interface_id = p_transaction_interface_id) LOOP
2708 gme_transactions_pvt.validate_lot_for_ing(p_organization_id => l_mmti_rec.organization_id,
2709 p_inventory_item_id => l_mmti_rec.inventory_item_id,
2710 p_lot_number => get_lots.lot_number,
2711 x_return_status => l_return_status);
2712 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2713 RAISE lot_val_err;
2714 END IF;
2715 END LOOP;
2716 END IF;
2717
2718 -- if return transaction then check qty was issued and return not more than issued qty
2719 IF l_mmti_rec.transaction_type_id IN
2720 (gme_common_pvt.g_byprod_return
2721 ,gme_common_pvt.g_prod_return
2722 ,gme_common_pvt.g_ing_return) THEN
2723 IF (g_debug <= gme_debug.g_log_statement) THEN
2724 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||
2725 'Return transaction for : '||l_mmti_rec.transaction_type_id);
2726
2727 END IF;
2728 IF l_item_rec.lot_control_code = 1 THEN
2729 IF (g_debug <= gme_debug.g_log_statement) THEN
2730 gme_debug.put_line ( g_pkg_name
2731 || '.'
2732 || l_api_name
2733 || ':'
2734 || 'Item is NOT lot_control: '
2735 || l_item_rec.lot_control_code);
2736 END IF;
2737 get_returnable_qty(
2738 p_mmti_rec => l_mmti_rec
2739 ,p_lot_number => NULL
2740 ,p_lot_control => l_item_rec.lot_control_code
2741 ,x_available_qty => l_available_qty
2742 ,x_return_status => x_return_status);
2743
2744 IF x_return_Status <> fnd_api.g_ret_sts_success THEN
2745 RAISE fnd_api.g_exc_unexpected_error;
2746 END IF;
2747 IF (g_debug <= gme_debug.g_log_statement) THEN
2748 gme_debug.put_line ( g_pkg_name
2749 || '.'
2750 || l_api_name
2751 || ':'
2752 || 'Returning Qty '
2753 || l_mmti_rec.transaction_quantity);
2754 gme_debug.put_line ( g_pkg_name
2755 || '.'
2756 || l_api_name
2757 || ':'
2758 || 'Available to Return '
2759 || l_available_qty);
2760 END IF;
2761
2762 IF ABS (l_available_qty) < ABS (l_mmti_rec.transaction_quantity) THEN
2763 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
2764 RAISE fnd_api.g_exc_error;
2765 END IF;
2766 ELSE /* Lot Control */
2770 || l_api_name
2767 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2768 gme_debug.put_line ( g_pkg_name
2769 || '.'
2771 || ':'
2772 || 'Item is lot control: '
2773 || l_item_rec.lot_control_code);
2774 END IF;
2775
2776 FOR get_rec IN cur_lot_input (p_transaction_interface_id) LOOP
2777 -- first get the qty from the mtln table
2778 get_returnable_qty(
2779 p_mmti_rec => l_mmti_rec
2780 ,p_lot_number => get_rec.lot_number
2781 ,p_lot_control => l_item_rec.lot_control_code
2782 ,x_available_qty => l_available_qty
2783 ,x_return_status => x_return_status);
2784
2785 IF x_return_Status <> fnd_api.g_ret_sts_success THEN
2786 RAISE fnd_api.g_exc_unexpected_error;
2787 END IF;
2788 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2789 gme_debug.put_line ( g_pkg_name
2790 || '.'
2791 || l_api_name
2792 || ':'
2793 || 'Returning qty: '|| get_rec.l_mtli_lot_qty);
2794 gme_debug.put_line ( g_pkg_name
2795 || '.'
2796 || l_api_name
2797 || ':'
2798 || 'Available to Return '
2799 || l_available_qty);
2800 END IF;
2801
2802 IF ABS (l_available_qty) < ABS (get_rec.l_mtli_lot_qty) THEN
2803 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
2804 RAISE fnd_api.g_exc_error;
2805 END IF;
2806 END LOOP;
2807 END IF; /* IF l_item_rec.lot_control_code = 1 THEN */
2808 END IF; /* IF transaction_type_id in RETURNS */
2809 END IF; /* update_inventory_ind = 'Y' */
2810
2811 IF (g_debug <= gme_debug.g_log_statement) THEN
2812 gme_debug.put_line ( g_pkg_name
2813 || '.'
2814 || l_api_name
2815 || ':'
2816 || 'Exiting with '
2817 || x_return_status);
2818 END IF;
2819 EXCEPTION
2820 WHEN lot_val_err THEN
2821 x_return_status := l_return_status;
2822 WHEN fnd_api.g_exc_error THEN
2823 x_return_status := fnd_api.g_ret_sts_error;
2824 gme_transactions_pvt.gme_txn_message
2825 (p_api_name => l_api_name
2826 ,p_transaction_interface_id => p_transaction_interface_id
2827 );
2828 WHEN fnd_api.g_exc_unexpected_error THEN
2829 x_return_status := fnd_api.g_ret_sts_unexp_error;
2830 WHEN OTHERS THEN
2831 x_return_status := fnd_api.g_ret_sts_unexp_error;
2832 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2833
2834 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2835 gme_debug.put_line ( g_pkg_name
2836 || '.'
2837 || l_api_name
2838 || ':'
2839 || 'WHEN OTHERS:'
2840 || SQLERRM);
2841 END IF;
2842
2843 gme_transactions_pvt.gme_txn_message
2844 (p_api_name => l_api_name
2845 ,p_transaction_interface_id => p_transaction_interface_id
2846 );
2847
2848 END pre_process_val;
2849
2850 /* +==========================================================================+
2851 | PROCEDURE NAME
2852 | gme_txn_message
2853 |
2854 | USAGE
2855 |
2856 |
2857 | ARGUMENTS
2858 |
2859 |
2860 | RETURNS
2861 |
2862 |
2863 | HISTORY
2864 | Created 02-Feb-05 Pawan Kumar
2865 |
2866 +==========================================================================+ */
2867 PROCEDURE gme_txn_message (
2868 p_api_name IN VARCHAR2
2869 ,p_transaction_interface_id IN VARCHAR2
2870 )
2871 IS
2872 l_transaction_interface_id NUMBER;
2873 x_message_count NUMBER;
2874 x_message_list VARCHAR2 (2000);
2875 l_errm VARCHAR2 (2000) := SQLERRM;
2876 l_api_name CONSTANT VARCHAR2 (30) := 'gme_txn_message';
2877 BEGIN
2878 -- Initially let us assign the return status to success
2879
2880 l_transaction_interface_id := p_transaction_interface_id;
2881
2882 IF (g_debug <= gme_debug.g_log_statement) THEN
2883 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2884 || 'Entering');
2885 END IF;
2886
2887 -- based on message call count and get
2891
2888 gme_common_pvt.count_and_get (x_count => x_message_count
2889 ,p_encoded => fnd_api.g_false
2890 ,x_data => x_message_list);
2892 UPDATE mtl_transactions_interface
2893 SET ERROR_CODE = g_pkg_name || '.' || p_api_name
2894 ,error_explanation = NVL (x_message_list, l_errm)
2895 ,process_flag = 3 -- we can make it a constant in gme common
2896 WHERE transaction_interface_id = l_transaction_interface_id;
2897
2898 IF (g_debug <= gme_debug.g_log_statement) THEN
2899 gme_debug.put_line ( g_pkg_name
2900 || '.'
2901 || l_api_name
2902 || ':'
2903 || 'Exiting'
2904 );
2905 END IF;
2906 EXCEPTION
2907 WHEN OTHERS THEN
2908 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2909
2910 IF g_debug <= gme_debug.g_log_unexpected THEN
2911 gme_debug.put_line ( 'When others exception in '
2912 || g_pkg_name
2913 || '.'
2914 || l_api_name
2915 || ' Error is '
2916 || SQLERRM);
2917 END IF;
2918 END gme_txn_message;
2919
2920 /* +==========================================================================+
2921 | PROCEDURE NAME
2922 | gme_post_process
2923 |
2924 | USAGE
2925 |
2926 |
2927 | ARGUMENTS
2928 | p_transaction_id
2929 |
2930 |
2931 | RETURNS
2932 | returns via x_status OUT parameters
2933 |
2934 | HISTORY
2935 | Created 02-Feb-05 Pawan Kumar
2936 | 5176319 20-Jun-06 Namit S. Added call to gme_unrelease_batch_pvt.create_matl_resv_pplot
2937 | and gme_common_pvt.reset_txn_hdr_tbl.
2938 | Bug 5763818 28-Feb-2007 Archana Mundhe Do not update actual qty if
2939 | the material detail line has been deleted.
2940 | Bug 6997483 01-May-2005 Archana Mundhe Added parameter transaction_id
2941 | to gme_unrelease_batch_pvt.create_matl_resv_pplot.
2942 +==========================================================================+ */
2943 PROCEDURE gme_post_process (
2944 p_transaction_id IN NUMBER
2945 ,x_return_status OUT NOCOPY VARCHAR2)
2946 IS
2947 CURSOR cur_get_trans (v_transaction_id NUMBER)
2948 IS
2949 SELECT t.transaction_id, t.transaction_source_id, l.lot_number
2950 ,t.trx_source_line_id, t.source_line_id, t.transaction_type_id,
2951 t.transaction_reference, t.inventory_item_id
2952 , t.organization_id
2953 FROM mtl_material_transactions t, mtl_transaction_lot_numbers l
2954 WHERE t.transaction_id = l.transaction_id(+)
2955 AND t.transaction_id = v_transaction_id;
2956 /* Bug 5903208 Modified cursor so it gets 1 lot at a time for GMF needs */
2957 CURSOR cur_mat_sum(v_organization_id IN NUMBER
2958 ,v_batch_id IN NUMBER
2959 ,v_mat_det_id IN NUMBER) IS
2960 SELECT a.transaction_id, a.lot_number, a.doc_qty, SUM(a.doc_qty) over() mtl_qty
2961 FROM (SELECT t.transaction_id, tl.lot_number,
2962 DECODE(d.dtl_um,t.transaction_uom, NVL(tl.transaction_quantity,t.transaction_quantity),
2963 Inv_Convert.inv_um_convert(d.inventory_item_id,tl.lot_number,t.organization_id, 5
2964 ,NVL(tl.transaction_quantity,t.transaction_quantity), t.transaction_uom
2965 ,d.dtl_um, NULL, NULL)) doc_qty
2966 FROM mtl_material_transactions t , gme_material_details d, mtl_transaction_lot_numbers tl
2967 WHERE t.organization_id = v_organization_id
2968 AND t.transaction_source_id = v_batch_id
2969 AND t.trx_source_line_id = v_mat_det_id
2970 AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
2971 AND t.trx_source_line_id = d.material_detail_id
2972 AND tl.transaction_id(+) = t.transaction_id) a;
2973
2974 CURSOR cur_lot_qty (
2975 v_lot_number IN VARCHAR2
2976 ,v_item_id IN NUMBER
2977 ,v_organization_id IN NUMBER
2978 ,v_batch_id IN NUMBER
2979 ,v_mat_det_id IN NUMBER)
2980 IS
2981 SELECT lot_number, SUM (l.transaction_quantity)
2982 FROM mtl_material_transactions m, mtl_transaction_lot_numbers l
2983 WHERE l.transaction_id = m.transaction_id
2984 AND l.lot_number = v_lot_number
2985 AND l.inventory_item_id = v_item_id
2986 AND l.organization_id = v_organization_id
2987 AND l.transaction_source_id = v_batch_id
2988 AND m.trx_source_line_id = v_mat_det_id
2989 AND m.transaction_source_type_id =
2990 gme_common_pvt.g_txn_source_type
2991 GROUP BY l.lot_number, l.inventory_item_id;
2992
2993 -- Bug 5763818
2994 CURSOR check_event_batchmtl_removed (
2995 v_transaction_source_id IN NUMBER
2996 ,v_trx_source_line_id IN NUMBER)
2997 IS
2998 select count(1)
2999 from GME_ERES_GTMP
3000 where event_name = 'oracle.apps.gme.batchmtl.removed'
3004 x_msg_data VARCHAR2(2000);
3001 and event_key = v_transaction_source_id||'-'||v_trx_source_line_id;
3002
3003 x_msg_count NUMBER;
3005 l_return_status VARCHAR2(1) ;
3006 l_transaction_id NUMBER;
3007 l_dispense_id NUMBER;
3008 l_transaction_source_id NUMBER;
3009 l_transaction_type_id NUMBER;
3010 l_lot_number VARCHAR2 (80);
3011 l_transaction_reference VARCHAR2 (80);
3012 l_trx_source_line_id NUMBER;
3013 l_source_line_id NUMBER;
3014 l_inventory_item_id NUMBER;
3015 l_organization_id NUMBER;
3016 l_actual_qty NUMBER;
3017 l_gme_pairs_rec gme_transaction_pairs%ROWTYPE;
3018 l_mat_dtl_rec gme_material_details%ROWTYPE;
3019 l_api_name CONSTANT VARCHAR2 (30) := 'gme_post_process';
3020 l_exists NUMBER; -- Bug 5763818
3021 transfer_error EXCEPTION; -- B4944024
3022 dispense_error EXCEPTION;
3023 BEGIN
3024 -- Initially let us assign the return status to success
3025 x_return_status := fnd_api.g_ret_sts_success;
3026
3027 IF (g_debug <= gme_debug.g_log_statement) THEN
3028 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3029 || 'Entering with trans id: '||p_transaction_id);
3030
3031 END IF;
3032
3033 l_transaction_id := p_transaction_id;
3034 -- based on this transaction_id get all transactions details
3035 OPEN cur_get_trans (l_transaction_id);
3036
3037 FETCH cur_get_trans
3038 INTO l_transaction_id, l_transaction_source_id, l_lot_number
3039 ,l_trx_source_line_id, l_source_line_id,l_transaction_type_id,
3040 l_transaction_reference ,l_inventory_item_id, l_organization_id;
3041
3042 CLOSE cur_get_trans;
3043
3044 -- nsinghi bug#5176319
3045 /* Re-Create Material Reservation during un-release batch/step. */
3046 gme_unrelease_batch_pvt.create_matl_resv_pplot (
3047 p_material_dtl_id => l_trx_source_line_id,
3048 p_transaction_id => l_transaction_id, -- Bug 6997483
3049 x_return_status => x_return_status);
3050
3051 IF (g_debug <= gme_debug.g_log_statement) THEN
3052 gme_debug.put_line ( g_pkg_name
3053 || '.'
3054 || l_api_name
3055 || ':'
3056 || 'transaction_id: '
3057 || l_transaction_id);
3058 gme_debug.put_line ( g_pkg_name
3059 || '.'
3060 || l_api_name
3061 || ':'
3062 || 'transaction_source_id: '
3063 || l_transaction_source_id);
3064 gme_debug.put_line ( g_pkg_name
3065 || '.'
3066 || l_api_name
3067 || ':'
3068 || 'trx_source_line_id: '
3069 || l_trx_source_line_id);
3070 gme_debug.put_line ( g_pkg_name
3071 || '.'
3072 || l_api_name
3073 || ':'
3074 || 'source_line_id: '
3075 || l_source_line_id);
3076 gme_debug.put_line ( g_pkg_name
3077 || '.'
3078 || l_api_name
3079 || ':'
3080 || 'inventory_item_id: '
3081 || l_inventory_item_id);
3082 gme_debug.put_line ( g_pkg_name
3083 || '.'
3084 || l_api_name
3085 || ':'
3086 || 'transaction refernce: '
3087 || l_transaction_reference);
3088 END IF;
3089
3090 IF l_source_line_id <> -99 THEN
3091 IF (g_debug <= gme_debug.g_log_statement) THEN
3092 gme_debug.put_line ( g_pkg_name
3093 || '.'
3094 || l_api_name
3095 || ':'
3096 || 'for inserting reverse transaction_id: '
3097 || l_transaction_id);
3098 gme_debug.put_line ( g_pkg_name
3099 || '.'
3100 || l_api_name
3101 || ':'
3102 || 'for inserting reverse source_line_id: '
3103 || l_source_line_id);
3104 END IF;
3105
3106 -- UPDATE transactions pair table for the reversal transaction
3107 UPDATE gme_transaction_pairs
3108 SET transaction_id2 = l_transaction_id
3109 WHERE batch_id = l_transaction_source_id
3110 AND material_detail_id = l_trx_source_line_id
3111 AND transaction_id1 = l_source_line_id
3112 AND pair_type = gme_common_pvt.g_pairs_reversal_type;
3113 -- now insert a reverse record
3114 INSERT INTO gme_transaction_pairs
3115 (batch_id, material_detail_id
3116 ,transaction_id1, transaction_id2
3120 ,gme_common_pvt.g_pairs_reversal_type);
3117 ,pair_type)
3118 VALUES (l_transaction_source_id, l_trx_source_line_id
3119 ,l_transaction_id, l_source_line_id
3121
3122 IF (g_debug <= gme_debug.g_log_statement) THEN
3123 gme_debug.put_line ( g_pkg_name
3124 || '.'
3125 || l_api_name
3126 || ':'
3127 || 'after inserting reverse transaction_id: '
3128 || l_transaction_id);
3129 gme_debug.put_line ( g_pkg_name
3130 || '.'
3131 || l_api_name
3132 || ':'
3133 || 'after inserting reverse source_line_id: '
3134 || l_source_line_id);
3135 END IF;
3136 END IF; -- l_source_line_id
3137
3138 -- for transaction pairs
3139 IF l_transaction_reference IS NOT NULL THEN
3140 l_mat_dtl_rec.material_detail_id := l_trx_source_line_id ;
3141 IF NOT gme_material_details_dbl.fetch_row
3142 (p_material_detail => l_mat_dtl_rec
3143 ,x_material_detail => l_mat_dtl_rec) THEN
3144 RAISE fnd_api.g_exc_error;
3145 END IF;
3146 IF (g_debug <= gme_debug.g_log_statement) THEN
3147 gme_debug.put_line
3148 ( g_pkg_name
3149 || '.'
3150 || l_api_name
3151 || ':'
3152 || 'for inserting phantom l_transaction_reference: '
3153 || l_transaction_reference);
3154 END IF;
3155
3156
3157 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
3158 IF (g_debug <= gme_debug.g_log_statement) THEN
3159 gme_debug.put_line
3160 ( g_pkg_name
3161 || '.'
3162 || l_api_name
3163 || '.'
3164 || 'update row -phantom in pairs having l_trans_ref: '
3165 || l_transaction_reference);
3166 gme_debug.put_line
3167 ( g_pkg_name
3168 || '.'
3169 || l_api_name
3170 || '.'
3171 || 'update row-phantom in pairs with l_trans_ID: '
3172 || l_transaction_id);
3173 END IF;
3174 UPDATE gme_transaction_pairs
3175 SET transaction_id2 = l_transaction_id
3176 WHERE transaction_id2 = l_transaction_reference
3177 AND pair_type = gme_common_pvt.g_pairs_phantom_type;
3178
3179 BEGIN
3180 SELECT *
3181 INTO l_gme_pairs_rec
3182 FROM gme_transaction_pairs
3183 WHERE transaction_id2 = l_transaction_id
3184 AND pair_type = gme_common_pvt.g_pairs_phantom_type;
3185
3186 IF (g_debug <= gme_debug.g_log_statement) THEN
3187 gme_debug.put_line
3188 ( g_pkg_name
3189 || '.'
3190 || l_api_name
3191 || '.'
3192 || 'after update row -phantom l_trans_id1: '
3193 || l_gme_pairs_rec.transaction_id1);
3194 gme_debug.put_line ( g_pkg_name
3195 || '.'
3196 || l_api_name
3197 || '.'
3198 || 'after update row-phantom l_trans_ID2: '
3199 || l_gme_pairs_rec.transaction_id1);
3200 gme_debug.put_line
3201 ( g_pkg_name
3202 || '.'
3203 || l_api_name
3204 || '.'
3205 || 'insert row- after update -transaction_id1: '
3206 || l_gme_pairs_rec.transaction_id1);
3207 gme_debug.put_line
3208 ( g_pkg_name
3209 || '.'
3210 || l_api_name
3211 || '.'
3212 || 'insert row- after update -transaction_id2: '
3213 || l_gme_pairs_rec.transaction_id2);
3214 gme_debug.put_line ( g_pkg_name
3215 || '.'
3216 || l_api_name
3217 || '.'
3218 || 'insert row- after update -batch_id:'
3219 || l_transaction_source_id);
3220 gme_debug.put_line ( g_pkg_name
3221 || '.'
3222 || l_api_name
3223 || '.'
3224 || 'insert row- after update -mat_det_id: '
3225 || l_trx_source_line_id);
3229 (batch_id, material_detail_id
3226 END IF; -- for debug
3227
3228 INSERT INTO gme_transaction_pairs
3230 ,transaction_id1, transaction_id2
3231 ,pair_type)
3232 VALUES (l_transaction_source_id, l_trx_source_line_id
3233 ,l_transaction_id, l_gme_pairs_rec.transaction_id1
3234 ,gme_common_pvt.g_pairs_phantom_type);
3235
3236
3237 EXCEPTION
3238 WHEN NO_DATA_FOUND THEN
3239 IF (g_debug <= gme_debug.g_log_statement) THEN
3240 gme_debug.put_line ( g_pkg_name
3241 || '.'
3242 || l_api_name
3243 || '.'
3244 || 'No_data_found');
3245 gme_debug.put_line ( g_pkg_name
3246 || '.'
3247 || l_api_name
3248 || '.'
3249 || 'insert row-transaction_id1: '
3250 || l_transaction_id);
3251 gme_debug.put_line ( g_pkg_name
3252 || '.'
3253 || l_api_name
3254 || '.'
3255 || 'insert row-transaction_id2: '
3256 || l_transaction_reference);
3257 gme_debug.put_line ( g_pkg_name
3258 || '.'
3259 || l_api_name
3260 || '.'
3261 || 'insert row-batch_id: '
3262 || l_transaction_source_id);
3263 gme_debug.put_line ( g_pkg_name
3264 || '.'
3265 || l_api_name
3266 || '.'
3267 || 'insert row-material_detail_id: '
3268 || l_trx_source_line_id);
3269 END IF;
3270
3271 --INSERT a new row
3272 INSERT INTO gme_transaction_pairs
3273 (batch_id, material_detail_id
3274 ,transaction_id1, transaction_id2
3275 ,pair_type)
3276 VALUES (l_transaction_source_id, l_trx_source_line_id
3277 ,l_transaction_id, l_transaction_reference
3278 ,gme_common_pvt.g_pairs_phantom_type);
3279 END;
3280 ELSE
3281 l_dispense_id := l_transaction_reference ;
3282 -- make a call to GMO for informing about dispense_id
3283 IF l_mat_dtl_rec.dispense_ind = 'Y' THEN
3284 IF l_transaction_type_id = gme_common_pvt.g_ing_issue THEN
3285 -- For consume
3286 GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
3287 (p_api_version => 1.0,
3288 p_init_msg_list => 'F',
3289 p_commit => 'F',
3290 x_return_status => l_return_status,
3291 x_msg_count => x_msg_count,
3292 x_msg_data => x_msg_data,
3293 p_dispense_id => l_dispense_id,
3294 p_status_code => 'CNSUMED',
3295 p_transaction_id => l_transaction_id
3296 ) ;
3297 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3298 RAISE dispense_error;
3299 END IF;
3300 ELSE
3301 -- unconsume
3302
3303 GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
3304 (p_api_version => 1.0,
3305 p_init_msg_list => 'F',
3306 p_commit => 'F',
3307 x_return_status => l_return_status,
3308 x_msg_count => x_msg_count,
3309 x_msg_data => x_msg_data ,
3310 p_dispense_id => l_dispense_id,
3311 p_status_code => 'REVRDISP' ,
3312 p_transaction_id => l_transaction_id
3313 ) ;
3314 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3315 RAISE dispense_error;
3316 END IF;
3317 END IF;
3318 END IF ; -- dispense_ind
3319 END IF; -- if phantom_line_id is not null
3320 END IF; -- transaction_refernce is not null
3321
3322 -- Bug 5763818
3323 -- Open cursor to fetch event name and event key from gme_eres_gmtp
3324 -- IF event name is oracle.apps.gme.batchmtl.removed then do not process
3325 OPEN check_event_batchmtl_removed (l_transaction_source_id,
3326 l_trx_source_line_id);
3327 FETCH check_event_batchmtl_removed INTO l_exists;
3328 CLOSE check_event_batchmtl_removed;
3329
3330 IF (g_debug <= gme_debug.g_log_statement) THEN
3331 gme_debug.put_line (g_pkg_name||'.'||l_api_name||': '||'l_exists = '||
3332 TO_CHAR (l_exists));
3336 -- Do not update the actual qty if the line has been deleted.
3333 END IF;
3334
3335 -- Bug 5763818
3337 IF (l_exists = 0) THEN
3338
3339 -- get the total quantity for actaul qty update of material detail line
3340 l_mat_dtl_rec.material_detail_id := l_trx_source_line_id;
3341
3342 IF NOT gme_material_details_dbl.fetch_row
3343 (p_material_detail => l_mat_dtl_rec
3344 ,x_material_detail => l_mat_dtl_rec) THEN
3345 RAISE fnd_api.g_exc_error;
3346 END IF;
3347
3348 IF (g_debug <= gme_debug.g_log_statement) THEN
3349 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||' mat_id '|| l_mat_dtl_rec.material_detail_id );
3350 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||' DTL_UM '|| l_mat_dtl_rec.dtl_um );
3351
3352 END IF;
3353 --sum for material_detail line
3354 /* Bug 5903208 Instead of directly getting sum now we get 1 lot at a time and also the sum */
3355 FOR get_rec IN cur_mat_sum(l_organization_id, l_transaction_source_id, l_trx_source_line_id) LOOP
3356 l_actual_qty := get_rec.mtl_qty;
3357 p_qty_tbl(get_rec.transaction_id||'@'||get_rec.lot_number).doc_qty := get_rec.doc_qty;
3358 END LOOP;
3359
3360 -- now update the qty to material_detail
3361 IF (g_debug <= gme_debug.g_log_statement) THEN
3362 gme_debug.put_line ( g_pkg_name
3363 || '.'
3364 || l_api_name
3365 || ':Actual Quantity '
3366 || l_actual_qty);
3367 gme_debug.put_line ( g_pkg_name
3368 || '.'
3369 || l_api_name
3370 || ': TRX_SOURCE_LINE_ID '
3371 || l_trx_source_line_id);
3372 END IF;
3373
3374 l_mat_dtl_rec.actual_qty := ABS (l_actual_qty);
3375
3376 IF NOT gme_material_details_dbl.update_row
3377 (p_material_detail => l_mat_dtl_rec) THEN
3378 RAISE fnd_api.g_exc_error;
3379 END IF;
3380
3381 -- B4944024 BEGIN
3382 -- At yield, any reservations against PROD must transfer to a supply source of Inventory
3383 -- In this way, the newly generated inventory is re-secured to the demand source
3384 -- ======================================================================================
3385 -- Pawan Kumar bug 5483071 added check for transaction refernce and source_line_id
3386 -- THis done so that we donot try to invoke this for reversal of wip return
3387 -- dispense is not a issue as it is only for ingredients
3388 IF l_mat_dtl_rec.line_type <> -1 AND
3389 l_transaction_type_id = gme_common_pvt.g_prod_completion
3390 AND l_transaction_reference IS NULL
3391 -- Pawan Kumar add bug 5709186
3392 -- in case transaction added from transaction form, l_source_line_id is null.
3393 AND nvl(l_source_line_id, -99) < 0 THEN
3394
3395 IF (g_debug <= gme_debug.g_log_statement) THEN
3396 gme_debug.put_line ( g_pkg_name
3397 || '.'
3398 || l_api_name
3399 || ' Invoke transfer_reservation_to_inv ');
3400 END IF;
3401 GME_SUPPLY_RES_PVT.transfer_reservation_to_inv (
3402 p_matl_dtl_rec => l_mat_dtl_rec
3403 ,p_transaction_id => p_transaction_id
3404 ,x_message_count => x_msg_count
3405 ,x_message_list => x_msg_data
3406 ,x_return_status => x_return_status);
3407
3408 IF (g_debug <= gme_debug.g_log_statement) THEN
3409 gme_debug.put_line ( g_pkg_name
3410 || '.'
3411 || l_api_name
3412 || ' transfer_reservation_to_inv returns '
3413 || x_return_status);
3414 END IF;
3415 IF x_return_status <> fnd_api.g_ret_sts_success THEN
3416 RAISE transfer_error; -- B4944024
3417 END IF;
3418 END IF;
3419 -- B4944024 END
3420
3421 -- now check for negative qty
3422 IF (g_debug <= gme_debug.g_log_statement) THEN
3423 gme_debug.put_line ( g_pkg_name
3424 || '.'
3425 || l_api_name
3426 || ':'
3427 || 'Exiting with '
3428 || x_return_status);
3429 END IF;
3430 END IF; -- l_exists = 0
3431 EXCEPTION
3432 WHEN dispense_error THEN
3433
3434 x_return_status := fnd_api.g_ret_sts_error;
3435 WHEN fnd_api.g_exc_error THEN
3436 x_return_status := fnd_api.g_ret_sts_error;
3437 WHEN fnd_api.g_exc_unexpected_error THEN
3438 x_return_status := fnd_api.g_ret_sts_unexp_error;
3439 WHEN OTHERS THEN
3440 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3441
3442 IF g_debug <= gme_debug.g_log_unexpected THEN
3443 gme_debug.put_line ( 'When others exception in '
3444 || g_pkg_name
3445 || '.'
3446 || l_api_name
3447 || ' Error is '
3448 || SQLERRM);
3452 /* +==========================================================================+
3449 END IF;
3450 END gme_post_process;
3451
3453 | PROCEDURE NAME
3454 | purge_trans_pairs
3455 |
3456 | USAGE
3457 |
3458 |
3459 | ARGUMENTS
3460 | p_batch_id
3461 | p_material_detail_id
3462 |
3463 | RETURNS
3464 | returns via x_status OUT parameters
3465 |
3466 | HISTORY
3467 | Created 02-Feb-05 Pawan Kumar
3468 |
3469 +==========================================================================+ */
3470 PROCEDURE purge_trans_pairs (
3471 p_batch_id IN NUMBER
3472 ,p_material_detail_id IN NUMBER DEFAULT NULL
3473 ,x_return_status OUT NOCOPY VARCHAR2)
3474 IS
3475 l_batch_id NUMBER;
3476 l_material_detail_id NUMBER;
3477 l_api_name CONSTANT VARCHAR2 (30) := 'purge_trans_pairs';
3478 BEGIN
3479 -- Initially let us assign the return status to success
3480 x_return_status := fnd_api.g_ret_sts_success;
3481
3482 IF (g_debug <= gme_debug.g_log_statement) THEN
3483 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3484 || 'Entering');
3485 END IF;
3486
3487 IF p_batch_id IS NULL AND p_material_detail_id IS NULL THEN
3488 gme_common_pvt.log_message ('GME_INVALID_FIELD'
3489 ,'FIELD'
3490 ,'p_batch_id');
3491 RAISE fnd_api.g_exc_error;
3492 END IF;
3493
3494 l_batch_id := p_batch_id;
3495 l_material_detail_id := p_material_detail_id;
3496
3497 IF (g_debug <= gme_debug.g_log_statement) THEN
3498 gme_debug.put_line ( g_pkg_name
3499 || '.'
3500 || l_api_name
3501 || ':'
3502 || 'batch_id:'
3503 || l_batch_id);
3504 gme_debug.put_line ( g_pkg_name
3505 || '.'
3506 || l_api_name
3507 || ':'
3508 || 'material_detail_id:'
3509 || l_material_detail_id);
3510 END IF;
3511
3512 IF l_batch_id IS NOT NULL THEN
3513 DELETE FROM gme_transaction_pairs
3514 WHERE batch_id = l_batch_id;
3515 ELSIF l_material_detail_id IS NOT NULL THEN
3516 DELETE FROM gme_transaction_pairs
3517 WHERE material_detail_id = l_material_detail_id;
3518 ELSE
3519 DELETE FROM gme_transaction_pairs
3520 WHERE batch_id = l_batch_id
3521 AND material_detail_id = l_material_detail_id;
3522 END IF;
3523
3524 IF (g_debug <= gme_debug.g_log_statement) THEN
3525 gme_debug.put_line ( g_pkg_name
3526 || '.'
3527 || l_api_name
3528 || ':'
3529 || 'Exiting with '
3530 || x_return_status);
3531 END IF;
3532 EXCEPTION
3533 WHEN fnd_api.g_exc_error THEN
3534 x_return_status := fnd_api.g_ret_sts_error;
3535 WHEN fnd_api.g_exc_unexpected_error THEN
3536 x_return_status := fnd_api.g_ret_sts_unexp_error;
3537 WHEN OTHERS THEN
3538 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3539
3540 IF g_debug <= gme_debug.g_log_unexpected THEN
3541 gme_debug.put_line ( 'When others exception in '
3542 || g_pkg_name
3543 || '.'
3544 || l_api_name
3545 || ' Error is '
3546 || SQLERRM);
3547 END IF;
3548 END purge_trans_pairs;
3549
3550 /* +==========================================================================+
3551 | PROCEDURE NAME
3552 | Process_transactions
3553 |
3554 | USAGE
3555 | This is the interface procedure to the Inventory Transaction
3556 | Manager to validate and process a batch of material transaction
3557 | interface records
3558 |
3559 | ARGUMENTS
3560 | p_api_version API Version of this procedure. Current version is 1.0
3561 |
3562 | p_commit Indicates whether to commit the changes after successful processing
3563 | p_validation_level Indicates whether or not to perform a full validation
3564 | x_return_status Returns the status to indicate success or failure of execution
3565 | x_msg_count Returns number of error message in the error message stack in case of failure
3566 | x_msg_data Returns the error message in case of failure
3567 | x_trans_count The count of material transaction interface records processed.
3568 | p_table Source of transaction records with value 1 of material transaction interface table and value 2 of material transaction temp table
3569 | p_header_id Transaction header id DEFAULT gme_common_pvt.get_txn_header_id
3570 |
3571 | RETURNS
3572 | returns via x_ OUT parameters
3573 |
3574 | HISTORY
3575 | Created 07-Mar-05 Jalaj Srivastava
3576 | 26-JUL-2007 Swapna Bug#6266714
3577 | Added condition to check the transaction source type in the for loop query.
3581 +==========================================================================+ */
3578 | 26-JUL-2007 Swapna Bug#6685680
3579 | Added call to gme_common_pvt.log message to log the actual error message which we can
3580 | retrieve using gme_common_pvt.count_and_get from the wraper apis.
3582 /* Bug 5255959 added p_clear_qty_cache parameter */
3583 PROCEDURE process_transactions (
3584 p_api_version IN NUMBER := 1
3585 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
3586 ,p_commit IN VARCHAR2 := fnd_api.g_false
3587 ,p_validation_level IN NUMBER
3588 := fnd_api.g_valid_level_full
3589 ,p_table IN NUMBER := 2
3590 ,p_header_id IN NUMBER
3591 := gme_common_pvt.get_txn_header_id
3592 ,x_return_status OUT NOCOPY VARCHAR2
3593 ,x_msg_count OUT NOCOPY NUMBER
3594 ,x_msg_data OUT NOCOPY VARCHAR2
3595 ,x_trans_count OUT NOCOPY NUMBER
3596 --Bug#5584699 Changed variable from boolean to varchar2
3597 ,p_clear_qty_cache IN VARCHAR2 := fnd_api.g_true)
3598 --,p_clear_qty_cache IN BOOLEAN DEFAULT TRUE)
3599 IS
3600 CURSOR get_error_int
3601 IS
3602 SELECT ERROR_CODE, error_explanation
3603 FROM mtl_transactions_interface
3604 WHERE transaction_header_id =
3605 gme_common_pvt.g_transaction_header_id;
3606
3607 CURSOR get_error_temp
3608 IS
3609 SELECT ERROR_CODE, error_explanation
3610 FROM mtl_material_transactions_temp
3611 WHERE transaction_header_id =
3612 gme_common_pvt.g_transaction_header_id;
3613
3614 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_TRANSACTIONS';
3615 l_return NUMBER;
3616 l_trans_rec GMF_LAYERS.TRANS_REC_TYPE;
3617 BEGIN
3618 IF (NVL (g_debug, 0) IN
3619 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
3620 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3621 || 'Entering');
3622 END IF;
3623
3624 /* Jalaj Srivastava Bug 5109154
3625 if p_table is MMTT then
3626 free the quantity tree */
3627 /* Bug 5255959 added p_clear_qty_cache condition */
3628 --Bug#5584699
3629 IF (p_table = 2 AND p_clear_qty_cache = fnd_api.g_true) THEN
3630 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3631 gme_debug.put_line
3632 ( g_pkg_name
3633 || '.'
3634 || l_api_name
3635 || ':'
3636 || 'Calling inv_quantity_tree_pub.clear_quantity_cache. p_table is MMTT');
3637 END IF;
3638 inv_quantity_tree_pub.clear_quantity_cache;
3639 END IF;
3640
3641 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3642 gme_debug.put_line
3643 ( g_pkg_name
3644 || '.'
3645 || l_api_name
3646 || ':'
3647 || 'Calling INV_TXN_MANAGER_PUB.process_transactions');
3648 gme_debug.put_line ( g_pkg_name
3649 || '.'
3650 || l_api_name
3651 || ':'
3652 || 'Transaction Header ID = '
3653 || p_header_id);
3654 gme_debug.put_line
3655 ( g_pkg_name
3656 || '.'
3657 || l_api_name
3658 || ':'
3659 || 'Transaction Table passed in MTI->1 MMTT->2 = '
3660 || p_table);
3661 END IF;
3662
3663 l_return :=
3664 inv_txn_manager_pub.process_transactions
3665 (p_api_version => p_api_version
3666 ,p_init_msg_list => p_init_msg_list
3667 ,p_commit => p_commit
3668 ,p_validation_level => p_validation_level
3669 ,p_table => p_table
3670 ,p_header_id => p_header_id
3671 ,x_return_status => x_return_status
3672 ,x_msg_count => x_msg_count
3673 ,x_msg_data => x_msg_data
3674 ,x_trans_count => x_trans_count);
3675
3676 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3677 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'After calling INV_TXN_MANAGER_PUB.process_transactions l_return= '
3678 || l_return || ' x_return_status= '|| x_return_status
3679 || ' x_msg_data = '|| x_msg_data);
3680 END IF;
3681
3682 /* begin temporary */
3683 IF (l_return = 0) THEN
3684 x_return_status := 'S';
3685 END IF;
3686 IF (x_msg_data IS NOT NULL) THEN
3687 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
3688 ,p_product_code => 'FND'
3692 IF (l_return < 0) THEN
3689 ,p_token1_name => 'MESSAGE'
3690 ,p_token1_value => x_msg_data);
3691 END IF;
3693 --Pawan Added for messages display
3694 IF p_table = 1 THEN
3695 x_msg_count := 0;
3696 FOR rec IN get_error_int LOOP
3697 /*Bug#6685680 Add the below call to log the actual error message*/
3698 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
3699 ,p_product_code => 'FND'
3700 ,p_token1_name => 'MESSAGE'
3701 ,p_token1_value => rec.error_explanation);
3702 --fnd_message.set_encoded (rec.error_explanation);
3703
3704 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3705 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| rec.error_explanation);
3706 END IF;
3707 x_msg_count := x_msg_count + 1;
3708 x_msg_data := rec.error_explanation;
3709 END LOOP;
3710 ELSE
3711 x_msg_count := 0;
3712 FOR rec IN get_error_temp LOOP
3713 /*Bug#6685680 Add the below call to log the actual error message*/
3714 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
3715 ,p_product_code => 'FND'
3716 ,p_token1_name => 'MESSAGE'
3717 ,p_token1_value => rec.error_explanation);
3718 --fnd_message.set_encoded (rec.error_explanation);
3719
3720 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3721 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| rec.error_explanation);
3722 END IF;
3723 x_msg_count := x_msg_count + 1;
3724 x_msg_data := rec.error_explanation;
3725 END LOOP;
3726 END IF; -- IF p_table
3727
3728 IF (x_return_status IS NULL OR x_return_status = 'S') THEN
3729 RAISE fnd_api.g_exc_unexpected_error;
3730 END IF;
3731 END IF;
3732 /* Bug 5903208 Start GMF code */
3733 IF x_return_status = 'S' THEN
3734 FOR trans_rec in
3735 (
3736 SELECT
3737 mmt.transaction_id
3738 , mmt.transaction_source_type_id
3739 , mmt.transaction_action_id
3740 , mmt.transaction_type_id
3741 , mmt.inventory_item_id
3742 , mmt.organization_id
3743 , mtln.lot_number
3744 , mmt.transaction_date
3745 , nvl(mtln.primary_quantity, mmt.primary_quantity) as primary_quantity
3746 , msi.primary_uom_code
3747 , nvl(mtln.transaction_quantity, mmt.transaction_quantity) as transaction_quantity /* Doc Qty */
3748 , md.dtl_um as doc_uom
3749 , mmt.transaction_source_id -- batch_id
3750 , mmt.trx_source_line_id -- line_id
3751 , gtp.transaction_id2 AS reverse_id
3752 , md.line_type
3753 , mmt.last_updated_by
3754 , mmt.created_by
3755 , mmt.last_update_login
3756 FROM
3757 mtl_material_transactions mmt,
3758 mtl_transaction_lot_numbers mtln,
3759 mtl_system_items_b msi,
3760 gme_material_details md,
3761 gme_transaction_pairs gtp
3762 WHERE
3763 mmt.transaction_set_id = gme_common_pvt.g_transaction_header_id
3764 AND mtln.transaction_id(+) = mmt.transaction_id
3765 AND msi.organization_id = mmt.organization_id
3766 AND msi.inventory_item_id = mmt.inventory_item_id
3767 AND md.material_detail_id = mmt.trx_source_line_id
3768 AND gtp.transaction_id1(+) = mmt.transaction_id
3769 AND gtp.batch_id(+) = mmt.transaction_source_id
3770 AND gtp.material_detail_id(+) = mmt.trx_source_line_id
3771 AND mmt.transaction_source_type_id = gme_common_pvt.g_txn_source_type /*Bug#6266714*/
3772 ORDER BY mmt.transaction_date,
3773 case md.line_type
3774 when -1 then 0
3775 when 2 then 1
3776 when 1 then 2
3777 end,
3778 mmt.transaction_id, mtln.lot_number) LOOP
3779 l_trans_rec.transaction_id := trans_rec.transaction_id;
3780 l_trans_rec.transaction_source_type_id := trans_rec.transaction_source_type_id;
3781 l_trans_rec.transaction_action_id := trans_rec.transaction_action_id;
3782 l_trans_rec.transaction_type_id := trans_rec.transaction_type_id;
3783 l_trans_rec.inventory_item_id := trans_rec.inventory_item_id;
3784 l_trans_rec.organization_id := trans_rec.organization_id;
3785 l_trans_rec.lot_number := trans_rec.lot_number;
3786 l_trans_rec.transaction_date := trans_rec.transaction_date;
3787 l_trans_rec.primary_quantity := trans_rec.primary_quantity;
3788 l_trans_rec.primary_uom := trans_rec.primary_uom_code;
3789 l_trans_rec.doc_qty := p_qty_tbl(trans_rec.transaction_id||'@'||trans_rec.lot_number).doc_qty;
3790 l_trans_rec.doc_uom := trans_rec.doc_uom;
3791 l_trans_rec.transaction_source_id := trans_rec.transaction_source_id;
3792 l_trans_rec.trx_source_line_id := trans_rec.trx_source_line_id;
3793 l_trans_rec.reverse_id := trans_rec.reverse_id;
3794 l_trans_rec.line_type := trans_rec.line_type;
3798
3795 l_trans_rec.last_updated_by := trans_rec.last_updated_by;
3796 l_trans_rec.created_by := trans_rec.created_by;
3797 l_trans_rec.last_update_login := trans_rec.last_update_login;
3799 IF trans_rec.transaction_action_id in (1, 27) THEN
3800 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3801 gme_debug.put_line('in process_txns for (INGR) actionID: ' || trans_rec.transaction_action_id || '...calling GMF outgoing layers'); --xxxremove
3802 gme_debug.put_line('in process_txns for (INGR) actionID: ' || trans_rec.transaction_action_id || '...calling GMF outgoing layers');
3803 END IF;
3804
3805 l_trans_rec.doc_qty := -1 * l_trans_rec.doc_qty;
3806
3807 gmf_layers.Create_outgoing_Layers
3808 ( p_api_version => 1.0,
3809 p_init_msg_list => FND_API.G_FALSE,
3810 p_tran_rec => l_trans_rec,
3811 x_return_status => x_return_status,
3812 x_msg_count => x_msg_count,
3813 x_msg_data => x_msg_data);
3814 ELSIF trans_rec.transaction_action_id in (31, 32) THEN
3815 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3816 gme_debug.put_line('in process_txns for (PROD) actionID: ' || trans_rec.transaction_action_id || '...calling GMF incoming layers');
3817 END IF;
3818
3819 --Bug#6322202 Added the below condition for byproducts
3820 IF trans_rec.line_type <> 1 THEN
3821 gmf_layers.Create_outgoing_Layers
3822 ( p_api_version => 1.0,
3823 p_init_msg_list => FND_API.G_FALSE,
3824 p_tran_rec => l_trans_rec,
3825 x_return_status => x_return_status,
3826 x_msg_count => x_msg_count,
3827 x_msg_data => x_msg_data);
3828 ELSE
3829 gmf_layers.Create_Incoming_Layers
3830 ( p_api_version => 1.0,
3831 p_init_msg_list => FND_API.G_FALSE,
3832 p_tran_rec => l_trans_rec,
3833 x_return_status => x_return_status,
3834 x_msg_count => x_msg_count,
3835 x_msg_data => x_msg_data);
3836 END IF;
3837 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3838 gme_debug.put_line('in process_txns for (PROD) actionID: ' || trans_rec.transaction_action_id || '...after calling GMF incoming layers...status: ' || x_return_status); --xxxremove
3839 END IF;
3840 ELSIF trans_rec.transaction_action_id in (33, 34) THEN
3841 NULL;
3842 END IF;
3843 END LOOP;
3844 END IF;
3845 p_qty_tbl.delete();
3846 /* Bug 5903208 End GMF code */
3847
3848 IF x_return_status = 'S' THEN
3849 gme_common_pvt.g_transaction_header_id := NULL;
3850 gme_common_pvt.g_batch_status_check := fnd_api.g_true;
3851 END IF;
3852 /* end temporary */
3853
3854 IF (l_return = 0) AND (fnd_api.to_boolean (p_commit) ) THEN
3855 --empty the quantity tree cache
3856 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3857 gme_debug.put_line
3858 ( g_pkg_name
3859 || '.'
3860 || l_api_name
3861 || ':'
3862 || 'Calling Inv_Quantity_Tree_Pub.clear_quantity_cache');
3863 END IF;
3864
3865 inv_quantity_tree_pub.clear_quantity_cache;
3866 END IF;
3867
3868 gme_common_pvt.reset_txn_hdr_tbl; -- nsinghi bug#5176319
3869
3870 IF (NVL (g_debug, 0) IN
3871 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
3872 gme_debug.put_line ( g_pkg_name
3873 || '.'
3874 || l_api_name
3875 || ':'
3876 || 'Exiting with '
3877 || x_return_status);
3878 END IF;
3879
3880 EXCEPTION
3881 WHEN fnd_api.g_exc_error THEN
3882 x_return_status := fnd_api.g_ret_sts_error;
3883 fnd_msg_pub.count_and_get (p_count => x_msg_count
3884 ,p_data => x_msg_data);
3885 WHEN fnd_api.g_exc_unexpected_error THEN
3886 x_return_status := fnd_api.g_ret_sts_unexp_error;
3887
3888 IF (NVL (g_debug, 0) > 0) THEN
3889 gme_debug.put_line ( g_pkg_name
3890 || '.'
3891 || l_api_name
3892 || ':'
3893 || 'UNEXPECTED:'
3894 || SQLERRM);
3895 END IF;
3896
3897 fnd_msg_pub.count_and_get (p_count => x_msg_count
3898 ,p_data => x_msg_data);
3899 WHEN OTHERS THEN
3900 x_return_status := fnd_api.g_ret_sts_unexp_error;
3901
3902 IF (NVL (g_debug, 0) > 0) THEN
3903 gme_debug.put_line ( g_pkg_name
3904 || '.'
3905 || l_api_name
3906 || ':'
3907 || 'OTHERS:'
3908 || SQLERRM);
3909 END IF;
3910
3911 fnd_msg_pub.count_and_get (p_count => x_msg_count
3912 ,p_data => x_msg_data);
3916 | PROCEDURE NAME
3913 END process_transactions;
3914
3915 /* +==========================================================================+
3917 | query_quantities
3918 |
3919 | USAGE
3920 | Query quantities at a level specified by the input
3921 |
3922 | ARGUMENTS
3923 | p_api_version API Version of this procedure. Current version is 1.0
3924 | p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to determine whether to Initialize message list or not |
3925 | x_return_status Returns the status to indicate success or failure of execution
3926 | x_msg_count Returns number of error message in the error message stack in case of failure
3927 | x_msg_data Returns the error message in case of failure
3928 |
3929 | RETURNS
3930 | returns via x_ OUT parameters
3931 |
3932 | HISTORY
3933 | Created 07-Mar-05 Jalaj Srivastava
3934 |
3935 +==========================================================================+ */
3936 PROCEDURE query_quantities (
3937 p_api_version_number IN NUMBER := 1
3938 ,p_init_msg_lst IN VARCHAR2
3939 DEFAULT fnd_api.g_false
3940 ,x_return_status OUT NOCOPY VARCHAR2
3941 ,x_msg_count OUT NOCOPY NUMBER
3942 ,x_msg_data OUT NOCOPY VARCHAR2
3943 ,p_organization_id IN NUMBER
3944 ,p_inventory_item_id IN NUMBER
3945 ,p_tree_mode IN INTEGER
3946 ,p_is_serial_control IN BOOLEAN DEFAULT FALSE
3947 ,p_grade_code IN VARCHAR2
3948 ,p_demand_source_type_id IN NUMBER
3949 DEFAULT gme_common_pvt.g_txn_source_type
3950 ,p_demand_source_header_id IN NUMBER DEFAULT -9999
3951 ,p_demand_source_line_id IN NUMBER DEFAULT -9999
3952 ,p_demand_source_name IN VARCHAR2 DEFAULT NULL
3953 ,p_lot_expiration_date IN DATE DEFAULT NULL
3954 ,p_revision IN VARCHAR2
3955 ,p_lot_number IN VARCHAR2
3956 ,p_subinventory_code IN VARCHAR2
3957 ,p_locator_id IN NUMBER
3958 ,p_onhand_source IN NUMBER
3959 DEFAULT inv_quantity_tree_pvt.g_all_subs
3960 ,x_qoh OUT NOCOPY NUMBER
3961 ,x_rqoh OUT NOCOPY NUMBER
3962 ,x_qr OUT NOCOPY NUMBER
3963 ,x_qs OUT NOCOPY NUMBER
3964 ,x_att OUT NOCOPY NUMBER
3965 ,x_atr OUT NOCOPY NUMBER
3966 ,x_sqoh OUT NOCOPY NUMBER
3967 ,x_srqoh OUT NOCOPY NUMBER
3968 ,x_sqr OUT NOCOPY NUMBER
3969 ,x_sqs OUT NOCOPY NUMBER
3970 ,x_satt OUT NOCOPY NUMBER
3971 ,x_satr OUT NOCOPY NUMBER
3972 ,p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
3973 ,p_cost_group_id IN NUMBER DEFAULT NULL
3974 ,p_lpn_id IN NUMBER DEFAULT NULL
3975 ,p_transfer_locator_id IN NUMBER DEFAULT NULL)
3976 IS
3977 l_api_name CONSTANT VARCHAR2 (30) := 'QUERY_QUANTITIES';
3978 l_is_revision_control BOOLEAN := FALSE;
3979 l_is_lot_control BOOLEAN := FALSE;
3980 BEGIN
3981 IF (NVL (g_debug, 0) IN
3982 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
3983 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3984 || 'Entering');
3985 END IF;
3986
3987 IF (p_revision IS NOT NULL) THEN
3988 l_is_revision_control := TRUE;
3989 END IF;
3990
3991 IF (p_lot_number IS NOT NULL) THEN
3992 l_is_lot_control := TRUE;
3993 END IF;
3994
3995 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
3996 gme_debug.put_line ( g_pkg_name
3997 || '.'
3998 || l_api_name
3999 || ':'
4000 || 'Calling Inv_Quantity_Tree_Pub.Query_Quantities');
4001 gme_debug.put_line ( g_pkg_name
4002 || '.'
4003 || l_api_name
4004 || ':'
4005 || 'Organization ID = '
4006 || p_organization_id);
4007 gme_debug.put_line ( g_pkg_name
4008 || '.'
4009 || l_api_name
4010 || ':'
4011 || 'Inventory Item ID = '
4012 || p_inventory_item_id);
4013 gme_debug.put_line ( g_pkg_name
4014 || '.'
4015 || l_api_name
4016 || ':'
4017 || 'Subinventory = '
4018 || p_subinventory_code);
4019 gme_debug.put_line ( g_pkg_name
4020 || '.'
4024 || p_lot_number);
4021 || l_api_name
4022 || ':'
4023 || 'Lot Number = '
4025 gme_debug.put_line ( g_pkg_name
4026 || '.'
4027 || l_api_name
4028 || ':'
4029 || 'Locator ID = '
4030 || p_locator_id);
4031 gme_debug.put_line ( g_pkg_name
4032 || '.'
4033 || l_api_name
4034 || ':'
4035 || 'Item Revision = '
4036 || p_revision);
4037 gme_debug.put_line ( g_pkg_name
4038 || '.'
4039 || l_api_name
4040 || ':'
4041 || 'Tree Mode = '
4042 || p_tree_mode);
4043 gme_debug.put_line ( g_pkg_name
4044 || '.'
4045 || l_api_name
4046 || ':'
4047 || 'Grade = '
4048 || p_grade_code);
4049 gme_debug.put_line ( g_pkg_name
4050 || '.'
4051 || l_api_name
4052 || ':'
4053 || 'Lot Expiration Date = '
4054 || TO_CHAR (p_lot_expiration_date
4055 ,'MM/DD/YYYY HH24:MI:SS') );
4056 END IF;
4057
4058 inv_quantity_tree_pub.query_quantities
4059 (p_api_version_number => p_api_version_number
4060 ,p_init_msg_lst => p_init_msg_lst
4061 ,x_return_status => x_return_status
4062 ,x_msg_count => x_msg_count
4063 ,x_msg_data => x_msg_data
4064 ,p_organization_id => p_organization_id
4065 ,p_inventory_item_id => p_inventory_item_id
4066 ,p_tree_mode => p_tree_mode
4067 ,p_is_revision_control => l_is_revision_control
4068 ,p_is_lot_control => l_is_lot_control
4069 ,p_is_serial_control => p_is_serial_control
4070 ,p_grade_code => p_grade_code
4071 ,p_demand_source_type_id => p_demand_source_type_id
4072 ,p_demand_source_header_id => p_demand_source_header_id
4073 ,p_demand_source_line_id => p_demand_source_line_id
4074 ,p_demand_source_name => p_demand_source_name
4075 ,p_lot_expiration_date => p_lot_expiration_date
4076 ,p_revision => p_revision
4077 ,p_lot_number => p_lot_number
4078 ,p_subinventory_code => p_subinventory_code
4079 ,p_locator_id => p_locator_id
4080 ,p_onhand_source => p_onhand_source
4081 ,x_qoh => x_qoh
4082 ,x_rqoh => x_rqoh
4083 ,x_qr => x_qr
4084 ,x_qs => x_qs
4085 ,x_att => x_att
4086 ,x_atr => x_atr
4087 ,x_sqoh => x_sqoh
4088 ,x_srqoh => x_srqoh
4089 ,x_sqr => x_sqr
4090 ,x_sqs => x_sqs
4091 ,x_satt => x_satt
4092 ,x_satr => x_satr
4093 ,p_transfer_subinventory_code => p_transfer_subinventory_code
4094 ,p_cost_group_id => p_cost_group_id
4095 ,p_lpn_id => p_lpn_id
4096 ,p_transfer_locator_id => p_transfer_locator_id);
4097
4098 IF (NVL (g_debug, 0) IN
4099 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4100 gme_debug.put_line ( g_pkg_name
4101 || '.'
4102 || l_api_name
4103 || ':'
4104 || 'Exiting with '
4105 || x_return_status);
4106 END IF;
4107 EXCEPTION
4108 WHEN fnd_api.g_exc_error THEN
4109 x_return_status := fnd_api.g_ret_sts_error;
4110 fnd_msg_pub.count_and_get (p_count => x_msg_count
4111 ,p_data => x_msg_data);
4112 WHEN fnd_api.g_exc_unexpected_error THEN
4113 x_return_status := fnd_api.g_ret_sts_unexp_error;
4114
4115 IF (NVL (g_debug, 0) > 0) THEN
4116 gme_debug.put_line ( g_pkg_name
4117 || '.'
4118 || l_api_name
4119 || ':'
4120 || 'UNEXPECTED:'
4121 || SQLERRM);
4122 END IF;
4123
4124 fnd_msg_pub.count_and_get (p_count => x_msg_count
4128
4125 ,p_data => x_msg_data);
4126 WHEN OTHERS THEN
4127 x_return_status := fnd_api.g_ret_sts_unexp_error;
4129 IF (NVL (g_debug, 0) > 0) THEN
4130 gme_debug.put_line ( g_pkg_name
4131 || '.'
4132 || l_api_name
4133 || ':'
4134 || 'OTHERS:'
4135 || SQLERRM);
4136 END IF;
4137
4138 fnd_msg_pub.count_and_get (p_count => x_msg_count
4139 ,p_data => x_msg_data);
4140 END query_quantities;
4141
4142 /* +==========================================================================+
4143 | PROCEDURE NAME
4144 | update_quantities
4145 |
4146 | USAGE
4147 | Update quantity at the level specified by the input and
4148 | return the quantities at the level after the update
4149 |
4150 | ARGUMENTS
4151 | p_api_version API Version of this procedure. Current version is 1.0
4152 | p_init_msg_list fnd_api.g_false or fnd_api.g_true is passed as input to determine whether to Initialize message list or not |
4153 | x_return_status Returns the status to indicate success or failure of execution
4154 | x_msg_count Returns number of error message in the error message stack in case of failure
4155 | x_msg_data Returns the error message in case of failure
4156 |
4157 | RETURNS
4158 | returns via x_ OUT parameters
4159 |
4160 | HISTORY
4161 | Created 07-Mar-05 Jalaj Srivastava
4162 |
4163 +==========================================================================+ */
4164 PROCEDURE update_quantities (
4165 p_api_version_number IN NUMBER := 1
4166 ,p_init_msg_lst IN VARCHAR2
4167 DEFAULT fnd_api.g_false
4168 ,x_return_status OUT NOCOPY VARCHAR2
4169 ,x_msg_count OUT NOCOPY NUMBER
4170 ,x_msg_data OUT NOCOPY VARCHAR2
4171 ,p_organization_id IN NUMBER
4172 ,p_inventory_item_id IN NUMBER
4173 ,p_tree_mode IN INTEGER
4174 ,p_is_serial_control IN BOOLEAN := FALSE
4175 ,p_demand_source_type_id IN NUMBER
4176 DEFAULT gme_common_pvt.g_txn_source_type
4177 ,p_demand_source_header_id IN NUMBER DEFAULT -9999
4178 ,p_demand_source_line_id IN NUMBER DEFAULT -9999
4179 ,p_demand_source_name IN VARCHAR2 DEFAULT NULL
4180 ,p_lot_expiration_date IN DATE DEFAULT NULL
4181 ,p_revision IN VARCHAR2 DEFAULT NULL
4182 ,p_lot_number IN VARCHAR2 DEFAULT NULL
4183 ,p_subinventory_code IN VARCHAR2 DEFAULT NULL
4184 ,p_locator_id IN NUMBER DEFAULT NULL
4185 ,p_grade_code IN VARCHAR2 DEFAULT NULL
4186 ,p_primary_quantity IN NUMBER
4187 ,p_quantity_type IN INTEGER
4188 ,p_secondary_quantity IN NUMBER
4189 ,p_onhand_source IN NUMBER
4190 DEFAULT inv_quantity_tree_pvt.g_all_subs
4191 ,x_qoh OUT NOCOPY NUMBER
4192 ,x_rqoh OUT NOCOPY NUMBER
4193 ,x_qr OUT NOCOPY NUMBER
4194 ,x_qs OUT NOCOPY NUMBER
4195 ,x_att OUT NOCOPY NUMBER
4196 ,x_atr OUT NOCOPY NUMBER
4197 ,x_sqoh OUT NOCOPY NUMBER
4198 ,x_srqoh OUT NOCOPY NUMBER
4199 ,x_sqr OUT NOCOPY NUMBER
4200 ,x_sqs OUT NOCOPY NUMBER
4201 ,x_satt OUT NOCOPY NUMBER
4202 ,x_satr OUT NOCOPY NUMBER
4203 ,p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
4204 ,p_cost_group_id IN NUMBER DEFAULT NULL
4205 ,p_containerized IN NUMBER
4206 DEFAULT inv_quantity_tree_pvt.g_containerized_false
4207 ,p_lpn_id IN NUMBER DEFAULT NULL
4208 ,p_transfer_locator_id IN NUMBER DEFAULT NULL)
4209 IS
4210 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_QUANTITIES';
4211 l_is_revision_control BOOLEAN := FALSE;
4212 l_is_lot_control BOOLEAN := FALSE;
4213 BEGIN
4214 IF (NVL (g_debug, 0) IN
4215 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4216 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4217 || 'Entering');
4218 END IF;
4219
4220 IF (p_revision IS NOT NULL) THEN
4221 l_is_revision_control := TRUE;
4222 END IF;
4223
4224 IF (p_lot_number IS NOT NULL) THEN
4225 l_is_lot_control := TRUE;
4226 END IF;
4227
4228 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4229 gme_debug.put_line
4230 ( g_pkg_name
4234 || 'Calling Inv_Quantity_Tree_Pub.Update_Quantities');
4231 || '.'
4232 || l_api_name
4233 || ':'
4235 gme_debug.put_line ( g_pkg_name
4236 || '.'
4237 || l_api_name
4238 || ':'
4239 || 'Organization ID = '
4240 || p_organization_id);
4241 gme_debug.put_line ( g_pkg_name
4242 || '.'
4243 || l_api_name
4244 || ':'
4245 || 'Inventory Item ID = '
4246 || p_inventory_item_id);
4247 gme_debug.put_line ( g_pkg_name
4248 || '.'
4249 || l_api_name
4250 || ':'
4251 || 'Subinventory = '
4252 || p_subinventory_code);
4253 gme_debug.put_line ( g_pkg_name
4254 || '.'
4255 || l_api_name
4256 || ':'
4257 || 'Lot Number = '
4258 || p_lot_number);
4259 gme_debug.put_line ( g_pkg_name
4260 || '.'
4261 || l_api_name
4262 || ':'
4263 || 'Locator ID = '
4264 || p_locator_id);
4265 gme_debug.put_line ( g_pkg_name
4266 || '.'
4267 || l_api_name
4268 || ':'
4269 || 'Item Revision = '
4270 || p_revision);
4271 gme_debug.put_line ( g_pkg_name
4272 || '.'
4273 || l_api_name
4274 || ':'
4275 || 'Tree Mode = '
4276 || p_tree_mode);
4277 gme_debug.put_line ( g_pkg_name
4278 || '.'
4279 || l_api_name
4280 || ':'
4281 || 'Grade = '
4282 || p_grade_code);
4283 gme_debug.put_line ( g_pkg_name
4284 || '.'
4285 || l_api_name
4286 || ':'
4287 || 'Lot Expiration Date = '
4288 || TO_CHAR (p_lot_expiration_date
4289 ,'MM/DD/YYYY HH24:MI:SS') );
4290 gme_debug.put_line ( g_pkg_name
4291 || '.'
4292 || l_api_name
4293 || ':'
4294 || 'Quantity Type = '
4295 || p_quantity_type);
4296 gme_debug.put_line ( g_pkg_name
4297 || '.'
4298 || l_api_name
4299 || ':'
4300 || 'Primary Quantity = '
4301 || p_primary_quantity);
4302 gme_debug.put_line ( g_pkg_name
4303 || '.'
4304 || l_api_name
4305 || ':'
4306 || 'Secondary Quantity = '
4307 || p_secondary_quantity);
4308 END IF;
4309
4310 inv_quantity_tree_pub.update_quantities
4311 (p_api_version_number => p_api_version_number
4312 ,p_init_msg_lst => p_init_msg_lst
4313 ,x_return_status => x_return_status
4314 ,x_msg_count => x_msg_count
4315 ,x_msg_data => x_msg_data
4316 ,p_organization_id => p_organization_id
4317 ,p_inventory_item_id => p_inventory_item_id
4318 ,p_tree_mode => p_tree_mode
4319 ,p_is_revision_control => l_is_revision_control
4320 ,p_is_lot_control => l_is_lot_control
4321 ,p_is_serial_control => p_is_serial_control
4322 ,p_grade_code => p_grade_code
4323 ,p_demand_source_type_id => p_demand_source_type_id
4324 ,p_demand_source_header_id => p_demand_source_header_id
4325 ,p_demand_source_line_id => p_demand_source_line_id
4326 ,p_demand_source_name => p_demand_source_name
4327 ,p_lot_expiration_date => p_lot_expiration_date
4328 ,p_revision => p_revision
4329 ,p_lot_number => p_lot_number
4330 ,p_subinventory_code => p_subinventory_code
4331 ,p_locator_id => p_locator_id
4332 ,p_onhand_source => p_onhand_source
4333 ,p_primary_quantity => p_primary_quantity
4334 ,p_quantity_type => p_quantity_type
4338 ,x_qr => x_qr
4335 ,p_secondary_quantity => p_secondary_quantity
4336 ,x_qoh => x_qoh
4337 ,x_rqoh => x_rqoh
4339 ,x_qs => x_qs
4340 ,x_att => x_att
4341 ,x_atr => x_atr
4342 ,x_sqoh => x_sqoh
4343 ,x_srqoh => x_srqoh
4344 ,x_sqr => x_sqr
4345 ,x_sqs => x_sqs
4346 ,x_satt => x_satt
4347 ,x_satr => x_satr
4348 ,p_transfer_subinventory_code => p_transfer_subinventory_code
4349 ,p_cost_group_id => p_cost_group_id
4350 ,p_lpn_id => p_lpn_id
4351 ,p_transfer_locator_id => p_transfer_locator_id
4352 ,p_containerized => p_containerized);
4353
4354 IF (NVL (g_debug, 0) IN
4355 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4356 gme_debug.put_line ( g_pkg_name
4357 || '.'
4358 || l_api_name
4359 || ':'
4360 || 'Exiting with '
4361 || x_return_status);
4362 END IF;
4363 EXCEPTION
4364 WHEN fnd_api.g_exc_error THEN
4365 x_return_status := fnd_api.g_ret_sts_error;
4366 fnd_msg_pub.count_and_get (p_count => x_msg_count
4367 ,p_data => x_msg_data);
4368 WHEN fnd_api.g_exc_unexpected_error THEN
4369 x_return_status := fnd_api.g_ret_sts_unexp_error;
4370
4371 IF (NVL (g_debug, 0) > 0) THEN
4372 gme_debug.put_line ( g_pkg_name
4373 || '.'
4374 || l_api_name
4375 || ':'
4376 || 'UNEXPECTED:'
4377 || SQLERRM);
4378 END IF;
4379
4380 fnd_msg_pub.count_and_get (p_count => x_msg_count
4381 ,p_data => x_msg_data);
4382 WHEN OTHERS THEN
4383 x_return_status := fnd_api.g_ret_sts_unexp_error;
4384
4385 IF (NVL (g_debug, 0) > 0) THEN
4386 gme_debug.put_line ( g_pkg_name
4387 || '.'
4388 || l_api_name
4389 || ':'
4390 || 'OTHERS:'
4391 || SQLERRM);
4392 END IF;
4393
4394 fnd_msg_pub.count_and_get (p_count => x_msg_count
4395 ,p_data => x_msg_data);
4396 END update_quantities;
4397
4398 /* Bug 4929610 Added fucntion */
4399 /* +==========================================================================+
4400 | FUNCTION NAME
4401 | is_lot_expired
4402 |
4403 | USAGE
4404 |
4405 |
4406 | ARGUMENTS
4407 | p_organization_id
4408 | p_lot_number
4409 | p_inventory_item_id
4410 | p_date
4411 | RETURNS
4412 | returns BOOLEAN, TRUE if lot expired
4413 |
4414 | HISTORY
4415 | Created 16-Feb-06 Chandrashekar Tiruvidula
4416 |
4417 +==========================================================================+ */
4418 FUNCTION is_lot_expired (p_organization_id IN NUMBER,
4419 p_inventory_item_id IN NUMBER,
4420 p_lot_number IN VARCHAR2,
4421 p_date IN DATE) RETURN BOOLEAN IS
4422 l_expire_date DATE;
4423 l_api_name CONSTANT VARCHAR2 (30) := 'is_lot_expired';
4424 CURSOR Cur_lot_expire IS
4425 SELECT expiration_date
4426 FROM mtl_lot_numbers
4427 WHERE organization_id = p_organization_id
4428 AND inventory_item_id = p_inventory_item_id
4429 AND lot_number = p_lot_number;
4430 BEGIN
4431 OPEN Cur_lot_expire;
4432 FETCH Cur_lot_expire INTO l_expire_date;
4433 CLOSE Cur_lot_expire;
4434 IF l_expire_date IS NULL THEN
4435 RETURN FALSE;
4436 ELSE
4437 IF l_expire_date < NVL(p_date, SYSDATE) THEN
4438 gme_common_pvt.log_message(p_product_code => 'INV', p_message_code => 'INV_LOT_EXPIRED');
4439 RETURN TRUE;
4440 END IF;
4441 END IF;
4442 RETURN FALSE;
4443 EXCEPTION
4444 WHEN OTHERS THEN
4445 IF (NVL (g_debug, 0) > 0) THEN
4446 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
4447 END IF;
4448 RETURN FALSE;
4449 END is_lot_expired;
4450
4451 PROCEDURE insert_txn_inter_hdr(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
4452 x_return_status OUT NOCOPY VARCHAR2) IS
4453 l_api_name CONSTANT VARCHAR2 (30) := 'insert_txn_inter_hdr';
4454 BEGIN
4455 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4456 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
4460 (transaction_interface_id
4457 END IF;
4458 x_return_status := fnd_api.g_ret_sts_success;
4459 INSERT INTO mtl_transactions_interface
4461 ,transaction_header_id
4462 ,source_code
4463 ,source_header_id
4464 ,lock_flag
4465 ,transaction_mode
4466 ,process_flag
4467 ,validation_required
4468 ,source_line_id
4469 ,transaction_source_id
4470 ,trx_source_line_id
4471 ,last_updated_by
4472 ,last_update_login
4473 ,last_update_date
4474 ,creation_date
4475 ,created_by
4476 ,inventory_item_id
4477 ,revision
4478 ,organization_id
4479 ,transaction_date
4480 ,transaction_type_id
4481 ,transaction_action_id
4482 ,transaction_quantity
4483 ,primary_quantity
4484 ,secondary_transaction_quantity
4485 ,secondary_uom_code
4486 ,transaction_uom
4487 ,subinventory_code
4488 ,locator_id
4489 ,transaction_source_type_id
4490 ,wip_entity_type
4491 ,transaction_source_name
4492 ,transaction_reference
4493 ,reason_id
4494 ,transaction_batch_id
4495 ,transaction_batch_seq
4496 ,reservation_quantity
4497 ,transaction_sequence_id
4498 ,transfer_lpn_id)
4499 VALUES (p_mmti_rec.transaction_interface_id
4500 ,gme_common_pvt.g_transaction_header_id
4501 ,'OPM' -- source_code
4502 ,p_mmti_rec.transaction_source_id --source_header_id
4503 ,1 -- lock_flag
4504 ,2 -- transaction_mode
4505 ,1 -- (Yes) process_flag
4506 ,2 -- validation_required
4507 , NVL (p_mmti_rec.source_line_id, -99)-- transaction_id for reversal
4508 ,p_mmti_rec.transaction_source_id -- batch id
4509 ,p_mmti_rec.trx_source_line_id -- material detail id
4510 ,gme_common_pvt.g_user_ident --last_updated_by
4511 ,gme_common_pvt.g_user_ident -- last_update_login
4512 ,gme_common_pvt.g_timestamp --last_update_date
4513 ,gme_common_pvt.g_timestamp --creation_date
4514 ,gme_common_pvt.g_user_ident --created_by
4515 ,p_mmti_rec.inventory_item_id -- inventory_item_id
4516 ,p_mmti_rec.revision
4517 ,p_mmti_rec.organization_id --organization_id
4518 /* FPBug#4543872 rework
4519 removed defaulting the transaction date
4520 */
4521 ,p_mmti_rec.transaction_date
4522 ,p_mmti_rec.transaction_type_id
4523 , --(Batch Issue)transaction_type_id
4524 p_mmti_rec.transaction_action_id
4525 , --transaction_action_id
4526 p_mmti_rec.transaction_quantity
4527 , --transaction_quantity
4528 p_mmti_rec.primary_quantity
4529 , --primary_quantity
4530 p_mmti_rec.secondary_transaction_quantity -- secondary_quantity
4531 ,p_mmti_rec.secondary_uom_code -- secondary_uom_code
4532 , -- secondary_quantity
4533 p_mmti_rec.transaction_uom, --transaction_uom
4534 p_mmti_rec.subinventory_code
4535 , --subinventory_code
4536 p_mmti_rec.locator_id, --locator_id
4537 gme_common_pvt.g_txn_source_type
4538 , -- (Batch) transaction_source_type_id
4539 gme_common_pvt.g_wip_entity_type_batch -- (for batch) wip_entity_type
4540 ,p_mmti_rec.transaction_source_name -- transaction_source_name
4541 ,p_mmti_rec.transaction_reference
4542 ,p_mmti_rec.reason_id
4543 ,p_mmti_rec.transaction_batch_id -- must populate for seq
4544 ,p_mmti_rec.transaction_batch_seq
4545 ,p_mmti_rec.reservation_quantity
4546 ,p_mmti_rec.transaction_sequence_id
4547 ,p_mmti_rec.transfer_lpn_id);
4548 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4549 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Exiting');
4550 END IF;
4551 EXCEPTION
4552 WHEN OTHERS THEN
4553 x_return_status := fnd_api.g_ret_sts_unexp_error;
4554 IF (NVL (g_debug, 0) > 0) THEN
4555 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
4556 END IF;
4557 END insert_txn_inter_hdr;
4558
4559 -- nsinghi bug#5176319. Added this proc.
4560 /* +==========================================================================+
4561 | PROCEDURE NAME
4562 | get_mmt_transactions
4563 |
4564 | USAGE
4565 | Gets all transactions from mmt based on transaction_id passed. Unlike get_transactions,
4566 | this procedure does not check for enteries in gme_transaction_pairs
4567 |
4568 | ARGUMENTS
4569 | p_transaction_id -- transaction_id from mmt for fetch
4570 |
4571 | RETURNS
4572 |
4573 | returns via x_status OUT parameters
4574 | x_mmt_rec -- mtl_material_transactions rowtype
4575 | x_mmln_tbl -- table of mtl_trans_lots_number_tbl
4576 | HISTORY
4577 | Created 19-Jun-06 Namit S. Created
4578 |
4579 +==========================================================================+ */
4580 PROCEDURE get_mmt_transactions (
4581 p_transaction_id IN NUMBER
4582 ,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
4583 ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
4584 ,x_return_status OUT NOCOPY VARCHAR2)
4585 IS
4586
4587 CURSOR cur_get_transaction (v_transaction_id NUMBER)
4588 IS
4589 SELECT *
4590 FROM mtl_material_transactions mmt
4591 WHERE transaction_id = v_transaction_id;
4592
4593 CURSOR cur_get_lot_transaction (v_transaction_id NUMBER)
4594 IS
4595 SELECT *
4596 FROM mtl_transaction_lot_numbers
4597 WHERE transaction_id = v_transaction_id;
4598
4599 l_api_name CONSTANT VARCHAR2 (30) := 'GET_MMT_TRANSACTIONS';
4600 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
4601 l_transaction_id NUMBER;
4602 no_transaction_found EXCEPTION;
4603
4604 BEGIN
4605 x_return_status := fnd_api.g_ret_sts_success;
4606
4607 IF (g_debug <= gme_debug.g_log_statement) THEN
4608 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4609 || 'Entering with transaction '||p_transaction_id);
4610 END IF;
4611
4612 IF p_transaction_id IS NULL THEN
4613 gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
4614
4615 IF (g_debug <= gme_debug.g_log_statement) THEN
4616 gme_debug.put_line ( g_pkg_name
4617 || '.'
4618 || l_api_name
4619 || ':'
4620 || 'TRANSACTION ID NEEDED FOR RETRIEVAL');
4621 END IF;
4622 END IF;
4623
4624 l_transaction_id := p_transaction_id;
4625 OPEN cur_get_transaction (l_transaction_id);
4626 FETCH cur_get_transaction
4627 INTO x_mmt_rec;
4628 IF cur_get_transaction%FOUND THEN
4629 IF (g_debug <= gme_debug.g_log_statement) THEN
4630 gme_debug.put_line ( g_pkg_name
4631 || '.'
4632 || l_api_name
4633 || ':'
4634 || 'TRANSACTIONS found for '
4635 || l_transaction_id);
4636 END IF;
4637 get_lot_trans (p_transaction_id => l_transaction_id
4638 ,x_mmln_tbl => x_mmln_tbl
4639 ,x_return_status => l_return_status);
4640
4641 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4642 IF (g_debug <= gme_debug.g_log_statement) THEN
4643 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4644 || 'error from get lot trans');
4645 END IF;
4646 RAISE fnd_api.g_exc_error;
4647 END IF;
4648 ELSE /* IF cur_get_transaction%FOUND THEN */
4652 END IF;
4649 CLOSE cur_get_transaction;
4650 gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
4651 RAISE fnd_api.g_exc_error;
4653 CLOSE cur_get_transaction;
4654
4655 IF (g_debug <= gme_debug.g_log_statement) THEN
4656 gme_debug.put_line ( g_pkg_name
4657 || '.'
4658 || l_api_name
4659 || ':'
4660 || 'TRANSACTION '
4661 || x_mmt_rec.transaction_id);
4662 END IF;
4663
4664 IF (g_debug <= gme_debug.g_log_statement) THEN
4665 gme_debug.put_line ( g_pkg_name
4666 || '.'
4667 || l_api_name
4668 || ':'
4669 || 'Exiting with '
4670 || x_return_status);
4671 END IF;
4672 EXCEPTION
4673 WHEN fnd_api.g_exc_error THEN
4674 x_return_status := fnd_api.g_ret_sts_error;
4675 WHEN fnd_api.g_exc_unexpected_error THEN
4676 x_return_status := fnd_api.g_ret_sts_unexp_error;
4677 WHEN OTHERS THEN
4678 x_return_status := fnd_api.g_ret_sts_unexp_error;
4679 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4680
4681 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
4682 gme_debug.put_line ( g_pkg_name
4683 || '.'
4684 || l_api_name
4685 || ':'
4686 || 'WHEN OTHERS:'
4687 || SQLERRM);
4688 END IF;
4689 END get_mmt_transactions;
4690
4691 /* Bug 5358129 Added procedure */
4692 PROCEDURE validate_lot_for_ing(p_organization_id IN NUMBER,
4693 p_inventory_item_id IN NUMBER,
4694 p_lot_number IN VARCHAR2,
4695 x_return_status OUT NOCOPY VARCHAR2) IS
4696 CURSOR Cur_get_lot IS
4697 SELECT expiration_date
4698 FROM mtl_lot_numbers
4699 WHERE organization_id = p_organization_id
4700 AND inventory_item_id = p_inventory_item_id
4701 AND lot_number = p_lot_number;
4702 l_api_name CONSTANT VARCHAR2(30) := 'validate_lot_for_ing';
4703 l_date DATE;
4704 expired_lot EXCEPTION;
4705 invalid_lot EXCEPTION;
4706 BEGIN
4707 IF (g_debug <= gme_debug.g_log_procedure) THEN
4708 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Entering with organization_id = '||p_organization_id
4709 ||' inventory_item_id = '||p_inventory_item_id||' lot_number = '||p_lot_number);
4710 END IF;
4711 x_return_status := FND_API.G_RET_STS_SUCCESS;
4712 OPEN Cur_get_lot;
4713 FETCH Cur_get_lot INTO l_date;
4714 IF (Cur_get_lot%NOTFOUND) THEN
4715 CLOSE Cur_get_lot;
4716 RAISE invalid_lot;
4717 END IF;
4718 CLOSE Cur_get_lot;
4719 IF (l_date IS NOT NULL AND l_date < sysdate) THEN
4720 RAISE expired_lot;
4724 END IF;
4721 END IF;
4722 IF (g_debug <= gme_debug.g_log_procedure) THEN
4723 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Normal Exiting');
4725 EXCEPTION
4726 WHEN expired_lot THEN
4727 gme_common_pvt.log_message(p_message_code => 'INV_LOT_EXPIRED',
4728 p_product_code => 'INV');
4729 x_return_status := fnd_api.g_ret_sts_error;
4730 WHEN invalid_lot THEN
4731 x_return_status := fnd_api.g_ret_sts_unexp_error;
4732 gme_common_pvt.log_message(p_message_code => 'INV_INVALID_LOT',
4733 p_product_code => 'INV');
4734 WHEN OTHERS THEN
4735 x_return_status := fnd_api.g_ret_sts_unexp_error;
4736 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4737 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
4738 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
4739 END IF;
4740 END validate_lot_for_ing;
4741
4742 /* Added for bug 5597385 */
4743 PROCEDURE gmo_pre_process_val(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
4744 p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
4745 p_mode IN VARCHAR2,
4746 x_return_status OUT NOCOPY VARCHAR2) IS
4747 CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER) IS
4748 SELECT *
4749 FROM mtl_system_items_b
4750 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
4751 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER) IS
4752 SELECT step_status
4753 FROM gme_batch_steps s, gme_batch_step_items i
4754 WHERE s.batchstep_id = i.batchstep_id
4755 AND i.material_detail_id = v_matl_dtl_id;
4756 l_mat_dtl_rec gme_material_details%ROWTYPE;
4757 l_batch_hdr_rec gme_batch_header%ROWTYPE;
4758 l_item_rec mtl_system_items_b%ROWTYPE;
4759 l_available_qty NUMBER;
4760 l_step_status NUMBER;
4761 l_rel_type NUMBER;
4762 l_return_status VARCHAR2(1);
4763 item_not_found EXCEPTION;
4764 not_valid_trans EXCEPTION;
4765 lot_val_err EXCEPTION;
4766 l_api_name CONSTANT VARCHAR2(30) := 'GMO_PRE_PROCESS_VAL';
4767 BEGIN
4768 -- Initially let us assign the return status to success
4769 x_return_status := fnd_api.g_ret_sts_success;
4770 IF (g_debug <= gme_debug.g_log_statement) THEN
4771 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Entering');
4772 END IF;
4773 IF (g_debug <= gme_debug.g_log_statement) THEN
4774 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_header_id: '||p_mmti_rec.transaction_header_id);
4775 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.subinventory_code: '||p_mmti_rec.subinventory_code);
4776 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_uom: '||p_mmti_rec.transaction_uom);
4777 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.inventory_item_id: '||p_mmti_rec.inventory_item_id);
4778 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.trx_source_line_id: '||p_mmti_rec.trx_source_line_id);
4779 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.revision: '||p_mmti_rec.revision);
4780 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_source_id: '||p_mmti_rec.transaction_source_id);
4781 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.locator_id: '||p_mmti_rec.locator_id);
4782 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_type_id: '||p_mmti_rec.transaction_type_id);
4783 END IF;
4784 IF p_mmti_rec.transaction_source_id IS NOT NULL THEN
4785 l_batch_hdr_rec.batch_id := p_mmti_rec.transaction_source_id;
4786 IF NOT gme_batch_header_dbl.fetch_row(l_batch_hdr_rec, l_batch_hdr_rec) THEN
4787 RAISE fnd_api.g_exc_error;
4788 END IF;
4789 ELSE
4790 RAISE fnd_api.g_exc_error;
4791 END IF; -- transaction_source_id IS NOT NULL
4792
4793 IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
4794 IF p_mmti_rec.trx_source_line_id IS NOT NULL THEN
4795 l_mat_dtl_rec.material_detail_id := p_mmti_rec.trx_source_line_id;
4796 IF NOT gme_material_details_dbl.fetch_row(l_mat_dtl_rec, l_mat_dtl_rec) THEN
4797 RAISE fnd_api.g_exc_error;
4798 END IF; -- material fetch
4799 ELSE
4800 RAISE fnd_api.g_exc_error;
4801 END IF; -- trx_source_line_id IS NOT NULL
4802 IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
4803 IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
4804 gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
4805 RAISE fnd_api.g_exc_error;
4806 END IF;
4807 -- Check for step status in case the item is associated to a step.
4808 l_rel_type := gme_common_pvt.is_material_auto_release(l_mat_dtl_rec.material_detail_id);
4809 IF (l_rel_type = gme_common_pvt.g_mtl_autobystep_release) THEN
4810 OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
4811 FETCH Cur_associated_step INTO l_step_status;
4812 CLOSE Cur_associated_step;
4813 IF l_step_status NOT IN (2,3) THEN
4814 gme_common_pvt.log_message ('GME_API_INVALID_STEP_STATUS');
4815 RAISE fnd_api.g_exc_error;
4819 IF (l_rel_type = gme_common_pvt.g_mtl_auto_release AND l_mat_dtl_rec.line_type IN (1,2) AND l_mat_dtl_rec.phantom_line_id IS NULL) THEN
4816 END IF;
4817 END IF; -- IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN
4818 -- check for item release type for products
4820 IF l_batch_hdr_rec.batch_status <> 3 THEN
4821 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS');
4822 RAISE fnd_api.g_exc_error;
4823 END IF;
4824 END IF;
4825 END IF; -- gme_common_pvt.g_batch_status_check
4826 IF (l_mat_dtl_rec.line_type <> -1 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_ing_issue, gme_common_pvt.g_ing_return))
4827 OR (l_mat_dtl_rec.line_type <> 1 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_prod_completion, gme_common_pvt.g_prod_return))
4828 OR (l_mat_dtl_rec.line_type <> 2 AND p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_byprod_completion, gme_common_pvt.g_byprod_return)) THEN
4829 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
4830 RAISE fnd_api.g_exc_error;
4831 END IF;
4832 -- get the item propertites
4833 OPEN cur_get_item_rec (p_mmti_rec.inventory_item_id, p_mmti_rec.organization_id);
4834 FETCH cur_get_item_rec INTO l_item_rec;
4835 IF cur_get_item_rec%NOTFOUND THEN
4836 CLOSE cur_get_item_rec;
4837 gme_common_pvt.log_message ('PM_INVALID_ITEM');
4838 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4839 gme_debug.put_line('Item cursor fetch no record in mtl_system_items_b: ');
4840 gme_debug.put_line('inventory_item_id = '|| TO_CHAR (p_mmti_rec.inventory_item_id));
4841 gme_debug.put_line('organization_id = '|| TO_CHAR (p_mmti_rec.organization_id));
4842 END IF;
4843 RAISE item_not_found;
4844 END IF;
4845 CLOSE cur_get_item_rec;
4846 IF (g_debug <= gme_debug.g_log_statement) THEN
4847 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item lot_control Code: '|| l_item_rec.lot_control_code);
4848 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item loct_control Code: '|| l_item_rec.location_control_code);
4849 END IF;
4850
4851 /* Bug 5358129 for ingredients lots should exist */
4852 IF (l_mat_dtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mat_dtl_rec.phantom_type = 0 AND l_item_rec.lot_control_code = 2) THEN
4853 FOR i IN 1..p_mmli_tbl.COUNT LOOP
4854 gme_transactions_pvt.validate_lot_for_ing(p_organization_id => p_mmti_rec.organization_id,
4855 p_inventory_item_id => p_mmti_rec.inventory_item_id,
4856 p_lot_number => p_mmli_tbl(i).lot_number,
4857 x_return_status => l_return_status);
4858 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
4859 RAISE lot_val_err;
4860 END IF;
4861 END LOOP;
4862 END IF;
4863 -- if return transaction then check qty was issued and return not more than issued qty
4864 IF p_mmti_rec.transaction_type_id IN (gme_common_pvt.g_byprod_return, gme_common_pvt.g_prod_return, gme_common_pvt.g_ing_return) THEN
4865 IF (g_debug <= gme_debug.g_log_statement) THEN
4866 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'Return transaction for : '||p_mmti_rec.transaction_type_id);
4867 END IF;
4868 IF l_item_rec.lot_control_code = 1 THEN
4869 IF (g_debug <= gme_debug.g_log_statement) THEN
4870 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item is NOT lot_control: '|| l_item_rec.lot_control_code);
4871 END IF;
4872 get_returnable_qty(p_mmti_rec => p_mmti_rec
4873 ,p_lot_number => NULL
4874 ,p_lot_control => l_item_rec.lot_control_code
4875 ,x_available_qty => l_available_qty
4876 ,x_return_status => x_return_status);
4877 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4878 RAISE fnd_api.g_exc_unexpected_error;
4879 END IF;
4880 IF (g_debug <= gme_debug.g_log_statement) THEN
4881 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning Qty '|| p_mmti_rec.transaction_quantity);
4882 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
4883 END IF;
4884 IF ABS (l_available_qty) < ABS (p_mmti_rec.transaction_quantity) THEN
4885 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
4886 RAISE fnd_api.g_exc_error;
4887 END IF;
4888 ELSE /* Lot Control */
4889 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4890 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item is lot control: '|| l_item_rec.lot_control_code);
4891 END IF;
4892 FOR i IN 1..p_mmli_tbl.COUNT LOOP
4893 get_returnable_qty(p_mmti_rec => p_mmti_rec
4894 ,p_lot_number => p_mmli_tbl(i).lot_number
4895 ,p_lot_control => l_item_rec.lot_control_code
4896 ,x_available_qty => l_available_qty
4897 ,x_return_status => x_return_status);
4898 IF x_return_Status <> fnd_api.g_ret_sts_success THEN
4899 RAISE fnd_api.g_exc_unexpected_error;
4900 END IF;
4901 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
4902 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning qty: '|| p_mmli_tbl(i).transaction_quantity);
4903 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
4904 END IF;
4905 IF ABS(l_available_qty) < ABS(p_mmli_tbl(i).transaction_quantity) THEN
4906 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
4907 RAISE fnd_api.g_exc_error;
4908 END IF;
4909 END LOOP;
4910 END IF; /* IF l_item_rec.lot_control_code = 1 THEN */
4911 END IF; /* IF transaction_type_id in RETURNS */
4912 END IF; /* update_inventory_ind = 'Y' */
4913 IF (g_debug <= gme_debug.g_log_statement) THEN
4914 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Exiting with '|| x_return_status);
4915 END IF;
4916 EXCEPTION
4917 WHEN lot_val_err THEN
4918 x_return_status := l_return_status;
4919 WHEN fnd_api.g_exc_error THEN
4920 x_return_status := fnd_api.g_ret_sts_error;
4921 WHEN fnd_api.g_exc_unexpected_error THEN
4922 x_return_status := fnd_api.g_ret_sts_unexp_error;
4923 WHEN OTHERS THEN
4924 x_return_status := fnd_api.g_ret_sts_unexp_error;
4925 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4926 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
4927 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
4928 END IF;
4929 END gmo_pre_process_val;
4930 END gme_transactions_pvt;