[Home] [Help]
PACKAGE BODY: APPS.JAI_EXCISE_SCRIPTS_PKG
Source
1 PACKAGE BODY jai_excise_scripts_pkg AS
2 /* $Header: jaiexscr.plb 120.2.12000000.2 2007/10/25 02:31:04 rallamse noship $ */
3
4 gd_date date ;
5 gn_exists number ;
6 gn_action NUMBER;
7
8 -----------------------------------------GET_PREV_CESS_RG_BAL--------------------------------
9
10 PROCEDURE get_prev_cess_rg_bal( p_organization_id IN NUMBER,
11 p_location_id IN NUMBER,
12 p_register_type IN VARCHAR2,
13 p_tax_type IN VARCHAR2,
14 p_fin_year IN OUT NOCOPY NUMBER,
15 p_slno IN OUT NOCOPY NUMBER,
16 p_bal OUT NOCOPY NUMBER)
17 IS
18
19 CURSOR get_rg23_prev_slno IS
20 SELECT max(slno)
21 FROM JAI_CMN_RG_23AC_II_TRXS jrg
22 WHERE organization_id = p_organization_id
23 AND location_id = p_location_id
24 AND register_type = p_register_type
25 AND fin_year = p_fin_year
26 AND slno < p_slno
27 AND EXISTS ( SELECT 1
28 FROM JAI_CMN_RG_OTHERS
29 WHERE source_type = 1
30 AND tax_type = p_tax_type
31 AND source_register_id = jrg.register_id );
32
33 CURSOR get_rg23_prev_fin_slno IS
34 SELECT max(slno)
35 FROM JAI_CMN_RG_23AC_II_TRXS jrg
36 WHERE organization_id = p_organization_id
37 AND location_id = p_location_id
38 AND register_type = p_register_type
39 AND fin_year = p_fin_year - 1
40 AND exists ( SELECT 1
41 FROM JAI_CMN_RG_OTHERS
42 WHERE source_type = 1
43 AND tax_type = p_tax_type
44 AND source_register_id = jrg.register_id );
45
46 CURSOR get_rg23_balance( cp_fin_year NUMBER,cp_slno NUMBER ) IS
47 SELECT closing_balance
48 FROM JAI_CMN_RG_OTHERS
49 WHERE source_type = 1
50 AND tax_type = p_tax_type
51 AND source_register_id = ( SELECT register_id
52 FROM JAI_CMN_RG_23AC_II_TRXS
53 WHERE organization_id = p_organization_id
54 AND location_id = p_location_id
55 AND register_type = p_register_type
56 AND fin_year = p_fin_year
57 AND slno = cp_slno) ;
58
59 CURSOR get_pla_prev_slno IS
60 SELECT max(slno)
61 FROM JAI_CMN_RG_PLA_TRXS jpl
62 WHERE organization_id = p_organization_id
63 AND location_id = p_location_id
64 AND fin_year = p_fin_year
65 AND slno < p_slno
66 AND EXISTS ( SELECT 1
67 FROM JAI_CMN_RG_OTHERS
68 WHERE source_type = 2
69 AND tax_type = p_tax_type
70 AND source_register_id = jpl.register_id );
71
72
73 CURSOR get_pla_prev_fin_slno IS
74 SELECT max(slno)
75 FROM JAI_CMN_RG_PLA_TRXS jpl
76 WHERE organization_id = p_organization_id
77 AND location_id = p_location_id
78 AND fin_year = p_fin_year - 1
79 AND EXISTS ( SELECT 1
80 FROM JAI_CMN_RG_OTHERS
81 WHERE source_type = 2
82 AND tax_type = p_tax_type
83 AND source_register_id = jpl.register_id );
84
85 CURSOR get_pla_balance( cp_fin_year NUMBER,cp_slno NUMBER ) IS
86 SELECT closing_balance
87 FROM JAI_CMN_RG_OTHERS
88 WHERE source_type = 2
89 AND tax_type = p_tax_type
90 AND source_register_id = ( SELECT register_id
91 FROM JAI_CMN_RG_PLA_TRXS
92 WHERE organization_id = p_organization_id
93 AND location_id = p_location_id
94 AND fin_year = p_fin_year
95 AND slno = cp_slno );
96
97 ln_slno NUMBER;
98
99 BEGIN
100
101 IF p_register_type IN ( 'A','C') THEN
102
103 OPEN get_rg23_prev_slno;
104 FETCH get_rg23_prev_slno INTO ln_slno;
105 CLOSE get_rg23_prev_slno;
106
107 IF ln_slno IS NULL THEN
108
109 OPEN get_rg23_prev_fin_slno;
110 FETCH get_rg23_prev_fin_slno INTO ln_slno;
111 CLOSE get_rg23_prev_fin_slno;
112
113 p_fin_year := p_fin_year - 1;
114
115 END IF;
116
117 OPEN get_rg23_balance(p_fin_year,ln_slno);
118 FETCH get_rg23_balance INTO p_bal;
119 CLOSE get_rg23_balance;
120
121 ELSIF p_register_type = 'PLA' THEN
122
123 OPEN get_pla_prev_slno;
124 FETCH get_pla_prev_slno INTO ln_slno;
125 CLOSE get_pla_prev_slno;
126
127 IF ln_slno IS NULL THEN
128
129 OPEN get_pla_prev_fin_slno;
130 FETCH get_pla_prev_fin_slno INTO ln_slno;
131 CLOSE get_pla_prev_fin_slno;
132
133 p_fin_year := p_fin_year - 1;
134
135 END IF;
136
137 OPEN get_pla_balance(p_fin_year,ln_slno);
138 FETCH get_pla_balance INTO p_bal;
139 CLOSE get_pla_balance;
140
141 END IF;
142
143 p_slno := ln_slno;
144
145 END get_prev_cess_rg_bal;
146
147 -----------------------------------------GET_PREV_CESS_RG_BAL--------------------------------
148
149 -----------------------------------------GET_PREV_RG_BAL--------------------------------
150
151 PROCEDURE get_prev_rg_bal( p_organization_id IN NUMBER,
152 p_location_id IN NUMBER,
153 p_register_type IN VARCHAR2,
154 p_fin_year IN OUT NOCOPY NUMBER,
155 p_slno IN OUT NOCOPY NUMBER,
156 p_bal OUT NOCOPY NUMBER)
157 IS
158
159 CURSOR get_rg23_prev_slno IS
160 SELECT max(slno)
161 FROM JAI_CMN_RG_23AC_II_TRXS
162 WHERE organization_id = p_organization_id
163 AND location_id = p_location_id
164 AND register_type = p_register_type
165 AND fin_year = p_fin_year
166 AND slno < p_slno ;
167
168 CURSOR get_rg23_prev_fin_slno IS
169 SELECT max(slno)
170 FROM JAI_CMN_RG_23AC_II_TRXS
171 WHERE organization_id = p_organization_id
172 AND location_id = p_location_id
173 AND register_type = p_register_type
174 AND fin_year = p_fin_year - 1;
175
176 CURSOR get_rg23_balance( cp_fin_year NUMBER,cp_slno NUMBER ) IS
177 SELECT closing_balance
178 FROM JAI_CMN_RG_23AC_II_TRXS
179 WHERE organization_id = p_organization_id
180 AND location_id = p_location_id
181 AND register_type = p_register_type
182 AND fin_year = p_fin_year
183 AND slno = cp_slno ;
184
185
186 CURSOR get_pla_prev_slno IS
187 SELECT max(slno)
188 FROM JAI_CMN_RG_PLA_TRXS
189 WHERE organization_id = p_organization_id
190 AND location_id = p_location_id
191 AND fin_year = p_fin_year
192 AND slno < p_slno ;
193
194 CURSOR get_pla_prev_fin_slno IS
195 SELECT max(slno)
196 FROM JAI_CMN_RG_PLA_TRXS
197 WHERE organization_id = p_organization_id
198 AND location_id = p_location_id
199 AND fin_year = p_fin_year - 1;
200
201 CURSOR get_pla_balance( cp_fin_year NUMBER,cp_slno NUMBER ) IS
202 SELECT closing_balance
203 FROM JAI_CMN_RG_PLA_TRXS
204 WHERE organization_id = p_organization_id
205 AND location_id = p_location_id
206 AND fin_year = p_fin_year
207 AND slno = cp_slno ;
208
209 ln_slno NUMBER;
210
211 BEGIN
212 p_bal := 0 ;
213
214 IF p_register_type IN ( 'A','C') THEN
215
216 OPEN get_rg23_prev_slno;
217 FETCH get_rg23_prev_slno INTO ln_slno;
218 CLOSE get_rg23_prev_slno;
219
220 IF ln_slno IS NULL THEN
221
222 OPEN get_rg23_prev_fin_slno;
223 FETCH get_rg23_prev_fin_slno INTO ln_slno;
224 CLOSE get_rg23_prev_fin_slno;
225
226 p_fin_year := p_fin_year - 1;
227
228 END IF;
229
230 OPEN get_rg23_balance(p_fin_year,ln_slno);
231 FETCH get_rg23_balance INTO p_bal;
232 CLOSE get_rg23_balance;
233
234 ELSIF p_register_type = 'PLA' THEN
235
236 OPEN get_pla_prev_slno;
237 FETCH get_pla_prev_slno INTO ln_slno;
238 CLOSE get_pla_prev_slno;
239
240 IF ln_slno IS NULL THEN
241
242 OPEN get_pla_prev_fin_slno;
243 FETCH get_pla_prev_fin_slno INTO ln_slno;
244 CLOSE get_pla_prev_fin_slno;
245
246 p_fin_year := p_fin_year - 1;
247
248 END IF;
249
250 OPEN get_pla_balance(p_fin_year,ln_slno);
251 FETCH get_pla_balance INTO p_bal;
252 CLOSE get_pla_balance;
253
254 END IF;
255
256 p_slno := ln_slno;
257
258 END get_prev_rg_bal;
259
260 -----------------------------------------GET_PREV_RG_BAL--------------------------------
261 -----------------------------------------REMOVE_DUP_SLNO--------------------------------
262
263 PROCEDURE remove_dup_slno( p_organization_id NUMBER ,
264 p_location_id NUMBER ,
265 p_fin_year NUMBER ,
266 p_register_type VARCHAR2 ,
267 p_slno NUMBER ,
268 p_dup_cnt NUMBER )
269 IS
270 ln_cnt NUMBER;
271
272 BEGIN
273
274 IF p_register_type IN ('A','C') THEN
275
276 UPDATE JAI_CMN_RG_23AC_II_TRXS
277 SET slno = slno + p_dup_cnt - 1
278 WHERE organization_id = p_organization_id
279 AND location_id = p_location_id
280 AND register_type = p_register_type
281 AND fin_year = p_fin_year
282 AND slno > p_slno;
283
284 UPDATE JAI_CMN_RG_SLNOS
285 SET slno = slno + p_dup_cnt - 1
286 WHERE organization_id = p_organization_id
287 AND location_id = p_location_id
288 AND register_type = p_register_type
289 AND current_fin_year = p_fin_year;
290
291 ln_cnt := 0;
292 FOR dup_rec in ( SELECT *
293 FROM JAI_CMN_RG_23AC_II_TRXS
294 WHERE organization_id = p_organization_id
295 AND location_id = p_location_id
296 AND register_type = p_register_type
297 AND fin_year = p_fin_year
298 AND slno = p_slno
299 ORDER BY register_id ) LOOP
300
301
302 UPDATE JAI_CMN_RG_23AC_II_TRXS
303 SET slno = slno + ln_cnt
304 WHERE register_id = dup_rec.register_id;
305
306 ln_cnt := ln_cnt + 1;
307
308 END LOOP;
309
310 ELSIF p_register_type = 'PLA' THEN
311
312 UPDATE JAI_CMN_RG_PLA_TRXS
313 SET slno = slno + p_dup_cnt - 1
314 WHERE organization_id = p_organization_id
315 AND location_id = p_location_id
316 AND fin_year = p_fin_year
317 AND slno > p_slno;
318
319 UPDATE JAI_CMN_RG_SLNOS
320 SET slno = slno + p_dup_cnt - 1
321 WHERE organization_id = p_organization_id
322 AND location_id = p_location_id
323 AND register_type = p_register_type
324 AND current_fin_year = p_fin_year;
325
326
327 ln_cnt := 0;
328 FOR dup_rec in ( SELECT *
329 FROM JAI_CMN_RG_PLA_TRXS
330 WHERE organization_id = p_organization_id
331 AND location_id = p_location_id
332 AND fin_year = p_fin_year
333 AND slno = p_slno
334 ORDER BY register_id ) LOOP
335
336 UPDATE JAI_CMN_RG_PLA_TRXS
337 SET slno = slno + ln_cnt
338 WHERE register_id = dup_rec.register_id;
339
340 ln_cnt := ln_cnt + 1;
341
342 END LOOP;
343
344 END IF;
345
346 END remove_dup_slno;
347
348 -----------------------------------------REMOVE_DUP_SLNO--------------------------------
349
350 -----------------------------------------UPD_PERIOD_BALANCES--------------------------------
351
352 PROCEDURE upd_period_balances( p_organization_id NUMBER,
353 p_location_id NUMBER,
354 p_register_type VARCHAR2,
355 p_start_date DATE,
356 p_err_msg OUT NOCOPY VARCHAR2,
357 p_ret_code OUT NOCOPY NUMBER)
358 IS
359 BEGIN
360
361 DELETE JAI_CMN_RG_PERIOD_BALS
362 WHERE organization_id = p_organization_id
363 AND location_id = p_location_id
364 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
365 AND start_date >= p_start_date;
366
367 UPDATE JAI_CMN_RG_23AC_II_TRXS
368 SET period_balance_id = NULL
369 WHERE organization_id = p_organization_id
370 AND location_id = p_location_id
371 AND register_type = p_register_type
372 AND trunc(creation_date) >= p_start_date;
373
374 jai_cmn_rg_period_bals_pkg.consolidate_balances( p_err_msg,
375 p_ret_code ,
376 NULL,
377 p_register_type,
378 trunc(last_day(add_months(sysdate,-1))));
379
380 END upd_period_balances;
381
382 -----------------------------------------UPD_PERIOD_BALANCES--------------------------------
383
384
385 -----------------------------------------VALIDATE_PERIOD_BALANCES--------------------------------
386 PROCEDURE validate_period_balances( p_organization_id NUMBER,
387 p_location_id NUMBER,
388 p_register_type VARCHAR2,
389 p_date DATE)
390 IS
391 CURSOR cur_get_tot_amt( cp_start_date DATE,cp_end_date DATE ) IS
392 SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
393 - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
394 FROM JAI_CMN_RG_23AC_II_TRXS
395 WHERE organization_id = p_organization_id
396 AND location_id = p_location_id
397 AND register_type = p_register_type
398 AND trunc(creation_date) between cp_start_date and cp_end_date
399 AND inventory_item_id <> 0;
400
401 CURSOR cur_get_period_balance( cp_start_date DATE,cp_end_date DATE ) IS
402 SELECT closing_balance - opening_balance
403 FROM JAI_CMN_RG_PERIOD_BALS
404 WHERE organization_id = p_organization_id
405 AND location_id = p_location_id
406 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
407 AND start_date = cp_start_date
408 AND end_date = cp_end_date;
409
410
411 CURSOR c_total_cess_amount(cp_start_date in date, cp_end_date in date, cp_tax_type in varchar2) IS
412 SELECT sum(nvl(b.credit,0) - nvl(b.debit,0)) total_cess
413 FROM JAI_CMN_RG_23AC_II_TRXS a, JAI_CMN_RG_OTHERS b
414 WHERE a.organization_id = p_organization_id
415 AND a.location_id = p_location_id
416 AND a.register_type = p_register_type
417 AND trunc(a.creation_date) between cp_start_date and cp_end_date
418 AND a.inventory_item_id <> 0
419 AND b.source_register_id = a.register_id
420 AND b.source_type = 1
421 AND b.tax_type = cp_tax_type;
422
423 CURSOR cur_get_cess_period_bal(cp_start_date in date, cp_end_date in date) IS
424 SELECT exc_edu_cess_cl_bal,
425 cvd_edu_cess_cl_bal,
426 sh_exc_edu_cess_cl_bal,
427 sh_cvd_edu_cess_cl_bal
428 FROM JAI_CMN_RG_PERIOD_BALS
429 WHERE organization_id = p_organization_id
430 AND location_id = p_location_id
431 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
432 AND start_date = cp_start_date
433 AND end_date = cp_end_date;
434
435 ln_prev_cess_cl_bal NUMBER;
436 ln_cess_cl_bal NUMBER;
437 ln_cess_amount NUMBER;
438 r_prev_cess_period_bal cur_get_cess_period_bal%ROWTYPE;
439 r_cess_period_bal cur_get_cess_period_bal%ROWTYPE;
440 lv_tax_type VARCHAR2(30);
441
442 ld_start_date DATE;
443 ld_end_date DATE;
444 ln_tot_amt NUMBER;
445 ln_period_bal NUMBER;
446 lv_err_msg VARCHAR2(4000);
447 ln_ret_code NUMBER;
448
449
450
451 BEGIN
452 ld_start_date := to_date(01||'-'||to_char(p_date,'MON')||'-'||to_char(p_date,'YYYY'),'DD-MM-YYYY');
453 ld_end_date := last_day(p_date);
454
455 LOOP
456
457 ln_tot_amt := NULL;
458 ln_period_bal := NULL;
459
460 OPEN cur_get_tot_amt(ld_start_date,ld_end_date);
461 FETCH cur_get_tot_amt INTO ln_tot_amt;
462 CLOSE cur_get_tot_amt;
463
464 OPEN cur_get_period_balance(ld_start_date,ld_end_date);
465 FETCH cur_get_period_balance INTO ln_period_bal;
466 IF cur_get_period_balance%NOTFOUND THEN
467 exit;
468 END IF;
469 CLOSE cur_get_period_balance;
470
471 IF ln_tot_amt <> ln_period_bal THEN
472 IF gn_action = 3 THEN
473 upd_period_balances( p_organization_id => p_organization_id,
474 p_location_id => p_location_id,
475 p_register_type => p_register_type,
476 p_start_date => ld_start_date,
477 p_err_msg => lv_err_msg,
478 p_ret_code => ln_ret_code);
479 END IF;
480
481 capture_error( p_organization_id => p_organization_id ,
482 p_location_id => p_location_id ,
483 p_register_type => p_register_type ,
484 p_fin_year => null ,
485 p_opening_balance => null ,
486 p_error_codes => 'E01' ,
487 p_slno => null ,
488 p_register_id => null ,
489 p_rowcount => null ,
490 p_tax_type => null ,
491 p_date => null ,
492 p_month => to_char(ld_start_date,'MONTH') ,
493 p_year => to_number(to_char(ld_start_date,'YYYY'))
494 ) ;
495 IF gn_action = 3 THEN
496 return; END IF;
497
498 END IF;
499
500
501 OPEN cur_get_cess_period_bal( add_months(ld_start_date,-1),add_months(ld_end_date,-1) );
502 FETCH cur_get_cess_period_bal INTO r_prev_cess_period_bal;
503 CLOSE cur_get_cess_period_bal;
504
505 OPEN cur_get_cess_period_bal( ld_start_date,ld_end_date );
506 FETCH cur_get_cess_period_bal INTO r_cess_period_bal;
507 CLOSE cur_get_cess_period_bal;
508
509 FOR tax in 1..4 LOOP
510
511 ln_prev_cess_cl_bal := 0;
512 ln_cess_cl_bal := 0;
513 ln_cess_amount := 0;
514
515 IF tax = 1 THEN
516
517 lv_tax_type := jai_constants.tax_type_exc_edu_cess ;
518 ln_prev_cess_cl_bal := nvl(r_prev_cess_period_bal.exc_edu_cess_cl_bal,0);
519 ln_cess_cl_bal := nvl(r_cess_period_bal.exc_edu_cess_cl_bal,0);
520
521 ELSIF tax = 2 THEN
522
523 lv_tax_type := jai_constants.tax_type_cvd_edu_cess ;
524 ln_prev_cess_cl_bal := nvl(r_prev_cess_period_bal.cvd_edu_cess_cl_bal,0);
525 ln_cess_cl_bal := nvl(r_cess_period_bal.cvd_edu_cess_cl_bal,0);
526
527 ELSIF tax = 3 THEN
528
529 lv_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
530 ln_prev_cess_cl_bal := nvl(r_prev_cess_period_bal.sh_exc_edu_cess_cl_bal,0);
531 ln_cess_cl_bal := nvl(r_cess_period_bal.sh_exc_edu_cess_cl_bal,0);
532
533 ELSIF tax = 4 THEN
534
535 lv_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
536 ln_prev_cess_cl_bal := nvl(r_prev_cess_period_bal.sh_cvd_edu_cess_cl_bal,0);
537 ln_cess_cl_bal := nvl(r_cess_period_bal.sh_cvd_edu_cess_cl_bal,0);
538
539 END IF;
540
541 OPEN c_total_cess_amount( ld_start_date,ld_end_date,lv_tax_type );
542 FETCH c_total_cess_amount INTO ln_cess_amount;
543 CLOSE c_total_cess_amount;
544
545
546 IF nvl(ln_cess_amount,0) + nvl(ln_prev_cess_cl_bal,0) <> nvl(ln_cess_cl_bal,0) THEN
547
548 IF gn_action = 3 THEN
549 upd_period_balances( p_organization_id => p_organization_id,
550 p_location_id => p_location_id,
551 p_register_type => p_register_type,
552 p_start_date => ld_start_date,
553 p_err_msg => lv_err_msg,
554 p_ret_code => ln_ret_code);
555 END IF;
556
557 capture_error( p_organization_id => p_organization_id ,
558 p_location_id => p_location_id ,
559 p_register_type => p_register_type ,
560 p_fin_year => null ,
561 p_opening_balance => null ,
562 p_error_codes => 'E01' ,
563 p_slno => null ,
564 p_register_id => null ,
565 p_rowcount => null ,
566 p_tax_type => lv_tax_type ,
567 p_date => null ,
568 p_month => to_char(ld_start_date,'MONTH') ,
569 p_year => to_number(to_char(ld_start_date,'YYYY'))
570 ) ;
571 IF gn_action = 3 THEN
572 return;
573 END IF;
574
575 END IF;
576
577 END LOOP;
578
579 ld_start_date := to_date(01||'-'||to_char(add_months(ld_start_date,1),'MON')||'-'||to_char(add_months(ld_start_date,1),'YYYY'),'DD-MM-YYYY');
580 ld_end_date := last_day ( ld_start_date );
581 exit when ld_start_date > trunc(sysdate);
582
583 END LOOP;
584
585 END validate_period_balances;
586 -----------------------------------------VALIDATE_PERIOD_BALANCES--------------------------------
587 -----------------------------------------CORR_OTH_BALANCES--------------------------------
588
589 PROCEDURE corr_oth_balances
590 (
591 p_organization_id NUMBER,
592 p_location_id NUMBER,
593 p_fin_year NUMBER,
594 p_register_type VARCHAR2,
595 p_slno NUMBER,
596 p_tax_type VARCHAR2,
597 p_last_updated_by NUMBER
598 )
599 IS
600 CURSOR cur_rg23_next_records IS
601 SELECT jrg.opening_balance ,
602 jrg.closing_balance ,
603 nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
604 jrg.last_updated_by,
605 jrg.last_update_date,
606 jrg.rg_other_id,
607 rg23.slno,
608 rg23.organization_id,
609 rg23.location_id
610 FROM JAI_CMN_RG_OTHERS jrg,
611 JAI_CMN_RG_23AC_II_TRXS rg23
612 WHERE organization_id = p_organization_id
613 AND location_id = p_location_id
614 AND ((fin_year = p_fin_year
615 AND slno >= p_slno)
616 OR fin_year > p_fin_year )
617 AND register_type = p_register_type
618 AND source_type = 1
619 AND source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
620 AND tax_type = p_tax_type
621 AND source_register_id = rg23.register_id
622 ORDER BY fin_year,slno
623 FOR UPDATE OF jrg.opening_balance,
624 jrg.closing_balance,
625 jrg.last_updated_by,
626 jrg.last_update_date;
627
628 CURSOR cur_pla_next_records IS
629 SELECT jrg.opening_balance ,
630 jrg.closing_balance ,
631 nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
632 jrg.last_updated_by,
633 jrg.last_update_date,
634 jrg.rg_other_id,
635 jpl.slno,
636 jpl.organization_id,
637 jpl.location_id
638 FROM JAI_CMN_RG_OTHERS jrg,
639 JAI_CMN_RG_PLA_TRXS jpl
640 WHERE source_type = 2
641 and tax_type = p_tax_type
642 and source_register_id = jpl.register_id
643 and organization_id = p_organization_id
644 AND location_id = p_location_id
645 AND ((fin_year = p_fin_year
646 AND slno >= p_slno)
647 OR fin_year > p_fin_year )
648 ORDER BY fin_year,slno
649 FOR UPDATE OF jrg.opening_balance,
650 jrg.closing_balance,
651 jrg.last_updated_by,
652 jrg.last_update_date;
653
654 ln_prev_balance NUMBER;
655 ln_prev_slno NUMBER;
656 ln_fin_year NUMBER;
657 ln_opening_balance NUMBER;
658 ln_closing_balance NUMBER;
659
660 BEGIN
661
662
663 ln_fin_year := p_fin_year;
664 ln_prev_slno := p_slno;
665
666 get_prev_cess_rg_bal( p_organization_id => p_organization_id,
667 p_location_id => p_location_id,
668 p_register_type => p_register_type,
669 p_tax_type => p_tax_type,
670 p_fin_year => ln_fin_year,
671 p_slno => ln_prev_slno,
672 p_bal => ln_prev_balance);
673
674 IF ln_prev_slno IS NULL THEN
675
676 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error : No previous record exists. Need to fix Manually');
677 RETURN;
678
679 END IF;
680
681 IF p_register_type in ('A','C') THEN
682
683 FOR records in cur_rg23_next_records LOOP
684
685 ln_opening_balance := ln_prev_balance;
686 ln_closing_balance := ln_opening_balance + records.transaction_amount;
687
688 UPDATE JAI_CMN_RG_OTHERS
689 SET opening_balance = ln_opening_balance,
690 closing_balance = ln_closing_balance,
691 last_updated_by = p_last_updated_by,
692 last_update_date = sysdate
693 WHERE CURRENT OF cur_rg23_next_records;
694
695 ln_prev_balance := ln_closing_balance;
696
697 END LOOP;
698
699 UPDATE JAI_CMN_RG_OTH_BALANCES
700 SET balance = ln_prev_balance,
701 last_updated_by = p_last_updated_by,
702 last_update_date = sysdate
703 WHERE org_unit_id = ( SELECT org_unit_id
704 FROM JAI_CMN_INVENTORY_ORGS
705 WHERE organization_id = p_organization_id
706 AND location_id = p_location_id )
707 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
708 AND tax_type = p_tax_type;
709
710 ELSIF p_register_type = 'PLA' THEN
711
712 FOR records in cur_pla_next_records LOOP
713
714 ln_opening_balance := ln_prev_balance;
715 ln_closing_balance := ln_opening_balance + records.transaction_amount;
716
717 UPDATE JAI_CMN_RG_OTHERS
718 SET opening_balance = ln_opening_balance,
719 closing_balance = ln_closing_balance,
720 last_updated_by = p_last_updated_by,
721 last_update_date = sysdate
722 WHERE CURRENT OF cur_pla_next_records;
723
724 ln_prev_balance := ln_closing_balance;
725
726 END LOOP;
727
728 UPDATE JAI_CMN_RG_OTH_BALANCES
729 SET balance = ln_prev_balance,
730 last_updated_by = p_last_updated_by,
731 last_update_date = sysdate
732 WHERE org_unit_id = ( SELECT org_unit_id
733 FROM JAI_CMN_INVENTORY_ORGS
734 WHERE organization_id = p_organization_id
735 AND location_id = p_location_id )
736 AND register_type = 'PLA'
737 AND tax_type = p_tax_type;
738
739 END IF;
740
741 END corr_oth_balances;
742
743 -----------------------------------------CORR_OTH_BALANCES--------------------------------
744
745 -----------------------------------------CORR_FINAL_BAL--------------------------------
746
747 PROCEDURE corr_final_bal( p_organization_id NUMBER,
748 p_location_id NUMBER,
749 p_register_type VARCHAR2,
750 p_tax_type VARCHAR2,
751 p_closing_balance NUMBER)
752 IS
753 BEGIN
754
755 IF p_tax_type IS NULL THEN
756
757 IF p_register_type = 'A' THEN
758
759 UPDATE JAI_CMN_RG_BALANCES
760 SET rg23a_balance = p_closing_balance
761 WHERE organization_id = p_organization_id
762 AND location_id = p_location_id
763 AND rg23a_balance <> p_closing_balance;
764
765
766 ELSIF p_register_type = 'C' THEN
767
768 UPDATE JAI_CMN_RG_BALANCES
769 SET rg23c_balance = p_closing_balance
770 WHERE organization_id = p_organization_id
771 AND location_id = p_location_id
772 AND rg23a_balance <> p_closing_balance;
773
774 ELSIF p_register_type = 'PLA' THEN
775
776 UPDATE JAI_CMN_RG_BALANCES
777 SET pla_balance = p_closing_balance
778 WHERE organization_id = p_organization_id
779 AND location_id = p_location_id
780 AND rg23a_balance <> p_closing_balance;
781
782 END IF;
783
784
785 UPDATE JAI_CMN_RG_SLNOS
786 SET balance = p_closing_balance
787 WHERE organization_id = p_organization_id
788 AND location_id = p_location_id
789 AND register_type = p_register_type
790 AND balance <> p_closing_balance ;
791
792 ELSE
793
794 UPDATE JAI_CMN_RG_OTH_BALANCES
795 SET balance = p_closing_balance
796 WHERE org_unit_id = ( SELECT org_unit_id
797 FROM JAI_CMN_INVENTORY_ORGS
798 WHERE organization_id = p_organization_id
799 AND location_id = p_location_id )
800 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
801 AND tax_type = p_tax_type
802 AND balance <> p_closing_balance;
803
804 END IF;
805
806 END corr_final_bal;
807 -----------------------------------------CORR_FINAL_BAL--------------------------------
808
809 -----------------------------------------CORR_FINAL_SLNO--------------------------------
810 PROCEDURE corr_final_slno( p_organization_id NUMBER,
811 p_location_id NUMBER,
812 p_register_type VARCHAR2,
813 p_slno NUMBER)
814 IS
815 BEGIN
816
817 UPDATE JAI_CMN_RG_SLNOS
818 SET slno = p_slno
819 WHERE organization_id = p_organization_id
820 AND location_id = p_location_id
821 AND register_type = p_register_type
822 AND slno <> p_slno ;
823
824 END corr_final_slno;
825 -----------------------------------------CORR_FINAL_SLNO--------------------------------
826
827
828 -----------------------------------------VALIDATE_RG_OTHERS--------------------------------
829 PROCEDURE validate_rg_others ( p_organization_id NUMBER,
830 p_location_id NUMBER,
831 p_register_type VARCHAR2,
832 p_date DATE)
833 IS
834 CURSOR cur_rg23_next_records( cp_fin_year NUMBER,cp_slno NUMBER,cp_tax_type VARCHAR2) IS
835 SELECT jrg.opening_balance ,
836 jrg.closing_balance ,
837 nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
838 jrg.last_updated_by,
839 jrg.last_update_date,
840 jrg.rg_other_id,
841 rg23.slno,
842 rg23.organization_id,
843 rg23.location_id
844 FROM JAI_CMN_RG_OTHERS jrg,
845 JAI_CMN_RG_23AC_II_TRXS rg23
846 WHERE organization_id = p_organization_id
847 AND location_id = p_location_id
848 AND fin_year = cp_fin_year
849 AND register_type = p_register_type
850 AND slno > nvl(cp_slno,1)
851 AND source_type = 1
852 AND source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
853 AND tax_type = cp_tax_type
854 AND source_register_id = rg23.register_id
855 ORDER BY slno;
856
857 CURSOR cur_pla_next_records(cp_fin_year NUMBER, cp_slno NUMBER,cp_tax_type VARCHAR2) IS
858 SELECT jrg.opening_balance ,
859 jrg.closing_balance ,
860 nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
861 jrg.last_updated_by,
862 jrg.last_update_date,
863 jrg.rg_other_id,
864 jpl.slno,
865 jpl.organization_id,
866 jpl.location_id
867 FROM JAI_CMN_RG_OTHERS jrg,
868 JAI_CMN_RG_PLA_TRXS jpl
869 WHERE source_type = 2
870 and tax_type = cp_tax_type
871 and source_register_id = jpl.register_id
872 and organization_id = p_organization_id
873 AND location_id = p_location_id
874 AND fin_year = cp_fin_year
875 AND slno > nvl(cp_slno,1)
876 ORDER BY slno;
877
878 CURSOR cur_get_curr_fin_year IS
879 SELECT fin_year
880 FROM JAI_CMN_FIN_YEARS
881 WHERE organization_id = p_organization_id
882 AND fin_active_flag = 'Y';
883
884 CURSOR cur_get_fin_year IS
885 SELECT fin_year
886 FROM JAI_CMN_FIN_YEARS
887 WHERE organization_id = p_organization_id
888 AND p_date between fin_year_start_date and fin_year_end_date;
889
890 CURSOR cur_get_rg_slno(cp_fin_year NUMBER,cp_tax_type VARCHAR2,cp_date DATE) IS
891 SELECT max(slno),min(slno)
892 FROM JAI_CMN_RG_23AC_II_TRXS jrg
893 WHERE organization_id = p_organization_id
894 AND location_id = p_location_id
895 AND fin_year = cp_fin_year
896 AND register_type = p_register_type
897 AND trunc(creation_date) < cp_date
898 AND EXISTS ( SELECT 1
899 FROM JAI_CMN_RG_OTHERS
900 WHERE source_type = 1
901 AND source_register_id = jrg.register_id
902 AND tax_type = cp_tax_type );
903
904
905 CURSOR cur_rg_tax_exists(cp_fin_year NUMBER,cp_tax_type VARCHAR2,cp_date DATE) IS
906 SELECT 1
907 FROM JAI_CMN_RG_23AC_II_TRXS jrg
908 WHERE organization_id = p_organization_id
909 AND location_id = p_location_id
910 AND fin_year = cp_fin_year
911 AND register_type = p_register_type
912 AND trunc(creation_date) < cp_date
913 AND EXISTS ( SELECT 1
914 FROM JAI_CMN_RG_OTHERS
915 WHERE source_type = 1
916 AND source_register_id = jrg.register_id
917 AND tax_type = cp_tax_type );
918 ln_rg_tax_exists NUMBER;
919
920
921 CURSOR cur_get_pla_slno(cp_fin_year NUMBER,cp_tax_type VARCHAR2,cp_date DATE) IS
922 SELECT max(slno),min(slno)
923 FROM JAI_CMN_RG_PLA_TRXS jpl
924 WHERE organization_id = p_organization_id
925 AND location_id = p_location_id
926 AND fin_year = cp_fin_year
927 AND trunc(creation_date) < cp_date
928 AND EXISTS ( SELECT 1
929 FROM JAI_CMN_RG_OTHERS
930 WHERE source_type = 2
931 AND source_register_id = jpl.register_id
932 AND tax_type = cp_tax_type );
933
934 CURSOR cur_pla_tax_exists(cp_fin_year NUMBER,cp_tax_type VARCHAR2,cp_date DATE) IS
935 SELECT 1
936 FROM JAI_CMN_RG_PLA_TRXS jpl
937 WHERE organization_id = p_organization_id
938 AND location_id = p_location_id
939 AND fin_year = cp_fin_year
940 AND trunc(creation_date) < cp_date
941 AND EXISTS ( SELECT 1
942 FROM JAI_CMN_RG_OTHERS
943 WHERE source_type = 2
944 AND source_register_id = jpl.register_id
945 AND tax_type = cp_tax_type );
946 ln_pla_tax_exists NUMBER;
947
948
949 CURSOR get_rg_closing_bal(cp_fin_year NUMBER,cp_slno NUMBER,cp_tax_type VARCHAR2) IS
950 SELECT closing_balance
951 FROM JAI_CMN_RG_OTHERS
952 WHERE source_type = 1
953 AND tax_type = cp_tax_type
954 AND source_register_id in ( SELECT register_id
955 FROM JAI_CMN_RG_23AC_II_TRXS
956 WHERE organization_id = p_organization_id
957 AND location_id = p_location_id
958 AND register_type = p_register_type
959 AND fin_year = cp_fin_year
960 AND slno = nvl(cp_slno,1) );
961
962 CURSOR get_pla_closing_bal(cp_fin_year NUMBER,cp_slno NUMBER,cp_tax_type VARCHAR2) IS
963 SELECT closing_balance
964 FROM JAI_CMN_RG_OTHERS
965 WHERE source_type = 2
966 AND tax_type = cp_tax_type
967 AND source_register_id in ( SELECT register_id
968 FROM JAI_CMN_RG_PLA_TRXS
969 WHERE organization_id = p_organization_id
970 AND location_id = p_location_id
971 AND fin_year = cp_fin_year
972 AND slno = nvl(cp_slno,1) );
973
974 CURSOR cur_get_final_bal(cp_tax_type VARCHAR2) IS
975 SELECT balance
976 FROM JAI_CMN_RG_OTH_BALANCES
977 WHERE org_unit_id = ( SELECT org_unit_id
978 FROM JAI_CMN_INVENTORY_ORGS
979 WHERE organization_id = p_organization_id
980 AND location_id = p_location_id )
981 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
982 AND tax_type = cp_tax_type;
983
984 ln_rg_prev_slno NUMBER;
985 ln_pla_prev_slno NUMBER;
986 ln_rg_first_slno NUMBER;
987 ln_pla_first_slno NUMBER;
988 ln_prev_balance NUMBER;
989 ln_final_bal NUMBER;
990 ln_fin_year NUMBER;
991 ln_curr_fin_year NUMBER;
992 lv_tax_type VARCHAR2(50);
993 ld_date DATE;
994 ln_final_slno NUMBER;
995
996 BEGIN
997
998 OPEN cur_get_curr_fin_year;
999 FETCH cur_get_curr_fin_year INTO ln_curr_fin_year;
1000 CLOSE cur_get_curr_fin_year;
1001
1002 FOR tax in 1..4 LOOP
1003
1004 OPEN cur_get_fin_year;
1005 FETCH cur_get_fin_year INTO ln_fin_year;
1006 CLOSE cur_get_fin_year;
1007
1008 IF tax = 1 THEN
1009
1010 lv_tax_type := jai_constants.tax_type_exc_edu_cess ;
1011
1012 ELSIF tax = 2 THEN
1013
1014 lv_tax_type := jai_constants.tax_type_cvd_edu_cess ;
1015
1016 ELSIF tax = 3 THEN
1017
1018 lv_tax_type := jai_constants.tax_type_sh_exc_edu_cess;
1019
1020 ELSIF tax = 4 THEN
1021
1022 lv_tax_type := jai_constants.tax_type_sh_cvd_edu_cess;
1023
1024 END IF;
1025
1026 ld_date := p_date;
1027
1028 ln_prev_balance := NULL;
1029 ln_rg_prev_slno := NULL;
1030 ln_pla_prev_slno := NULL;
1031
1032
1033 OPEN cur_get_rg_slno(ln_fin_year,lv_tax_type,ld_date);
1034 FETCH cur_get_rg_slno INTO ln_rg_prev_slno,ln_rg_first_slno;
1035 CLOSE cur_get_rg_slno;
1036
1037 OPEN cur_get_pla_slno(ln_fin_year,lv_tax_type,ld_date);
1038 FETCH cur_get_pla_slno INTO ln_pla_prev_slno,ln_pla_first_slno;
1039 CLOSE cur_get_pla_slno;
1040
1041 LOOP
1042
1043 IF p_register_type in ( 'A','C') and ln_rg_prev_slno IS NOT NULL THEN
1044
1045 OPEN get_rg_closing_bal(ln_fin_year,ln_rg_prev_slno,lv_tax_type);
1046 FETCH get_rg_closing_bal INTO ln_prev_balance;
1047 CLOSE get_rg_closing_bal;
1048
1049 FOR rg_rec in cur_rg23_next_records( ln_fin_year,ln_rg_prev_slno,lv_tax_type) LOOP
1050
1051 IF ln_prev_balance <> rg_rec.opening_balance THEN
1052 capture_error( p_organization_id => p_organization_id ,
1053 p_location_id => p_location_id ,
1054 p_register_type => p_register_type ,
1055 p_fin_year => ln_fin_year ,
1056 p_opening_balance => null ,
1057 p_error_codes => 'E19' ,
1058 p_slno => rg_rec.slno ,
1059 p_register_id => null ,
1060 p_rowcount => null ,
1061 p_tax_type => lv_tax_type ,
1062 p_date => null ,
1063 p_month => null ,
1064 p_year => null
1065 ) ;
1066 IF gn_action = 3 THEN
1067 corr_oth_balances
1068 (
1069 p_organization_id => p_organization_id,
1070 p_location_id => p_location_id,
1071 p_fin_year => ln_fin_year,
1072 p_register_type => p_register_type,
1073 p_slno => rg_rec.slno,
1074 p_tax_type => lv_tax_type,
1075 p_last_updated_by => -5451134
1076 );
1077 END IF;
1078
1079 EXIT;
1080 END IF;
1081 IF rg_rec.opening_balance + rg_rec.transaction_amount <> rg_rec.closing_balance THEN
1082 capture_error( p_organization_id => p_organization_id ,
1083 p_location_id => p_location_id ,
1084 p_register_type => p_register_type ,
1085 p_fin_year => ln_fin_year ,
1086 p_opening_balance => null ,
1087 p_error_codes => 'E08' ,
1088 p_slno => rg_rec.slno ,
1089 p_register_id => null ,
1090 p_rowcount => null ,
1091 p_tax_type => lv_tax_type ,
1092 p_date => null ,
1093 p_month => null ,
1094 p_year => null
1095 ) ;
1096 IF gn_action = 3 THEN
1097 corr_oth_balances
1098 (
1099 p_organization_id => p_organization_id,
1100 p_location_id => p_location_id,
1101 p_fin_year => ln_fin_year,
1102 p_register_type => p_register_type,
1103 p_slno => rg_rec.slno,
1104 p_tax_type => lv_tax_type,
1105 p_last_updated_by => -5451134
1106 );
1107 return;
1108 END IF;
1109 END IF;
1110 ln_rg_prev_slno := rg_rec.slno;
1111 ln_prev_balance := rg_rec.closing_balance;
1112 END LOOP;
1113
1114 ELSIF p_register_type = 'PLA' AND ln_pla_prev_slno IS NOT NULL THEN
1115
1116 OPEN get_rg_closing_bal(ln_fin_year,ln_pla_prev_slno,lv_tax_type);
1117 FETCH get_rg_closing_bal INTO ln_prev_balance;
1118 CLOSE get_rg_closing_bal;
1119
1120 FOR pla_rec in cur_pla_next_records( ln_fin_year,ln_pla_prev_slno,lv_tax_type) LOOP
1121
1122 IF ln_prev_balance <> pla_rec.opening_balance THEN
1123 capture_error( p_organization_id => p_organization_id ,
1124 p_location_id => p_location_id ,
1125 p_register_type => 'PLA' ,
1126 p_fin_year => ln_fin_year ,
1127 p_opening_balance => null ,
1128 p_error_codes => 'E19' ,
1129 p_slno => pla_rec.slno ,
1130 p_register_id => null ,
1131 p_rowcount => null ,
1132 p_tax_type => lv_tax_type ,
1133 p_date => null ,
1134 p_month => null ,
1135 p_year => null
1136 ) ;
1137 IF gn_action = 3 THEN
1138 corr_oth_balances
1139 (
1140 p_organization_id => p_organization_id,
1141 p_location_id => p_location_id,
1142 p_fin_year => ln_fin_year,
1143 p_register_type => p_register_type,
1144 p_slno => pla_rec.slno,
1145 p_tax_type => lv_tax_type,
1146 p_last_updated_by => -5451134
1147 );
1148 END IF;
1149 return;
1150
1151 END IF;
1152
1153 IF pla_rec.opening_balance + pla_rec.transaction_amount <> pla_rec.closing_balance THEN
1154 capture_error( p_organization_id => p_organization_id ,
1155 p_location_id => p_location_id ,
1156 p_register_type => p_register_type ,
1157 p_fin_year => ln_fin_year ,
1158 p_opening_balance => null ,
1159 p_error_codes => 'E08' ,
1160 p_slno => pla_rec.slno ,
1161 p_register_id => null ,
1162 p_rowcount => null ,
1163 p_tax_type => lv_tax_type ,
1164 p_date => null ,
1165 p_month => null ,
1166 p_year => null
1167 ) ;
1168 IF gn_action = 3 THEN
1169 corr_oth_balances
1170 (
1171 p_organization_id => p_organization_id,
1172 p_location_id => p_location_id,
1173 p_fin_year => ln_fin_year,
1174 p_register_type => p_register_type,
1175 p_slno => pla_rec.slno,
1176 p_tax_type => lv_tax_type,
1177 p_last_updated_by => -5451134
1178 );
1179 return;
1180 END IF;
1181
1182 END IF;
1183
1184 ln_pla_prev_slno := pla_rec.slno;
1185 ln_prev_balance := pla_rec.closing_balance;
1186
1187 END LOOP;
1188
1189 END IF;
1190
1191 ln_fin_year := ln_fin_year + 1;
1192 EXIT WHEN ln_fin_year > ln_curr_fin_year ;
1193 ld_date := SYSDATE;
1194
1195 ln_rg_tax_exists := 0;
1196 OPEN cur_rg_tax_exists(ln_fin_year,lv_tax_type,ld_date);
1197 FETCH cur_rg_tax_exists INTO ln_rg_tax_exists;
1198 CLOSE cur_rg_tax_exists;
1199
1200 IF p_register_type IN ('A','C') AND ln_rg_tax_exists = 0 THEN
1201 EXIT;
1202 END IF;
1203
1204 OPEN cur_get_rg_slno(ln_fin_year,lv_tax_type,ld_date);
1205 FETCH cur_get_rg_slno INTO ln_rg_prev_slno,ln_rg_first_slno;
1206 CLOSE cur_get_rg_slno;
1207
1208 ln_pla_tax_exists := 0;
1209 OPEN cur_pla_tax_exists(ln_fin_year,lv_tax_type,ld_date);
1210 FETCH cur_pla_tax_exists INTO ln_pla_tax_exists;
1211 CLOSE cur_pla_tax_exists;
1212
1213 IF p_register_type = 'PLA' AND ln_pla_tax_exists = 0 THEN
1214 EXIT;
1215 END IF;
1216
1217 OPEN cur_get_pla_slno(ln_fin_year,lv_tax_type,ld_date);
1218 FETCH cur_get_pla_slno INTO ln_pla_prev_slno,ln_pla_first_slno;
1219 CLOSE cur_get_pla_slno;
1220
1221 OPEN cur_get_rg_slno(ln_fin_year,lv_tax_type,ld_date);
1222 FETCH cur_get_rg_slno INTO ln_rg_prev_slno,ln_rg_first_slno;
1223 CLOSE cur_get_rg_slno;
1224
1225 OPEN cur_get_pla_slno(ln_fin_year,lv_tax_type,ld_date);
1226 FETCH cur_get_pla_slno INTO ln_pla_prev_slno,ln_pla_first_slno;
1227 CLOSE cur_get_pla_slno;
1228
1229 ln_pla_prev_slno := ln_pla_first_slno;
1230 ln_rg_prev_slno := ln_rg_first_slno;
1231
1232 END LOOP;
1233
1234 OPEN cur_get_final_bal(lv_tax_type);
1235 FETCH cur_get_final_bal INTO ln_final_bal;
1236 CLOSE cur_get_final_bal;
1237
1238 IF ln_prev_balance <> ln_final_bal THEN
1239 IF p_register_type = 'PLA' THEN
1240 ln_final_slno := ln_pla_prev_slno ;
1241 ELSE
1242 ln_final_slno := ln_rg_prev_slno ;
1243 END IF;
1244
1245 if gn_action = 3 then
1246 corr_final_bal( p_organization_id => p_organization_id ,
1247 p_location_id => p_location_id ,
1248 p_register_type => p_register_type ,
1249 p_tax_type => lv_tax_type ,
1250 p_closing_balance => ln_prev_balance );
1251 end if ;
1252
1253 capture_error( p_organization_id => p_organization_id ,
1254 p_location_id => p_location_id ,
1255 p_register_type => p_register_type ,
1256 p_fin_year => ln_fin_year ,
1257 p_opening_balance => null ,
1258 p_error_codes => 'E06' ,
1259 p_slno => ln_final_slno ,
1260 p_register_id => null ,
1261 p_rowcount => null ,
1262 p_tax_type => lv_tax_type ,
1263 p_date => null ,
1264 p_month => null ,
1265 p_year => null
1266 ) ;
1267
1268 END IF;
1269 END LOOP;
1270 END validate_rg_others ;
1271 ------------------------------------ VALIDATE_RG_OTHERS ------------------------------------------------
1272 ------------------------------------ CORR_EXC_BAL ------------------------------------------------
1273
1274 PROCEDURE corr_exc_bal( p_register_type VARCHAR2,
1275 p_organization_id NUMBER ,
1276 p_location_id NUMBER ,
1277 p_slno NUMBER ,
1278 p_fin_year NUMBER ,
1279 p_last_updated_by NUMBER )
1280 IS
1281 CURSOR cur_next_pla_records IS
1282 SELECT opening_balance,
1283 closing_balance,
1284 nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0) -
1285 nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0) transaction_amount,
1286 register_id,
1287 slno
1288 FROM JAI_CMN_RG_PLA_TRXS
1289 WHERE organization_id = p_organization_id
1290 AND location_id = p_location_id
1291 AND ((fin_year = p_fin_year
1292 AND slno >= p_slno ) OR
1293 ( fin_year > p_fin_year ))
1294 ORDER BY FIN_YEAR,SLNO
1295 FOR UPDATE ;
1296
1297 l_apps_short_name CONSTANT VARCHAR2(2) := 'JA';
1298
1299 CURSOR c_check_addl_cvd
1300 IS
1301 SELECT 1
1302 FROM all_tab_cols
1303 WHERE table_name = 'JAI_CMN_RG_23AC_II_TRXS'
1304 AND column_name IN ( 'DR_ADDITIONAL_CVD', 'CR_ADDITIONAL_CVD')
1305 AND owner = l_apps_short_name ; /*added by ssawant*/
1306
1307 ln_prev_balance NUMBER;
1308 ln_prev_slno NUMBER;
1309 lv_trans_str VARCHAR2(1000);
1310 lv_cursor_str VARCHAR2(4000);
1311 ln_cvd_exists NUMBER := 0;
1312 ln_fin_year NUMBER;
1313 ln_opening_balance NUMBER;
1314 ln_closing_balance NUMBER;
1315 ln_trans_amt NUMBER;
1316 ln_slno NUMBER;
1317 ln_register_id NUMBER;
1318 type records_ref is ref cursor;
1319 cur_next_rg_records records_ref;
1320 BEGIN
1321
1322 lv_trans_str := 'nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0) -
1323 nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)';
1324
1325 OPEN c_check_addl_cvd ;
1326 FETCH c_check_addl_cvd INTO ln_cvd_exists ;
1327 CLOSE c_check_addl_cvd ;
1328
1329 IF ln_cvd_exists = 1 THEN
1330
1331 lv_trans_str := lv_trans_str || '+ nvl(CR_ADDITIONAL_CVD,0) - nvl(DR_ADDITIONAL_CVD,0)';
1332
1333 END IF;
1334 lv_cursor_str := 'SELECT '||
1335 lv_trans_str||' ,
1336 register_id,
1337 slno
1338 FROM JAI_CMN_RG_23AC_II_TRXS
1339 WHERE organization_id ='|| p_organization_id||'
1340 AND location_id ='|| p_location_id||'
1341 AND register_type ='''|| p_register_type||'''
1342 AND ((fin_year ='|| p_fin_year||'
1343 AND slno >='|| p_slno||' ) OR
1344 ( fin_year >'|| p_fin_year||' ))
1345 ORDER BY fin_year,slno
1346 FOR UPDATE ';
1347 ln_fin_year := p_fin_year;
1348 ln_prev_slno := p_slno;
1349
1350 get_prev_rg_bal( p_organization_id => p_organization_id,
1351 p_location_id => p_location_id,
1352 p_register_type => p_register_type,
1353 p_fin_year => ln_fin_year,
1354 p_slno => ln_prev_slno,
1355 p_bal => ln_prev_balance);
1356
1357 IF ln_prev_slno IS NULL THEN
1358
1359 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error : No previous record exists. Need to fix Manually');
1360 RETURN;
1361
1362 END IF;
1363
1364
1365 IF p_register_type IN ('A','C') THEN
1366
1367 OPEN cur_next_rg_records FOR lv_cursor_str;
1368 LOOP
1369 FETCH cur_next_rg_records INTO ln_trans_amt,ln_register_id,ln_slno ;
1370 EXIT WHEN cur_next_rg_records%NOTFOUND;
1371 ln_opening_balance := ln_prev_balance;
1372 ln_closing_balance := ln_opening_balance + ln_trans_amt;
1373
1374 UPDATE JAI_CMN_RG_23AC_II_TRXS
1375 SET opening_balance = ln_opening_balance,
1376 closing_balance = ln_closing_balance,
1377 last_updated_by = p_last_updated_by,
1378 last_update_date = sysdate
1379 WHERE register_id = ln_register_id;
1380
1381 ln_prev_balance := ln_closing_balance;
1382
1383 END LOOP;
1384
1385 IF p_register_type = 'A' THEN
1386
1387 UPDATE JAI_CMN_RG_BALANCES
1388 SET rg23a_balance = ln_prev_balance,
1389 last_updated_by = p_last_updated_by
1390 WHERE organization_id = p_organization_id
1391 AND location_id = p_location_id ;
1392
1393 UPDATE JAI_CMN_RG_SLNOS
1394 SET balance = ln_prev_balance
1395 WHERE organization_id = p_organization_id
1396 AND location_id = p_location_id
1397 AND register_type = 'A' ;
1398
1399 ELSIF p_register_type = 'C' THEN
1400
1401 UPDATE JAI_CMN_RG_BALANCES
1402 SET rg23c_balance = ln_prev_balance
1403 WHERE organization_id = p_organization_id
1404 AND location_id = p_location_id ;
1405
1406 UPDATE JAI_CMN_RG_SLNOS
1407 SET balance = ln_prev_balance
1408 WHERE organization_id = p_organization_id
1409 AND location_id = p_location_id
1410 AND register_type = 'C' ;
1411
1412 END IF;
1413
1414 ELSIF p_register_type = 'PLA' THEN
1415
1416 FOR records in cur_next_pla_records LOOP
1417
1418 ln_opening_balance := ln_prev_balance;
1419 ln_closing_balance := ln_opening_balance + records.transaction_amount;
1420
1421 UPDATE JAI_CMN_RG_PLA_TRXS
1422 SET opening_balance = ln_opening_balance,
1423 closing_balance = ln_closing_balance,
1424 last_updated_by = p_last_updated_by,
1425 last_update_date = sysdate
1426 WHERE CURRENT OF cur_next_pla_records;
1427
1428 ln_prev_balance := ln_closing_balance;
1429
1430 END LOOP;
1431
1432 UPDATE JAI_CMN_RG_BALANCES
1433 SET pla_balance = ln_prev_balance,
1434 last_updated_by = p_last_updated_by
1435 WHERE organization_id = p_organization_id
1436 AND location_id = p_location_id ;
1437
1438 UPDATE JAI_CMN_RG_SLNOS
1439 SET balance = ln_prev_balance
1440 WHERE organization_id = p_organization_id
1441 AND location_id = p_location_id
1442 AND register_type = 'PLA' ;
1443
1444 END IF;
1445
1446 END corr_exc_bal;
1447
1448 ------------------------------------ CORR_EXC_BAL ------------------------------------------------
1449 ------------------------------------ UPD_OTH_TAX ------------------------------------------------
1450
1451 PROCEDURE upd_oth_tax( p_register_type VARCHAR2,
1452 p_register_id NUMBER )
1453 IS
1454 BEGIN
1455
1456 IF p_register_type IN ( 'A','C') THEN
1457
1458 UPDATE JAI_CMN_RG_23AC_II_TRXS
1459 SET other_tax_credit = ( SELECT sum(credit)
1460 FROM JAI_CMN_RG_OTHERS
1461 WHERE source_type = 1
1462 AND source_register_id = p_register_id ),
1463 other_tax_debit = ( SELECT sum(debit)
1464 FROM JAI_CMN_RG_OTHERS
1465 WHERE source_type = 1
1466 AND source_register_id = p_register_id )
1467 WHERE register_id = p_register_id;
1468
1469 ELSIF p_register_type = 'PLA' THEN
1470
1471 UPDATE JAI_CMN_RG_PLA_TRXS
1472 SET other_tax_credit = ( SELECT sum(credit)
1473 FROM JAI_CMN_RG_OTHERS
1474 WHERE source_type = 2
1475 AND source_register_id = p_register_id ),
1476 other_tax_debit = ( SELECT sum(debit)
1477 FROM JAI_CMN_RG_OTHERS
1478 WHERE source_type = 2
1479 AND source_register_id = p_register_id )
1480 WHERE register_id = p_register_id;
1481
1482 END IF;
1483
1484 END upd_oth_tax;
1485
1486 ------------------------------------ UPD_OTH_TAX ------------------------------------------------
1487
1488 ------------------------------------ PLA_VALIDATION ------------------------------------------------
1489 PROCEDURE pla_validation (
1490 p_organization_id IN JAI_CMN_RG_PLA_TRXS.ORGANIZATION_ID%TYPE ,
1491 p_location_id IN JAI_CMN_RG_PLA_TRXS.LOCATION_ID%TYPE ,
1492 p_fin_year IN JAI_CMN_RG_PLA_TRXS.FIN_YEAR%TYPE
1493 )
1494 IS
1495
1496 Cursor c_duplicate_slno
1497 IS
1498 select slno, count(*) rowcount
1499 from
1500 JAI_CMN_RG_PLA_TRXS
1501 where
1502 organization_id = p_organization_id and
1503 location_id = p_location_id and
1504 fin_year = p_fin_year and
1505 trunc(creation_date) >= gd_date
1506 group by slno
1507 having count(*) > 1 ;
1508
1509 Cursor c_transaction_balance
1510 is
1511 select slno
1512 from
1513 JAI_CMN_RG_PLA_TRXS
1514 where
1515 closing_balance <> nvl(opening_balance,0) +
1516 ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) ) -
1517 ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) ) AND
1518 organization_id = p_organization_id AND
1519 location_id = p_location_id AND
1520 fin_year = p_fin_year AND
1521 trunc(creation_date) >= gd_date
1522 ORDER BY
1523 register_id ;
1524
1525 Cursor check_balances
1526 is
1527 select
1528 rowid ,
1529 slno ,
1530 organization_id ,
1531 location_id ,
1532 register_id ,
1533 fin_year ,
1534 opening_balance ,
1535 closing_balance ,
1536 nvl(other_tax_credit,other_tax_debit) rg_other_amt ,
1537 transaction_source_num
1538 FROM
1539 JAI_CMN_RG_PLA_TRXS
1540 WHERE
1541 organization_id = p_organization_id AND
1542 location_id = p_location_id AND
1543 fin_year = p_fin_year AND
1544 trunc(creation_date) >= gd_date
1545 ORDER BY
1546 slno ;
1547
1548 cursor c_get_closing_balance ( cp_organization_id JAI_CMN_RG_PLA_TRXS.organization_id%type ,
1549 cp_location_id JAI_CMN_RG_PLA_TRXS.location_id%type ,
1550 cp_fin_year JAI_CMN_RG_PLA_TRXS.fin_year%type ,
1551 cp_slno JAI_CMN_RG_PLA_TRXS.slno%type
1552 )
1553 is
1554 select
1555 nvl(closing_balance,0)
1556 from
1557 JAI_CMN_RG_PLA_TRXS
1558 where
1559 organization_id = cp_organization_id AND
1560 location_id = cp_location_id AND
1561 fin_year = cp_fin_year AND
1562 slno =
1563 ( select max(slno)
1564 from
1565 JAI_CMN_RG_PLA_TRXS
1566 where
1567 organization_id = cp_organization_id and
1568 location_id = cp_location_id and
1569 fin_year = cp_fin_year and
1570 slno < cp_slno
1571 );
1572
1573 Cursor c_final_balance_pla (cp_organization_id IN NUMBER,
1574 cp_location_id IN NUMBER,
1575 cp_fin_year IN NUMBER
1576 ) IS
1577 select nvl(closing_balance,0)
1578 from JAI_CMN_RG_PLA_TRXS
1579 where organization_id = cp_organization_id
1580 and location_id = cp_location_id
1581 and fin_year = cp_fin_year
1582 and slno in
1583 ( select nvl(max(slno),0)
1584 from JAI_CMN_RG_PLA_TRXS
1585 where organization_id = cp_organization_id
1586 and location_id = cp_location_id
1587 and fin_year = cp_fin_year
1588 );
1589
1590
1591 ln_closing_balance number ;
1592
1593 cursor c_rg_others(cp_source_register_id number)
1594 is
1595 select nvl(sum(credit), sum(debit))
1596 from JAI_CMN_RG_OTHERS
1597 where source_register = 'PLA'
1598 and source_register_id = cp_source_register_id
1599 and source_type = 2 ;
1600
1601 ln_rg_other_amt number ;
1602 ln_rowcount number ;
1603 ln_slno number ;
1604 ln_register_id number ;
1605
1606 BEGIN
1607
1608 ln_rowcount := null ;
1609
1610 For rec_slno in c_duplicate_slno
1611 loop
1612
1613 if nvl(ln_slno,0) <> 0 then
1614
1615 if gn_action = 3 then
1616 remove_dup_slno( p_organization_id => p_organization_id ,
1617 p_location_id => p_location_id ,
1618 p_fin_year => p_fin_year ,
1619 p_register_type => 'PLA' ,
1620 p_slno => rec_slno.slno ,
1621 p_dup_cnt => rec_slno.rowcount );
1622 end if ;
1623
1624 capture_error( p_organization_id => p_organization_id ,
1625 p_location_id => p_location_id ,
1626 p_register_type => 'PLA' ,
1627 p_fin_year => p_fin_year ,
1628 p_opening_balance => null ,
1629 p_error_codes => 'E07' ,
1630 p_slno => rec_slno.slno ,
1631 p_register_id => null ,
1632 p_rowcount => rec_slno.rowcount ,
1633 p_tax_type => null ,
1634 p_date => null ,
1635 p_month => null ,
1636 p_year => null
1637 ) ;
1638
1639 end if ;
1640
1641 end loop ;
1642
1643 ln_slno := null ;
1644 ln_rowcount := null ;
1645
1646 open c_transaction_balance ;
1647 fetch c_transaction_balance into ln_slno ;
1648 ln_rowcount := c_transaction_balance%ROWCOUNT ;
1649 close c_transaction_balance ;
1650
1651 if nvl(ln_slno,0) <> 0 then
1652 capture_error( p_organization_id => p_organization_id ,
1653 p_location_id => p_location_id ,
1654 p_register_type => 'PLA' ,
1655 p_fin_year => p_fin_year ,
1656 p_opening_balance => null ,
1657 p_error_codes => 'E05' ,
1658 p_slno => ln_slno ,
1659 p_register_id => null ,
1660 p_rowcount => ln_rowcount ,
1661 p_tax_type => null ,
1662 p_date => null ,
1663 p_month => null ,
1664 p_year => null
1665 ) ;
1666
1667 IF gn_action = 3 THEN
1668 corr_exc_bal( p_register_type => 'PLA' ,
1669 p_organization_id => p_organization_id ,
1670 p_location_id => p_location_id ,
1671 p_slno => ln_slno ,
1672 p_fin_year => p_fin_year ,
1673 p_last_updated_by => -5451134 );
1674 return ;
1675 END IF;
1676
1677 return ;
1678
1679
1680
1681 end if ;
1682
1683 FOR rec IN check_balances
1684 LOOP
1685
1686 ln_closing_balance := null ;
1687 open c_get_closing_balance(rec.organization_id,rec.location_id, rec.fin_year, rec.slno) ;
1688 fetch c_get_closing_balance into ln_closing_balance ;
1689 close c_get_closing_balance ;
1690
1691 if ln_closing_balance is null then
1692 open c_final_balance_pla(rec.organization_id,rec.location_id, rec.fin_year-1) ;
1693 fetch c_final_balance_pla into ln_closing_balance ;
1694 close c_final_balance_pla ;
1695
1696 if ln_closing_balance is null then
1697 ln_closing_balance := 0 ;
1698 end if ;
1699 end if ;
1700
1701 if nvl(rec.opening_balance,0) <> nvl(ln_closing_balance,0) then
1702 capture_error( p_organization_id => p_organization_id ,
1703 p_location_id => p_location_id ,
1704 p_register_type => 'PLA' ,
1705 p_fin_year => p_fin_year ,
1706 p_opening_balance => null ,
1707 p_error_codes => 'E04' ,
1708 p_slno => rec.slno ,
1709 p_register_id => rec.register_id ,
1710 p_rowcount => null ,
1711 p_tax_type => null ,
1712 p_date => null ,
1713 p_month => null ,
1714 p_year => null
1715 ) ;
1716
1717 IF gn_action = 3 THEN
1718 corr_exc_bal( p_register_type => 'PLA' ,
1719 p_organization_id => rec.organization_id ,
1720 p_location_id => rec.location_id ,
1721 p_slno => rec.slno ,
1722 p_fin_year => rec.fin_year ,
1723 p_last_updated_by => -5451134 );
1724 END IF;
1725
1726 return ;
1727
1728 end if;
1729
1730 ln_rg_other_amt := null ;
1731 open c_rg_others(rec.register_id) ;
1732 fetch c_rg_others into ln_rg_other_amt ;
1733 close c_rg_others ;
1734
1735 if nvl(ln_rg_other_amt,0) <> nvl(rec.rg_other_amt,0)
1736 then
1737 capture_error( p_organization_id => p_organization_id ,
1738 p_location_id => p_location_id ,
1739 p_register_type => 'PLA' ,
1740 p_fin_year => p_fin_year ,
1741 p_opening_balance => null ,
1742 p_error_codes => 'E10' ,
1743 p_slno => null ,
1744 p_register_id => rec.register_id ,
1745 p_rowcount => null ,
1746 p_tax_type => null ,
1747 p_date => null ,
1748 p_month => null ,
1749 p_year => null
1750 ) ;
1751
1752 IF gn_action = 3 THEN
1753
1754 upd_oth_tax( p_register_type => 'PLA',
1755 p_register_id => rec.register_id );
1756 END IF;
1757
1758 end if ;
1759 END LOOP ;
1760
1761 END pla_validation;
1762 ------------------------------------ PLA_VALIDATION ------------------------------------------------
1763
1764
1765
1766 ------------------------------------ RG23_PART_II_VALIDATION ------------------------------------------------
1767 PROCEDURE rg23_part_ii_validation( p_organization_id IN JAI_CMN_RG_23AC_II_TRXS.ORGANIZATION_ID%TYPE ,
1768 p_location_id IN JAI_CMN_RG_23AC_II_TRXS.LOCATION_ID%TYPE ,
1769 p_fin_year IN JAI_CMN_RG_23AC_II_TRXS.FIN_YEAR%TYPE ,
1770 p_register_type IN JAI_CMN_RG_23AC_II_TRXS.REGISTER_TYPE%TYPE
1771 )
1772 IS
1773
1774 Cursor c_duplicate_slno
1775 IS
1776 select slno , count(*) rowcount
1777 from
1778 JAI_CMN_RG_23AC_II_TRXS
1779 where
1780 organization_id = p_organization_id and
1781 location_id = p_location_id and
1782 fin_year = p_fin_year and
1783 register_type = p_register_type and
1784 trunc(creation_date) >= gd_date
1785 group by slno
1786 having count(*) > 1 ;
1787
1788 Cursor c_transaction_balance
1789 is
1790 select slno
1791 from
1792 JAI_CMN_RG_23AC_II_TRXS
1793 where
1794 closing_balance <> nvl(opening_balance,0) +
1795 ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) ) -
1796 ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) ) AND
1797 register_type = p_register_type AND
1798 organization_id = p_organization_id AND
1799 location_id = p_location_id AND
1800 fin_year = p_fin_year AND
1801 trunc(creation_date) >= gd_date
1802 ORDER BY
1803 register_id ;
1804
1805 Cursor check_balances
1806 is
1807 select
1808 rowid ,
1809 slno ,
1810 organization_id ,
1811 location_id ,
1812 register_id ,
1813 register_type ,
1814 fin_year ,
1815 opening_balance ,
1816 closing_balance ,
1817 nvl(other_tax_credit,other_tax_debit) rg_other_amt
1818 FROM
1819 JAI_CMN_RG_23AC_II_TRXS
1820 WHERE
1821 organization_id = p_organization_id AND
1822 location_id = p_location_id AND
1823 fin_year = p_fin_year AND
1824 register_type = p_register_type AND
1825 trunc(creation_date) >= gd_date
1826 ORDER BY
1827 organization_id ,
1828 location_id ,
1829 fin_year ,
1830 register_type ,
1831 slno ;
1832
1833 cursor c_get_closing_balance ( cp_organization_id JAI_CMN_RG_23AC_II_TRXS.ORGANIZATION_ID%TYPE ,
1834 cp_location_id JAI_CMN_RG_23AC_II_TRXS.LOCATION_ID%TYPE ,
1835 cp_fin_year JAI_CMN_RG_23AC_II_TRXS.FIN_YEAR%TYPE ,
1836 cp_register_type JAI_CMN_RG_23AC_II_TRXS.REGISTER_TYPE%TYPE ,
1837 cp_slno JAI_CMN_RG_23AC_II_TRXS.SLNO%TYPE
1838 )
1839 is
1840 select
1841 nvl(closing_balance,0)
1842 from
1843 JAI_CMN_RG_23AC_II_TRXS
1844 where
1845 organization_id = cp_organization_id AND
1846 location_id = cp_location_id AND
1847 fin_year = cp_fin_year AND
1848 register_type = cp_register_type AND
1849 slno =
1850 ( select max(slno)
1851 from
1852 JAI_CMN_RG_23AC_II_TRXS
1853 where
1854 organization_id = cp_organization_id and
1855 location_id = cp_location_id and
1856 fin_year = cp_fin_year and
1857 register_type = cp_register_type and
1858 slno < cp_slno ) ;
1859
1860 Cursor c_final_balance_rg23(cp_organization_id IN NUMBER,
1861 cp_location_id IN NUMBER,
1862 cp_fin_year IN NUMBER,
1863 cp_register_type CHAR) IS
1864 select nvl(closing_balance,0)
1865 from JAI_CMN_RG_23AC_II_TRXS
1866 where organization_id = cp_organization_id
1867 and location_id = cp_location_id
1868 and register_type = cp_register_type
1869 and fin_year = cp_fin_year
1870 and slno in
1871 ( select nvl(max(slno),0)
1872 from JAI_CMN_RG_23AC_II_TRXS
1873 where organization_id = cp_organization_id
1874 and location_id = cp_location_id
1875 and fin_year = cp_fin_year
1876 and register_type = cp_register_type);
1877
1878 cursor c_rg_others(cp_source_register_id number , cp_register_type varchar2)
1879 is
1880 select nvl(sum(credit), sum(debit))
1881 from JAI_CMN_RG_OTHERS
1882 where source_register = decode(cp_register_type,'A','RG23A_P2','C','RG23C_P2')
1883 and source_register_id = cp_source_register_id
1884 and source_type = 1 ;
1885
1886 ln_rg_other_amt number ;
1887 ln_closing_balance number ;
1888 ln_rowcount number ;
1889 ln_slno number ;
1890 ln_register_id number ;
1891
1892
1893 BEGIN
1894
1895 ln_rowcount := null ;
1896
1897 For rec_slno in c_duplicate_slno
1898 loop
1899 if nvl(ln_slno,0) <> 0 then
1900 if gn_action = 3 then
1901 remove_dup_slno( p_organization_id => p_organization_id ,
1902 p_location_id => p_location_id ,
1903 p_fin_year => p_fin_year ,
1904 p_register_type => p_register_type ,
1905 p_slno => rec_slno.slno ,
1906 p_dup_cnt => rec_slno.rowcount );
1907 end if ;
1908
1909 capture_error( p_organization_id => p_organization_id ,
1910 p_location_id => p_location_id ,
1911 p_register_type => p_register_type ,
1912 p_fin_year => p_fin_year ,
1913 p_opening_balance => null ,
1914 p_error_codes => 'E18' ,
1915 p_slno => rec_slno.slno ,
1916 p_register_id => null ,
1917 p_rowcount => rec_slno.rowcount ,
1918 p_tax_type => null ,
1919 p_date => null ,
1920 p_month => null ,
1921 p_year => null
1922 ) ;
1923
1924 --return ;
1925 end if ;
1926 end loop ;
1927
1928 ln_slno := null ;
1929 ln_rowcount := null ;
1930
1931
1932 if nvl(gn_exists,0) =1
1933 then
1934
1935 begin
1936 execute immediate
1937 ' select slno
1938 from
1939 JAI_CMN_RG_23AC_II_TRXS
1940 where
1941 closing_balance <> nvl(opening_balance,0) +
1942 ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0)) -
1943 ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) + nvl(dr_additional_cvd,0)) AND
1944 register_type = ''' || p_register_type || ''' AND
1945 organization_id = ' || p_organization_id || ' AND
1946 location_id = ' || p_location_id || ' AND
1947 fin_year = ' || p_fin_year || ' AND
1948 trunc(creation_date) >= to_date(''' || gd_date || ''',''dd-mon-rrrr'')
1949 and rownum =1 ORDER BY register_id '
1950 into ln_register_id ;
1951
1952
1953 execute immediate
1954 ' select count(1)
1955 from
1956 JAI_CMN_RG_23AC_II_TRXS
1957 where
1958 closing_balance <> nvl(opening_balance,0) +
1959 ( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) ) -
1960 ( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) ) AND
1961 register_type = ''' || p_register_type || ''' AND
1962 organization_id = ' || p_organization_id || ' AND
1963 location_id = ' || p_location_id || ' AND
1964 fin_year = ' || p_fin_year || ' AND
1965 trunc(creation_date) >= to_date(''' || gd_date || ''',''dd-mon-rrrr'')
1966 and rownum =1 ORDER BY register_id '
1967 into ln_rowcount ;
1968
1969 exception
1970 when no_data_found then
1971 null ;
1972 end ;
1973 else
1974 open c_transaction_balance ;
1975 fetch c_transaction_balance into ln_slno ;
1976 ln_rowcount := c_transaction_balance%ROWCOUNT ;
1977 close c_transaction_balance ;
1978 end if ;
1979
1980 if nvl(ln_slno,0) <> 0 then
1981 capture_error( p_organization_id => p_organization_id ,
1982 p_location_id => p_location_id ,
1983 p_register_type => p_register_type ,
1984 p_fin_year => p_fin_year ,
1985 p_opening_balance => null ,
1986 p_error_codes => 'E03' ,
1987 p_slno => ln_slno ,
1988 p_register_id => NULL ,
1989 p_rowcount => ln_rowcount ,
1990 p_tax_type => null ,
1991 p_date => null ,
1992 p_month => null ,
1993 p_year => null
1994 ) ;
1995
1996
1997 IF gn_action = 3 THEN
1998 corr_exc_bal( p_register_type => p_register_type ,
1999 p_organization_id => p_organization_id ,
2000 p_location_id => p_location_id ,
2001 p_slno => ln_slno ,
2002 p_fin_year => p_fin_year ,
2003 p_last_updated_by => -5451134 );
2004 END IF;
2005
2006
2007
2008 return ;
2009 end if ;
2010
2011 FOR rec IN check_balances
2012 LOOP
2013 ln_closing_balance := null ;
2014 open c_get_closing_balance(rec.organization_id,rec.location_id, rec.fin_year, rec.register_type, rec.slno) ;
2015 fetch c_get_closing_balance into ln_closing_balance ;
2016 close c_get_closing_balance ;
2017
2018 if ln_closing_balance is null then
2019 open c_final_balance_rg23(rec.organization_id,rec.location_id, rec.fin_year-1, rec.register_type) ;
2020 fetch c_final_balance_rg23 into ln_closing_balance ;
2021 close c_final_balance_rg23 ;
2022
2023 if ln_closing_balance is null then
2024 ln_closing_balance := 0 ;
2025 end if ;
2026 end if ;
2027
2028 if nvl(rec.opening_balance,0) <> nvl(ln_closing_balance,0) then
2029 capture_error( p_organization_id => p_organization_id ,
2030 p_location_id => p_location_id ,
2031 p_register_type => p_register_type ,
2032 p_fin_year => p_fin_year ,
2033 p_opening_balance => null ,
2034 p_error_codes => 'E02' ,
2035 p_slno => rec.slno ,
2036 p_register_id => rec.register_id ,
2037 p_rowcount => null ,
2038 p_tax_type => null ,
2039 p_date => null ,
2040 p_month => null ,
2041 p_year => null
2042 ) ;
2043
2044
2045 IF gn_action = 3 THEN
2046 corr_exc_bal( p_register_type => p_register_type ,
2047 p_organization_id => p_organization_id ,
2048 p_location_id => p_location_id ,
2049 p_slno => rec.slno ,
2050 p_fin_year => p_fin_year ,
2051 p_last_updated_by => -5451134 );
2052 END IF;
2053
2054
2055 return ;
2056 end if;
2057
2058 ln_rg_other_amt := null ;
2059 open c_rg_others(rec.register_id, rec.register_type) ;
2060 fetch c_rg_others into ln_rg_other_amt ;
2061 close c_rg_others ;
2062
2063 if nvl(ln_rg_other_amt,0) <> nvl(rec.rg_other_amt,0)
2064 then
2065 capture_error( p_organization_id => p_organization_id ,
2066 p_location_id => p_location_id ,
2067 p_register_type => p_register_type ,
2068 p_fin_year => p_fin_year ,
2069 p_opening_balance => null ,
2070 p_error_codes => 'E09' ,
2071 p_slno => null ,
2072 p_register_id => rec.register_id ,
2073 p_rowcount => null ,
2074 p_tax_type => null ,
2075 p_date => null ,
2076 p_month => null ,
2077 p_year => null
2078 ) ;
2079
2080 IF gn_action = 3 THEN
2081
2082 upd_oth_tax( p_register_type => p_register_type,
2083 p_register_id => rec.register_id );
2084 END IF;
2085
2086
2087 end if ;
2088 END LOOP ;
2089
2090 END rg23_part_ii_validation;
2091 ------------------------------------ RG23_PART_II_VALIDATION ------------------------------------------------
2092
2093
2094 ------------------------------------ CAPTURE_ERROR ------------------------------------------------
2095 /*
2096
2097 Column list and their related information for the table JAI_TRX_GT
2098
2099 organization_id - JAI_INFO_N1
2100 location_id - JAI_INFO_N2
2101 register_type - JAI_INFO_V1
2102 fin_yr - JAI_INFO_N3
2103 opening_balance - JAI_INFO_N4
2104 credit_amount - JAI_INFO_N5
2105 debit_amount - JAI_INFO_N6
2106 closing_balance - JAI_INFO_N7
2107 Status - JAI_INFO_V2
2108 error_codes - JAI_INFO_V3
2109 slno - JAI_INFO_N8
2110 register_id - JAI_INFO_N9
2111 rowcount - JAI_INFO_N10
2112 tax_type - JAI_INFO_V4
2113 date - JAI_INFO_D1
2114 month - JAI_INFO_V5
2115 year - JAI_INFO_N11
2116
2117 */
2118
2119
2120
2121 PROCEDURE capture_error
2122 ( p_organization_id number,
2123 p_location_id number,
2124 p_register_type varchar2,
2125 p_fin_year number,
2126 p_opening_balance number,
2127 p_error_codes varchar2,
2128 p_slno number,
2129 p_register_id number,
2130 p_rowcount number,
2131 p_tax_type varchar2,
2132 p_date date,
2133 p_month varchar2,
2134 p_year number
2135 )
2136 IS
2137 BEGIN
2138
2139 insert into JAI_TRX_GT
2140 ( JAI_INFO_N1 ,
2141 JAI_INFO_N2 ,
2142 JAI_INFO_V1 ,
2143 JAI_INFO_N3 ,
2144 JAI_INFO_N4 ,
2145 JAI_INFO_V3 ,
2146 JAI_INFO_N8 ,
2147 JAI_INFO_N9 ,
2148 JAI_INFO_N10 ,
2149 JAI_INFO_V4 ,
2150 JAI_INFO_D1 ,
2151 JAI_INFO_V5 ,
2152 JAI_INFO_N11
2153 )
2154 values
2155 ( p_organization_id ,
2156 p_location_id ,
2157 p_register_type ,
2158 p_fin_year ,
2159 p_opening_balance ,
2160 p_error_codes ,
2161 p_slno ,
2162 p_register_id ,
2163 p_rowcount ,
2164 p_tax_type ,
2165 p_date ,
2166 p_month ,
2167 p_year
2168 ) ;
2169
2170 END capture_error ;
2171 ------------------------------------ CAPTURE_ERROR ------------------------------------------------
2172
2173
2174
2175 -----------------------------------------PROCESS_RG_TRX--------------------------------
2176 PROCEDURE process_rg_trx
2177 ( errbuf out nocopy varchar2,
2178 retcode out nocopy varchar2,
2179 p_date VARCHAR2,
2180 p_organization_id NUMBER ,
2181 p_location_id NUMBER ,
2182 p_register_type VARCHAR2,
2183 p_action NUMBER ,
2184 p_debug VARCHAR2 DEFAULT NULL ,
2185 p_backup VARCHAR2 DEFAULT NULL
2186 )
2187
2188 IS
2189
2190 Cursor c_rg23_balance( cp_organization_id number,
2191 cp_location_id number,
2192 cp_register_type varchar2 )
2193 is
2194 select closing_balance , slno
2195 from JAI_CMN_RG_23AC_II_TRXS
2196 where organization_id = cp_organization_id
2197 and location_id = cp_location_id
2198 and register_type = cp_register_type
2199 order by fin_year desc , slno desc ;
2200
2201 cursor c_rg_balance( cp_organization_id number,
2202 cp_location_id number,
2203 cp_register_type varchar2 )
2204 is
2205 select decode(cp_register_type, 'A', rg23a_balance, 'C', rg23c_balance, 'PLA', pla_balance)
2206 from JAI_CMN_RG_BALANCES
2207 where organization_id = cp_organization_id
2208 and location_id = cp_location_id ;
2209
2210 Cursor c_pla_balance(cp_organization_id number,
2211 cp_location_id number)
2212 is
2213 select closing_balance, slno
2214 from JAI_CMN_RG_PLA_TRXS
2215 where organization_id = cp_organization_id
2216 and location_id = cp_location_id
2217 order by fin_year desc , slno desc ;
2218
2219 ln_rg23_balance number ;
2220 ln_pla_balance number ;
2221 ln_rg_balance number ;
2222
2223 CURSOR cur_pla_trans_amt(cp_organization_id NUMBER, cp_location_id NUMBER)
2224 IS
2225 SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
2226 - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
2227 FROM JAI_CMN_RG_PLA_TRXS
2228 WHERE organization_id = cp_organization_id
2229 AND location_id = cp_location_id;
2230
2231 ln_pla_trans_amt number ;
2232
2233 Cursor c_rg_slno_balance( cp_organization_id number,
2234 cp_location_id number,
2235 cp_register_type varchar2
2236 )
2237 is
2238 select balance , slno
2239 from JAI_CMN_RG_SLNOS
2240 where organization_id = cp_organization_id
2241 and location_id = cp_location_id
2242 and register_type = cp_register_type;
2243
2244 cursor pla_cons_amt ( cp_organization_id number ,
2245 cp_location_id number ,
2246 cp_creation_date date
2247 )
2248 is
2249 select
2250 sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) pla_cons_amt ,
2251 sum(other_tax_credit) pla_oth_amt
2252 from JAI_CMN_RG_PLA_TRXS
2253 where
2254 transaction_source_num is null and
2255 organization_id = cp_organization_id and
2256 location_id = cp_location_id and
2257 trunc(creation_date) = cp_creation_date ;
2258
2259 l_apps_short_name CONSTANT VARCHAR2(2) := 'JA';
2260
2261 cursor c_check_addl_cvd
2262 is
2263 select 1
2264 from all_tab_cols
2265 where
2266 table_name = 'JAI_CMN_RG_23AC_II_TRXS'
2267 and column_name IN ( 'DR_ADDITIONAL_CVD', 'CR_ADDITIONAL_CVD')
2268 AND owner = l_apps_short_name ; /*added by ssawant*/
2269
2270 cursor c_get_rg23_open_bal ( cp_organization_id JAI_CMN_RG_23AC_II_TRXS.ORGANIZATION_ID%TYPE ,
2271 cp_location_id JAI_CMN_RG_23AC_II_TRXS.LOCATION_ID%TYPE ,
2272 cp_register_type JAI_CMN_RG_23AC_II_TRXS.REGISTER_TYPE%TYPE ,
2273 cp_date date
2274 )
2275 is
2276 select
2277 nvl(opening_balance,0)
2278 from
2279 JAI_CMN_RG_23AC_II_TRXS
2280 where
2281 organization_id = cp_organization_id AND
2282 location_id = cp_location_id AND
2283 register_type = cp_register_type AND
2284 trunc(creation_date) >= cp_date
2285 order by fin_year, slno ;
2286
2287 cursor c_get_rg23_tran_amt ( cp_organization_id JAI_CMN_RG_23AC_II_TRXS.ORGANIZATION_ID%TYPE ,
2288 cp_location_id JAI_CMN_RG_23AC_II_TRXS.LOCATION_ID%TYPE ,
2289 cp_register_type JAI_CMN_RG_23AC_II_TRXS.REGISTER_TYPE%TYPE ,
2290 cp_date date
2291 )
2292 is
2293 select
2294 sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) credit_amount ,
2295 sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0)) debit_amount
2296 from
2297 JAI_CMN_RG_23AC_II_TRXS
2298 where
2299 organization_id = cp_organization_id AND
2300 location_id = cp_location_id AND
2301 register_type = cp_register_type AND
2302 trunc(creation_date) >= cp_date ;
2303
2304 cursor c_rg_slno_bal ( cp_organization_id JAI_CMN_RG_23AC_II_TRXS.ORGANIZATION_ID%TYPE ,
2305 cp_location_id JAI_CMN_RG_23AC_II_TRXS.LOCATION_ID%TYPE ,
2306 cp_register_type JAI_CMN_RG_23AC_II_TRXS.REGISTER_TYPE%TYPE
2307 )
2308 is
2309 select balance
2310 from JAI_CMN_RG_SLNOS
2311 where
2312 organization_id = cp_organization_id AND
2313 location_id = cp_location_id AND
2314 register_type = cp_register_type ;
2315
2316 cursor c_err_exists(cp_organization_id number ,
2317 cp_location_id number ,
2318 cp_register_type varchar2
2319 )
2320 is
2321 select count(1)
2322 from JAI_TRX_GT
2323 where
2324 JAI_INFO_N1 = cp_organization_id and
2325 JAI_INFO_N2 = cp_location_id and
2326 JAI_INFO_V1 = cp_register_type ;
2327
2328 cursor c_cess_err_exists(cp_organization_id number ,
2329 cp_location_id number ,
2330 cp_register_type varchar2 ,
2331 cp_tax_type VARCHAR2
2332 )
2333 is
2334 select count(1)
2335 from JAI_TRX_GT
2336 where
2337 JAI_INFO_N1 = cp_organization_id AND
2338 JAI_INFO_N2 = cp_location_id AND
2339 JAI_INFO_V1 = cp_register_type AND
2340 JAI_INFO_V4 = cp_tax_type;
2341
2342 Cursor c_get_pla_open_bal( cp_organization_id number,
2343 cp_location_id number,
2344 cp_date date
2345 )
2346 is
2347 SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
2348 - nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
2349 FROM JAI_CMN_RG_PLA_TRXS
2350 WHERE organization_id = cp_organization_id
2351 AND location_id = cp_location_id
2352 and trunc(creation_date) < cp_date ;
2353
2354 cursor c_get_pla_tran_amt ( cp_organization_id JAI_CMN_RG_PLA_TRXS.ORGANIZATION_ID%TYPE ,
2355 cp_location_id JAI_CMN_RG_PLA_TRXS.LOCATION_ID%TYPE ,
2356 cp_date date
2357 )
2358 is
2359 select
2360 sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) credit_amount ,
2361 sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0)) debit_amount
2362 from
2363 JAI_CMN_RG_PLA_TRXS
2364 where
2365 organization_id = cp_organization_id AND
2366 location_id = cp_location_id AND
2367 trunc(creation_date) >= cp_date ;
2368
2369 CURSOR cur_get_fin_year( cp_organization_id NUMBER,
2370 cp_date DATE ) IS
2371 SELECT fin_year
2372 FROM JAI_CMN_FIN_YEARS
2373 WHERE organization_id = cp_organization_id
2374 AND cp_date between fin_year_start_date and fin_year_end_date;
2375
2376 CURSOR cur_get_rg23_cess_slno( cp_organization_id NUMBER,
2377 cp_location_id NUMBER,
2378 cp_fin_year NUMBER,
2379 cp_tax_type VARCHAR2,
2380 cp_date DATE) IS
2381 SELECT max(slno)
2382 FROM JAI_CMN_RG_23AC_II_TRXS jrg
2383 WHERE organization_id = p_organization_id
2384 AND location_id = p_location_id
2385 AND fin_year = cp_fin_year
2386 AND register_type = p_register_type
2387 AND trunc(creation_date) < cp_date
2388 AND EXISTS ( SELECT 1
2389 FROM JAI_CMN_RG_OTHERS
2390 WHERE source_type = 1
2391 AND source_register_id = jrg.register_id
2392 AND tax_type = cp_tax_type );
2393
2394 CURSOR cur_get_rg23_cess_trans(cp_organization_id NUMBER,
2395 cp_location_id NUMBER,
2396 cp_date DATE ,
2397 cp_tax_type VARCHAR2)
2398 IS
2399 SELECT sum(credit),sum(debit)
2400 FROM JAI_CMN_RG_OTHERS
2401 WHERE source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
2402 AND source_register_id in ( SELECT register_id
2403 FROM JAI_CMN_RG_23AC_II_TRXS
2404 WHERE organization_id = cp_organization_id
2405 AND location_id = cp_location_id
2406 AND register_type = p_register_type
2407 AND trunc(creation_date) >= cp_date)
2408 AND tax_type = cp_tax_type;
2409
2410 CURSOR cur_get_pla_cess_trans(cp_organization_id NUMBER,
2411 cp_location_id NUMBER,
2412 cp_date DATE ,
2413 cp_tax_type VARCHAR2)
2414 IS
2415 SELECT sum(credit),sum(debit)
2416 FROM JAI_CMN_RG_OTHERS
2417 WHERE source_register = 'PLA'
2418 AND source_register_id in ( SELECT register_id
2419 FROM JAI_CMN_RG_PLA_TRXS
2420 WHERE organization_id = cp_organization_id
2421 AND location_id = cp_location_id
2422 AND trunc(creation_date) >= cp_date)
2423 AND tax_type = cp_tax_type;
2424
2425
2426 CURSOR cur_get_pla_cess_slno(cp_organization_id NUMBER ,
2427 cp_location_id NUMBER ,
2428 cp_fin_year NUMBER ,
2429 cp_tax_type VARCHAR2 ,
2430 cp_date DATE ) IS
2431 SELECT max(slno)
2432 FROM JAI_CMN_RG_PLA_TRXS jpl
2433 WHERE organization_id = p_organization_id
2434 AND location_id = p_location_id
2435 AND fin_year = cp_fin_year
2436 AND trunc(creation_date) < cp_date
2437 AND EXISTS ( SELECT 1
2438 FROM JAI_CMN_RG_OTHERS
2439 WHERE source_type = 2
2440 AND source_register_id = jpl.register_id
2441 AND tax_type = cp_tax_type );
2442
2443 CURSOR get_rg23_cess_closing_bal(cp_fin_year NUMBER,cp_slno NUMBER,cp_tax_type VARCHAR2) IS
2444 SELECT closing_balance
2445 FROM JAI_CMN_RG_OTHERS
2446 WHERE source_type = 1
2447 AND tax_type = cp_tax_type
2448 AND source_register_id in ( SELECT register_id
2449 FROM JAI_CMN_RG_23AC_II_TRXS
2450 WHERE organization_id = p_organization_id
2451 AND location_id = p_location_id
2452 AND register_type = p_register_type
2453 AND fin_year = cp_fin_year
2454 AND slno = nvl(cp_slno,1) );
2455
2456 CURSOR get_pla_cess_closing_bal(cp_fin_year NUMBER,cp_slno NUMBER,cp_tax_type VARCHAR2) IS
2457 SELECT closing_balance
2458 FROM JAI_CMN_RG_OTHERS
2459 WHERE source_type = 2
2460 AND tax_type = cp_tax_type
2461 AND source_register_id in ( SELECT register_id
2462 FROM JAI_CMN_RG_PLA_TRXS
2463 WHERE organization_id = p_organization_id
2464 AND location_id = p_location_id
2465 AND fin_year = cp_fin_year
2466 AND slno = nvl(cp_slno,1) );
2467
2468 CURSOR cur_get_final_cess_bal(cp_organization_id NUMBER,
2469 cp_location_id NUMBER,
2470 cp_tax_type VARCHAR2) IS
2471 SELECT balance
2472 FROM JAI_CMN_RG_OTH_BALANCES
2473 WHERE org_unit_id = ( SELECT org_unit_id
2474 FROM JAI_CMN_INVENTORY_ORGS
2475 WHERE organization_id = cp_organization_id
2476 AND location_id = cp_location_id )
2477 AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
2478 AND tax_type = cp_tax_type;
2479
2480 ln_rg23_cess_slno NUMBER;
2481 ln_pla_cess_slno NUMBER;
2482 ln_exc_cess_rg23 NUMBER;
2483 ln_cvd_cess_rg23 NUMBER;
2484 ln_sh_exc_cess_rg23 NUMBER;
2485 ln_sh_cvd_cess_rg23 NUMBER;
2486 ln_exc_cess_rg23_final NUMBER;
2487 ln_cvd_cess_rg23_final NUMBER;
2488 ln_sh_exc_cess_rg23_final NUMBER;
2489 ln_sh_cvd_cess_rg23_final NUMBER;
2490 ln_exc_cess_pla NUMBER;
2491 ln_cvd_cess_pla NUMBER;
2492 ln_sh_exc_cess_pla NUMBER;
2493 ln_sh_cvd_cess_pla NUMBER;
2494 ln_exc_cess_pla_final NUMBER;
2495 ln_cvd_cess_pla_final NUMBER;
2496 ln_sh_exc_cess_pla_final NUMBER;
2497 ln_sh_cvd_cess_pla_final NUMBER;
2498 ln_exc_cess_rg23_cr NUMBER;
2499 ln_cvd_cess_rg23_cr NUMBER;
2500 ln_sh_exc_cess_rg23_cr NUMBER;
2501 ln_sh_cvd_cess_rg23_cr NUMBER;
2502 ln_exc_cess_rg23_dr NUMBER;
2503 ln_cvd_cess_rg23_dr NUMBER;
2504 ln_sh_exc_cess_rg23_dr NUMBER;
2505 ln_sh_cvd_cess_rg23_dr NUMBER;
2506 ln_exc_cess_pla_cr NUMBER;
2507 ln_cvd_cess_pla_cr NUMBER;
2508 ln_sh_exc_cess_pla_cr NUMBER;
2509 ln_sh_cvd_cess_pla_cr NUMBER;
2510 ln_exc_cess_pla_dr NUMBER;
2511 ln_cvd_cess_pla_dr NUMBER;
2512 ln_sh_exc_cess_pla_dr NUMBER;
2513 ln_sh_cvd_cess_pla_dr NUMBER;
2514
2515 ln_fin_year NUMBER;
2516
2517 ln_err_exists number ;
2518 lv_status varchar2(20) ;
2519 lv_exc_cess_status varchar2(20) ;
2520 lv_cvd_cess_status varchar2(20) ;
2521 lv_sh_exc_cess_status varchar2(20) ;
2522 lv_sh_cvd_cess_status varchar2(20) ;
2523
2524 ln_open_bal number;
2525 ln_credit_amount number;
2526 ln_debit_amount number;
2527 ln_slno_bal number;
2528 ln_header number ;
2529
2530 ln_pla_cons_amt number ;
2531 ln_pla_oth_amt number ;
2532 ln_pla_slno number ;
2533 ln_rg23_slno number ;
2534 ln_rg23_finyr_bal number ;
2535 ln_rg_slno_balance number ;
2536 ln_rg_slno number ;
2537 ln_slno number ;
2538
2539 pv_date DATE DEFAULT fnd_date.canonical_to_date(p_date);
2540
2541 BEGIN
2542
2543 FND_FILE.put_line( FND_FILE.log, ' Process RG Trx Inputs. Organization Id:' || p_organization_id ||
2544 ' Location Id : ' || p_location_id ||
2545 ' Register Type : ' || NVL(p_register_type,'ALL') ||
2546 ' p_date : ' || pv_date ||
2547 ' p_action : ' || p_action ||
2548 ' p_debug : ' || p_debug ||
2549 ' p_backup : ' || p_backup ) ;
2550
2551 FND_FILE.put_line( FND_FILE.log, '' ) ;
2552 FND_FILE.put_line( FND_FILE.log, '' ) ;
2553
2554 retcode := 0 ;
2555 gd_date := to_date(pv_date,'DD/MM/RRRR') ;
2556 gn_action := p_action ;
2557 gn_exists := null ;
2558
2559 if p_action = 3 and nvl(p_backup,'N') = 'N' then
2560 raise_application_error(-20054, ' Pls take a backup of the following tables from JA before you
2561 proceed with the fix : JAI_CMN_RG_23AC_II_TRXS,JAI_CMN_RG_PLA_TRXS ,JAI_CMN_RG_BALANCES ,JAI_CMN_RG_SLNOS ,JAI_CMN_RG_OTHERS ,JAI_CMN_RG_OTH_BALANCES ,JAI_CMN_RG_PERIOD_BALS' ) ;
2562 end if ;
2563
2564 open c_check_addl_cvd ;
2565 fetch c_check_addl_cvd into gn_exists ;
2566 close c_check_addl_cvd ;
2567
2568 if (p_register_type is null ) or (p_register_type IN ('A', 'C'))
2569 then
2570 -- To verify intra table data
2571 for rec in ( select
2572 distinct organization_id, location_id, fin_year, register_type
2573 from JAI_CMN_RG_23AC_II_TRXS
2574 where
2575 trunc(creation_date) >= pv_date and
2576 register_type = nvl(p_register_type, register_type) and
2577 location_id = nvl(p_location_id , location_id) and
2578 organization_id = nvl(p_organization_id, organization_id)
2579 order by register_type, organization_id, location_id, fin_year )
2580 loop
2581
2582 -- call to validate records in JAI_CMN_RG_23AC_II_TRXS
2583 rg23_part_ii_validation( p_organization_id => rec.organization_id ,
2584 p_location_id => rec.location_id ,
2585 p_fin_year => rec.fin_year ,
2586 p_register_type => rec.register_type
2587 ) ;
2588
2589
2590 end loop ;
2591
2592 -- To verify inter table data
2593 for rg23_bal_rec in ( select
2594 distinct organization_id, location_id, register_type
2595 from JAI_CMN_RG_23AC_II_TRXS
2596 where
2597 register_type = nvl(p_register_type, register_type) and
2598 organization_id = nvl(p_organization_id, organization_id) and
2599 location_id = nvl(p_location_id , location_id) and
2600 trunc(creation_date) >= pv_date
2601 order by register_type, organization_id, location_id )
2602 loop
2603
2604 validate_period_balances( p_organization_id => rg23_bal_rec.organization_id,
2605 p_location_id => rg23_bal_rec.location_id,
2606 p_register_type => rg23_bal_rec.register_type,
2607 p_date => pv_date
2608 ) ;
2609
2610 -- call to validate cess info
2611 validate_rg_others
2612 ( p_organization_id => rg23_bal_rec.organization_id,
2613 p_location_id => rg23_bal_rec.location_id,
2614 p_register_type => rg23_bal_rec.register_type,
2615 p_date => pv_date
2616 ) ;
2617
2618
2619 -- to fetch rg23 last record balance
2620 ln_rg23_balance := null ;
2621 ln_rg23_slno := null ;
2622 open c_rg23_balance(rg23_bal_rec.organization_id,rg23_bal_rec.location_id, rg23_bal_rec.register_type) ;
2623 fetch c_rg23_balance into ln_rg23_balance,ln_rg23_slno ;
2624 close c_rg23_balance ;
2625
2626 -- code to validate balance in JAI_CMN_RG_BALANCES
2627 ln_rg_balance := null ;
2628 open c_rg_balance(rg23_bal_rec.organization_id,rg23_bal_rec.location_id, rg23_bal_rec.register_type) ;
2629 fetch c_rg_balance into ln_rg_balance ;
2630 close c_rg_balance ;
2631
2632 if nvl(ln_rg_balance,0) <> nvl(ln_rg23_balance,0) then
2633 if gn_action = 3 then
2634 corr_final_bal( p_organization_id => rg23_bal_rec.organization_id ,
2635 p_location_id => rg23_bal_rec.location_id ,
2636 p_register_type => rg23_bal_rec.register_type ,
2637 p_tax_type => NULL ,
2638 p_closing_balance => ln_rg23_balance );
2639
2640 end if ;
2641 capture_error( p_organization_id => rg23_bal_rec.organization_id ,
2642 p_location_id => rg23_bal_rec.location_id ,
2643 p_register_type => rg23_bal_rec.register_type ,
2644 p_fin_year => null ,
2645 p_opening_balance => null ,
2646 p_error_codes => 'E13' ,
2647 p_slno => null ,
2648 p_register_id => null ,
2649 p_rowcount => null ,
2650 p_tax_type => null ,
2651 p_date => null ,
2652 p_month => null ,
2653 p_year => null
2654 ) ;
2655 end if ;
2656
2657 -- Code to Validate balance in JAI_CMN_RG_SLNOS
2658 ln_rg_slno_balance := null ;
2659 ln_rg_slno := null ;
2660 open c_rg_slno_balance(rg23_bal_rec.organization_id,rg23_bal_rec.location_id, rg23_bal_rec.register_type) ;
2661 fetch c_rg_slno_balance into ln_rg_slno_balance, ln_rg_slno;
2662 close c_rg_slno_balance ;
2663
2664 if nvl(ln_rg23_balance,0) <> nvl(ln_rg_slno_balance,0) or (nvl(ln_rg23_slno,0) <> nvl(ln_rg_slno,0) ) then
2665 if gn_action = 3 then
2666 corr_final_bal( p_organization_id => rg23_bal_rec.organization_id ,
2667 p_location_id => rg23_bal_rec.location_id ,
2668 p_register_type => rg23_bal_rec.register_type ,
2669 p_tax_type => NULL ,
2670 p_closing_balance => ln_rg23_balance );
2671
2672 corr_final_slno( p_organization_id => rg23_bal_rec.organization_id ,
2673 p_location_id => rg23_bal_rec.location_id ,
2674 p_register_type => rg23_bal_rec.register_type ,
2675 p_slno => ln_rg23_slno );
2676 end if ;
2677 capture_error( p_organization_id => rg23_bal_rec.organization_id ,
2678 p_location_id => rg23_bal_rec.location_id ,
2679 p_register_type => rg23_bal_rec.register_type ,
2680 p_fin_year => null ,
2681 p_opening_balance => null ,
2682 p_error_codes => 'E11' ,
2683 p_slno => null ,
2684 p_register_id => null ,
2685 p_rowcount => null ,
2686 p_tax_type => null ,
2687 p_date => null ,
2688 p_month => null ,
2689 p_year => null
2690 ) ;
2691 end if ;
2692
2693 end loop ;
2694 end if ;
2695
2696 if (p_register_type is null ) or (p_register_type = 'PLA')
2697 then
2698 -- To verify intra table data
2699 for rec in ( select
2700 distinct organization_id, location_id, fin_year
2701 from JAI_CMN_RG_PLA_TRXS
2702 where
2703 trunc(creation_date) >= pv_date and
2704 location_id = nvl(p_location_id , location_id) and
2705 organization_id = nvl(p_organization_id, organization_id)
2706 order by organization_id, location_id, fin_year )
2707 loop
2708
2709 pla_validation( p_organization_id => rec.organization_id ,
2710 p_location_id => rec.location_id ,
2711 p_fin_year => rec.fin_year
2712 ) ;
2713 end loop ;
2714
2715 -- To verify inter table data
2716 for pla_bal_rec in ( select
2717 distinct organization_id, location_id
2718 from JAI_CMN_RG_PLA_TRXS
2719 where
2720 organization_id = nvl(p_organization_id, organization_id) and
2721 location_id = nvl(p_location_id , location_id) and
2722 trunc(creation_date) >= pv_date
2723 order by organization_id, location_id
2724 )
2725 loop
2726
2727 -- call to validate pla cess info
2728 validate_rg_others
2729 ( p_organization_id => pla_bal_rec.organization_id,
2730 p_location_id => pla_bal_rec.location_id,
2731 p_register_type => 'PLA',
2732 p_date => pv_date
2733 ) ;
2734
2735 ln_pla_balance := null ;
2736 ln_pla_slno := null ;
2737 open c_pla_balance(pla_bal_rec.organization_id,pla_bal_rec.location_id) ;
2738 fetch c_pla_balance into ln_pla_balance,ln_pla_slno ;
2739 close c_pla_balance ;
2740
2741 ln_rg_balance := null ;
2742 open c_rg_balance(pla_bal_rec.organization_id,pla_bal_rec.location_id, 'PLA') ;
2743 fetch c_rg_balance into ln_rg_balance ;
2744 close c_rg_balance ;
2745
2746 if nvl(ln_rg_balance,0) <> nvl(ln_pla_balance,0) then
2747 if gn_action = 3 then
2748 corr_final_bal( p_organization_id => pla_bal_rec.organization_id ,
2749 p_location_id => pla_bal_rec.location_id ,
2750 p_register_type => 'PLA' ,
2751 p_tax_type => NULL ,
2752 p_closing_balance => ln_pla_balance );
2753 end if ;
2754
2755 capture_error( p_organization_id => pla_bal_rec.organization_id ,
2756 p_location_id => pla_bal_rec.location_id ,
2757 p_register_type => 'PLA' ,
2758 p_fin_year => null ,
2759 p_opening_balance => null ,
2760 p_error_codes => 'E14' ,
2761 p_slno => null ,
2762 p_register_id => null ,
2763 p_rowcount => null ,
2764 p_tax_type => null ,
2765 p_date => null ,
2766 p_month => null ,
2767 p_year => null
2768 ) ;
2769 end if ;
2770
2771 -- Code to Validate balance in JAI_CMN_RG_SLNOS
2772 ln_rg_slno_balance := null ;
2773 ln_rg_slno := null ;
2774 open c_rg_slno_balance(pla_bal_rec.organization_id,pla_bal_rec.location_id, 'PLA') ;
2775 fetch c_rg_slno_balance into ln_rg_slno_balance, ln_rg_slno;
2776 close c_rg_slno_balance ;
2777
2778 if nvl(ln_pla_balance,0) <> nvl(ln_rg_slno_balance,0) or (nvl(ln_pla_slno,0) <> nvl(ln_rg_slno,0) ) then
2779
2780 if gn_action = 3 then
2781 corr_final_bal( p_organization_id => pla_bal_rec.organization_id ,
2782 p_location_id => pla_bal_rec.location_id ,
2783 p_register_type => 'PLA' ,
2784 p_tax_type => NULL ,
2785 p_closing_balance => ln_pla_balance );
2786
2787 corr_final_slno( p_organization_id => pla_bal_rec.organization_id ,
2788 p_location_id => pla_bal_rec.location_id ,
2789 p_register_type => 'PLA' ,
2790 p_slno => ln_pla_slno );
2791 end if ;
2792
2793
2794 capture_error( p_organization_id => pla_bal_rec.organization_id ,
2795 p_location_id => pla_bal_rec.location_id ,
2796 p_register_type => 'PLA' ,
2797 p_fin_year => null ,
2798 p_opening_balance => null ,
2799 p_error_codes => 'E11' ,
2800 p_slno => null ,
2801 p_register_id => null ,
2802 p_rowcount => null ,
2803 p_tax_type => null ,
2804 p_date => null ,
2805 p_month => null ,
2806 p_year => null
2807 ) ;
2808 end if ;
2809
2810 open cur_pla_trans_amt(pla_bal_rec.organization_id,pla_bal_rec.location_id) ;
2811 fetch cur_pla_trans_amt into ln_pla_trans_amt ;
2812 close cur_pla_trans_amt ;
2813
2814 if nvl(ln_rg_balance,0) <> nvl(ln_pla_trans_amt,0) then
2815 capture_error( p_organization_id => pla_bal_rec.organization_id ,
2816 p_location_id => pla_bal_rec.location_id ,
2817 p_register_type => 'PLA' ,
2818 p_fin_year => null ,
2819 p_opening_balance => null ,
2820 p_error_codes => 'E15' ,
2821 p_slno => null ,
2822 p_register_id => null ,
2823 p_rowcount => null ,
2824 p_tax_type => null ,
2825 p_date => null ,
2826 p_month => null ,
2827 p_year => null
2828 ) ;
2829 end if ;
2830 end loop ;
2831
2832 end if ;
2833
2834 -- check for consolidation
2835 if p_register_type is null
2836 then
2837 For cons_rec in ( select
2838 distinct organization_id, location_id
2839 from JAI_CMN_RG_23AC_II_TRXS
2840 where
2841 organization_id = nvl(p_organization_id, organization_id) and
2842 location_id = nvl(p_location_id , location_id) and
2843 trunc(creation_date) >= pv_date
2844 order by organization_id, location_id
2845 )
2846 loop
2847
2848 For rg23_cons_rec in ( select
2849 sum( nvl(dr_basic_ed,0) +nvl(dr_additional_ed,0) +nvl(dr_other_ed,0) ) rg23_cons_amt ,
2850 sum(other_tax_debit) rg23_oth_amt,
2851 trunc(creation_date) cons_date
2852 from JAI_CMN_RG_23AC_II_TRXS
2853 where
2854 transaction_source_num is null and
2855 organization_id = cons_rec.organization_id and
2856 location_id = cons_rec.location_id and
2857 trunc(creation_date) >= pv_date
2858 group by trunc(creation_date)
2859 )
2860 loop
2861 open pla_cons_amt(cons_rec.organization_id,cons_rec.location_id , rg23_cons_rec.cons_date) ;
2862 fetch pla_cons_amt into ln_pla_cons_amt, ln_pla_oth_amt ;
2863 close pla_cons_amt ;
2864
2865 if nvl(ln_pla_cons_amt,0) <> nvl(rg23_cons_rec.rg23_cons_amt,0)
2866 then
2867 capture_error( p_organization_id => cons_rec.organization_id ,
2868 p_location_id => cons_rec.location_id ,
2869 p_register_type => null ,
2870 p_fin_year => null ,
2871 p_opening_balance => null ,
2872 p_error_codes => 'E16' ,
2873 p_slno => null ,
2874 p_register_id => null ,
2875 p_rowcount => null ,
2876 p_tax_type => null ,
2877 p_date => rg23_cons_rec.cons_date ,
2878 p_month => null ,
2879 p_year => null
2880 ) ;
2881 end if ;
2882
2883 if nvl(ln_pla_oth_amt,0) <> nvl(rg23_cons_rec.rg23_oth_amt,0)
2884 then
2885 capture_error( p_organization_id => cons_rec.organization_id ,
2886 p_location_id => cons_rec.location_id ,
2887 p_register_type => null ,
2888 p_fin_year => null ,
2889 p_opening_balance => null ,
2890 p_error_codes => 'E16' ,
2891 p_slno => null ,
2892 p_register_id => null ,
2893 p_rowcount => null ,
2894 p_tax_type => null ,
2895 p_date => rg23_cons_rec.cons_date ,
2896 p_month => null ,
2897 p_year => null
2898 ) ;
2899 end if ;
2900 end loop ;
2901 end loop ;
2902
2903 end if ;
2904
2905
2906 -- Code to populate the Log
2907
2908
2909 if (p_register_type is null ) or (p_register_type IN ('A', 'C'))
2910 then
2911
2912 ln_header := 1 ;
2913
2914 FND_FILE.PUT_LINE(FND_FILE.LOG, '| Organization Id |' || ' Location Id |' || ' Register Type |' || ' Tax Type |
2915 ' || ' Data State |'|| ' Opening Balance |' || 'Credit Amount |' || ' Debit Amount |' || ' Closing Bal(RG) |' || ' Closing Bal(RG Slno) |' || ' Closing Bal(Register) |' ) ;
2916 FND_FILE.PUT_LINE(FND_FILE.LOG, '| --------------- |' || ' ----------- |' || ' ------------- |' || ' -------------------- |
2917 ' || ' ------ |'|| ' --------------- |' || '------------- |' || ' ------------ |' || ' --------------- |' || ' -------------------- |' || ' --------------------- |' ) ;
2918
2919 for rg23_log_rec in ( select
2920 distinct organization_id, location_id, register_type
2921 from JAI_CMN_RG_23AC_II_TRXS
2922 where
2923 register_type = nvl(p_register_type, register_type) and
2924 organization_id = nvl(p_organization_id, organization_id) and
2925 location_id = nvl(p_location_id , location_id) and
2926 trunc(creation_date) >= pv_date
2927 order by organization_id, location_id, register_type )
2928 loop
2929
2930 ln_open_bal := null ;
2931 ln_credit_amount := null ;
2932 ln_debit_amount := null ;
2933 ln_slno_bal := null ;
2934 ln_rg23_balance := null ;
2935 ln_rg_balance := null ;
2936 lv_status := null ;
2937 ln_err_exists := null ;
2938 ln_rg23_slno := null ;
2939
2940
2941 OPEN cur_get_fin_year( rg23_log_rec.organization_id,pv_date ) ;
2942 FETCH cur_get_fin_year INTO ln_fin_year;
2943 CLOSE cur_get_fin_year;
2944
2945 open c_get_rg23_open_bal(rg23_log_rec.organization_id, rg23_log_rec.location_id, rg23_log_rec.register_type, pv_date) ;
2946 fetch c_get_rg23_open_bal into ln_open_bal ;
2947 close c_get_rg23_open_bal ;
2948
2949 if nvl(gn_exists,0) =1
2950 then
2951 execute immediate
2952 ' select
2953 sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0)) credit_amount ,
2954 sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) + nvl(dr_additional_cvd,0)) debit_amount
2955 from
2956 JAI_CMN_RG_23AC_II_TRXS
2957 where
2958 organization_id = ' || rg23_log_rec.organization_id || ' AND
2959 location_id = ' || rg23_log_rec.location_id || ' AND
2960 register_type = ''' || rg23_log_rec.register_type || ''' AND
2961 trunc(creation_date) >= ''' || pv_date || ''''
2962 into ln_credit_amount, ln_debit_amount ;
2963 else
2964 open c_get_rg23_tran_amt(rg23_log_rec.organization_id, rg23_log_rec.location_id, rg23_log_rec.register_type, pv_date) ;
2965 fetch c_get_rg23_tran_amt into ln_credit_amount, ln_debit_amount ;
2966 close c_get_rg23_tran_amt ;
2967 end if ;
2968
2969 ln_rg23_cess_slno := NULL;
2970 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year,'EXCISE_EDUCATION_CESS',pv_date);
2971 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
2972 CLOSE cur_get_rg23_cess_slno;
2973
2974 IF ln_rg23_cess_slno IS NULL THEN
2975
2976 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year-1,'EXCISE_EDUCATION_CESS',pv_date);
2977 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
2978 CLOSE cur_get_rg23_cess_slno;
2979
2980 IF ln_rg23_cess_slno IS NULL THEN
2981
2982 ln_exc_cess_rg23 := 0;
2983
2984 ELSE
2985
2986 OPEN get_rg23_cess_closing_bal(ln_fin_year-1,ln_rg23_cess_slno,'EXCISE_EDUCATION_CESS') ;
2987 FETCH get_rg23_cess_closing_bal INTO ln_exc_cess_rg23;
2988 CLOSE get_rg23_cess_closing_bal;
2989
2990 END IF;
2991
2992 ELSE
2993
2994 OPEN get_rg23_cess_closing_bal(ln_fin_year,ln_rg23_cess_slno,'EXCISE_EDUCATION_CESS') ;
2995 FETCH get_rg23_cess_closing_bal INTO ln_exc_cess_rg23;
2996 CLOSE get_rg23_cess_closing_bal;
2997
2998 END IF;
2999
3000 OPEN cur_get_rg23_cess_trans(rg23_log_rec.organization_id,
3001 rg23_log_rec.location_id ,
3002 pv_date ,
3003 'EXCISE_EDUCATION_CESS');
3004 FETCH cur_get_rg23_cess_trans INTO ln_exc_cess_rg23_cr,ln_exc_cess_rg23_dr;
3005 CLOSE cur_get_rg23_cess_trans;
3006
3007 ln_rg23_cess_slno := NULL;
3008 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year,'CVD_EDUCATION_CESS',pv_date);
3009 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
3010 CLOSE cur_get_rg23_cess_slno;
3011
3012 IF ln_rg23_cess_slno IS NULL THEN
3013
3014 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year-1,'CVD_EDUCATION_CESS',pv_date);
3015 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
3016 CLOSE cur_get_rg23_cess_slno;
3017
3018 IF ln_rg23_cess_slno IS NULL THEN
3019
3020 ln_cvd_cess_rg23 := 0;
3021
3022 ELSE
3023
3024 OPEN get_rg23_cess_closing_bal(ln_fin_year-1,ln_rg23_cess_slno,'CVD_EDUCATION_CESS') ;
3025 FETCH get_rg23_cess_closing_bal INTO ln_cvd_cess_rg23;
3026 CLOSE get_rg23_cess_closing_bal;
3027
3028 END IF;
3029
3030 ELSE
3031
3032 OPEN get_rg23_cess_closing_bal(ln_fin_year,ln_rg23_cess_slno,'CVD_EDUCATION_CESS') ;
3033 FETCH get_rg23_cess_closing_bal INTO ln_cvd_cess_rg23;
3034 CLOSE get_rg23_cess_closing_bal;
3035
3036 END IF;
3037 OPEN cur_get_rg23_cess_trans(rg23_log_rec.organization_id,
3038 rg23_log_rec.location_id ,
3039 pv_date ,
3040 'CVD_EDUCATION_CESS');
3041 FETCH cur_get_rg23_cess_trans INTO ln_cvd_cess_rg23_cr,ln_cvd_cess_rg23_dr;
3042 CLOSE cur_get_rg23_cess_trans;
3043
3044
3045 ln_rg23_cess_slno := NULL;
3046 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year,jai_constants.tax_type_sh_exc_edu_cess,pv_date);
3047 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
3048 CLOSE cur_get_rg23_cess_slno;
3049
3050 IF ln_rg23_cess_slno IS NULL THEN
3051
3052 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year-1,jai_constants.tax_type_sh_exc_edu_cess,pv_date);
3053 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
3054 CLOSE cur_get_rg23_cess_slno;
3055
3056 IF ln_rg23_cess_slno IS NULL THEN
3057
3058 ln_sh_exc_cess_rg23 := 0;
3059
3060 ELSE
3061
3062 OPEN get_rg23_cess_closing_bal(ln_fin_year-1,ln_rg23_cess_slno,jai_constants.tax_type_sh_exc_edu_cess) ;
3063 FETCH get_rg23_cess_closing_bal INTO ln_sh_exc_cess_rg23;
3064 CLOSE get_rg23_cess_closing_bal;
3065
3066 END IF;
3067
3068 ELSE
3069
3070 OPEN get_rg23_cess_closing_bal(ln_fin_year,ln_rg23_cess_slno,jai_constants.tax_type_sh_exc_edu_cess) ;
3071 FETCH get_rg23_cess_closing_bal INTO ln_sh_exc_cess_rg23;
3072 CLOSE get_rg23_cess_closing_bal;
3073
3074 END IF;
3075
3076 OPEN cur_get_rg23_cess_trans(rg23_log_rec.organization_id,
3077 rg23_log_rec.location_id ,
3078 pv_date ,
3079 jai_constants.tax_type_sh_exc_edu_cess);
3080 FETCH cur_get_rg23_cess_trans INTO ln_sh_exc_cess_rg23_cr,ln_sh_exc_cess_rg23_dr;
3081 CLOSE cur_get_rg23_cess_trans;
3082
3083 ln_rg23_cess_slno := NULL;
3084 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year,jai_constants.tax_type_sh_cvd_edu_cess,pv_date);
3085 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
3086 CLOSE cur_get_rg23_cess_slno;
3087
3088 IF ln_rg23_cess_slno IS NULL THEN
3089
3090 OPEN cur_get_rg23_cess_slno(rg23_log_rec.organization_id,rg23_log_rec.location_id,ln_fin_year-1,jai_constants.tax_type_sh_cvd_edu_cess,pv_date);
3091 FETCH cur_get_rg23_cess_slno INTO ln_rg23_cess_slno;
3092 CLOSE cur_get_rg23_cess_slno;
3093
3094 IF ln_rg23_cess_slno IS NULL THEN
3095
3096 ln_exc_cess_rg23 := 0;
3097
3098 ELSE
3099
3100 OPEN get_rg23_cess_closing_bal(ln_fin_year-1,ln_rg23_cess_slno,jai_constants.tax_type_sh_cvd_edu_cess) ;
3101 FETCH get_rg23_cess_closing_bal INTO ln_sh_cvd_cess_rg23;
3102 CLOSE get_rg23_cess_closing_bal;
3103
3104 END IF;
3105
3106 ELSE
3107
3108 OPEN get_rg23_cess_closing_bal(ln_fin_year,ln_rg23_cess_slno,jai_constants.tax_type_sh_cvd_edu_cess) ;
3109 FETCH get_rg23_cess_closing_bal INTO ln_sh_cvd_cess_rg23;
3110 CLOSE get_rg23_cess_closing_bal;
3111
3112 END IF;
3113
3114 OPEN cur_get_rg23_cess_trans(rg23_log_rec.organization_id,
3115 rg23_log_rec.location_id ,
3116 pv_date ,
3117 jai_constants.tax_type_sh_cvd_edu_cess);
3118 FETCH cur_get_rg23_cess_trans INTO ln_sh_cvd_cess_rg23_cr,ln_sh_cvd_cess_rg23_dr;
3119 CLOSE cur_get_rg23_cess_trans;
3120
3121
3122 if gn_action IN (1,3) then
3123
3124 open c_rg_slno_bal(rg23_log_rec.organization_id, rg23_log_rec.location_id, rg23_log_rec.register_type) ;
3125 fetch c_rg_slno_bal into ln_slno_bal ;
3126 close c_rg_slno_bal ;
3127
3128 open c_rg23_balance(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type) ;
3129 fetch c_rg23_balance into ln_rg23_balance, ln_rg23_slno ;
3130 close c_rg23_balance ;
3131
3132 open c_rg_balance(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type) ;
3133 fetch c_rg_balance into ln_rg_balance ;
3134 close c_rg_balance ;
3135
3136 OPEN cur_get_final_cess_bal(rg23_log_rec.organization_id,rg23_log_rec.location_id,'EXCISE_EDUCATION_CESS');
3137 FETCH cur_get_final_cess_bal INTO ln_exc_cess_rg23_final;
3138 CLOSE cur_get_final_cess_bal;
3139
3140 OPEN cur_get_final_cess_bal(rg23_log_rec.organization_id,rg23_log_rec.location_id,'CVD_EDUCATION_CESS');
3141 FETCH cur_get_final_cess_bal INTO ln_cvd_cess_rg23_final;
3142 CLOSE cur_get_final_cess_bal;
3143
3144
3145 OPEN cur_get_final_cess_bal(rg23_log_rec.organization_id,rg23_log_rec.location_id,jai_constants.tax_type_sh_exc_edu_cess);
3146 FETCH cur_get_final_cess_bal INTO ln_sh_exc_cess_rg23_final;
3147 CLOSE cur_get_final_cess_bal;
3148
3149 OPEN cur_get_final_cess_bal(rg23_log_rec.organization_id,rg23_log_rec.location_id,jai_constants.tax_type_sh_cvd_edu_cess);
3150 FETCH cur_get_final_cess_bal INTO ln_sh_cvd_cess_rg23_final;
3151 CLOSE cur_get_final_cess_bal;
3152
3153
3154
3155 open c_err_exists(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type) ;
3156 fetch c_err_exists into ln_err_exists ;
3157 close c_err_exists ;
3158
3159 if nvl(ln_err_exists,0) = 0 then
3160 lv_status := 'CONSISTENT' ;
3161 else
3162 lv_status := 'INCONSISTENT' ;
3163 end if ;
3164 ln_err_exists := NULL;
3165
3166 open c_cess_err_exists(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type,'EXCISE_EDUCATION_CESS') ;
3167 fetch c_cess_err_exists into ln_err_exists ;
3168 close c_cess_err_exists ;
3169
3170 if nvl(ln_err_exists,0) = 0 then
3171 lv_exc_cess_status := 'CONSISTENT' ;
3172 else
3173 lv_exc_cess_status := 'INCONSISTENT' ;
3174 end if ;
3175 ln_err_exists := NULL;
3176
3177 open c_cess_err_exists(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type,'CVD_EDUCATION_CESS') ;
3178 fetch c_cess_err_exists into ln_err_exists ;
3179 close c_cess_err_exists ;
3180
3181 if nvl(ln_err_exists,0) = 0 then
3182 lv_cvd_cess_status := 'CONSISTENT' ;
3183 else
3184 lv_cvd_cess_status := 'INCONSISTENT' ;
3185 end if ;
3186
3187 ln_err_exists := NULL;
3188
3189 open c_cess_err_exists(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type,jai_constants.tax_type_sh_exc_edu_cess) ;
3190 fetch c_cess_err_exists into ln_err_exists ;
3191 close c_cess_err_exists ;
3192
3193 if nvl(ln_err_exists,0) = 0 then
3194 lv_sh_exc_cess_status := 'CONSISTENT' ;
3195 else
3196 lv_sh_exc_cess_status := 'INCONSISTENT' ;
3197 end if ;
3198 ln_err_exists := NULL;
3199
3200 open c_cess_err_exists(rg23_log_rec.organization_id,rg23_log_rec.location_id, rg23_log_rec.register_type,jai_constants.tax_type_sh_cvd_edu_cess) ;
3201 fetch c_cess_err_exists into ln_err_exists ;
3202 close c_cess_err_exists ;
3203
3204 if nvl(ln_err_exists,0) = 0 then
3205 lv_sh_cvd_cess_status := 'CONSISTENT' ;
3206 else
3207 lv_sh_cvd_cess_status := 'INCONSISTENT' ;
3208 end if ;
3209
3210 elsif gn_action = 2 then
3211 ln_slno_bal := nvl(ln_open_bal,0) + nvl(ln_credit_amount,0) - nvl(ln_debit_amount,0) ;
3212 ln_rg23_balance := ln_slno_bal ;
3213 ln_rg_balance := ln_slno_bal ;
3214 ln_exc_cess_rg23_final := nvl(ln_exc_cess_rg23,0) + nvl(ln_exc_cess_rg23_cr,0) - nvl(ln_exc_cess_rg23_dr,0);
3215 ln_cvd_cess_rg23_final := nvl(ln_cvd_cess_rg23,0) + nvl(ln_cvd_cess_rg23_cr,0) - nvl(ln_cvd_cess_rg23_dr,0);
3216 lv_status := 'CONSISTENT' ;
3217 lv_exc_cess_status := 'CONSISTENT';
3218 lv_cvd_cess_status := 'CONSISTENT';
3219 lv_sh_exc_cess_status := 'CONSISTENT';
3220 lv_sh_cvd_cess_status := 'CONSISTENT';
3221 end if ;
3222 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(rg23_log_rec.organization_id, 17, ' ')|| '|' || RPAD(rg23_log_rec.location_id, 13, ' ') ||
3223 '|' || RPAD(rg23_log_rec.register_type, 15, ' ') || '|' || 'Excise |'|| RPAD(lv_status, 12, ' ') || '|'|| RPAD(nvl(ln_open_bal,0), 17, ' ') || '|' || RPAD(nvl(ln_credit_amount,0), 14, ' ') || '|'
3224 || RPAD(nvl(ln_debit_amount,0), 14, ' ') || '|' || RPAD(nvl(ln_rg_balance,0), 17, ' ') || '|' || RPAD(nvl(ln_slno_bal,0), 22, ' ') || '|' || RPAD(nvl(ln_rg23_balance,0), 23, ' ') || '|' ) ;
3225 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(rg23_log_rec.organization_id, 17, ' ')|| '|' || RPAD(rg23_log_rec.location_id, 13, ' ') ||
3226 '|' || RPAD(rg23_log_rec.register_type, 15, ' ') || '|' || 'EXCISE_EDUCATION_CESS |'|| RPAD(lv_exc_cess_status, 12, ' ') || '|'||
3227 RPAD(nvl(ln_exc_cess_rg23,0), 17, ' ') || '|' || RPAD(nvl(ln_exc_cess_rg23_cr,0), 14, ' ') || '|' || RPAD(nvl(ln_exc_cess_rg23_dr,0), 14, ' ') ||
3228 '|' || RPAD(nvl(ln_exc_cess_rg23_final,0), 17, ' ') || '|' || ' ' || '|' || ' ' || '|' ) ;
3229 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(rg23_log_rec.organization_id, 17, ' ')|| '|' || RPAD(rg23_log_rec.location_id, 13, ' ') || '|'
3230 || RPAD(rg23_log_rec.register_type, 15, ' ') || '|' || 'CVD_EDUCATION_CESS |'|| RPAD(lv_cvd_cess_status, 12, ' ') || '|'|| RPAD(nvl(ln_cvd_cess_rg23,0), 17, ' ') || '|'
3231 || RPAD(nvl(ln_cvd_cess_rg23_cr,0), 14, ' ') || '|' || RPAD(nvl(ln_cvd_cess_rg23_dr,0), 14, ' ') || '|' || RPAD(nvl(ln_cvd_cess_rg23_final,0), 17, ' ') || '|'
3232 || ' ' || '|' || ' ' || '|' ) ;
3233 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(rg23_log_rec.organization_id, 17, ' ')|| '|'
3234 || RPAD(rg23_log_rec.location_id, 13, ' ') || '|' || RPAD(rg23_log_rec.register_type, 15, ' ') || '|' || 'EXCISE_SH_EDU_CESS |'
3235 || RPAD(lv_sh_exc_cess_status, 12, ' ') || '|'|| RPAD(nvl(ln_sh_exc_cess_rg23,0), 17, ' ') || '|' || RPAD(nvl(ln_sh_exc_cess_rg23_cr,0), 14, ' ') || '|' || RPAD(nvl(ln_sh_exc_cess_rg23_dr,0), 14, ' ') || '|'
3236 || RPAD(nvl(ln_sh_exc_cess_rg23_final,0), 17, ' ') || '|' || ' ' || '|' || ' ' || '|' ) ;
3237 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(rg23_log_rec.organization_id, 17, ' ')|| '|' || RPAD(rg23_log_rec.location_id, 13, ' ') || '|' || RPAD(rg23_log_rec.register_type, 15, ' ')
3238 || '|' || 'CVD_SH_EDU_CESS |'|| RPAD(lv_sh_cvd_cess_status, 12, ' ') || '|'|| RPAD(nvl(ln_sh_cvd_cess_rg23,0), 17, ' ') || '|' || RPAD(nvl(ln_sh_cvd_cess_rg23_cr,0), 14, ' ') || '|'
3239 || RPAD(nvl(ln_sh_cvd_cess_rg23_dr,0), 14, ' ') || '|' || RPAD(nvl(ln_sh_cvd_cess_rg23_final,0), 17, ' ') || '|' || ' ' || '|'
3240 || ' ' || '|' ) ;
3241 FND_FILE.PUT_LINE(FND_FILE.LOG, '----------------------------------------------------------------------
3242 -----------------------------------------------------------------------------------------
3243 -------------------------------------------------' ) ;
3244 end loop ;
3245
3246 end if ;
3247
3248 if (p_register_type is null ) or (p_register_type = 'PLA')
3249 then
3250
3251 if nvl(ln_header,0) = 0 then
3252 FND_FILE.PUT_LINE(FND_FILE.LOG, '| Organization Id |' || ' Location Id |' || ' Register Type |' || ' Tax Type |'|| ' Data State |'|| ' Opening Balance |'
3253 || 'Credit Amount |' || ' Debit Amount |' || ' Closing Bal(RG) |' || ' Closing Bal(RG Slno) |' || ' Closing Bal(Register) |' ) ;
3254 FND_FILE.PUT_LINE(FND_FILE.LOG, '| --------------- |' || ' ----------- |' || ' ------------- |' || ' --------------------|'|| ' ------ |'|| ' --------------- |'
3255 || '------------- |' || ' ------------ |' || ' --------------- |' || ' -------------------- |' || ' --------------------- |' ) ;
3256 end if ;
3257
3258 for pla_log_rec in ( select
3259 distinct organization_id, location_id
3260 from JAI_CMN_RG_PLA_TRXS
3261 where
3262 organization_id = nvl(p_organization_id, organization_id) and
3263 location_id = nvl(p_location_id , location_id) and
3264 trunc(creation_date) >= pv_date
3265 order by organization_id, location_id )
3266 loop
3267
3268 ln_open_bal := null ;
3269 ln_credit_amount := null ;
3270 ln_debit_amount := null ;
3271 ln_slno_bal := null ;
3272 ln_pla_balance := null ;
3273 ln_rg_balance := null ;
3274 lv_status := null ;
3275 ln_err_exists := null ;
3276 ln_pla_slno := null ;
3277
3278 OPEN cur_get_fin_year( pla_log_rec.organization_id,pv_date ) ;
3279 FETCH cur_get_fin_year INTO ln_fin_year;
3280 CLOSE cur_get_fin_year;
3281
3282 open c_get_pla_open_bal(pla_log_rec.organization_id, pla_log_rec.location_id, pv_date) ;
3283 fetch c_get_pla_open_bal into ln_open_bal ;
3284 close c_get_pla_open_bal ;
3285
3286 open c_get_pla_tran_amt(pla_log_rec.organization_id, pla_log_rec.location_id, pv_date) ;
3287 fetch c_get_pla_tran_amt into ln_credit_amount, ln_debit_amount ;
3288 close c_get_pla_tran_amt ;
3289
3290 ln_pla_cess_slno := NULL;
3291 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year,'EXCISE_EDUCATION_CESS',pv_date);
3292 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3293 CLOSE cur_get_pla_cess_slno;
3294
3295 IF ln_pla_cess_slno IS NULL THEN
3296
3297 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year-1,'EXCISE_EDUCATION_CESS',pv_date);
3298 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3299 CLOSE cur_get_pla_cess_slno;
3300
3301 IF ln_pla_cess_slno IS NULL THEN
3302
3303 ln_exc_cess_pla := 0;
3304
3305 ELSE
3306
3307 OPEN get_pla_cess_closing_bal(ln_fin_year-1,ln_pla_cess_slno,'EXCISE_EDUCATION_CESS') ;
3308 FETCH get_pla_cess_closing_bal INTO ln_exc_cess_pla;
3309 CLOSE get_pla_cess_closing_bal;
3310
3311 END IF;
3312
3313 ELSE
3314
3315 OPEN get_pla_cess_closing_bal(ln_fin_year,ln_pla_cess_slno,'EXCISE_EDUCATION_CESS') ;
3316 FETCH get_pla_cess_closing_bal INTO ln_exc_cess_pla;
3317 CLOSE get_pla_cess_closing_bal;
3318
3319 END IF;
3320
3321 OPEN cur_get_pla_cess_trans(pla_log_rec.organization_id,
3322 pla_log_rec.location_id ,
3323 pv_date ,
3324 'EXCISE_EDUCATION_CESS');
3325 FETCH cur_get_pla_cess_trans INTO ln_exc_cess_pla_cr,ln_exc_cess_pla_dr;
3326 CLOSE cur_get_pla_cess_trans;
3327
3328 ln_pla_cess_slno := NULL;
3329 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year,'CVD_EDUCATION_CESS',pv_date);
3330 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3331 CLOSE cur_get_pla_cess_slno;
3332
3333 IF ln_pla_cess_slno IS NULL THEN
3334
3335 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year-1,'CVD_EDUCATION_CESS',pv_date);
3336 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3337 CLOSE cur_get_pla_cess_slno;
3338
3339 IF ln_pla_cess_slno IS NULL THEN
3340
3341 ln_cvd_cess_pla := 0;
3342
3343 ELSE
3344
3345 OPEN get_pla_cess_closing_bal(ln_fin_year-1,ln_pla_cess_slno,'CVD_EDUCATION_CESS') ;
3346 FETCH get_pla_cess_closing_bal INTO ln_cvd_cess_pla;
3347 CLOSE get_pla_cess_closing_bal;
3348
3349 END IF;
3350
3351 ELSE
3352
3353 OPEN get_pla_cess_closing_bal(ln_fin_year,ln_pla_cess_slno,'CVD_EDUCATION_CESS') ;
3354 FETCH get_pla_cess_closing_bal INTO ln_cvd_cess_pla;
3355 CLOSE get_pla_cess_closing_bal;
3356
3357 END IF;
3358
3359 OPEN cur_get_pla_cess_trans(pla_log_rec.organization_id,
3360 pla_log_rec.location_id ,
3361 pv_date ,
3362 'CVD_EDUCATION_CESS');
3363 FETCH cur_get_pla_cess_trans INTO ln_cvd_cess_pla_cr,ln_cvd_cess_pla_dr;
3364 CLOSE cur_get_pla_cess_trans;
3365
3366
3367
3368 ln_pla_cess_slno := NULL;
3369 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year,jai_constants.tax_type_sh_exc_edu_cess,pv_date);
3370 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3371 CLOSE cur_get_pla_cess_slno;
3372
3373 IF ln_pla_cess_slno IS NULL THEN
3374
3375 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year-1,jai_constants.tax_type_sh_exc_edu_cess,pv_date);
3376 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3377 CLOSE cur_get_pla_cess_slno;
3378
3379 IF ln_pla_cess_slno IS NULL THEN
3380
3381 ln_sh_exc_cess_pla := 0;
3382
3383 ELSE
3384
3385 OPEN get_pla_cess_closing_bal(ln_fin_year-1,ln_pla_cess_slno,jai_constants.tax_type_sh_exc_edu_cess) ;
3386 FETCH get_pla_cess_closing_bal INTO ln_sh_exc_cess_pla;
3387 CLOSE get_pla_cess_closing_bal;
3388
3389 END IF;
3390
3391 ELSE
3392
3393 OPEN get_pla_cess_closing_bal(ln_fin_year,ln_pla_cess_slno,jai_constants.tax_type_sh_exc_edu_cess) ;
3394 FETCH get_pla_cess_closing_bal INTO ln_sh_exc_cess_pla;
3395 CLOSE get_pla_cess_closing_bal;
3396
3397 END IF;
3398
3399 OPEN cur_get_pla_cess_trans(pla_log_rec.organization_id,
3400 pla_log_rec.location_id ,
3401 pv_date ,
3402 jai_constants.tax_type_sh_exc_edu_cess);
3403 FETCH cur_get_pla_cess_trans INTO ln_sh_exc_cess_pla_cr,ln_sh_exc_cess_pla_dr;
3404 CLOSE cur_get_pla_cess_trans;
3405
3406 ln_pla_cess_slno := NULL;
3407 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year,jai_constants.tax_type_sh_cvd_edu_cess,pv_date);
3408 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3409 CLOSE cur_get_pla_cess_slno;
3410
3411 IF ln_pla_cess_slno IS NULL THEN
3412
3413 OPEN cur_get_pla_cess_slno(pla_log_rec.organization_id,pla_log_rec.location_id,ln_fin_year-1,jai_constants.tax_type_sh_cvd_edu_cess,pv_date);
3414 FETCH cur_get_pla_cess_slno INTO ln_pla_cess_slno;
3415 CLOSE cur_get_pla_cess_slno;
3416
3417 IF ln_pla_cess_slno IS NULL THEN
3418
3419 ln_sh_cvd_cess_pla := 0;
3420
3421 ELSE
3422
3423 OPEN get_pla_cess_closing_bal(ln_fin_year-1,ln_pla_cess_slno,jai_constants.tax_type_sh_cvd_edu_cess) ;
3424 FETCH get_pla_cess_closing_bal INTO ln_sh_cvd_cess_pla;
3425 CLOSE get_pla_cess_closing_bal;
3426
3427 END IF;
3428
3429 ELSE
3430
3431 OPEN get_pla_cess_closing_bal(ln_fin_year,ln_pla_cess_slno,jai_constants.tax_type_sh_cvd_edu_cess) ;
3432 FETCH get_pla_cess_closing_bal INTO ln_sh_cvd_cess_pla;
3433 CLOSE get_pla_cess_closing_bal;
3434
3435 END IF;
3436
3437 OPEN cur_get_pla_cess_trans(pla_log_rec.organization_id,
3438 pla_log_rec.location_id ,
3439 pv_date ,
3440 jai_constants.tax_type_sh_cvd_edu_cess);
3441 FETCH cur_get_pla_cess_trans INTO ln_sh_cvd_cess_pla_cr,ln_sh_cvd_cess_pla_dr;
3442 CLOSE cur_get_pla_cess_trans;
3443
3444
3445
3446 if gn_action IN (1,3) then
3447 open c_rg_slno_bal(pla_log_rec.organization_id, pla_log_rec.location_id, 'PLA') ;
3448 fetch c_rg_slno_bal into ln_slno_bal ;
3449 close c_rg_slno_bal ;
3450
3451 open c_pla_balance(pla_log_rec.organization_id,pla_log_rec.location_id) ;
3452 fetch c_pla_balance into ln_pla_balance, ln_pla_slno ;
3453 close c_pla_balance ;
3454
3455 open c_rg_balance(pla_log_rec.organization_id,pla_log_rec.location_id, 'PLA') ;
3456 fetch c_rg_balance into ln_rg_balance ;
3457 close c_rg_balance ;
3458
3459 OPEN cur_get_final_cess_bal(pla_log_rec.organization_id,pla_log_rec.location_id,'EXCISE_EDUCATION_CESS');
3460 FETCH cur_get_final_cess_bal INTO ln_exc_cess_pla_final;
3461 CLOSE cur_get_final_cess_bal;
3462
3463 OPEN cur_get_final_cess_bal(pla_log_rec.organization_id,pla_log_rec.location_id,'CVD_EDUCATION_CESS');
3464 FETCH cur_get_final_cess_bal INTO ln_cvd_cess_pla_final;
3465 CLOSE cur_get_final_cess_bal;
3466
3467
3468
3469 OPEN cur_get_final_cess_bal(pla_log_rec.organization_id,pla_log_rec.location_id,jai_constants.tax_type_sh_exc_edu_cess);
3470 FETCH cur_get_final_cess_bal INTO ln_sh_exc_cess_pla_final;
3471 CLOSE cur_get_final_cess_bal;
3472
3473 OPEN cur_get_final_cess_bal(pla_log_rec.organization_id,pla_log_rec.location_id,jai_constants.tax_type_sh_cvd_edu_cess);
3474 FETCH cur_get_final_cess_bal INTO ln_sh_cvd_cess_pla_final;
3475 CLOSE cur_get_final_cess_bal;
3476
3477
3478
3479 open c_err_exists(pla_log_rec.organization_id,pla_log_rec.location_id, 'PLA') ;
3480 fetch c_err_exists into ln_err_exists ;
3481 close c_err_exists ;
3482
3483 if nvl(ln_err_exists,0) = 0 then
3484 lv_status := 'CONSISTENT' ;
3485 else
3486 lv_status := 'INCONSISTENT' ;
3487 end if ;
3488 ln_err_exists := NULL;
3489
3490 open c_cess_err_exists(pla_log_rec.organization_id,pla_log_rec.location_id, 'PLA','EXCISE_EDUCATION_CESS') ;
3491 fetch c_cess_err_exists into ln_err_exists ;
3492 close c_cess_err_exists ;
3493
3494 if nvl(ln_err_exists,0) = 0 then
3495 lv_exc_cess_status := 'CONSISTENT' ;
3496 else
3497 lv_exc_cess_status := 'INCONSISTENT' ;
3498 end if ;
3499 ln_err_exists := NULL;
3500
3501 open c_cess_err_exists(pla_log_rec.organization_id,pla_log_rec.location_id, 'PLA','CVD_EDUCATION_CESS') ;
3502 fetch c_cess_err_exists into ln_err_exists ;
3503 close c_cess_err_exists ;
3504
3505 if nvl(ln_err_exists,0) = 0 then
3506 lv_cvd_cess_status := 'CONSISTENT' ;
3507 else
3508 lv_cvd_cess_status := 'INCONSISTENT' ;
3509 end if ;
3510
3511
3512
3513 ln_err_exists := NULL;
3514
3515 open c_cess_err_exists(pla_log_rec.organization_id,pla_log_rec.location_id, 'PLA',jai_constants.tax_type_sh_exc_edu_cess) ;
3516 fetch c_cess_err_exists into ln_err_exists ;
3517 close c_cess_err_exists ;
3518
3519 if nvl(ln_err_exists,0) = 0 then
3520 lv_sh_exc_cess_status := 'CONSISTENT' ;
3521 else
3522 lv_sh_exc_cess_status := 'INCONSISTENT' ;
3523 end if ;
3524 ln_err_exists := NULL;
3525
3526 open c_cess_err_exists(pla_log_rec.organization_id,pla_log_rec.location_id, 'PLA',jai_constants.tax_type_sh_cvd_edu_cess) ;
3527 fetch c_cess_err_exists into ln_err_exists ;
3528 close c_cess_err_exists ;
3529
3530 if nvl(ln_err_exists,0) = 0 then
3531 lv_sh_cvd_cess_status := 'CONSISTENT' ;
3532 else
3533 lv_sh_cvd_cess_status := 'INCONSISTENT' ;
3534 end if ;
3535
3536
3537
3538 elsif gn_action = 2 then
3539 ln_slno_bal := nvl(ln_open_bal,0) + nvl(ln_credit_amount,0) - nvl(ln_debit_amount,0) ;
3540 ln_exc_cess_pla_final := nvl(ln_exc_cess_pla,0) + nvl(ln_exc_cess_pla_cr,0) - nvl(ln_exc_cess_pla_dr,0);
3541 ln_cvd_cess_pla_final := nvl(ln_cvd_cess_pla,0) + nvl(ln_cvd_cess_pla_cr,0) - nvl(ln_cvd_cess_pla_dr,0);
3542 ln_pla_balance := ln_slno_bal ;
3543 ln_rg_balance := ln_slno_bal ;
3544 lv_status := 'CONSISTENT' ;
3545 lv_exc_cess_status := 'CONSISTENT';
3546 lv_cvd_cess_status := 'CONSISTENT';
3547 lv_sh_exc_cess_status := 'CONSISTENT';
3548 lv_sh_cvd_cess_status := 'CONSISTENT';
3549
3550 end if ;
3551
3552
3553 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(pla_log_rec.organization_id, 17, ' ')|| '|' || RPAD(pla_log_rec.location_id, 13, ' ') || '|' || RPAD('PLA', 15, ' ') || '|' || 'Excise |'
3554 || RPAD(lv_status, 12, ' ') || '|' || RPAD(nvl(ln_open_bal,0), 17, ' ') || '|' || RPAD(nvl(ln_credit_amount,0), 14, ' ') || '|' || RPAD(nvl(ln_debit_amount,0), 14, ' ') || '|' || RPAD(nvl(ln_rg_balance,0), 17, ' ') || '|'
3555 || RPAD(nvl(ln_slno_bal,0), 22, ' ') || '|' || RPAD(nvl(ln_pla_balance,0) , 23, ' ') || '|' ) ;
3556 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(pla_log_rec.organization_id, 17, ' ')|| '|' || RPAD(pla_log_rec.location_id, 13, ' ') || '|' || RPAD('PLA', 15, ' ') || '|' || 'EXCISE_EDUCATION_CESS |'
3557 || RPAD(lv_exc_cess_status, 12, ' ') || '|'|| RPAD(nvl(ln_exc_cess_pla,0), 17, ' ') || '|' || RPAD(nvl(ln_exc_cess_pla_cr,0), 14, ' ') || '|' || RPAD(nvl(ln_exc_cess_pla_dr,0), 14, ' ') || '|'
3558 || RPAD(nvl(ln_exc_cess_pla_final,0), 17, ' ') || '|' || ' ' || '|' || ' ' || '|' ) ;
3559 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(pla_log_rec.organization_id, 17, ' ')
3560 || '|' || RPAD(pla_log_rec.location_id, 13, ' ') || '|' || RPAD('PLA', 15, ' ')
3561 || '|' || 'CVD_EDUCATION_CESS |'|| RPAD(lv_cvd_cess_status, 12, ' ') || '|'
3562 || RPAD(nvl(ln_cvd_cess_pla,0), 17, ' ') || '|'
3563 || RPAD(nvl(ln_cvd_cess_pla_cr,0), 14, ' ') ||
3564 '|' || RPAD(nvl(ln_cvd_cess_pla_dr,0), 14, ' ') || '|' || RPAD(nvl(ln_cvd_cess_pla_final,0), 17, ' ') || '|' || ' ' || '|' || ' ' || '|' ) ;
3565 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(pla_log_rec.organization_id, 17, ' ')|| '|'
3566 || RPAD(pla_log_rec.location_id, 13, ' ') || '|' || RPAD('PLA', 15, ' ') || '|' || 'EXCISE_SH_EDU_CESS |'|| RPAD(lv_sh_exc_cess_status, 12, ' ') || '|'
3567 || RPAD(nvl(ln_sh_exc_cess_pla,0), 17, ' ') || '|' || RPAD(nvl(ln_sh_exc_cess_pla_cr,0), 14, ' ') || '|' || RPAD(nvl(ln_sh_exc_cess_pla_dr,0), 14, ' ') ||
3568 '|' || RPAD(nvl(ln_sh_exc_cess_pla_final,0), 17, ' ') || '|' || ' ' || '|' || ' ' || '|' ) ;
3569 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(pla_log_rec.organization_id, 17, ' ')|| '|'
3570 || RPAD(pla_log_rec.location_id, 13, ' ') || '|' || RPAD('PLA', 15, ' ') || '|' || 'CVD_SH_EDU_CESS |'|| RPAD(lv_sh_cvd_cess_status, 12, ' ') || '|'|| RPAD(nvl(ln_sh_cvd_cess_pla,0), 17, ' ') || '|'
3571 || RPAD(nvl(ln_sh_cvd_cess_pla_cr,0), 14, ' ') || '|' || RPAD(nvl(ln_sh_cvd_cess_pla_dr,0), 14, ' ') || '|' ||
3572 RPAD(nvl(ln_sh_cvd_cess_pla_final,0), 17, ' ') || '|' || ' ' || '|' || ' ' || '|' ) ;
3573 FND_FILE.PUT_LINE(FND_FILE.LOG, '------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------' ) ;
3574
3575 end loop ;
3576
3577 end if ;
3578
3579 if NVL(p_debug, 'N') = 'Y'
3580 then
3581
3582 FND_FILE.PUT_LINE(FND_FILE.LOG, '') ;
3583 FND_FILE.PUT_LINE(FND_FILE.LOG, '') ;
3584
3585 FND_FILE.PUT_LINE(FND_FILE.LOG, '| Organization Id |' || ' Location Id |' || ' Register Type |' || ' Fin Year |' || 'Error |' || ' Slno |' || ' Register Id |'
3586 || ' RowCount |' || ' Tax Type |' || ' Date |' || ' Month ' || ' Year ' ) ;
3587 FND_FILE.PUT_LINE(FND_FILE.LOG, '| --------------- |' || ' ----------- |' || ' ------------- |' || ' -------- |' || '----- |' || ' ------ |' || ' ----------- |'
3588 || ' -------- |' || ' -------- |' || ' -------- |' || ' ----- ' || ' ---- ' ) ;
3589
3590
3591 for rec in ( select * from JAI_TRX_GT)
3592 loop
3593
3594 FND_FILE.PUT_LINE(FND_FILE.LOG, '|' || RPAD(nvl(to_char(rec.JAI_INFO_N1),' '), 17, ' ')|| '|' || RPAD(nvl(to_char(rec.JAI_INFO_N2),' '), 13, ' ') || '|'
3595 || RPAD(nvl(rec.JAI_INFO_V1,' '), 15, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N3),' '), 10, ' ') || '|' || RPAD(nvl(rec.JAI_INFO_V3,' '), 6, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N8),' '), 8, ' ')
3596 || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N9),' '), 13, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N10),' '), 10, ' ') || '|'
3597 || RPAD(nvl(rec.JAI_INFO_V4,' '), 23, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_D1),' '), 10, ' ') || '|' || RPAD(nvl(rec.JAI_INFO_V5,' '), 7, ' ') || '|'
3598 || RPAD(nvl(to_char(rec.JAI_INFO_N11),' '), 6, ' ') ) ;
3599
3600 end loop ;
3601
3602 FND_FILE.PUT_LINE(FND_FILE.LOG, '') ;
3603 FND_FILE.PUT_LINE(FND_FILE.LOG, '') ;
3604
3605 FND_FILE.PUT_LINE(FND_FILE.LOG, ' Error Code Information ' ) ;
3606 FND_FILE.PUT_LINE(FND_FILE.LOG, ' ---------------------- ' ) ;
3607
3608
3609 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E01 : Total transaction amount does not equal the Period balance for the MON and Year ' ) ;
3610
3611 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E02 : RG23 Opening Balance is not equal to closing balance of previous record ' ) ;
3612
3613 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E03 : RG23 Closing Balance is not equal to opening balance + Transaction Amount ' ) ;
3614
3615 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E04 : PLA Opening Balance is not equal to closing balance of previous record ' ) ;
3616
3617 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E05 : PLA Closing Balance is not equal to opening balance + Transaction Amount ' ) ;
3618
3619 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E06 : Final balance in JAI_RG_OTH_BALANCE is not equal to Closing Balance of the last record ' ) ;
3620
3621 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E07 : PLA Duplicate Slno ' ) ;
3622
3623 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E08 : JAI_CMN_RG_OTHERS Closing Balance is not equal to Opening Balance + Transaction Amount ' ) ;
3624
3625 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E09 : RG23 Other Tax Credit/Debit is not equal to the sum of credit/debit in JAI_CMN_RG_OTHERS ' ) ;
3626
3627 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E10 : PLA Other Tax Credit/Debit is not equal to the sum of credit/debit in JAI_CMN_RG_OTHERS ' ) ;
3628
3629 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E11 : RG23 Balance, Slno do not match with JAI_CMN_RG_SLNOS Balance,Slno ' ) ;
3630
3631 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E12 : PLA Balance, Slno do not match with JAI_CMN_RG_SLNOS Balance,Slno ' ) ;
3632
3633 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E13 : RG23 Last record Balance does not match with the balance in JAI_CMN_RG_BALANCES ' ) ;
3634
3635 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E14 : PLA Last record Balance does not match with the balance in JAI_CMN_RG_BALANCES ' ) ;
3636
3637 if gn_action = 1 then
3638 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E15 : PLA Total Transaction amount does not match with the balance in JAI_CMN_RG_BALANCES ' ) ;
3639 elsif gn_action = 3 then
3640 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E15 : PLA Total Transaction amount does not match with the balance in JAI_CMN_RG_BALANCES. Manual Intervention is required. ' ) ;
3641 end if ;
3642
3643 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E16 : The Consolidation Amount for rg23 and pla do not match ' ) ;
3644
3645 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E17 : The Other Tax Consolidation Amount for rg23 and pla do not match ' ) ;
3646
3647 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E18 : RG23 Duplicate Slno ' ) ;
3648
3649 FND_FILE.PUT_LINE(FND_FILE.LOG, 'E19 : JAI_CMN_RG_OTHERS Opening Balance is not equal to the closing balance of the previous record ' ) ;
3650
3651 end if ;
3652
3653 exception
3654 when others then
3655 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Error : ' || SQLERRM ) ;
3656 retcode := 2 ;
3657
3658 END process_rg_trx;
3659 -----------------------------------------PROCESS_RG_TRX--------------------------------
3660
3661 END jai_excise_scripts_pkg ;