[Home] [Help]
PACKAGE BODY: APPS.JAI_CMN_RG_TRANSFER_PKG
Source
1 PACKAGE BODY jai_cmn_rg_transfer_pkg AS
2 /* $Header: jai_cmn_rg_trnfr.plb 120.6.12010000.2 2008/08/07 14:27:35 jmeena ship $ */
3
4 /* --------------------------------------------------------------------------------------
5 Filename:
6
7 Change History:
8
9 Date Bug Remarks
10 --------- ---------- -------------------------------------------------------------
11 08-Jun-2005 Version 116.2 jai_cmn_rg_trnfr -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13
14 14-Jun-2005 rchandan for bug#4428980, Version 116.3
15 Modified the object to remove literals from DML statements and CURSORS.
16 17/04/2007 bduvarag for the Bug#5989740, file version 120.3
17 Forward porting the changes done in 11i bug#5907436
18 17/04/2007 bduvarag for the Bug#4543171, file version 120.3
19 Forward porting the changes done in 11i bug#4404994
20 17/04/2007 bduvarag for the Bug#5349052, file version 120.3
21 Forward porting the changes done in 11i bug#5352134
22 02/05/2007 bduvarag for the Bug#5141459, file version 120.4
23 Forward porting the changes done in 11i bug#4548378
24 14-may-07 kunkumar made changes for Budget and ST by IO and Build issues resolved
25
26 25-JUL-2008 Changed by JMEENA for bug#7260552
27 Reset the variable lv_debit and lv_credit to NULL in the procedure insert_rg_others.
28 Added the code to populate the gl_interface table for the SH CESS accounting and added conditions
29 to verify that Cenvat / Cess / SH Cess Accounts are not NULL.
30
31
32 */
33
34
35 PROCEDURE balance_transfer (
36 p_organization_id NUMBER,
37 p_to_organization_id NUMBER,
38 p_location_id NUMBER,
39 p_to_location_id NUMBER,
40 p_register VARCHAR2,
41 p_amount NUMBER,
42 p_cess_amount NUMBER,/*Bug 5989740 bduvarag*/
43 p_sh_cess_amount NUMBER,
44 p_process_flag OUT NOCOPY VARCHAR2,
45 p_process_message OUT NOCOPY VARCHAR2
46
47 ) IS
48
49 /* Added by Ramananda for bug#4407165 */
50 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_transfer_pkg.balance_transfer';
51 lv_debit jai_cmn_rg_others.debit%TYPE ; --rchandan for bug#4428980
52 lv_credit jai_cmn_rg_others.credit%TYPE ; --rchandan for bug#4428980
53
54 CURSOR c_rg_balances(p_organization_id NUMBER, p_location_id NUMBER) IS
55 select nvl(rg23a_balance,0), nvl(rg23c_balance,0), nvl(pla_balance,0)
56 from JAI_CMN_RG_BALANCES
57 where organization_id = p_organization_id
58 and location_id = p_location_id;
59
60 --Added by Sanjikum for cess for Bug #4136939
61 CURSOR c_oth_balances(p_organization_id IN JAI_CMN_INVENTORY_ORGS.organization_id%TYPE,
62 p_location_id IN JAI_CMN_INVENTORY_ORGS.location_id%TYPE,
63 p_register_type IN JAI_CMN_RG_OTH_BALANCES.register_type%TYPE,
64 p_tax_type IN JAI_CMN_RG_OTH_BALANCES.tax_type%TYPE DEFAULT NULL)
65 IS
66 SELECT NVL(SUM(balance),0) balance
67 FROM JAI_CMN_RG_OTH_BALANCES a,
68 JAI_CMN_INVENTORY_ORGS b
69 WHERE a.org_unit_id = b.org_unit_id
70 AND b.organization_id = p_organization_id
71 AND b.location_id = p_location_id
72 AND a.register_type = p_register_type
73 AND a.tax_type in (jai_constants.tax_type_exc_edu_cess,jai_constants.tax_type_cvd_edu_cess)
74 AND a.tax_type = NVL(p_tax_type,a.tax_type);
75 /*Bug 5989740 bduvarag start*/
76 CURSOR c_sh_oth_balances(p_organization_id IN JAI_CMN_INVENTORY_ORGS.organization_id%TYPE,
77 p_location_id IN JAI_CMN_INVENTORY_ORGS.location_id%TYPE,
78 p_register_type IN JAI_CMN_RG_OTH_BALANCES.register_type%TYPE,
79 p_tax_type IN JAI_CMN_RG_OTH_BALANCES.tax_type%TYPE DEFAULT NULL)
80 IS
81 SELECT NVL(SUM(balance),0) balance
82 FROM JAI_CMN_RG_OTH_BALANCES a,
83 JAI_CMN_INVENTORY_ORGS b
84 WHERE a.org_unit_id = b.org_unit_id
85 AND b.organization_id = p_organization_id
86 AND b.location_id = p_location_id
87 AND a.register_type = p_register_type
88 AND a.tax_type in (jai_constants.tax_type_sh_exc_edu_cess,jai_constants.tax_type_sh_cvd_edu_cess)
89 AND a.tax_type = NVL(p_tax_type,a.tax_type);
90 /*Bug 5989740 bduvarag end*/
91 CURSOR balances_to_exist IS
92 select count(1)
93 from JAI_CMN_RG_BALANCES
94 where organization_id = p_to_organization_id
95 and location_id = p_to_location_id;
96
97 CURSOR balance_cur(p_serial_no IN NUMBER, p_org_id NUMBER, p_location_id NUMBER,
98 p_fin_year NUMBER, p_register_type VARCHAR2) IS
99 SELECT nvl(closing_balance,0)
100 FROM JAI_CMN_RG_23AC_II_TRXS
101 WHERE organization_id = p_org_id
102 AND location_id = p_location_id
103 AND slno = p_serial_no
104 AND register_type = p_register_type
105 AND fin_year = p_fin_year;
106
107 CURSOR serial_no_cur(p_org_id NUMBER, p_location_id NUMBER, p_fin_year NUMBER, p_register_type VARCHAR2) IS
108 SELECT nvl(max(slno),0)
109 FROM JAI_CMN_RG_23AC_II_TRXS
110 WHERE organization_id = p_org_id
111 and location_id = p_location_id
112 and fin_year = p_fin_year
113 and register_type = p_register_type;
114
115 CURSOR fin_year_cur(p_org_id NUMBER, p_location_id NUMBER) IS
116 Select max(fin_year) fin_year
117 From JAI_CMN_FIN_YEARS
118 Where organization_id = p_org_id and
119 NVL(fin_active_flag,'N') = 'Y';
120
121 CURSOR c_rg23_fin_yr(p_org_id NUMBER, p_location_id NUMBER, p_register VARCHAR2) IS
122 Select max(fin_year) fin_year
123 From JAI_CMN_RG_23AC_II_TRXS
124 Where organization_id = p_org_id
125 and location_id = p_location_id
126 and register_type = p_register;
127
128 CURSOR c_pla_fin_yr(p_org_id NUMBER, p_location_id NUMBER) IS
129 Select max(fin_year) fin_year
130 From JAI_CMN_RG_PLA_TRXS
131 Where organization_id = p_org_id
132 and location_id = p_location_id;
133
134 CURSOR pla_serial_no_cur(p_org_id NUMBER, p_location_id NUMBER, p_fin_year NUMBER) IS
135 SELECT nvl(max(slno),0)
136 FROM JAI_CMN_RG_PLA_TRXS
137 WHERE organization_id = p_org_id
138 and location_id = p_location_id
139 and fin_year = p_fin_year;
140
141 CURSOR pla_balance_cur(p_previous_serial_no IN NUMBER, p_org_id NUMBER, p_location_id NUMBER, p_fin_year NUMBER) IS
142 SELECT nvl(closing_balance,0)
143 FROM JAI_CMN_RG_PLA_TRXS
144 WHERE organization_id = p_org_id
145 AND location_id = p_location_id
146 AND slno = p_previous_serial_no
147 AND fin_year = p_fin_year;
148
149 v_rg23a_bal NUMBER;
150 v_rg23c_bal NUMBER;
151 v_pla_bal NUMBER;
152 v_to_rg23a_bal NUMBER;
153 v_to_rg23c_bal NUMBER;
154 v_to_pla_bal NUMBER;
155
156 --Start Added by Sanjikum for cess for Bug #4136939
157 v_excise_cess_bal NUMBER;
158 v_excise_cess_amount NUMBER;
159 v_cvd_cess_amount NUMBER;
160 v_oth_balances c_oth_balances%ROWTYPE;
161 v_source_register JAI_CMN_RG_OTHERS.source_register%TYPE;
162 --End Added by Sanjikum for cess for Bug #4136939
163 /*Bug 5989740 bduvarag start*/
164 v_sh_excise_cess_bal NUMBER;
165 v_sh_excise_cess_amount NUMBER;
166 v_sh_cvd_cess_amount NUMBER;
167 v_sh_oth_balances c_sh_oth_balances%ROWTYPE;
168 /*Bug 5989740 bduvarag end*/
169 v_check_amount NUMBER := 0;
170 v_exist NUMBER := 0;
171 v_previous_serial_no NUMBER;
172 v_closing_balance NUMBER;
173 v_fin_year NUMBER;
174 v_register CHAR(1);
175 v_to_previous_serial_no NUMBER;
176 v_to_closing_balance NUMBER;
177 v_to_fin_year NUMBER;
178
179 -- Vijay Shankar for BUG#3587423
180 v_from_register_id NUMBER;
181 v_to_register_id NUMBER;
182 v_slno NUMBER;
183 v_to_slno NUMBER;
184 v_opening_balance NUMBER;
185 v_to_opening_balance NUMBER;
186 v_to_transaction_id NUMBER := 18;
187 v_src_transaction_id NUMBER := 33;
188 v_not_a_first_transaction BOOLEAN := true;
189 v_remarks VARCHAR2(50); -- := 'RG Funds Transfer'; File.Sql.35 by Brathod
190 v_user_id NUMBER ; -- := nvl(to_number(FND_PROFILE.value('USER_ID')), -1) File.Sql.35 by Brathod
191 v_login_id NUMBER ;-- := nvl(to_number(FND_PROFILE.value('LOGIN_ID')), -1) File.Sql.35 by Brathod
192 lv_reference_num jai_cmn_rg_23ac_ii_trxs.reference_num%TYPE ;--rchandan for bug#4428980
193 /*Bug 4543171 bduvarag start*/
194 CURSOR currency_cur IS
195 SELECT currency_code
196 FROM JAI_CMN_RG_BALANCES_v
197 WHERE organization_id = p_Organization_ID and Location_ID = p_Location_ID;
198
199 /* Added by Ramananda for bug#4404994, start */
200
201 CURSOR rg23a_account_cur( p_orgid IN Number,
202 p_locid IN Number
203 )IS
204 SELECT MODVAT_RM_ACCOUNT_ID
205 FROM JAI_CMN_INVENTORY_ORGS
206 WHERE organization_id = p_orgid
207 AND location_id = p_locid;
208
209 CURSOR rg23c_account_cur( p_orgid IN Number,
210 p_locid IN Number
211 )IS
212 SELECT MODVAT_CG_ACCOUNT_ID
213 FROM JAI_CMN_INVENTORY_ORGS
214 WHERE organization_id = p_orgid
215 AND location_id = p_locid;
216
217 CURSOR pla_account_cur( p_orgid IN Number,
218 p_locid IN Number
219 )IS
220 SELECT MODVAT_PLA_ACCOUNT_ID
221 FROM JAI_CMN_INVENTORY_ORGS
222 WHERE organization_id = p_orgid
223 AND location_id = p_locid;
224
225
226 CURSOR cur_cess_accountid ( p_orgid IN Number, p_locid IN Number, p_regtyp IN VARCHAR2) IS
227 SELECT
228 decode( p_regtyp,
229 'A', EXCISE_EDU_CESS_RM_ACCOUNT,
230 'C', EXCISE_EDU_CESS_CG_ACCOUNT
231 ) cess_account_id,
232 --added by JMEENA for BUG#7260552
233 decode( p_regtyp,
234 'A', SH_CESS_RM_ACCOUNT,
235 'C', SH_CESS_CG_ACCOUNT_ID
236 ) sh_cess_account_id
237
238 FROM
239 JAI_CMN_INVENTORY_ORGS
240 WHERE organization_id = p_orgid
241 AND location_id = p_locid;
242
243 lv_currency varchar2(10) ;
244 --Commented by JMEENA for bug#7260552
245 --lv_from_cess_account_id number ;
246 --lv_to_cess_account_id number ;
247 --Added by JMEENA for bug#7260552
248 lv_from_cess_account_id cur_cess_accountid%ROWTYPE ;
249 lv_to_cess_account_id cur_cess_accountid%ROWTYPE ;
250 lv_from_account_id number ;
251 lv_to_account_id number ;
252
253 /*Bug 4543171 bduvarag end*/
254 --Added by Sanjikum for cess for Bug #4136939
255 PROCEDURE insert_rg_others( p_organization_id IN JAI_CMN_INVENTORY_ORGS.organization_id%TYPE,
256 p_location_id IN JAI_CMN_INVENTORY_ORGS.location_id%TYPE,
257 p_register_type IN VARCHAR2,
258 p_source_register IN VARCHAR2,
259 p_excise_cess_amount IN NUMBER,
260 p_sh_exc_cess_amount IN NUMBER, /*Bug 5989740 bduvarag*/
261 p_sh_cvd_cess_amount IN NUMBER, /*Bug 5989740 bduvarag*/
262 p_cvd_cess_amount IN NUMBER,
263 p_transfer_from_to IN VARCHAR2,
264 p_register_id IN NUMBER,
265 p_fin_year IN NUMBER)
266 IS
267
268 /* Added by Ramananda for bug#4407165 */
269 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_transfer_pkg.insert_rg_others';
270
271 CURSOR c_pla_cess_slno(c_tax_type VARCHAR2) IS
272 select max(slno) slno
273 from JAI_CMN_RG_PLA_TRXS a
274 where organization_id = p_organization_id
275 and location_id = p_location_id
276 and fin_year = p_fin_year
277 and register_id < 0
278 and exists (select '1'
279 from JAI_CMN_RG_OTHERS
280 where source_type = 2 --2 is for PLA
281 and source_register_id = a.register_id
282 and tax_type = c_tax_type);
283
284 CURSOR c_pla_cess_master_org(c_slno NUMBER, c_tax_type VARCHAR) IS
285 Select NVL(a.closing_balance,0) closing_balance
286 from JAI_CMN_RG_OTHERS a,
287 JAI_CMN_RG_PLA_TRXS b
288 Where a.source_type = 2 --2 is for PLA
289 AND b.register_id = a.source_register_id
290 AND b.organization_id = p_organization_id
291 and b.location_id = p_location_id
292 and a.tax_type = c_tax_type
293 and b.slno = c_slno
294 and b.fin_year = p_fin_year;
295
296 cursor c_pla_cess_non_master_org(c_tax_type VARCHAR2) is
297 Select nvl(a.closing_balance,0) closing_balance
298 from JAI_CMN_RG_OTHERS a
299 Where a.source_type = 2 --2 is for PLA
300 and a.tax_type = c_tax_type /*Bug 5141459 bduvarag*/
301 AND a.source_register_id = (SELECT b.register_id
302 FROM JAI_CMN_RG_PLA_TRXS b
303 WHERE b.slno =(
304 SELECT MAX(JIP.slno)
305 FROM JAI_CMN_RG_PLA_TRXS JIP
306 WHERE JIP.organization_id = p_organization_id
307 AND JIP.location_id = p_location_id
308 AND JIP.fin_year = p_fin_year
309 AND JIP.register_id > 0
310 AND EXISTS (SELECT '1'
311 FROM JAI_CMN_RG_OTHERS
312 WHERE source_type = 2
313 AND source_register_id = JIP.register_id
314 AND source_register = p_source_register
315 AND tax_type = c_tax_type)
316 )
317 AND b.organization_id = p_organization_id
318 AND b.location_id = p_location_id
319 AND b.fin_year = p_fin_year
320 );
321
322
323 CURSOR c_rg23_cess_slno(c_tax_type VARCHAR2, c_register_type VARCHAR2, c_source_register VARCHAR2) IS
324 select max(slno) slno
325 from JAI_CMN_RG_23AC_II_TRXS a
326 where organization_id = p_organization_id
327 and location_id = p_location_id
328 and fin_year = p_fin_year
329 AND register_type = c_register_type
330 and register_id < 0
331 and exists (select '1'
332 from JAI_CMN_RG_OTHERS
333 where source_type = 1 --1 is for RG23
334 and source_register_id = a.register_id
335 and source_register = c_source_register
336 and tax_type = c_tax_type);
337
338 CURSOR c_rg23_cess_master_org(c_slno NUMBER, c_tax_type VARCHAR,
339 c_register_type VARCHAR2, c_source_register VARCHAR2) IS
340 Select NVL(a.closing_balance,0) closing_balance
341 from JAI_CMN_RG_OTHERS a,
342 JAI_CMN_RG_23AC_II_TRXS b
343 Where a.source_type = 1 --1 is for RG23
344 AND b.register_id = a.source_register_id
345 AND b.organization_id = p_organization_id
346 and b.location_id = p_location_id
347 and a.tax_type = c_tax_type
348 and b.slno = c_slno
349 and b.fin_year = p_fin_year
350 and b.register_type = c_register_type
351 AND a.source_register = c_source_register;
352
353 cursor c_rg23_cess_non_master_org(c_tax_type VARCHAR2, c_register_type VARCHAR2, c_source_register VARCHAR2) IS
354 Select nvl(a.closing_balance,0) closing_balance
355 from JAI_CMN_RG_OTHERS a
356 Where a.source_type = 1 --1 is for RG23
357 and a.tax_type = c_tax_type
358 and a.source_register = c_source_register/*Bug 5141459 bduvarag*/
359 AND a.source_register_id = (SELECT register_id
360 FROM JAI_CMN_RG_23AC_II_TRXS b
361 WHERE b.slno =(
362 SELECT MAX(JIRP.slno)
363 FROM JAI_CMN_RG_23AC_II_TRXS JIRP
364 WHERE JIRP.organization_id = p_organization_id
365 AND JIRP.location_id = p_location_id
366 AND JIRP.fin_year = p_fin_year
367 AND JIRP.register_type = p_register_type
368 AND EXISTS (SELECT '1'
369 FROM JAI_CMN_RG_OTHERS
370 WHERE source_type = 2
371 AND source_register_id = JIRP.register_id
372 AND source_register = p_source_register
373 AND tax_type = c_tax_type
374 )
375 )
376 AND b.organization_id = p_organization_id
377 AND b.location_id = p_location_id
378 AND b.fin_year = p_fin_year
379 AND b.register_type = p_register_type
380
381 );
382
383
384 /*CURSOR cess_balance_cur_pla(c_organization_id IN JAI_CMN_INVENTORY_ORGS.organization_id%TYPE,
385 c_location_id IN JAI_CMN_INVENTORY_ORGS.location_id%TYPE,
386 c_source_register IN VARCHAR2,
387 c_tax_type IN JAI_CMN_RG_OTH_BALANCES.tax_type%TYPE) IS
388 SELECT nvl(a.closing_balance,0) closing_balance
389 FROM JAI_CMN_RG_OTHERS a
390 WHERE a.source_type = 2 --2 is for JAI_CMN_RG_PLA_TRXS
391 AND a.source_register = c_source_register
392 AND a.tax_type = c_tax_type
393 AND abs(a.source_register_id) = (SELECT max(abs(c.source_register_id))
394 FROM JAI_CMN_RG_PLA_TRXS b,
395 JAI_CMN_RG_OTHERS c
396 WHERE b.register_id = c.source_register_id
397 AND b.organization_id = c_organization_id
398 AND b.location_id = c_location_id
399 AND c.tax_type = c_tax_type
400 AND c.source_type = 2 --2 is for JAI_CMN_RG_PLA_TRXS
401 AND c.source_register = c_source_register);
402
403 CURSOR cess_balance_cur(c_organization_id IN JAI_CMN_INVENTORY_ORGS.organization_id%TYPE,
404 c_location_id IN JAI_CMN_INVENTORY_ORGS.location_id%TYPE,
405 c_register_type IN VARCHAR2,
406 c_source_register IN VARCHAR2,
407 c_tax_type IN JAI_CMN_RG_OTH_BALANCES.tax_type%TYPE) IS
408 SELECT nvl(a.closing_balance,0) closing_balance
409 FROM JAI_CMN_RG_OTHERS a
410 WHERE a.source_type = 1 --1 is for JAI_CMN_RG_23AC_II_TRXS
411 AND a.source_register = c_source_register
412 AND a.tax_type = c_tax_type
413 AND abs(a.source_register_id) = (SELECT max(abs(c.source_register_id))
414 FROM JAI_CMN_RG_23AC_II_TRXS b,
415 JAI_CMN_RG_OTHERS c
416 WHERE b.register_id = c.source_register_id
417 AND b.organization_id = c_organization_id
418 AND b.location_id = c_location_id
419 AND b.register_type = c_register_type
420 AND c.tax_type = c_tax_type
421 AND c.source_type = 1 --1 is for JAI_CMN_RG_23AC_II_TRXS
422 AND c.source_register = c_source_register);*/
423
424 v_cess_amount JAI_CMN_RG_OTHERS.debit%TYPE;
425 v_cess_opening_balance JAI_CMN_RG_OTHERS.opening_balance%TYPE;
426 v_cess_closing_balance JAI_CMN_RG_OTHERS.closing_balance%TYPE;
427 v_tax_type JAI_CMN_RG_OTH_BALANCES.tax_type%TYPE;
428 v_source_type NUMBER(1);
429 v_slno NUMBER;
430
431 BEGIN
432 FOR i in 1..4 LOOP/*Bug 5989740 bduvarag*/
433 v_tax_type := NULL;
434 v_cess_amount := 0;
435 IF i = 1 AND p_excise_cess_amount > 0 THEN
436 v_tax_type := jai_constants.tax_type_exc_edu_cess;
437 v_cess_amount := p_excise_cess_amount;
438 ELSIF i = 2 AND p_cvd_cess_amount > 0 THEN
439 v_tax_type := jai_constants.tax_type_cvd_edu_cess;
440 v_cess_amount := p_cvd_cess_amount;
441 /*Bug 5989740 bduvarag start*/
442 ELSIF i = 3 AND p_sh_exc_cess_amount > 0 THEN
443 v_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
444 v_cess_amount := p_sh_exc_cess_amount;
445 ELSIF i = 4 AND p_sh_cvd_cess_amount > 0 THEN
446 v_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
447 v_cess_amount := p_sh_cvd_cess_amount;
448 /*Bug 5989740 bduvarag end*/
449 END IF;
450
451 IF v_cess_amount = 0 THEN
452 goto NEXT_RECORD;
453 END IF;
454
455 IF p_register_type <> 'PLA' THEN
456 OPEN c_rg23_cess_slno(v_tax_type, p_register_type, p_source_register);
457 FETCH c_rg23_cess_slno INTO v_slno;
458 CLOSE c_rg23_cess_slno;
459
460 IF v_slno IS NULL THEN
461 OPEN c_rg23_cess_non_master_org(v_tax_type, p_register_type, p_source_register);
462 FETCH c_rg23_cess_non_master_org INTO v_cess_opening_balance;
463 CLOSE c_rg23_cess_non_master_org;
464 ELSE
465 OPEN c_rg23_cess_master_org(v_slno, v_tax_type, p_register_type, p_source_register);
466 FETCH c_rg23_cess_master_org INTO v_cess_opening_balance;
467 CLOSE c_rg23_cess_master_org;
468 END IF;
469
470
471 /*OPEN cess_balance_cur(p_organization_id, p_location_id, p_register_type, p_source_register, v_tax_type);
472 FETCH cess_balance_cur INTO v_cess_opening_balance;
473 CLOSE cess_balance_cur;*/
474
475 v_source_type := 1;
476
477 ELSIF p_register_type = 'PLA' THEN
478 /*OPEN cess_balance_cur_pla(p_organization_id, p_location_id, p_source_register, v_tax_type);
479 FETCH cess_balance_cur_pla INTO v_cess_opening_balance;
480 CLOSE cess_balance_cur_pla; */
481
482 OPEN c_pla_cess_slno(v_tax_type);
483 FETCH c_pla_cess_slno INTO v_slno;
484 CLOSE c_pla_cess_slno;
485
486 IF v_slno IS NULL THEN
487 OPEN c_pla_cess_non_master_org(v_tax_type);
488 FETCH c_pla_cess_non_master_org INTO v_cess_opening_balance;
489 CLOSE c_pla_cess_non_master_org;
490 ELSE
491 OPEN c_pla_cess_master_org(v_slno, v_tax_type);
492 FETCH c_pla_cess_master_org INTO v_cess_opening_balance;
493 CLOSE c_pla_cess_master_org;
494 END IF;
495
496
497 v_source_type := 2;
498
499 END IF;
500 v_cess_closing_balance := NVL(v_cess_closing_balance,0);/*Bug 5989740 bduvarag*/
501 IF p_transfer_from_to = 'FROM' THEN
502 v_cess_closing_balance := v_cess_opening_balance - v_cess_amount;
503 ELSIF p_transfer_from_to = 'TO' THEN
504 v_cess_closing_balance := v_cess_opening_balance + v_cess_amount;
505 END IF;
506
507 lv_debit:=NULL; --Added for bug#7260552
508 lv_credit:=NULL; --Added for bug#7260552
509 IF p_transfer_from_to = 'FROM' THEN
510 lv_debit := v_cess_amount;
511 ELSIF p_transfer_from_to = 'TO' THEN
512 lv_credit := v_cess_amount;
513 END IF;--rchandan for bug#4428980
514
515 INSERT INTO JAI_CMN_RG_OTHERS
516 (RG_OTHER_ID,
517 SOURCE_TYPE,
518 SOURCE_REGISTER,
519 SOURCE_REGISTER_ID,
520 TAX_TYPE,
521 DEBIT,
522 CREDIT,
523 OPENING_BALANCE,
524 CLOSING_BALANCE,
525 CREATED_BY,
526 CREATION_DATE,
527 LAST_UPDATED_BY,
528 LAST_UPDATE_DATE)
529 VALUES
530 (JAI_CMN_RG_OTHERS_S.nextval,
531 v_source_type,
532 p_source_register,
533 p_register_id,
534 v_tax_type,
535 lv_debit,
536 lv_credit,
537 v_cess_opening_balance,
538 v_cess_closing_balance,
539 uid,
540 sysdate,
541 uid,
542 sysdate);
543
544 <<NEXT_RECORD>>
545 NULL;
546 END LOOP;
547
548 /* Added by Ramananda for bug#4407165 */
549 EXCEPTION
550 WHEN OTHERS THEN
551 p_process_flag := jai_constants.unexpected_error;/*Bug 5989740 bduvarag*/
552 p_process_message := 'error in insert_rg_others proc ' ||sqlerrm;/*Bug 5989740 bduvarag*/
553
554 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
555 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
556 app_exception.raise_exception;
557
558 END insert_rg_others;
559
560
561
562 /*Bug 4543171 bduvarag start*/
563 PROCEDURE rg_fund_transfer_gl_interface(
564 p_org_id number,
565 p_excise_credit number,
566 p_excise_debit number,
567 p_cess_credit number,
568 p_cess_debit number,
569 p_sh_cess_credit number, --Added by JMEENA for bug#7260552
570 p_sh_cess_debit number, --Added by JMEENA for bug#7260552
571 p_account_id number,
572 p_cess_account_id number,
573 p_sh_cess_account_id number, --Added by JMEENA for bug#7260552
574 p_charge_account_id number,
575 p_currency varchar2,
576 p_v_reference10 varchar2,
577 p_v_reference23 varchar2,
578 p_v_reference24 varchar2,
579 p_v_reference25 varchar2,
580 p_v_reference26 varchar2
581 ) IS
582
583 BEGIN
584 /* Following two Inserts will insert excise amount data into gl_interface table */
585
586 jai_cmn_gl_pkg.create_gl_entry
587 (p_org_id,
588 lv_currency,
589 p_excise_debit, /* rallamse bug#4404994 reversed p_excise_debit and p_excise_credit */
590 p_excise_credit, --null
591 p_account_id,
592 'Register India',
593 'Register India',
594 to_number(fnd_profile.value('USER_ID')),
595 NULL,
596 NULL,
597 NULL,
598 NULL,
599 p_v_reference10,
600 p_v_reference23,
601 p_v_reference24,
602 p_v_reference25,
603 p_v_reference26
604 );
605
606
607 jai_cmn_gl_pkg.create_gl_entry
608 (p_org_id,
609 lv_currency,
610 p_cess_debit, /* rallamse bug#4404994 reversed p_cess_credit and p_cess_debit */
611 p_cess_credit, --null
612 p_cess_account_id,
613 'Register India',
614 'Register India',
615 to_number(fnd_profile.value('USER_ID')),
616 NULL,
617 NULL,
618 NULL,
619 NULL,
620 p_v_reference10,
621 p_v_reference23,
622 p_v_reference24,
623 p_v_reference25,
624 p_v_reference26
625 );
626 --Added by JMEENA for bug#7260552
627 jai_cmn_gl_pkg.create_gl_entry
628 (p_org_id,
629 lv_currency,
630 p_sh_cess_debit, /* rallamse bug#4404994 reversed p_cess_credit and p_cess_debit */
631 p_sh_cess_credit,
632 p_sh_cess_account_id,
633 'Register India',
634 'Register India',
635 to_number(fnd_profile.value('USER_ID')),
636 NULL,
637 NULL,
638 NULL,
639 NULL,
640 p_v_reference10,
641 p_v_reference23,
642 p_v_reference24,
643 p_v_reference25,
644 p_v_reference26
645 );
646 EXCEPTION
647 WHEN OTHERS THEN
648 --FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
649 --FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
650 --FND_MESSAGE.SET_STRING('rg_fund_transfer_gl_interface: Error in balance_transfer procedure. Err:'||sqlerrm );
651 --raise_application_error(-20001, 'Error in rg_fund_transfer_gl_interface procedure');
652 app_exception.raise_exception;
653
654 END rg_fund_transfer_gl_interface ;
655
656 /*Bug 4543171 bduvarag end*/
657 BEGIN
658 /*------------------------------------------------------------------------------------------
659 CHANGE HISTORY for FILENAME: ja_in_jai_cmn_rg_transfer_pkg.balance_transfer.sql
660 SlNo yyyy/mm/dd Details of Changes
661 ------------------------------------------------------------------------------------------
662 1. 2004/04/27 Vijay Shankar for BUG#3587423, FileVersion - 619.1
663 Cleanedup the procedure to function properly as required
664
665 2. 2005/01/21 Sanjikum For Bug #4136939, File Version - 115.1
666 Changes done for handling the Cess
667
668 -- # Future Dependencies For the release Of this Object:-
669 -- # (Please add a row in the section below only if your bug introduces a dependency due to spec change/ A new call to a object/
670 -- # A datamodel change )
671
672 --===============================================================================================================
673 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
674 -- # Current Version Current Bug Dependent Files Version Author Date Remarks
675 -- # Of File On Bug/Patchset Dependent On
676 -- # ja_in_jai_cmn_rg_transfer_pkg.balance_transfer.sql
677 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
678 -- # 115.1 4136939 IN60105D2+ 4146708
679 -- # Enhnacement added 2 columns in table JAI_RCV_CENVAT_CLAIMS
680 -- # --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
681 -- # ****************************************************************************************************************************************************************************************
682
683 --------------------------------------------------------------------------------------------*/
684 /*Bug 4543171 bduvarag start*/
685 open currency_cur;
686 fetch currency_cur into lv_currency;
687 close currency_cur;
688 /*Bug 4543171 bduvarag end*/
689 v_remarks := 'RG Funds Transfer'; -- File.Sql.35 by Brathod
690 v_user_id := nvl(to_number(FND_PROFILE.value('USER_ID')), -1) ; -- File.Sql.35 by Brathod
691 v_login_id := nvl(to_number(FND_PROFILE.value('LOGIN_ID')), -1) ; -- File.Sql.35 by Brathod
692
693 IF p_register NOT IN ('RG23A','RG23C','PLA') THEN
694 RAISE_APPLICATION_ERROR(-20120, 'Select a Register');
695 END IF;
696
697 OPEN c_rg_balances(p_organization_id, p_location_id);
698 FETCH c_rg_balances INTO v_rg23a_bal, v_rg23c_bal, v_pla_bal;
699 CLOSE c_rg_balances;
700
701 IF p_register = 'RG23A' THEN
702 v_rg23a_bal := nvl(v_rg23a_bal,0) - nvl(p_amount,0);
703 v_check_amount := v_rg23a_bal;
704 v_register := 'A';
705 v_source_register := jai_constants.reg_rg23a_2; --Added by Sanjikum for cess for Bug #4136939
706 ELSIF p_register = 'RG23C' THEN
707 v_rg23c_bal := nvl(v_rg23c_bal,0) - nvl(p_amount,0);
708 v_check_amount := v_rg23c_bal;
709 v_register := 'C';
710 v_source_register := jai_constants.reg_rg23c_2; --Added by Sanjikum for cess for Bug #4136939
711 ELSIF p_register = 'PLA' THEN
712 v_pla_bal := nvl(v_pla_bal,0) - nvl(p_amount,0);
713 v_check_amount := v_pla_bal;
714 v_source_register := jai_constants.reg_pla; --Added by Sanjikum for cess for Bug #4136939
715 END IF;
716
717 IF v_check_amount < 0 THEN
718 RAISE_APPLICATION_ERROR(-20120, 'Amount that you want to transfer will turn your --> ' ||
719 p_register || ' Balance Negative For Organization ' ||
720 to_char(p_organization_id) || ' and for location_id '|| to_char(p_location_id));
721 END If;
722
723 --Start Added by Sanjikum for cess for Bug #4136939
724
725 --Get the total balance, for all cess taxes
726 OPEN c_oth_balances(p_organization_id, p_location_id, p_register);
727 FETCH c_oth_balances INTO v_oth_balances;
728 CLOSE c_oth_balances;
729
730 IF v_oth_balances.balance - NVL(p_cess_amount,0) < 0 THEN
731 RAISE_APPLICATION_ERROR(-20120, 'Amount that you want to transfer will turn your --> ' ||
732 p_register || ' Cess Balance Negative For Organization ' ||
733 to_char(p_organization_id) || ' and for location_id '|| to_char(p_location_id));
734 END If;
735
736 --Get the balance for excise cess
737 OPEN c_oth_balances(p_organization_id, p_location_id, p_register, jai_constants.tax_type_exc_edu_cess);
738 FETCH c_oth_balances INTO v_excise_cess_bal;
739 CLOSE c_oth_balances;
740
741 IF NVL(p_cess_amount,0) > v_excise_cess_bal THEN
742 v_excise_cess_amount := v_excise_cess_bal;
743 v_cvd_cess_amount := NVL(p_cess_amount,0) - v_excise_cess_amount;
744 ELSE
745 v_excise_cess_amount := NVL(p_cess_amount,0);
746 v_cvd_cess_amount := 0;
747 END IF;
748
749 --End Added by Sanjikum for cess for Bug #4136939
750 /*Bug 5989740 bduvarag start*/
751 OPEN c_sh_oth_balances(p_organization_id, p_location_id, p_register);
752 FETCH c_sh_oth_balances INTO v_sh_oth_balances;
753 CLOSE c_sh_oth_balances;
754
755 IF v_sh_oth_balances.balance - NVL(p_sh_cess_amount,0) < 0 THEN
756 RAISE_APPLICATION_ERROR(-20120, 'Amount that you want to transfer will turn your --> ' ||
757 p_register || ' Cess Balance Negative For Organization ' ||
758 to_char(p_organization_id) || ' and for location_id '|| to_char(p_location_id));
759 END If;
760
761 --Get the balance for excise cess
762 OPEN c_sh_oth_balances(p_organization_id, p_location_id, p_register, jai_constants.tax_type_sh_exc_edu_cess);
763 FETCH c_sh_oth_balances INTO v_sh_excise_cess_bal;
764 CLOSE c_sh_oth_balances;
765
766 IF NVL(p_sh_cess_amount,0) > v_sh_excise_cess_bal THEN
767 v_sh_excise_cess_amount := v_sh_excise_cess_bal;
768 v_sh_cvd_cess_amount := NVL(p_sh_cess_amount,0) - v_sh_excise_cess_amount;
769 ELSE
770 v_sh_excise_cess_amount := NVL(p_sh_cess_amount,0);
771 v_sh_cvd_cess_amount := 0;
772 END IF;
773 /*Bug 5989740 bduvarag end*/
774 OPEN balances_to_exist;
775 FETCH balances_to_exist INTO v_exist;
776 CLOSE balances_to_exist;
777
778 IF nvl(v_exist,0) = 0 THEN
779 RAISE_APPLICATION_ERROR(-20120, 'Balances not available for Organization ' ||
780 to_char(p_to_organization_id) ||
781 ' and for location_id ' ||to_char(p_to_location_id));
782 ELSE
783 OPEN c_rg_balances(p_to_organization_id, p_to_location_id);
784 FETCH c_rg_balances INTO v_to_rg23a_bal, v_to_rg23c_bal, v_to_pla_bal;
785 CLOSE c_rg_balances;
786 END IF;
787
788 IF p_register = 'RG23A' THEN
789 v_to_rg23a_bal := nvl(v_to_rg23a_bal,0) + nvl(p_amount,0);
790 ELSIF p_register = 'RG23C' THEN
791 v_to_rg23c_bal := nvl(v_to_rg23c_bal,0) + nvl(p_amount,0);
792 ELSIF p_register = 'PLA' THEN
793 v_to_pla_bal := nvl(v_to_pla_bal,0) + nvl(p_amount,0);
794 END IF;
795
796 -- RG Register Updation starts here For FROM Organization
797 IF p_register IN ('RG23A','RG23C') THEN
798 OPEN c_rg23_fin_yr(p_organization_id , p_location_id, v_register);
799 FETCH c_rg23_fin_yr INTO v_fin_year;
800 CLOSE c_rg23_fin_yr;
801 ELSIF p_register IN ('PLA') THEN
802 OPEN c_pla_fin_yr(p_organization_id , p_location_id);
803 FETCH c_pla_fin_yr INTO v_fin_year;
804 CLOSE c_pla_fin_yr;
805 END IF;
806
807 -- following condition is successful means, there is no transaction for FROM Orgn and Loc
808 IF v_fin_year IS NULL THEN
809 RAISE_APPLICATION_ERROR(-20119, 'Balances does not exist for From Organization:' ||
810 to_char(p_organization_id) || ' and location:' ||to_char(p_location_id));
811 END IF;
812
813 UPDATE JAI_CMN_RG_BALANCES
814 SET pla_balance = v_pla_bal,
815 rg23a_balance = v_rg23a_bal,
816 rg23c_balance = v_rg23c_bal
817 WHERE organization_id = p_organization_id
818 and location_id = p_location_id;
819
820 UPDATE JAI_CMN_RG_BALANCES
821 SET pla_balance = v_to_pla_bal,
822 rg23a_balance = v_to_rg23a_bal,
823 rg23c_balance = v_to_rg23c_bal
824 where organization_id = p_to_organization_id
825 and location_id = p_to_location_id;
826
827 --No Updates required for Cess balance as, this will be handled through a trigger
828
829 --Updations for the From Organization
830 IF p_register IN ('RG23A','RG23C') THEN
831
832 OPEN serial_no_cur(p_organization_id , p_location_id, v_fin_year, v_register);
833 FETCH serial_no_cur INTO v_previous_serial_no;
834 CLOSE serial_no_cur;
835
836 IF nvl(v_previous_serial_no,0) > 0 THEN
837
838 OPEN balance_cur(v_previous_serial_no, p_organization_id , p_location_id, v_fin_year, v_register);
839 FETCH balance_cur INTO v_closing_balance;
840 CLOSE balance_cur;
841
842 v_slno := v_previous_serial_no + 1;
843 v_opening_balance := nvl(v_closing_balance,0);
844 v_closing_balance := v_opening_balance - p_amount;
845
846 -- to fetch the active fin_year and populate PART II Register
847 v_fin_year := null;
848 OPEN fin_year_cur(p_organization_id , p_location_id) ;
849 FETCH fin_year_cur INTO v_fin_year;
850 CLOSE fin_year_cur;
851 lv_reference_num := v_remarks||'. Slno-'||v_slno;--rchandan for bug#4428980
852
853 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
854 REGISTER_ID, FIN_YEAR, SLNO, TRANSACTION_SOURCE_NUM, INVENTORY_ITEM_ID, ORGANIZATION_ID,
855 RECEIPT_REF, RECEIPT_DATE, RANGE_NO, DIVISION_NO, CR_BASIC_ED, CR_ADDITIONAL_ED, CR_OTHER_ED,
856 DR_BASIC_ED, DR_ADDITIONAL_ED, DR_OTHER_ED, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
857 REGISTER_TYPE, REMARKS, VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
858 LOCATION_ID, TRANSACTION_DATE, OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
859 REGISTER_ID_PART_I, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
860 LAST_UPDATE_LOGIN, POSTED_FLAG, MASTER_FLAG, REFERENCE_NUM,
861 other_tax_debit --Added by Sanjikum for cess for Bug #4136939
862 ) VALUES (
863 JAI_CMN_RG_23AC_II_TRXS_S.nextval, v_fin_year, v_slno, v_src_transaction_id, -1, p_organization_id,
864 null, null, null, null, null, null, null,
865 p_amount, null, null, null, null,
866 v_register, v_remarks, null, null, null, null,
867 p_location_id, SYSDATE, v_opening_balance, v_closing_balance, null,
868 null, SYSDATE, v_user_id, SYSDATE, v_user_id, v_login_id, null, null,lv_reference_num,--rchandan for bug#4428980
869 p_cess_amount+ nvl(p_sh_cess_amount,0) --Added by Sanjikum for cess for Bug #4136939 --Added + nvl(p_sh_cess_amount,0) by JMEENA for bug#7260552
870 ) RETURNING register_id INTO v_from_register_id;
871 /*Bug 4543171 bduvarag start*/
872 IF p_register = 'RG23A' THEN
873
874 open cur_cess_accountid(p_organization_id, p_location_id, 'A');
875 fetch cur_cess_accountid into lv_from_cess_account_id;
876 close cur_cess_accountid;
877
878 open rg23a_account_cur(p_organization_id, p_location_id);
879 fetch rg23a_account_cur into lv_from_account_id;
880 close rg23a_account_cur;
881
882 open rg23a_account_cur(p_to_organization_id, p_to_location_id);
883 fetch rg23a_account_cur into lv_to_account_id;
884 close rg23a_account_cur;
885 /*added by JMEENA for bug#7260552, start*/
886 if lv_from_account_id IS NULL OR lv_from_cess_account_id.cess_account_id IS NULL OR lv_from_cess_account_id.sh_cess_account_id IS NULL THEN
887 p_process_flag := jai_constants.expected_error;
888 p_process_message := 'Cenvat / Cess / SH Cess Accounts not defined for the Source Organization';
889 return;
890 END IF;
891 /* JMEENA for bug#7260552, end*/
892 rg_fund_transfer_gl_interface(
893 p_org_id => p_organization_id ,
894 p_excise_credit => null ,
895 p_excise_debit => p_amount ,
896 p_cess_credit => null ,
897 p_cess_debit => p_cess_amount ,
898 p_sh_cess_credit => null , --Added by JMEENA for bug#7260552
899 p_sh_cess_debit => p_sh_cess_amount , --Added by JMEENA for bug#7260552
900 p_account_id => lv_from_account_id ,
901 p_cess_account_id => lv_from_cess_account_id.cess_account_id , --Changed by JMEENA for bug#7260552, ( lv_from_cess_account_id to lv_from_cess_account_id.cess_account_id)
902 p_sh_cess_account_id => lv_from_cess_account_id.sh_cess_account_id , --Added by JMEENA for bug#7260552
903 p_charge_account_id => lv_to_account_id ,
904 p_currency => lv_currency ,
905 p_v_reference10 => 'India Local RG Funds Transfer of RG23A part ii Register' ,
906 p_v_reference23 => 'JAINRGFT Form : balance_transfer',
907 p_v_reference24 => 'ja_in_rg23_part_ii',
908 p_v_reference25 => 'register_id : slno' ,
909 p_v_reference26 => v_from_register_id|| ':' || v_slno
910 ) ;
911 END IF ;
912
913 IF p_register = 'RG23C'
914 THEN
915 open cur_cess_accountid(p_organization_id, p_location_id, 'C');
916 fetch cur_cess_accountid into lv_from_cess_account_id;
917 close cur_cess_accountid;
918
919 open rg23c_account_cur(p_organization_id, p_location_id);
920 fetch rg23c_account_cur into lv_from_account_id;
921 close rg23c_account_cur;
922
923 open rg23c_account_cur(p_to_organization_id, p_to_location_id);
924 fetch rg23c_account_cur into lv_to_account_id;
925 close rg23c_account_cur;
926 /*added by JMEENA for bug#7260552, start*/
927 if lv_from_account_id IS NULL OR lv_from_cess_account_id.cess_account_id IS NULL OR lv_from_cess_account_id.sh_cess_account_id IS NULL THEN
928 p_process_flag := jai_constants.expected_error;
929 p_process_message := 'Cenvat / Cess / SH Cess Accounts not defined for the Source Organization';
930 return;
931 END IF;
932 /* JMEENA for bug#7260552, end*/
933 rg_fund_transfer_gl_interface(
934 p_org_id => p_organization_id ,
935 p_excise_credit => null ,
936 p_excise_debit => p_amount ,
937 p_cess_credit => null ,
938 p_cess_debit => p_cess_amount ,
939 p_sh_cess_credit => null , --Added by JMEENA for bug#7260552
940 p_sh_cess_debit => p_sh_cess_amount , --Added by JMEENA for bug#7260552
941 p_account_id => lv_from_account_id ,
942 p_cess_account_id => lv_from_cess_account_id.cess_account_id, --Changed by JMEENA for bug#7260552
943 p_sh_cess_account_id => lv_from_cess_account_id.sh_cess_account_id , --Added by JMEENA for bug#7260552
944 p_charge_account_id => lv_to_account_id ,
945 p_currency => lv_currency,
946 p_v_reference10 => 'India Local RG Funds Transfer of RG23C part ii Register',
947 p_v_reference23 => 'JAINRGFT Form : balance_transfer',
948 p_v_reference24 => 'ja_in_rg23_part_ii',
949 p_v_reference25 => 'register_id : slno' ,
950 p_v_reference26 => v_from_register_id|| ':' || v_slno
951 ) ;
952
953 END IF;
954
955 /*Bug 4543171 bduvarag end*/
956 --Added by Sanjikum for cess for Bug #4136939
957 insert_rg_others( p_organization_id => p_organization_id,
958 p_location_id => p_location_id,
959 p_register_type => v_register,
960 p_source_register => v_source_register,
961 p_excise_cess_amount => v_excise_cess_amount,
962 p_sh_exc_cess_amount => v_sh_excise_cess_amount,/*Bug 5989740 bduvarag*/
963 p_sh_cvd_cess_amount => v_sh_cvd_cess_amount, /*Bug 5989740 bduvarag*/
964 p_cvd_cess_amount => v_cvd_cess_amount,
965 p_transfer_from_to => 'FROM',
966 p_register_id => v_from_register_id,
967 p_fin_year => v_fin_year);
968
969 ELSE
970 RAISE_APPLICATION_ERROR(-20021,'Previous Serial Number not available for Organization_id:'||p_organization_id
971 ||', location_id:'||p_location_id);
972 END IF;
973
974 ELSIF p_register = 'PLA' THEN
975
976 OPEN pla_serial_no_cur(p_organization_id , p_location_id, v_fin_year);
977 FETCH pla_serial_no_cur INTO v_previous_serial_no;
978 CLOSE pla_serial_no_cur;
979
980 IF NVL(v_previous_serial_no,0) > 0 THEN
981 OPEN pla_balance_cur(v_previous_serial_no, p_organization_id , p_location_id, v_fin_year );
982 FETCH pla_balance_cur INTO v_closing_balance;
983 CLOSE pla_balance_cur;
984
985 v_slno := v_previous_serial_no + 1;
986 v_opening_balance := nvl(v_closing_balance, 0);
987 v_closing_balance := v_opening_balance - p_amount;
988
989 -- to fetch the active fin_year and populate PART II Register
990 v_fin_year := null;
991 OPEN fin_year_cur(p_organization_id , p_location_id) ;
992 FETCH fin_year_cur INTO v_fin_year;
993 CLOSE fin_year_cur;
994
995 INSERT INTO JAI_CMN_RG_PLA_TRXS(
996 REGISTER_ID, FIN_YEAR, SLNO, TR6_CHALLAN_NO, TR6_CHALLAN_DATE, CR_BASIC_ED, CR_ADDITIONAL_ED,
997 CR_OTHER_ED, TRANSACTION_SOURCE_NUM, REF_DOCUMENT_ID, REF_DOCUMENT_DATE, DR_INVOICE_NO, DR_INVOICE_DATE,
998 DR_BASIC_ED, DR_ADDITIONAL_ED, DR_OTHER_ED, ORGANIZATION_ID, LOCATION_ID, BANK_BRANCH_ID,
999 ENTRY_DATE, INVENTORY_ITEM_ID, VENDOR_CUST_FLAG, VENDOR_ID, VENDOR_SITE_ID, RANGE_NO,
1000 DIVISION_NO, EXCISE_INVOICE_NO, REMARKS, TRANSACTION_DATE, OPENING_BALANCE, CLOSING_BALANCE,
1001 CHARGE_ACCOUNT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1002 LAST_UPDATE_LOGIN, POSTED_FLAG, MASTER_FLAG, BASIC_OPENING_BALANCE, BASIC_CLOSING_BALANCE,
1003 ADDITIONAL_OPENING_BALANCE, ADDITIONAL_CLOSING_BALANCE, OTHER_OPENING_BALANCE, OTHER_CLOSING_BALANCE,
1004 OTHER_TAX_DEBIT --Added by Sanjikum for cess for Bug #4136939
1005 ) VALUES (
1006 JAI_CMN_RG_PLA_TRXS_S1.nextval, v_fin_year, v_slno, null, null, null, null,
1007 null, v_src_transaction_id, null, null, null, null,
1008 p_amount, null, null, p_organization_id, p_location_id, null,
1009 SYSDATE, null, null, null, null, null,
1010 null, null, v_remarks, trunc(SYSDATE), v_opening_balance, v_closing_balance,
1011 null, SYSDATE, v_user_id, SYSDATE, v_user_id,
1012 v_login_id, null, null, null, null,
1013 null, null, null, null,
1014 p_cess_amount + nvl(p_sh_cess_amount,0)/*Bug 5989740 bduvarag*/--Added by Sanjikum for cess for Bug #4136939
1015 ) RETURNING register_id INTO v_from_register_id;
1016
1017 /*Bug 4543171 bduvarag start*/
1018 IF p_register = 'PLA' THEN
1019 open pla_account_cur(p_organization_id, p_location_id);
1020 fetch pla_account_cur into lv_from_account_id;
1021 close pla_account_cur;
1022
1023 open pla_account_cur(p_to_organization_id, p_to_location_id);
1024 fetch pla_account_cur into lv_to_account_id;
1025 close pla_account_cur;
1026 /*added by JMEENA for bug#7260552, start*/
1027 if lv_from_account_id IS NULL THEN
1028 p_process_flag := jai_constants.expected_error;
1029 p_process_message := 'Cenvat / Cess / SH Cess Accounts not defined for the Source Organization';
1030 return;
1031 END IF;
1032 /* JMEENA for bug#7260552, end*/
1033 rg_fund_transfer_gl_interface(
1034 p_org_id => p_organization_id ,
1035 p_excise_credit => null ,
1036 p_excise_debit => p_amount ,
1037 p_cess_credit => null ,
1038 p_cess_debit => p_cess_amount ,
1039 p_sh_cess_credit => null , --Added by JMEENA for bug#7260552 --csahoo for BUG#5907436
1040 p_sh_cess_debit => p_sh_cess_amount , --Added by JMEENA for bug#7260552
1041 p_account_id => lv_from_account_id ,
1042 p_cess_account_id => lv_from_account_id ,
1043 p_sh_cess_account_id=> lv_from_account_id , --Added by JMEENA for bug#7260552
1044 p_charge_account_id => lv_to_account_id ,
1045 p_currency => lv_currency,
1046 p_v_reference10 => 'India Local RG Funds Transfer of PLA Register',
1047 p_v_reference23 => 'JAINRGFT Form : balance_transfer',
1048 p_v_reference24 => 'ja_in_pla',
1049 p_v_reference25 => 'register_id : slno' ,
1050 p_v_reference26 => v_from_register_id|| ':' || v_slno
1051 ) ;
1052 END IF ;
1053
1054 /*Bug 4543171 bduvarag end*/
1055 --Added by Sanjikum for cess for Bug #4136939
1056 insert_rg_others( p_organization_id => p_organization_id,
1057 p_location_id => p_location_id,
1058 p_register_type => 'PLA',
1059 p_source_register => v_source_register,
1060 p_excise_cess_amount => v_excise_cess_amount,
1061 p_sh_exc_cess_amount => v_sh_excise_cess_amount,/*Bug 5989740 bduvarag*/
1062 p_sh_cvd_cess_amount => v_sh_cvd_cess_amount, /*Bug 5989740 bduvarag*/
1063 p_cvd_cess_amount => v_cvd_cess_amount,
1064 p_transfer_from_to => 'FROM',
1065 p_register_id => v_from_register_id,
1066 p_fin_year => v_fin_year);
1067
1068 ELSE
1069 RAISE_APPLICATION_ERROR(-20022,'Previous Serial Number not available for Organization_id:'||p_organization_id
1070 ||', location_id:'||p_location_id);
1071 END IF;
1072
1073 END IF;
1074
1075 --- RG Register Updation starts here for TO Organization
1076 IF p_register IN ('RG23A','RG23C') THEN
1077 OPEN c_rg23_fin_yr(p_to_organization_id , p_to_location_id, v_register);
1078 FETCH c_rg23_fin_yr INTO v_to_fin_year;
1079 CLOSE c_rg23_fin_yr;
1080 ELSIF p_register IN ('PLA') THEN
1081 OPEN c_pla_fin_yr(p_to_organization_id , p_to_location_id);
1082 FETCH c_pla_fin_yr INTO v_to_fin_year;
1083 CLOSE c_pla_fin_yr;
1084 END IF;
1085
1086 -- following condition is successful if the funds transfer is the first transaction for TO Orgn. and Loc.
1087 IF v_to_fin_year IS NULL THEN -- AND v_to_previous_serial_no = 0 THEN
1088 v_to_previous_serial_no := 0;
1089 v_to_closing_balance := 0;
1090 v_not_a_first_transaction := false;
1091
1092 OPEN fin_year_cur(p_to_organization_id , p_to_location_id) ;
1093 FETCH fin_year_cur INTO v_to_fin_year;
1094 CLOSE fin_year_cur;
1095 END IF;
1096
1097 IF p_register IN ('RG23A', 'RG23C') THEN
1098
1099 -- following if loop gets executed only if transactions exist for TO_ORGANIZATION
1100 IF v_not_a_first_transaction THEN -- AND v_to_previous_serial_no = 0 THEN
1101
1102 OPEN serial_no_cur(p_to_organization_id , p_to_location_id, v_to_fin_year, v_register);
1103 FETCH serial_no_cur INTO v_to_previous_serial_no; --, v_to_register_id;
1104 CLOSE serial_no_cur;
1105
1106 IF NVL(v_to_previous_serial_no,0) = 0 THEN
1107 -- If transactions exist in previous financial year
1108
1109 OPEN serial_no_cur(p_to_organization_id , p_to_location_id, v_to_fin_year-1, v_register);
1110 FETCH serial_no_cur INTO v_to_previous_serial_no; --, v_to_register_id;
1111 CLOSE serial_no_cur;
1112
1113 OPEN balance_cur(v_to_previous_serial_no, p_to_organization_id , p_to_location_id, v_to_fin_year-1, v_register);
1114 FETCH balance_cur INTO v_to_closing_balance;
1115 CLOSE balance_cur;
1116
1117 IF NVL(v_to_previous_serial_no,0) > 0 THEN
1118 -- as this is new financial year and balances exist in previous fin_year
1119 v_to_previous_serial_no := 0;
1120 END IF;
1121
1122 ELSE -- If transactions exist in the same Financial Year
1123 OPEN balance_cur(v_to_previous_serial_no, p_to_organization_id , p_to_location_id, v_to_fin_year, v_register);
1124 FETCH balance_cur INTO v_to_closing_balance;
1125 CLOSE balance_cur;
1126
1127 END IF;
1128
1129 END IF;
1130
1131 v_to_slno := v_to_previous_serial_no + 1;
1132 v_to_opening_balance := nvl(v_to_closing_balance, 0);
1133 v_to_closing_balance := v_to_opening_balance + p_amount;
1134 lv_reference_num := v_remarks||'. Slno-'||v_slno;--rchandan for bug#4428980
1135 INSERT INTO JAI_CMN_RG_23AC_II_TRXS(
1136 REGISTER_ID, FIN_YEAR, SLNO, TRANSACTION_SOURCE_NUM, INVENTORY_ITEM_ID, ORGANIZATION_ID,
1137 RECEIPT_REF, RECEIPT_DATE, RANGE_NO, DIVISION_NO, CR_BASIC_ED, CR_ADDITIONAL_ED, CR_OTHER_ED,
1138 DR_BASIC_ED, DR_ADDITIONAL_ED, DR_OTHER_ED, EXCISE_INVOICE_NO, EXCISE_INVOICE_DATE,
1139 REGISTER_TYPE, REMARKS, VENDOR_ID, VENDOR_SITE_ID, CUSTOMER_ID, CUSTOMER_SITE_ID,
1140 LOCATION_ID, TRANSACTION_DATE, OPENING_BALANCE, CLOSING_BALANCE, CHARGE_ACCOUNT_ID,
1141 REGISTER_ID_PART_I, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1142 LAST_UPDATE_LOGIN, POSTED_FLAG, MASTER_FLAG, REFERENCE_NUM,
1143 OTHER_TAX_CREDIT --Added by Sanjikum for cess for Bug #4136939
1144 ) VALUES (
1145 JAI_CMN_RG_23AC_II_TRXS_S.nextval, v_to_fin_year, v_to_slno, v_to_transaction_id, -1, p_to_organization_id,
1146 v_from_register_id, null, null, null, p_amount, null, null,
1147 null, null, null, null, null,
1148 v_register, v_remarks, null, null, null, null,
1149 p_to_location_id, SYSDATE, v_to_opening_balance, v_to_closing_balance, null,
1150 null, SYSDATE, v_user_id, SYSDATE, v_user_id, v_login_id, null, null, lv_reference_num,--rchandan for bug#4428980
1151 p_cess_amount + nvl(p_sh_cess_amount,0)/*Bug 5989740 bduvarag*/--Added by Sanjikum for cess for Bug #4136939
1152 ) RETURNING register_id INTO v_to_register_id;
1153
1154 UPDATE JAI_CMN_RG_23AC_II_TRXS
1155 SET RECEIPT_REF = v_to_register_id
1156 WHERE register_id = v_from_register_id;
1157 /*Bug 4543171 bduvarag start*/
1158 IF p_register = 'RG23A' THEN
1159 open cur_cess_accountid(p_to_organization_id, p_to_location_id, 'A');
1160 fetch cur_cess_accountid into lv_to_cess_account_id;
1161 close cur_cess_accountid;
1162
1163 open rg23a_account_cur(p_organization_id, p_location_id);
1164 fetch rg23a_account_cur into lv_from_account_id;
1165 close rg23a_account_cur;
1166
1167 open rg23a_account_cur(p_to_organization_id, p_to_location_id);
1168 fetch rg23a_account_cur into lv_to_account_id;
1169 close rg23a_account_cur;
1170 /*added by JMEENA for bug#7260552, start*/
1171 if lv_to_account_id IS NULL OR lv_to_cess_account_id.cess_account_id IS NULL OR lv_to_cess_account_id.sh_cess_account_id IS NULL THEN
1172 p_process_flag := jai_constants.expected_error;
1173 p_process_message := 'Cenvat / Cess / SH Cess Accounts not defined for the Destination Organization';
1174 return;
1175 END IF;
1176 /*JMEENA for bug#7260552, end*/
1177 rg_fund_transfer_gl_interface(
1178 p_org_id => p_organization_id , /*Bug 5349052 bduvarag*/
1179 p_excise_credit => p_amount ,
1180 p_excise_debit => null ,
1181 p_cess_credit => p_cess_amount ,
1182 p_cess_debit => null ,
1183 p_sh_cess_credit => p_sh_cess_amount , --JMEENA for bug#7260552
1184 p_sh_cess_debit => null , --JMEENA for bug#7260552
1185 p_account_id => lv_to_account_id ,
1186 p_cess_account_id => lv_to_cess_account_id.cess_account_id , --Changed by JMEENA for bug#7260552, Added .cess_account_id
1187 p_sh_cess_account_id => lv_to_cess_account_id.sh_cess_account_id , --JMEENA for BUG#7260552
1188 p_charge_account_id => lv_from_account_id ,
1189 p_currency => lv_currency,
1190 p_v_reference10 => 'India Local RG Funds Transfer of RG23A part ii Register',
1191 p_v_reference23 => 'JAINRGFT Form: balance_transfer',
1192 p_v_reference24 => 'ja_in_rg23_part_ii',
1193 p_v_reference25 => 'register_id : slno' ,
1194 p_v_reference26 => v_from_register_id|| ':' || v_slno
1195 ) ;
1196 END IF ;
1197
1198 IF p_register = 'RG23C'
1199 THEN
1200 open cur_cess_accountid(p_to_organization_id, p_to_location_id, 'C');
1201 fetch cur_cess_accountid into lv_to_cess_account_id;
1202 close cur_cess_accountid;
1203
1204 open rg23c_account_cur(p_organization_id, p_location_id);
1205 fetch rg23c_account_cur into lv_from_account_id;
1206 close rg23c_account_cur;
1207
1208 open rg23c_account_cur(p_to_organization_id, p_to_location_id);
1209 fetch rg23c_account_cur into lv_to_account_id;
1210 close rg23c_account_cur;
1211 /*added by JMEENA for bug#7260552, start*/
1212 if lv_to_account_id IS NULL OR lv_to_cess_account_id.cess_account_id IS NULL OR lv_to_cess_account_id.sh_cess_account_id IS NULL THEN
1213 p_process_flag := jai_constants.expected_error;
1214 p_process_message := 'Cenvat / Cess / SH Cess Accounts not defined for the Destination Organization';
1215 return;
1216 END IF;
1217 /*JMEENA for bug#7260552, end*/
1218 rg_fund_transfer_gl_interface(
1219 p_org_id => p_organization_id , /*Bug 5349052 bduvarag*/
1220 p_excise_credit => p_amount ,
1221 p_excise_debit => null ,
1222 p_cess_credit => p_cess_amount ,
1223 p_cess_debit => null ,
1224 p_sh_cess_credit => p_sh_cess_amount , --JMEENA for bug#7260552
1225 p_sh_cess_debit => null , --JMEENA for bug#7260552
1226 p_account_id => lv_to_account_id ,
1227 p_cess_account_id => lv_to_cess_account_id.cess_account_id , --JMEENA for bug#7260552, Changed to lv_to_cess_account_id.cess_account_id from lv_to_cess_account_id
1228 p_sh_cess_account_id => lv_to_cess_account_id.sh_cess_account_id ,--JMEENA for bug#7260552
1229 p_charge_account_id => lv_from_account_id ,
1230 p_currency => lv_currency,
1231 p_v_reference10 => 'India Local RG Funds Transfer of RG23C part ii Register',
1232 p_v_reference23 => 'JAINRGFT Form : balance_transfer',
1233 p_v_reference24 => 'ja_in_rg23_part_ii',
1234 p_v_reference25 => 'register_id : slno' ,
1235 p_v_reference26 => v_from_register_id|| ':' || v_slno
1236 ) ;
1237 END IF;
1238
1239 /*Bug 4543171 bduvarag end*/
1240
1241 --Added by Sanjikum for cess for Bug #4136939
1242 insert_rg_others( p_organization_id => p_organization_id,
1243 p_location_id => p_location_id,
1244 p_register_type => v_register,
1245 p_source_register => v_source_register,
1246 p_excise_cess_amount => v_excise_cess_amount,
1247 p_cvd_cess_amount => v_cvd_cess_amount,
1248 p_sh_exc_cess_amount => v_sh_excise_cess_amount, /*Bug 5989740 bduvarag*/
1249 p_sh_cvd_cess_amount => v_sh_cvd_cess_amount, /*Bug 5989740 bduvarag*/
1250 p_transfer_from_to => 'TO',
1251 p_register_id => v_to_register_id,
1252 p_fin_year => v_fin_year);
1253
1254 ELSIF p_register = 'PLA' THEN
1255
1256 -- following if loop gets executed only if transactions exist for TO_ORGANIZATION in PLA
1257 IF v_not_a_first_transaction THEN -- AND v_to_previous_serial_no = 0 THEN
1258
1259 OPEN pla_serial_no_cur(p_to_organization_id , p_to_location_id, v_to_fin_year);
1260 FETCH pla_serial_no_cur INTO v_to_previous_serial_no; --, v_to_register_id;
1261 CLOSE pla_serial_no_cur;
1262
1263 IF NVL(v_to_previous_serial_no, 0) = 0 THEN
1264 -- If transactions exist in previous financial year, then the execution comes here
1265
1266 OPEN pla_serial_no_cur(p_to_organization_id , p_to_location_id, v_to_fin_year-1);
1267 FETCH pla_serial_no_cur INTO v_to_previous_serial_no; --, v_to_register_id;
1268 CLOSE pla_serial_no_cur;
1269
1270 OPEN pla_balance_cur(v_to_previous_serial_no, p_to_organization_id , p_to_location_id, v_to_fin_year-1 );
1271 FETCH pla_balance_cur INTO v_to_closing_balance;
1272 CLOSE pla_balance_cur;
1273
1274 IF NVL(v_to_previous_serial_no,0) > 0 THEN
1275 -- as this is new financial year and balances exist in previous fin_year
1276 v_to_slno := 0;
1277 END IF;
1278
1279 ELSE -- If transactions exist in the same Financial Year
1280 OPEN pla_balance_cur(v_to_previous_serial_no, p_to_organization_id , p_to_location_id, v_to_fin_year);
1281 FETCH pla_balance_cur INTO v_to_closing_balance;
1282 CLOSE pla_balance_cur;
1283
1284 v_to_slno := v_to_previous_serial_no + 1;
1285 END IF;
1286
1287 END IF;
1288
1289 v_to_slno := v_to_previous_serial_no + 1;
1290 v_to_opening_balance := nvl(v_to_closing_balance, 0);
1291 v_to_closing_balance := v_to_opening_balance + p_amount;
1292
1293 INSERT INTO JAI_CMN_RG_PLA_TRXS(
1294 REGISTER_ID, FIN_YEAR, SLNO, TR6_CHALLAN_NO, TR6_CHALLAN_DATE, CR_BASIC_ED, CR_ADDITIONAL_ED,
1295 CR_OTHER_ED, TRANSACTION_SOURCE_NUM, REF_DOCUMENT_ID, REF_DOCUMENT_DATE, DR_INVOICE_NO, DR_INVOICE_DATE,
1296 DR_BASIC_ED, DR_ADDITIONAL_ED, DR_OTHER_ED, ORGANIZATION_ID, LOCATION_ID, BANK_BRANCH_ID,
1297 ENTRY_DATE, INVENTORY_ITEM_ID, VENDOR_CUST_FLAG, VENDOR_ID, VENDOR_SITE_ID, RANGE_NO,
1298 DIVISION_NO, EXCISE_INVOICE_NO, REMARKS, TRANSACTION_DATE, OPENING_BALANCE, CLOSING_BALANCE,
1299 CHARGE_ACCOUNT_ID, CREATION_DATE, CREATED_BY, LAST_UPDATE_DATE, LAST_UPDATED_BY,
1300 LAST_UPDATE_LOGIN, POSTED_FLAG, MASTER_FLAG, BASIC_OPENING_BALANCE, BASIC_CLOSING_BALANCE,
1301 ADDITIONAL_OPENING_BALANCE, ADDITIONAL_CLOSING_BALANCE, OTHER_OPENING_BALANCE, OTHER_CLOSING_BALANCE,
1302 OTHER_TAX_CREDIT --Added by Sanjikum for cess for Bug #4136939
1303 ) VALUES (
1304 JAI_CMN_RG_PLA_TRXS_S1.nextval, v_to_fin_year, v_to_slno, null, null, p_amount, null,
1305 null, v_to_transaction_id, v_from_register_id, null, null, null,
1306 null, null, null, p_to_organization_id, p_to_location_id, null,
1307 SYSDATE, null, null, null, null, null,
1308 null, null, v_remarks, trunc(SYSDATE), v_to_opening_balance, v_to_closing_balance,
1309 null, SYSDATE, v_user_id, SYSDATE, v_user_id,
1310 v_login_id, null, null, null, null,
1311 null, null, null, null,
1312 p_cess_amount + nvl(p_sh_cess_amount,0)/*Bug 5989740 bduvarag*/--Added by Sanjikum for cess for Bug #4136939
1313 ) RETURNING register_id INTO v_to_register_id;
1314
1315 UPDATE JAI_CMN_RG_PLA_TRXS
1316 SET ref_document_id = v_to_register_id
1317 WHERE register_id = v_from_register_id;
1318 /*Bug 4543171 bduvarag start*/
1319 IF p_register = 'PLA' THEN
1320 open pla_account_cur(p_organization_id, p_location_id);
1321 fetch pla_account_cur into lv_from_account_id;
1322 close pla_account_cur;
1323
1324 open pla_account_cur(p_to_organization_id, p_to_location_id);
1325 fetch pla_account_cur into lv_to_account_id;
1326 close pla_account_cur;
1327 /*added by JMEENA for bug#7260552, start*/
1328 if lv_to_account_id IS NULL THEN
1329 p_process_flag := jai_constants.expected_error;
1330 p_process_message := 'Cenvat / Cess / SH Cess Accounts not defined for the Destination Organization';
1331 return;
1332 END IF;
1333 /*JMEENA for bug#7260552, end*/
1334 rg_fund_transfer_gl_interface(
1335 p_org_id => p_organization_id , /*Bug 5349052 bduvarag*/
1336 p_excise_credit => p_amount ,
1337 p_excise_debit => null ,
1338 p_cess_credit => p_cess_amount ,
1339 p_cess_debit => null ,
1340 p_sh_cess_credit => p_sh_cess_amount , --JMEENA for bug#7260552
1341 p_sh_cess_debit => null , --JMEENA for bug#7260552
1342 p_account_id => lv_to_account_id ,
1343 p_cess_account_id => lv_to_account_id ,
1344 p_sh_cess_account_id=> lv_to_account_id , --JMEENA for bug#7260552
1345 p_charge_account_id => lv_from_account_id ,
1346 p_currency => lv_currency,
1347 p_v_reference10 => 'India Local RG Funds Transfer of PLA Register',
1348 p_v_reference23 => 'JAINRGFT Form : balance_transfer',
1349 p_v_reference24 => 'ja_in_pla',
1350 p_v_reference25 => 'register_id : slno' ,
1351 p_v_reference26 => v_from_register_id|| ':' || v_slno
1352 ) ;
1353 END IF ;
1354
1355 /*Bug 4543171 bduvarag end*/
1356 --Added by Sanjikum for cess for Bug #4136939
1357 insert_rg_others( p_organization_id => p_organization_id,
1358 p_location_id => p_location_id,
1359 p_register_type => 'PLA',
1360 p_source_register => v_source_register,
1361 p_excise_cess_amount => v_excise_cess_amount,
1362 p_cvd_cess_amount => v_cvd_cess_amount,
1363 p_sh_exc_cess_amount => v_sh_excise_cess_amount, /*Bug 5989740 bduvarag*/
1364 p_sh_cvd_cess_amount => v_sh_cvd_cess_amount, /*Bug 5989740 bduvarag*/
1365 p_transfer_from_to => 'TO',
1366 p_register_id => v_to_register_id,
1367 p_fin_year => v_fin_year);
1368
1369 END IF;
1370
1371 /* Added by Ramananda for bug#4407165 */
1372 EXCEPTION
1373 WHEN OTHERS THEN
1374
1375 p_process_flag := jai_constants.unexpected_error;
1376 p_process_message := sqlerrm;
1377
1378 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
1379 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
1380 app_exception.raise_exception;
1381
1382 END balance_transfer;
1383
1384 END jai_cmn_rg_transfer_pkg ;