[Home] [Help]
PACKAGE BODY: APPS.GMI_CMP_TRAN_PVT
Source
1 PACKAGE BODY GMI_CMP_TRAN_PVT AS
2 --$Header: GMIVCMPB.pls 115.4 2000/02/03 04:23:40 pkm ship $
3 -- Body start of comments
4 --+==========================================================================+
5 --| Copyright (c) 1998 Oracle Corporation |
6 --| Redwood Shores, CA, USA |
7 --| All rights reserved. |
8 --+==========================================================================+
9 --| FILE NAME |
10 --| GMIVCMPB.pls |
11 --| |
12 --| PACKAGE NAME |
13 --| GMI_CMP_TRAN_PVT |
14 --| |
15 --| DESCRIPTION |
16 --| This package contains all utility functions that performs validations |
17 --| and insert/update of inventory |
18 --| |
19 --| CONTENTS |
20 --| Update_Quantity_Transaction |
21 --| Update_Movement |
22 --| Update_Lot_Status |
23 --| Update_Qc_Grade |
24 --| Insert_Ic_Tran_Pnd |
25 --| Update_Ic_Loct_Inv |
26 --| Update_Summ_Inv |
27 --| Update_Ic_Loct_Inv_Lot_Status |
28 --| Update_Summ_Inv_Qc_Grade |
29 --| |
30 --| HISTORY |
31 --| Liz Enstone 2 Jan 2000 BUg 1159923 Change message name from SY_API_ |
32 --| UNABLE_TO_GET_SURROGATE to IC_API_UNABLE.... |
33 --| |
34 --+==========================================================================+
35 -- Body end of comments
36
37 -- Global variables
38 G_PKG_NAME CONSTANT VARCHAR2(30) := 'GMI_CMP_TRAN_PVT';
39 IC$DEFAULT_LOT VARCHAR2(255);
40 -- Func start of comments
41 --+==========================================================================+
42 --| FUNCTION NAME |
43 --| Update_Quantity_Transaction |
44 --| |
45 --| USAGE |
46 --| Perform Post-update validation for Inventory Posting |
47 --| |
48 --| DESCRIPTION |
49 --| This function controls the update functions associated with the |
50 --| inventory quantities API |
51 --| |
52 --| PARAMETERS |
53 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
54 --| |
55 --| RETURNS |
56 --| TRUE - If validation successful |
57 --| FALSE - If error detected |
58 --| |
59 --| HISTORY |
60 --| |
61 --+==========================================================================+
62 -- Func end of comments
63 FUNCTION update_quantity_transaction
64 (p_cmp_tran_rec IN cmp_tran_typ)
65 RETURN BOOLEAN
66 IS
67 l_cmp_tran_rec cmp_tran_typ;
68
69 BEGIN
70
71 -- Move completed transaction record to local
72 l_cmp_tran_rec :=p_cmp_tran_rec;
73
74 -- If trans_id not supplied then get it
75 IF (l_cmp_tran_rec.trans_id = 0)
76 THEN
77 SELECT gem5_trans_id_s.nextval INTO l_cmp_tran_rec.trans_id FROM dual;
78 IF (l_cmp_tran_rec.trans_id <= 0)
79 THEN
80 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
81 FND_MESSAGE.SET_TOKEN('SKEY','trans_id');
82 FND_MSG_PUB.Add;
83 RAISE FND_API.G_EXC_ERROR;
84 END IF;
85 END IF;
86
87 -- Perform update according to type of transaction
88 IF (l_cmp_tran_rec.doc_type = 'CREI' OR
89 l_cmp_tran_rec.doc_type = 'ADJI' OR
90 l_cmp_tran_rec.doc_type = 'TRNI')
91 THEN
92 IF NOT Update_movement(l_cmp_tran_rec)
93 THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END IF;
96 ELSIF (l_cmp_tran_rec.doc_type = 'STSI')
97 THEN
98 IF NOT Update_lot_status(l_cmp_tran_rec)
99 THEN
100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
101 END IF;
102 ELSIF (l_cmp_tran_rec.doc_type = 'GRDI')
103 THEN
104 IF NOT Update_QC_grade(l_cmp_tran_rec)
105 THEN
106 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
107 END IF;
108 END IF;
109
110 RETURN TRUE;
111
112 EXCEPTION
113
114 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
115 RETURN FALSE;
116
117 WHEN OTHERS THEN
118 -- IF FND_MSG_PUB.check_msg_level
119 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
120 -- THEN
121
122 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
123 , 'Update_Quantity_Transaction'
124 );
125 -- END IF;
126 RETURN FALSE;
127
128 END Update_Quantity_Transaction;
129
130 -- Func start of comments
131 --+==========================================================================+
132 --| FUNCTION NAME |
133 --| Update_Movement |
134 --| |
135 --| USAGE |
136 --| Perform Post-update validation for Inventory Posting |
137 --| |
138 --| DESCRIPTION |
139 --| This function controls the update functions associated with the |
140 --| inventory movement transactions. This applies to create/adjust |
141 --| and move inventory |
142 --| |
143 --| PARAMETERS |
144 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
145 --| |
146 --| RETURNS |
147 --| TRUE - If validation successful |
148 --| FALSE - If error detected |
149 --| |
150 --| HISTORY |
151 --| |
152 --+==========================================================================+
153 -- Func end of comments
154 FUNCTION Update_Movement
155 (p_cmp_tran_rec IN cmp_tran_typ)
156 RETURN BOOLEAN
157 IS
158 l_cmp_tran_rec cmp_tran_typ;
159
160 BEGIN
161
162 -- Move completed transaction record to local
163 l_cmp_tran_rec := p_cmp_tran_rec;
164
165 -- insert row in ic_tran_cmp
166 IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
167 THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170
171 -- Update location inventory
172 IF NOT Update_ic_loct_inv(l_cmp_tran_rec)
173 THEN
174 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
175 END IF;
176
177 -- Update inventory summary
178 IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
179 THEN
180 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
181 END IF;
182
183 RETURN TRUE;
184
185 EXCEPTION
186
187 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
188 RETURN FALSE;
189
190 WHEN OTHERS THEN
191 -- IF FND_MSG_PUB.check_msg_level
192 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
193 -- THEN
194 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
195 , 'Update_Movement'
196 );
197 -- END IF;
198 RETURN FALSE;
199
200 END Update_Movement;
201
202 -- Func start of comments
203 --+==========================================================================+
204 --| FUNCTION NAME |
205 --| Update_Lot_Status |
206 --| |
207 --| USAGE |
208 --| Perform Post-update validation for Inventory Posting |
209 --| |
210 --| DESCRIPTION |
211 --| This function controls the update functions associated with the |
212 --| change Lot Status transactions |
213 --| |
214 --| PARAMETERS |
215 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
216 --| |
217 --| RETURNS |
218 --| TRUE - If validation successful |
219 --| FALSE - If error detected |
220 --| |
221 --| HISTORY |
222 --| |
223 --+==========================================================================+
224 -- Func end of comments
225 FUNCTION update_lot_status
226 (p_cmp_tran_rec IN cmp_tran_typ)
227 RETURN BOOLEAN
228 IS
229 l_cmp_tran_rec cmp_tran_typ;
230
231 BEGIN
232
233 -- Move completed transaction record to local
234 l_cmp_tran_rec := p_cmp_tran_rec;
235
236 -- insert row in ic_tran_cmp
237 IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
238 THEN
239 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
240 END IF;
241
242 -- Update location inventory
243 IF NOT Update_ic_loct_inv_LOT_STATUS(l_cmp_tran_rec)
244 THEN
245 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
246 END IF;
247
248 RETURN TRUE;
249
250 EXCEPTION
251
252 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
253 RETURN FALSE;
254
255 WHEN OTHERS THEN
256 -- IF FND_MSG_PUB.check_msg_level
257 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
258 -- THEN
259 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
260 , 'Update_Lot_Status'
261 );
262 -- END IF;
263 RETURN FALSE;
264
265
266 END Update_Lot_Status;
267
268 -- Func start of comments
269 --+==========================================================================+
270 --| FUNCTION NAME |
271 --| Update_Qc_Grade |
272 --| |
273 --| USAGE |
274 --| Perform Post-update validation for Inventory Posting |
275 --| |
276 --| DESCRIPTION |
277 --| This function controls the update functions associated with the |
278 --| change QC Grade Transactions |
279 --| |
280 --| PARAMETERS |
281 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
282 --| |
283 --| RETURNS |
284 --| TRUE - If validation successful |
285 --| FALSE - If error detected |
286 --| |
287 --| HISTORY |
288 --| |
289 --+==========================================================================+
290 -- Func end of comments
291 FUNCTION Update_Qc_Grade
292 (p_cmp_tran_rec IN cmp_tran_typ)
293 RETURN BOOLEAN
294 IS
295 l_cmp_tran_rec cmp_tran_typ;
296
297 BEGIN
298
299 -- Move completed transaction record to local
300 l_cmp_tran_rec := p_cmp_tran_rec;
301
302 -- insert row in ic_tran_cmp
303 IF NOT insert_ic_tran_cmp(l_cmp_tran_rec)
304 THEN
305 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
306 END IF;
307
308 -- Update lots Master for new QC grade
309 IF (p_cmp_tran_rec.line_type = 1)
310 THEN
311 UPDATE ic_lots_mst
312 SET
313 qc_grade = p_cmp_tran_rec.qc_grade
314 , last_updated_by = p_cmp_tran_rec.user_id
315 , last_update_date = SYSDATE
316 WHERE
317 item_id = p_cmp_tran_rec.item_id
318 AND lot_id = p_cmp_tran_rec.lot_id;
319 END IF;
320
321 -- Update summary inventory
322 IF NOT Update_ic_summ_inv_QC_GRADE(l_cmp_tran_rec)
323 THEN
324 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
325 END IF;
326
327 RETURN TRUE;
328
329 EXCEPTION
330
331 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
332 RETURN FALSE;
333
334 WHEN OTHERS THEN
335 -- IF FND_MSG_PUB.check_msg_level
336 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
337 -- THEN
338 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
339 , 'Update_Qc_Grade'
340 );
341 -- END IF;
342 RETURN FALSE;
343
344 END Update_Qc_Grade;
345
346 -- Func start of comments
347 --+==========================================================================+
348 --| FUNCTION NAME |
349 --| Insert_Ic_Tran_Cmp |
350 --| |
351 --| USAGE |
352 --| Inserts a row into ic_tran_cmp |
353 --| |
354 --| DESCRIPTION |
355 --| This function inserts a row into ic_tran_cmp |
356 --| |
357 --| PARAMETERS |
358 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
359 --| |
360 --| RETURNS |
361 --| TRUE - If validation successful |
362 --| FALSE - If error detected |
363 --| |
364 --| HISTORY |
365 --| |
366 --+==========================================================================+
367 -- Func end of comments
368 FUNCTION Insert_Ic_Tran_Cmp
369 (p_cmp_tran_rec IN cmp_tran_typ)
370 RETURN BOOLEAN
371 IS
372
373 BEGIN
374
375 INSERT INTO ic_tran_cmp
376 ( item_id
377 , line_id
378 , trans_id
379 , co_code
380 , orgn_code
381 , whse_code
382 , lot_id
383 , location
384 , doc_id
385 , doc_type
386 , doc_line
387 , line_type
388 , reason_code
389 , creation_date
390 , trans_date
391 , trans_qty
392 , trans_qty2
393 , qc_grade
394 , lot_status
395 , trans_stat
396 , trans_um
397 , trans_um2
398 , op_code
399 , gl_posted_ind
400 , event_id
401 , text_code
402 , last_update_date
403 , created_by
404 , last_updated_by
405 )
406 VALUES
407 ( p_cmp_tran_rec.item_id
408 , p_cmp_tran_rec.line_id
409 , p_cmp_tran_rec.trans_id
410 , p_cmp_tran_rec.co_code
411 , p_cmp_tran_rec.orgn_code
412 , p_cmp_tran_rec.whse_code
413 , p_cmp_tran_rec.lot_id
414 , p_cmp_tran_rec.location
415 , p_cmp_tran_rec.doc_id
416 , p_cmp_tran_rec.doc_type
417 , p_cmp_tran_rec.doc_line
418 , p_cmp_tran_rec.line_type
419 , p_cmp_tran_rec.reason_code
420 , SYSDATE
421 , p_cmp_tran_rec.trans_date
422 , p_cmp_tran_rec.trans_qty
423 , p_cmp_tran_rec.trans_qty2
424 , p_cmp_tran_rec.qc_grade
425 , p_cmp_tran_rec.lot_status
426 , p_cmp_tran_rec.trans_stat
427 , p_cmp_tran_rec.trans_um
428 , p_cmp_tran_rec.trans_um2
429 , p_cmp_tran_rec.user_id
430 , p_cmp_tran_rec.gl_posted_ind
431 , p_cmp_tran_rec.event_id
432 , p_cmp_tran_rec.text_code
433 , SYSDATE
434 , p_cmp_tran_rec.user_id
435 , p_cmp_tran_rec.user_id
436
437 );
438
439 RETURN TRUE;
440
441 EXCEPTION
442 WHEN OTHERS THEN
443 -- IF FND_MSG_PUB.check_msg_level
444 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
445 -- THEN
446
447 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
448 , 'insert_ic_tran_cmp'
449 );
450 -- END IF;
451 RETURN FALSE;
452
453 END Insert_Ic_Tran_Cmp;
454
455 -- Func start of comments
456 --+==========================================================================+
457 --| FUNCTION NAME |
458 --| Update_Ic_Loct_Inv |
459 --| |
460 --| USAGE |
461 --| Update inventory level on ic_loct_inv |
462 --| |
463 --| DESCRIPTION |
464 --| This function updates the on-hand inventory on ic_loct_inv |
465 --| |
466 --| PARAMETERS |
467 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
468 --| |
469 --| RETURNS |
470 --| TRUE - If validation successful |
471 --| FALSE - If error detected |
472 --| |
473 --| HISTORY |
474 --| |
475 --+==========================================================================+
476 -- Func end of comments
477 FUNCTION Update_Ic_Loct_Inv
478 (p_cmp_tran_rec IN cmp_tran_typ)
479 RETURN BOOLEAN
480 IS
481
482 CURSOR location_inv IS
483 SELECT
484 *
485 FROM
486 ic_loct_inv
487 WHERE
488 item_id = p_cmp_tran_rec.item_id
489 AND whse_code = p_cmp_tran_rec.whse_code
490 AND lot_id = p_cmp_tran_rec.lot_id
491 AND location = p_cmp_tran_rec.location
492 FOR UPDATE;
493
494 -- Local variables
495 l_ic_loct_inv ic_loct_inv%ROWTYPE;
496
497 BEGIN
498
499 -- Check if inventory exists at location
500
501 OPEN location_inv;
502
503 FETCH location_inv INTO l_ic_loct_inv;
504
505 IF (location_inv%NOTFOUND)
506 THEN
507 -- Insert location inventory
508 IF NOT Insert_Ic_Loct_Inv(p_cmp_tran_rec)
509 THEN
510 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
511 END IF;
512 END IF;
513
514 CLOSE location_inv;
515
516 UPDATE ic_loct_inv
517 SET
518 loct_onhand = loct_onhand + p_cmp_tran_rec.trans_qty
519 , loct_onhand2 = loct_onhand2 + p_cmp_tran_rec.trans_qty2
520 , last_updated_by = p_cmp_tran_rec.user_id
521 , last_update_date = SYSDATE
522 WHERE
523 item_id = p_cmp_tran_rec.item_id
524 AND whse_code = p_cmp_tran_rec.whse_code
525 AND lot_id = p_cmp_tran_rec.lot_id
526 AND location = p_cmp_tran_rec.location;
527
528 -- If inventory movment then update lot_status for ic_loct_inv
529 IF (p_cmp_tran_rec.doc_type = 'TRNI' AND
530 p_cmp_tran_rec.line_type = 1)
531 THEN
532 UPDATE ic_loct_inv
533 SET
534 lot_status = p_cmp_tran_rec.lot_status
535 , last_updated_by = p_cmp_tran_rec.user_id
536 , last_update_date = SYSDATE
537 WHERE
538 item_id = p_cmp_tran_rec.item_id
539 AND whse_code = p_cmp_tran_rec.whse_code
540 AND lot_id = p_cmp_tran_rec.lot_id
541 AND location = p_cmp_tran_rec.location;
542 END IF;
543
544 RETURN TRUE;
545
546 EXCEPTION
547
548 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
549 RETURN FALSE;
550
551 WHEN OTHERS THEN
552 -- IF FND_MSG_PUB.check_msg_level
553 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
554 -- THEN
555 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
556 , 'Update_ic_loct_inv'
557 );
558 -- END IF;
559 RETURN FALSE;
560
561 END Update_Ic_Loct_Inv;
562
563 -- Func start of comments
564 --+==========================================================================+
565 --| FUNCTION NAME |
566 --| Insert_Ic_Loct_Inv |
567 --| |
568 --| USAGE |
569 --| Insert row into ic_loct_inv |
570 --| |
571 --| DESCRIPTION |
572 --| This function creates a new row in ic_loct_inv |
573 --| |
574 --| PARAMETERS |
575 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
576 --| |
577 --| RETURNS |
578 --| TRUE - If validation successful |
579 --| FALSE - If error detected |
580 --| |
581 --| HISTORY |
582 --| |
583 --+==========================================================================+
584 -- Func end of comments
585 FUNCTION Insert_Ic_Loct_Inv
586 (p_cmp_tran_rec IN cmp_tran_typ)
587 RETURN BOOLEAN
588 IS
589
590 BEGIN
591
592 INSERT INTO Ic_Loct_Inv
593 ( item_id
594 , whse_code
595 , lot_id
596 , location
597 , loct_onhand
598 , loct_onhand2
599 , lot_status
600 , qchold_res_code
601 , delete_mark
602 , text_code
603 , last_updated_by
604 , created_by
605 , last_update_date
606 , creation_date
607 , last_update_login
608 )
609 VALUES
610 ( p_cmp_tran_rec.item_id
611 , p_cmp_tran_rec.whse_code
612 , p_cmp_tran_rec.lot_id
613 , p_cmp_tran_rec.location
614 , 0
615 , NULL
616 , p_cmp_tran_rec.lot_status
617 , NULL
618 , 0
619 , p_cmp_tran_rec.text_code
620 , p_cmp_tran_rec.user_id
621 , p_cmp_tran_rec.user_id
622 , SYSDATE
623 , SYSDATE
624 , TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
625 );
626
627 RETURN TRUE;
628
629 EXCEPTION
630 WHEN OTHERS THEN
631 -- IF FND_MSG_PUB.check_msg_level
632 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
633 -- THEN
634
635 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
636 , 'insert_ic_loct_inv'
637 );
638 -- END IF;
639 RETURN FALSE;
640
641 END Insert_Ic_Loct_Inv;
642
643 -- Func start of comments
644 --+==========================================================================+
645 --| FUNCTION NAME |
646 --| Update_Ic_Summ_Inv |
647 --| |
648 --| USAGE |
649 --| Update inventory balances on ic_summ_inv |
650 --| |
651 --| DESCRIPTION |
652 --| This function updates the on-hand inventory balances on ic_summ_inv |
653 --| |
654 --| PARAMETERS |
655 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
656 --| |
657 --| RETURNS |
658 --| TRUE - If validation successful |
659 --| FALSE - If error detected |
660 --| |
661 --| HISTORY |
662 --| |
663 --+==========================================================================+
664 -- Func end of comments
665 FUNCTION Update_Ic_Summ_Inv
666 (p_cmp_tran_rec IN cmp_tran_typ)
667 RETURN BOOLEAN
668 IS
669 -- Cursors
670 CURSOR lot_status IS
671 SELECT
672 *
673 FROM
674 ic_lots_sts
675 WHERE
676 lot_status = p_cmp_tran_rec.lot_status;
677
678 CURSOR summary_inv IS
679 SELECT
680 *
681 FROM
682 ic_summ_inv
683 WHERE
684 item_id = p_cmp_tran_rec.item_id
685 AND whse_code = p_cmp_tran_rec.whse_code
686 AND ( qc_grade = p_cmp_tran_rec.qc_grade
687 OR qc_grade is NULL)
688 FOR UPDATE;
689
690 -- Local variables
691 l_ic_lots_sts ic_lots_sts%ROWTYPE;
692 l_ic_summ_inv ic_summ_inv%ROWTYPE;
693 l_qty NUMBER := 0;
694 l_qty2 NUMBER := 0;
695 l_prod_qty NUMBER := 0;
696 l_prod_qty2 NUMBER := 0;
697 l_order_qty NUMBER := 0;
698 l_order_qty2 NUMBER := 0;
699 l_ship_qty NUMBER := 0;
700 l_ship_qty2 NUMBER := 0;
701 l_summ_inv_id ic_summ_inv.summ_inv_id%TYPE :=0;
702
703 BEGIN
704 -- Retrieve lot status indicators
705
706 OPEN lot_status;
707
708 FETCH lot_status INTO l_ic_lots_sts;
709 IF (lot_status%NOTFOUND)
710 THEN
711 l_ic_lots_sts.nettable_ind :=1;
712 l_ic_lots_sts.order_proc_ind :=1;
713 l_ic_lots_sts.prod_ind :=1;
714 l_ic_lots_sts.shipping_ind :=1;
715 END IF;
716
717 CLOSE lot_status;
718
719
720 IF (l_ic_lots_sts.nettable_ind = 1)
721 THEN
722 l_qty := p_cmp_tran_rec.trans_qty;
723 l_qty2 := p_cmp_tran_rec.trans_qty2;
724 END IF;
725
726 IF (l_ic_lots_sts.order_proc_ind = 1)
727 THEN
728 l_order_qty := p_cmp_tran_rec.trans_qty;
729 l_order_qty2 := p_cmp_tran_rec.trans_qty2;
730 END IF;
731
732 IF (l_ic_lots_sts.prod_ind = 1)
733 THEN
734 l_prod_qty := p_cmp_tran_rec.trans_qty;
735 l_prod_qty2 := p_cmp_tran_rec.trans_qty2;
736 END IF;
737
738 IF (l_ic_lots_sts.shipping_ind = 1)
739 THEN
740 l_ship_qty := p_cmp_tran_rec.trans_qty;
741 l_ship_qty2 := p_cmp_tran_rec.trans_qty2;
742 END IF;
743
744 -- Check if inventory summary row exists and if not create it
745 OPEN summary_inv;
746
747 FETCH summary_inv INTO l_ic_summ_inv;
748
749 IF (summary_inv%NOTFOUND)
750 THEN
751 SELECT gem5_summ_inv_id_s.nextval INTO l_summ_inv_id FROM dual;
752 IF (l_summ_inv_id <= 0)
753 THEN
754 FND_MESSAGE.SET_NAME('GMI','IC_API_UNABLE_TO_GET_SURROGATE');
755 FND_MESSAGE.SET_TOKEN('SKEY','summ_inv_id');
756 FND_MSG_PUB.Add;
757 RAISE FND_API.G_EXC_ERROR;
758 ELSE
759 INSERT INTO ic_summ_inv
760 ( summ_inv_id
761 , item_id
762 , whse_code
763 , qc_grade
764 , onhand_qty
765 , onhand_qty2
766 , onhand_prod_qty
767 , onhand_prod_qty2
768 , onhand_order_qty
769 , onhand_order_qty2
770 , onhand_ship_qty
771 , onhand_ship_qty2
772 , onpurch_qty
773 , onpurch_qty2
774 , onprod_qty
775 , onprod_qty2
776 , committedsales_qty
777 , committedsales_qty2
778 , committedprod_qty
779 , committedprod_qty2
780 , intransit_qty
781 , intransit_qty2
782 , last_updated_by
783 , created_by
784 , last_update_date
785 , creation_date
786 , last_update_login
787 , program_application_id
788 , program_id
789 , program_update_date
790 , request_id
791 )
792 VALUES
793 ( l_summ_inv_id
794 , p_cmp_tran_rec.item_id
795 , p_cmp_tran_rec.whse_code
796 , p_cmp_tran_rec.qc_grade
797 , 0
798 , NULL
799 , 0
800 , NULL
801 , 0
802 , NULL
803 , 0
804 , NULL
805 , 0
806 , NULL
807 , 0
808 , NULL
809 , 0
810 , NULL
811 , 0
812 , NULL
813 , 0
814 , NULL
815 , p_cmp_tran_rec.user_id
816 , p_cmp_tran_rec.user_id
817 , SYSDATE
818 , SYSDATE
819 , TO_NUMBER(FND_PROFILE.Value('LOGIN_ID'))
820 , NULL
821 , NULL
822 , NULL
823 , NULL
824 );
825 END IF;
826 END IF;
827
828 CLOSE summary_inv;
829 UPDATE ic_summ_inv
830 SET
831 onhand_qty = onhand_qty + l_qty
832 , onhand_qty2 = onhand_qty2 + l_qty2
833 , onhand_prod_qty = onhand_prod_qty + l_prod_qty
834 , onhand_prod_qty2 = onhand_prod_qty2 + l_prod_qty2
835 , onhand_order_qty = onhand_order_qty + l_order_qty
836 , onhand_order_qty2 = onhand_order_qty2 + l_order_qty2
837 , onhand_ship_qty = onhand_ship_qty + l_ship_qty
838 , onhand_ship_qty2 = onhand_ship_qty2 + l_ship_qty2
839 , last_updated_by = p_cmp_tran_rec.user_id
840 , last_update_date = SYSDATE
841 WHERE
842 item_id = p_cmp_tran_rec.item_id
843 AND whse_code = p_cmp_tran_rec.whse_code
844 AND (qc_grade = p_cmp_tran_rec.qc_grade
845 OR qc_grade is NULL);
846
847 RETURN TRUE;
848
849 EXCEPTION
850
851 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
852 RETURN FALSE;
853
854 WHEN OTHERS THEN
855 -- IF FND_MSG_PUB.check_msg_level
856 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
857 -- THEN
858 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
859 , 'Update_ic_summ_inv'
860 );
861 -- END IF;
862 RETURN FALSE;
863
864
865 END Update_Ic_Summ_Inv;
866
867 -- Func start of comments
868 --+==========================================================================+
869 --| FUNCTION NAME |
870 --| Update_Ic_Loct_Inv_Lot_Status |
871 --| |
872 --| USAGE |
873 --| Update ic_loct_inv for Lot Status change |
874 --| |
875 --| DESCRIPTION |
876 --| This function retrieves the on-hand balances for updating the |
877 --| summary inventory For 'After' transactions, the lost_status is |
878 --| also updated |
879 --| |
880 --| PARAMETERS |
881 --| p_cmp_tran_rec IN RECORD - Inventory Transaction Details |
882 --| |
883 --| RETURNS |
884 --| TRUE - If update successful |
885 --| FALSE - If error detected |
886 --| |
887 --| HISTORY |
888 --| |
889 --+==========================================================================+
890 -- Func end of comments
891 FUNCTION Update_Ic_Loct_Inv_Lot_Status
892 (p_cmp_tran_rec IN cmp_tran_typ)
893 RETURN BOOLEAN
894 IS
895 -- Cursors
896 CURSOR location_inv IS
897 SELECT
898 loct_onhand
899 , loct_onhand2
900 FROM
901 ic_loct_inv
902 WHERE
903 item_id = p_cmp_tran_rec.item_id
904 AND whse_code = p_cmp_tran_rec.whse_code
905 AND lot_id = p_cmp_tran_rec.lot_id
906 AND location = p_cmp_tran_rec.location;
907
908 -- local variables
909 l_cmp_tran_rec cmp_tran_typ;
910 l_qty NUMBER := 0;
911 l_qty2 NUMBER := 0;
912
913 BEGIN
914
915 -- Move completed transaction record to local
916 l_cmp_tran_rec := p_cmp_tran_rec;
917
918 -- Get on-hand balances at this location
919 OPEN location_inv;
920
921 FETCH location_inv INTO
922 l_qty
923 , l_qty2;
924
925 IF (location_inv%NOTFOUND)
926 THEN
927 -- Insert location inventory
928 IF NOT Insert_Ic_Loct_Inv(p_cmp_tran_rec)
929 THEN
930 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
931 END IF;
932 END IF;
933
934 CLOSE location_inv;
935
936 -- Update transaction record with quantities
937 -- If 'After' transaction then update lot_status
938 IF (l_cmp_tran_rec.line_type = -1)
939 THEN
940 l_cmp_tran_rec.trans_qty := 0 - l_qty;
941 l_cmp_tran_rec.trans_qty2 := 0 - l_qty2;
942 ELSE
943 l_cmp_tran_rec.trans_qty := l_qty;
944 l_cmp_tran_rec.trans_qty2 := l_qty2;
945 UPDATE ic_loct_inv
946 SET
947 lot_status = l_cmp_tran_rec.lot_status
948 , last_updated_by = p_cmp_tran_rec.user_id
949 , last_update_date = SYSDATE
950 WHERE
951 item_id = p_cmp_tran_rec.item_id
952 AND whse_code = p_cmp_tran_rec.whse_code
953 AND lot_id = p_cmp_tran_rec.lot_id
954 AND location = p_cmp_tran_rec.location;
955 END IF;
956
957 -- Update inventory summary
958 IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
959 THEN
960 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
961 END IF;
962
963 RETURN TRUE;
964
965 EXCEPTION
966
967 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
968 RETURN FALSE;
969
970 WHEN OTHERS THEN
971 -- IF FND_MSG_PUB.check_msg_level
972 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
973 -- THEN
974 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
975 , 'Update_ic_loct_inv_Lot_Status'
976 );
977 -- END IF;
978 RETURN FALSE;
979
980
981 END Update_Ic_Loct_Inv_Lot_Status;
982
983 -- Func start of comments
984 --+==========================================================================+
985 --| FUNCTION NAME |
986 --| Update_Ic_Summ_Inv_Qc_Grade |
987 --| |
988 --| USAGE |
989 --| Update ic_summ_inv for QC Grade change |
990 --| |
991 --| DESCRIPTION |
992 --| This function retrieves the on-hand balances for updating the |
993 --| summary inventory |
994 --| |
995 --| PARAMETERS |
996 --| p_cmp_tran_rec IN RECORD - Inventory Transction Details |
997 --| |
998 --| RETURNS |
999 --| TRUE - If update successful |
1000 --| FALSE - If error detected |
1001 --| |
1002 --| HISTORY |
1003 --| |
1004 --+==========================================================================+
1005 -- Func end of comments
1006 FUNCTION Update_Ic_Summ_Inv_Qc_Grade
1007 (p_cmp_tran_rec IN cmp_tran_typ)
1008 RETURN BOOLEAN
1009 IS
1010 -- Cursors
1011 CURSOR location_inv IS
1012 SELECT
1013 whse_code,
1014 lot_status
1015 , SUM(loct_onhand)
1016 , SUM(loct_onhand2)
1017 FROM
1018 ic_loct_inv
1019 WHERE
1020 item_id = p_cmp_tran_rec.item_id
1021 AND lot_id = p_cmp_tran_rec.lot_id
1022 GROUP BY
1023 whse_code,lot_status
1024 ORDER BY
1025 whse_code;
1026
1027 -- local variables
1028 l_cmp_tran_rec cmp_tran_typ;
1029 l_whse_code ic_loct_inv.whse_code%TYPE;
1030 l_lot_status ic_loct_inv.lot_status%TYPE;
1031 l_qty NUMBER := 0;
1032 l_qty2 NUMBER := 0;
1033
1034 BEGIN
1035 -- Move completed transaction record to local
1036 l_cmp_tran_rec :=p_cmp_tran_rec;
1037
1038 -- Get warehouse and on-hand balances
1039 OPEN location_inv;
1040
1041 LOOP
1042
1043 FETCH location_inv INTO
1044 l_whse_code,
1045 l_lot_status
1046 , l_qty, l_qty2;
1047
1048 EXIT WHEN location_inv%NOTFOUND;
1049
1050 -- Update transaction record with warehouse and quantities
1051 l_cmp_tran_rec.whse_code := l_whse_code;
1052 l_cmp_tran_rec.lot_status := l_lot_status;
1053 IF (l_cmp_tran_rec.line_type = -1)
1054 THEN
1055 l_cmp_tran_rec.trans_qty := 0 - l_qty;
1056 l_cmp_tran_rec.trans_qty2 := 0 - l_qty2;
1057 ELSE
1058 l_cmp_tran_rec.trans_qty := l_qty;
1059 l_cmp_tran_rec.trans_qty2 := l_qty2;
1060 END IF;
1061 -- Update inventory summary
1062 IF NOT Update_ic_summ_inv(l_cmp_tran_rec)
1063 THEN
1064 CLOSE location_inv;
1065 Raise FND_API.G_EXC_UNEXPECTED_ERROR;
1066 END IF;
1067
1068 END LOOP;
1069
1070 CLOSE location_inv;
1071
1072 RETURN TRUE;
1073
1074 EXCEPTION
1075
1076 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1077 RETURN FALSE;
1078
1079 WHEN OTHERS THEN
1080 -- IF FND_MSG_PUB.check_msg_level
1081 -- (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1082 -- THEN
1083 FND_MSG_PUB.Add_Exc_Msg ( G_PKG_NAME
1084 , 'Update_ic_summ_inv_Qc_Grade'
1085 );
1086 -- END IF;
1087 RETURN FALSE;
1088
1089
1090 END Update_Ic_Summ_Inv_Qc_Grade;
1091
1092 END GMI_CMP_TRAN_PVT;