1 PACKAGE BODY JAI_JOM_LM_TRIGGER_PKG AS
2 /* $Header: jai_jom_lm_t.plb 120.1 2007/06/13 06:23:49 bduvarag ship $ */
3
4 /*
5 REM +======================================================================+
6 REM NAME ARI_T1
7 REM
8 REM DESCRIPTION Called from trigger JAI_JOM_LM_ARIUD_T1
9 REM
10 REM NOTES Refers to old trigger JAI_JOM_LM_ARI_T1
11 REM
12 REM +======================================================================+
13 */
14 PROCEDURE ARI_T1 ( pr_old t_rec%type , pr_new t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
15 CURSOR Lc_Check_Count IS
16 SELECT COUNT(*)
17 FROM JAI_OM_LC_HDRS
18 WHERE lc_number = pr_new.lc_number;
19
20 CURSOR c_lc_Balance IS
21 SELECT LC_BALANCE_AMOUNT
22 FROM JAI_OM_LC_HDRS
23 WHERE LC_NUMBER = pr_new.LC_NUMBER;
24
25
26 lv_amount_applied NUMBER; -- := pr_new.amount; --Ramananda for File.Sql.35
27 lv_lc_count NUMBER;
28 lv_picking_line_id NUMBER;
29 v_lc_balance Number; --Added by Sriram Bug # 2165355
30 /*--------------------------------------------------------------------------------------
31 Change History
32 1. Sriram - Bug # 2165355 - Lc Forward Porting - 25/10/2002
33
34 2. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
35 DB Entity as required for CASE COMPLAINCE. Version 116.1
36
37 3. 13-Jun-2005 File Version: 116.2
38 Ramananda for bug#4428980. Removal of SQL LITERALs is done
39 4. 13/06/2007 bduvarag for the bug#6124130, File version 120.1
40 changed the condtion > to >= while matching the lc_amount
41 ----------------------------------------------------------------------------------------*/
42 BEGIN
43 pv_return_code := jai_constants.successful ;
44 lv_amount_applied := pr_new.amount; --Ramananda for File.Sql.35
45
46 OPEN lc_check_count;
47 FETCH lc_check_count INTO lv_lc_count;
48 CLOSE lc_check_count;
49
50 OPEN c_lc_Balance;
51 Fetch c_lc_Balance into v_lc_balance;
52 Close c_lc_Balance;
53
54 IF lv_lc_count > 0
55 THEN
56 /*Bug6124130 bduvarag, added the = condition */
57 IF v_lc_balance - lv_amount_applied >= 0 Then
58 UPDATE JAI_OM_LC_HDRS
59 SET lc_balance_amount = ROUND(NVL(lc_balance_amount,0),2) - ROUND(NVL(lv_amount_applied,0),2)
60 WHERE lc_number = pr_new.lc_number;
61 ELSE
62 /* RAISE_APPLICATION_ERROR(-20102,'There is not enough Balance available for this Matching');
63 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'There is not enough Balance available for this Matching' ; return ;
64 END IF ;
65 ELSE
66 /* RAISE_APPLICATION_ERROR(-20101,'The Matched LC Does not Exist');
67 */ pv_return_code := jai_constants.expected_error ; pv_return_message := 'The Matched LC Does not Exist' ; return ;
68 END IF ;
69 /* Added an exception block by Ramananda for bug#4570303 */
70 EXCEPTION
71 WHEN OTHERS THEN
72 Pv_return_code := jai_constants.unexpected_error;
73 Pv_return_message := 'Encountered an error in JAI_JOM_LM_TRIGGER_PKG.ARI_T1 ' || substr(sqlerrm,1,1900);
74
75 END ARI_T1 ;
76
77 END JAI_JOM_LM_TRIGGER_PKG ;