[Home] [Help]
PACKAGE BODY: APPS.GME_TRANS_ENGINE_UTIL
Source
1 PACKAGE BODY gme_trans_engine_util AS
2 /* $Header: GMEUTXNB.pls 120.6 2005/10/17 14:15:58 pxkumar noship $
3
4 *****************************************************************
5 * *
6 * Package GME_TRANS_ENGINE_UTIL *
7 * *
8 * Contents LOAD_MAT_AND_RSC_TRANS *
9 * Contents BUILD_TRANS_REC *
10 * *
11 * Use This is the UTIL layer of the GME Inventory *
12 * Transaction Loading *
13 * *
14 * History *
15 *****************************************************************
16 */
17 /* Global variables */
18 g_pkg_name CONSTANT VARCHAR2 (30) := 'GME_TRANS_ENGINE_UTIL';
19 g_debug VARCHAR2 (5) := fnd_profile.VALUE ('AFLOG_LEVEL');
20
21 PROCEDURE load_mat_and_rsc_trans (
22 p_batch_row IN gme_batch_header%ROWTYPE
23 ,x_mat_row_count OUT NOCOPY NUMBER
24 ,x_rsc_row_count OUT NOCOPY NUMBER
25 ,x_return_status OUT NOCOPY VARCHAR2)
26 IS
27 CURSOR c_get_init_reversal (v_doc_id NUMBER)
28 IS
29 SELECT *
30 FROM gme_inventory_txns_gtmp
31 -- WHERE ACTION_CODE ='REVL'; -- Should this be indexed.
32 WHERE transaction_no = 2 -- Should this be indexed.
33 AND trans_qty <>
34 0
35 -- these are already matched up... don't match them again.
36 AND doc_id = v_doc_id
37 ORDER BY line_type
38 ,item_id
39 ,material_detail_id
40 ,whse_code
41 ,lot_id
42 ,LOCATION
43 ,completed_ind
44 ,trans_id;
45
46 --BUG#3528006 Added cursor c_get_match_reversal_date_cmp
47 CURSOR c_get_match_reversal_date_cmp (
48 v_doc_id NUMBER
49 ,v_detail_id NUMBER
50 ,v_line_type NUMBER
51 ,v_item_id NUMBER
52 ,v_whse_code VARCHAR2
53 ,v_lot_id NUMBER
54 ,v_location VARCHAR2
55 ,v_completed_ind NUMBER
56 ,v_qty NUMBER
57 ,v_trans_date DATE)
58 IS
59 SELECT *
60 FROM gme_inventory_txns_gtmp
61 WHERE transaction_no <> 2 -- Should this be indexed.
62 AND doc_id = v_doc_id
63 AND line_type = v_line_type
64 AND item_id = v_item_id
65 AND material_detail_id = v_detail_id
66 AND whse_code = v_whse_code
67 AND lot_id = v_lot_id
68 AND LOCATION = v_location
69 AND completed_ind = v_completed_ind
70 AND trans_date = v_trans_date
71 AND ABS (trans_qty) = v_qty;
72
73 --BUG#3528006 Modified cursor c_get_match_reversal
74 CURSOR c_get_match_reversal (
75 v_doc_id NUMBER
76 ,v_detail_id NUMBER
77 ,v_line_type NUMBER
78 ,v_item_id NUMBER
79 ,v_whse_code VARCHAR2
80 ,v_lot_id NUMBER
81 ,v_location VARCHAR2
82 ,v_completed_ind NUMBER
83 ,v_qty NUMBER)
84 IS
85 SELECT *
86 FROM gme_inventory_txns_gtmp
87 WHERE transaction_no <> 2 -- Should this be indexed.
88 AND doc_id = v_doc_id
89 AND line_type = v_line_type
90 AND item_id = v_item_id
91 AND material_detail_id = v_detail_id
92 AND whse_code = v_whse_code
93 AND lot_id = v_lot_id
94 AND LOCATION = v_location
95 AND completed_ind = v_completed_ind
96 AND ABS (trans_qty) = v_qty;
97
98 CURSOR c_get_cmplt_zero_def_txns
99 IS
100 SELECT *
101 FROM gme_inventory_txns_gtmp
102 WHERE completed_ind = 1 AND trans_qty = 0
103 ORDER BY line_type
104 ,item_id
105 ,material_detail_id
106 ,whse_code
107 ,lot_id
108 ,LOCATION
109 ,completed_ind
110 ,trans_id;
111
112 CURSOR c_check_mat_transactions (
113 p_batch_id IN NUMBER
114 ,p_batch_type IN VARCHAR2)
115 IS
116 SELECT COUNT (1)
117 FROM gme_inventory_txns_gtmp
118 WHERE doc_id = p_batch_id AND doc_type = p_batch_type;
119
120 l_last_txn c_get_cmplt_zero_def_txns%ROWTYPE;
121 l_current_txn c_get_cmplt_zero_def_txns%ROWTYPE;
122 init_revs c_get_init_reversal%ROWTYPE;
123 match_revs c_get_match_reversal%ROWTYPE;
124 l_trans_no gme_inventory_txns_gtmp.transaction_no%TYPE;
125 l_api_name CONSTANT VARCHAR2 (30) := 'LOAD_MAT_AND_RSC_TRANS';
126 l_mat_row_count NUMBER := 0;
127 l_rsc_row_count NUMBER := 0;
128 l_inv_exists NUMBER := 0;
129 l_doc_type VARCHAR2 (4);
130 l_return_status VARCHAR2 (1) := fnd_api.g_ret_sts_success;
131 l_match_rev_id NUMBER;
132 BEGIN
133 x_return_status := fnd_api.g_ret_sts_success;
134
135 /* Check that we have at least a BATCH ID */
136 IF ( (p_batch_row.batch_id IS NULL)
137 OR (p_batch_row.batch_id = fnd_api.g_miss_num) ) THEN
138 gme_common_pvt.log_message ('GME_NO_KEYS', 'TABLE_NAME', l_api_name);
139
140 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
141 gme_debug.put_line ( g_pkg_name
142 || '.'
143 || l_api_name
144 || ':'
145 || 'BATCH ID NEEDED FOR RETRIEVAL');
146 END IF;
147 END IF;
148
149 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
150 gme_debug.put_line ( g_pkg_name
151 || '.'
152 || l_api_name
153 || ':'
154 || 'Update Inventory Ind:'
155 || p_batch_row.update_inventory_ind
156 || ' Batch Id:'
157 || p_batch_row.batch_id);
158 END IF;
159
160 -- Check that the UPDATE_INVENTORY_IND Value
161 IF (p_batch_row.update_inventory_ind <> 'Y') THEN
162 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
163 gme_debug.put_line ( g_pkg_name
164 || '.'
165 || l_api_name
166 || ':'
167 || 'No Transactions will be loaded');
168 END IF;
169
170 gme_common_pvt.log_message ('GME_BATCH_NON_INVENTORY'
171 ,'BATCH_NO'
172 ,p_batch_row.batch_no);
173 RAISE fnd_api.g_exc_error;
174 END IF;
175
176 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
177 gme_debug.put_line ( g_pkg_name
178 || '.'
179 || l_api_name
180 || ':'
181 || 'Batch Type = > '
182 || p_batch_row.batch_type);
183 END IF;
184
185 -- Detemine Transactional Doc Type
186 -- 0 - PROD 10 - FPO
187 IF (p_batch_row.batch_type = 0) THEN
188 l_doc_type := 'PROD';
189 ELSIF (p_batch_row.batch_type = 10) THEN
190 l_doc_type := 'FPO';
191 ELSE
192 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
193 gme_debug.put_line ( g_pkg_name
194 || '.'
195 || l_api_name
196 || ':'
197 || 'No BATCH TYPE loaded');
198 END IF;
199
200 gme_common_pvt.log_message ('INPUT_PARMS_MISS', 'PROC', l_api_name);
201 RAISE fnd_api.g_exc_error;
202 END IF;
203
204 /* Now Validate Transactions */
205 /* Have Been Loaded */
206
207 -- Check if values already exist in Table
208 OPEN c_check_mat_transactions (p_batch_row.batch_id, l_doc_type);
209
210 FETCH c_check_mat_transactions
211 INTO l_inv_exists;
212
213 CLOSE c_check_mat_transactions;
214
215 IF (l_inv_exists > 0) THEN -- We have Alreay Loaded INV Batch Data
216 l_mat_row_count := l_inv_exists;
217 ELSE -- Now Populate The GME_INVENTORY_TXNS_GTMP table
218 -- Should this be in same file as other table routines */
219
220 INSERT INTO gme_inventory_txns_gtmp
221 (trans_id,
222 item_id,
223 co_code,
224 orgn_code,
225 whse_code,
226 lot_id,
227 location,
228 doc_id,
229 doc_type,
230 doc_line,
231 line_type,
232 reason_code,
233 trans_date,
234 trans_qty,
235 trans_qty2,
236 qc_grade,
237 lot_status,
238 trans_stat,
239 trans_um,
240 trans_um2,
241 completed_ind,
242 staged_ind,
243 gl_posted_ind,
244 event_id,
245 delete_mark,
246 text_code,
247 action_code,
248 material_detail_id,
249 transaction_no,
250 def_trans_ind,
251 organization_id,
252 locator_id,
253 subinventory,
254 alloc_um,
255 alloc_qty
256 )
257 SELECT i.trans_id,
258 i.item_id,
259 i.co_code,
260 i.orgn_code,
261 i.whse_code,
262 i.lot_id,
263 i.location,
264 i.doc_id,
265 i.doc_type,
266 i.doc_line,
267 i.line_type,
268 i.reason_code,
269 i.trans_date,
270 i.trans_qty,
271 i.trans_qty2,
272 i.qc_grade,
273 i.lot_status,
274 i.trans_stat,
275 i.trans_um,
276 i.trans_um2,
277 i.completed_ind,
278 i.staged_ind,
279 i.gl_posted_ind,
280 i.event_id,
281 --Rishi Varma 25-05-2004 3476239 Serono enh.
282 --setting the delete_mark to 9 only if phantoms are involved
283 decode(g.phantom_id,NULL,i.delete_mark,9),
284 i.text_code,
285 'NONE',
286 i.line_id, -- I.TRANS_ID,-- Using TRANS ID for tranasction_no
287 1, -- This means Display the Record Use For Forms,
288 0, -- def_trans_ind => default it to NO
289 0, -- For Future Use
290 0, -- For Future Use
291 --Swapna Kommineni bug#3897220 24-SEP-2004
292 -- subinvenoty is inserted with the trans_id which is used to check
293 -- before calling the delete_pending_trans procedure in GMEVTXNB.pls
294 trans_id, --NULL, -- For Future Use
295 g.item_um,
296 -- B2834826 prevent uom conv if not required
297 decode(g.item_um,i.trans_um2,
298 ABS(i.trans_qty2),
299 gmicuom.uom_conversion (
300 i.item_id,
301 i.lot_id,
302 ABS (i.trans_qty),
303 i.trans_um,
304 g.item_um,
305 0) )
306 FROM ic_tran_pnd i, gme_material_details g
307 WHERE doc_id = p_batch_row.batch_id AND
308 doc_type = l_doc_type AND
309 line_id = g.material_detail_id AND
310 -- Bug 3777331 commented next condition since not needed
311 --doc_id = batch_id AND
312 delete_mark <> 1 --3187467
313 -- Bug 3777331 added next AND condition and commented rest of the where clause
314 AND reverse_id IS NULL;
315 --BEGIN BUG#3528006
316 --END BUG#3528006
317 l_mat_row_count := SQL%ROWCOUNT;
318
319
320 /* Lets Now Mark all Transactions That are Reversals
321 With the ACTION_CODE ='REVL' */
322
323 /* Let's look at zero completed def transactions first */
324 OPEN c_get_cmplt_zero_def_txns;
325
326 FETCH c_get_cmplt_zero_def_txns
327 INTO l_last_txn;
328
329 IF c_get_cmplt_zero_def_txns%FOUND THEN
330 LOOP
331 FETCH c_get_cmplt_zero_def_txns
332 INTO l_current_txn;
333
334 EXIT WHEN c_get_cmplt_zero_def_txns%NOTFOUND;
335
336 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
337 gme_debug.put_line ( g_pkg_name
338 || '.'
339 || l_api_name
340 || ':'
341 || 'l_last_txn id = '
342 || l_last_txn.trans_id);
343 gme_debug.put_line ( g_pkg_name
344 || '.'
345 || l_api_name
346 || ':'
347 || 'l_current_txn id = '
348 || l_current_txn.trans_id);
349 END IF;
350
351 IF (l_last_txn.material_detail_id =
352 l_current_txn.material_detail_id)
353 AND (l_last_txn.line_type = l_current_txn.line_type)
354 AND (l_last_txn.item_id = l_current_txn.item_id)
355 AND (l_last_txn.whse_code = l_current_txn.whse_code)
356 AND (l_last_txn.lot_id = l_current_txn.lot_id)
357 AND (l_last_txn.LOCATION = l_current_txn.LOCATION) THEN
358 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
359 gme_debug.put_line ( g_pkg_name
360 || '.'
361 || l_api_name
362 || ':'
363 || 'These txns match!');
364 gme_debug.put_line ( g_pkg_name
365 || '.'
366 || l_api_name
367 || ':'
368 || 'Matching Reversal for '
369 || l_last_txn.trans_id
370 || ' Is => '
371 || l_current_txn.trans_id);
372 END IF;
373
374 UPDATE gme_inventory_txns_gtmp
375 -- SET ACTION_CODE = 'REVS'
376 SET transaction_no = 2
377 WHERE trans_id IN
378 (l_last_txn.trans_id, l_current_txn.trans_id);
379
380 FETCH c_get_cmplt_zero_def_txns
381 INTO l_last_txn;
382 ELSE
383 /* _last_txn.material_detail_id = l_current_txn.material_detail_id */
384 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
385 gme_debug.put_line ( g_pkg_name
386 || '.'
387 || l_api_name
388 || ':'
389 || 'These txns do not match!');
390 END IF;
391
392 l_last_txn := l_current_txn;
393 END IF;
394 /* _last_txn.material_detail_id = l_current_txn.material_detail_id */
395 END LOOP;
396 END IF; /* c_get_cmplt_zero_def_txns%FOUND */
397
398 CLOSE c_get_cmplt_zero_def_txns;
399
400 /* Bug 2376240 - Thomas Daniel */
401 /* Added the action_code = NONE and the batch_id condition as the following update */
402 /* was updating the rows of previous batches which have been modified */
403 UPDATE gme_inventory_txns_gtmp
404 SET transaction_no = 2
405 WHERE action_code = 'NONE'
406 AND doc_id = p_batch_row.batch_id
407 AND ( (line_type = -1 AND trans_qty > 0)
408 OR (line_type <> -1 AND trans_qty < 0) );
409
410 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
411 gme_debug.put_line ( g_pkg_name
412 || '.'
413 || l_api_name
414 || ':'
415 || 'No. Of reversals Found is = '
416 || SQL%ROWCOUNT);
417 END IF;
418
419 IF (SQL%ROWCOUNT > 0) THEN
420 OPEN c_get_init_reversal (p_batch_row.batch_id);
421
422 LOOP
423 FETCH c_get_init_reversal
424 INTO init_revs;
425
426 EXIT WHEN c_get_init_reversal%NOTFOUND;
427
428 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
429 gme_debug.put_line ('Find matching Revserals');
430 gme_debug.put_line ('ORIG id is => ' || init_revs.trans_id);
431 /* gme_debug.put_line('ORIG itemid is => ' || init_revs.item_id);
432 gme_debug.put_line('ORIG lotid is => ' || init_revs.lot_id);
433 gme_debug.put_line('ORIG location is => ' || init_revs.location);
434 gme_debug.put_line('ORIG COM IND is => ' || init_revs.completed_ind);
435 gme_debug.put_line('ORIG TRANS is => ' || ABS(init_revs.trans_qty));
436 */
437 END IF; /* NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT */
438
439 --BUG#3528006 OPEN c_get_match_reversal (p_batch_row.batch_id);
440 --3187467 Shikha Nagar 05/12/03
441 l_match_rev_id := NULL;
442
443 --BEGIN BUG#3528006
444 OPEN c_get_match_reversal_date_cmp
445 (p_batch_row.batch_id
446 ,init_revs.material_detail_id
447 ,init_revs.line_type
448 ,init_revs.item_id
449 ,init_revs.whse_code
450 ,init_revs.lot_id
451 ,init_revs.LOCATION
452 ,init_revs.completed_ind
453 ,ABS (init_revs.trans_qty)
454 ,init_revs.trans_date);
455
456 FETCH c_get_match_reversal_date_cmp
457 INTO match_revs;
458
459 IF c_get_match_reversal_date_cmp%FOUND THEN
460 gme_debug.put_line ('NEW id is => ' || match_revs.trans_id);
461 gme_debug.put_line ( g_pkg_name
462 || '.'
463 || l_api_name
464 || ':'
465 || 'Matching Reversal for '
466 || init_revs.trans_id
467 || ' Is => '
468 || match_revs.trans_id);
469 l_match_rev_id := match_revs.trans_id;
470 ELSE /* c_get_match_reversal_date_cmp is not found */
471 OPEN c_get_match_reversal (p_batch_row.batch_id
472 ,init_revs.material_detail_id
473 ,init_revs.line_type
474 ,init_revs.item_id
475 ,init_revs.whse_code
476 ,init_revs.lot_id
477 ,init_revs.LOCATION
478 ,init_revs.completed_ind
479 ,ABS (init_revs.trans_qty) );
480
481 --END BUG#3528006
482 --BUG#3528006 LOOP
483 FETCH c_get_match_reversal
484 INTO match_revs;
485
486 /* BEGIN BUG#3528006
487 EXIT WHEN c_get_match_reversal%NOTFOUND;
488 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
489 END BUG#3528006 */
490 IF c_get_match_reversal%FOUND THEN --BUG#3528006
491 gme_debug.put_line ('NEW id is => '
492 || match_revs.trans_id);
493 --BEGIN BUG#3528006
494 gme_debug.put_line ( g_pkg_name
495 || '.'
496 || l_api_name
497 || ':'
498 || 'Matching Reversal for '
499 || init_revs.trans_id
500 || ' Is => '
501 || match_revs.trans_id);
502 l_match_rev_id := match_revs.trans_id;
503 --END BUG#3528006
504 END IF;
505
506 --BEGIN BUG#3528006
507 CLOSE c_get_match_reversal;
508 END IF; /* c_get_match_reversal_date_cmp if condition end */
509
510 CLOSE c_get_match_reversal_date_cmp;
511
512 --END BUG#3528006
513
514 /* BEGIN BUG#3528006
515 IF ((init_revs.material_detail_id =
516 match_revs.material_detail_id) AND
517 (init_revs.line_type = match_revs.line_type) AND
518 (init_revs.item_id = match_revs.item_id) AND
519 (init_revs.whse_code = match_revs.whse_code) AND
520 (init_revs.lot_id = match_revs.lot_id) AND
521 (init_revs.location = match_revs.location) AND
522 (init_revs.completed_ind = match_revs.completed_ind) AND
523 (NVL(init_revs.reason_code,0) = NVL(match_revs.reason_code,0)) AND --3187467
524 (ABS (init_revs.trans_qty) = ABS (match_revs.trans_qty))
525 ) THEN
526 IF ( NVL(G_DEBUG,-1) = GME_DEBUG.G_LOG_STATEMENT ) THEN
527 gme_debug.put_line (g_pkg_name||'.'||l_api_name||':'||'Tentative Matching Reversal for '
528 || init_revs.trans_id|| ' Is => '|| match_revs.trans_id);
529 END IF;
530 --3187467
531 IF l_match_rev_id IS NULL THEN
532 l_match_rev_id := match_revs.trans_id;
533 END IF;
534 IF (init_revs.trans_date = match_revs.trans_date) THEN
535 l_match_rev_id := match_revs.trans_id;
536 EXIT;
537 END IF;
538 END BUG#3528006 */
539 --BUG #3528006 END IF; /* init_revs.material_detail_id = match_revs.material_detail_id */
540 --BUG #3528006 END LOOP; /* FETCH c_get_match_reversal */
541 --BUG #3528006 CLOSE c_get_match_reversal;
542 -- 3187467 mark the reversal if matching txn found
543 IF l_match_rev_id IS NOT NULL THEN
544 UPDATE gme_inventory_txns_gtmp
545 SET transaction_no = 2
546 WHERE trans_id = l_match_rev_id;
547 END IF;
548 END LOOP; /* c_get_init_reversal INTO init_revs */
549
550 CLOSE c_get_init_reversal;
551 END IF; /* SQL%ROWCOUNT > 0 */
552
553 IF p_batch_row.migrated_batch_ind = 'Y' THEN --BUG#3528006
554 set_default_lot_for_batch (p_batch_row => p_batch_row
555 ,x_return_status => l_return_status);
556 --BEGIN BUG#3528006
557 ELSE
558 set_default_lot_for_new_batch (x_return_status => l_return_status);
559 END IF;
560
561 --END BUG#3528006
562 IF l_return_status <> fnd_api.g_ret_sts_success THEN
563 RAISE fnd_api.g_exc_error;
564 END IF;
565 END IF; /* l_inv_exists > 0 */
566
567 load_rsrc_trans (p_batch_row => p_batch_row
568 ,x_rsc_row_count => l_rsc_row_count
569 ,x_return_status => l_return_status);
570
571 IF l_return_status <> fnd_api.g_ret_sts_success THEN
572 RAISE fnd_api.g_exc_error;
573 END IF;
574
575 /* Set Default Values for Return Parameters */
576 x_return_status := l_return_status;
577 x_mat_row_count := l_mat_row_count;
578 x_rsc_row_count := l_rsc_row_count;
579 EXCEPTION
580 WHEN fnd_api.g_exc_error THEN
581 x_return_status := fnd_api.g_ret_sts_error;
582 WHEN fnd_api.g_exc_unexpected_error THEN
583 x_return_status := fnd_api.g_ret_sts_unexp_error;
584 WHEN OTHERS THEN
585 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
586 gme_debug.put_line ('sqlerrm=' || SQLERRM);
587 END IF;
588
589 x_return_status := fnd_api.g_ret_sts_unexp_error;
590 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
591 END load_mat_and_rsc_trans;
592
593 FUNCTION build_trans_rec (
594 p_tran_row IN gme_inventory_txns_gtmp%ROWTYPE
595 ,x_tran_rec OUT NOCOPY gmi_trans_engine_pub.ictran_rec)
596 RETURN BOOLEAN
597 IS
598 l_api_name CONSTANT VARCHAR2 (30) := 'BUILD_TRANS_REC';
599 BEGIN
600 x_tran_rec.trans_id := p_tran_row.trans_id;
601 x_tran_rec.item_id := p_tran_row.item_id;
602 x_tran_rec.line_id := p_tran_row.material_detail_id;
603 x_tran_rec.co_code := p_tran_row.co_code;
604 x_tran_rec.orgn_code := p_tran_row.orgn_code;
605 x_tran_rec.whse_code := p_tran_row.whse_code;
606 x_tran_rec.lot_id := NVL (p_tran_row.lot_id, 0);
607 x_tran_rec.LOCATION :=
608 NVL (p_tran_row.LOCATION, gmigutl.ic$default_loct);
609 x_tran_rec.doc_type := p_tran_row.doc_type;
610 x_tran_rec.doc_id := p_tran_row.doc_id;
611 x_tran_rec.doc_line := NVL (p_tran_row.doc_line, 0);
612 x_tran_rec.line_type := NVL (p_tran_row.line_type, 0);
613 x_tran_rec.trans_date := NVL (p_tran_row.trans_date, SYSDATE);
614 x_tran_rec.trans_qty := p_tran_row.trans_qty;
615 x_tran_rec.trans_qty2 := p_tran_row.trans_qty2;
616 x_tran_rec.qc_grade := p_tran_row.qc_grade;
617 x_tran_rec.lot_status := p_tran_row.lot_status;
618 x_tran_rec.trans_stat := p_tran_row.trans_stat;
619 x_tran_rec.trans_um := p_tran_row.trans_um;
620 x_tran_rec.trans_um2 := p_tran_row.trans_um2;
621 x_tran_rec.staged_ind := p_tran_row.staged_ind;
622 x_tran_rec.event_id := NVL (p_tran_row.event_id, 0);
623 x_tran_rec.text_code := p_tran_row.text_code;
624 RETURN TRUE;
625 EXCEPTION
626 WHEN OTHERS THEN
627 RETURN FALSE;
628 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
629 END build_trans_rec;
630
631 /*===========================================================================================
632 Procedure
633 load_rsrc_trans
634 Description
635 This particular procedure loads the resource transactions.
636 Parameters
637 p_batch_row The batch header row to identify the batch
638 x_rsc_row_count No of resource transaction rows loaded.
639 x_return_status outcome of the API call
640 S - Success
641 E - Error
642 U - Unexpected error
643 History
644 Rishi Varma B3818266/3759970 10-08-2004
645 Added the condition for elimination of reversed records
646 =============================================================================================*/
647 PROCEDURE load_rsrc_trans (
648 p_batch_row IN gme_batch_header%ROWTYPE
649 ,x_rsc_row_count OUT NOCOPY NUMBER
650 ,x_return_status OUT NOCOPY VARCHAR2)
651 IS
652 CURSOR c_get_reversal_resources
653 IS
654 SELECT *
655 FROM gme_resource_txns_gtmp
656 WHERE action_code = 'REVL'; -- Should this be indexed.
657
658 CURSOR c_get_match_reversal_resources (v_line_id NUMBER)
659 IS
660 SELECT *
661 FROM gme_resource_txns_gtmp
662 WHERE action_code NOT IN ('REVL', 'REVS')
663 AND line_id = v_line_id
664 AND completed_ind = 1
665 ORDER BY trans_date DESC, poc_trans_id;
666
667 CURSOR c_check_rsc_transactions (p_batch_id IN NUMBER)
668 IS
669 SELECT COUNT (1)
670 FROM gme_resource_txns_gtmp
671 WHERE doc_id = p_batch_id;
672
673 l_api_name CONSTANT VARCHAR2 (30) := 'LOAD_RSRC_TRANS';
674 l_rsc_row_count NUMBER := 0;
675 l_rsc_exists NUMBER := 0;
676 l_doc_type VARCHAR2 (10);
677 resrc_revs c_get_reversal_resources%ROWTYPE;
678 mtch_resrc_revs c_get_match_reversal_resources%ROWTYPE;
679 l_return_status VARCHAR2 (1);
680 BEGIN
681 x_return_status := fnd_api.g_ret_sts_success;
682
683 -- Detemine Transactional Doc Type
684 -- 0 - PROD 10 - FPO
685 IF (p_batch_row.batch_type = 0) THEN
686 l_doc_type := 'PROD';
687 ELSIF (p_batch_row.batch_type = 10) THEN
688 l_doc_type := 'FPO';
689 ELSE
690 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
691 gme_debug.put_line ( g_pkg_name
692 || '.'
693 || l_api_name
694 || ':'
695 || 'No BATCH TYPE loaded');
696 gme_debug.put_line ( g_pkg_name
697 || '.'
698 || l_api_name
699 || ':'
700 || 'This should RETURN AN Expected Error');
701 END IF;
702
703 RAISE fnd_api.g_exc_error;
704 END IF;
705
706 OPEN c_check_rsc_transactions (p_batch_row.batch_id);
707
708 FETCH c_check_rsc_transactions
709 INTO l_rsc_exists;
710
711 CLOSE c_check_rsc_transactions;
712
713 -- Validate That We have loaded RSC txns if POC_IND ='Y'
714 IF (NVL (p_batch_row.poc_ind, 'N') = 'Y' AND l_rsc_exists = 0) THEN
715 -- Now Populate The GME_RESOURCE_TXNS_GTMP table
716 -- Should this be in same file as other table routines */
717 INSERT INTO gme_resource_txns_gtmp
718 (poc_trans_id, orgn_code, doc_type, doc_id, line_type
719 ,line_id, resources, resource_usage, trans_um
720 ,trans_date, completed_ind, posted_ind, reason_code, reason_id
721 ,start_date, end_date, text_code, transaction_no
722 ,overrided_protected_ind, action_code, delete_mark
723 ,instance_id, sequence_dependent_ind,organization_id
724 ,attribute1, attribute2, attribute3, attribute4
725 ,attribute5, attribute6, attribute7, attribute8
726 ,attribute9, attribute10, attribute11, attribute12
727 ,attribute13, attribute14, attribute15, attribute16
728 ,attribute17, attribute18, attribute19, attribute20
729 ,attribute21, attribute22, attribute23, attribute24
730 ,attribute25, attribute26, attribute27, attribute28
731 ,attribute29, attribute30, attribute_category)
732 SELECT poc_trans_id, orgn_code, doc_type, doc_id, line_type
733 ,line_id, resources, resource_usage, trans_qty_um
734 ,trans_date, completed_ind, posted_ind, reason_code, reason_id
735 ,start_date, end_date, text_code, poc_trans_id
736 ,overrided_protected_ind, 'NONE', delete_mark, instance_id
737 ,sequence_dependent_ind,organization_id
738 ,attribute1, attribute2, attribute3, attribute4
739 ,attribute5, attribute6, attribute7, attribute8
740 ,attribute9, attribute10, attribute11, attribute12
741 ,attribute13, attribute14, attribute15, attribute16
742 ,attribute17, attribute18, attribute19, attribute20
743 ,attribute21, attribute22, attribute23, attribute24
744 ,attribute25, attribute26, attribute27, attribute28
745 ,attribute29, attribute30, attribute_category
746 FROM gme_resource_txns
747 WHERE doc_id = p_batch_row.batch_id
748 AND doc_type = l_doc_type
749 AND delete_mark = 0
750 --Rishi Varma B3818266/3759970 10-08-2004
751 /*Added the condition for elimination of reversed records*/
752 AND reverse_id IS NULL;
753
754 x_rsc_row_count := SQL%ROWCOUNT;
755
756 /* Lets Now Mark all Resource Transactions that are Reversals
757 With the ACTION_CODE ='REVL' */
758 IF (NVL (g_debug, -1) = gme_debug.g_log_statement) THEN
759 gme_debug.put_line ( g_pkg_name
760 || '.'
761 || l_api_name
762 || ':'
763 || 'Use SQL to Mark reversals');
764 END IF;
765
766 UPDATE gme_resource_txns_gtmp
767 SET action_code = 'REVL'
768 WHERE resource_usage < 0 AND completed_ind = 1;
769
770 IF (SQL%ROWCOUNT > 0) THEN
771 OPEN c_get_reversal_resources;
772
773 LOOP
774 FETCH c_get_reversal_resources
775 INTO resrc_revs;
776
777 EXIT WHEN c_get_reversal_resources%NOTFOUND;
778
779 OPEN c_get_match_reversal_resources (resrc_revs.line_id);
780
781 LOOP
782 FETCH c_get_match_reversal_resources
783 INTO mtch_resrc_revs;
784
785 EXIT WHEN c_get_match_reversal_resources%NOTFOUND;
786
787 IF ( (resrc_revs.trans_date = mtch_resrc_revs.trans_date)
788 AND (ABS (resrc_revs.resource_usage) =
789 ABS (mtch_resrc_revs.resource_usage) ) ) THEN
790 UPDATE gme_resource_txns_gtmp
791 SET action_code = 'REVS'
792 WHERE poc_trans_id = mtch_resrc_revs.poc_trans_id;
793
794 EXIT;
795 END IF;
796 END LOOP;
797
798 CLOSE c_get_match_reversal_resources;
799 END LOOP;
800
801 CLOSE c_get_reversal_resources;
802 END IF;
803 ELSE
804 x_rsc_row_count := l_rsc_exists;
805 END IF;
806 EXCEPTION
807 WHEN fnd_api.g_exc_error THEN
808 x_return_status := fnd_api.g_ret_sts_error;
809 WHEN fnd_api.g_exc_unexpected_error THEN
810 x_return_status := fnd_api.g_ret_sts_unexp_error;
811 WHEN OTHERS THEN
812 x_return_status := fnd_api.g_ret_sts_unexp_error;
813 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
814 END load_rsrc_trans;
815
816 PROCEDURE set_default_lot_for_batch (
817 p_batch_row IN gme_batch_header%ROWTYPE
818 ,x_return_status OUT NOCOPY VARCHAR2)
819 IS
820 CURSOR cur_get_matl (v_batch_id gme_batch_header.batch_id%TYPE)
821 IS
822 SELECT material_detail_id
823 FROM gme_material_details
824 WHERE batch_id = v_batch_id;
825
826 get_matl_rec cur_get_matl%ROWTYPE;
827 l_def_trans_id ic_tran_pnd.trans_id%TYPE;
828 l_is_plain BOOLEAN;
829 l_return_status VARCHAR2 (1);
830 l_api_name CONSTANT VARCHAR2 (30) := 'SET_DEFAULT_LOT_FOR_BATCH';
831 error_fetch_def_lot_id EXCEPTION;
832 BEGIN
833 x_return_status := fnd_api.g_ret_sts_success;
834
835 FOR get_matl_rec IN cur_get_matl (p_batch_row.batch_id) LOOP
836 get_default_lot (get_matl_rec.material_detail_id
837 ,l_def_trans_id
838 ,l_is_plain
839 ,l_return_status);
840
841 IF l_return_status <> x_return_status OR l_def_trans_id IS NULL THEN
842 RAISE error_fetch_def_lot_id;
843 END IF;
844
845 UPDATE gme_inventory_txns_gtmp
846 SET def_trans_ind = 1
847 WHERE trans_id = l_def_trans_id;
848 END LOOP;
849 EXCEPTION
850 WHEN error_fetch_def_lot_id THEN
851 x_return_status := l_return_status;
852 WHEN OTHERS THEN
853 x_return_status := fnd_api.g_ret_sts_unexp_error;
854 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
855 END set_default_lot_for_batch;
856
857 PROCEDURE deduce_transaction_warehouse (
858 p_transaction IN ic_tran_pnd%ROWTYPE
859 ,p_item_master IN ic_item_mst%ROWTYPE
860 ,x_whse_code OUT NOCOPY ps_whse_eff.whse_code%TYPE
861 ,x_return_status OUT NOCOPY VARCHAR2)
862 IS
863 CURSOR cur_eff_whse (
864 p_orgn_code VARCHAR2
865 ,p_item_id NUMBER
866 ,p_line_type NUMBER)
867 IS
868 SELECT whse_code
869 FROM ps_whse_eff
870 WHERE plant_code = p_orgn_code
871 AND (whse_item_id IS NULL OR whse_item_id = p_item_id)
872 AND ( (p_line_type > 0 AND replen_ind = 1)
873 OR (p_line_type < 0 AND consum_ind = 1) )
874 ORDER BY whse_item_id, whse_code;
875
876 l_api_name CONSTANT VARCHAR2 (30) := 'DEDUCE_TRANSACTION_WAREHOUSE';
877 BEGIN
878 x_return_status := fnd_api.g_ret_sts_success;
879
880 OPEN cur_eff_whse (p_transaction.orgn_code
881 ,p_item_master.whse_item_id
882 ,p_transaction.line_type);
883
884 FETCH cur_eff_whse
885 INTO x_whse_code;
886
887 IF cur_eff_whse%NOTFOUND THEN
888 x_whse_code := NULL;
889 END IF;
890
891 CLOSE cur_eff_whse;
892 EXCEPTION
893 WHEN OTHERS THEN
894 x_whse_code := NULL;
895 x_return_status := fnd_api.g_ret_sts_unexp_error;
896 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
897 END deduce_transaction_warehouse;
898
899 PROCEDURE get_default_lot (
900 p_line_id IN gme_material_details.material_detail_id%TYPE
901 ,x_def_trans_id OUT NOCOPY ic_tran_pnd.trans_id%TYPE
902 ,x_is_plain OUT NOCOPY BOOLEAN
903 ,x_return_status OUT NOCOPY VARCHAR2)
904 IS
905 l_matl_dtl gme_material_details%ROWTYPE;
906 l_item_mst ic_item_mst%ROWTYPE;
907 l_ic_tran_pnd ic_tran_pnd%ROWTYPE;
908 l_whse_loct_ctl ic_whse_mst.whse_code%TYPE;
909 l_def_lot_found BOOLEAN;
910
911 CURSOR cur_get_def_trans (
912 v_batch_id gme_batch_header.batch_id%TYPE
913 ,v_line_id gme_material_details.material_detail_id%TYPE
914 ,v_doc_type gme_inventory_txns_gtmp.doc_type%TYPE)
915 IS
916 SELECT trans_id, whse_code
917 FROM gme_inventory_txns_gtmp
918 WHERE doc_id = v_batch_id
919 AND doc_type = v_doc_type
920 AND material_detail_id = v_line_id
921 AND lot_id = 0
922 AND LOCATION = p_default_loct
923 AND
924 --Rishi Varma B3476239 Serono enh.
925 --delete_mark = 0 AND
926 transaction_no <> 2
927 ORDER BY line_type
928 ,item_id
929 ,material_detail_id
930 ,whse_code
931 ,lot_id
932 ,LOCATION
933 ,completed_ind
934 ,trans_id;
935
936 CURSOR cur_get_whse_ctl (v_whse_code IN VARCHAR2)
937 IS
938 SELECT loct_ctl
939 FROM ic_whse_mst
940 WHERE whse_code = v_whse_code;
941
942 get_trans_rec cur_get_def_trans%ROWTYPE;
943 l_tran_whse ps_whse_eff.whse_code%TYPE;
944 l_return_status VARCHAR2 (1);
945 l_api_name CONSTANT VARCHAR2 (30) := 'GET_DEFAULT_LOT';
946 l_batch_type gme_batch_header.batch_type%TYPE;
947 l_doc_type gme_inventory_txns_gtmp.doc_type%TYPE;
948 l_cnt NUMBER;
949 error_deduce_trans_whse EXCEPTION;
950 -- corrupt_batch EXCEPTION;
951 BEGIN
952 x_return_status := fnd_api.g_ret_sts_success;
953 l_def_lot_found := FALSE;
954 x_def_trans_id := NULL;
955
956 SELECT *
957 INTO l_matl_dtl
958 FROM gme_material_details
959 WHERE material_detail_id = p_line_id;
960
961 SELECT *
962 INTO l_item_mst
963 FROM ic_item_mst
964 WHERE item_id = l_matl_dtl.item_id;
965
966 SELECT batch_type
967 INTO l_batch_type
968 FROM gme_batch_header
969 WHERE batch_id = l_matl_dtl.batch_id;
970
971 IF (l_batch_type = 0) THEN
972 l_doc_type := 'PROD';
973 ELSIF (l_batch_type = 10) THEN
974 l_doc_type := 'FPO';
975 END IF;
976
977 SELECT COUNT (1)
978 INTO l_cnt
979 FROM gme_inventory_txns_gtmp
980 WHERE doc_id = l_matl_dtl.batch_id
981 AND doc_type = l_doc_type
982 AND material_detail_id = p_line_id
983 AND transaction_no <> 2
984 AND trans_qty = 0;
985
986 IF l_cnt = 1 THEN
987 -- This is the default lot for sure, because there can only at most one zero qty txn, completed
988 -- or pending. No need for further processing.
989 SELECT trans_id
990 INTO x_def_trans_id
991 FROM gme_inventory_txns_gtmp
992 WHERE doc_id = l_matl_dtl.batch_id
993 AND doc_type = l_doc_type
994 AND material_detail_id = p_line_id
995 AND transaction_no <> 2
996 AND trans_qty = 0;
997
998 l_def_lot_found := TRUE;
999 --ELSIF l_cnt > 1 THEN
1000 -- OOPS! this is a corrupt batch. If there is more than one zero qty txn after
1001 -- reversals have been figured out, then this is a corrupt batch...
1002 --RAISE corrupt_batch;
1003 ELSE
1004 FOR get_rec IN cur_get_def_trans (l_matl_dtl.batch_id
1005 ,p_line_id
1006 ,l_doc_type) LOOP
1007 OPEN cur_get_whse_ctl (get_rec.whse_code);
1008
1009 FETCH cur_get_whse_ctl
1010 INTO l_whse_loct_ctl;
1011
1012 CLOSE cur_get_whse_ctl;
1013
1014 IF l_item_mst.lot_ctl = 1
1015 OR (l_item_mst.loct_ctl > 0 AND l_whse_loct_ctl > 0) THEN
1016 -- This should be the only transaction that was returned for lot or loct ctrl
1017 x_def_trans_id := get_rec.trans_id;
1018 x_is_plain := FALSE;
1019 -- Shikha Nagar 03/20/02 B2273867
1020 -- Exit out of the loop as we have found default lot for sure
1021 -- no need to loop through other fetched transactions after this
1022 EXIT;
1023 l_def_lot_found := TRUE;
1024 ELSE
1025 IF l_def_lot_found = FALSE THEN
1026 x_is_plain := TRUE;
1027
1028 SELECT *
1029 INTO l_ic_tran_pnd
1030 FROM ic_tran_pnd
1031 WHERE trans_id = get_rec.trans_id;
1032
1033 deduce_transaction_warehouse
1034 (p_transaction => l_ic_tran_pnd
1035 ,p_item_master => l_item_mst
1036 ,x_whse_code => l_tran_whse
1037 ,x_return_status => l_return_status);
1038
1039 IF l_return_status <> x_return_status THEN
1040 RAISE error_deduce_trans_whse;
1041 END IF;
1042
1043 IF (l_tran_whse = l_ic_tran_pnd.whse_code) THEN
1044 x_def_trans_id := get_rec.trans_id;
1045
1046 IF l_ic_tran_pnd.completed_ind = 0
1047 OR l_ic_tran_pnd.trans_qty = 0 THEN
1048 l_def_lot_found := TRUE;
1049 END IF;
1050 END IF;
1051 END IF;
1052 END IF;
1053 END LOOP;
1054 END IF;
1055
1056 IF x_def_trans_id IS NULL THEN
1057 OPEN cur_get_def_trans (l_matl_dtl.batch_id, p_line_id, l_doc_type);
1058
1059 FETCH cur_get_def_trans
1060 INTO get_trans_rec;
1061
1062 x_def_trans_id := get_trans_rec.trans_id;
1063
1064 CLOSE cur_get_def_trans;
1065
1066 x_is_plain := TRUE;
1067 END IF;
1068 EXCEPTION
1069 WHEN error_deduce_trans_whse THEN
1070 x_return_status := l_return_status;
1071 --WHEN corrupt_batch THEN
1072 --x_return_status := fnd_api.g_ret_sts_error;
1073 WHEN OTHERS THEN
1074 x_return_status := fnd_api.g_ret_sts_unexp_error;
1075 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1076 END get_default_lot;
1077
1078 /*===========================================================================================
1079 Procedure
1080 set_default_lot_for_new_batch
1081 Description
1082 This procedure is to set the default lot for the batches which are created or not migrated.
1083 Parameters
1084 x_return_status outcome of the API call
1085 S - Success
1086 E - Error
1087 U - Unexpected error
1088 History
1089 Vipul Vaish BUG#3528006 15-APR-2004 - PORT BUG#3470266
1090 Added this procedure to improve the performance.
1091 =============================================================================================*/
1092 PROCEDURE set_default_lot_for_new_batch (x_return_status OUT NOCOPY VARCHAR2)
1093 IS
1094 l_api_name CONSTANT VARCHAR2 (30) := 'SET_DEFAULT_LOT_FOR_NEW_BATCH';
1095 BEGIN
1096 x_return_status := fnd_api.g_ret_sts_success;
1097
1098 UPDATE gme_inventory_txns_gtmp g
1099 SET def_trans_ind = 1
1100 WHERE trans_id = (SELECT MIN (trans_id)
1101 FROM gme_inventory_txns_gtmp
1102 WHERE material_detail_id = g.material_detail_id);
1103 EXCEPTION
1104 WHEN OTHERS THEN
1105 x_return_status := fnd_api.g_ret_sts_unexp_error;
1106 fnd_msg_pub.add_exc_msg (g_pkg_name, l_api_name);
1107 END set_default_lot_for_new_batch;
1108 END gme_trans_engine_util;