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