1 PACKAGE BODY JAI_JRG_OTH_TRIGGER_PKG AS
2 /* $Header: jai_jrg_oth_t.plb 120.6 2007/10/08 05:03:03 ssumaith ship $ */
3 /* REM +======================================================================+
4 REM NAME BRI_T1
5 REM
6 REM DESCRIPTION Called from trigger JAI_JRG_OTH_BRIUD_T1
7 REM
8 REM NOTES Refers to old trigger JAI_JRG_OTH_BRI_T1
9 REM
10 REM +======================================================================+
11 */
12 PROCEDURE BRI_T1 ( pr_old t_rec%type , pr_new in out t_rec%type , pv_action varchar2 , pv_return_code out nocopy varchar2 , pv_return_message out nocopy varchar2 ) IS
13 ln_balance number;
14 ln_balance_cnt number;
15 ln_org_unit_id JAI_CMN_INVENTORY_ORGS.org_unit_id%TYPE;
16 lv_register_type JAI_CMN_RG_OTH_BALANCES.register_type%TYPE;
17
18 CURSOR cur_org_unit_id_pla IS
19 SELECT org_unit_id
20 FROM JAI_CMN_INVENTORY_ORGS
21 WHERE (organization_id,location_id)
22 IN ( SELECT organization_id,location_id
23 FROM JAI_CMN_RG_PLA_TRXS
24 WHERE register_id = pr_new.source_register_id
25 AND pr_new.source_type = 2 );
26
27 CURSOR cur_org_unit_id_rg23 IS
28 SELECT org_unit_id
29 FROM JAI_CMN_INVENTORY_ORGS
30 WHERE ( organization_id,location_id)
31 IN ( SELECT organization_id,location_id
32 FROM JAI_CMN_RG_23AC_II_TRXS
33 WHERE register_id = pr_new.source_register_id
34 AND pr_new.source_type = 1);
35
36 CURSOR cur_balance
37 IS
38 SELECT balance
39 FROM JAI_CMN_RG_OTH_BALANCES
40 WHERE org_unit_id = ln_org_unit_id
41 AND tax_type = pr_new.tax_type
42 AND register_type = lv_register_type;
43 /*Bug 5141459 start*/
44 CURSOR cur_chk_consolidation( p_register_id NUMBER )
45 IS
46 SELECT 1
47 FROM JAI_CMN_RG_23AC_II_TRXS
48 WHERE register_id = p_register_id
49 AND transaction_source_num IS NULL
50 AND pr_new.source_type = 1
51 UNION
52 SELECT 1
53 FROM JAI_CMN_RG_PLA_TRXS
54 WHERE register_id = p_register_id
55 AND ( transaction_source_num IS NULL OR tr6_source='CONSOLIDATION' or tr6_source='MANUAL') /*ADDED or tr6_source='MANUAL' for bug #5894216*/
56 AND pr_new.source_type = 2;
57
58 /*Bug 5141459 End*/
59
60 /*------------------------------------------------------------------------------------------
61 CHANGE HISTORY: FILENAME: jai_rg_others_bi_trg.sql
62
63
64 S.No Version Date Author and Details
65 ------------------------------------------------------------------------------------------
66 1. 08-Jun-2005 This Object is Modified to refer to New DB Entity names in place of Old
67 DB Entity as required for CASE COMPLAINCE. Version 116.1
68
69
70 2. 13-Jun-2005 File Version: 116.2
71 Ramananda for bug#4428980. Removal of SQL LITERALs is done
72
73 3. 3-Mar-2007 bduvarag for bug#5141459,File version 120.2
74 Forward porting the changes done in 11i bug#4548378
75
76 4. 9-jul-2007 vkaranam for bug#5894216,File Version 120.3
77 Forward porting the changes done in 11i bug#5854331
78 (In Pla Duty Book Report, Education Cess Opening Balance Is Showing Wrongly)
79
80 5. 01-Oct-2007 Bgowrava for Bug#6455886, File Version 120.4
81 Added NVL condition to ln_balance. Thus the balance calculation is proper.
82
83 6. 03-Oct-2007 Bgowrava for Bug#6455886, File Version 120.5
84 Added one condition to check if the variable ln_balance has null value,
85 instead of nvl condition at 3 places.
86
87 Dependency:
88 ----------
89
90 Sl No. Current Bug Dependent on
91 Bug/Patch set Details
92 -------------------------------------------------------------------------------------------------
93 1 4146822 4146708 New tables created(JAI_CMN_RG_OTHERS,JAI_CMN_RG_OTH_BALANCES)
94 , columns added to tables
95 which are refered in this trigger
96
97 --------------------------------------------------------------------------------------------------*/
98 BEGIN
99 pv_return_code := jai_constants.successful ;
100
101 /**********************************************************************
102 CREATED BY : rchandan
103 CREATED DATE : 28-JAN-2005
104 ENHANCEMENT BUG : 4146822
105 PURPOSE : To update opening balance and closing balance of JAI_CMN_RG_OTHERS and update the balance of
106 JAI_CMN_RG_OTH_BALANCES for the inserted tax type and register id. bug# 4146708 creates the objects
107
108 **********************************************************************/
109 ln_balance := 0 ;
110 /*Bug 5141459 Start*/
111 OPEN cur_chk_consolidation(pr_new.source_register_id);
112 FETCH cur_chk_consolidation INTO ln_balance;
113 CLOSE cur_chk_consolidation;
114
115 IF ln_balance = 1 THEN
116 RETURN;
117 END IF;
118 /*Bug 5141459 End*/
119 SELECT DECODE(pr_new.source_register,'RG23A_P2','RG23A','RG23C_P2','RG23C','PLA','PLA')
120 INTO lv_register_type
121 FROM dual;
122
123 IF pr_new.source_type IN ( 3,4) THEN
124 /* Insertion into this table from JAI_IN_RG23D, JAI_RCV_CENVAT_CLAIMS . No need of balances calculation in this case*/
125 return;
126
127 ELSIF pr_new.source_type = 1 THEN /* RG23 */
128
129 OPEN cur_org_unit_id_rg23;
130 FETCH cur_org_unit_id_rg23 into ln_org_unit_id;
131 CLOSE cur_org_unit_id_rg23;
132
133 ELSIF pr_new.source_type = 2 THEN /* PLA */
134
135 OPEN cur_org_unit_id_pla;
136 FETCH cur_org_unit_id_pla into ln_org_unit_id;
137 CLOSE cur_org_unit_id_pla;
138
139 END IF;
140
141 -- Retrieve the balance from JAI_CMN_RG_OTH_BALANCES
142
143 SELECT count(1)
144 INTO ln_balance_cnt
145 FROM JAI_CMN_RG_OTH_BALANCES
146 WHERE org_unit_id = ln_org_unit_id
147 AND tax_type = pr_new.tax_type
148 AND register_type = lv_register_type;
149
150 IF ln_balance_cnt <> 0 THEN /* If there are no records in JAI_CMN_RG_OTH_BALANCES
151 for that org_unit_id,tax_type,lv_register_type*/
152
153 --Lock the table ja_in_oth_balances with a dummy update.
154
155 UPDATE JAI_CMN_RG_OTH_BALANCES
156 SET tax_type = tax_type
157 WHERE org_unit_id = ln_org_unit_id
158 AND tax_type = pr_new.tax_type
159 AND register_type = lv_register_type;
160
161 OPEN cur_balance;
162 FETCH cur_balance INTO ln_balance;
163 CLOSE cur_balance;
164
165 --Added below by Bgowrava for Bug#6455886
166 if ln_balance is null then
167 ln_balance := 0;
168 end if;
169
170 ELSE
171
172 ln_balance := 0;
173
174 INSERT INTO JAI_CMN_RG_OTH_BALANCES( org_unit_id ,
175 tax_type ,
176 balance ,
177 register_type ,
178 created_by ,
179 creation_date ,
180 last_updated_by ,
181 last_update_date ,
182 last_update_login)
183 VALUES( ln_org_unit_id ,
184 pr_new.tax_type ,
185 0 ,
186 lv_register_type ,
187 fnd_global.user_id ,
188 sysdate ,
189 fnd_global.user_id ,
190 sysdate ,
191 fnd_global.login_id
192 );
193
194 END IF;
195
196 --Update closing and opening balances of JAI_CMN_RG_OTHERS accordingly.
197
198 pr_new.opening_balance := ln_balance;
199
200 IF pr_new.debit IS NOT NULL THEN
201
202 pr_new.closing_balance := ln_balance - pr_new.debit;
203
204 ELSIF pr_new.credit IS NOT NULL THEN
205
206 pr_new.closing_balance := ln_balance + pr_new.credit;
207
208 END IF ;
209
210 UPDATE JAI_CMN_RG_OTH_BALANCES
211 SET balance = pr_new.closing_balance,
212 last_updated_by = fnd_global.user_id,
213 last_update_date = sysdate,
214 last_update_login = fnd_global.login_id
215 WHERE org_unit_id = ln_org_unit_id
216 AND tax_type = pr_new.tax_type
217 AND register_type = lv_register_type;
218
219 /* Added an exception block by Ramananda for bug#4570303 */
220 EXCEPTION
221 WHEN OTHERS THEN
222 Pv_return_code := jai_constants.unexpected_error;
223 Pv_return_message := 'Encountered an error in JAI_JRG_OTH_TRIGGER_PKG.BRI_T1 ' || substr(sqlerrm,1,1900);
224
225 END BRI_T1 ;
226
227 END JAI_JRG_OTH_TRIGGER_PKG ;
[Home] [Help]