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.
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 .
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;
