1 PACKAGE BODY jai_cmn_rg_period_bals_pkg AS
2 /* $Header: jai_cmn_rg_pbal.plb 120.4 2007/05/04 04:51:42 bduvarag 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_pbal -Object is Modified to refer to New DB Entity names in place of Old DB Entity Names
12 as required for CASE COMPLAINCE.
13 17/04/2007 bduvarag for the Bug#5989740, file version 120.11
14 Forward porting the changes done in 11i bug#5907436
15
16 */
17
18 procedure consolidate_balances
19 (
20 errbuf OUT NOCOPY varchar2,
21 retcode OUT NOCOPY varchar2,
22 p_period_type in varchar2,
23 p_register_type in varchar2,
24 pv_consolidate_till in varchar2 /* rallamse bug#4336482 changed to VARCHAR2 from DATE */
25 )
26 is
27
28 /* Added by Ramananda for bug#4407165 */
29 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_period_bals_pkg.consolidate_balances';
30
31 ld_consolidate_from date;
32 ld_consolidate_till date;
33
34 ld_start_date date;
35 ld_end_date date;
36 ln_period_opening_bal number;
37 ln_period_closing_bal number;
38 ln_fin_year JAI_CMN_RG_PERIOD_BALS.fin_year%type;
39 lv_register_type JAI_CMN_RG_PERIOD_BALS.register_type%type;
40
41 ln_previous_closing_bal number;
42 ld_previous_end_date date;
43
44 ln_total_rg_amount number;
45 r_last_record JAI_CMN_RG_PERIOD_BALS%rowtype;
46
47 /* rallamse bug#4336482 */
48 p_consolidate_till DATE; -- DEFAULT fnd_date.canonical_to_date(pv_consolidate_till) File.Sql.35 by Brathod
49 /* End of Bug# 4336482 */
50
51 lc_end_date date ; -- := to_date('31-MAR-2003', 'dd-mon-yyyy') File.Sql.35 by Brathod
52
53 CURSOR c_last_record(cp_organization_id IN NUMBER, cp_location_id IN NUMBER, cp_register_type IN VARCHAR2) IS
54 SELECT *
55 FROM JAI_CMN_RG_PERIOD_BALS
56 WHERE organization_id = cp_organization_id
57 AND location_id = cp_location_id
58 AND register_type = cp_register_type
59 AND (start_date, end_date) =
60 (
61 SELECT max(start_date), max(end_date)
62 FROM JAI_CMN_RG_PERIOD_BALS
63 WHERE organization_id = cp_organization_id
64 AND location_id = cp_location_id
65 AND register_type = cp_register_type
66 );
67
68 CURSOR c_total_rg_amount
69 (cp_organization_id in number, cp_location_id in number, cp_register_type in varchar2,
70 cp_start_date in date, cp_end_date in date)
71 IS
72 /* bgowrava for forward porting bug#5674376. additional_cvd column added */
73 SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0)
74 - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0) - nvl(dr_additional_cvd,0)) total_modvat_amount,
75 min(fin_year)
76 FROM JAI_CMN_RG_23AC_II_TRXS
77 WHERE organization_id = cp_organization_id
78 AND location_id = cp_location_id
79 AND register_type = cp_register_type
80 AND trunc(creation_date) between cp_start_date and cp_end_date
81 and period_balance_id is null
82 and inventory_item_id <> 0;
83
84
85 /* following cursors added by bgowrava for forward porting bug#5674376 */
86 CURSOR c_total_cess_amount
87 (cp_organization_id in number, cp_location_id in number, cp_register_type in varchar2,
88 cp_start_date in date, cp_end_date in date, cp_tax_type in varchar2)
89 IS
90 SELECT sum(nvl(b.credit,0) - nvl(b.debit,0)) total_cess
91 FROM JAI_CMN_RG_23AC_II_TRXS a, JAI_CMN_RG_OTHERS b
92 WHERE a.organization_id = cp_organization_id
93 AND a.location_id = cp_location_id
94 AND a.register_type = cp_register_type
95 AND trunc(a.creation_date) between cp_start_date and cp_end_date
96 and a.period_balance_id is null
97 and a.inventory_item_id <> 0
98 and b.source_register_id = a.register_id
99 and b.source_type = 1
100 and b.tax_type = cp_tax_type;
101
102 CURSOR c_get_fin_year(cp_organization_id IN NUMBER, cp_period_start_date in date) IS
103 SELECT fin_year
104 FROM JAI_CMN_FIN_YEARS
105 WHERE organization_id = cp_organization_id
106 AND cp_period_start_date between fin_year_start_date and fin_year_end_date;
107 /* end bgowrava for forward porting bug#5674376 */
108
109 CURSOR c_min_rg_date( cp_organization_id IN NUMBER,
110 cp_location_id IN NUMBER,
111 cp_register_type IN VARCHAR2,
112 cp_consolidate_till IN DATE)/*bug 5241875.Added
113 this parameter*/
114 IS
115 SELECT min(creation_date)
116 FROM JAI_CMN_RG_23AC_II_TRXS
117 WHERE organization_id = cp_organization_id
118 AND location_id = cp_location_id
119 AND register_type = cp_register_type
120 AND period_balance_id is null
121 AND trunc(creation_date) <= cp_consolidate_till;/*bug 5241875*/
122
123 cursor c_get_period_balance_id is
124 select JAI_CMN_RG_PERIOD_BALS_S.nextval
125 from dual;
126
127 ln_period_balance_id JAI_CMN_RG_PERIOD_BALS.period_balance_id%type;
128 ln_no_balances_updated number;
129
130 ln_cumulative_adjustment number := 0;
131
132 /* Start bgowrava for forward porting bug#5674376 */
133 ln_exc_edu_cess_cl_bal number;
134 ln_cvd_edu_cess_cl_bal number;
135 ln_prev_exc_edu_cess_cl_bal number;
136 ln_prev_cvd_edu_cess_cl_bal number;
137 ln_total_exc_edu_cess number;
138 ln_total_cvd_edu_cess number;
139 /*end bgowrava for forward porting bug#5674376 */
140 /*Bug 5989740 bduvarag start*/
141 ln_sh_exc_edu_cess_cl_bal number;
142 ln_sh_cvd_edu_cess_cl_bal number;
143 ln_prev_sh_exc_edu_cess_cl_bal number;
144 ln_prev_sh_cvd_edu_cess_cl_bal number;
145 ln_total_sh_exc_edu_cess number;
146 ln_total_sh_cvd_edu_cess number;
147 /*Bug 5989740 bduvarag end*/
148 BEGIN
149
150 lc_end_date := to_date(' 31/03/2003', 'dd/mm/yyyy'); -- File.Sql.35 by Brathod
151 p_consolidate_till := fnd_date.canonical_to_date(pv_consolidate_till); -- File.Sql.35 by Brathod
152
153 /*--------------------------------------------------------------------------------------------------------------------------------
154 Change History for Filename - jai_cmn_rg_period_bals_pkg.sql
155 S.No dd/mm/yyyy Author and Details
156 ----------------------------------------------------------------------------------------------------------------------------------
157 1 14/08/2004 Vijay Shankar for Bug# 3810344 Version : 115.1
158 Created the Package to populate data into JAI_CMN_RG_PERIOD_BALS
159 Huge Dependency
160 The Whole Patch should accompany this patch.
161
162 2 28/04/2005 rallamse for Bug#4336482, Version 116.1
163 For SEED there is a change in concurrent "JAINRGPB" to use FND_STANDARD_DATE with STANDARD_DATE format
164 Procedure ja_in_rg_period_balance_pkg.consolidate_balances signature modified by converting p_consolidate_till of DATE datatype
165 to pv_consolidate_till of varchar2 datatype. The varchar2 values are converted to DATE fromat
166 using fnd_date.canonical_to_date function.
167
168
169 3. 26-FEB-2007 SSAWANT , File version 120.2
170 Forward porting the change in 11.5 bug 5060037 to R12 bug no 5241875.
171 Issue:
172 The concurrent is poulating wrong balances when it is run for the first time.
173 Fix:
174 Consider the following case:
175 A first record created in JAI_CMN_RG_23AC_II_TRXS table is in 2nd August 2006.
176 Now if we run the RG23 part II report it would ask the user to run
177 the "India RG Period balance calculation" concurrent till July 31. If we run this concurrent
178 it would do the following steps:
179
180 It would fetch the previous balances which are NULL in our case.
181 Since these are NULL it would get the minimum of creation date using c_min_rg_date cursor.
182 In our case it would be 2nd august.
183 So it would get the sum of the transaction amounts in the month of august and populate
184 the balances for august which is wrong.
185 It should populate 0 balance for July in our case.
186 So added a check in cursor c_min_rg_date to fetch the minimum of creation date of only those
187 records which are created before the consolidate till date.
188 So in our case the minimum of creation date of only those records which are created before
189 31st July would be NULL and so the start date would be taken as 1 March 2003 and
190 end date as 31 March 2003 as per existing code. So the balances fetched would be zero for
191 July and would be populated as zero.
192
193 4. 28-FEB-2007 bgowrava for forward porting bug#5674376. File Version 120.3
194 Issue:
195 Rounding entries are not consolidated properly when generated in next month.
196 Fix:
197 In consolidate_balances procedure cursor is used to fetch all the rounding entries generated
198 before the consolidate till date. This is wrong as all the rounding entries should be consolidated
199 even if they are generated after the consolidate till date. So commented a condition in the
200 cursor to achieve this.
201
202 cbabu Implemented the functionality for CESS balances
203 - added required code in consolidate_balances and adjust_rounding
204 - New function get_cess_opening_balance is created. this is used in report for getting the balances
205
206
207 Dependancy due to this bug: Yes (introduced new columns for cess balances calculation)
208
209 --------------------------------------------------------------------------------------------------------------------------------*/
210
211 IF p_consolidate_till <> LAST_DAY(p_consolidate_till) THEN
212 FND_FILE.put_line( FND_FILE.log, 'Please enter the last day of month for RG Period Balance consolidation');
213 retcode := '2';
214 RETURN;
215 ELSIF p_consolidate_till >= trunc(sysdate) THEN
216 FND_FILE.put_line( FND_FILE.log, 'Consolidate Till value cannot be more than or equal to SYSTEM date');
217 retcode := '2';
218 RETURN;
219 END IF;
220
221 IF p_consolidate_till IS NULL THEN
222 ld_consolidate_till := to_date('31/03/2004', 'dd/mm/yyyy');
223 ELSE
224 ld_consolidate_till := p_consolidate_till;
225 END IF;
226
227 IF p_register_type = 'A' THEN
228 lv_register_type := 'RG23A';
229 ELSIF p_register_type = 'C' THEN
230 lv_register_type := 'RG23C';
231 ELSE
232 lv_register_type := 'XXX';
233 END IF;
234
235 FOR org IN (select organization_id, location_id
236 from JAI_CMN_INVENTORY_ORGS
237 where location_id > 0
238 order by organization_id, location_id)
239 LOOP
240
241 r_last_record := NULL;
242 ld_previous_end_date := NULL;
243 ln_previous_closing_bal := 0;
244
245 /* bgowrava for forward porting bug#5674376 */
246 ln_prev_exc_edu_cess_cl_bal := 0;
247 ln_prev_cvd_edu_cess_cl_bal := 0;
248 /*Bug 5989740 bduvarag*/
249 ln_prev_sh_exc_edu_cess_cl_bal := 0;
250 ln_prev_sh_cvd_edu_cess_cl_bal := 0;
251
252
253
254 OPEN c_last_record(org.organization_id, org.location_id, lv_register_type);
255 FETCH c_last_record INTO r_last_record;
256 CLOSE c_last_record;
257
258 IF r_last_record.opening_balance IS NULL THEN
259
260 OPEN c_min_rg_date(org.organization_id, org.location_id,
261 p_register_type,ld_consolidate_till);/*for bug 5241875*/
262 FETCH c_min_rg_date INTO ld_previous_end_date;
263 CLOSE c_min_rg_date;
264
265 IF ld_previous_end_date IS NOT NULL THEN
266 -- this will give 30-NOV-2004 if min(creation_date) is >= '1-DEC-2004' and <= '31-DEC-2004'
267 ld_previous_end_date := trunc(ld_previous_end_date, 'MM')-1;
268 ELSE
269 ld_previous_end_date := LC_END_DATE;
270 END IF;
271 ln_previous_closing_bal := 0;
272 /* bgowrava for forward porting bug#5674376 */
273 ln_prev_exc_edu_cess_cl_bal := 0;
274 ln_prev_cvd_edu_cess_cl_bal := 0;
275 /*Bug 5989740 bduvarag*/
276 ln_prev_sh_exc_edu_cess_cl_bal := 0;
277 ln_prev_sh_cvd_edu_cess_cl_bal := 0;
278
279
280 ELSIF r_last_record.end_date >= ld_consolidate_till THEN
281
282 FND_FILE.put_line( FND_FILE.log, 'Balances have already been consolidated till '||r_last_record.end_date
283 ||' for organization '||org.organization_id||' and location '||org.location_id
284 );
285 GOTO next_org;
286
287 ELSE
288
289 ld_previous_end_date := r_last_record.end_date;
290 ln_previous_closing_bal := r_last_record.closing_balance;
291 /* bgowrava for forward porting bug#5674376 */
292 ln_prev_exc_edu_cess_cl_bal := nvl(r_last_record.exc_edu_cess_cl_bal,0);
293 ln_prev_cvd_edu_cess_cl_bal := nvl(r_last_record.cvd_edu_cess_cl_bal,0);
294 /*Bug 5989740 bduvarag*/
295 ln_prev_sh_exc_edu_cess_cl_bal := nvl(r_last_record.sh_exc_edu_cess_cl_bal,0);
296 ln_prev_sh_cvd_edu_cess_cl_bal := nvl(r_last_record.sh_cvd_edu_cess_cl_bal,0);
297
298 END IF;
299
300 LOOP
301
302 ld_start_date := ld_previous_end_date + 1;
303 ld_end_date := last_day(ld_start_date);
304 ln_period_opening_bal := ln_previous_closing_bal;
305
306 ln_total_rg_amount := 0;
307 ln_fin_year := NULL;
308
309 OPEN c_total_rg_amount(org.organization_id, org.location_id, p_register_type, ld_start_date, ld_end_date);
310 FETCH c_total_rg_amount INTO ln_total_rg_amount, ln_fin_year;
311 CLOSE c_total_rg_amount;
312
313 ln_period_closing_bal := ln_period_opening_bal + nvl(ln_total_rg_amount, 0);
314
315 /* start, bgowrava for forward porting bug#5674376 */
316 if ln_fin_year is null then
317 OPEN c_get_fin_year(org.organization_id, ld_start_date);
318 FETCH c_get_fin_year INTO ln_fin_year;
319 CLOSE c_get_fin_year;
320 end if;
321
322 OPEN c_total_cess_amount(org.organization_id, org.location_id, p_register_type,
323 ld_start_date, ld_end_date, JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS);/*Bug 5989740 bduvarag*/
324 FETCH c_total_cess_amount INTO ln_total_exc_edu_cess;
325 CLOSE c_total_cess_amount;
326 ln_exc_edu_cess_cl_bal := ln_prev_exc_edu_cess_cl_bal + nvl(ln_total_exc_edu_cess, 0);
327
328 OPEN c_total_cess_amount(org.organization_id, org.location_id, p_register_type,
329 ld_start_date, ld_end_date, JAI_CONSTANTS.TAX_TYPE_CVD_EDU_CESS);/*Bug 5989740 bduvarag*/
330 FETCH c_total_cess_amount INTO ln_total_cvd_edu_cess;
331 CLOSE c_total_cess_amount;
332 ln_cvd_edu_cess_cl_bal := ln_prev_cvd_edu_cess_cl_bal + nvl(ln_total_cvd_edu_cess, 0);
333 /* end, bgowrava for forward porting bug#5674376 */
334 /*Bug 5989740 bduvarag start*/
335 OPEN c_total_cess_amount(org.organization_id, org.location_id, p_register_type,
336 ld_start_date, ld_end_date, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS);
337 FETCH c_total_cess_amount INTO ln_total_sh_exc_edu_cess;
338 CLOSE c_total_cess_amount;
339 ln_sh_exc_edu_cess_cl_bal := ln_prev_sh_exc_edu_cess_cl_bal + nvl(ln_total_sh_exc_edu_cess, 0);
340
341 OPEN c_total_cess_amount(org.organization_id, org.location_id, p_register_type,
345 ln_sh_cvd_edu_cess_cl_bal := ln_prev_sh_cvd_edu_cess_cl_bal + nvl(ln_total_sh_cvd_edu_cess, 0);
342 ld_start_date, ld_end_date, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS);
343 FETCH c_total_cess_amount INTO ln_total_sh_cvd_edu_cess;
344 CLOSE c_total_cess_amount;
346 /*Bug 5989740 bduvarag end*/
347 ln_period_balance_id:= null;
348 open c_get_period_balance_id;
349 fetch c_get_period_balance_id into ln_period_balance_id;
350 close c_get_period_balance_id;
351
352
353 insert into JAI_CMN_RG_PERIOD_BALS
354 (
355 period_balance_id,
356 organization_id,
357 location_id,
358 register_type,
359 start_date,
360 end_date,
361 fin_year,
362 opening_balance,
363 closing_balance,
364 misc_adjustment,
365 rounding_adjustment,
366 cumulative_misc_adjustment,
367 cumulative_rounding_adjustment,
368 creation_date,
369 created_by,
370 last_update_date,
371 last_updated_by,
372 last_update_login
373 /*following columns added by bgowrava for forward porting bug#5674376 */
374 , exc_edu_cess_cl_bal
375 , exc_edu_cess_adj
376 , exc_edu_cess_adj_op_bal
377 , cvd_edu_cess_cl_bal
378 , cvd_edu_cess_adj
379 , cvd_edu_cess_adj_op_bal
380 /*Bug 5989740 bduvarag*/
381 , sh_exc_edu_cess_cl_bal
382 , sh_exc_edu_cess_adj
383 , sh_exc_edu_cess_adj_op_bal
384 , sh_cvd_edu_cess_cl_bal
385 , sh_cvd_edu_cess_adj
386 , sh_cvd_edu_cess_adj_op_bal
387
388
389 )
390 values
391 (
392 ln_period_balance_id,
393 org.organization_id,
394 org.location_id,
395 lv_register_type,
396 ld_start_date,
397 ld_end_date,
398 ln_fin_year,
399 ln_period_opening_bal,
400 ln_period_closing_bal,
401 0,
402 0,
403 nvl(r_last_record.cumulative_misc_adjustment, 0)+ nvl(r_last_record.misc_adjustment, 0),
404 nvl(r_last_record.cumulative_rounding_adjustment, 0)+nvl(r_last_record.rounding_adjustment, 0),
405 sysdate,
406 fnd_global.user_id,
407 sysdate,
408 fnd_global.user_id,
409 fnd_global.login_id
410 /*following columns added by bgowrava for forward porting bug#5674376 */
411 , ln_exc_edu_cess_cl_bal
412 , 0
413 , nvl(r_last_record.exc_edu_cess_adj_op_bal,0) + nvl(r_last_record.exc_edu_cess_adj,0)
414 , ln_cvd_edu_cess_cl_bal
415 , 0
416 , nvl(r_last_record.cvd_edu_cess_adj_op_bal,0) + nvl(r_last_record.cvd_edu_cess_adj,0)
417 /*Bug 5989740 bduvarag*/
418 , ln_sh_exc_edu_cess_cl_bal
419 , 0
420 , nvl(r_last_record.sh_exc_edu_cess_adj_op_bal,0) + nvl(r_last_record.sh_exc_edu_cess_adj,0)
421 , ln_sh_cvd_edu_cess_cl_bal
422 , 0
423 , nvl(r_last_record.sh_cvd_edu_cess_adj_op_bal,0) + nvl(r_last_record.sh_cvd_edu_cess_adj,0)
424
425 );
426
427 -- Punch the PERIOD_BALANCE_ID aginst all the records that have been considered
428 -- for consolidation
429 update JAI_CMN_RG_23AC_II_TRXS
430 set period_balance_id = ln_period_balance_id
431 WHERE organization_id = org.organization_id
432 AND location_id = org.location_id
433 AND register_type = p_register_type
434 AND trunc(creation_date) between ld_start_date and ld_end_date
435 and period_balance_id is null
436 and inventory_item_id <> 0;
437
438
439 EXIT WHEN ld_end_date >= ld_consolidate_till;
440
441 ld_previous_end_date := ld_end_date;
442 ln_previous_closing_bal := ln_period_closing_bal;
443
444
445 /* bgowrava for forward porting bug#5674376 */
446 ln_prev_exc_edu_cess_cl_bal := ln_exc_edu_cess_cl_bal;
447 ln_prev_cvd_edu_cess_cl_bal := ln_cvd_edu_cess_cl_bal;
448 /*Bug 5989740 bduvarag*/
449 ln_prev_sh_exc_edu_cess_cl_bal := ln_sh_exc_edu_cess_cl_bal;
450 ln_prev_sh_cvd_edu_cess_cl_bal := ln_sh_cvd_edu_cess_cl_bal;
451
452 END LOOP;
453
454 <<next_org>>
455 NULL;
456
457 END LOOP;
458
459 -- loop thru all the rounding entries in the register that has not been consolidated.
460 for cur_rounding_rec in
461 (
462 select register_id
463 from JAI_CMN_RG_23AC_II_TRXS
464 where inventory_item_id = 0
465 and period_balance_id is null
466 and register_type = p_register_type /* added by bgowrava for forward porting bug#5674376*/
467 --and trunc(creation_date) <= ld_consolidate_till /*commented by bgowrava for forward porting bug#5674376 */
468 )
469 loop
470
471 /* Call the rounding adjustment proc for each of the rounding */
472 ln_period_balance_id := null;
473 ln_no_balances_updated := null;
474
475 adjust_rounding
476 (
477 p_register_id_rounding => cur_rounding_rec.register_id,
478 p_period_balance_id => ln_period_balance_id,
479 p_no_balances_updated => ln_no_balances_updated
480 );
481
482 end loop;
483
484 /* Added by Ramananda for bug#4407165 */
485 EXCEPTION
486 WHEN OTHERS THEN
487 errbuf := sqlerrm;
488 retcode := null;
489 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
493 END consolidate_balances;
490 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
491 app_exception.raise_exception;
492
494
495 /* ****************************************************************************************** */
496
497 procedure adjust_rounding
498 (
499 p_register_id_rounding in number,
500 p_period_balance_id OUT NOCOPY number,
501 p_no_balances_updated OUT NOCOPY number
502 )
503 is
504
505 /* Added by Ramananda for bug#4407165 */
506 lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_period_bals_pkg.adjust_rounding';
507
508 cursor c_get_parent_balance_id (cp_register_id number) is
509 select period_balance_id
510 from JAI_CMN_RG_23AC_II_TRXS
511 where register_id = cp_register_id;
512
513
514 cursor c_get_round_amount(cp_register_id number) is
515 /* bgowrava for forward porting bug#5674376. additional_cvd column added */
516 select (nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)+ nvl(cr_additional_cvd,0)
517 - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0) - nvl(dr_additional_cvd,0)) rounding_amount
518 from JAI_CMN_RG_23AC_II_TRXS
519 where register_id = cp_register_id;
520
521 cursor c_get_start_balance_detail(cp_starting_period_balance_id number) is
522 select organization_id, location_id, register_type, end_date
523 from JAI_CMN_RG_PERIOD_BALS
524 where period_balance_id = cp_starting_period_balance_id;
525
526 r_get_start_balance_detail c_get_start_balance_detail%rowtype;
527 ln_parent_register_id JAI_CMN_RG_23AC_II_TRXS.register_id%type;
528 ln_parent_period_balance_id JAI_CMN_RG_PERIOD_BALS.period_balance_id%type;
529 lv_error_flag varchar(1); -- := 'N' -- File.Sql.35 by Brathod
530 lv_error_message varchar2(100);
531 ln_round_amount number;
532
533 /* bgowrava for forward porting bug#5674376 */
534 ln_exc_edu_cess_adj number;
535 ln_cvd_edu_cess_adj number;
536 cursor c_get_cess_rnd_amount(cp_register_id number, cp_tax_type varchar2) is
537 select nvl(credit,0) - nvl(debit,0)
538 from JAI_CMN_RG_OTHERS
539 where source_register_id = cp_register_id
540 and source_type = 1
541 and tax_type = cp_tax_type;
542 /*Bug 5989740 bduvarag*/
543 ln_sh_exc_edu_cess_adj number;
544 ln_sh_cvd_edu_cess_adj number;
545
546
547 begin
548 lv_error_flag := 'N'; -- File.Sql.35 by Brathod
549 ln_parent_register_id := null;
550 ln_parent_register_id :=
551 jai_rcv_rnd_pkg.get_parent_register_id(p_register_id_rounding);
552
553 if ln_parent_register_id is null then
554 lv_error_message := 'Parent register id not found, cannot proceed';
555 lv_error_flag:='Y';
556 fnd_file.put_line(fnd_file.log, lv_error_message);
557 goto exit_adjust_rounding;
558 end if;
559
560 open c_get_parent_balance_id(ln_parent_register_id);
561 fetch c_get_parent_balance_id into ln_parent_period_balance_id;
562 close c_get_parent_balance_id;
563
564 if ln_parent_period_balance_id is null then
565 lv_error_message := 'Parent has not been consolidated, cannot proceed';
566 lv_error_flag:='Y';
567 fnd_file.put_line(fnd_file.log, lv_error_message);
568 goto exit_adjust_rounding;
569 end if;
570
571 /* get the round amount */
572 open c_get_round_amount(p_register_id_rounding);
573 fetch c_get_round_amount into ln_round_amount;
574 close c_get_round_amount;
575
576 /* Start bgowrava for forward porting bug#5674376 */
577 ln_round_amount := nvl(ln_round_amount,0);
578
579 open c_get_cess_rnd_amount(p_register_id_rounding,JAI_CONSTANTS.TAX_TYPE_EXC_EDU_CESS);/*Bug 5989740 bduvarag*/
580 fetch c_get_cess_rnd_amount into ln_exc_edu_cess_adj;
581 close c_get_cess_rnd_amount;
582
583 open c_get_cess_rnd_amount(p_register_id_rounding, JAI_CONSTANTS.TAX_TYPE_CVD_EDU_CESS);/*Bug 5989740 bduvarag*/
584 fetch c_get_cess_rnd_amount into ln_cvd_edu_cess_adj;
585 close c_get_cess_rnd_amount;
586
587 ln_exc_edu_cess_adj := nvl(ln_exc_edu_cess_adj,0);
588 ln_cvd_edu_cess_adj := nvl(ln_cvd_edu_cess_adj,0);
589 /* End bgowrava for forward porting bug#5674376 */
590 /*Bug 5989740 bduvarag start*/
591 open c_get_cess_rnd_amount(p_register_id_rounding, JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS);
592 fetch c_get_cess_rnd_amount into ln_sh_exc_edu_cess_adj;
593 close c_get_cess_rnd_amount;
594
595 open c_get_cess_rnd_amount(p_register_id_rounding, JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS);
596 fetch c_get_cess_rnd_amount into ln_sh_cvd_edu_cess_adj;
597 close c_get_cess_rnd_amount;
598
599 ln_sh_exc_edu_cess_adj := nvl(ln_sh_exc_edu_cess_adj,0);
600 ln_sh_cvd_edu_cess_adj := nvl(ln_sh_cvd_edu_cess_adj,0);
601 /*Bug 5989740 bduvarag end*/
602 if ln_round_amount = 0
603 /* following cess conditions added by bgowrava for forward porting bug#5674376 */
604 and ln_exc_edu_cess_adj = 0
605 and ln_cvd_edu_cess_adj = 0
606 /*Bug 5989740 bduvarag*/
607 and ln_sh_exc_edu_cess_adj = 0
608 and ln_sh_cvd_edu_cess_adj = 0
609
610 then
611 /* There is no rounding amount, no need to adjust */
612 fnd_file.put_line(fnd_file.log, '0 Zero rounding');
613 goto exit_adjust_rounding;
614 end if;
615
616 /* update the parent_balance record */
617 update JAI_CMN_RG_PERIOD_BALS
618 set rounding_adjustment = nvl(rounding_adjustment, 0) + ln_round_amount
619 , exc_edu_cess_adj = nvl(exc_edu_cess_adj, 0) + ln_exc_edu_cess_adj -- bgowrava for forward porting bug#5674376
623
620 , cvd_edu_cess_adj = nvl(cvd_edu_cess_adj, 0) + ln_cvd_edu_cess_adj -- bgowrava for forward porting bug#5674376
621 , sh_exc_edu_cess_adj = nvl(sh_exc_edu_cess_adj, 0) + ln_sh_exc_edu_cess_adj /*Bug 5989740 bduvarag*/
622 , sh_cvd_edu_cess_adj = nvl(sh_cvd_edu_cess_adj, 0) + ln_sh_cvd_edu_cess_adj /*Bug 5989740 bduvarag*/
624 where period_balance_id = ln_parent_period_balance_id;
625
626 /* punch the balance id in the register rounding record */
627 update JAI_CMN_RG_23AC_II_TRXS
628 set period_balance_id = ln_parent_period_balance_id
629 where register_id = p_register_id_rounding;
630
631 /* update all subsequent balance records */
632 open c_get_start_balance_detail(ln_parent_period_balance_id);
633 fetch c_get_start_balance_detail into r_get_start_balance_detail;
634 close c_get_start_balance_detail;
635
636 update JAI_CMN_RG_PERIOD_BALS
637 set cumulative_rounding_adjustment =
638 nvl(cumulative_rounding_adjustment, 0) + ln_round_amount
639 -- bgowrava for forward porting bug#5674376
640 , exc_edu_cess_adj_op_bal = nvl(exc_edu_cess_adj_op_bal, 0) + ln_exc_edu_cess_adj
641 , cvd_edu_cess_adj_op_bal = nvl(cvd_edu_cess_adj_op_bal, 0) + ln_cvd_edu_cess_adj
642 , sh_exc_edu_cess_adj_op_bal = nvl(sh_exc_edu_cess_adj_op_bal, 0) + ln_sh_exc_edu_cess_adj/*Bug 5989740 bduvarag*/
643 , sh_cvd_edu_cess_adj_op_bal = nvl(sh_cvd_edu_cess_adj_op_bal, 0) + ln_sh_cvd_edu_cess_adj/*Bug 5989740 bduvarag*/
644
645 where organization_id = r_get_start_balance_detail.organization_id
646 and location_id = r_get_start_balance_detail.location_id
647 and register_type = r_get_start_balance_detail.register_type
648 and start_date > r_get_start_balance_detail.end_date;
649
650 << exit_adjust_rounding >>
651 return;
652
653 /* Added by Ramananda for bug#4407165 */
654 EXCEPTION
655 WHEN OTHERS THEN
656 p_period_balance_id := null;
657 p_no_balances_updated := null;
658 FND_MESSAGE.SET_NAME('JA','JAI_EXCEPTION_OCCURED');
659 FND_MESSAGE.SET_TOKEN('JAI_PROCESS_MSG', lv_object_name ||'. Err:'||sqlerrm );
660 app_exception.raise_exception;
661
662 end adjust_rounding;
663
664 /* function created by bgowrava for forward porting bug#5674376 */
665 function get_cess_opening_balance(
666 cp_organization_id in number,
667 cp_location_id in number,
668 cp_register_type in varchar2,
669 cp_period_start_date in date,
670 cp_tax_type in varchar2
671 ) return number is
672
673 /* get the period balance record of previous period to know the opening cess balances */
674 cursor c_period_balance_record is
675 select *
676 from JAI_CMN_RG_PERIOD_BALS
677 where organization_id = cp_organization_id
678 and location_id = cp_location_id
679 and register_type = cp_register_type
680 and end_date = cp_period_start_date-1;
681 r_period_bal_rec JAI_CMN_RG_PERIOD_BALS%rowtype;
682
683 ln_cess_op_bal number;
684 ln_sh_cess_op_bal number; /*Bug 5989740 bduvarag*/
685
686 begin
687
688 open c_period_balance_record;
689 fetch c_period_balance_record into r_period_bal_rec;
690 close c_period_balance_record;
691
692 if r_period_bal_rec.period_balance_id is null then
693 ln_cess_op_bal := 0;
694 else
695 if cp_tax_type = 'EXCISE_EDUCATION_CESS' then
696 ln_cess_op_bal := r_period_bal_rec.exc_edu_cess_cl_bal;
697 elsif cp_tax_type = 'CVD_EDUCATION_CESS' then
698 ln_cess_op_bal := r_period_bal_rec.cvd_edu_cess_cl_bal;
699 /*Bug 5989740 bduvarag start*/
700 elsif cp_tax_type = JAI_CONSTANTS.TAX_TYPE_SH_EXC_EDU_CESS then
701 ln_cess_op_bal := r_period_bal_rec.sh_exc_edu_cess_cl_bal;
702 elsif cp_tax_type = JAI_CONSTANTS.TAX_TYPE_SH_CVD_EDU_CESS then
703 ln_cess_op_bal := r_period_bal_rec.sh_cvd_edu_cess_cl_bal;
704 /*Bug 5989740 bduvarag end*/
705 else
706 ln_cess_op_bal := 0;
707 end if;
708 end if;
709
710 return ln_cess_op_bal;
711
712 end get_cess_opening_balance;
713
714 end jai_cmn_rg_period_bals_pkg;
[Home] [Help]