[Home] [Help]
PACKAGE BODY: APPS.GME_TRANSACTIONS_PVT
Source
1 PACKAGE BODY gme_transactions_pvt AS
2 /* $Header: GMEVPTXB.pls 120.69.12020000.8 2013/02/06 19:06:52 gmurator ship $ */
3 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
4 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_transactions_PVT';
5 /*
6 REM *********************************************************************
7 REM *
8 REM * FILE: GMEVPTXB.pls
9 REM * PURPOSE: Package Body for the GME batch transactions api
10 REM * AUTHOR: Pawan Kumar
11 REM * DATE: 2 May 2005
12 REM * HISTORY:
13 REM * ========
14 REM *
15 REM *
16 REM * Archana Mundhe Bug 6437252 - LPN support
17 REM * Modified insert_txn_inter_hdr to insert lpn_id , transfer_lpn_id into
18 REM * mtl_transactions_interface table
19 REM * Modiied delete_material_txn to populate lpn_id or transfer_lpn_id
20 REM * into mtl_transactions_interface based on the transaction type
21 REM * Swapna K Bug 7226474
22 REM * Modified build_txn_inter_lot to insert attribute columns into
23 REM * mtl_transactions_interface table
24 REM * Archana Mundhe Bug 7385309
25 REM * Modified procedure update_material_txn to create the transaction
26 REM * first and then delete.
27
28 REM * G. Muratore 24-Dec-2008 Bug 7626742/7423041
29 REM * Backout one piece of fix from 7385309 - Do not clear the cache.
30 REM * Procedure: query_quantities
31
32 REM * G. Muratore 29-Dec-2008 Bug 7623144
33 REM * Add 'C_', 'D_' and 'N_' lot attribute columns plus lot_attribute_category.
34 REM * Procedure: build_txn_inter_lot
35 REM * Kbanddyo 21-Jan-2009 Bug 7720970
36 REM * Procedure : process_transactions
37 REM * Swapna k 18-MAR-09 Bug 8300015
38 REM * Added p_phantom_line_id parameter to the get_mat_txns procedure.
39
40 REM * G. Muratore 26-MAY-2009 Bug 8453485
41 REM * Added dynamically derived column rev_order_column to help us in order by clause.
42 REM * This will aid in handling Product Yield reversals first for layer sequencing for GMF.
43 REM * Procedure: process_transactions
44
45 REM * Apeksha Mishra 21-Sep-2009 Bug 8605909
46 REM * Added the call to function gme_common_pvt.check_close_period to check whether
47 REM * the period is closed or not.
48 REM * Procedure: delete_material_txn
49
50 REM * G. Muratore 05-AUG-2009 Bug 8639523 (rework of 7385309 for ingreds)
51 REM * Resequence calls for transaction reversals depending on line_type.
52 REM * PROCEDURE: update_material_txn
53
54 REM * G. Muratore 01-Dec-2009 Bug 9170460
55 REM * Pass in subinventory and locator id to applicable function.
56 REM * PROCEDURE: build_txn_inter_lot
57
58 REM * G. Muratore 15-FEB-2010 Bug 9301755 (extension of 8639523/7385309)
59 rem * update_material_txn is an overloaded function so we need to make same fix again.
60 REM * Resequence calls for transaction reversals depending on line_type.
61 REM * PROCEDURE: update_material_txn
62
63 REM * G. Muratore 19-MAR-2010 Bug 8751983
64 REM * Added p_order_by parameter to allow fetching of transactions in reverse trans order.
65 REM * PROCEDURE: get_mat_trans
66
67 REM * G. Muratore 10-JUN-2010 Bug 9770408 / 9626176
68 REM * Evaluate the non divisible flag for a product yield being modified. This will be used to sequence
69 REM * the calls for deleting/creating transactions. Also to set the force the INV trans engine
70 REM * to process data in a desired order we now set transaction_batch_seq column in the temp table.
71 REM * PROCEDURE: build_txn_inter_hdr and update_material_txn
72
73 REM * Change for 9626176 will now allow yielding an auto rel prod in a wip batch to match the form.
74 REM * PROCEDURE: pre_process_val and gmo_pre_process_val
75
76 REM * S. Kommineni 10-JUN-2010 Bug 9717803 (Included with patch 9770408)
77 REM * Insert into transaction pairs table prior to creating new transaction.
78 REM * This is to facilitate reversing yields for non divisible items.
79 REM * PROCEDURE: delete_material_txn
80
81 REM * G. Muratore 06-MAY-2011 Bug 12391271
82 REM * Initialize primary_quantity when necessary. Also pass lpn_id value to build lot inter function.
83 REM * lpn_id will now be passed into status_applicable function for a more accurate evaluation.
84 REM * PROCEDURE: build_txn_inter and build_txn_inter_lot
85
86 REM * G. Muratore 06-MAY-2011 Bug 12881196
87 REM * Add missing attribute columns so that they get saved.
88 REM * PROCEDURE: insert_txn_inter_hdr
89
90 REM * G. Muratore 06-MAY-2011 Bug 12836004
91 REM * Per recommendation from INV team, pass in p_validation_level as g_valid_level_none
92 REM * when calling inv_txn_manager_grp.validate_transactions
93 REM * PROCEDURE: create_material_txn
94
95 REM * G. Muratore 12-APR-2012 Bug 13925279
96 REM * Assign the user entered expiration date.
97 REM * PROCEDURE: build_txn_inter
98
99 REM * A. Mishra 26-APR-2012 Bug 13835011
100 REM * Update the grade code in MTLT OR MTLI.
101 REM * Procedure: process_transactions
102
103 REM * G. Muratore 15-MAY-2012 Bug 14065291
104 REM * Initialize secondary_quantity when necessary.
105 REM * PROCEDURE: build_txn_inter.
106
107 REM * A. Mishra 10-Jul-2012 Bug 14297117
108 REM * Update the supplier lot in MTLT OR MTLI.
109 REM * Procedure: process_transactions
110
111 REM * G. Muratore 06-MAY-2011 Bug 14461780 - Back out 12836004
112 REM * Per recommendation from INV team, pass in p_validation_level as g_valid_level_full
113 REM * when calling inv_txn_manager_grp.validate_transactions. We need this so that
114 REM * expiration and origination dates are calculated centrally by INV code.
115 REM * PROCEDURE: create_material_txn
116
117 REM * Abhay Satpute 15 Nov 2012 Bug 15879394 Removed check requiring lot
118 REM * to exist to be used in ingredient issue transaction
119
120 REM * G. Muratore 06-DEC-2012 Bug 14685438
121 REM * Limited message size to size of database field.
122 REM * PROCEDURE: gme_txn_message
123
124 REM * G. Muratore 04-FEB-2013 Bug 16079842 - Rework 13835011 and 14297117.
125 REM * Use grade_code and supplier lot number from the passed in record.
126 REM * PROCEDURE: build_txn_inter_lot and process_transactions
127 REM **********************************************************************
128 */
129 /* +==========================================================================+
130 | PROCEDURE NAME
131 | create_material_txn
132 |
133 | USAGE
134 | Inserts the transaction to interface table
135 |
136 | ARGUMENTS
137 | p_mmti_rec -- mtl_transaction_interface rowtype
138 | p_mmli_tbl -- table of mtl_trans_lots_inter_tbl as input
139 |
140 | RETURNS
141 | returns via x_status OUT parameters
142 |
143 | HISTORY
144 | Created 02-Feb-05 Pawan Kumar
145 |
146 +==========================================================================+ */
147 PROCEDURE create_material_txn (
148 p_mmti_rec IN mtl_transactions_interface%ROWTYPE
149 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
150 ,p_phantom_trans IN NUMBER DEFAULT 0
151 ,x_return_status OUT NOCOPY VARCHAR2)
152 IS
153 l_api_name CONSTANT VARCHAR2 (30) := 'CREATE_MATERIAL_TXN';
154 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
155 l_mmti_rec mtl_transactions_interface%ROWTYPE;
156 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
157 m_mmti_rec mtl_transactions_interface%ROWTYPE;
158 m_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
159 x_mmti_rec mtl_transactions_interface%ROWTYPE;
160 x_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
161 l_mat_dtl_rec gme_material_details%ROWTYPE;
162 m_mat_dtl_rec gme_material_details%ROWTYPE;
163 l_ret NUMBER;
164 l_api_version NUMBER;
165 l_msg_count NUMBER;
166 l_msg_data VARCHAR2 (2000);
167 l_msg_index NUMBER (5);
168 l_txn_count NUMBER;
169 l_assign_phantom NUMBER;
170 l_cnt_int NUMBER;
171 l_cnt_temp NUMBER;
172 build_txn_inter_err EXCEPTION;
173
174 -- bug 9832061
175 l_trans_date DATE;
176 BEGIN
177 /* Initially let us assign the return status to success */
178 x_return_status := fnd_api.g_ret_sts_success;
179
180 IF (g_debug <= gme_debug.g_log_statement) THEN
181 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
182 || 'Entering');
183 END IF;
184
185 l_mmti_rec := p_mmti_rec;
186 l_mmli_tbl := p_mmli_tbl;
187 l_mat_dtl_rec.material_detail_id := l_mmti_rec.trx_source_line_id;
188
189 -- Now fetch the material details for the material
190 IF NOT gme_material_details_dbl.fetch_row
191 (p_material_detail => l_mat_dtl_rec
192 ,x_material_detail => l_mat_dtl_rec) THEN
193 RAISE fnd_api.g_exc_error;
194 END IF;
195
196 IF (g_debug <= gme_debug.g_log_statement) THEN
197 gme_debug.put_line ( g_pkg_name
198 || '.'
199 || l_api_name
200 || ':'
201 || 'material det_id'
202 || l_mat_dtl_rec.material_detail_id);
203 gme_debug.put_line ( g_pkg_name
204 || '.'
205 || l_api_name
206 || ':'
207 || 'batch_id'
208 || l_mat_dtl_rec.batch_id);
209 END IF;
210
211 -- Start bug 9832061
212 IF (g_debug <= gme_debug.g_log_statement) THEN
213 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
214 || 'Before calling gme_common_pvt.check_close_period');
215 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
216 || 'l_mmti_rec.transaction_date = ' || to_char(l_mmti_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
217
218 END IF;
219
220 l_trans_date := l_mmti_rec.transaction_date;
221
222 -- Bug 8605909 Check to see if original transaction date is in a closed period
223 IF NOT gme_common_pvt.check_close_period(p_org_id => l_mmti_rec.organization_id
224 ,p_trans_date => l_mmti_rec.transaction_date) THEN
225
226 -- Let's default to timestamp and overwrite if the user entered a different date.
227 IF (g_debug <= gme_debug.g_log_statement) THEN
228 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning gme_common_pvt.g_timestamp to transaction date');
229 END IF;
230
231 l_mmti_rec.transaction_date := gme_common_pvt.g_timestamp;
232
233 -- bug 9832061
234 --IF l_trans_date IS NOT NULL AND l_trans_date <> l_mmt_rec.transaction_date THEN
235 IF l_trans_date IS NOT NULL AND l_trans_date > l_mmti_rec.transaction_date THEN
236 IF (g_debug <= gme_debug.g_log_statement) THEN
237 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning l_trans_date to transaction date');
238 END IF;
239 l_mmti_rec.transaction_date := l_trans_date;
240 END IF;
241 END IF;
242 -- Bug 8605909 Ends
243
244 -- bug 9832061
245 IF (g_debug <= gme_debug.g_log_statement) THEN
246 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
247 || 'Final l_mmt_rec.transaction_date = ' || to_char(l_mmti_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
248 END IF;
249 -- END bug 9832061
250
251 IF p_phantom_trans <> 2 THEN
252 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL
253 AND (p_phantom_trans = 0) THEN
254 l_assign_phantom := 1;
255 END IF;
256
257 -- call for build procedure
258 build_txn_inter (p_mmti_rec => l_mmti_rec
259 ,p_mmli_tbl => l_mmli_tbl
260 ,p_assign_phantom => l_assign_phantom
261 ,x_mmti_rec => x_mmti_rec
262 ,x_mmli_tbl => x_mmli_tbl
263 ,x_return_status => l_return_status);
264 IF l_return_status <> fnd_api.g_ret_sts_success THEN
265 RAISE build_txn_inter_err;
266 END IF;
267 l_assign_phantom := 0;
268
269 IF (g_debug <= gme_debug.g_log_statement) THEN
270 gme_debug.put_line ( g_pkg_name
271 || '.'
272 || l_api_name
273 || ':'
274 || 'status after build : '
275 || l_return_status);
276 gme_debug.put_line ( g_pkg_name
277 || '.'
278 || l_api_name
279 || ':'
280 || 'x_mmti_rec.transaction_interface_id : '
281 || x_mmti_rec.transaction_interface_id);
282 gme_debug.put_line ( g_pkg_name
283 || '.'
284 || l_api_name
285 || ':'
286 || 'x_mmti_rec.transaction_reference : '
287 || x_mmti_rec.transaction_reference);
288 END IF;
289 END IF; --p_phantom_trans <> 2
290
291 -- For phantom
292 IF p_phantom_trans <> 1 THEN
293 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
294 m_mmti_rec := p_mmti_rec;
295 m_mmli_tbl := p_mmli_tbl;
296 m_mmti_rec.trx_source_line_id := l_mat_dtl_rec.phantom_line_id;
297 m_mat_dtl_rec.material_detail_id := m_mmti_rec.trx_source_line_id;
298
299 IF (g_debug <= gme_debug.g_log_statement) THEN
300 gme_debug.put_line ( g_pkg_name
301 || '.'
302 || l_api_name
303 || ':'
304 || 'for phantom line id:'
305 || l_mat_dtl_rec.phantom_line_id);
306 gme_debug.put_line ( g_pkg_name
307 || '.'
308 || l_api_name
309 || ':'
310 || ' original transaction_type_id :'
311 || l_mmti_rec.transaction_type_id);
312 END IF;
313
314 -- Now fetch the material details for the phantom line
315 IF NOT gme_material_details_dbl.fetch_row
316 (p_material_detail => m_mat_dtl_rec
317 ,x_material_detail => m_mat_dtl_rec) THEN
318 RAISE fnd_api.g_exc_error;
319 END IF;
320
321 m_mmti_rec.transaction_source_id := m_mat_dtl_rec.batch_id;
322
323 IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
324 --(35)
325 m_mmti_rec.transaction_type_id :=
326 gme_common_pvt.g_prod_completion;
327 -- (44)
328 m_mmti_rec.transaction_action_id :=
329 gme_common_pvt.g_prod_comp_txn_action;
330 --(31)
331 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
332 --(43)
333 m_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
334 -- (17)
335 m_mmti_rec.transaction_action_id :=
336 gme_common_pvt.g_prod_ret_txn_action;
337 --(27)
338 ELSIF l_mmti_rec.transaction_type_id =
339 gme_common_pvt.g_prod_completion THEN
340 -- (44)
341 m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
342 -- (35)
343 m_mmti_rec.transaction_action_id :=
344 gme_common_pvt.g_ing_issue_txn_action;
345 --(1)
346 ELSIF l_mmti_rec.transaction_type_id =
347 gme_common_pvt.g_prod_return THEN
348 m_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
349 -- (17)
350 m_mmti_rec.transaction_action_id :=
351 gme_common_pvt.g_ing_ret_txn_action;
352 --(27)
353 ELSIF l_mmti_rec.transaction_type_id =
354 gme_common_pvt.g_byprod_completion THEN
355 -- add for byprod type_id
356 m_mmti_rec.transaction_action_id :=
357 gme_common_pvt.g_byprod_ret_txn_action;
358 --(32)
359 ELSE
360 --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
361 -- add for byprod type_id
362 m_mmti_rec.transaction_action_id :=
363 gme_common_pvt.g_byprod_comp_txn_action;
364 --(31)
365 END IF;
366
367 IF (g_debug <= gme_debug.g_log_statement) THEN
368 gme_debug.put_line ( g_pkg_name
369 || '.'
370 || l_api_name
371 || ':'
372 || 'calling build for phantom');
373 gme_debug.put_line ( g_pkg_name
374 || '.'
375 || l_api_name
376 || ':'
377 || 'transaction_type_id for phantom :'
378 || m_mmti_rec.transaction_type_id);
379 gme_debug.put_line ( g_pkg_name
380 || '.'
381 || l_api_name
382 || ':'
383 || 'transaction_action_id for phantom :'
384 || m_mmti_rec.transaction_action_id);
385 gme_debug.put_line ( g_pkg_name
386 || '.'
387 || l_api_name
388 || ':'
389 || 'm_mat_dtl_rec.phantom_line_id :'
390 || m_mat_dtl_rec.phantom_line_id);
391 gme_debug.put_line ( g_pkg_name
392 || '.'
393 || l_api_name
394 || ':'
395 || 'x_mmti_rec.transaction_interface_id :'
396 || x_mmti_rec.transaction_interface_id);
397 gme_debug.put_line ( g_pkg_name
398 || '.'
399 || l_api_name
400 || ':'
401 || 'x_mmti_rec.transaction_reference :'
402 || x_mmti_rec.transaction_reference);
403 END IF;
404
405 IF (p_phantom_trans = 0) THEN
406 m_mmti_rec.transaction_reference :=
407 x_mmti_rec.transaction_interface_id;
408 l_assign_phantom := 0;
409 ELSE
410 l_assign_phantom := 0;
411 END IF;
412
413 -- calling build for phantom
414 build_txn_inter (p_mmti_rec => m_mmti_rec
415 ,p_mmli_tbl => m_mmli_tbl
416 ,p_assign_phantom => l_assign_phantom
417 ,x_mmti_rec => x_mmti_rec
418 ,x_mmli_tbl => x_mmli_tbl
419 ,x_return_status => l_return_status);
420
421 IF l_return_status <> fnd_api.g_ret_sts_success THEN
422 RAISE build_txn_inter_err;
423 END IF;
424
425 IF (g_debug <= gme_debug.g_log_statement) THEN
426 gme_debug.put_line ( g_pkg_name
427 || '.'
428 || l_api_name
429 || ':'
430 || 'status from build for phantom '
431 || l_return_status);
432 END IF;
433 END IF; -- for phantom
434 END IF; --p_phantom_trans <> 1 THEN
435
436 -- code for moving the data to temp
437 IF gme_common_pvt.g_move_to_temp = fnd_api.g_true THEN
438 IF (g_debug <= gme_debug.g_log_statement) THEN
439 gme_debug.put_line ( g_pkg_name
440 || '.'
441 || l_api_name
442 || ':'
443 || 'calling validate transactions:'
444 || gme_common_pvt.g_transaction_header_id);
445
446
447 select count(*)
448 into l_cnt_int
449 from mtl_transactions_interface
450 where transaction_header_id= gme_common_pvt.g_transaction_header_id;
451 gme_debug.put_line ( g_pkg_name
452 || '.'
453 || l_api_name
454 || ':'
455 || 'count from interface table:'
456 || l_cnt_int);
457
458 END IF;
459
460 /* Jalaj Srivastava Bug 5109154
461 pass additional parameter p_free_tree as false.
462 we wil free the tree while calling process transactions */
463
464 -- Bug 14461780 - pass validation level as full again so exp and orig dates get computed correctly.
465 l_ret := inv_txn_manager_grp.validate_transactions
466 (p_api_version => l_api_version
467 ,p_init_msg_list => fnd_api.g_true
468 ,p_validation_level => fnd_api.g_valid_level_full -- do not change this !!!
469 -- bug 12836004
470 --,p_validation_level => fnd_api.g_valid_level_none
471 ,p_header_id => gme_common_pvt.g_transaction_header_id
472 ,x_return_status => l_return_status
473 ,x_msg_count => l_msg_count
474 ,x_msg_data => l_msg_data
475 ,x_trans_count => l_txn_count
476 ,p_free_tree => fnd_api.g_false);
477 IF (g_debug <= gme_debug.g_log_statement) THEN
478 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'after validate transactions:'|| l_ret);
479 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'x_trans_count:'|| l_txn_count);
480 select count(*) into l_cnt_temp
481 from mtl_material_transactions_temp
482 where transaction_header_id= gme_common_pvt.g_transaction_header_id;
483 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'count from temp table:'|| l_cnt_temp);
484 END IF;
485 IF l_ret < 0 THEN
486 IF (g_debug <= gme_debug.g_log_statement) THEN
487 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from validate transactions');
488 END IF;
489 /* Jalaj Srivastava Bug 5001915 add msg returned to stack */
490 IF (l_msg_data IS NOT NULL) THEN
491 IF (g_debug <= gme_debug.g_log_statement) THEN
492 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'l_msg_data is: '||l_msg_data);
493 END IF;
494 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
495 ,p_product_code => 'FND'
496 ,p_token1_name => 'MESSAGE'
497 ,p_token1_value => l_msg_data);
498 ELSE
499 /* Bug 5256543 Get messages from interface table and put on stack */
500 FOR get_msgs IN (SELECT error_explanation FROM mtl_transactions_interface
501 WHERE transaction_header_id = gme_common_pvt.g_transaction_header_id
502 AND error_explanation IS NOT NULL) LOOP
503 IF (g_debug <= gme_debug.g_log_statement) THEN
504 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'error_explanation is: '||get_msgs.error_explanation);
505 END IF;
506 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
507 ,p_product_code => 'FND'
508 ,p_token1_name => 'MESSAGE'
509 ,p_token1_value => get_msgs.error_explanation);
510 END LOOP;
511 END IF;
512 RAISE fnd_api.g_exc_error;
513 END IF;
514 END IF;
515 IF (g_debug <= gme_debug.g_log_statement) THEN
516 gme_debug.put_line ( g_pkg_name
517 || '.'
518 || l_api_name
519 || ':'
520 || 'Exiting with '
521 || x_return_status);
522 END IF;
523 EXCEPTION
524 WHEN build_txn_inter_err THEN
525 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'
526 ||' error from build_txn_inter');
527 x_return_status := l_return_status;
528 WHEN fnd_api.g_exc_error THEN
529 x_return_status := fnd_api.g_ret_sts_error;
530 WHEN fnd_api.g_exc_unexpected_error THEN
531 x_return_status := fnd_api.g_ret_sts_unexp_error;
532 WHEN OTHERS THEN
533 x_return_status := fnd_api.g_ret_sts_unexp_error;
534 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
535
536 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
537 gme_debug.put_line ( g_pkg_name
538 || '.'
539 || l_api_name
540 || ':'
541 || 'WHEN OTHERS:'
542 || SQLERRM);
543 END IF;
544 END create_material_txn;
545
546 /* +==========================================================================+
547 | PROCEDURE NAME
548 | check_lpn_subinv_loc(
549 |
550 | USAGE
551 | Checks if the current LPN subinv and Locator are same as the input values
552 |
553 | ARGUMENTS
554 | p_lpn_id -- LPN Id
555 | p_in_subinv -- Subinv Code
556 | p_in_locid -- Locator Id
557 |
558 |
559 | RETURNS
560 | returns a Boolean status. True if the subinv and locator info match otherwise false
561 | x_out_subinv The current Subinv for the LPN
562 | x_out_locId The current locator id for the LPN
563 | x_context The LPN context
564 |
565 | HISTORY
566 | Created 06-Sep-2011 Apeksha Misrha
567 |
568 +==========================================================================+ */
569 FUNCTION check_lpn_subinv_loc(p_lpn_id IN NUMBER,
570 p_in_subinv IN VARCHAR2 default NULL,
571 p_in_locid IN NUMBER default NULL,
572 x_out_subinv OUT NOCOPY VARCHAR2 ,
573 x_out_locId OUT NOCOPY VARCHAR2,
574 x_out_lpnno OUT NOCOPY VARCHAR2,
575 x_context OUT NOCOPY NUMBER) RETURN BOOLEAN AS
576
577 BEGIN
578 IF (g_debug <= gme_debug.g_log_statement) THEN
579 gme_debug.put_line ( g_pkg_name
580 || '.'
581 || 'check_lpn_subinv_loc'
582 || ': '
583 || 'Input data Lpn :'||p_lpn_id || 'Subinv :'|| p_in_subinv ||' Locator : ' || p_in_locid);
584 END IF;
585
586 SELECT SUBINVENTORY_CODE,
587 LOCATOR_ID,
588 LPN_CONTEXT,
589 LICENSE_PLATE_NUMBER
590 INTO x_out_subinv,
591 x_out_locId,
592 x_context,
593 x_out_lpnno
594 FROM WMS_LICENSE_PLATE_NUMBERS
595 WHERE lpn_id = p_lpn_id;
596
597 IF (g_debug <= gme_debug.g_log_statement) THEN
598 gme_debug.put_line ( g_pkg_name
599 || '.'
600 || 'check_lpn_subinv_loc'
601 || ':'
602 || 'Fecthed data Subinv : '|| x_out_subinv ||' Locator : ' || x_out_locId || ' Context : ' || x_context);
603 END IF;
604
605 IF x_context = 5 THEN
606 -- subinv and loc would be null
607 x_out_subinv := p_in_subinv;
608 x_out_locId := p_in_locid;
609 RETURN TRUE;
610 ELSIF x_context = 1 THEN
611 IF (x_out_subinv <> p_in_subinv OR
612 (p_in_subinv IS NULL and x_out_subinv IS NOT NULL) OR
613 (x_out_subinv IS NULL and p_in_subinv IS NOT NULL) OR
614 x_out_locId <> p_in_locid OR
615 (p_in_locid IS NULL and x_out_locId IS NOT NULL) OR
616 (x_out_locId IS NULL and p_in_locid IS NOT NULL)
617 ) THEN
618 IF (g_debug <= gme_debug.g_log_statement) THEN
619 gme_debug.put_line ( g_pkg_name
620 || '.'
621 || 'check_lpn_subinv_loc'
622 || ':'
623 || 'Returning FALSE' );
624 END IF;
625 RETURN FALSE;
626 END IF;
627 ELSE
628 RETURN TRUE;
629 END IF;
630
631 RETURN TRUE;
632 END check_lpn_subinv_loc;
633
634 /* +==========================================================================+
635 | PROCEDURE NAME
636 | update_material_txn
637 |
638 | USAGE
639 | update the transaction in interface table - it deletes all transactions
640 | of transaction_id passed. Creates new transactions as passed.
641 |
642 | ARGUMENTS
643 | p_transaction_id - transaction_id from mmt for deletion
644 | p_mmti_rec -- mtl_transaction_interface rowtype
645 | p_mmli_tbl -- table of mtl_transaction_lots_inumber_tbl as input
646 |
647 | RETURNS
648 | returns via x_status OUT parameters
649 |
650 | HISTORY
651 | Created 02-Feb-05 Pawan Kumar
652 | Bug 7385309 Archana Mundhe
653 | Create a new transaction before deleting existing one.
654 |
655 | G. Muratore 15-FEB-2010 Bug 9301755
656 | Resequence calls for reversals depending on line_type. This is an
657 | extension of 8639523/7385309 which dealt with product yields only.
658 | update_material_txn is an overloaded function so we need to make same fix here.
659 +==========================================================================+ */
660 PROCEDURE update_material_txn (
661 p_transaction_id IN NUMBER
662 ,p_mmti_rec IN mtl_transactions_interface%ROWTYPE
663 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
664 ,x_return_status OUT NOCOPY VARCHAR2)
665 IS
666 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN';
667 l_mmt_rec mtl_material_transactions%ROWTYPE;
668 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
669 l_mmti_rec mtl_transactions_interface%ROWTYPE;
670 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
671 l_mat_dtl_rec gme_material_details%ROWTYPE;
672 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
673
674 create_material_txn_err EXCEPTION;
675 delete_material_txn_err EXCEPTION;
676
677 -- Bug 9301755 - Introduce cursor to get line_type and variables.
678 -- Bug 9770408 - Fetch the divisible flag also.
679 CURSOR Cur_get_material_line_type (v_transaction_id NUMBER) IS
680 SELECT d.line_type, NVL( lot_divisible_flag, 'Y')
681 FROM mtl_material_transactions t, gme_material_details d, mtl_system_items_b i
682 WHERE t.transaction_source_type_id = 5
683 AND t.transaction_id = v_transaction_id
684 AND d.batch_id = t.transaction_source_id
685 AND d.material_detail_id = t.trx_source_line_id
686 AND d.inventory_item_id = i.inventory_item_id
687 AND d.organization_id = i.organization_id;
688
689 l_line_type gme_material_details.line_type%TYPE;
690 l_lot_divisible VARCHAR2(1);
691 l_delete_done NUMBER;
692
693 invalid_line_type EXCEPTION;
694 BEGIN
695 --Initially let us assign the return status to success
696 x_return_status := fnd_api.g_ret_sts_success;
697
698 IF (g_debug <= gme_debug.g_log_statement) THEN
699 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
700 || 'Entering');
701 END IF;
702
703 l_mmti_rec := p_mmti_rec;
704 l_mmli_tbl := p_mmli_tbl;
705
706 -- Bug 9301755 - Fetch the line_type so we can decide whether to call delete first or create.
707 OPEN Cur_get_material_line_type (p_transaction_id);
708 FETCH Cur_get_material_line_type INTO l_line_type, l_lot_divisible;
709 IF (Cur_get_material_line_type%NOTFOUND) THEN
710 CLOSE Cur_get_material_line_type;
711 RAISE invalid_line_type;
712 END IF;
713 CLOSE Cur_get_material_line_type;
714
715 IF (g_debug <= gme_debug.g_log_statement) THEN
716 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'line type is '||l_line_type);
717 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'l_lot_divisible is '||l_lot_divisible);
718 END IF;
719
720 -- Bug 9301755 - Call delete first only for ingredient lines since we want
721 -- the inventory put back before we call create txn again. This ensures all
722 -- the inventory is available as we do not get erroneous shortage messages.
723
724 -- Bug 9770408 - Evaluate non divisible flag also. This is needed for Yield corrections.
725 l_delete_done := 0;
726 IF l_line_type = gme_common_pvt.g_line_type_ing OR l_lot_divisible = 'N' THEN
727 -- call to delete all the transactions for this transaction_id
728 IF (g_debug <= gme_debug.g_log_statement) THEN
729 gme_debug.put_line
730 ( g_pkg_name
731 || '.'
732 || l_api_name
733 || ':'
734 || 'calling delete transaction for transaction id'
735 || p_transaction_id);
736 END IF;
737
738 delete_material_txn (p_transaction_id => p_transaction_id
739 -- 8605909 updated the delete material transaction with the trans date parameter
740 ,p_trans_date => l_mmti_rec.transaction_date
741 ,x_return_status => l_return_status);
742
743 IF l_return_status <> fnd_api.g_ret_sts_success THEN
744 IF (g_debug <= gme_debug.g_log_statement) THEN
745 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
746 END IF;
747 RAISE delete_material_txn_err;
748 END IF;
749 l_delete_done := 1;
750 END IF;
751
752 IF (g_debug <= gme_debug.g_log_statement) THEN
753 gme_debug.put_line ( g_pkg_name
754 || '.'
755 || l_api_name
756 || ':'
757 || 'calling create in update with :'
758 || l_mmti_rec.transaction_interface_id);
759 END IF;
760
761 -- Bug 9770408 - If delete has occurred then we need to set the batch_seq
762 -- for the new transaction higher than 100 to make sure delete is done first.
763 IF l_delete_done = 1 THEN
764 l_mmti_rec.transaction_batch_seq := 101;
765 END IF;
766
767 -- Now send the new transaction and lot tbl for create a new transactions
768 create_material_txn (p_mmti_rec => l_mmti_rec
769 ,p_mmli_tbl => l_mmli_tbl
770 ,x_return_status => l_return_status);
771
772 IF l_return_status <> fnd_api.g_ret_sts_success THEN
773 RAISE create_material_txn_err;
774 END IF;
775
776 -- Bug 9301755 - Call delete for non ingredient lines.
777 -- IF l_line_type <> gme_common_pvt.g_line_type_ing THEN
778 IF l_delete_done = 0 THEN
779 -- call to delete all the transactions for this transaction_id
780 IF (g_debug <= gme_debug.g_log_statement) THEN
781 gme_debug.put_line
782 ( g_pkg_name
783 || '.'
784 || l_api_name
785 || ':'
786 || 'calling delete transaction for transaction id'
787 || p_transaction_id);
788 END IF;
789
790 delete_material_txn (p_transaction_id => p_transaction_id
791 -- 8605909 updated the delete material transaction with the trans date parameter
792 ,p_trans_date => l_mmti_rec.transaction_date
793 ,x_return_status => l_return_status);
794
795 IF l_return_status <> fnd_api.g_ret_sts_success THEN
796 IF (g_debug <= gme_debug.g_log_statement) THEN
797 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
798 END IF;
799 RAISE delete_material_txn_err;
800 END IF;
801 END IF;
802
803 IF (g_debug <= gme_debug.g_log_statement) THEN
804 gme_debug.put_line ( g_pkg_name
805 || '.'
806 || l_api_name
807 || ':'
808 || 'Exiting with '
809 || x_return_status);
810 END IF;
811 EXCEPTION
812 WHEN invalid_line_type THEN
813 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error fetching line_type.');
814 x_return_status := fnd_api.g_ret_sts_unexp_error ;
815
816 WHEN delete_material_txn_err THEN
817 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
818 x_return_status := l_return_status ;
819
820 WHEN create_material_txn_err THEN
821 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
822 x_return_status := l_return_status ;
823 WHEN fnd_api.g_exc_error THEN
824 x_return_status := fnd_api.g_ret_sts_error;
825 WHEN fnd_api.g_exc_unexpected_error THEN
826 x_return_status := fnd_api.g_ret_sts_unexp_error;
827 WHEN OTHERS THEN
828 x_return_status := fnd_api.g_ret_sts_unexp_error;
829 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
830
831 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
832 gme_debug.put_line ( g_pkg_name
833 || '.'
834 || l_api_name
835 || ':'
836 || 'WHEN OTHERS:'
837 || SQLERRM);
838 END IF;
839 END update_material_txn;
840
841 /* +==========================================================================+
842 | PROCEDURE NAME
843 | update_material_txn
844 |
845 | USAGE
846 | update the transaction in interface table - it deletes all transactions
847 | by getting transaction_id from the mmt record passed. Creates new transactions
848 | in interface by converting the mmt to mmti.
849 |
850 | ARGUMENTS
851 | p_mmt_rec -- mtl_material_transaction rowtype
852 | p_mmln_tbl -- table of mtl_transaction_lots_inumber_tbl as input
853 |
854 | RETURNS
855 | returns via x_status OUT parameters
856 |
857 | HISTORY
858 | Created 02-Feb-05 Pawan Kumar
859 |
860 | Bug 7385309 Archana Mundhe
861 | Create a new transaction before deleting existing one.
862 |
863 | G. Muratore 05-AUG-2009 Bug 8639523
864 | Resequence calls for reversals depending on line_type. This is a rework
865 | of 7385309 which dealt with product yields only. This keeps that fix
866 | in place for products and byproducts.
867 +==========================================================================+ */
868 PROCEDURE update_material_txn (
869 p_mmt_rec IN mtl_material_transactions%ROWTYPE
870 ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
871 ,x_return_status OUT NOCOPY VARCHAR2)
872 IS
873 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_MATERIAL_TXN-2';
874 l_transaction_id NUMBER;
875 l_mmt_rec mtl_material_transactions%ROWTYPE;
876 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
877 l_mmti_rec mtl_transactions_interface%ROWTYPE;
878 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
879 l_mat_dtl_rec gme_material_details%ROWTYPE;
880 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
881 create_material_txn_err EXCEPTION;
882 delete_material_txn_err EXCEPTION;
883
884 -- Bug 8639523 - Introduce cursor to get line_type and variables.
885 CURSOR Cur_get_material_line_type (v_transaction_id NUMBER) IS
886 SELECT d.line_type, NVL( lot_divisible_flag, 'Y')
887 FROM mtl_material_transactions t, gme_material_details d, mtl_system_items_b i
888 WHERE t.transaction_source_type_id = 5
889 AND t.transaction_id = v_transaction_id
890 AND d.batch_id = t.transaction_source_id
891 AND d.material_detail_id = t.trx_source_line_id
892 AND d.inventory_item_id = i.inventory_item_id
893 AND d.organization_id = i.organization_id;
894
895 l_line_type gme_material_details.line_type%TYPE;
896 l_lot_divisible VARCHAR2(1);
897 l_delete_done NUMBER;
898
899 invalid_line_type EXCEPTION;
900 BEGIN
901 -- Initially let us assign the return status to success
902 x_return_status := fnd_api.g_ret_sts_success;
903
904 IF (g_debug <= gme_debug.g_log_statement) THEN
905 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
906 || 'Entering');
907 END IF;
908
909 l_mmt_rec := p_mmt_rec;
910 l_mmln_tbl := p_mmln_tbl;
911 -- get the transaction_id from the mmt record for deleting it.
912 l_transaction_id := l_mmt_rec.transaction_id;
913
914 IF (g_debug <= gme_debug.g_log_statement) THEN
915 gme_debug.put_line ( g_pkg_name
916 || '.'
917 || l_api_name
918 || ':'
919 || 'calling construct from mmt to mmti :'
920 || l_mmt_rec.transaction_id);
921 END IF;
922
923 -- Now call the construct procedure to populate the interface for inserting new txns
924 construct_mmti (p_mmt_rec => l_mmt_rec
925 ,p_mmln_tbl => l_mmln_tbl
926 ,x_mmti_rec => l_mmti_rec
927 ,x_mmli_tbl => l_mmli_tbl
928 ,x_return_status => l_return_status);
929
930 IF l_return_status <> fnd_api.g_ret_sts_success THEN
931 IF (g_debug <= gme_debug.g_log_statement) THEN
932 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
933 END IF;
934 RAISE fnd_api.g_exc_error;
935 END IF;
936
937 -- Bug 8639523 - Fetch the line_type so we can decide whether to call delete first or create.
938 OPEN Cur_get_material_line_type (l_transaction_id);
939 FETCH Cur_get_material_line_type INTO l_line_type, l_lot_divisible;
940 IF (Cur_get_material_line_type%NOTFOUND) THEN
941 CLOSE Cur_get_material_line_type;
942 RAISE invalid_line_type;
943 END IF;
944 CLOSE Cur_get_material_line_type;
945
946 IF (g_debug <= gme_debug.g_log_statement) THEN
947 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'line type is '||l_line_type);
948 END IF;
949
950 -- Bug 8639523 - Call delete first only for ingredient lines since we want
951 -- the inventory put back before we call create txn again. This ensures all
952 -- the inventory is available as we do not get erroneous shortage messages.
953
954 -- Bug 9770408 - Evaluate non divisible flag also. This is needed for Yield corrections.
955 l_delete_done := 0;
956 IF l_line_type = gme_common_pvt.g_line_type_ing OR l_lot_divisible = 'N' THEN
957 -- call to delete all the transactions for this transaction_id
958 IF (g_debug <= gme_debug.g_log_statement) THEN
959 gme_debug.put_line
960 ( g_pkg_name
961 || '.'
962 || l_api_name
963 || ':'
964 || 'calling delete transaction for transaction id'
965 || l_transaction_id);
966 END IF;
967
968 delete_material_txn (p_transaction_id => l_transaction_id
969 --8605909 updated the delete material transaction with the trans date parameter
970 ,p_trans_date => l_mmt_rec.transaction_date
971 ,x_return_status => l_return_status);
972
973 IF l_return_status <> fnd_api.g_ret_sts_success THEN
974 IF (g_debug <= gme_debug.g_log_statement) THEN
975 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
976 END IF;
977 RAISE delete_material_txn_err;
978 END IF;
979 l_delete_done := 1;
980 END IF;
981
982 IF (g_debug <= gme_debug.g_log_statement) THEN
983 gme_debug.put_line ( g_pkg_name
984 || '.'
985 || l_api_name
986 || ':'
987 || 'calling create trans with ='
988 || l_mmt_rec.transaction_id);
989 gme_debug.put_line ( g_pkg_name
990 || '.'
991 || l_api_name
992 || ':'
993 || 'calling create trans with ='
994 || l_mmt_rec.source_line_id);
995 END IF;
996
997 -- Bug 9770408 - If delete has occurred then we need to set the batch_seq
998 -- for the new transaction higher than 100 to make sure delete is done first.
999 IF l_delete_done = 1 THEN
1000 l_mmti_rec.transaction_batch_seq := 101;
1001 END IF;
1002
1003 -- Now send the new transaction and lot tbl for create a new transactions
1004 create_material_txn (p_mmti_rec => l_mmti_rec
1005 ,p_mmli_tbl => l_mmli_tbl
1006 ,x_return_status => l_return_status);
1007
1008 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1009 IF (g_debug <= gme_debug.g_log_statement) THEN
1010 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1011 END IF;
1012 RAISE create_material_txn_err;
1013 END IF;
1014
1015 -- Bug 8639523 - Call delete for non ingredient lines.
1016 -- IF l_line_type <> gme_common_pvt.g_line_type_ing THEN
1017 IF l_delete_done = 0 THEN
1018 -- call to delete all the transactions for this transaction_id
1019 IF (g_debug <= gme_debug.g_log_statement) THEN
1020 gme_debug.put_line
1021 ( g_pkg_name
1022 || '.'
1023 || l_api_name
1024 || ':'
1025 || 'calling delete transaction for transaction id'
1026 || l_transaction_id);
1027 END IF;
1028
1029 -- call to delete all the transactions for this transaction_id
1030 delete_material_txn (p_transaction_id => l_transaction_id
1031 --8605909 updated the delete material transaction with the trans date parameter
1032 ,p_trans_date => l_mmt_rec.transaction_date
1033 ,x_return_status => l_return_status);
1034
1035 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1036 IF (g_debug <= gme_debug.g_log_statement) THEN
1037 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
1038 END IF;
1039 RAISE delete_material_txn_err;
1040 END IF;
1041 END IF;
1042
1043 IF (g_debug <= gme_debug.g_log_statement) THEN
1044 gme_debug.put_line ( g_pkg_name
1045 || '.'
1046 || l_api_name
1047 || ':'
1048 || 'Exiting with '
1049 || x_return_status);
1050 END IF;
1051
1052 EXCEPTION
1053 WHEN invalid_line_type THEN
1054 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error fetching line_type.');
1055 x_return_status := fnd_api.g_ret_sts_unexp_error ;
1056 WHEN delete_material_txn_err THEN
1057 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
1058 x_return_status := l_return_status ;
1059 WHEN create_material_txn_err THEN
1060 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1061 x_return_status := l_return_status ;
1062 WHEN fnd_api.g_exc_error THEN
1063 x_return_status := fnd_api.g_ret_sts_error;
1064 WHEN fnd_api.g_exc_unexpected_error THEN
1065 x_return_status := fnd_api.g_ret_sts_unexp_error;
1066 WHEN OTHERS THEN
1067 x_return_status := fnd_api.g_ret_sts_unexp_error;
1068 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1069
1070 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
1071 gme_debug.put_line ( g_pkg_name
1072 || '.'
1073 || l_api_name
1074 || ':'
1075 || 'WHEN OTHERS:'
1076 || SQLERRM);
1077 END IF;
1078 END update_material_txn;
1079
1080 /* +==========================================================================+
1081 | PROCEDURE NAME
1082 | delete_material_txn
1083 |
1084 | USAGE
1085 | delete all transactions of transaction_id passed by creating reverse transaction.
1086 |
1087 | ARGUMENTS
1088 | p_transaction_id -- transaction_id from mmt for deletion
1089 |
1090 |
1091 | RETURNS
1092 | returns via x_status OUT parameters
1093 |
1094 | HISTORY
1095 | Created 02-Feb-05 Pawan Kumar
1096 |
1097 | A. Mishra 03-Sep-2009 Bug 8605909
1098 | Added p_trans_date parameter to be potentially be used on reversals
1099 | where original transaction is now in a closed period.
1100 +==========================================================================+ */
1101 PROCEDURE delete_material_txn (
1102 p_transaction_id IN NUMBER
1103 ,p_txns_pair IN NUMBER DEFAULT NULL
1104 ,p_trans_date IN DATE DEFAULT NULL
1105 ,x_return_status OUT NOCOPY VARCHAR2)
1106 IS
1107 CURSOR cur_get_ph_txns (v_transaction_id NUMBER)
1108 IS
1109 SELECT transaction_id2
1110 FROM gme_transaction_pairs
1111 WHERE transaction_id1 = v_transaction_id
1112 AND pair_type = gme_common_pvt.g_pairs_phantom_type;
1113
1114 l_api_name CONSTANT VARCHAR2 (30) := 'DELETE_MATERIAL_TXN';
1115 l_transaction_id NUMBER;
1116 m_transaction_id NUMBER;
1117 l_mmt_rec mtl_material_transactions%ROWTYPE;
1118 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
1119 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1120 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1121 l_mat_dtl_rec gme_material_details%ROWTYPE;
1122 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1123 create_material_txn_err EXCEPTION;
1124 delete_material_txn_err EXCEPTION;
1125 get_trans_err EXCEPTION;
1126 l_trans_date DATE;
1127 BEGIN
1128 -- Initially let us assign the return status to success
1129 x_return_status := fnd_api.g_ret_sts_success;
1130
1131 IF (g_debug <= gme_debug.g_log_statement) THEN
1132 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1133 || 'Entering');
1134 END IF;
1135
1136 l_transaction_id := p_transaction_id;
1137 l_trans_date := p_trans_date;
1138
1139 IF l_transaction_id IS NOT NULL THEN
1140 IF (g_debug <= gme_debug.g_log_statement) THEN
1141 gme_debug.put_line
1142 ( g_pkg_name
1143 || '.'
1144 || l_api_name
1145 || ':'
1146 || 'getting all transaction for deletion with transaction id '
1147 || l_transaction_id);
1148 END IF;
1149
1150 get_transactions (p_transaction_id => l_transaction_id
1151 ,x_mmt_rec => l_mmt_rec
1152 ,x_mmln_tbl => l_mmln_tbl
1153 ,x_return_status => l_return_status);
1154
1155 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1156 IF (g_debug <= gme_debug.g_log_statement) THEN
1157 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get transactions');
1158 END IF;
1159 RAISE get_trans_err;
1160 END IF;
1161
1162 -- bug 9832061
1163 IF (g_debug <= gme_debug.g_log_statement) THEN
1164 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1165 || 'Before calling gme_common_pvt.check_close_period');
1166 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1167 || 'l_mmt_rec.transaction_date = ' || to_char(l_mmt_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
1168
1169 IF l_trans_date IS NOT NULL THEN
1170 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1171 || 'l_trans_date = ' || to_char(l_trans_date, 'DD-MON-YYYY HH24:MI:SS'));
1172 END IF;
1173 END IF;
1174
1175 -- Bug 8605909 Check to see if original transaction date is in a closed period
1176 IF NOT gme_common_pvt.check_close_period(p_org_id => l_mmt_rec.organization_id
1177 ,p_trans_date => l_mmt_rec.transaction_date) THEN
1178
1179 -- Let's default to timestamp and overwrite if the user entered a different date.
1180 IF (g_debug <= gme_debug.g_log_statement) THEN
1181 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning gme_common_pvt.g_timestamp to transaction date');
1182 END IF;
1183
1184 l_mmt_rec.transaction_date := gme_common_pvt.g_timestamp;
1185
1186 -- bug 9832061
1187 --IF l_trans_date IS NOT NULL AND l_trans_date <> l_mmt_rec.transaction_date THEN
1188 IF l_trans_date IS NOT NULL AND l_trans_date > l_mmt_rec.transaction_date THEN
1189 IF (g_debug <= gme_debug.g_log_statement) THEN
1190 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Assigning l_trans_date to transaction date');
1191 END IF;
1192 l_mmt_rec.transaction_date := l_trans_date;
1193 END IF;
1194 END IF;
1195 -- Bug 8605909 Ends
1196
1197 -- bug 9832061
1198 IF (g_debug <= gme_debug.g_log_statement) THEN
1199 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1200 || 'Final l_mmt_rec.transaction_date = ' || to_char(l_mmt_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
1201 END IF;
1202 END IF;
1203
1204 IF (g_debug <= gme_debug.g_log_statement) THEN
1205 gme_debug.put_line ( g_pkg_name
1206 || '.'
1207 || l_api_name
1208 || ':'
1209 || 'calling construct from mmt to mmti :'
1210 || l_mmt_rec.transaction_id);
1211 gme_debug.put_line ( g_pkg_name
1212 || '.'
1213 || l_api_name
1214 || ':'
1215 || 'the mmt record source line id(null) ='
1216 || l_mmt_rec.source_line_id);
1217 END IF;
1218
1219 construct_mmti (p_mmt_rec => l_mmt_rec
1220 ,p_mmln_tbl => l_mmln_tbl
1221 ,x_mmti_rec => l_mmti_rec
1222 ,x_mmli_tbl => l_mmli_tbl
1223 ,x_return_status => l_return_status);
1224
1225 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1226 IF (g_debug <= gme_debug.g_log_statement) THEN
1227 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from construct mmti');
1228 END IF;
1229 RAISE fnd_api.g_exc_error;
1230 END IF;
1231
1232 IF (g_debug <= gme_debug.g_log_statement) THEN
1233 gme_debug.put_line ( g_pkg_name
1234 || '.'
1235 || l_api_name
1236 || ':'
1237 || 'getting material for '
1238 || l_mmt_rec.trx_source_line_id);
1239 END IF;
1240
1241 -- get the material details of the transaction
1242 l_mat_dtl_rec.material_detail_id := l_mmt_rec.trx_source_line_id;
1243
1244 -- Now fetch the material details for the material
1245 IF (g_debug <= gme_debug.g_log_statement) THEN
1246 gme_debug.put_line ( g_pkg_name
1247 || '.'
1248 || l_api_name
1249 || ':'
1250 || 'getting material for '
1251 || l_mat_dtl_rec.material_detail_id);
1252 END IF;
1253
1254 IF NOT gme_material_details_dbl.fetch_row
1255 (p_material_detail => l_mat_dtl_rec
1256 ,x_material_detail => l_mat_dtl_rec) THEN
1257 RAISE fnd_api.g_exc_error;
1258 END IF;
1259
1260 -- Bug 6437252 LPN Support
1261 IF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue THEN
1262 --(35)
1263 l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_return;
1264 --(43)
1265 l_mmti_rec.transaction_action_id :=
1266 gme_common_pvt.g_ing_ret_txn_action;
1267 --(27)
1268 l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1269 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_return THEN
1270 --(43)
1271 l_mmti_rec.transaction_type_id := gme_common_pvt.g_ing_issue;
1272 -- (35)
1273 l_mmti_rec.transaction_action_id :=
1274 gme_common_pvt.g_ing_issue_txn_action;
1275 --(1)
1276 l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1277 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_completion THEN
1278 -- (44)
1279 l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_return;
1280 --(17)
1281 l_mmti_rec.transaction_action_id :=
1282 gme_common_pvt.g_prod_ret_txn_action;
1283 --(27)
1284 l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1285 ELSIF l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return THEN
1286 l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1287 l_mmti_rec.transaction_action_id :=
1288 gme_common_pvt.g_prod_comp_txn_action;
1289 --(31)
1290 l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1291 ELSIF l_mmti_rec.transaction_type_id =
1292 gme_common_pvt.g_byprod_completion THEN
1293 l_mmti_rec.transaction_type_id := gme_common_pvt.g_byprod_return;
1294 l_mmti_rec.transaction_action_id :=
1295 gme_common_pvt.g_byprod_ret_txn_action;
1296 --(32)
1297 l_mmti_rec.lpn_id := l_mmt_rec.transfer_lpn_id;
1298 ELSE
1299 --l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return THEN
1300 l_mmti_rec.transaction_type_id := gme_common_pvt.g_prod_completion;
1301 l_mmti_rec.transaction_action_id :=
1302 gme_common_pvt.g_byprod_comp_txn_action;
1303 --(31)
1304 l_mmti_rec.transfer_lpn_id := l_mmt_rec.lpn_id;
1305 END IF;
1306
1307 IF (g_debug <= gme_debug.g_log_statement) THEN
1308 gme_debug.put_line ( g_pkg_name
1309 || '.'
1310 || l_api_name
1311 || ':'
1312 || 'sending transaction_type_id:'
1313 || l_mmti_rec.transaction_type_id);
1314 gme_debug.put_line ( g_pkg_name
1315 || '.'
1316 || l_api_name
1317 || ':'
1318 || 'sending transaction_action_id:'
1319 || l_mmti_rec.transaction_action_id);
1320 END IF;
1321
1322 -- set for delete transaction
1323 l_mmti_rec.source_line_id := l_mmt_rec.transaction_id;
1324 gme_debug.put_line ( g_pkg_name
1325 || '.'
1326 || l_api_name
1327 || ':'
1328 || 'calling create trans with ='
1329 || l_mmti_rec.source_line_id);
1330
1331 -- changes for phantom
1332 --Bug#8453427 Start
1333 /*Bug#8453427 Added the delete call for the phantom transactions for the product return transactions, as the
1334 corresponsing phantom transctions would be of the production completion types and always +ve sign transactions should be
1335 created first */
1336 IF (l_mmti_rec.transaction_type_id = gme_common_pvt.g_prod_return OR
1337 l_mmti_rec.transaction_type_id = gme_common_pvt.g_byprod_return ) THEN
1338 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
1339 IF (g_debug <= gme_debug.g_log_statement) THEN
1340 gme_debug.put_line ( g_pkg_name
1341 || '.'
1342 || l_api_name
1343 || ':'
1344 || 'deleting for phantom:'
1345 || l_mat_dtl_rec.phantom_line_id);
1346 END IF;
1347
1348 OPEN cur_get_ph_txns (p_transaction_id);
1349
1350 FETCH cur_get_ph_txns
1351 INTO m_transaction_id;
1352
1353 CLOSE cur_get_ph_txns;
1354
1355 IF m_transaction_id IS NOT NULL THEN
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 || 'calling delete txns for phantom:'
1362 || m_transaction_id);
1363 END IF;
1364
1365 delete_material_txn (p_transaction_id => m_transaction_id
1366 ,p_txns_pair => 1
1367 ,p_trans_date => l_trans_date
1368 ,x_return_status => l_return_status);
1369 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1370 IF (g_debug <= gme_debug.g_log_statement) THEN
1371 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
1372 END IF;
1373 RAISE delete_material_txn_err;
1374 END IF; -- ret status
1375 ELSE
1376 IF (g_debug <= gme_debug.g_log_statement) THEN
1377 gme_debug.put_line ( g_pkg_name
1378 || '.'
1379 || l_api_name
1380 || ':'
1381 || 'no phantom txns found for '
1382 || l_transaction_id);
1383 END IF;
1384 END IF; -- m_transaction_id is not null
1385 END IF;
1386 END IF;
1387 --Bug#8453427 End
1388
1389 -- Bug 9717803 - Move transaction pair update to happen before create of mat txn.
1390 -- Insert into gme_transactions_pairs table
1391 -- code need to added for INSERT INTO GME_TRANSACTION_PAIRS tables
1392 -- which column will carry the material detail ld
1393 IF (g_debug <= gme_debug.g_log_statement) THEN
1394 gme_debug.put_line ( g_pkg_name
1395 || '.'
1396 || l_api_name
1397 || ':'
1398 || 'inserting into pairs table transaction_id:'
1399 || l_transaction_id);
1400 gme_debug.put_line ( g_pkg_name
1401 || '.'
1402 || l_api_name
1403 || ':'
1404 || 'inserting into pairs table batch_id:'
1405 || l_mat_dtl_rec.batch_id);
1406 gme_debug.put_line
1407 ( g_pkg_name
1408 || '.'
1409 || l_api_name
1410 || ':'
1411 || 'inserting into pairs table material_detail_id:'
1412 || l_mat_dtl_rec.material_detail_id);
1413 gme_debug.put_line ( g_pkg_name
1414 || '.'
1415 || l_api_name
1416 || ':'
1417 || 'inserting into pairs table pair_type:'
1418 || gme_common_pvt.g_pairs_reversal_type);
1419 END IF;
1420
1421 INSERT INTO gme_transaction_pairs
1422 (batch_id, material_detail_id
1423 ,transaction_id1, transaction_id2
1424 ,pair_type)
1425 VALUES (l_mat_dtl_rec.batch_id, l_mat_dtl_rec.material_detail_id
1426 ,l_mmt_rec.transaction_id, NULL
1427 ,gme_common_pvt.g_pairs_reversal_type);
1428
1429
1430 -- with the new rec- call the create txn
1431 create_material_txn (p_mmti_rec => l_mmti_rec
1432 ,p_mmli_tbl => l_mmli_tbl
1433 ,p_phantom_trans => 1
1434 ,x_return_status => l_return_status);
1435
1436 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1437 IF (g_debug <= gme_debug.g_log_statement) THEN
1438 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
1439 END IF;
1440 RAISE create_material_txn_err;
1441 END IF;
1442
1443 IF (g_debug <= gme_debug.g_log_statement) THEN
1444 gme_debug.put_line ( g_pkg_name
1445 || '.'
1446 || l_api_name
1447 || ':'
1448 || 'status from create :'
1449 || l_return_status);
1450 END IF;
1451
1452
1453 /*Bug#8453427 Added the below if condition as the phantom transactions already created
1454 above for the product and by product return transaction types. */
1455 IF (l_mmti_rec.transaction_type_id <> gme_common_pvt.g_prod_return AND
1456 l_mmti_rec.transaction_type_id <> gme_common_pvt.g_byprod_return ) THEN
1457 -- changes for phantom
1458 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL AND p_txns_pair IS NULL THEN
1459 IF (g_debug <= gme_debug.g_log_statement) THEN
1460 gme_debug.put_line ( g_pkg_name
1461 || '.'
1462 || l_api_name
1463 || ':'
1464 || 'deleting for phantom:'
1465 || l_mat_dtl_rec.phantom_line_id);
1466 END IF;
1467
1468 OPEN cur_get_ph_txns (p_transaction_id);
1469
1470 FETCH cur_get_ph_txns
1471 INTO m_transaction_id;
1472
1473 CLOSE cur_get_ph_txns;
1474
1475 IF m_transaction_id IS NOT NULL THEN
1476 IF (g_debug <= gme_debug.g_log_statement) THEN
1477 gme_debug.put_line ( g_pkg_name
1478 || '.'
1479 || l_api_name
1480 || ':'
1481 || 'calling delete txns for phantom:'
1482 || m_transaction_id);
1483 END IF;
1484
1485 delete_material_txn (p_transaction_id => m_transaction_id
1486 ,p_txns_pair => 1
1487 ,p_trans_date => l_trans_date
1488 ,x_return_status => l_return_status);
1489 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1490 IF (g_debug <= gme_debug.g_log_statement) THEN
1491 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create mat txn');
1492 END IF;
1493 RAISE delete_material_txn_err;
1494 END IF; -- ret status
1495 ELSE
1496 IF (g_debug <= gme_debug.g_log_statement) THEN
1497 gme_debug.put_line ( g_pkg_name
1498 || '.'
1499 || l_api_name
1500 || ':'
1501 || 'no phantom txns found for '
1502 || l_transaction_id);
1503 END IF;
1504 END IF; -- m_transaction_id is not null
1505 END IF;
1506 END IF;
1507 IF (g_debug <= gme_debug.g_log_statement) THEN
1508 gme_debug.put_line ( g_pkg_name
1509 || '.'
1510 || l_api_name
1511 || ':'
1512 || 'Exiting with '
1513 || x_return_status);
1514 END IF;
1515 EXCEPTION
1516 WHEN delete_material_txn_err THEN
1517 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from delete material txn');
1518 x_return_status := l_return_status ;
1519 WHEN get_trans_err THEN
1520 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from get_transactions');
1521 x_return_status := l_return_status ;
1522 WHEN create_material_txn_err THEN
1523 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from create material txn');
1524 x_return_status := l_return_status ;
1525 WHEN fnd_api.g_exc_error THEN
1526 x_return_status := fnd_api.g_ret_sts_error;
1527 WHEN fnd_api.g_exc_unexpected_error THEN
1528 IF (g_debug <= gme_debug.g_log_statement) THEN
1529 gme_debug.put_line ( g_pkg_name
1530 || '.'
1531 || l_api_name
1532 || ':'
1533 || 'Unexpected');
1534 END IF;
1535
1536 x_return_status := fnd_api.g_ret_sts_unexp_error;
1537 WHEN OTHERS THEN
1538 x_return_status := fnd_api.g_ret_sts_unexp_error;
1539 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1540
1541 IF (g_debug <= gme_debug.g_log_statement) THEN
1542 gme_debug.put_line ( g_pkg_name
1543 || '.'
1544 || l_api_name
1545 || ':'
1546 || 'WHEN OTHERS:'
1547 || SQLERRM);
1548 END IF;
1549 END delete_material_txn;
1550
1551 /* +==========================================================================+
1552 | PROCEDURE NAME
1553 | build_txn_inter
1554 |
1555 | USAGE
1556 | Inserts the transaction to interface table
1557 |
1558 | ARGUMENTS
1559 | p_mmti_rec -- mtl_transaction_interface rowtype
1560 | p_mmli_tbl -- table of mtl_trans_lots_inter_tbl as input
1561 |
1562 | RETURNS
1563 | returns via x_status OUT parameters
1564 |
1565 | HISTORY
1566 | Created 02-Feb-05 Pawan Kumar
1567 |
1568 | G. Muratore 06-MAY-2011 Bug 12391271
1569 | Initialize primary_quantity when necessary.
1570 | Also pass lpn_id value to build lot inter function.
1571 |
1572 | G. Muratore 12-APR-2012 Bug 13925279
1573 | Assign the user entered expiration date.
1574 |
1575 | G. Muratore 15-MAY-2012 Bug 14065291
1576 | Initialize secondary_quantity when necessary.
1577 +==========================================================================+ */
1578 PROCEDURE build_txn_inter (
1579 p_mmti_rec IN mtl_transactions_interface%ROWTYPE
1580 ,p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl
1581 ,p_assign_phantom IN NUMBER DEFAULT 0
1582 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1583 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
1584 ,x_return_status OUT NOCOPY VARCHAR2)
1585 IS
1586 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1587 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
1588 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER';
1589 x_header_id NUMBER;
1590 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1591 l_insert_hdr BOOLEAN;
1592 build_txn_inter_err EXCEPTION;
1593 build_txn_inter_lot_err EXCEPTION;
1594 lot_expired_err EXCEPTION;
1595 insert_hdr_err EXCEPTION ;
1596
1597 -- Bug 12391271
1598 l_prim_qty NUMBER;
1599 l_primary_uom VARCHAR2(3);
1600 l_item_no mtl_system_items_kfv.concatenated_segments%TYPE;
1601
1602 -- Bug 14065291
1603 l_secondary_qty NUMBER;
1604 l_secondary_uom VARCHAR2(3);
1605
1606 -- Bug 14065291
1607 CURSOR Cur_get_material_info (v_organization_id NUMBER, v_inventory_item_id NUMBER) IS
1608 SELECT i.primary_uom_code, k.concatenated_segments, i.secondary_uom_code
1609 FROM mtl_system_items_b i, mtl_system_items_kfv k
1610 WHERE v_inventory_item_id = i.inventory_item_id
1611 AND v_organization_id = i.organization_id
1612 AND v_inventory_item_id = k.inventory_item_id
1613 AND v_organization_id = k.organization_id;
1614
1615
1616 um_convert_error EXCEPTION;
1617
1618 BEGIN
1619 -- Initially let us assign the return status to success
1620 x_return_status := fnd_api.g_ret_sts_success;
1621
1622 IF (g_debug <= gme_debug.g_log_statement) THEN
1623 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1624 || 'Entering');
1625 END IF;
1626
1627 l_mmti_rec := p_mmti_rec;
1628 l_mmli_tbl := p_mmli_tbl;
1629 /* Bug 4929610 Added code to pass parameter */
1630 IF (l_mmli_tbl.COUNT > 0) THEN
1631 l_insert_hdr := FALSE;
1632 ELSE
1633 l_insert_hdr := TRUE;
1634 END IF;
1635 build_txn_inter_hdr (p_mmti_rec => p_mmti_rec
1636 ,p_assign_phantom => p_assign_phantom
1637 ,x_mmti_rec => x_mmti_rec
1638 ,x_return_status => l_return_status
1639 ,p_insert_hdr => l_insert_hdr);
1640
1641 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1642 IF (g_debug <= gme_debug.g_log_statement) THEN
1643 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from build inter hdr');
1644 END IF;
1645 RAISE build_txn_inter_err;
1646 END IF;
1647
1648 IF (g_debug <= gme_debug.g_log_statement) THEN
1649 gme_debug.put_line ( g_pkg_name
1650 || '.'
1651 || l_api_name
1652 || ':'
1653 || 'after header- inserting lot');
1654 gme_debug.put_line ( g_pkg_name
1655 || '.'
1656 || l_api_name
1657 || ':'
1658 || 'after header- inter_id:'
1659 || x_mmti_rec.transaction_interface_id);
1660 gme_debug.put_line ( g_pkg_name
1661 || '.'
1662 || l_api_name
1663 || ':'
1664 || 'after header- header_id:'
1665 || x_mmti_rec.transaction_header_id);
1666 gme_debug.put_line ( g_pkg_name
1667 || '.'
1668 || l_api_name
1669 || ':'
1670 || 'after header- trasn_type:'
1671 || x_mmti_rec.transaction_type_id);
1672 END IF;
1673
1674 IF (l_mmli_tbl.COUNT > 0) THEN
1675 FOR i IN 1 .. l_mmli_tbl.COUNT LOOP
1676 IF (g_debug <= gme_debug.g_log_statement) THEN
1677 gme_debug.put_line ( g_pkg_name
1678 || '.'
1679 || l_api_name
1680 || ':'
1681 || 'lot_number '
1682 || l_mmli_tbl (i).lot_number);
1683 gme_debug.put_line ( g_pkg_name
1684 || '.'
1685 || l_api_name
1686 || ':'
1687 || 'transaction_quantity '
1688 || l_mmli_tbl (i).transaction_quantity);
1689 END IF;
1690 /* Bug 4929610 added lot expiry check */
1691 IF (x_mmti_rec.transaction_type_id = gme_common_pvt.g_ing_issue) THEN
1692 IF (gme_transactions_pvt.is_lot_expired (p_organization_id => x_mmti_rec.organization_id,
1693 p_inventory_item_id => x_mmti_rec.inventory_item_id,
1694 p_lot_number => l_mmli_tbl(i).lot_number,
1695 p_date => x_mmti_rec.transaction_date)) THEN
1696 RAISE lot_expired_err;
1697 END IF;
1698 END IF;
1699
1700 -- Bug 14065291 - Renamed and moved cursor outside of if and also fetch secondary.
1701 OPEN Cur_get_material_info (x_mmti_rec.organization_id, x_mmti_rec.inventory_item_id);
1702 FETCH Cur_get_material_info INTO l_primary_uom, l_item_no, l_secondary_uom;
1703 CLOSE Cur_get_material_info;
1704
1705 -- Bug 12391271 - Let's make sure Primary quantity is initialized.
1706 IF (l_mmli_tbl(i).primary_quantity IS NULL) THEN
1707 l_prim_qty := l_mmli_tbl(i).transaction_quantity;
1708 IF (l_primary_uom <> x_mmti_rec.transaction_uom) THEN
1709
1710 l_prim_qty := inv_convert.inv_um_convert
1711 (item_id => x_mmti_rec.inventory_item_id
1712 ,lot_number => l_mmli_tbl(i).lot_number
1713 ,organization_id => x_mmti_rec.organization_id
1714 ,precision => gme_common_pvt.g_precision
1715 ,from_quantity => l_mmli_tbl(i).transaction_quantity
1716 ,from_unit => x_mmti_rec.transaction_uom
1717 ,to_unit => l_primary_uom
1718 ,from_name => NULL
1719 ,to_name => NULL);
1720
1721 IF l_prim_qty = -99999 THEN
1722 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1723 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inv_convert.inv_um_convert returned error');
1724 END IF;
1725 RAISE um_convert_error;
1726 END IF;
1727
1728 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1729 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after call to inv_convert.inv_um_convert');
1730 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_prim_qty= '||to_char(l_prim_qty));
1731 END IF;
1732 END IF;
1733 l_mmli_tbl(i).primary_quantity := l_prim_qty;
1734 END IF;
1735
1736 -- Bug 14065291 - Let's make sure Secondary quantity is initialized when dual controlled.
1737 IF (l_mmli_tbl(i).secondary_transaction_quantity IS NULL AND l_secondary_uom IS NOT NULL) THEN
1738 l_secondary_qty := l_mmli_tbl(i).transaction_quantity;
1739 IF (l_secondary_uom <> x_mmti_rec.transaction_uom) THEN
1740
1741 l_secondary_qty := inv_convert.inv_um_convert
1742 (item_id => x_mmti_rec.inventory_item_id
1743 ,lot_number => l_mmli_tbl(i).lot_number
1744 ,organization_id => x_mmti_rec.organization_id
1745 ,precision => gme_common_pvt.g_precision
1746 ,from_quantity => l_mmli_tbl(i).transaction_quantity
1747 ,from_unit => x_mmti_rec.transaction_uom
1748 ,to_unit => l_secondary_uom
1749 ,from_name => NULL
1750 ,to_name => NULL);
1751
1752 IF l_secondary_qty = -99999 THEN
1753 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1754 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' inv_convert.inv_um_convert returned error');
1755 END IF;
1756 RAISE um_convert_error;
1757 END IF;
1758
1759 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
1760 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' after call to inv_convert.inv_um_convert');
1761 gme_debug.put_line (g_pkg_name||'.'||l_api_name||' l_secondary_qty= '||to_char(l_secondary_qty));
1762 END IF;
1763 END IF;
1764 l_mmli_tbl(i).secondary_transaction_quantity := l_secondary_qty;
1765 END IF;
1766
1767 -- Bug 12391271 - pass in lpn_id value.
1768 build_txn_inter_lot
1769 (p_trans_inter_id => x_mmti_rec.transaction_interface_id
1770 ,p_transaction_type_id => x_mmti_rec.transaction_type_id
1771 ,p_inventory_item_id => x_mmti_rec.inventory_item_id
1772 ,p_subinventory_code => x_mmti_rec.subinventory_code
1773 ,p_locator_id => x_mmti_rec.locator_id
1774 ,p_lpn_id => x_mmti_rec.lpn_id
1775 ,p_mmli_rec => l_mmli_tbl (i)
1776 ,x_mmli_rec => x_mmli_tbl (i)
1777 ,x_return_status => l_return_status);
1778
1779 IF l_return_status <> fnd_api.g_ret_sts_success THEN
1780 IF (g_debug <= gme_debug.g_log_statement) THEN
1781 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Error from build lot inter');
1782 END IF;
1783 RAISE build_txn_inter_lot_err;
1784 END IF;
1785
1786 -- Bug 13925279 - Assign the user entered expiration date.
1787 IF (x_mmti_rec.transaction_type_id <> gme_common_pvt.g_ing_issue AND
1788 l_mmli_tbl(i).lot_expiration_date IS NOT NULL) THEN
1789 UPDATE mtl_transaction_lots_interface
1790 SET lot_expiration_date = l_mmli_tbl(i).lot_expiration_date
1791 WHERE transaction_interface_id = x_mmti_rec.transaction_interface_id;
1792 END IF;
1793
1794 END LOOP;
1795 END IF; --l_mmli_tbl.count
1796
1797 /* Bug 4929610 Added code to insert if not inserted originally */
1798 IF NOT(l_insert_hdr) THEN
1799 insert_txn_inter_hdr(p_mmti_rec => x_mmti_rec,
1800 x_return_status => l_return_status);
1801 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
1802 RAISE insert_hdr_err;
1803 END IF;
1804 END IF;
1805
1806 IF (g_debug <= gme_debug.g_log_statement) THEN
1807 gme_debug.put_line ( g_pkg_name
1808 || '.'
1809 || l_api_name
1810 || ':'
1811 || 'Exiting with '
1812 || x_return_status);
1813 END IF;
1814 EXCEPTION
1815 WHEN insert_hdr_err THEN
1816 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'insert_hdr_err');
1817 x_return_status := l_return_status;
1818 WHEN um_convert_error THEN
1819 fnd_message.set_name ('GMI', 'IC_API_UOM_CONVERSION_ERROR');
1820 fnd_message.set_token ('ITEM_NO', l_item_no);
1821 fnd_message.set_token ('FROM_UOM',x_mmti_rec.transaction_uom);
1822 fnd_message.set_token ('TO_UOM', l_primary_uom);
1823 fnd_msg_pub.ADD;
1824 x_return_status := FND_API.g_ret_sts_error;
1825 WHEN lot_expired_err THEN
1826 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'lot_expired_err');
1827 x_return_status := 'T';
1828 WHEN build_txn_inter_lot_err THEN
1829 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'build_txn_inter_lot_err');
1830 x_return_status := l_return_status;
1831 WHEN build_txn_inter_err THEN
1832 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'build_txn_inter_err');
1833 x_return_status := l_return_status;
1834 WHEN fnd_api.g_exc_error THEN
1835 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'user defined error');
1836 x_return_status := fnd_api.g_ret_sts_error;
1837 WHEN fnd_api.g_exc_unexpected_error THEN
1838 gme_debug.put_line ( g_pkg_name
1839 || '.'
1840 || l_api_name
1841 || ':'
1842 || 'unexp'
1843 || SQLERRM);
1844 x_return_status := fnd_api.g_ret_sts_unexp_error;
1845 WHEN OTHERS THEN
1846 x_return_status := fnd_api.g_ret_sts_unexp_error;
1847 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1848
1849 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
1850 gme_debug.put_line ( g_pkg_name
1851 || '.'
1852 || l_api_name
1853 || ':'
1854 || 'WHEN OTHERS:'
1855 || SQLERRM);
1856 END IF;
1857 END build_txn_inter;
1858
1859 /* +==========================================================================+
1860 | PROCEDURE NAME
1861 | build_txn_inter_hdr
1862 |
1863 | USAGE
1864 | Inserts the transaction to interface table
1865 |
1866 | ARGUMENTS
1867 | p_mmti_rec -- mtl_transaction_interface rowtype
1868 |
1869 |
1870 | RETURNS
1871 | returns via x_status OUT parameters
1872 |
1873 | HISTORY
1874 | Created 02-Feb-05 Pawan Kumar
1875 | 13-Sep-05 Namit Singhi - Modified to include insert into transfer_lpn_id.
1876 |
1877 +==========================================================================+ */
1878 PROCEDURE build_txn_inter_hdr (
1879 p_mmti_rec IN mtl_transactions_interface%ROWTYPE
1880 ,p_assign_phantom IN NUMBER DEFAULT 0
1881 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
1882 ,x_return_status OUT NOCOPY VARCHAR2
1883 ,p_insert_hdr IN BOOLEAN DEFAULT TRUE)
1884 IS
1885 CURSOR get_location (v_org_id IN NUMBER
1886 ,v_sub_inv IN VARCHAR2
1887 ,v_loc_id IN NUMBER) IS
1888 SELECT substr(concatenated_segments,1,100)
1889 FROM wms_item_locations_kfv
1890 WHERE organization_id = v_org_id
1891 AND subinventory_code = v_sub_inv
1892 AND inventory_location_id (+) = v_loc_id;
1893 l_mmti_rec mtl_transactions_interface%ROWTYPE;
1894 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_hdr';
1895 x_header_id NUMBER;
1896 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
1897 l_item VARCHAR2(100);
1898 l_type VARCHAR2(100);
1899 l_locator VARCHAR2(100);
1900 material_status_err EXCEPTION ;
1901 insert_hdr_err EXCEPTION ;
1902 BEGIN
1903 -- Initially let us assign the return status to success
1904 x_return_status := fnd_api.g_ret_sts_success;
1905
1906 IF (g_debug <= gme_debug.g_log_statement) THEN
1907 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
1908 || 'Entering');
1909 END IF;
1910
1911 l_mmti_rec := p_mmti_rec;
1912
1913 IF gme_common_pvt.g_transaction_header_id IS NULL THEN
1914 SELECT mtl_material_transactions_s.NEXTVAL
1915 INTO gme_common_pvt.g_transaction_header_id
1916 FROM DUAL;
1917
1918 IF (g_debug <= gme_debug.g_log_statement) THEN
1919 gme_debug.put_line ( g_pkg_name
1920 || '.'
1921 || l_api_name
1922 || ':'
1923 || 'GME_COMMON_PVT.g_transaction_header_id '
1924 || gme_common_pvt.g_transaction_header_id);
1925 END IF;
1926 END IF;
1927
1928 l_mmti_rec.transaction_header_id :=
1929 gme_common_pvt.g_transaction_header_id;
1930
1931 IF (g_debug <= gme_debug.g_log_statement) THEN
1932 gme_debug.put_line ( g_pkg_name
1933 || '.'
1934 || l_api_name
1935 || ':'
1936 || 'l_mmti_rec.transaction_header_id '
1937 || l_mmti_rec.transaction_header_id);
1938 END IF;
1939
1940 SELECT mtl_material_transactions_s.NEXTVAL
1941 INTO l_mmti_rec.transaction_interface_id
1942 FROM DUAL;
1943
1944 IF l_mmti_rec.transaction_type_id IN
1945 (gme_common_pvt.g_ing_return
1946 ,gme_common_pvt.g_prod_completion
1947 ,gme_common_pvt.g_byprod_completion) THEN
1948
1949 -- Bug 9770408 - Do not overwrite transaction_batch_seq value if it is passed in.
1950 IF l_mmti_rec.transaction_batch_seq IS NULL THEN
1951 l_mmti_rec.transaction_batch_seq := 1;
1952 END IF;
1953
1954 -- l_mmti_rec.transaction_batch_seq := 1;
1955 l_mmti_rec.transaction_batch_id := l_mmti_rec.transaction_header_id ;
1956 l_mmti_rec.transaction_quantity :=
1957 ABS(l_mmti_rec.transaction_quantity);
1958 l_mmti_rec.secondary_transaction_quantity :=
1959 ABS(l_mmti_rec.secondary_transaction_quantity);
1960 ELSE
1961 l_mmti_rec.transaction_batch_seq := 100;
1962 l_mmti_rec.transaction_batch_id := l_mmti_rec.transaction_header_id ;
1963 l_mmti_rec.transaction_quantity :=
1964 (-1) * ABS(l_mmti_rec.transaction_quantity);
1965 l_mmti_rec.secondary_transaction_quantity :=
1966 (-1) * ABS(l_mmti_rec.secondary_transaction_quantity);
1967 END IF;
1968
1969 -- Code for checking material status
1970 IF (inv_material_status_grp.is_status_applicable
1971 (p_wms_installed => NULL
1972 ,p_trx_status_enabled => NULL
1973 ,p_trx_type_id => l_mmti_rec.transaction_type_id
1974 ,p_lot_status_enabled => NULL
1975 ,p_serial_status_enabled => NULL
1976 ,p_organization_id => l_mmti_rec.organization_id
1977 ,p_inventory_item_id => l_mmti_rec.inventory_item_id
1978 ,p_sub_code => l_mmti_rec.subinventory_code
1979 ,p_locator_id => l_mmti_rec.locator_id
1980 ,p_lot_number => NULL
1981 ,p_serial_number => NULL
1982 ,p_object_type => 'A') = 'N') THEN
1983 IF (g_debug <= gme_debug.g_log_statement) THEN
1984 gme_debug.put_line ( g_pkg_name
1985 || '.'
1986 || l_api_name
1987 || ':'
1988 || 'material status check not valid for item '
1989 || l_mmti_rec.inventory_item_id);
1990 END IF;
1991 RAISE material_status_err;
1992 END IF; /* inv_material_status_grp.is_status_applicable */
1993 -- for a phantom transaction- asssign value to transaction_refernece
1994 IF (g_debug <= gme_debug.g_log_statement) THEN
1995 gme_debug.put_line ( g_pkg_name
1996 || '.'
1997 || l_api_name
1998 || 'Material Status is VALID');
1999 gme_debug.put_line ( g_pkg_name
2000 || '.'
2001 || l_api_name
2002 || 'p_assign_phantom: '
2003 || p_assign_phantom);
2004 END IF;
2005
2006 IF p_assign_phantom = 1 THEN
2007 l_mmti_rec.transaction_reference :=
2008 (l_mmti_rec.transaction_interface_id);
2009 END IF;
2010
2011 IF (g_debug <= gme_debug.g_log_statement) THEN
2012 gme_debug.put_line ( g_pkg_name
2013 || '.'
2014 || l_api_name
2015 || ':'
2016 || 'transaction_interface_id: '
2017 || l_mmti_rec.transaction_interface_id);
2018 gme_debug.put_line ( g_pkg_name
2019 || '.'
2020 || l_api_name
2021 || ':'
2022 || 'transaction_source_id: '
2023 || l_mmti_rec.transaction_source_id);
2024 gme_debug.put_line ( g_pkg_name
2025 || '.'
2026 || l_api_name
2027 || ':'
2028 || 'transaction_type_id: '
2029 || l_mmti_rec.transaction_type_id);
2030 gme_debug.put_line ( g_pkg_name
2031 || '.'
2032 || l_api_name
2033 || ':'
2034 || 'transaction_source_type_id: '
2035 || l_mmti_rec.transaction_source_type_id);
2036 gme_debug.put_line ( g_pkg_name
2037 || '.'
2038 || l_api_name
2039 || ':'
2040 || 'transaction_quantity: '
2041 || l_mmti_rec.transaction_quantity);
2042 gme_debug.put_line ( g_pkg_name
2043 || '.'
2044 || l_api_name
2045 || ':'
2046 || 'transaction_uom: '
2047 || l_mmti_rec.transaction_uom);
2048 gme_debug.put_line ( g_pkg_name
2049 || '.'
2050 || l_api_name
2051 || ':'
2052 || 'secondary_transaction_quantity: '
2053 || l_mmti_rec.secondary_transaction_quantity);
2054 gme_debug.put_line ( g_pkg_name
2055 || '.'
2056 || l_api_name
2057 || ':'
2058 || 'secondary_uom_code: '
2059 || l_mmti_rec.secondary_uom_code);
2060 gme_debug.put_line ( g_pkg_name
2061 || '.'
2062 || l_api_name
2063 || ':'
2064 || 'primary_quantity: '
2065 || l_mmti_rec.primary_quantity);
2066 gme_debug.put_line ( g_pkg_name
2067 || '.'
2068 || l_api_name
2069 || ':'
2070 || 'process_flag: '
2071 || l_mmti_rec.process_flag);
2072 gme_debug.put_line ( g_pkg_name
2073 || '.'
2074 || l_api_name
2075 || ':'
2076 || 'inventory_item_id: '
2077 || l_mmti_rec.inventory_item_id);
2078 gme_debug.put_line ( g_pkg_name
2079 || '.'
2080 || l_api_name
2081 || ':'
2082 || 'revision: '
2083 || l_mmti_rec.revision);
2084 gme_debug.put_line ( g_pkg_name
2085 || '.'
2086 || l_api_name
2087 || ':'
2088 || 'transfer_lpn_id: '
2089 || l_mmti_rec.transfer_lpn_id);
2090 gme_debug.put_line ( g_pkg_name
2091 || '.'
2092 || l_api_name
2093 || ':'
2094 || 'organization_id: '
2095 || l_mmti_rec.organization_id);
2096 gme_debug.put_line ( g_pkg_name
2097 || '.'
2098 || l_api_name
2099 || ':'
2100 || 'subinventory_code: '
2101 || TO_CHAR (l_mmti_rec.subinventory_code) );
2102 gme_debug.put_line ( g_pkg_name
2103 || '.'
2104 || l_api_name
2105 || ':'
2106 || 'locator_id:'
2107 || l_mmti_rec.locator_id);
2108 gme_debug.put_line ( g_pkg_name
2109 || '.'
2110 || l_api_name
2111 || ':'
2112 || 'source_line_id: '
2113 || l_mmti_rec.source_line_id);
2114 gme_debug.put_line ( g_pkg_name
2115 || '.'
2116 || l_api_name
2117 || ':'
2118 || 'trx_source_line_id: '
2119 || l_mmti_rec.trx_source_line_id);
2120 gme_debug.put_line ( g_pkg_name
2121 || '.'
2122 || l_api_name
2123 || ':'
2124 || 'source_header_id: '
2125 || l_mmti_rec.source_header_id);
2126 gme_debug.put_line ( g_pkg_name
2127 || '.'
2128 || l_api_name
2129 || ':'
2130 || 'transaction_source_name: '
2131 || l_mmti_rec.transaction_source_name);
2132 gme_debug.put_line ( g_pkg_name
2133 || '.'
2134 || l_api_name
2135 || ':'
2136 || 'transaction_mode: '
2137 || l_mmti_rec.transaction_mode);
2138 gme_debug.put_line ( g_pkg_name
2139 || '.'
2140 || l_api_name
2141 || ':'
2142 || 'last_updated_by: '
2143 || gme_common_pvt.g_user_ident);
2144 gme_debug.put_line ( g_pkg_name
2145 || '.'
2146 || l_api_name
2147 || ':'
2148 || 'transaction_reference: '
2149 || l_mmti_rec.transaction_reference);
2150 gme_debug.put_line ( g_pkg_name
2151 || '.'
2152 || l_api_name
2153 || ':'
2154 || 'transaction_batch_id: '
2155 || l_mmti_rec.transaction_batch_id);
2156 gme_debug.put_line ( g_pkg_name
2157 || '.'
2158 || l_api_name
2159 || ':'
2160 || 'transaction_batch_seq: '
2161 || l_mmti_rec.transaction_batch_seq);
2162 gme_debug.put_line ( g_pkg_name
2163 || '.'
2164 || l_api_name
2165 || ':'
2166 || 'reservation_quantity: '
2167 || l_mmti_rec.reservation_quantity);
2168 gme_debug.put_line ( g_pkg_name
2169 || '.'
2170 || l_api_name
2171 || ':'
2172 || 'transaction_sequence_id: '
2173 || l_mmti_rec.transaction_sequence_id);
2174 gme_debug.put_line ( g_pkg_name
2175 || '.'
2176 || l_api_name
2177 || ':'
2178 || 'reason_id: '
2179 || l_mmti_rec.reason_id);
2180 gme_debug.put_line ( g_pkg_name
2181 || '.'
2182 || l_api_name
2183 || ':'
2184 || 'transfer_lpn_id: '
2185 || l_mmti_rec.transfer_lpn_id);
2186 gme_debug.put_line ( g_pkg_name
2187 || '.'
2188 || l_api_name
2189 || ':'
2190 || 'transaction_date: '
2191 || to_char(l_mmti_rec.transaction_date,'YYYY-MON-DD HH24:MI:SS'));
2192 END IF;
2193 /* Bug 4929610 fixed */
2194 IF (p_insert_hdr) THEN
2195 insert_txn_inter_hdr(p_mmti_rec => l_mmti_rec,
2196 x_return_status => l_return_status);
2197 IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
2198 RAISE insert_hdr_err;
2199 END IF;
2200 END IF;
2201 x_mmti_rec := l_mmti_rec;
2202
2203 IF (g_debug <= gme_debug.g_log_statement) THEN
2204 gme_debug.put_line ( g_pkg_name
2205 || '.'
2206 || l_api_name
2207 || ':'
2208 || 'after inserting header with status:'||x_return_status);
2209
2210 END IF;
2211 EXCEPTION
2212 WHEN insert_hdr_err THEN
2213 x_return_status := l_return_status;
2214 WHEN material_status_err THEN
2215 SELECT substr(concatenated_segments,1,100)
2216 INTO l_item
2217 FROM mtl_system_items_kfv
2218 WHERE organization_id = l_mmti_rec.organization_id
2219 AND inventory_item_id = l_mmti_rec.inventory_item_id;
2220 OPEN get_location(l_mmti_rec.organization_id, l_mmti_rec.subinventory_code, l_mmti_rec.locator_id);
2221 FETCH get_location INTO l_locator;
2222 CLOSE get_location;
2223 SELECT transaction_type_name
2224 INTO l_type
2225 FROM mtl_transaction_types
2226 WHERE transaction_type_id = l_mmti_rec.transaction_type_id;
2227 IF l_locator IS NOT NULL THEN
2228 gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_SUB_LOC'
2229 ,'TRANSTYPE',l_type,'ITEM',l_item
2230 ,'SUBINV',l_mmti_rec.subinventory_code
2231 ,'LOCN',l_locator);
2232 ELSE
2233 gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_SUB'
2234 ,'TRANSTYPE',l_type,'ITEM',l_item
2235 ,'SUBINV',l_mmti_rec.subinventory_code);
2236 END IF;
2237 gme_debug.put_line ( g_pkg_name
2238 || '.'
2239 || l_api_name
2240 || ':'
2241 || 'material status invalid for item, subinventory, locator etc'
2242 );
2243 x_return_status := 'T';
2244 WHEN fnd_api.g_exc_unexpected_error THEN
2245 gme_debug.put_line ( g_pkg_name
2246 || '.'
2247 || l_api_name
2248 || ':'
2249 || 'unexp'
2250 || SQLERRM);
2251 x_return_status := fnd_api.g_ret_sts_unexp_error;
2252 WHEN OTHERS THEN
2253 x_return_status := fnd_api.g_ret_sts_unexp_error;
2254 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2255
2256 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2257 gme_debug.put_line ( g_pkg_name
2258 || '.'
2259 || l_api_name
2260 || ':'
2261 || 'WHEN OTHERS:'
2262 || SQLERRM);
2263 END IF;
2264 END build_txn_inter_hdr;
2265
2266 /* +==========================================================================+
2267 | PROCEDURE NAME
2268 | build_txn_inter_lot
2269 |
2270 | USAGE
2271 | Inserts the transaction to interface table
2272 |
2273 | ARGUMENTS
2274 |
2275 | p_mmli_rec -- table of mtl_trans_lots_inter_tbl as input
2276 |
2277 | RETURNS
2278 | returns via x_status OUT parameters
2279 |
2280 | HISTORY
2281 | Created 02-Feb-05 Pawan Kumar
2282 |
2283 | G. Muratore 29-Dec-08 Bug 7623144 - add all missing lot attribute columns
2284 | 'C_', 'D_' and 'N_' attribute columns plus lot_attribute_category.
2285 |
2286 | G. Muratore 01-Dec-09 Bug 9170460
2287 | Pass in subinventory and locator id to applicable function.
2288 |
2289 | G. Muratore 06-MAY-2011 Bug 12391271
2290 | Added lpn_id parameter so we can pass it to status_applicable call.
2291 |
2292 | G. Muratore 04-FEB-2013 Bug 16079842 - Rework 13835011 and 14297117.
2293 | Use grade_code and supplier lot number from the passed in record.
2294 +==========================================================================+ */
2295 PROCEDURE build_txn_inter_lot (
2296 p_trans_inter_id IN NUMBER
2297 ,p_transaction_type_id IN NUMBER
2298 ,p_inventory_item_id IN NUMBER
2299 ,p_subinventory_code IN VARCHAR2
2300 ,p_locator_id IN NUMBER
2301 ,p_lpn_id IN NUMBER DEFAULT NULL
2302 ,p_mmli_rec IN mtl_transaction_lots_interface%ROWTYPE
2303 ,x_mmli_rec OUT NOCOPY mtl_transaction_lots_interface%ROWTYPE
2304 ,x_return_status OUT NOCOPY VARCHAR2)
2305 IS
2306 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TXN_INTER_LOT';
2307 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
2308 l_mmli_rec mtl_transaction_lots_interface%ROWTYPE;
2309 l_transaction_type_id NUMBER;
2310 l_inventory_item_id NUMBER;
2311 l_item VARCHAR2(100);
2312 l_type VARCHAR2(100);
2313
2314 material_status_err EXCEPTION;
2315 BEGIN
2316 x_return_status := fnd_api.g_ret_sts_success;
2317
2318 IF (g_debug <= gme_debug.g_log_statement) THEN
2319 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2320 || 'Entering');
2321 gme_debug.put_line ('p_transaction_type_id is '||p_transaction_type_id);
2322 gme_debug.put_line ('p_inventory_item_id is '||p_inventory_item_id);
2323 gme_debug.put_line ('p_subinventory_code is '||p_subinventory_code);
2324 gme_debug.put_line ('p_locator_id is '||p_locator_id);
2325 gme_debug.put_line ('p_lpn_id is '||p_lpn_id);
2326 END IF;
2327
2328 l_mmli_rec := p_mmli_rec;
2329 l_mmli_rec.transaction_interface_id := p_trans_inter_id;
2330 l_transaction_type_id := p_transaction_type_id;
2331 l_inventory_item_id := p_inventory_item_id ;
2332 IF l_transaction_type_id IN
2333 (gme_common_pvt.g_ing_return
2334 ,gme_common_pvt.g_prod_completion
2335 ,gme_common_pvt.g_byprod_completion) THEN
2336 l_mmli_rec.transaction_quantity :=
2337 ABS(l_mmli_rec.transaction_quantity);
2338 l_mmli_rec.secondary_transaction_quantity :=
2339 ABS(l_mmli_rec.secondary_transaction_quantity);
2340 ELSE
2341 l_mmli_rec.transaction_quantity :=
2342 (-1) * ABS(l_mmli_rec.transaction_quantity);
2343 l_mmli_rec.secondary_transaction_quantity :=
2344 (-1) * ABS(l_mmli_rec.secondary_transaction_quantity);
2345 END IF;
2346
2347 -- Bug 9170460 - Pass in subinventory and locator id to applicable function.
2348 -- Bug 12391271 - Pass in lpn_id also.
2349 IF (inv_material_status_grp.is_status_applicable
2350 (p_wms_installed => NULL
2351 ,p_trx_status_enabled => NULL
2352 ,p_trx_type_id => l_transaction_type_id
2353 ,p_lot_status_enabled => NULL
2354 ,p_serial_status_enabled => NULL
2355 ,p_organization_id => gme_common_pvt.g_organization_id
2356 ,p_inventory_item_id => l_inventory_item_id
2357 ,p_sub_code => p_subinventory_code
2358 ,p_locator_id => p_locator_id
2359 ,p_lot_number => l_mmli_rec.lot_number
2360 ,p_serial_number => NULL
2361 ,p_lpn_id => p_lpn_id
2362 ,p_object_type => 'A') = 'N') THEN
2363 IF (g_debug <= gme_debug.g_log_statement) THEN
2364 gme_debug.put_line ( g_pkg_name
2365 || '.'
2366 || l_api_name
2367 || ':'
2368 || 'material status check is NOT valid for lot '
2369 || l_mmli_rec.lot_number);
2370 END IF;
2371 RAISE material_status_err;
2372 END IF; /* inv_material_status_grp.is_status_applicable */
2373
2374 IF (g_debug <= gme_debug.g_log_statement) THEN
2375 gme_debug.put_line ( g_pkg_name
2376 || '.'
2377 || l_api_name
2378 || ':'
2379 || 'Material Status is VALID for lot '
2380 || l_mmli_rec.lot_number);
2381 gme_debug.put_line ( g_pkg_name
2382 || '.'
2383 || l_api_name
2384 || ':'
2385 || 'lot_qty is '
2386 || l_mmli_rec.transaction_quantity);
2387 END IF;
2388 /* Bug#7226474 Added attribute columns as the lot attribute information DFF
2389 * fields can be entered using api */
2390 INSERT INTO mtl_transaction_lots_interface
2391 (transaction_interface_id, last_update_date
2392 ,last_updated_by, last_update_login
2393 ,creation_date, created_by,parent_lot_number
2394 ,lot_number, transaction_quantity
2395 ,primary_quantity
2396 ,secondary_transaction_quantity
2397 ,grade_code -- Bug 16079842
2398 ,supplier_lot_number -- Bug 16079842
2399 ,attribute1
2400 ,attribute2
2401 ,attribute3
2402 ,attribute4
2403 ,attribute5
2404 ,attribute6
2405 ,attribute7
2406 ,attribute8
2407 ,attribute9
2408 ,attribute10
2409 ,attribute11
2410 ,attribute12
2411 ,attribute13
2412 ,attribute14
2413 ,attribute15
2414 ,attribute_category -- ); -- Bug 7623144 Added additional missing columns here for lot attributes.
2415 ,lot_attribute_category
2416 ,c_attribute1
2417 ,c_attribute2
2418 ,c_attribute3
2419 ,c_attribute4
2420 ,c_attribute5
2421 ,c_attribute6
2422 ,c_attribute7
2423 ,c_attribute8
2424 ,c_attribute9
2425 ,c_attribute10
2426 ,c_attribute11
2427 ,c_attribute12
2428 ,c_attribute13
2429 ,c_attribute14
2430 ,c_attribute15
2431 ,c_attribute16
2432 ,c_attribute17
2433 ,c_attribute18
2434 ,c_attribute19
2435 ,c_attribute20
2436 ,d_attribute1
2437 ,d_attribute2
2438 ,d_attribute3
2439 ,d_attribute4
2440 ,d_attribute5
2441 ,d_attribute6
2442 ,d_attribute7
2443 ,d_attribute8
2444 ,d_attribute9
2445 ,d_attribute10
2446 ,n_attribute1
2447 ,n_attribute2
2448 ,n_attribute3
2449 ,n_attribute4
2450 ,n_attribute5
2451 ,n_attribute6
2452 ,n_attribute7
2453 ,n_attribute8
2454 ,n_attribute9
2455 ,n_attribute10)
2456 VALUES ( p_trans_inter_id --transaction_interface_id
2457 ,gme_common_pvt.g_timestamp --last_update_date
2458 ,gme_common_pvt.g_user_ident --last_updated_by
2459 ,gme_common_pvt.g_user_ident --last_update_login
2460 ,gme_common_pvt.g_timestamp --creation_date
2461 ,gme_common_pvt.g_user_ident --created_by
2462 ,l_mmli_rec.parent_lot_number --parent lot_number
2463 /*Bug#7372673*/
2464 ,l_mmli_rec.lot_number --lot_number
2465 ,l_mmli_rec.transaction_quantity --lot_quantity
2466 ,l_mmli_rec.primary_quantity
2467 ,l_mmli_rec.secondary_transaction_quantity
2468 ,l_mmli_rec.grade_code -- Bug 16079842
2469 ,l_mmli_rec.supplier_lot_number -- Bug 16079842
2470 ,l_mmli_rec.attribute1
2471 ,l_mmli_rec.attribute2
2472 ,l_mmli_rec.attribute3
2473 ,l_mmli_rec.attribute4
2474 ,l_mmli_rec.attribute5
2475 ,l_mmli_rec.attribute6
2476 ,l_mmli_rec.attribute7
2477 ,l_mmli_rec.attribute8
2478 ,l_mmli_rec.attribute9
2479 ,l_mmli_rec.attribute10
2480 ,l_mmli_rec.attribute11
2481 ,l_mmli_rec.attribute12
2482 ,l_mmli_rec.attribute13
2483 ,l_mmli_rec.attribute14
2484 ,l_mmli_rec.attribute15
2485 ,l_mmli_rec.attribute_category -- ); -- Bug 7623144 Added aditional missing columns here for lot attributes.
2486 ,l_mmli_rec.lot_attribute_category
2487 ,l_mmli_rec.c_attribute1
2488 ,l_mmli_rec.c_attribute2
2489 ,l_mmli_rec.c_attribute3
2490 ,l_mmli_rec.c_attribute4
2491 ,l_mmli_rec.c_attribute5
2492 ,l_mmli_rec.c_attribute6
2493 ,l_mmli_rec.c_attribute7
2494 ,l_mmli_rec.c_attribute8
2495 ,l_mmli_rec.c_attribute9
2496 ,l_mmli_rec.c_attribute10
2497 ,l_mmli_rec.c_attribute11
2498 ,l_mmli_rec.c_attribute12
2499 ,l_mmli_rec.c_attribute13
2500 ,l_mmli_rec.c_attribute14
2501 ,l_mmli_rec.c_attribute15
2502 ,l_mmli_rec.c_attribute16
2503 ,l_mmli_rec.c_attribute17
2504 ,l_mmli_rec.c_attribute18
2505 ,l_mmli_rec.c_attribute19
2506 ,l_mmli_rec.c_attribute20
2507 ,l_mmli_rec.d_attribute1
2508 ,l_mmli_rec.d_attribute2
2509 ,l_mmli_rec.d_attribute3
2510 ,l_mmli_rec.d_attribute4
2511 ,l_mmli_rec.d_attribute5
2512 ,l_mmli_rec.d_attribute6
2513 ,l_mmli_rec.d_attribute7
2514 ,l_mmli_rec.d_attribute8
2515 ,l_mmli_rec.d_attribute9
2516 ,l_mmli_rec.d_attribute10
2517 ,l_mmli_rec.n_attribute1
2518 ,l_mmli_rec.n_attribute2
2519 ,l_mmli_rec.n_attribute3
2520 ,l_mmli_rec.n_attribute4
2521 ,l_mmli_rec.n_attribute5
2522 ,l_mmli_rec.n_attribute6
2523 ,l_mmli_rec.n_attribute7
2524 ,l_mmli_rec.n_attribute8
2525 ,l_mmli_rec.n_attribute9
2526 ,l_mmli_rec.n_attribute10);
2527
2528 x_mmli_rec := l_mmli_rec;
2529
2530 IF (g_debug <= gme_debug.g_log_statement) THEN
2531 gme_debug.put_line ( g_pkg_name
2532 || '.'
2533 || l_api_name
2534 || ':'
2535 || 'Exiting with '
2536 || x_return_status);
2537 END IF;
2538 EXCEPTION
2539 WHEN material_status_err THEN
2540 SELECT substr(concatenated_segments,1,100)
2541 INTO l_item
2542 FROM mtl_system_items_kfv
2543 WHERE organization_id = gme_common_pvt.g_organization_id
2544 AND inventory_item_id = l_inventory_item_id;
2545 SELECT transaction_type_name
2546 INTO l_type
2547 FROM mtl_transaction_types
2548 WHERE transaction_type_id = p_transaction_type_id;
2549 gme_common_pvt.log_message ('GME_MATERIAL_STS_INV_LOT'
2550 ,'TRANSTYPE',l_type,'ITEM',l_item
2551 ,'LOT',l_mmli_rec.lot_number);
2552 gme_debug.put_line ( g_pkg_name
2553 || '.'
2554 || l_api_name
2555 || ':'
2556 || 'material status invalid for item, subinventory, locator etc');
2557 x_return_status := 'T';
2558 WHEN fnd_api.g_exc_error THEN
2559 gme_debug.put_line ( g_pkg_name
2560 || '.'
2561 || l_api_name
2562 || ':'
2563 || 'WHEN exe'
2564 || SQLERRM);
2565 x_return_status := fnd_api.g_ret_sts_error;
2566 WHEN fnd_api.g_exc_unexpected_error THEN
2567 gme_debug.put_line ( g_pkg_name
2568 || '.'
2569 || l_api_name
2570 || ':'
2571 || 'unexp'
2572 || SQLERRM);
2573 x_return_status := fnd_api.g_ret_sts_unexp_error;
2574 WHEN OTHERS THEN
2575 x_return_status := fnd_api.g_ret_sts_unexp_error;
2576 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2577
2578 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
2579 gme_debug.put_line ( g_pkg_name
2580 || '.'
2581 || l_api_name
2582 || ':'
2583 || 'WHEN OTHERS:'
2584 || SQLERRM);
2585 END IF;
2586 END BUILD_TXN_INTER_LOT;
2587
2588 /* +==========================================================================+
2589 | PROCEDURE NAME
2590 | get_transactions
2591 |
2592 | USAGE
2593 | Gets all transactions from mmt based on transaction_id passed.
2594 |
2595 | ARGUMENTS
2596 | p_transaction_id -- transaction_id from mmt for fetch
2597 |
2598 | RETURNS
2599 |
2600 | returns via x_status OUT parameters
2601 | x_mmt_rec -- mtl_transaction_interface rowtype
2602 | x_mmln_tbl -- table of mtl_trans_lots_number_tbl
2603 | HISTORY
2604 | Created 02-Feb-05 Pawan Kumar
2605 | 09-Feb-06 Namit S. Bug4917213 Changed query for perf reasons.
2606 |
2607 +==========================================================================+ */
2608 PROCEDURE get_transactions (
2609 p_transaction_id IN NUMBER
2610 ,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
2611 ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
2612 ,x_return_status OUT NOCOPY VARCHAR2)
2613 IS
2614
2615 -- Namit S. Bug4917213. Changed query to add hint to improve sharable memory.
2616 -- Hint was provided by the apps perf team.
2617 -- Pawan Kumar bug 5483071 added order by clause
2618 -- donot change the order by clause it is done so that we reverse the outbound transaction first
2619
2620 CURSOR cur_get_transaction (v_transaction_id NUMBER, v_reversal_type NUMBER)
2621 IS
2622 SELECT *
2623 FROM mtl_material_transactions mmt
2624 WHERE transaction_id = v_transaction_id
2625 AND NOT EXISTS ( SELECT /*+ no_unnest */
2626 transaction_id1
2627 FROM gme_transaction_pairs
2628 WHERE transaction_id1 = mmt.transaction_id
2629 AND pair_type = v_reversal_type)
2630 ORDER BY mmt.transaction_quantity;
2631
2632 CURSOR cur_get_lot_transaction (v_transaction_id NUMBER)
2633 IS
2634 SELECT *
2635 FROM mtl_transaction_lot_numbers
2636 WHERE transaction_id = v_transaction_id;
2637
2638 l_api_name CONSTANT VARCHAR2 (30) := 'GET_TRANSACTIONS';
2639 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
2640 l_transaction_id NUMBER;
2641 no_transaction_found EXCEPTION;
2642
2643 BEGIN
2644 x_return_status := fnd_api.g_ret_sts_success;
2645
2646 IF (g_debug <= gme_debug.g_log_statement) THEN
2647 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2648 || 'Entering with transaction '||p_transaction_id);
2649 END IF;
2650
2651 IF p_transaction_id IS NULL THEN
2652 gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
2653
2654 IF (g_debug <= gme_debug.g_log_statement) THEN
2655 gme_debug.put_line ( g_pkg_name
2656 || '.'
2657 || l_api_name
2658 || ':'
2659 || 'TRANSACTION ID NEEDED FOR RETRIEVAL');
2660 END IF;
2661 END IF;
2662
2663 l_transaction_id := p_transaction_id;
2664 -- Namit S. Bug4917213.
2665 OPEN cur_get_transaction (l_transaction_id, gme_common_pvt.g_pairs_reversal_type);
2666 FETCH cur_get_transaction
2667 INTO x_mmt_rec;
2668 IF cur_get_transaction%FOUND THEN
2669 IF (g_debug <= gme_debug.g_log_statement) THEN
2670 gme_debug.put_line ( g_pkg_name
2671 || '.'
2672 || l_api_name
2673 || ':'
2674 || 'TRANSACTIONS found for '
2675 || l_transaction_id);
2676 END IF;
2677 get_lot_trans (p_transaction_id => l_transaction_id
2678 ,x_mmln_tbl => x_mmln_tbl
2679 ,x_return_status => l_return_status);
2680
2681 IF l_return_status <> fnd_api.g_ret_sts_success THEN
2682 IF (g_debug <= gme_debug.g_log_statement) THEN
2683 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2684 || 'error from get lot trans');
2685 END IF;
2686 RAISE fnd_api.g_exc_error;
2687 END IF;
2688 ELSE /* IF cur_get_transaction%FOUND THEN */
2689 CLOSE cur_get_transaction;
2690 gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
2691 RAISE fnd_api.g_exc_error;
2692 END IF;
2693 CLOSE cur_get_transaction;
2694
2695 IF (g_debug <= gme_debug.g_log_statement) THEN
2696 gme_debug.put_line ( g_pkg_name
2697 || '.'
2698 || l_api_name
2699 || ':'
2700 || 'TRANSACTION '
2701 || x_mmt_rec.transaction_id);
2702 END IF;
2703
2704 IF (g_debug <= gme_debug.g_log_statement) THEN
2705 gme_debug.put_line ( g_pkg_name
2706 || '.'
2707 || l_api_name
2708 || ':'
2709 || 'Exiting with '
2710 || x_return_status);
2711 END IF;
2712 EXCEPTION
2713 WHEN fnd_api.g_exc_error THEN
2714 x_return_status := fnd_api.g_ret_sts_error;
2715 WHEN fnd_api.g_exc_unexpected_error THEN
2716 x_return_status := fnd_api.g_ret_sts_unexp_error;
2717 WHEN OTHERS THEN
2718 x_return_status := fnd_api.g_ret_sts_unexp_error;
2719 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2720
2721 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2722 gme_debug.put_line ( g_pkg_name
2723 || '.'
2724 || l_api_name
2725 || ':'
2726 || 'WHEN OTHERS:'
2727 || SQLERRM);
2728 END IF;
2729 END get_transactions;
2730
2731 /* +==========================================================================+
2732 | PROCEDURE NAME
2733 | construct_mmti
2734 |
2735 | USAGE
2736 | Construct interface table record based on mmt passed to it.
2737 |
2738 | ARGUMENTS
2739 | p_mmt_rec -- mtl_material_transaction rowtype
2740 | p_mmln_tbl -- table of mtl_trans_lots_num_tbl as input
2741 |
2742 | RETURNS
2743 | returns via x_status OUT parameters
2744 | x_mmti_rec mtl_transactions_interface rowtype
2745 | x_mmli_tbl table of mtl_trans_lots_inter_tbl
2746 | HISTORY
2747 | Created 02-Feb-05 Pawan Kumar
2748 |
2749 +==========================================================================+ */
2750 PROCEDURE construct_mmti (
2751 p_mmt_rec IN mtl_material_transactions%ROWTYPE
2752 ,p_mmln_tbl IN gme_common_pvt.mtl_trans_lots_num_tbl
2753 ,x_mmti_rec OUT NOCOPY mtl_transactions_interface%ROWTYPE
2754 ,x_mmli_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_inter_tbl
2755 ,x_return_status OUT NOCOPY VARCHAR2)
2756 IS
2757 l_mmt_rec mtl_material_transactions%ROWTYPE;
2758 l_mmln_tbl gme_common_pvt.mtl_trans_lots_num_tbl;
2759 l_mmti_rec mtl_transactions_interface%ROWTYPE;
2760 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
2761 l_api_name CONSTANT VARCHAR2 (30) := 'CONSTRUCT_MMTI';
2762 BEGIN
2763 -- Initially let us assign the return status to success
2764 x_return_status := fnd_api.g_ret_sts_success;
2765
2766 IF (g_debug <= gme_debug.g_log_statement) THEN
2767 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2768 || 'Entering');
2769 END IF;
2770
2771 l_mmt_rec := p_mmt_rec;
2772 l_mmln_tbl := p_mmln_tbl;
2773 -- x_mmti_rec.transaction_mode := l_mmt_rec.transaction_mode;
2774 x_mmti_rec.source_code := l_mmt_rec.source_code;
2775 -- x_mmti_rec.source_header_id := l_mmt_rec.source_header_id ;
2776 x_mmti_rec.source_line_id := NVL (l_mmt_rec.source_line_id, -99);
2777 x_mmti_rec.transaction_source_id := l_mmt_rec.transaction_source_id;
2778 x_mmti_rec.trx_source_line_id := l_mmt_rec.trx_source_line_id;
2779 x_mmti_rec.last_updated_by := l_mmt_rec.last_updated_by;
2780 x_mmti_rec.last_update_login := l_mmt_rec.last_update_login;
2781 x_mmti_rec.last_update_date := l_mmt_rec.last_update_date;
2782 x_mmti_rec.creation_date := l_mmt_rec.creation_date;
2783 x_mmti_rec.created_by := l_mmt_rec.created_by;
2784 x_mmti_rec.inventory_item_id := l_mmt_rec.inventory_item_id;
2785 x_mmti_rec.revision := l_mmt_rec.revision;
2786 x_mmti_rec.organization_id := l_mmt_rec.organization_id;
2787 x_mmti_rec.acct_period_id := l_mmt_rec.acct_period_id;
2788 x_mmti_rec.transaction_date := l_mmt_rec.transaction_date;
2789 x_mmti_rec.transaction_type_id := l_mmt_rec.transaction_type_id;
2790 x_mmti_rec.transaction_action_id := l_mmt_rec.transaction_action_id;
2791 x_mmti_rec.transaction_quantity := l_mmt_rec.transaction_quantity;
2792 x_mmti_rec.primary_quantity := l_mmt_rec.primary_quantity;
2793 x_mmti_rec.secondary_transaction_quantity := l_mmt_rec.secondary_transaction_quantity;
2794 x_mmti_rec.secondary_uom_code := l_mmt_rec.secondary_uom_code ;
2795 x_mmti_rec.distribution_account_id := l_mmt_rec.distribution_account_id;
2796 x_mmti_rec.transaction_uom := l_mmt_rec.transaction_uom;
2797 x_mmti_rec.subinventory_code := l_mmt_rec.subinventory_code;
2798 x_mmti_rec.locator_id := l_mmt_rec.locator_id;
2799
2800 x_mmti_rec.transaction_source_type_id := l_mmt_rec.transaction_source_type_id;
2801 x_mmti_rec.transaction_source_name := l_mmt_rec.transaction_source_name;
2802 x_mmti_rec.transaction_reference := l_mmt_rec.transaction_reference;
2803 x_mmti_rec.reason_id := l_mmt_rec.reason_id;
2804 --x_mmti_rec.reservation_quantity := l_mmt_rec.reservation_quantity;
2805 -- x_mmti_rec.transaction_sequence_id := l_mmt_rec.transaction_sequence_id;
2806 --x_mmti_rec.transaction_reference := l_mmt_rec.transaction_reference;
2807
2808 -- construct mtl_transaction_lots_interface
2809 IF (l_mmln_tbl.COUNT > 0) THEN
2810 FOR i IN 1 .. l_mmln_tbl.COUNT LOOP
2811 x_mmli_tbl (i).last_update_date :=
2812 l_mmln_tbl (i).last_update_date;
2813 x_mmli_tbl (i).last_updated_by := l_mmln_tbl (i).last_updated_by;
2814 x_mmli_tbl (i).creation_date := l_mmln_tbl (i).creation_date;
2815 x_mmli_tbl (i).created_by := l_mmln_tbl (i).created_by;
2816 x_mmli_tbl (i).lot_number := l_mmln_tbl (i).lot_number;
2817 x_mmli_tbl (i).transaction_quantity :=
2818 l_mmln_tbl (i).transaction_quantity;
2819 x_mmli_tbl (i).primary_quantity :=
2820 l_mmln_tbl (i).primary_quantity;
2821 x_mmli_tbl (i).secondary_transaction_quantity :=
2822 l_mmln_tbl (i).secondary_transaction_quantity;
2823 END LOOP;
2824 END IF; --l_mmln_tbl.count
2825
2826 IF (g_debug <= gme_debug.g_log_statement) THEN
2827 gme_debug.put_line ( g_pkg_name
2828 || '.'
2829 || l_api_name
2830 || ':'
2831 || 'Exiting with '
2832 || x_return_status);
2833 END IF;
2834 EXCEPTION
2835 WHEN fnd_api.g_exc_error THEN
2836 x_return_status := fnd_api.g_ret_sts_error;
2837 WHEN fnd_api.g_exc_unexpected_error THEN
2838 x_return_status := fnd_api.g_ret_sts_unexp_error;
2839 WHEN OTHERS THEN
2840 x_return_status := fnd_api.g_ret_sts_unexp_error;
2841 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2842
2843 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2844 gme_debug.put_line ( g_pkg_name
2845 || '.'
2846 || l_api_name
2847 || ':'
2848 || 'WHEN OTHERS:'
2849 || SQLERRM);
2850 END IF;
2851 END construct_mmti;
2852
2853 /* +==========================================================================+
2854 | PROCEDURE NAME
2855 | get_mat_trans
2856 |
2857 | USAGE
2858 | Gets all transactions from mmt based on material_detail_id and batch_id passed.
2859 |
2860 | ARGUMENTS
2861 | p_mat_det_id -- material_detail_id passed of material
2862 | p_batch_id -- batch_id to which the material belongs.
2863 |
2864 | RETURNS
2865 | returns via x_status OUT parameters
2866 | x_mmt_tbl- gives back all transactions of the material
2867 | HISTORY
2868 | Created 02-Feb-05 Pawan Kumar
2869 | 09-Feb-06 Namit S. Bug4917213 Changed query for perf reasons.
2870 | Swapna K Bug#8300015 Added p_phantom_line_id parameter and loaded the the
2871 | phantom transactions also if the parameter is not null.
2872 |
2873 | G. Muratore 19-MAR-2010 Bug 8751983
2874 | Added p_order_by parameter to allow fetching of transactions in reverse trans order.
2875 +==========================================================================+ */
2876 PROCEDURE get_mat_trans (
2877 p_mat_det_id IN NUMBER
2878 ,p_batch_id IN NUMBER
2879 ,p_phantom_line_id IN NUMBER DEFAULT NULL
2880 ,p_order_by IN NUMBER DEFAULT 1
2881 ,x_mmt_tbl OUT NOCOPY gme_common_pvt.mtl_mat_tran_tbl
2882 ,x_return_status OUT NOCOPY VARCHAR2)
2883 IS
2884
2885 -- Namit S. Bug4917213. Changed query to add hint to improve sharable memory.
2886 -- Hint was provided by the apps perf team.
2887 -- Pawan Kumar bug 5483071 added order by clause
2888 -- do not change the order by clause it is done so that we reverse the outbound transaction first
2889
2890 -- Bug 8751983 - Let's fetch the transactions in reverse order based on parameter value.
2891 CURSOR cur_get_trans (v_mat_det_id NUMBER, v_batch_id NUMBER,
2892 v_txn_source_type NUMBER, v_pairs_reversal_type NUMBER)
2893 IS
2894 SELECT *
2895 FROM mtl_material_transactions mmt
2896 WHERE trx_source_line_id = v_mat_det_id
2897 AND transaction_source_id = v_batch_id
2898 AND transaction_source_type_id = v_txn_source_type
2899 AND NOT EXISTS ( SELECT /*+ no_unnest */
2900 transaction_id1
2901 FROM gme_transaction_pairs
2902 WHERE transaction_id1 = mmt.transaction_id
2903 AND pair_type = v_pairs_reversal_type)
2904 ORDER BY CASE p_order_by
2905 when 1 then Row_Number() over(order by transaction_quantity)
2906 when 2 then Row_Number() over(order by transaction_id DESC)
2907 END;
2908
2909
2910 -- Bug 8751983 - Let's fetch the resource transaction in reverse order based on parameter value.
2911 CURSOR cur_get_all_trans (v_mat_det_id NUMBER, v_batch_id NUMBER, v_phantom_line_id NUMBER,v_phantom_batch_id NUMBER,
2912 v_txn_source_type NUMBER, v_pairs_reversal_type NUMBER)
2913 IS
2914
2915 SELECT * FROM
2916 ( SELECT *
2917 FROM mtl_material_transactions mmt
2918 WHERE trx_source_line_id = v_mat_det_id
2919 AND transaction_source_id = v_batch_id
2920 AND transaction_source_type_id = v_txn_source_type
2921 AND NOT EXISTS ( SELECT /*+ no_unnest */
2922 transaction_id1
2923 FROM gme_transaction_pairs
2924 WHERE transaction_id1 = mmt.transaction_id
2925 AND pair_type = v_pairs_reversal_type)
2926
2927 UNION ALL
2928
2929 SELECT *
2930 FROM mtl_material_transactions mmt
2931 WHERE trx_source_line_id = v_phantom_line_id
2932 AND transaction_source_id = v_phantom_batch_id
2933 AND transaction_source_type_id = v_txn_source_type
2934 AND NOT EXISTS ( SELECT /*+ no_unnest */
2935 transaction_id1
2936 FROM gme_transaction_pairs
2937 WHERE transaction_id1 = mmt.transaction_id
2938 AND pair_type = v_pairs_reversal_type))
2939 ORDER BY CASE p_order_by
2940 when 1 then Row_Number() over(order by transaction_quantity)
2941 when 2 then Row_Number() over(order by transaction_id DESC)
2942 END;
2943
2944 l_api_name CONSTANT VARCHAR2 (30) := 'GET_MAT_TRANS';
2945 p_phantom_batch_id NUMBER;
2946 BEGIN
2947 -- Initially let us assign the return status to success
2948 x_return_status := fnd_api.g_ret_sts_success;
2949
2950 IF (g_debug <= gme_debug.g_log_statement) THEN
2951 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
2952 || 'Entering with mat/batch '||p_mat_det_id||'/'||p_batch_id);
2953 END IF;
2954
2955 IF p_mat_det_id IS NOT NULL AND p_batch_id IS NOT NULL THEN
2956 /*Bug#8300015 Fetching the transactions along with the associated phantom material */
2957 IF p_phantom_line_id IS NOT NULL THEN
2958 SELECT batch_id INTO p_phantom_batch_id
2959 FROM gme_material_details
2960 WHERE material_detail_id = p_phantom_line_id;
2961 OPEN cur_get_all_trans (p_mat_det_id, p_batch_id,p_phantom_line_id,p_phantom_batch_id,
2962 gme_common_pvt.g_txn_source_type, gme_common_pvt.g_pairs_reversal_type);
2963 FETCH cur_get_all_trans
2964 BULK COLLECT INTO x_mmt_tbl;
2965 CLOSE cur_get_all_trans;
2966 ELSE
2967 -- Namit S. Bug4917213.
2968 OPEN cur_get_trans (p_mat_det_id, p_batch_id,
2969 gme_common_pvt.g_txn_source_type, gme_common_pvt.g_pairs_reversal_type);
2970 FETCH cur_get_trans
2971 BULK COLLECT INTO x_mmt_tbl;
2972 CLOSE cur_get_trans;
2973 END IF;
2974 END IF;
2975 IF (g_debug <= gme_debug.g_log_statement) THEN
2976 gme_debug.put_line ( g_pkg_name
2977 || '.'
2978 || l_api_name
2979 || ':'
2980 || 'Exiting with '
2981 || x_return_status);
2982 END IF;
2983 EXCEPTION
2984 WHEN fnd_api.g_exc_error THEN
2985 x_return_status := fnd_api.g_ret_sts_error;
2986 WHEN fnd_api.g_exc_unexpected_error THEN
2987 x_return_status := fnd_api.g_ret_sts_unexp_error;
2988 WHEN OTHERS THEN
2989 x_return_status := fnd_api.g_ret_sts_unexp_error;
2990 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
2991
2992 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
2993 gme_debug.put_line ( g_pkg_name
2994 || '.'
2995 || l_api_name
2996 || ':'
2997 || 'WHEN OTHERS:'
2998 || SQLERRM);
2999 END IF;
3000 END get_mat_trans;
3001
3002
3003 /* +==========================================================================+
3004 | PROCEDURE NAME
3005 | get_lot_trans
3006 |
3007 | USAGE
3008 | Gets all lot transactions from mmln for a given transaction_id.
3009 |
3010 | ARGUMENTS
3011 | p_transaction_id -- transaction_id for which all lot info is required.
3012 |
3013 | RETURNS
3014 | returns via x_status OUT parameters
3015 | x_mmln_tbl- all lot info for a given transaction_id.
3016 | HISTORY
3017 | Created 02-Feb-05 Pawan Kumar
3018 |
3019 +==========================================================================+ */
3020 PROCEDURE get_lot_trans (
3021 p_transaction_id IN NUMBER
3022 ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
3023 ,x_return_status OUT NOCOPY VARCHAR2)
3024 IS
3025 CURSOR cur_get_lot_trans (v_transaction_id NUMBER)
3026 IS
3027 SELECT *
3028 FROM mtl_transaction_lot_numbers
3029 WHERE transaction_id = v_transaction_id;
3030
3031 l_api_name CONSTANT VARCHAR2 (30) := 'GET_LOT_TRANS';
3032 BEGIN
3033 -- Initially let us assign the return status to success
3034 x_return_status := fnd_api.g_ret_sts_success;
3035
3036 IF (g_debug <= gme_debug.g_log_statement) THEN
3037 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3038 || 'Entering with transaction id '||p_transaction_id);
3039
3040 END IF;
3041
3042 IF p_transaction_id IS NOT NULL THEN
3043 OPEN cur_get_lot_trans (p_transaction_id);
3044 FETCH cur_get_lot_trans
3045 BULK COLLECT INTO x_mmln_tbl;
3046 CLOSE cur_get_lot_trans;
3047 END IF;
3048
3049 IF (g_debug <= gme_debug.g_log_statement) THEN
3050 gme_debug.put_line ( g_pkg_name
3051 || '.'
3052 || l_api_name
3053 || ':'
3054 || 'lot count '
3055 || x_mmln_tbl.COUNT);
3056 gme_debug.put_line ( g_pkg_name
3057 || '.'
3058 || l_api_name
3059 || ':'
3060 || 'Exiting with '
3061 || x_return_status);
3062 END IF;
3063 EXCEPTION
3064 WHEN fnd_api.g_exc_error THEN
3065 x_return_status := fnd_api.g_ret_sts_error;
3066 WHEN fnd_api.g_exc_unexpected_error THEN
3067 x_return_status := fnd_api.g_ret_sts_unexp_error;
3068 WHEN OTHERS THEN
3069 x_return_status := fnd_api.g_ret_sts_unexp_error;
3070
3071 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3072 gme_debug.put_line ( g_pkg_name
3073 || '.'
3074 || l_api_name
3075 || ':'
3076 || 'WHEN OTHERS:'
3077 || SQLERRM);
3078 END IF;
3079
3080 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3081 END get_lot_trans;
3082
3083 /* +==========================================================================+
3084 | PROCEDURE NAME
3085 | GME_PRE_PROCESS
3086 |
3087 | USAGE
3088 | Gets all pre-process validations based on header_id
3089 |
3090 | ARGUMENTS
3091 | p_transaction_hdr_id
3092 |
3093 |
3094 | RETURNS
3095 | returns via x_status OUT parameters
3096 |
3097 | HISTORY
3098 | Created 02-Feb-05 Pawan Kumar
3099 |
3100 +==========================================================================+ */
3101 PROCEDURE gme_pre_process (
3102 p_transaction_hdr_id IN NUMBER
3103 ,x_return_status OUT NOCOPY VARCHAR2)
3104 IS
3105 CURSOR cur_get_trans (v_hdr_id NUMBER)
3106 IS
3107 SELECT transaction_interface_id
3108 FROM mtl_transactions_interface
3109 WHERE transaction_header_id = v_hdr_id
3110 AND transaction_source_type_id = gme_common_pvt.g_txn_source_type
3111 AND wip_entity_type = gme_common_pvt.g_wip_entity_type_batch;
3112
3113 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
3114 l_number_tab gme_common_pvt.number_tab;
3115 no_transaction_found EXCEPTION;
3116 l_api_name CONSTANT VARCHAR2 (30) := 'GME_PRE_PROCESS';
3117 BEGIN
3118 -- Initially let us assign the return status to success
3119 x_return_status := fnd_api.g_ret_sts_success;
3120
3121 IF (g_debug <= gme_debug.g_log_statement) THEN
3122 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3123 || 'Entering with p_transaction_hdr_id '||p_transaction_hdr_id);
3124 END IF;
3125
3126 IF p_transaction_hdr_id IS NOT NULL THEN
3127 OPEN cur_get_trans (p_transaction_hdr_id);
3128 IF cur_get_trans%NOTFOUND THEN
3129 CLOSE cur_get_trans;
3130 gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
3131 RAISE fnd_api.g_exc_error;
3132 END IF;
3133 FETCH cur_get_trans
3134 BULK COLLECT INTO l_number_tab;
3135 CLOSE cur_get_trans;
3136 END IF;
3137
3138 FOR i IN 1 .. l_number_tab.COUNT LOOP
3139 IF (g_debug <= gme_debug.g_log_statement) THEN
3140 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3141 || 'calling pre_process_val with transaction_id '||l_number_tab (i));
3142 END IF;
3143 pre_process_val (p_transaction_interface_id => l_number_tab (i)
3144 ,x_return_status => l_return_status);
3145
3146 IF l_return_status <> fnd_api.g_ret_sts_success THEN
3147 RAISE fnd_api.g_exc_error;
3148 END IF;
3149 END LOOP;
3150
3151 IF (g_debug <= gme_debug.g_log_statement) THEN
3152 gme_debug.put_line ( g_pkg_name
3153 || '.'
3154 || l_api_name
3155 || ':'
3156 || 'Exiting with '
3157 || x_return_status);
3158 END IF;
3159 EXCEPTION
3160 /* update mtl_transactions_interface
3161 set error_code = 'wip_mtlInterfaceProc_pub.processInterface()',
3162 error_explanation = l_errMessage,
3163 process_flag = wip_constants.mti_error
3164 where transaction_header_id = p_txnHdrID; */
3165 WHEN fnd_api.g_exc_error THEN
3166 x_return_status := fnd_api.g_ret_sts_error;
3167 WHEN fnd_api.g_exc_unexpected_error THEN
3168 x_return_status := fnd_api.g_ret_sts_unexp_error;
3169 WHEN OTHERS THEN
3170 x_return_status := fnd_api.g_ret_sts_unexp_error;
3171
3172 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3173 gme_debug.put_line ( g_pkg_name
3174 || '.'
3175 || l_api_name
3176 || ':'
3177 || 'WHEN OTHERS:'
3178 || SQLERRM);
3179 END IF;
3180
3181 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3182 END gme_pre_process;
3183
3184 /* +==========================================================================+
3185 | PROCEDURE NAME
3186 | get_returnable_qty
3187 |
3188 | USAGE
3189 | Gets net quantity that can be returned from mmt based on the details passed
3190 |
3191 | ARGUMENTS
3192 | p_mmti_rec -- mtl_transaction_interface rowtype
3193 | p_lot_number - Lot number
3194 | p_lot_control - 1 for plain 2 for lot control
3195 | RETURNS
3196 | x_return_status S for success, U for unexpected
3197 | x_available_qty Quantity that can be returned.
3198 | HISTORY
3199 | Created 20-Sep-05 Shrikant Nene
3200 | Bug 13853087 - Modified cur_lot_qty query to pass lot_number and organization_id.
3201 +==========================================================================+ */
3202
3203 PROCEDURE get_returnable_qty (
3204 p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
3205 p_lot_number IN VARCHAR2,
3206 p_lot_control IN NUMBER,
3207 x_available_qty OUT NOCOPY NUMBER,
3208 x_return_status OUT NOCOPY VARCHAR2
3209 ) IS
3210
3211 CURSOR cur_plain_item (
3212 v_item_id IN NUMBER,
3213 v_organization_id IN NUMBER,
3214 v_revision IN VARCHAR2,
3215 v_batch_id IN NUMBER,
3216 v_mat_det_id IN NUMBER,
3217 v_trans_uom IN VARCHAR2
3218 ) IS
3219 SELECT SUM (DECODE (v_trans_uom,
3220 t.transaction_uom, transaction_quantity,
3221 inv_convert.inv_um_convert (d.inventory_item_id,
3222 gme_common_pvt.g_precision,
3223 t.transaction_quantity,
3224 t.transaction_uom,
3225 v_trans_uom,
3226 NULL,
3227 NULL
3228 )
3229 )
3230 )
3231 FROM mtl_material_transactions t, gme_material_details d
3232 WHERE t.organization_id = v_organization_id
3233 AND t.inventory_item_id = v_item_id
3234 AND t.transaction_source_id = v_batch_id
3235 AND t.trx_source_line_id = v_mat_det_id
3236 AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3237 AND t.trx_source_line_id = d.material_detail_id
3238 AND (t.revision IS NULL OR t.revision = v_revision)
3239 GROUP BY t.revision, t.inventory_item_id;
3240
3241 CURSOR cur_lot_qty (
3242 v_lot_number IN VARCHAR2,
3243 v_item_id IN NUMBER,
3244 v_organization_id IN NUMBER,
3245 v_revision IN VARCHAR2,
3246 v_batch_id IN NUMBER,
3247 v_mat_det_id IN NUMBER,
3248 v_trans_uom IN VARCHAR2
3249 ) IS
3250 SELECT lot_number,
3251 SUM (DECODE (v_trans_uom,
3252 m.transaction_uom, m.transaction_quantity,
3253 inv_convert.inv_um_convert (d.inventory_item_id,
3254 v_lot_number,
3255 v_organization_id,
3256 gme_common_pvt.g_precision,
3257 m.transaction_quantity,
3258 m.transaction_uom,
3259 v_trans_uom,
3260 NULL,
3261 NULL
3262 )
3263 )
3264 )
3265 FROM mtl_material_transactions m, mtl_transaction_lot_numbers l, gme_material_details d
3266 WHERE l.transaction_id = m.transaction_id
3267 AND m.trx_source_line_id = d.material_detail_id
3268 AND l.lot_number = v_lot_number
3269 AND l.inventory_item_id = v_item_id
3270 AND l.organization_id = v_organization_id
3271 AND l.transaction_source_id = v_batch_id
3272 AND m.trx_source_line_id = v_mat_det_id
3273 AND m.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3274 -- Pawan Kumar added for checking of revision bug 5451006- 5493370
3275 AND (m.revision IS NULL OR m.revision = v_revision)
3276 GROUP BY l.lot_number, l.inventory_item_id;
3277
3278 l_lot_no VARCHAR2 (80);
3279 l_api_name CONSTANT VARCHAR2 (30) := 'GET_RETURNABLE_QTY';
3280
3281 BEGIN
3282 -- Initially let us assign the return status to success
3283 x_return_status := fnd_api.g_ret_sts_success;
3284
3285 IF (g_debug <= gme_debug.g_log_statement) THEN
3286 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
3287 END IF;
3288 IF (g_debug <= gme_debug.g_log_statement) THEN
3289 gme_debug.put_line ( g_pkg_name
3290 || '.'
3291 || l_api_name
3292 || ':'
3293 || 'p_lot_number: '
3294 || p_lot_number
3295 || ' p_lot_control code: '
3296 || p_lot_control);
3297 END IF;
3298 IF p_lot_control = 1 THEN
3299 /* Plain Item */
3300 OPEN cur_plain_item (p_mmti_rec.inventory_item_id
3301 ,p_mmti_rec.organization_id
3302 ,p_mmti_rec.revision
3303 ,p_mmti_rec.transaction_source_id
3304 ,p_mmti_rec.trx_source_line_id
3305 ,p_mmti_rec.transaction_uom);
3306
3307 FETCH cur_plain_item
3308 INTO x_available_qty;
3309
3310 IF cur_plain_item%NOTFOUND THEN
3311 x_available_qty := 0;
3312 END IF;
3313
3314 CLOSE cur_plain_item;
3315 ELSE /* Lot control Item */
3316 OPEN cur_lot_qty (p_lot_number
3317 ,p_mmti_rec.inventory_item_id
3318 ,p_mmti_rec.organization_id
3319 ,p_mmti_rec.revision
3320 ,p_mmti_rec.transaction_source_id
3321 ,p_mmti_rec.trx_source_line_id
3322 ,p_mmti_rec.transaction_uom);
3323
3324 FETCH cur_lot_qty
3325 INTO l_lot_no, x_available_qty;
3326
3327 IF cur_lot_qty%NOTFOUND THEN
3328 x_available_qty := 0;
3329 END IF;
3330
3331 CLOSE cur_lot_qty;
3332 END IF;
3333 IF (g_debug <= gme_debug.g_log_statement) THEN
3334 gme_debug.put_line ( g_pkg_name
3335 || '.'
3336 || l_api_name
3337 || ':'
3338 || 'Exiting with return status '
3339 || x_return_status
3340 || ' Available Qty '
3341 || x_available_qty);
3342 END IF;
3343 EXCEPTION
3344 WHEN OTHERS THEN
3345 x_return_status := fnd_api.g_ret_sts_unexp_error;
3346 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3347
3348 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3349 gme_debug.put_line ( g_pkg_name
3350 || '.'
3351 || l_api_name
3352 || ':'
3353 || 'WHEN OTHERS:'
3354 || SQLERRM);
3355 END IF;
3356 END get_returnable_qty;
3357
3358 /* +==========================================================================+
3359 | PROCEDURE NAME
3360 | PRE_PROCESS_VAL
3361 |
3362 | USAGE
3363 | Gets all transactions from mmt based on material_detail_id and batch_id passed.
3364 |
3365 | ARGUMENTS
3366 | p_mmti_rec -- mtl_transaction_interface rowtype
3367 |
3368 |
3369 | RETURNS
3370 | returns via x_status OUT parameters
3371 |
3372 | HISTORY
3373 | Created 02-Feb-05 Pawan Kumar
3374 |
3375 +==========================================================================+ */
3376 PROCEDURE pre_process_val (
3377 p_transaction_interface_id IN NUMBER
3378 ,x_return_status OUT NOCOPY VARCHAR2)
3379 IS
3380 CURSOR cur_trans_detail (v_trans_inter_id NUMBER)
3381 IS
3382 SELECT *
3383 FROM mtl_transactions_interface
3384 WHERE transaction_interface_id = v_trans_inter_id;
3385
3386
3387 CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER)
3388 IS
3389 SELECT *
3390 FROM mtl_system_items_b
3391 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
3392 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER)
3393 IS
3394 SELECT step_status
3395 FROM gme_batch_steps s, gme_batch_step_items i
3396 WHERE s.batchstep_id = i.batchstep_id
3397 AND i.material_detail_id = v_matl_dtl_id;
3398
3399 CURSOR cur_lot_input (v_trans_inter_id NUMBER)
3400 IS
3401 SELECT lot_number, SUM (transaction_quantity) l_mtli_lot_qty
3402 FROM mtl_transaction_lots_interface
3403 WHERE transaction_interface_id = v_trans_inter_id
3404 GROUP BY lot_number;
3405
3406 l_mmti_rec mtl_transactions_interface%ROWTYPE;
3407 l_mmli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
3408 l_mat_dtl_rec gme_material_details%ROWTYPE;
3409 l_batch_hdr_rec gme_batch_header%ROWTYPE;
3410 l_item_rec mtl_system_items_b%ROWTYPE;
3411 l_available_qty NUMBER;
3412 l_step_status NUMBER;
3413 l_rel_type NUMBER;
3414 l_return_status VARCHAR2(1);
3415 item_not_found EXCEPTION;
3416 not_valid_trans EXCEPTION;
3417 lot_val_err EXCEPTION;
3418 l_api_name CONSTANT VARCHAR2 (30) := 'PRE_PROCESS_VAL';
3419 BEGIN
3420 -- Initially let us assign the return status to success
3421 x_return_status := fnd_api.g_ret_sts_success;
3422
3423 IF (g_debug <= gme_debug.g_log_statement) THEN
3424 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3425 || 'Entering');
3426 END IF;
3427
3428 OPEN cur_trans_detail (p_transaction_interface_id);
3429
3430 FETCH cur_trans_detail
3431 INTO l_mmti_rec;
3432
3433 CLOSE cur_trans_detail;
3434
3435 IF (g_debug <= gme_debug.g_log_statement) THEN
3436 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_header_id: '||l_mmti_rec.transaction_header_id);
3437 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.subinventory_code: '||l_mmti_rec.subinventory_code);
3438 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_uom: '||l_mmti_rec.transaction_uom);
3439 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.inventory_item_id: '||l_mmti_rec.inventory_item_id);
3440 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.trx_source_line_id: '||l_mmti_rec.trx_source_line_id);
3441 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.revision: '||l_mmti_rec.revision);
3442 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_source_id: '||l_mmti_rec.transaction_source_id);
3443 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.locator_id: '||l_mmti_rec.locator_id);
3444 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'l_mmti_rec.transaction_type_id: '||l_mmti_rec.transaction_type_id);
3445 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_transaction_interface_id: '||p_transaction_interface_id);
3446 END IF;
3447
3448 IF l_mmti_rec.transaction_source_id IS NOT NULL THEN
3449 l_batch_hdr_rec.batch_id := l_mmti_rec.transaction_source_id;
3450
3451 IF NOT gme_batch_header_dbl.fetch_row
3452 (p_batch_header => l_batch_hdr_rec
3453 ,x_batch_header => l_batch_hdr_rec) THEN
3454 RAISE fnd_api.g_exc_error;
3455 END IF;-- batch fetch
3456 ELSE
3457 RAISE fnd_api.g_exc_error;
3458 END IF; -- transaction_source_id IS NOT NULL
3459
3460 IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
3461 IF l_mmti_rec.trx_source_line_id IS NOT NULL THEN
3462 l_mat_dtl_rec.material_detail_id := l_mmti_rec.trx_source_line_id;
3463
3464 IF NOT gme_material_details_dbl.fetch_row
3465 (p_material_detail => l_mat_dtl_rec
3466 ,x_material_detail => l_mat_dtl_rec) THEN
3467 RAISE fnd_api.g_exc_error;
3468 END IF; -- material fetch
3469
3470 ELSE
3471 RAISE fnd_api.g_exc_error;
3472 END IF; -- trx_source_line_id IS NOT NULL
3473 IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
3474 IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
3475 gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
3476 RAISE fnd_api.g_exc_error;
3477 END IF;
3478 -- Check for step status in case the item is associated to a step.
3479 l_rel_type :=
3480 gme_common_pvt.is_material_auto_release
3481 (l_mat_dtl_rec.material_detail_id);
3482 IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN -- /*3*/
3483 OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
3484 FETCH Cur_associated_step INTO l_step_status;
3485 CLOSE Cur_associated_step;
3486 IF l_step_status NOT IN (2,3) THEN
3487 gme_common_pvt.log_message ('GME_API_INVALID_STEP_STATUS');
3488 RAISE fnd_api.g_exc_error;
3489 END IF;
3490 END IF; -- IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN
3491
3492 -- check for item release type for products
3493 IF (l_rel_type = gme_common_pvt.g_mtl_auto_release )
3494 AND l_mat_dtl_rec.line_type IN (1,2)
3495 AND l_mat_dtl_rec.phantom_line_id IS NULL THEN
3496 -- Bug 9626176 -- Allow transactions in wip batch for auto release prods.
3497 IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
3498 -- IF l_batch_hdr_rec.batch_status <> 3 THEN
3499 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS');
3500 RAISE fnd_api.g_exc_error;
3501 END IF;
3502 END IF;
3503 END IF; -- gme_common_pvt.g_batch_status_check
3504 IF l_mmti_rec.transaction_type_id IN
3505 (gme_common_pvt.g_ing_issue, gme_common_pvt.g_ing_return) THEN
3506 IF l_mat_dtl_rec.line_type <> -1 THEN
3507 /* Bug 5141394 Changed message */
3508 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
3509 RAISE fnd_api.g_exc_error;
3510 END IF;
3511 ELSIF l_mmti_rec.transaction_type_id IN
3512 (gme_common_pvt.g_prod_completion
3513 ,gme_common_pvt.g_prod_return) THEN
3514 IF l_mat_dtl_rec.line_type <> 1 THEN
3515 /* Bug 5141394 Changed message */
3516 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
3517 RAISE fnd_api.g_exc_error;
3518 END IF;
3519 ELSIF l_mmti_rec.transaction_type_id IN
3520 (gme_common_pvt.g_byprod_completion
3521 ,gme_common_pvt.g_byprod_return) THEN
3522 IF l_mat_dtl_rec.line_type <> 2 THEN
3523 /* Bug 5141394 Changed message */
3524 --RLNAGARA Bug6873185 Moved below line which was setting message name inside the IF condition.
3525 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
3526 RAISE fnd_api.g_exc_error;
3527 END IF;
3528 END IF;
3529
3530 -- check for phantom
3531 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
3532 IF l_mmti_rec.transaction_header_id <>
3533 gme_common_pvt.g_transaction_header_id THEN
3534 gme_common_pvt.log_message ('GME_PHANTOM_NO_RETURN');
3535 RAISE fnd_api.g_exc_error;
3536 END IF;
3537 END IF;
3538
3539 -- get the item propertites
3540 OPEN cur_get_item_rec (l_mmti_rec.inventory_item_id, l_mmti_rec.organization_id);
3541 FETCH cur_get_item_rec INTO l_item_rec;
3542 IF cur_get_item_rec%NOTFOUND THEN
3543 CLOSE cur_get_item_rec;
3544 gme_common_pvt.log_message ('PM_INVALID_ITEM');
3545 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3546 gme_debug.put_line('Item cursor fetch no record in mtl_system_items_b: ');
3547 gme_debug.put_line('inventory_item_id = '|| TO_CHAR (l_mmti_rec.inventory_item_id));
3548 gme_debug.put_line('organization_id = '|| TO_CHAR (l_mmti_rec.organization_id));
3549 END IF;
3550 RAISE item_not_found;
3551 END IF;
3552 CLOSE cur_get_item_rec;
3553 IF (g_debug <= gme_debug.g_log_statement) THEN
3554 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item lot_control Code: '|| l_item_rec.lot_control_code);
3555 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item loct_control Code: '|| l_item_rec.location_control_code);
3556 END IF;
3557
3558 /* Bug 5358129 for ingredients lots should exist */
3559 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
3560 FOR get_lots IN (SELECT DISTINCT lot_number FROM mtl_transaction_lots_interface WHERE transaction_interface_id = p_transaction_interface_id) LOOP
3561 gme_transactions_pvt.validate_lot_for_ing(p_organization_id => l_mmti_rec.organization_id,
3562 p_inventory_item_id => l_mmti_rec.inventory_item_id,
3563 p_lot_number => get_lots.lot_number,
3564 x_return_status => l_return_status);
3565 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
3566 RAISE lot_val_err;
3567 END IF;
3568 END LOOP;
3569 END IF;
3570
3571 -- if return transaction then check qty was issued and return not more than issued qty
3572 IF l_mmti_rec.transaction_type_id IN
3573 (gme_common_pvt.g_byprod_return
3574 ,gme_common_pvt.g_prod_return
3575 ,gme_common_pvt.g_ing_return) THEN
3576 IF (g_debug <= gme_debug.g_log_statement) THEN
3577 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||
3578 'Return transaction for : '||l_mmti_rec.transaction_type_id);
3579
3580 END IF;
3581 IF l_item_rec.lot_control_code = 1 THEN
3582 IF (g_debug <= gme_debug.g_log_statement) THEN
3583 gme_debug.put_line ( g_pkg_name
3584 || '.'
3585 || l_api_name
3586 || ':'
3587 || 'Item is NOT lot_control: '
3588 || l_item_rec.lot_control_code);
3589 END IF;
3590 get_returnable_qty(
3591 p_mmti_rec => l_mmti_rec
3592 ,p_lot_number => NULL
3593 ,p_lot_control => l_item_rec.lot_control_code
3594 ,x_available_qty => l_available_qty
3595 ,x_return_status => x_return_status);
3596
3597 IF x_return_Status <> fnd_api.g_ret_sts_success THEN
3598 RAISE fnd_api.g_exc_unexpected_error;
3599 END IF;
3600 IF (g_debug <= gme_debug.g_log_statement) THEN
3601 gme_debug.put_line ( g_pkg_name
3602 || '.'
3603 || l_api_name
3604 || ':'
3605 || 'Returning Qty '
3606 || l_mmti_rec.transaction_quantity);
3607 gme_debug.put_line ( g_pkg_name
3608 || '.'
3609 || l_api_name
3610 || ':'
3611 || 'Available to Return '
3612 || l_available_qty);
3613 END IF;
3614
3615 IF ABS (l_available_qty) < ABS (l_mmti_rec.transaction_quantity) THEN
3616 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
3617 RAISE fnd_api.g_exc_error;
3618 END IF;
3619 ELSE /* Lot Control */
3620 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3621 gme_debug.put_line ( g_pkg_name
3622 || '.'
3623 || l_api_name
3624 || ':'
3625 || 'Item is lot control: '
3626 || l_item_rec.lot_control_code);
3627 END IF;
3628
3629 FOR get_rec IN cur_lot_input (p_transaction_interface_id) LOOP
3630 -- first get the qty from the mtln table
3631 get_returnable_qty(
3632 p_mmti_rec => l_mmti_rec
3633 ,p_lot_number => get_rec.lot_number
3634 ,p_lot_control => l_item_rec.lot_control_code
3635 ,x_available_qty => l_available_qty
3636 ,x_return_status => x_return_status);
3637
3638 IF x_return_Status <> fnd_api.g_ret_sts_success THEN
3639 RAISE fnd_api.g_exc_unexpected_error;
3640 END IF;
3641 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
3642 gme_debug.put_line ( g_pkg_name
3643 || '.'
3644 || l_api_name
3645 || ':'
3646 || 'Returning qty: '|| get_rec.l_mtli_lot_qty);
3647 gme_debug.put_line ( g_pkg_name
3648 || '.'
3649 || l_api_name
3650 || ':'
3651 || 'Available to Return '
3652 || l_available_qty);
3653 END IF;
3654
3655 IF ABS (l_available_qty) < ABS (get_rec.l_mtli_lot_qty) THEN
3656 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
3657 RAISE fnd_api.g_exc_error;
3658 END IF;
3659 END LOOP;
3660 END IF; /* IF l_item_rec.lot_control_code = 1 THEN */
3661 END IF; /* IF transaction_type_id in RETURNS */
3662 END IF; /* update_inventory_ind = 'Y' */
3663
3664 IF (g_debug <= gme_debug.g_log_statement) THEN
3665 gme_debug.put_line ( g_pkg_name
3666 || '.'
3667 || l_api_name
3668 || ':'
3669 || 'Exiting with '
3670 || x_return_status);
3671 END IF;
3672 EXCEPTION
3673 WHEN lot_val_err THEN
3674 x_return_status := l_return_status;
3675 WHEN fnd_api.g_exc_error THEN
3676 x_return_status := fnd_api.g_ret_sts_error;
3677 gme_transactions_pvt.gme_txn_message
3678 (p_api_name => l_api_name
3679 ,p_transaction_interface_id => p_transaction_interface_id
3680 );
3681 WHEN fnd_api.g_exc_unexpected_error THEN
3682 x_return_status := fnd_api.g_ret_sts_unexp_error;
3683 WHEN OTHERS THEN
3684 x_return_status := fnd_api.g_ret_sts_unexp_error;
3685 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3686
3687 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
3688 gme_debug.put_line ( g_pkg_name
3689 || '.'
3690 || l_api_name
3691 || ':'
3692 || 'WHEN OTHERS:'
3693 || SQLERRM);
3694 END IF;
3695
3696 gme_transactions_pvt.gme_txn_message
3697 (p_api_name => l_api_name
3698 ,p_transaction_interface_id => p_transaction_interface_id
3699 );
3700
3701 END pre_process_val;
3702
3703 /* +==========================================================================+
3704 | PROCEDURE NAME
3705 | gme_txn_message
3706 |
3707 | USAGE
3708 |
3709 |
3710 | ARGUMENTS
3711 |
3712 |
3713 | RETURNS
3714 |
3715 |
3716 | HISTORY
3717 | Created 02-Feb-05 Pawan Kumar
3718 |
3719 | G. Muratore 06-DEC-2012 Bug 14685438
3720 | Limited message size to size of database field.
3721 +==========================================================================+ */
3722 PROCEDURE gme_txn_message (
3723 p_api_name IN VARCHAR2
3724 ,p_transaction_interface_id IN VARCHAR2
3725 )
3726 IS
3727 l_transaction_interface_id NUMBER;
3728 x_message_count NUMBER;
3729 x_message_list VARCHAR2 (2000);
3730 l_errm VARCHAR2 (2000) := SQLERRM;
3731 l_api_name CONSTANT VARCHAR2 (30) := 'gme_txn_message';
3732 BEGIN
3733 -- Initially let us assign the return status to success
3734
3735 l_transaction_interface_id := p_transaction_interface_id;
3736
3737 IF (g_debug <= gme_debug.g_log_statement) THEN
3738 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3739 || 'Entering');
3740 END IF;
3741
3742 -- based on message call count and get
3743 gme_common_pvt.count_and_get (x_count => x_message_count
3744 ,p_encoded => fnd_api.g_false
3745 ,x_data => x_message_list);
3746
3747 -- Bug 14685438 - limit error string to size of the field.
3748 UPDATE mtl_transactions_interface
3749 SET ERROR_CODE = g_pkg_name || '.' || p_api_name
3750 ,error_explanation = SUBSTR(NVL (x_message_list, l_errm), 1, 240)
3751 ,process_flag = 3 -- we can make it a constant in gme common
3752 WHERE transaction_interface_id = l_transaction_interface_id;
3753
3754 IF (g_debug <= gme_debug.g_log_statement) THEN
3755 gme_debug.put_line ('Full error message is: '||NVL (x_message_list, l_errm));
3756
3757 gme_debug.put_line ( g_pkg_name
3758 || '.'
3759 || l_api_name
3760 || ':'
3761 || 'Exiting'
3762 );
3763 END IF;
3764 EXCEPTION
3765 WHEN OTHERS THEN
3766 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
3767
3768 IF g_debug <= gme_debug.g_log_unexpected THEN
3769 gme_debug.put_line ( 'When others exception in '
3770 || g_pkg_name
3771 || '.'
3772 || l_api_name
3773 || ' Error is '
3774 || SQLERRM);
3775 END IF;
3776 END gme_txn_message;
3777
3778 /* +==========================================================================+
3779 | PROCEDURE NAME
3780 | gme_post_process
3781 |
3782 | USAGE
3783 |
3784 |
3785 | ARGUMENTS
3786 | p_transaction_id
3787 |
3788 |
3789 | RETURNS
3790 | returns via x_status OUT parameters
3791 |
3792 | HISTORY
3793 | Created 02-Feb-05 Pawan Kumar
3794 | 5176319 20-Jun-06 Namit S. Added call to gme_unrelease_batch_pvt.create_matl_resv_pplot
3795 | and gme_common_pvt.reset_txn_hdr_tbl.
3796 | Bug 5763818 28-Feb-2007 Archana Mundhe Do not update actual qty if
3797 | the material detail line has been deleted.
3798 | Bug 8300015 Changed the logic of updating the phantom transactions.
3799 | Bug back port 6997483 Srinivasulu Puri Added parameter transaction_id
3800 | to gme_unrelease_batch_pvt.create_matl_resv_pplot.
3801 +==========================================================================+ */
3802 PROCEDURE gme_post_process (
3803 p_transaction_id IN NUMBER
3804 ,x_return_status OUT NOCOPY VARCHAR2)
3805 IS
3806 CURSOR cur_get_trans (v_transaction_id NUMBER)
3807 IS
3808 SELECT t.transaction_id, t.transaction_source_id, l.lot_number
3809 ,t.trx_source_line_id, t.source_line_id, t.transaction_type_id,
3810 t.transaction_reference, t.inventory_item_id
3811 , t.organization_id ,t.transaction_quantity
3812 FROM mtl_material_transactions t, mtl_transaction_lot_numbers l
3813 WHERE t.transaction_id = l.transaction_id(+)
3814 AND t.transaction_id = v_transaction_id;
3815 /* Bug 5903208 Modified cursor so it gets 1 lot at a time for GMF needs */
3816 CURSOR cur_mat_sum(v_organization_id IN NUMBER
3817 ,v_batch_id IN NUMBER
3818 ,v_mat_det_id IN NUMBER) IS
3819 SELECT a.transaction_id, a.lot_number, a.doc_qty, SUM(a.doc_qty) over() mtl_qty
3820 FROM (SELECT t.transaction_id, tl.lot_number,
3821 DECODE(d.dtl_um,t.transaction_uom, NVL(tl.transaction_quantity,t.transaction_quantity),
3822 Inv_Convert.inv_um_convert(d.inventory_item_id,tl.lot_number,t.organization_id, 5
3823 ,NVL(tl.transaction_quantity,t.transaction_quantity), t.transaction_uom
3824 ,d.dtl_um, NULL, NULL)) doc_qty
3825 FROM mtl_material_transactions t , gme_material_details d, mtl_transaction_lot_numbers tl
3826 WHERE t.organization_id = v_organization_id
3827 AND t.transaction_source_id = v_batch_id
3828 AND t.trx_source_line_id = v_mat_det_id
3829 AND t.transaction_source_type_id = gme_common_pvt.g_txn_source_type
3830 AND t.trx_source_line_id = d.material_detail_id
3831 AND tl.transaction_id(+) = t.transaction_id) a;
3832
3833 CURSOR cur_lot_qty (
3834 v_lot_number IN VARCHAR2
3835 ,v_item_id IN NUMBER
3836 ,v_organization_id IN NUMBER
3837 ,v_batch_id IN NUMBER
3838 ,v_mat_det_id IN NUMBER)
3839 IS
3840 SELECT lot_number, SUM (l.transaction_quantity)
3841 FROM mtl_material_transactions m, mtl_transaction_lot_numbers l
3842 WHERE l.transaction_id = m.transaction_id
3843 AND l.lot_number = v_lot_number
3844 AND l.inventory_item_id = v_item_id
3845 AND l.organization_id = v_organization_id
3846 AND l.transaction_source_id = v_batch_id
3847 AND m.trx_source_line_id = v_mat_det_id
3848 AND m.transaction_source_type_id =
3849 gme_common_pvt.g_txn_source_type
3850 GROUP BY l.lot_number, l.inventory_item_id;
3851
3852 -- Bug 5763818
3853 CURSOR check_event_batchmtl_removed (
3854 v_transaction_source_id IN NUMBER
3855 ,v_trx_source_line_id IN NUMBER)
3856 IS
3857 select count(1)
3858 from GME_ERES_GTMP
3859 where event_name = 'oracle.apps.gme.batchmtl.removed'
3860 and event_key = v_transaction_source_id||'-'||v_trx_source_line_id;
3861
3862 x_msg_count NUMBER;
3863 x_msg_data VARCHAR2(2000);
3864 l_return_status VARCHAR2(1) ;
3865 l_transaction_id NUMBER;
3866 l_dispense_id NUMBER;
3867 l_transaction_source_id NUMBER;
3868 l_transaction_type_id NUMBER;
3869 l_lot_number VARCHAR2 (80);
3870 l_transaction_reference VARCHAR2 (80);
3871 l_trx_source_line_id NUMBER;
3872 l_source_line_id NUMBER;
3873 l_inventory_item_id NUMBER;
3874 l_organization_id NUMBER;
3875 l_actual_qty NUMBER;
3876 l_gme_pairs_rec gme_transaction_pairs%ROWTYPE;
3877 l_mat_dtl_rec gme_material_details%ROWTYPE;
3878 l_api_name CONSTANT VARCHAR2 (30) := 'gme_post_process';
3879 l_exists NUMBER; -- Bug 5763818
3880 transfer_error EXCEPTION; -- B4944024
3881 dispense_error EXCEPTION;
3882
3883 l_gme_pairs_rec_upd gme_transaction_pairs%ROWTYPE;
3884 l_transaction_quantity NUMBER;
3885 l_transaction_quantity_upd NUMBER;
3886
3887
3888 BEGIN
3889 -- Initially let us assign the return status to success
3890 x_return_status := fnd_api.g_ret_sts_success;
3891
3892 IF (g_debug <= gme_debug.g_log_statement) THEN
3893 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
3894 || 'Entering with trans id: '||p_transaction_id);
3895
3896 END IF;
3897
3898 l_transaction_id := p_transaction_id;
3899 -- based on this transaction_id get all transactions details
3900 OPEN cur_get_trans (l_transaction_id);
3901
3902 FETCH cur_get_trans
3903 INTO l_transaction_id, l_transaction_source_id, l_lot_number
3904 ,l_trx_source_line_id, l_source_line_id,l_transaction_type_id,
3905 l_transaction_reference ,l_inventory_item_id, l_organization_id,l_transaction_quantity;
3906
3907 CLOSE cur_get_trans;
3908
3909 -- nsinghi bug#5176319
3910 /* Re-Create Material Reservation during un-release batch/step. */
3911 gme_unrelease_batch_pvt.create_matl_resv_pplot (
3912 p_material_dtl_id => l_trx_source_line_id,
3913 p_transaction_id => l_transaction_id,
3914 x_return_status => x_return_status);
3915
3916 IF (g_debug <= gme_debug.g_log_statement) THEN
3917 gme_debug.put_line ( g_pkg_name
3918 || '.'
3919 || l_api_name
3920 || ':'
3921 || 'transaction_id: '
3922 || l_transaction_id);
3923 gme_debug.put_line ( g_pkg_name
3924 || '.'
3925 || l_api_name
3926 || ':'
3927 || 'transaction_source_id: '
3928 || l_transaction_source_id);
3929 gme_debug.put_line ( g_pkg_name
3930 || '.'
3931 || l_api_name
3932 || ':'
3933 || 'trx_source_line_id: '
3934 || l_trx_source_line_id);
3935 gme_debug.put_line ( g_pkg_name
3936 || '.'
3937 || l_api_name
3938 || ':'
3939 || 'source_line_id: '
3940 || l_source_line_id);
3941 gme_debug.put_line ( g_pkg_name
3942 || '.'
3943 || l_api_name
3944 || ':'
3945 || 'inventory_item_id: '
3946 || l_inventory_item_id);
3947 gme_debug.put_line ( g_pkg_name
3948 || '.'
3949 || l_api_name
3950 || ':'
3951 || 'transaction refernce: '
3952 || l_transaction_reference);
3953 END IF;
3954
3955 IF l_source_line_id <> -99 THEN
3956 IF (g_debug <= gme_debug.g_log_statement) THEN
3957 gme_debug.put_line ( g_pkg_name
3958 || '.'
3959 || l_api_name
3960 || ':'
3961 || 'for inserting reverse transaction_id: '
3962 || l_transaction_id);
3963 gme_debug.put_line ( g_pkg_name
3964 || '.'
3965 || l_api_name
3966 || ':'
3967 || 'for inserting reverse source_line_id: '
3968 || l_source_line_id);
3969 END IF;
3970
3971 -- UPDATE transactions pair table for the reversal transaction
3972 UPDATE gme_transaction_pairs
3973 SET transaction_id2 = l_transaction_id
3974 WHERE batch_id = l_transaction_source_id
3975 AND material_detail_id = l_trx_source_line_id
3976 AND transaction_id1 = l_source_line_id
3977 AND pair_type = gme_common_pvt.g_pairs_reversal_type;
3978 -- Now insert a reverse record
3979 INSERT INTO gme_transaction_pairs
3980 (batch_id, material_detail_id
3981 ,transaction_id1, transaction_id2
3982 ,pair_type)
3983 VALUES (l_transaction_source_id, l_trx_source_line_id
3984 ,l_transaction_id, l_source_line_id
3985 ,gme_common_pvt.g_pairs_reversal_type);
3986
3987 IF (g_debug <= gme_debug.g_log_statement) THEN
3988 gme_debug.put_line ( g_pkg_name
3989 || '.'
3990 || l_api_name
3991 || ':'
3992 || 'after inserting reverse transaction_id: '
3993 || l_transaction_id);
3994 gme_debug.put_line ( g_pkg_name
3995 || '.'
3996 || l_api_name
3997 || ':'
3998 || 'after inserting reverse source_line_id: '
3999 || l_source_line_id);
4000 END IF;
4001 END IF; -- l_source_line_id
4002
4003 -- for transaction pairs
4004 IF l_transaction_reference IS NOT NULL THEN
4005 l_mat_dtl_rec.material_detail_id := l_trx_source_line_id ;
4006 IF NOT gme_material_details_dbl.fetch_row
4007 (p_material_detail => l_mat_dtl_rec
4008 ,x_material_detail => l_mat_dtl_rec) THEN
4009 RAISE fnd_api.g_exc_error;
4010 END IF;
4011 IF (g_debug <= gme_debug.g_log_statement) THEN
4012 gme_debug.put_line
4013 ( g_pkg_name
4014 || '.'
4015 || l_api_name
4016 || ':'
4017 || 'for inserting phantom l_transaction_reference: '
4018 || l_transaction_reference);
4019 END IF;
4020
4021
4022 IF l_mat_dtl_rec.phantom_line_id IS NOT NULL THEN
4023 IF (g_debug <= gme_debug.g_log_statement) THEN
4024 gme_debug.put_line
4025 ( g_pkg_name
4026 || '.'
4027 || l_api_name
4028 || '.'
4029 || 'update row -phantom in pairs having l_trans_ref: '
4030 || l_transaction_reference);
4031 gme_debug.put_line
4032 ( g_pkg_name
4033 || '.'
4034 || l_api_name
4035 || '.'
4036 || 'update row-phantom in pairs with l_trans_ID: '
4037 || l_transaction_id);
4038 END IF;
4039 /* Added the below loop to update the transaction only if it matches with the existing transactions
4040 and with the qty and opposite sign */
4041 FOR l_gme_pairs_rec_upd in (select * from gme_transaction_pairs where transaction_id2 = l_transaction_reference)
4042 LOOP
4043 SELECT transaction_quantity INTO l_transaction_quantity_upd
4044 FROM mtl_material_transactions t
4045 WHERE t.transaction_id = l_gme_pairs_rec_upd.transaction_id1;
4046
4047 IF (l_transaction_quantity_upd = (-1) *l_transaction_quantity )AND
4048 (l_mat_dtl_rec.phantom_line_id = l_gme_pairs_rec_upd.material_detail_id) THEN
4049 UPDATE gme_transaction_pairs
4050 SET transaction_id2 = l_transaction_id
4051 WHERE transaction_id1 = l_gme_pairs_rec_upd.transaction_id1
4052 AND pair_type = gme_common_pvt.g_pairs_phantom_type;
4053 END IF;
4054 END LOOP;
4055 BEGIN
4056 SELECT *
4057 INTO l_gme_pairs_rec
4058 FROM gme_transaction_pairs
4059 WHERE transaction_id2 = l_transaction_id
4060 AND pair_type = gme_common_pvt.g_pairs_phantom_type;
4061
4062 IF (g_debug <= gme_debug.g_log_statement) THEN
4063 gme_debug.put_line
4064 ( g_pkg_name
4065 || '.'
4066 || l_api_name
4067 || '.'
4068 || 'after update row -phantom l_trans_id1: '
4069 || l_gme_pairs_rec.transaction_id1);
4070 gme_debug.put_line ( g_pkg_name
4071 || '.'
4072 || l_api_name
4073 || '.'
4074 || 'after update row-phantom l_trans_ID2: '
4075 || l_gme_pairs_rec.transaction_id1);
4076 gme_debug.put_line
4077 ( g_pkg_name
4078 || '.'
4079 || l_api_name
4080 || '.'
4081 || 'insert row- after update -transaction_id1: '
4082 || l_gme_pairs_rec.transaction_id1);
4083 gme_debug.put_line
4084 ( g_pkg_name
4085 || '.'
4086 || l_api_name
4087 || '.'
4088 || 'insert row- after update -transaction_id2: '
4089 || l_gme_pairs_rec.transaction_id2);
4090 gme_debug.put_line ( g_pkg_name
4091 || '.'
4092 || l_api_name
4093 || '.'
4094 || 'insert row- after update -batch_id:'
4095 || l_transaction_source_id);
4096 gme_debug.put_line ( g_pkg_name
4097 || '.'
4098 || l_api_name
4099 || '.'
4100 || 'insert row- after update -mat_det_id: '
4101 || l_trx_source_line_id);
4102 END IF; -- for debug
4103
4104 INSERT INTO gme_transaction_pairs
4105 (batch_id, material_detail_id
4106 ,transaction_id1, transaction_id2
4107 ,pair_type)
4108 VALUES (l_transaction_source_id, l_trx_source_line_id
4109 ,l_transaction_id, l_gme_pairs_rec.transaction_id1
4110 ,gme_common_pvt.g_pairs_phantom_type);
4111
4112
4113 EXCEPTION
4114 WHEN NO_DATA_FOUND THEN
4115 IF (g_debug <= gme_debug.g_log_statement) THEN
4116 gme_debug.put_line ( g_pkg_name
4117 || '.'
4118 || l_api_name
4119 || '.'
4120 || 'No_data_found');
4121 gme_debug.put_line ( g_pkg_name
4122 || '.'
4123 || l_api_name
4124 || '.'
4125 || 'insert row-transaction_id1: '
4126 || l_transaction_id);
4127 gme_debug.put_line ( g_pkg_name
4128 || '.'
4129 || l_api_name
4130 || '.'
4131 || 'insert row-transaction_id2: '
4132 || l_transaction_reference);
4133 gme_debug.put_line ( g_pkg_name
4134 || '.'
4135 || l_api_name
4136 || '.'
4137 || 'insert row-batch_id: '
4138 || l_transaction_source_id);
4139 gme_debug.put_line ( g_pkg_name
4140 || '.'
4141 || l_api_name
4142 || '.'
4143 || 'insert row-material_detail_id: '
4144 || l_trx_source_line_id);
4145 END IF;
4146
4147 --INSERT a new row
4148 INSERT INTO gme_transaction_pairs
4149 (batch_id, material_detail_id
4150 ,transaction_id1, transaction_id2
4151 ,pair_type)
4152 VALUES (l_transaction_source_id, l_trx_source_line_id
4153 ,l_transaction_id, l_transaction_reference
4154 ,gme_common_pvt.g_pairs_phantom_type);
4155 END;
4156 ELSE
4157 l_dispense_id := l_transaction_reference ;
4158 -- make a call to GMO for informing about dispense_id
4159 IF l_mat_dtl_rec.dispense_ind = 'Y' THEN
4160 IF l_transaction_type_id = gme_common_pvt.g_ing_issue THEN
4161 -- For consume
4162 GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
4163 (p_api_version => 1.0,
4164 p_init_msg_list => 'F',
4165 p_commit => 'F',
4166 x_return_status => l_return_status,
4167 x_msg_count => x_msg_count,
4168 x_msg_data => x_msg_data,
4169 p_dispense_id => l_dispense_id,
4170 p_status_code => 'CNSUMED',
4171 p_transaction_id => l_transaction_id
4172 ) ;
4173 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4174 RAISE dispense_error;
4175 END IF;
4176 ELSE
4177 -- unconsume
4178
4179 GMO_DISPENSE_GRP.CHANGE_DISPENSE_STATUS
4180 (p_api_version => 1.0,
4181 p_init_msg_list => 'F',
4182 p_commit => 'F',
4183 x_return_status => l_return_status,
4184 x_msg_count => x_msg_count,
4185 x_msg_data => x_msg_data ,
4186 p_dispense_id => l_dispense_id,
4187 p_status_code => 'REVRDISP' ,
4188 p_transaction_id => l_transaction_id
4189 ) ;
4190 IF l_return_status <> fnd_api.g_ret_sts_success THEN
4191 RAISE dispense_error;
4192 END IF;
4193 END IF;
4194 END IF ; -- dispense_ind
4195 END IF; -- if phantom_line_id is not null
4196 END IF; -- transaction_refernce is not null
4197
4198 -- Bug 5763818
4199 -- Open cursor to fetch event name and event key from gme_eres_gmtp
4200 -- IF event name is oracle.apps.gme.batchmtl.removed then do not process
4201 OPEN check_event_batchmtl_removed (l_transaction_source_id,
4202 l_trx_source_line_id);
4203 FETCH check_event_batchmtl_removed INTO l_exists;
4204 CLOSE check_event_batchmtl_removed;
4205
4206 IF (g_debug <= gme_debug.g_log_statement) THEN
4207 gme_debug.put_line (g_pkg_name||'.'||l_api_name||': '||'l_exists = '||
4208 TO_CHAR (l_exists));
4209 END IF;
4210
4211 -- Bug 5763818
4212 -- Do not update the actual qty if the line has been deleted.
4213 IF (l_exists = 0) THEN
4214
4215 -- get the total quantity for actaul qty update of material detail line
4216 l_mat_dtl_rec.material_detail_id := l_trx_source_line_id;
4217
4218 IF NOT gme_material_details_dbl.fetch_row
4219 (p_material_detail => l_mat_dtl_rec
4220 ,x_material_detail => l_mat_dtl_rec) THEN
4221 RAISE fnd_api.g_exc_error;
4222 END IF;
4223
4224 IF (g_debug <= gme_debug.g_log_statement) THEN
4225 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||' mat_id '|| l_mat_dtl_rec.material_detail_id );
4226 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||' DTL_UM '|| l_mat_dtl_rec.dtl_um );
4227
4228 END IF;
4229 --sum for material_detail line
4230 /* Bug 5903208 Instead of directly getting sum now we get 1 lot at a time and also the sum */
4231 FOR get_rec IN cur_mat_sum(l_organization_id, l_transaction_source_id, l_trx_source_line_id) LOOP
4232 l_actual_qty := get_rec.mtl_qty;
4233 p_qty_tbl(get_rec.transaction_id||'@'||get_rec.lot_number).doc_qty := get_rec.doc_qty;
4234 END LOOP;
4235
4236 -- Now update the qty to material_detail
4237 IF (g_debug <= gme_debug.g_log_statement) THEN
4238 gme_debug.put_line ( g_pkg_name
4239 || '.'
4240 || l_api_name
4241 || ':Actual Quantity '
4242 || l_actual_qty);
4243 gme_debug.put_line ( g_pkg_name
4244 || '.'
4245 || l_api_name
4246 || ': TRX_SOURCE_LINE_ID '
4247 || l_trx_source_line_id);
4248 END IF;
4249
4250 l_mat_dtl_rec.actual_qty := ABS (l_actual_qty);
4251
4252 IF NOT gme_material_details_dbl.update_row
4253 (p_material_detail => l_mat_dtl_rec) THEN
4254 RAISE fnd_api.g_exc_error;
4255 END IF;
4256
4257 -- B4944024 BEGIN
4258 -- At yield, any reservations against PROD must transfer to a supply source of Inventory
4259 -- In this way, the newly generated inventory is re-secured to the demand source
4260 -- ======================================================================================
4261 -- Pawan Kumar bug 5483071 added check for transaction refernce and source_line_id
4262 -- THis done so that we donot try to invoke this for reversal of wip return
4263 -- dispense is not a issue as it is only for ingredients
4264 IF l_mat_dtl_rec.line_type <> -1 AND
4265 l_transaction_type_id = gme_common_pvt.g_prod_completion
4266 AND l_transaction_reference IS NULL
4267 -- Pawan Kumar add bug 5709186
4268 -- in case transaction added from transaction form, l_source_line_id is null.
4269 AND nvl(l_source_line_id, -99) < 0 THEN
4270
4271 IF (g_debug <= gme_debug.g_log_statement) THEN
4272 gme_debug.put_line ( g_pkg_name
4273 || '.'
4274 || l_api_name
4275 || ' Invoke transfer_reservation_to_inv ');
4276 END IF;
4277 GME_SUPPLY_RES_PVT.transfer_reservation_to_inv (
4278 p_matl_dtl_rec => l_mat_dtl_rec
4279 ,p_transaction_id => p_transaction_id
4280 ,x_message_count => x_msg_count
4281 ,x_message_list => x_msg_data
4282 ,x_return_status => x_return_status);
4283
4284 IF (g_debug <= gme_debug.g_log_statement) THEN
4285 gme_debug.put_line ( g_pkg_name
4286 || '.'
4287 || l_api_name
4288 || ' transfer_reservation_to_inv returns '
4289 || x_return_status);
4290 END IF;
4291 IF x_return_status <> fnd_api.g_ret_sts_success THEN
4292 RAISE transfer_error; -- B4944024
4293 END IF;
4294 END IF;
4295 -- B4944024 END
4296
4297 -- Now check for negative qty
4298 IF (g_debug <= gme_debug.g_log_statement) THEN
4299 gme_debug.put_line ( g_pkg_name
4300 || '.'
4301 || l_api_name
4302 || ':'
4303 || 'Exiting with '
4304 || x_return_status);
4305 END IF;
4306 END IF; -- l_exists = 0
4307 EXCEPTION
4308 WHEN dispense_error THEN
4309
4310 x_return_status := fnd_api.g_ret_sts_error;
4311 WHEN fnd_api.g_exc_error THEN
4312 x_return_status := fnd_api.g_ret_sts_error;
4313 WHEN fnd_api.g_exc_unexpected_error THEN
4314 x_return_status := fnd_api.g_ret_sts_unexp_error;
4315 WHEN OTHERS THEN
4316 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4317
4318 IF g_debug <= gme_debug.g_log_unexpected THEN
4319 gme_debug.put_line ( 'When others exception in '
4320 || g_pkg_name
4321 || '.'
4322 || l_api_name
4323 || ' Error is '
4324 || SQLERRM);
4325 END IF;
4326 END gme_post_process;
4327
4328
4329 /* +==========================================================================+
4330 | PROCEDURE NAME
4331 | purge_trans_pairs
4332 |
4333 | USAGE
4334 |
4335 |
4336 | ARGUMENTS
4337 | p_batch_id
4338 | p_material_detail_id
4339 |
4340 | RETURNS
4341 | returns via x_status OUT parameters
4342 |
4343 | HISTORY
4344 | Created 02-Feb-05 Pawan Kumar
4345 |
4346 +==========================================================================+ */
4347 PROCEDURE purge_trans_pairs (
4348 p_batch_id IN NUMBER
4349 ,p_material_detail_id IN NUMBER DEFAULT NULL
4350 ,x_return_status OUT NOCOPY VARCHAR2)
4351 IS
4352 l_batch_id NUMBER;
4353 l_material_detail_id NUMBER;
4354 l_api_name CONSTANT VARCHAR2 (30) := 'purge_trans_pairs';
4355 BEGIN
4356 -- Initially let us assign the return status to success
4357 x_return_status := fnd_api.g_ret_sts_success;
4358
4359 IF (g_debug <= gme_debug.g_log_statement) THEN
4360 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4361 || 'Entering');
4362 END IF;
4363
4364 IF p_batch_id IS NULL AND p_material_detail_id IS NULL THEN
4365 gme_common_pvt.log_message ('GME_INVALID_FIELD'
4366 ,'FIELD'
4367 ,'p_batch_id');
4368 RAISE fnd_api.g_exc_error;
4369 END IF;
4370
4371 l_batch_id := p_batch_id;
4372 l_material_detail_id := p_material_detail_id;
4373
4374 IF (g_debug <= gme_debug.g_log_statement) THEN
4375 gme_debug.put_line ( g_pkg_name
4376 || '.'
4377 || l_api_name
4378 || ':'
4379 || 'batch_id:'
4380 || l_batch_id);
4381 gme_debug.put_line ( g_pkg_name
4382 || '.'
4383 || l_api_name
4384 || ':'
4385 || 'material_detail_id:'
4386 || l_material_detail_id);
4387 END IF;
4388
4389 IF l_batch_id IS NOT NULL THEN
4390 DELETE FROM gme_transaction_pairs
4391 WHERE batch_id = l_batch_id;
4392 ELSIF l_material_detail_id IS NOT NULL THEN
4393 DELETE FROM gme_transaction_pairs
4394 WHERE material_detail_id = l_material_detail_id;
4395 ELSE
4396 DELETE FROM gme_transaction_pairs
4397 WHERE batch_id = l_batch_id
4398 AND material_detail_id = l_material_detail_id;
4399 END IF;
4400
4401 IF (g_debug <= gme_debug.g_log_statement) THEN
4402 gme_debug.put_line ( g_pkg_name
4403 || '.'
4404 || l_api_name
4405 || ':'
4406 || 'Exiting with '
4407 || x_return_status);
4408 END IF;
4409 EXCEPTION
4410 WHEN fnd_api.g_exc_error THEN
4411 x_return_status := fnd_api.g_ret_sts_error;
4412 WHEN fnd_api.g_exc_unexpected_error THEN
4413 x_return_status := fnd_api.g_ret_sts_unexp_error;
4414 WHEN OTHERS THEN
4415 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
4416
4417 IF g_debug <= gme_debug.g_log_unexpected THEN
4418 gme_debug.put_line ( 'When others exception in '
4419 || g_pkg_name
4420 || '.'
4421 || l_api_name
4422 || ' Error is '
4423 || SQLERRM);
4424 END IF;
4425 END purge_trans_pairs;
4426
4427 /* +==========================================================================+
4428 | PROCEDURE NAME
4429 | Process_transactions
4430 |
4431 | USAGE
4432 | This is the interface procedure to the Inventory Transaction
4433 | Manager to validate and process a batch of material transaction
4434 | interface records
4435 |
4436 | ARGUMENTS
4437 | p_api_version API Version of this procedure. Current version is 1.0
4438 |
4439 | p_commit Indicates whether to commit the changes after successful processing
4440 | p_validation_level Indicates whether or not to perform a full validation
4441 | x_return_status Returns the status to indicate success or failure of execution
4442 | x_msg_count Returns number of error message in the error message stack in case of failure
4443 | x_msg_data Returns the error message in case of failure
4444 | x_trans_count The count of material transaction interface records processed.
4445 | p_table Source of transaction records with value 1 of material transaction interface table and value 2 of material transaction temp table
4446 | p_header_id Transaction header id DEFAULT gme_common_pvt.get_txn_header_id
4447 |
4448 | RETURNS
4449 | returns via x_ OUT parameters
4450 |
4451 | HISTORY
4452 | Created 07-Mar-05 Jalaj Srivastava
4453 | 26-JUL-2007 Swapna Bug#6266714
4454 | Added condition to check the transaction source type in the for loop query.
4455 | 26-JUL-2007 Swapna Bug#6685680
4456 | Added call to gme_common_pvt.log message to log the actual error message which we can
4457 | retrieve using gme_common_pvt.count_and_get from the wraper apis.
4458 |
4459 | 10-MAR-2009 Hari Luthra BUG # 6335682
4460 | Added condition gtp.pair_type(+) = 1 to avoid duplicate rows in the for loop while creation
4461 | of layers so as to handle the transactions for phantom batches.
4462 | 18-MAR-2009 Parag Kanetkar Bug 8347011 base bug 8219507 Removed mtln join before calling
4463 | layers API.
4464 |
4465 | 26-MAY-2009 G. Muratore Bug 8453485
4466 | Added dynamically derived column rev_order_column to help us in order by clause.
4467 | This will aid in handling Product Yield reversals first for layer sequencing for GMF.
4468 |
4469 | 26-APR-2012 A. Mishra Bug 13835011
4470 | Update the grade code in MTLT OR MTLI
4471 |
4472 | 04-FEB-2013 G. Muratore Bug 16079842
4473 | Backout fixes done for 13835011 and 14297117.
4474 +==========================================================================+ */
4475 /* Bug 5255959 added p_clear_qty_cache parameter */
4476 PROCEDURE process_transactions (
4477 p_api_version IN NUMBER := 1
4478 ,p_init_msg_list IN VARCHAR2 := fnd_api.g_false
4479 ,p_commit IN VARCHAR2 := fnd_api.g_false
4480 ,p_validation_level IN NUMBER
4481 := fnd_api.g_valid_level_full
4482 ,p_table IN NUMBER := 2
4483 ,p_header_id IN NUMBER
4484 := gme_common_pvt.get_txn_header_id
4485 ,x_return_status OUT NOCOPY VARCHAR2
4486 ,x_msg_count OUT NOCOPY NUMBER
4487 ,x_msg_data OUT NOCOPY VARCHAR2
4488 ,x_trans_count OUT NOCOPY NUMBER
4489 --Bug#5584699 Changed variable from boolean to varchar2
4490 ,p_clear_qty_cache IN VARCHAR2 := fnd_api.g_true)
4491 --,p_clear_qty_cache IN BOOLEAN DEFAULT TRUE)
4492 IS
4493
4494 --bug 7720970 kbanddyo added join error_explanation IS NOT NULL for both the cursors below
4495
4496 CURSOR get_error_int
4497 IS
4498 SELECT ERROR_CODE, error_explanation
4499 FROM mtl_transactions_interface
4500 WHERE transaction_header_id =gme_common_pvt.g_transaction_header_id
4501 AND error_explanation IS NOT NULL;
4502
4503 CURSOR get_error_temp
4504 IS
4505 SELECT ERROR_CODE, error_explanation
4506 FROM mtl_material_transactions_temp
4507 WHERE transaction_header_id =gme_common_pvt.g_transaction_header_id
4508 AND error_explanation IS NOT NULL;
4509
4510 l_api_name CONSTANT VARCHAR2 (30) := 'PROCESS_TRANSACTIONS';
4511 l_return NUMBER;
4512 l_trans_rec GMF_LAYERS.TRANS_REC_TYPE;
4513
4514 --
4515 -- Bug 10306577
4516 -- Getting a count of the records to be processed from MMTT
4517 --
4518 int_rec_count number;
4519
4520 BEGIN
4521 IF (NVL (g_debug, 0) IN
4522 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4523 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4524 || 'Entering');
4525 END IF;
4526
4527 --
4528 -- Bug 10306577
4529 -- Getting a count of records to be processed from MMTT and MTI
4530 --
4531 Select sum(cnt)
4532 INTO int_rec_count
4533 FROM (
4534 SELECT COUNT (*) cnt
4535 FROM mtl_material_transactions_temp
4536 WHERE transaction_header_id = p_header_id
4537 AND process_flag = 'Y'
4538 UNION ALL
4539 SELECT COUNT (*) cnt
4540 FROM mtl_transactions_interface
4541 WHERE transaction_header_id = p_header_id
4542 AND process_flag = 1
4543 );
4544
4545 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4546 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || ' count of records from MTI :'||int_rec_count);
4547 END IF;
4548
4549 --
4550 -- Bug 10306577
4551 -- Call the INV API to process the txns only if there are records in MMTT
4552 -- to be processed
4553 --
4554 If int_rec_count <>0 Then
4555 /* Jalaj Srivastava Bug 5109154
4556 if p_table is MMTT then
4557 free the quantity tree */
4558 /* Bug 5255959 added p_clear_qty_cache condition */
4559 --Bug#5584699
4560 IF (p_table = 2 AND p_clear_qty_cache = fnd_api.g_true) THEN
4561 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4562 gme_debug.put_line
4563 ( g_pkg_name
4564 || '.'
4565 || l_api_name
4566 || ':'
4567 || 'Calling inv_quantity_tree_pub.clear_quantity_cache. p_table is MMTT');
4568 END IF;
4569 inv_quantity_tree_pub.clear_quantity_cache;
4570 END IF;
4571
4572 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4573 gme_debug.put_line
4574 ( g_pkg_name
4575 || '.'
4576 || l_api_name
4577 || ':'
4578 || 'Calling INV_TXN_MANAGER_PUB.process_transactions');
4579 gme_debug.put_line ( g_pkg_name
4580 || '.'
4581 || l_api_name
4582 || ':'
4583 || 'Transaction Header ID = '
4584 || p_header_id);
4585 gme_debug.put_line
4586 ( g_pkg_name
4587 || '.'
4588 || l_api_name
4589 || ':'
4590 || 'Transaction Table passed in MTI->1 MMTT->2 = '
4591 || p_table);
4592 END IF;
4593
4594 -- Bug 16079842 - comment out this block.
4595
4596 /*
4597 -- Bug 13835011. Update the grade code in MTLT OR MTLI
4598 -- Bug 14297117. Update the Supplier lot number
4599 IF (p_table = 2) THEN
4600 FOR mmtt_rec_cur in
4601 (SELECT transaction_temp_id, inventory_item_id, organization_id
4602 FROM mtl_material_transactions_temp
4603 WHERE transaction_header_id = p_header_id
4604 AND process_flag = 'Y') LOOP
4605 UPDATE mtl_transaction_lots_temp mtlt
4606 SET (grade_code, supplier_lot_number) =
4607 (SELECT grade_code, supplier_lot_number
4608 FROM mtl_lot_numbers mln
4609 WHERE mln.lot_number = mtlt.lot_number
4610 AND mln.organization_id = mmtt_rec_cur.organization_id
4611 AND inventory_item_id = mmtt_rec_cur.inventory_item_id)
4612 WHERE transaction_temp_id = mmtt_rec_cur.transaction_temp_id
4613 AND (grade_code is null OR supplier_lot_number IS NULL);
4614 END LOOP;
4615 ELSE
4616 FOR mti_rec_cur in
4617 (SELECT transaction_interface_id, inventory_item_id, organization_id
4618 FROM mtl_Transactions_interface
4619 WHERE transaction_header_id = p_header_id
4620 AND process_flag = 1) LOOP
4621 UPDATE mtl_transaction_lots_interface mti
4622 SET (grade_code, supplier_lot_number) =
4623 (SELECT grade_code, supplier_lot_number
4624 FROM mtl_lot_numbers mln
4625 WHERE mln.lot_number = mti.lot_number
4626 AND mln.organization_id = mti_rec_cur.organization_id
4627 AND inventory_item_id = mti_rec_cur.inventory_item_id)
4628 WHERE transaction_interface_id = mti_rec_cur.transaction_interface_id
4629 AND (grade_code is null OR supplier_lot_number IS NULL);
4630 END LOOP;
4631 END IF;
4632 */
4633
4634 l_return :=
4635 inv_txn_manager_pub.process_transactions
4636 (p_api_version => p_api_version
4637 ,p_init_msg_list => p_init_msg_list
4638 ,p_commit => p_commit
4639 ,p_validation_level => p_validation_level
4640 ,p_table => p_table
4641 ,p_header_id => p_header_id
4642 ,x_return_status => x_return_status
4643 ,x_msg_count => x_msg_count
4644 ,x_msg_data => x_msg_data
4645 ,x_trans_count => x_trans_count);
4646
4647 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4648 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'After calling INV_TXN_MANAGER_PUB.process_transactions l_return= '
4649 || l_return || ' x_return_status= '|| x_return_status
4650 || ' x_msg_data = '|| x_msg_data);
4651 END IF;
4652
4653 /* begin temporary */
4654 IF (l_return = 0) THEN
4655 x_return_status := 'S';
4656 END IF;
4657 IF (x_msg_data IS NOT NULL) THEN
4658 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
4659 ,p_product_code => 'FND'
4660 ,p_token1_name => 'MESSAGE'
4661 ,p_token1_value => x_msg_data);
4662 END IF;
4663 IF (l_return < 0) THEN
4664 --Pawan Added for messages display
4665 IF p_table = 1 THEN
4666 x_msg_count := 0;
4667 FOR rec IN get_error_int LOOP
4668 /*Bug#6685680 Add the below call to log the actual error message*/
4669 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
4670 ,p_product_code => 'FND'
4671 ,p_token1_name => 'MESSAGE'
4672 ,p_token1_value => rec.error_explanation);
4673 --fnd_message.set_encoded (rec.error_explanation);
4674
4675 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4676 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| rec.error_explanation);
4677 END IF;
4678 x_msg_count := x_msg_count + 1;
4679 x_msg_data := rec.error_explanation;
4680 END LOOP;
4681 ELSE
4682 x_msg_count := 0;
4683 FOR rec IN get_error_temp LOOP
4684 /*Bug#6685680 Add the below call to log the actual error message*/
4685 gme_common_pvt.log_message(p_message_code => 'FND_GENERIC_MESSAGE'
4686 ,p_product_code => 'FND'
4687 ,p_token1_name => 'MESSAGE'
4688 ,p_token1_value => rec.error_explanation);
4689 --fnd_message.set_encoded (rec.error_explanation);
4690
4691 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4692 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| rec.error_explanation);
4693 END IF;
4694 x_msg_count := x_msg_count + 1;
4695 x_msg_data := rec.error_explanation;
4696 END LOOP;
4697 END IF; -- IF p_table
4698
4699 IF (x_return_status IS NULL OR x_return_status = 'S') THEN
4700 RAISE fnd_api.g_exc_unexpected_error;
4701 END IF;
4702 END IF;
4703 /* Bug 5903208 Start GMF code */
4704 /* BUG 6335682 HALUTHRA : Adding gtp.pair_type (+) = 1 to avoid duplicate generation of layers to handle phantom batches*/
4705 /* Bug 8347011 base bug 8219507 removed mtln join from the query */
4706
4707 -- Bug 8453485 - Added dynamically derived column rev_order_column to help us in order
4708 -- by clause. This will aid in handling Product Yield reversals for layer sequencing.
4709 -- It will force a real reversing transaction to be processed first.
4710 else
4711 -- Bug 10306577
4712 -- No records to be processed so setting the status to S for further processing.
4713 --
4714 x_return_status := 'S';
4715 end if;
4716
4717 IF x_return_status = 'S' THEN
4718 FOR trans_rec in
4719 (
4720 SELECT
4721 mmt.transaction_id
4722 , mmt.transaction_source_type_id
4723 , mmt.transaction_action_id
4724 , mmt.transaction_type_id
4725 , mmt.inventory_item_id
4726 , mmt.organization_id
4727 , NULL as lot_number
4728 , mmt.transaction_date
4729 , mmt.primary_quantity as primary_quantity /* Changed for Bug 8347011 base bug 8219507 */
4730 --nvl(mtln.primary_quantity, mmt.primary_quantity) as primary_quantity
4731 , msi.primary_uom_code
4732 ,mmt.transaction_quantity as transaction_quantity /* Changed for Bug 8347011 base bug 8219507 */
4733 --, nvl(mtln.transaction_quantity, mmt.transaction_quantity) as transaction_quantity /* Doc Qty */
4734 , md.dtl_um as doc_uom
4735 , mmt.transaction_source_id -- batch_id
4736 , mmt.trx_source_line_id -- line_id
4737 , gtp.transaction_id2 AS reverse_id
4738 , decode(NVL(gtp.transaction_id2, 0), 0, mmt.transaction_id + 999, mmt.transaction_id) as rev_order_column
4739 , md.line_type
4740 , mmt.last_updated_by
4741 , mmt.created_by
4742 , mmt.last_update_login
4743 FROM
4744 --mtl_material_transactions mmt,
4745 --mtl_transaction_lot_numbers mtln,
4746 mtl_material_transactions mmt, /* Removed mtln for Bug 8347011 base bug 8219507 */
4747 mtl_system_items_b msi,
4748 gme_material_details md,
4749 gme_transaction_pairs gtp
4750 WHERE
4751 mmt.transaction_set_id = gme_common_pvt.g_transaction_header_id
4752 --AND mtln.transaction_id(+) = mmt.transaction_id /*Commented for Bug 8347011*/
4753 AND msi.organization_id = mmt.organization_id
4754 AND msi.inventory_item_id = mmt.inventory_item_id
4755 AND md.material_detail_id = mmt.trx_source_line_id
4756 AND gtp.transaction_id1(+) = mmt.transaction_id
4757 AND gtp.batch_id(+) = mmt.transaction_source_id
4758 AND gtp.material_detail_id(+) = mmt.trx_source_line_id
4759 AND mmt.transaction_source_type_id = gme_common_pvt.g_txn_source_type /*Bug#6266714*/
4760 AND gtp.pair_type (+) = 1 /*BUG 6335682 */
4761 ORDER BY mmt.transaction_date,
4762 case md.line_type
4763 when -1 then 0
4764 when 2 then 1
4765 when 1 then 2
4766 end,
4767 md.material_detail_id,
4768 case md.line_type
4769 when -1 then mmt.transaction_id
4770 when 2 then rev_order_column
4771 when 1 then rev_order_column
4772 --mmt.transaction_id, mtln.lot_number) LOOP
4773 end) LOOP
4774 -- mmt.transaction_id, lot_number) LOOP
4775 l_trans_rec.transaction_id := trans_rec.transaction_id;
4776 l_trans_rec.transaction_source_type_id := trans_rec.transaction_source_type_id;
4777 l_trans_rec.transaction_action_id := trans_rec.transaction_action_id;
4778 l_trans_rec.transaction_type_id := trans_rec.transaction_type_id;
4779 l_trans_rec.inventory_item_id := trans_rec.inventory_item_id;
4780 l_trans_rec.organization_id := trans_rec.organization_id;
4781 l_trans_rec.lot_number := trans_rec.lot_number;
4782 l_trans_rec.transaction_date := trans_rec.transaction_date;
4783 l_trans_rec.primary_quantity := trans_rec.primary_quantity;
4784 l_trans_rec.primary_uom := trans_rec.primary_uom_code;
4785 l_trans_rec.doc_qty := trans_rec.transaction_quantity; /* Bug 8347011 base bug 8219507 */
4786 l_trans_rec.doc_uom := trans_rec.doc_uom;
4787 l_trans_rec.transaction_source_id := trans_rec.transaction_source_id;
4788 l_trans_rec.trx_source_line_id := trans_rec.trx_source_line_id;
4789 l_trans_rec.reverse_id := trans_rec.reverse_id;
4790 l_trans_rec.line_type := trans_rec.line_type;
4791 l_trans_rec.last_updated_by := trans_rec.last_updated_by;
4792 l_trans_rec.created_by := trans_rec.created_by;
4793 l_trans_rec.last_update_login := trans_rec.last_update_login;
4794
4795 IF trans_rec.transaction_action_id in (1, 27) THEN
4796 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4797 gme_debug.put_line('in process_txns for (INGR) actionID: ' || trans_rec.transaction_action_id || '...calling GMF outgoing layers'); --xxxremove
4798 gme_debug.put_line('in process_txns for (INGR) actionID: ' || trans_rec.transaction_action_id || '...calling GMF outgoing layers');
4799 END IF;
4800
4801 l_trans_rec.doc_qty := -1 * l_trans_rec.doc_qty;
4802
4803 gmf_layers.Create_outgoing_Layers
4804 ( p_api_version => 1.0,
4805 p_init_msg_list => FND_API.G_FALSE,
4806 p_tran_rec => l_trans_rec,
4807 x_return_status => x_return_status,
4808 x_msg_count => x_msg_count,
4809 x_msg_data => x_msg_data);
4810 ELSIF trans_rec.transaction_action_id in (31, 32) THEN
4811 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4812 gme_debug.put_line('in process_txns for (PROD) actionID: ' || trans_rec.transaction_action_id || '...calling GMF incoming layers');
4813 END IF;
4814
4815 --Bug#6322202 Added the below condition for byproducts
4816 IF trans_rec.line_type <> 1 THEN
4817 gmf_layers.Create_outgoing_Layers
4818 ( p_api_version => 1.0,
4819 p_init_msg_list => FND_API.G_FALSE,
4820 p_tran_rec => l_trans_rec,
4821 x_return_status => x_return_status,
4822 x_msg_count => x_msg_count,
4823 x_msg_data => x_msg_data);
4824 ELSE
4825 gmf_layers.Create_Incoming_Layers
4826 ( p_api_version => 1.0,
4827 p_init_msg_list => FND_API.G_FALSE,
4828 p_tran_rec => l_trans_rec,
4829 x_return_status => x_return_status,
4830 x_msg_count => x_msg_count,
4831 x_msg_data => x_msg_data);
4832 END IF;
4833 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4834 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
4835 END IF;
4836 ELSIF trans_rec.transaction_action_id in (33, 34) THEN
4837 NULL;
4838 END IF;
4839 END LOOP;
4840 END IF;
4841 p_qty_tbl.delete();
4842 /* Bug 5903208 End GMF code */
4843
4844 IF x_return_status = 'S' THEN
4845 gme_common_pvt.g_transaction_header_id := NULL;
4846 gme_common_pvt.g_batch_status_check := fnd_api.g_true;
4847 END IF;
4848 /* end temporary */
4849
4850 IF (l_return = 0) AND (fnd_api.to_boolean (p_commit) ) THEN
4851 --empty the quantity tree cache
4852 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4853 gme_debug.put_line
4854 ( g_pkg_name
4855 || '.'
4856 || l_api_name
4857 || ':'
4858 || 'Calling Inv_Quantity_Tree_Pub.clear_quantity_cache');
4859 END IF;
4860
4861 inv_quantity_tree_pub.clear_quantity_cache;
4862 END IF;
4863
4864 gme_common_pvt.reset_txn_hdr_tbl; -- nsinghi bug#5176319
4865
4866 -- Bug 8751983 - Reset global IB timestamp to NULL.
4867 gme_common_pvt.g_ib_timestamp_set := 0;
4868
4869 IF (NVL (g_debug, 0) IN
4870 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4871 gme_debug.put_line ( g_pkg_name
4872 || '.'
4873 || l_api_name
4874 || ':'
4875 || 'Exiting with '
4876 || x_return_status);
4877 END IF;
4878
4879 EXCEPTION
4880 WHEN fnd_api.g_exc_error THEN
4881 x_return_status := fnd_api.g_ret_sts_error;
4882 fnd_msg_pub.count_and_get (p_count => x_msg_count
4883 ,p_data => x_msg_data);
4884 WHEN fnd_api.g_exc_unexpected_error THEN
4885 x_return_status := fnd_api.g_ret_sts_unexp_error;
4886
4887 IF (NVL (g_debug, 0) > 0) THEN
4888 gme_debug.put_line ( g_pkg_name
4889 || '.'
4890 || l_api_name
4891 || ':'
4892 || 'UNEXPECTED:'
4893 || SQLERRM);
4894 END IF;
4895
4896 fnd_msg_pub.count_and_get (p_count => x_msg_count
4897 ,p_data => x_msg_data);
4898 WHEN OTHERS THEN
4899 x_return_status := fnd_api.g_ret_sts_unexp_error;
4900
4901 IF (NVL (g_debug, 0) > 0) THEN
4902 gme_debug.put_line ( g_pkg_name
4903 || '.'
4904 || l_api_name
4905 || ':'
4906 || 'OTHERS:'
4907 || SQLERRM);
4908 END IF;
4909
4910 fnd_msg_pub.count_and_get (p_count => x_msg_count
4911 ,p_data => x_msg_data);
4912 END process_transactions;
4913
4914 /* +==========================================================================+
4915 | PROCEDURE NAME
4916 | query_quantities
4917 |
4918 | USAGE
4919 | Query quantities at a level specified by the input
4920 |
4921 | ARGUMENTS
4922 | p_api_version API Version of this procedure. Current version is 1.0
4923 | 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 |
4924 | x_return_status Returns the status to indicate success or failure of execution
4925 | x_msg_count Returns number of error message in the error message stack in case of failure
4926 | x_msg_data Returns the error message in case of failure
4927 |
4928 | RETURNS
4929 | returns via x_ OUT parameters
4930 |
4931 | HISTORY
4932 | Created 07-Mar-05 Jalaj Srivastava
4933 | Archana Mundhe 20-Oct-2008 Bug 7385309
4934 | Added code to clear cache before querying quantity tree.
4935 |
4936 | G. Muratore 24-Dec-2008 Bug 7626742/7423041
4937 | Backout one piece of fix from 7385309 - Do not clear the cache.
4938 +==========================================================================+ */
4939 PROCEDURE query_quantities (
4940 p_api_version_number IN NUMBER := 1
4941 ,p_init_msg_lst IN VARCHAR2
4942 DEFAULT fnd_api.g_false
4943 ,x_return_status OUT NOCOPY VARCHAR2
4944 ,x_msg_count OUT NOCOPY NUMBER
4945 ,x_msg_data OUT NOCOPY VARCHAR2
4946 ,p_organization_id IN NUMBER
4947 ,p_inventory_item_id IN NUMBER
4948 ,p_tree_mode IN INTEGER
4949 ,p_is_serial_control IN BOOLEAN DEFAULT FALSE
4950 ,p_grade_code IN VARCHAR2
4951 ,p_demand_source_type_id IN NUMBER
4952 DEFAULT gme_common_pvt.g_txn_source_type
4953 ,p_demand_source_header_id IN NUMBER DEFAULT -9999
4954 ,p_demand_source_line_id IN NUMBER DEFAULT -9999
4955 ,p_demand_source_name IN VARCHAR2 DEFAULT NULL
4956 ,p_lot_expiration_date IN DATE DEFAULT NULL
4957 ,p_revision IN VARCHAR2
4958 ,p_lot_number IN VARCHAR2
4959 ,p_subinventory_code IN VARCHAR2
4960 ,p_locator_id IN NUMBER
4961 ,p_onhand_source IN NUMBER
4962 DEFAULT inv_quantity_tree_pvt.g_all_subs
4963 ,x_qoh OUT NOCOPY NUMBER
4964 ,x_rqoh OUT NOCOPY NUMBER
4965 ,x_qr OUT NOCOPY NUMBER
4966 ,x_qs OUT NOCOPY NUMBER
4967 ,x_att OUT NOCOPY NUMBER
4968 ,x_atr OUT NOCOPY NUMBER
4969 ,x_sqoh OUT NOCOPY NUMBER
4970 ,x_srqoh OUT NOCOPY NUMBER
4971 ,x_sqr OUT NOCOPY NUMBER
4972 ,x_sqs OUT NOCOPY NUMBER
4973 ,x_satt OUT NOCOPY NUMBER
4974 ,x_satr OUT NOCOPY NUMBER
4975 ,p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
4976 ,p_cost_group_id IN NUMBER DEFAULT NULL
4977 ,p_lpn_id IN NUMBER DEFAULT NULL
4978 ,p_transfer_locator_id IN NUMBER DEFAULT NULL)
4979 IS
4980 l_api_name CONSTANT VARCHAR2 (30) := 'QUERY_QUANTITIES';
4981 l_is_revision_control BOOLEAN := FALSE;
4982 l_is_lot_control BOOLEAN := FALSE;
4983 BEGIN
4984 IF (NVL (g_debug, 0) IN
4985 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
4986 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
4987 || 'Entering');
4988 END IF;
4989
4990 IF (p_revision IS NOT NULL) THEN
4991 l_is_revision_control := TRUE;
4992 END IF;
4993
4994 IF (p_lot_number IS NOT NULL) THEN
4995 l_is_lot_control := TRUE;
4996 END IF;
4997
4998 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
4999 gme_debug.put_line ( g_pkg_name
5000 || '.'
5001 || l_api_name
5002 || ':'
5003 || 'Calling Inv_Quantity_Tree_Pub.Query_Quantities');
5004 gme_debug.put_line ( g_pkg_name
5005 || '.'
5006 || l_api_name
5007 || ':'
5008 || 'Organization ID = '
5009 || p_organization_id);
5010 gme_debug.put_line ( g_pkg_name
5011 || '.'
5012 || l_api_name
5013 || ':'
5014 || 'Inventory Item ID = '
5015 || p_inventory_item_id);
5016 gme_debug.put_line ( g_pkg_name
5017 || '.'
5018 || l_api_name
5019 || ':'
5020 || 'Subinventory = '
5021 || p_subinventory_code);
5022 gme_debug.put_line ( g_pkg_name
5023 || '.'
5024 || l_api_name
5025 || ':'
5026 || 'Lot Number = '
5027 || p_lot_number);
5028 gme_debug.put_line ( g_pkg_name
5029 || '.'
5030 || l_api_name
5031 || ':'
5032 || 'Locator ID = '
5033 || p_locator_id);
5034 gme_debug.put_line ( g_pkg_name
5035 || '.'
5036 || l_api_name
5037 || ':'
5038 || 'Item Revision = '
5039 || p_revision);
5040 gme_debug.put_line ( g_pkg_name
5041 || '.'
5042 || l_api_name
5043 || ':'
5044 || 'Tree Mode = '
5045 || p_tree_mode);
5046 gme_debug.put_line ( g_pkg_name
5047 || '.'
5048 || l_api_name
5049 || ':'
5050 || 'Grade = '
5051 || p_grade_code);
5052 gme_debug.put_line ( g_pkg_name
5053 || '.'
5054 || l_api_name
5055 || ':'
5056 || 'Lot Expiration Date = '
5057 || TO_CHAR (p_lot_expiration_date
5058 ,'MM/DD/YYYY HH24:MI:SS') );
5059 END IF;
5060
5061
5062 -- Bug 7385309
5063 -- Clear cache before querying quantity tree.
5064 -- Bug 7626742 - Backout fix from 7385309 - Do not clear the cache.
5065 -- inv_quantity_tree_pub.clear_quantity_cache;
5066 inv_quantity_tree_pub.query_quantities
5067 (p_api_version_number => p_api_version_number
5068 ,p_init_msg_lst => p_init_msg_lst
5069 ,x_return_status => x_return_status
5070 ,x_msg_count => x_msg_count
5071 ,x_msg_data => x_msg_data
5072 ,p_organization_id => p_organization_id
5073 ,p_inventory_item_id => p_inventory_item_id
5074 ,p_tree_mode => p_tree_mode
5075 ,p_is_revision_control => l_is_revision_control
5076 ,p_is_lot_control => l_is_lot_control
5077 ,p_is_serial_control => p_is_serial_control
5078 ,p_grade_code => p_grade_code
5079 ,p_demand_source_type_id => p_demand_source_type_id
5080 ,p_demand_source_header_id => p_demand_source_header_id
5081 ,p_demand_source_line_id => p_demand_source_line_id
5082 ,p_demand_source_name => p_demand_source_name
5083 ,p_lot_expiration_date => p_lot_expiration_date
5084 ,p_revision => p_revision
5085 ,p_lot_number => p_lot_number
5086 ,p_subinventory_code => p_subinventory_code
5087 ,p_locator_id => p_locator_id
5088 ,p_onhand_source => p_onhand_source
5089 ,x_qoh => x_qoh
5090 ,x_rqoh => x_rqoh
5091 ,x_qr => x_qr
5092 ,x_qs => x_qs
5093 ,x_att => x_att
5094 ,x_atr => x_atr
5095 ,x_sqoh => x_sqoh
5096 ,x_srqoh => x_srqoh
5097 ,x_sqr => x_sqr
5098 ,x_sqs => x_sqs
5099 ,x_satt => x_satt
5100 ,x_satr => x_satr
5101 ,p_transfer_subinventory_code => p_transfer_subinventory_code
5102 ,p_cost_group_id => p_cost_group_id
5103 ,p_lpn_id => p_lpn_id
5104 ,p_transfer_locator_id => p_transfer_locator_id);
5105
5106 IF (NVL (g_debug, 0) IN
5107 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
5108 gme_debug.put_line ( g_pkg_name
5109 || '.'
5110 || l_api_name
5111 || ':'
5112 || 'Exiting with '
5113 || x_return_status);
5114 END IF;
5115 EXCEPTION
5116 WHEN fnd_api.g_exc_error THEN
5117 x_return_status := fnd_api.g_ret_sts_error;
5118 fnd_msg_pub.count_and_get (p_count => x_msg_count
5119 ,p_data => x_msg_data);
5120 WHEN fnd_api.g_exc_unexpected_error THEN
5121 x_return_status := fnd_api.g_ret_sts_unexp_error;
5122
5123 IF (NVL (g_debug, 0) > 0) THEN
5124 gme_debug.put_line ( g_pkg_name
5125 || '.'
5126 || l_api_name
5127 || ':'
5128 || 'UNEXPECTED:'
5129 || SQLERRM);
5130 END IF;
5131
5132 fnd_msg_pub.count_and_get (p_count => x_msg_count
5133 ,p_data => x_msg_data);
5134 WHEN OTHERS THEN
5135 x_return_status := fnd_api.g_ret_sts_unexp_error;
5136
5137 IF (NVL (g_debug, 0) > 0) THEN
5138 gme_debug.put_line ( g_pkg_name
5139 || '.'
5140 || l_api_name
5141 || ':'
5142 || 'OTHERS:'
5143 || SQLERRM);
5144 END IF;
5145
5146 fnd_msg_pub.count_and_get (p_count => x_msg_count
5147 ,p_data => x_msg_data);
5148 END query_quantities;
5149
5150 /* +==========================================================================+
5151 | PROCEDURE NAME
5152 | update_quantities
5153 |
5154 | USAGE
5155 | Update quantity at the level specified by the input and
5156 | return the quantities at the level after the update
5157 |
5158 | ARGUMENTS
5159 | p_api_version API Version of this procedure. Current version is 1.0
5160 | 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 |
5161 | x_return_status Returns the status to indicate success or failure of execution
5162 | x_msg_count Returns number of error message in the error message stack in case of failure
5163 | x_msg_data Returns the error message in case of failure
5164 |
5165 | RETURNS
5166 | returns via x_ OUT parameters
5167 |
5168 | HISTORY
5169 | Created 07-Mar-05 Jalaj Srivastava
5170 |
5171 +==========================================================================+ */
5172 PROCEDURE update_quantities (
5173 p_api_version_number IN NUMBER := 1
5174 ,p_init_msg_lst IN VARCHAR2
5175 DEFAULT fnd_api.g_false
5176 ,x_return_status OUT NOCOPY VARCHAR2
5177 ,x_msg_count OUT NOCOPY NUMBER
5178 ,x_msg_data OUT NOCOPY VARCHAR2
5179 ,p_organization_id IN NUMBER
5180 ,p_inventory_item_id IN NUMBER
5181 ,p_tree_mode IN INTEGER
5182 ,p_is_serial_control IN BOOLEAN := FALSE
5183 ,p_demand_source_type_id IN NUMBER
5184 DEFAULT gme_common_pvt.g_txn_source_type
5185 ,p_demand_source_header_id IN NUMBER DEFAULT -9999
5186 ,p_demand_source_line_id IN NUMBER DEFAULT -9999
5187 ,p_demand_source_name IN VARCHAR2 DEFAULT NULL
5188 ,p_lot_expiration_date IN DATE DEFAULT NULL
5189 ,p_revision IN VARCHAR2 DEFAULT NULL
5190 ,p_lot_number IN VARCHAR2 DEFAULT NULL
5191 ,p_subinventory_code IN VARCHAR2 DEFAULT NULL
5192 ,p_locator_id IN NUMBER DEFAULT NULL
5193 ,p_grade_code IN VARCHAR2 DEFAULT NULL
5194 ,p_primary_quantity IN NUMBER
5195 ,p_quantity_type IN INTEGER
5196 ,p_secondary_quantity IN NUMBER
5197 ,p_onhand_source IN NUMBER
5198 DEFAULT inv_quantity_tree_pvt.g_all_subs
5199 ,x_qoh OUT NOCOPY NUMBER
5200 ,x_rqoh OUT NOCOPY NUMBER
5201 ,x_qr OUT NOCOPY NUMBER
5202 ,x_qs OUT NOCOPY NUMBER
5203 ,x_att OUT NOCOPY NUMBER
5204 ,x_atr OUT NOCOPY NUMBER
5205 ,x_sqoh OUT NOCOPY NUMBER
5206 ,x_srqoh OUT NOCOPY NUMBER
5207 ,x_sqr OUT NOCOPY NUMBER
5208 ,x_sqs OUT NOCOPY NUMBER
5209 ,x_satt OUT NOCOPY NUMBER
5210 ,x_satr OUT NOCOPY NUMBER
5211 ,p_transfer_subinventory_code IN VARCHAR2 DEFAULT NULL
5212 ,p_cost_group_id IN NUMBER DEFAULT NULL
5213 ,p_containerized IN NUMBER
5214 DEFAULT inv_quantity_tree_pvt.g_containerized_false
5215 ,p_lpn_id IN NUMBER DEFAULT NULL
5216 ,p_transfer_locator_id IN NUMBER DEFAULT NULL)
5217 IS
5218 l_api_name CONSTANT VARCHAR2 (30) := 'UPDATE_QUANTITIES';
5219 l_is_revision_control BOOLEAN := FALSE;
5220 l_is_lot_control BOOLEAN := FALSE;
5221 BEGIN
5222 IF (NVL (g_debug, 0) IN
5223 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
5224 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
5225 || 'Entering');
5226 END IF;
5227
5228 IF (p_revision IS NOT NULL) THEN
5229 l_is_revision_control := TRUE;
5230 END IF;
5231
5232 IF (p_lot_number IS NOT NULL) THEN
5233 l_is_lot_control := TRUE;
5234 END IF;
5235
5236 IF (NVL (g_debug, 0) = gme_debug.g_log_statement) THEN
5237 gme_debug.put_line
5238 ( g_pkg_name
5239 || '.'
5240 || l_api_name
5241 || ':'
5242 || 'Calling Inv_Quantity_Tree_Pub.Update_Quantities');
5243 gme_debug.put_line ( g_pkg_name
5244 || '.'
5245 || l_api_name
5246 || ':'
5247 || 'Organization ID = '
5248 || p_organization_id);
5249 gme_debug.put_line ( g_pkg_name
5250 || '.'
5251 || l_api_name
5252 || ':'
5253 || 'Inventory Item ID = '
5254 || p_inventory_item_id);
5255 gme_debug.put_line ( g_pkg_name
5256 || '.'
5257 || l_api_name
5258 || ':'
5259 || 'Subinventory = '
5260 || p_subinventory_code);
5261 gme_debug.put_line ( g_pkg_name
5262 || '.'
5263 || l_api_name
5264 || ':'
5265 || 'Lot Number = '
5266 || p_lot_number);
5267 gme_debug.put_line ( g_pkg_name
5268 || '.'
5269 || l_api_name
5270 || ':'
5271 || 'Locator ID = '
5272 || p_locator_id);
5273 gme_debug.put_line ( g_pkg_name
5274 || '.'
5275 || l_api_name
5276 || ':'
5277 || 'Item Revision = '
5278 || p_revision);
5279 gme_debug.put_line ( g_pkg_name
5280 || '.'
5281 || l_api_name
5282 || ':'
5283 || 'Tree Mode = '
5284 || p_tree_mode);
5285 gme_debug.put_line ( g_pkg_name
5286 || '.'
5287 || l_api_name
5288 || ':'
5289 || 'Grade = '
5290 || p_grade_code);
5291 gme_debug.put_line ( g_pkg_name
5292 || '.'
5293 || l_api_name
5294 || ':'
5295 || 'Lot Expiration Date = '
5296 || TO_CHAR (p_lot_expiration_date
5297 ,'MM/DD/YYYY HH24:MI:SS') );
5298 gme_debug.put_line ( g_pkg_name
5299 || '.'
5300 || l_api_name
5301 || ':'
5302 || 'Quantity Type = '
5303 || p_quantity_type);
5304 gme_debug.put_line ( g_pkg_name
5305 || '.'
5306 || l_api_name
5307 || ':'
5308 || 'Primary Quantity = '
5309 || p_primary_quantity);
5310 gme_debug.put_line ( g_pkg_name
5311 || '.'
5312 || l_api_name
5313 || ':'
5314 || 'Secondary Quantity = '
5315 || p_secondary_quantity);
5316 END IF;
5317
5318 inv_quantity_tree_pub.update_quantities
5319 (p_api_version_number => p_api_version_number
5320 ,p_init_msg_lst => p_init_msg_lst
5321 ,x_return_status => x_return_status
5322 ,x_msg_count => x_msg_count
5323 ,x_msg_data => x_msg_data
5324 ,p_organization_id => p_organization_id
5325 ,p_inventory_item_id => p_inventory_item_id
5326 ,p_tree_mode => p_tree_mode
5327 ,p_is_revision_control => l_is_revision_control
5328 ,p_is_lot_control => l_is_lot_control
5329 ,p_is_serial_control => p_is_serial_control
5330 ,p_grade_code => p_grade_code
5331 ,p_demand_source_type_id => p_demand_source_type_id
5332 ,p_demand_source_header_id => p_demand_source_header_id
5333 ,p_demand_source_line_id => p_demand_source_line_id
5334 ,p_demand_source_name => p_demand_source_name
5335 ,p_lot_expiration_date => p_lot_expiration_date
5336 ,p_revision => p_revision
5337 ,p_lot_number => p_lot_number
5338 ,p_subinventory_code => p_subinventory_code
5339 ,p_locator_id => p_locator_id
5340 ,p_onhand_source => p_onhand_source
5341 ,p_primary_quantity => p_primary_quantity
5342 ,p_quantity_type => p_quantity_type
5343 ,p_secondary_quantity => p_secondary_quantity
5344 ,x_qoh => x_qoh
5345 ,x_rqoh => x_rqoh
5346 ,x_qr => x_qr
5347 ,x_qs => x_qs
5348 ,x_att => x_att
5349 ,x_atr => x_atr
5350 ,x_sqoh => x_sqoh
5351 ,x_srqoh => x_srqoh
5352 ,x_sqr => x_sqr
5353 ,x_sqs => x_sqs
5354 ,x_satt => x_satt
5355 ,x_satr => x_satr
5356 ,p_transfer_subinventory_code => p_transfer_subinventory_code
5357 ,p_cost_group_id => p_cost_group_id
5358 ,p_lpn_id => p_lpn_id
5359 ,p_transfer_locator_id => p_transfer_locator_id
5360 ,p_containerized => p_containerized);
5361
5362 IF (NVL (g_debug, 0) IN
5363 (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
5364 gme_debug.put_line ( g_pkg_name
5365 || '.'
5366 || l_api_name
5367 || ':'
5368 || 'Exiting with '
5369 || x_return_status);
5370 END IF;
5371 EXCEPTION
5372 WHEN fnd_api.g_exc_error THEN
5373 x_return_status := fnd_api.g_ret_sts_error;
5374 fnd_msg_pub.count_and_get (p_count => x_msg_count
5375 ,p_data => x_msg_data);
5376 WHEN fnd_api.g_exc_unexpected_error THEN
5377 x_return_status := fnd_api.g_ret_sts_unexp_error;
5378
5379 IF (NVL (g_debug, 0) > 0) THEN
5380 gme_debug.put_line ( g_pkg_name
5381 || '.'
5382 || l_api_name
5383 || ':'
5384 || 'UNEXPECTED:'
5385 || SQLERRM);
5386 END IF;
5387
5388 fnd_msg_pub.count_and_get (p_count => x_msg_count
5389 ,p_data => x_msg_data);
5390 WHEN OTHERS THEN
5391 x_return_status := fnd_api.g_ret_sts_unexp_error;
5392
5393 IF (NVL (g_debug, 0) > 0) THEN
5394 gme_debug.put_line ( g_pkg_name
5395 || '.'
5396 || l_api_name
5397 || ':'
5398 || 'OTHERS:'
5399 || SQLERRM);
5400 END IF;
5401
5402 fnd_msg_pub.count_and_get (p_count => x_msg_count
5403 ,p_data => x_msg_data);
5404 END update_quantities;
5405
5406 /* Bug 4929610 Added fucntion */
5407 /* +==========================================================================+
5408 | FUNCTION NAME
5409 | is_lot_expired
5410 |
5411 | USAGE
5412 |
5413 |
5414 | ARGUMENTS
5415 | p_organization_id
5416 | p_lot_number
5417 | p_inventory_item_id
5418 | p_date
5419 | RETURNS
5420 | returns BOOLEAN, TRUE if lot expired
5421 |
5422 | HISTORY
5423 | Created 16-Feb-06 Chandrashekar Tiruvidula
5424 |
5425 +==========================================================================+ */
5426 FUNCTION is_lot_expired (p_organization_id IN NUMBER,
5427 p_inventory_item_id IN NUMBER,
5428 p_lot_number IN VARCHAR2,
5429 p_date IN DATE) RETURN BOOLEAN IS
5430 l_expire_date DATE;
5431 l_api_name CONSTANT VARCHAR2 (30) := 'is_lot_expired';
5432 CURSOR Cur_lot_expire IS
5433 SELECT expiration_date
5434 FROM mtl_lot_numbers
5435 WHERE organization_id = p_organization_id
5436 AND inventory_item_id = p_inventory_item_id
5437 AND lot_number = p_lot_number;
5438 BEGIN
5439 OPEN Cur_lot_expire;
5440 FETCH Cur_lot_expire INTO l_expire_date;
5441 CLOSE Cur_lot_expire;
5442 IF l_expire_date IS NULL THEN
5443 RETURN FALSE;
5444 ELSE
5445 IF l_expire_date < NVL(p_date, SYSDATE) THEN
5446 gme_common_pvt.log_message(p_product_code => 'INV', p_message_code => 'INV_LOT_EXPIRED');
5447 RETURN TRUE;
5448 END IF;
5449 END IF;
5450 RETURN FALSE;
5451 EXCEPTION
5452 WHEN OTHERS THEN
5453 IF (NVL (g_debug, 0) > 0) THEN
5454 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5455 END IF;
5456 RETURN FALSE;
5457 END is_lot_expired;
5458
5459 PROCEDURE insert_txn_inter_hdr(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
5460 x_return_status OUT NOCOPY VARCHAR2) IS
5461 l_api_name CONSTANT VARCHAR2 (30) := 'insert_txn_inter_hdr';
5462 BEGIN
5463 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
5464 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Entering');
5465 END IF;
5466 x_return_status := fnd_api.g_ret_sts_success;
5467 INSERT INTO mtl_transactions_interface
5468 (transaction_interface_id
5469 ,transaction_header_id
5470 ,source_code
5471 ,source_header_id
5472 ,lock_flag
5473 ,transaction_mode
5474 ,process_flag
5475 ,validation_required
5476 ,source_line_id
5477 ,transaction_source_id
5478 ,trx_source_line_id
5479 ,last_updated_by
5480 ,last_update_login
5481 ,last_update_date
5482 ,creation_date
5483 ,created_by
5484 ,inventory_item_id
5485 ,revision
5486 ,organization_id
5487 ,transaction_date
5488 ,transaction_type_id
5489 ,transaction_action_id
5490 ,transaction_quantity
5491 ,primary_quantity
5492 ,secondary_transaction_quantity
5493 ,secondary_uom_code
5494 ,transaction_uom
5495 ,subinventory_code
5496 ,locator_id
5497 ,transaction_source_type_id
5498 ,wip_entity_type
5499 ,transaction_source_name
5500 ,transaction_reference
5501 ,reason_id
5502 ,transaction_batch_id
5503 ,transaction_batch_seq
5504 ,reservation_quantity
5505 ,transaction_sequence_id
5506 ,attribute1
5507 ,attribute2
5508 ,attribute3
5509 ,attribute4
5510 ,attribute5
5511 ,attribute6
5512 ,attribute7
5513 ,attribute8
5514 ,attribute9
5515 ,attribute10
5516 ,attribute11
5517 ,attribute12
5518 ,attribute13
5519 ,attribute14
5520 ,attribute15
5521 ,attribute_category -- Bug 12881196 Add missing attribute columns so that they get saved
5522 ,transfer_lpn_id
5523 ,lpn_id) -- Bug 6437252 LPN support
5524 VALUES (p_mmti_rec.transaction_interface_id
5525 ,gme_common_pvt.g_transaction_header_id
5526 ,'OPM' -- source_code
5527 ,p_mmti_rec.transaction_source_id --source_header_id
5528 ,1 -- lock_flag
5529 ,2 -- transaction_mode
5530 ,1 -- (Yes) process_flag
5531 ,2 -- validation_required
5532 , NVL (p_mmti_rec.source_line_id, -99)-- transaction_id for reversal
5533 ,p_mmti_rec.transaction_source_id -- batch id
5534 ,p_mmti_rec.trx_source_line_id -- material detail id
5535 ,gme_common_pvt.g_user_ident --last_updated_by
5536 ,gme_common_pvt.g_user_ident -- last_update_login
5537 ,gme_common_pvt.g_timestamp --last_update_date
5538 ,gme_common_pvt.g_timestamp --creation_date
5539 ,gme_common_pvt.g_user_ident --created_by
5540 ,p_mmti_rec.inventory_item_id -- inventory_item_id
5541 ,p_mmti_rec.revision
5542 ,p_mmti_rec.organization_id --organization_id
5543 /* FPBug#4543872 rework
5544 removed defaulting the transaction date
5545 */
5546 ,p_mmti_rec.transaction_date
5547 ,p_mmti_rec.transaction_type_id
5548 , --(Batch Issue)transaction_type_id
5549 p_mmti_rec.transaction_action_id
5550 , --transaction_action_id
5551 p_mmti_rec.transaction_quantity
5552 , --transaction_quantity
5553 p_mmti_rec.primary_quantity
5554 , --primary_quantity
5555 p_mmti_rec.secondary_transaction_quantity -- secondary_quantity
5556 ,p_mmti_rec.secondary_uom_code -- secondary_uom_code
5557 , -- secondary_quantity
5558 p_mmti_rec.transaction_uom, --transaction_uom
5559 p_mmti_rec.subinventory_code
5560 , --subinventory_code
5561 p_mmti_rec.locator_id, --locator_id
5562 gme_common_pvt.g_txn_source_type
5563 , -- (Batch) transaction_source_type_id
5564 gme_common_pvt.g_wip_entity_type_batch -- (for batch) wip_entity_type
5565 ,p_mmti_rec.transaction_source_name -- transaction_source_name
5566 ,p_mmti_rec.transaction_reference
5567 ,p_mmti_rec.reason_id
5568 ,p_mmti_rec.transaction_batch_id -- must populate for seq
5569 ,p_mmti_rec.transaction_batch_seq
5570 ,p_mmti_rec.reservation_quantity
5571 ,p_mmti_rec.transaction_sequence_id
5572 ,p_mmti_rec.attribute1
5573 ,p_mmti_rec.attribute2
5574 ,p_mmti_rec.attribute3
5575 ,p_mmti_rec.attribute4
5576 ,p_mmti_rec.attribute5
5577 ,p_mmti_rec.attribute6
5578 ,p_mmti_rec.attribute7
5579 ,p_mmti_rec.attribute8
5580 ,p_mmti_rec.attribute9
5581 ,p_mmti_rec.attribute10
5582 ,p_mmti_rec.attribute11
5583 ,p_mmti_rec.attribute12
5584 ,p_mmti_rec.attribute13
5585 ,p_mmti_rec.attribute14
5586 ,p_mmti_rec.attribute15
5587 ,p_mmti_rec.attribute_category -- Bug 12881196 Add missing attribute columns so that they get saved.
5588 ,p_mmti_rec.transfer_lpn_id
5589 ,p_mmti_rec.lpn_id); -- Bug 6437252 LPN support
5590 IF (NVL (g_debug, 0) IN (gme_debug.g_log_statement, gme_debug.g_log_procedure) ) THEN
5591 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':' || 'Exiting');
5592 END IF;
5593 EXCEPTION
5594 WHEN OTHERS THEN
5595 x_return_status := fnd_api.g_ret_sts_unexp_error;
5596 IF (NVL (g_debug, 0) > 0) THEN
5597 gme_debug.put_line (g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5598 END IF;
5599 END insert_txn_inter_hdr;
5600
5601 -- nsinghi bug#5176319. Added this proc.
5602 /* +==========================================================================+
5603 | PROCEDURE NAME
5604 | get_mmt_transactions
5605 |
5606 | USAGE
5607 | Gets all transactions from mmt based on transaction_id passed. Unlike get_transactions,
5608 | this procedure does not check for enteries in gme_transaction_pairs
5609 |
5610 | ARGUMENTS
5611 | p_transaction_id -- transaction_id from mmt for fetch
5612 |
5613 | RETURNS
5614 |
5615 | returns via x_status OUT parameters
5616 | x_mmt_rec -- mtl_material_transactions rowtype
5617 | x_mmln_tbl -- table of mtl_trans_lots_number_tbl
5618 | HISTORY
5619 | Created 19-Jun-06 Namit S. Created
5620 |
5621 +==========================================================================+ */
5622 PROCEDURE get_mmt_transactions (
5623 p_transaction_id IN NUMBER
5624 ,x_mmt_rec OUT NOCOPY mtl_material_transactions%ROWTYPE
5625 ,x_mmln_tbl OUT NOCOPY gme_common_pvt.mtl_trans_lots_num_tbl
5626 ,x_return_status OUT NOCOPY VARCHAR2)
5627 IS
5628
5629 CURSOR cur_get_transaction (v_transaction_id NUMBER)
5630 IS
5631 SELECT *
5632 FROM mtl_material_transactions mmt
5633 WHERE transaction_id = v_transaction_id;
5634
5635 CURSOR cur_get_lot_transaction (v_transaction_id NUMBER)
5636 IS
5637 SELECT *
5638 FROM mtl_transaction_lot_numbers
5639 WHERE transaction_id = v_transaction_id;
5640
5641 l_api_name CONSTANT VARCHAR2 (30) := 'GET_MMT_TRANSACTIONS';
5642 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
5643 l_transaction_id NUMBER;
5644 no_transaction_found EXCEPTION;
5645
5646 BEGIN
5647 x_return_status := fnd_api.g_ret_sts_success;
5648
5649 IF (g_debug <= gme_debug.g_log_statement) THEN
5650 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
5651 || 'Entering with transaction '||p_transaction_id);
5652 END IF;
5653
5654 IF p_transaction_id IS NULL THEN
5655 gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
5656
5657 IF (g_debug <= gme_debug.g_log_statement) THEN
5658 gme_debug.put_line ( g_pkg_name
5659 || '.'
5660 || l_api_name
5661 || ':'
5662 || 'TRANSACTION ID NEEDED FOR RETRIEVAL');
5663 END IF;
5664 END IF;
5665
5666 l_transaction_id := p_transaction_id;
5667 OPEN cur_get_transaction (l_transaction_id);
5668 FETCH cur_get_transaction
5669 INTO x_mmt_rec;
5670 IF cur_get_transaction%FOUND THEN
5671 IF (g_debug <= gme_debug.g_log_statement) THEN
5672 gme_debug.put_line ( g_pkg_name
5673 || '.'
5674 || l_api_name
5675 || ':'
5676 || 'TRANSACTIONS found for '
5677 || l_transaction_id);
5678 END IF;
5679 get_lot_trans (p_transaction_id => l_transaction_id
5680 ,x_mmln_tbl => x_mmln_tbl
5681 ,x_return_status => l_return_status);
5682
5683 IF l_return_status <> fnd_api.g_ret_sts_success THEN
5684 IF (g_debug <= gme_debug.g_log_statement) THEN
5685 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'
5686 || 'error from get lot trans');
5687 END IF;
5688 RAISE fnd_api.g_exc_error;
5689 END IF;
5690 ELSE /* IF cur_get_transaction%FOUND THEN */
5691 CLOSE cur_get_transaction;
5692 gme_common_pvt.log_message ('GME_NO_TRANS_FOUND');
5693 RAISE fnd_api.g_exc_error;
5694 END IF;
5695 CLOSE cur_get_transaction;
5696
5697 IF (g_debug <= gme_debug.g_log_statement) THEN
5698 gme_debug.put_line ( g_pkg_name
5699 || '.'
5700 || l_api_name
5701 || ':'
5702 || 'TRANSACTION '
5703 || x_mmt_rec.transaction_id);
5704 END IF;
5705
5706 IF (g_debug <= gme_debug.g_log_statement) THEN
5707 gme_debug.put_line ( g_pkg_name
5708 || '.'
5709 || l_api_name
5710 || ':'
5711 || 'Exiting with '
5712 || x_return_status);
5713 END IF;
5714 EXCEPTION
5715 WHEN fnd_api.g_exc_error THEN
5716 x_return_status := fnd_api.g_ret_sts_error;
5717 WHEN fnd_api.g_exc_unexpected_error THEN
5718 x_return_status := fnd_api.g_ret_sts_unexp_error;
5719 WHEN OTHERS THEN
5720 x_return_status := fnd_api.g_ret_sts_unexp_error;
5721 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5722
5723 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5724 gme_debug.put_line ( g_pkg_name
5725 || '.'
5726 || l_api_name
5727 || ':'
5728 || 'WHEN OTHERS:'
5729 || SQLERRM);
5730 END IF;
5731 END get_mmt_transactions;
5732
5733 /* Bug 5358129 Added procedure */
5734 /* Bug 15879394 Removed check for existence of lot */
5735 PROCEDURE validate_lot_for_ing(p_organization_id IN NUMBER,
5736 p_inventory_item_id IN NUMBER,
5737 p_lot_number IN VARCHAR2,
5738 x_return_status OUT NOCOPY VARCHAR2) IS
5739 CURSOR Cur_get_lot IS
5740 SELECT expiration_date
5741 FROM mtl_lot_numbers
5742 WHERE organization_id = p_organization_id
5743 AND inventory_item_id = p_inventory_item_id
5744 AND lot_number = p_lot_number;
5745 l_api_name CONSTANT VARCHAR2(30) := 'validate_lot_for_ing';
5746 l_date DATE := NULL ; /* Bug 15879394 Initialized the variable */
5747 expired_lot EXCEPTION;
5748 invalid_lot EXCEPTION;
5749 BEGIN
5750 IF (g_debug <= gme_debug.g_log_procedure) THEN
5751 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Entering with organization_id = '||p_organization_id
5752 ||' inventory_item_id = '||p_inventory_item_id||' lot_number = '||p_lot_number);
5753 END IF;
5754 x_return_status := FND_API.G_RET_STS_SUCCESS;
5755 OPEN Cur_get_lot;
5756 FETCH Cur_get_lot INTO l_date;
5757 /* Bug 15879394 Removed the erroring out */
5758 /*
5759 IF (Cur_get_lot%NOTFOUND) THEN
5760 CLOSE Cur_get_lot;
5761 RAISE invalid_lot;
5762 END IF;
5763 */
5764 CLOSE Cur_get_lot;
5765 IF (l_date IS NOT NULL AND l_date < sysdate) THEN
5766 RAISE expired_lot;
5767 END IF;
5768 IF (g_debug <= gme_debug.g_log_procedure) THEN
5769 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Normal Exiting');
5770 END IF;
5771 EXCEPTION
5772 WHEN expired_lot THEN
5773 gme_common_pvt.log_message(p_message_code => 'INV_LOT_EXPIRED',
5774 p_product_code => 'INV');
5775 x_return_status := fnd_api.g_ret_sts_error;
5776 WHEN invalid_lot THEN
5777 x_return_status := fnd_api.g_ret_sts_unexp_error;
5778 gme_common_pvt.log_message(p_message_code => 'INV_INVALID_LOT',
5779 p_product_code => 'INV');
5780 WHEN OTHERS THEN
5781 x_return_status := fnd_api.g_ret_sts_unexp_error;
5782 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5783 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5784 gme_debug.put_line ( g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5785 END IF;
5786 END validate_lot_for_ing;
5787
5788 /* Added for bug 5597385 */
5789 PROCEDURE gmo_pre_process_val(p_mmti_rec IN mtl_transactions_interface%ROWTYPE,
5790 p_mmli_tbl IN gme_common_pvt.mtl_trans_lots_inter_tbl,
5791 p_mode IN VARCHAR2,
5792 x_return_status OUT NOCOPY VARCHAR2) IS
5793 CURSOR cur_get_item_rec (v_item_id NUMBER, v_org_id NUMBER) IS
5794 SELECT *
5795 FROM mtl_system_items_b
5796 WHERE inventory_item_id = v_item_id AND organization_id = v_org_id;
5797 CURSOR Cur_associated_step(v_matl_dtl_id NUMBER) IS
5798 SELECT step_status
5799 FROM gme_batch_steps s, gme_batch_step_items i
5800 WHERE s.batchstep_id = i.batchstep_id
5801 AND i.material_detail_id = v_matl_dtl_id;
5802 l_mat_dtl_rec gme_material_details%ROWTYPE;
5803 l_batch_hdr_rec gme_batch_header%ROWTYPE;
5804 l_item_rec mtl_system_items_b%ROWTYPE;
5805 l_available_qty NUMBER;
5806 l_step_status NUMBER;
5807 l_rel_type NUMBER;
5808 l_return_status VARCHAR2(1);
5809 item_not_found EXCEPTION;
5810 not_valid_trans EXCEPTION;
5811 lot_val_err EXCEPTION;
5812 l_api_name CONSTANT VARCHAR2(30) := 'GMO_PRE_PROCESS_VAL';
5813 BEGIN
5814 -- Initially let us assign the return status to success
5815 x_return_status := fnd_api.g_ret_sts_success;
5816 IF (g_debug <= gme_debug.g_log_statement) THEN
5817 gme_debug.put_line (g_pkg_name || '.' || l_api_name || ':'|| 'Entering');
5818 END IF;
5819 IF (g_debug <= gme_debug.g_log_statement) THEN
5820 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_header_id: '||p_mmti_rec.transaction_header_id);
5821 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.subinventory_code: '||p_mmti_rec.subinventory_code);
5822 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_uom: '||p_mmti_rec.transaction_uom);
5823 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.inventory_item_id: '||p_mmti_rec.inventory_item_id);
5824 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.trx_source_line_id: '||p_mmti_rec.trx_source_line_id);
5825 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.revision: '||p_mmti_rec.revision);
5826 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_source_id: '||p_mmti_rec.transaction_source_id);
5827 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.locator_id: '||p_mmti_rec.locator_id);
5828 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'p_mmti_rec.transaction_type_id: '||p_mmti_rec.transaction_type_id);
5829 END IF;
5830 IF p_mmti_rec.transaction_source_id IS NOT NULL THEN
5831 l_batch_hdr_rec.batch_id := p_mmti_rec.transaction_source_id;
5832 IF NOT gme_batch_header_dbl.fetch_row(l_batch_hdr_rec, l_batch_hdr_rec) THEN
5833 RAISE fnd_api.g_exc_error;
5834 END IF;
5835 ELSE
5836 RAISE fnd_api.g_exc_error;
5837 END IF; -- transaction_source_id IS NOT NULL
5838
5839 IF l_batch_hdr_rec.update_inventory_ind = 'Y' THEN
5840 IF p_mmti_rec.trx_source_line_id IS NOT NULL THEN
5841 l_mat_dtl_rec.material_detail_id := p_mmti_rec.trx_source_line_id;
5842 IF NOT gme_material_details_dbl.fetch_row(l_mat_dtl_rec, l_mat_dtl_rec) THEN
5843 RAISE fnd_api.g_exc_error;
5844 END IF; -- material fetch
5845 ELSE
5846 RAISE fnd_api.g_exc_error;
5847 END IF; -- trx_source_line_id IS NOT NULL
5848 IF gme_common_pvt.g_batch_status_check = fnd_api.g_true THEN
5849 IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
5850 gme_common_pvt.log_message ('GME_INVALID_BATCH_STATUS');
5851 RAISE fnd_api.g_exc_error;
5852 END IF;
5853 -- Check for step status in case the item is associated to a step.
5854 l_rel_type := gme_common_pvt.is_material_auto_release(l_mat_dtl_rec.material_detail_id);
5855 IF (l_rel_type = gme_common_pvt.g_mtl_autobystep_release) THEN
5856 OPEN Cur_associated_step(l_mat_dtl_rec.material_detail_id);
5857 FETCH Cur_associated_step INTO l_step_status;
5858 CLOSE Cur_associated_step;
5859 IF l_step_status NOT IN (2,3) THEN
5860 gme_common_pvt.log_message ('GME_API_INVALID_STEP_STATUS');
5861 RAISE fnd_api.g_exc_error;
5862 END IF;
5863 END IF; -- IF ( l_rel_type = gme_common_pvt.g_mtl_autobystep_release ) THEN
5864 -- check for item release type for products
5865 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
5866 -- Bug 9626176 -- Allow transactions in wip batch for auto release prods.
5867 IF l_batch_hdr_rec.batch_status NOT IN (2, 3) THEN
5868 -- IF l_batch_hdr_rec.batch_status <> 3 THEN
5869 gme_common_pvt.log_message('GME_INVALID_BATCH_STATUS');
5870 RAISE fnd_api.g_exc_error;
5871 END IF;
5872 END IF;
5873 END IF; -- gme_common_pvt.g_batch_status_check
5874 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))
5875 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))
5876 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
5877 gme_common_pvt.log_message ('GME_LINE_TYPE_TXN_TYPE_DIFF');
5878 RAISE fnd_api.g_exc_error;
5879 END IF;
5880 -- get the item propertites
5881 OPEN cur_get_item_rec (p_mmti_rec.inventory_item_id, p_mmti_rec.organization_id);
5882 FETCH cur_get_item_rec INTO l_item_rec;
5883 IF cur_get_item_rec%NOTFOUND THEN
5884 CLOSE cur_get_item_rec;
5885 gme_common_pvt.log_message ('PM_INVALID_ITEM');
5886 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5887 gme_debug.put_line('Item cursor fetch no record in mtl_system_items_b: ');
5888 gme_debug.put_line('inventory_item_id = '|| TO_CHAR (p_mmti_rec.inventory_item_id));
5889 gme_debug.put_line('organization_id = '|| TO_CHAR (p_mmti_rec.organization_id));
5890 END IF;
5891 RAISE item_not_found;
5892 END IF;
5893 CLOSE cur_get_item_rec;
5894 IF (g_debug <= gme_debug.g_log_statement) THEN
5895 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item lot_control Code: '|| l_item_rec.lot_control_code);
5896 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item loct_control Code: '|| l_item_rec.location_control_code);
5897 END IF;
5898
5899 /* Bug 5358129 for ingredients lots should exist */
5900 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
5901 FOR i IN 1..p_mmli_tbl.COUNT LOOP
5902 gme_transactions_pvt.validate_lot_for_ing(p_organization_id => p_mmti_rec.organization_id,
5903 p_inventory_item_id => p_mmti_rec.inventory_item_id,
5904 p_lot_number => p_mmli_tbl(i).lot_number,
5905 x_return_status => l_return_status);
5906 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
5907 RAISE lot_val_err;
5908 END IF;
5909 END LOOP;
5910 END IF;
5911 -- if return transaction then check qty was issued and return not more than issued qty
5912 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
5913 IF (g_debug <= gme_debug.g_log_statement) THEN
5914 gme_debug.put_line(g_pkg_name||'.'||l_api_name||':'||'Return transaction for : '||p_mmti_rec.transaction_type_id);
5915 END IF;
5916 IF l_item_rec.lot_control_code = 1 THEN
5917 IF (g_debug <= gme_debug.g_log_statement) THEN
5918 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item is NOT lot_control: '|| l_item_rec.lot_control_code);
5919 END IF;
5920 get_returnable_qty(p_mmti_rec => p_mmti_rec
5921 ,p_lot_number => NULL
5922 ,p_lot_control => l_item_rec.lot_control_code
5923 ,x_available_qty => l_available_qty
5924 ,x_return_status => x_return_status);
5925 IF x_return_status <> fnd_api.g_ret_sts_success THEN
5926 RAISE fnd_api.g_exc_unexpected_error;
5927 END IF;
5928 IF (g_debug <= gme_debug.g_log_statement) THEN
5929 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning Qty '|| p_mmti_rec.transaction_quantity);
5930 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
5931 END IF;
5932 IF ABS (l_available_qty) < ABS (p_mmti_rec.transaction_quantity) THEN
5933 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
5934 RAISE fnd_api.g_exc_error;
5935 END IF;
5936 ELSE /* Lot Control */
5937 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5938 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Item is lot control: '|| l_item_rec.lot_control_code);
5939 END IF;
5940 FOR i IN 1..p_mmli_tbl.COUNT LOOP
5941 get_returnable_qty(p_mmti_rec => p_mmti_rec
5942 ,p_lot_number => p_mmli_tbl(i).lot_number
5943 ,p_lot_control => l_item_rec.lot_control_code
5944 ,x_available_qty => l_available_qty
5945 ,x_return_status => x_return_status);
5946 IF x_return_Status <> fnd_api.g_ret_sts_success THEN
5947 RAISE fnd_api.g_exc_unexpected_error;
5948 END IF;
5949 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
5950 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Returning qty: '|| p_mmli_tbl(i).transaction_quantity);
5951 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Available to Return '|| l_available_qty);
5952 END IF;
5953 IF ABS(l_available_qty) < ABS(p_mmli_tbl(i).transaction_quantity) THEN
5954 gme_common_pvt.log_message ('GME_QTY_LESS_THEN_ISSUED');
5955 RAISE fnd_api.g_exc_error;
5956 END IF;
5957 END LOOP;
5958 END IF; /* IF l_item_rec.lot_control_code = 1 THEN */
5959 END IF; /* IF transaction_type_id in RETURNS */
5960 END IF; /* update_inventory_ind = 'Y' */
5961 IF (g_debug <= gme_debug.g_log_statement) THEN
5962 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'Exiting with '|| x_return_status);
5963 END IF;
5964 EXCEPTION
5965 WHEN lot_val_err THEN
5966 x_return_status := l_return_status;
5967 WHEN fnd_api.g_exc_error THEN
5968 x_return_status := fnd_api.g_ret_sts_error;
5969 WHEN fnd_api.g_exc_unexpected_error THEN
5970 x_return_status := fnd_api.g_ret_sts_unexp_error;
5971 WHEN OTHERS THEN
5972 x_return_status := fnd_api.g_ret_sts_unexp_error;
5973 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
5974 IF (NVL (g_debug, -1) = gme_debug.g_log_unexpected) THEN
5975 gme_debug.put_line(g_pkg_name|| '.'|| l_api_name|| ':'|| 'WHEN OTHERS:'|| SQLERRM);
5976 END IF;
5977 END gmo_pre_process_val;
5978 END gme_transactions_pvt;
5979