PLA balance not matching with India PLA register report

PLA balance not matching with India PLA register report

The following India localization balances not matching with India PLA Register

India Localization > Registers > View Register Balance Form > PLA Balance

India Localization > Registers >PLA View Transactions Form > PLA Balance

India Localization > Registers > RG Consolidation Screen Form > PLA balance

Solution 1.

Step 1. Due to the lower version of the JAINPLA file.

1. Download and review the readme and pre-requisites for Patch.'19132611' 

2. Ensure that you have taken a backup of your system before applying 
the recommended patch. 

3. Apply the patch in a TEST environment. 

4. Confirm the following file versions: 
JAINPLA.rdf 120.6.12010000.7 
You can use the commands like the following: 
strings -a $XX_TOP/filename |grep '$Header' 

5. Retest the issue by creating fresh transaction. 

6. Migrate the solution as appropriate to other environments. 

Step 2. Issue in Invalid and Incorrect data

The cause of the issue is invalid / incorrect data in below tables 
JAI_CMN_RG_23AC_II_TRXS , 
JAI_CMN_RG_PLA_TRXS , 
JAI_CMN_RG_OTHERS , 
JAI_CMN_RG_OTH_BALANCES , 
JAI_CMN_RG_PERIOD_BALS . 

RG View PLA Balances And India - PLA Register Report Are Not Matching. ( Doc ID 1534016.1 ) 

Solution 2

Data fix Script Bug : 16196925

REM +======================================================================+

REM | Copyright (c) 2011 Oracle Corporation Redwood Shores, California, USA|

REM |                       All rights reserved.                           |

REM +======================================================================+

REM NAME

REM b16196925.sql

REM DESCRIPTION

REM

REM NOTES

REM

REM +======================================================================+

REM dbdrv: phase=compile

SET VERIFY OFF

WHENEVER SQLERROR EXIT FAILURE ROLLBACK;

WHENEVER OSERROR  EXIT FAILURE ROLLBACK;

Declare

CURSOR GET_MISMATCH_RECORDS

IS

select TRX.SLNO ,hdr.pla_ID

from JAI_CMN_RG_PLA_TRXS trx ,

     JAI_CMN_RG_PLA_HDRS hdr

where trx.organization_id=hdr.organization_id

and trx.location_id=hdr.location_id

and trx.slno<>hdr.pla_rg_sr_no

and trx.tr6_challan_no=hdr.tr6_number

and hdr.ack_recvd_flag='Y'

and hdr.pla_amount is not null

and trx.organization_id=&p_organization_id

and trx.location_id=&p_location_id;

Cursor get_miss_entries

is

select trx.register_id,trx.creation_date, hdr.pla_id

from JAI_CMN_RG_PLA_TRXS trx ,

     JAI_CMN_RG_PLA_HDRS hdr

where trx.organization_id=hdr.organization_id

and trx.location_id=hdr.location_id

and trx.tr6_challan_no=hdr.tr6_number

and hdr.ack_recvd_flag='Y'

and hdr.pla_amount is not null

and trx.organization_id=&p_organization_id

and trx.location_id=&p_location_id

and not exists (select 1

                  from JAI_CMN_RG_OTHERS 

                                                                          where source_type=2

                                                                            and source_register_id=trx.register_id)             ;

Cursor get_cess_amts(cp_pla_id in number)

is

select sum(nvl(edu_cess_excise_amount, 0)),

       sum(nvl(sh_cess_excise_amount, 0))

  from JAI_CMN_RG_PLA_DTLS

 where pla_id = cp_pla_id   ;

ln_cess_amt number;

ln_sh_cess_amt number;

Begin

FOR MISMATCH_REC IN GET_MISMATCH_RECORDS

LOOP

 UPDATE JAI_CMN_RG_PLA_HDRS

   SET pla_rg_sr_no = MISMATCH_REC.SLNO,

       LAST_UPDATED_BY = -16196925

  WHERE PLA_ID = MISMATCH_REC.PLA_ID;

COMMIT;

END loop;

for miss_rec in get_miss_entries

Loop

 ln_cess_amt := null;

 ln_sh_cess_amt := null;

   open  get_cess_amts(miss_rec.pla_id);

   fetch get_cess_amts into ln_cess_amt,ln_sh_cess_amt;

   close get_cess_amts;

if NVL(ln_cess_amt,0) <> 0

then 

 

 Insert into JAI_CMN_RG_OTHERS

    (

    rg_other_id              ,          

    source_type              ,           

    source_register          ,            

    source_register_id       ,

    tax_type                 , 

    credit                   ,

    debit                    ,

    created_by               ,

    creation_date            ,

    last_updated_by          ,

    last_update_date         ,

    last_update_login      

    )

    Values

    (

    JAI_CMN_RG_OTHERS_s.nextval     ,

    2                           ,

    'PLA'                       ,

    miss_rec.register_id        ,

    'EXCISE_EDUCATION_CESS'     ,

    ln_cess_amt                 ,

    NULL                        ,

    fnd_global.user_id          ,

    miss_rec.creation_date      ,

    '-16196925'                 ,

    sysdate                     ,

    fnd_global.login_id

    );

end if;

if NVL(ln_sh_cess_amt,0) <> 0

then

      

 Insert into JAI_CMN_RG_OTHERS

    (

    rg_other_id              ,          

    source_type              ,           

    source_register          ,            

    source_register_id       ,

    tax_type                 , 

    credit                   ,

    debit                    ,

    created_by               ,

    creation_date            ,

    last_updated_by          ,

    last_update_date         ,

    last_update_login      

    )

    Values

    (

    JAI_CMN_RG_OTHERS_s.nextval      ,

    2                            ,

    'PLA'                        ,

    miss_rec.register_id         ,

    'EXCISE_SH_EDU_CESS'         ,

    ln_sh_cess_amt               ,

    NULL                         ,

    fnd_global.user_id           ,

    miss_rec.creation_date       ,

    '-16196925'                  ,

    sysdate                      ,

    fnd_global.login_id

    );

end if;

end loop;

COMMIT;

 

END;

/

COMMIT;

EXIT;