The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT max(slno)
FROM JAI_CMN_RG_23AC_II_TRXS jrg
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year
AND slno < p_slno
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type = p_tax_type
AND source_register_id = jrg.register_id );
SELECT max(slno)
FROM JAI_CMN_RG_23AC_II_TRXS jrg
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year - 1
AND exists ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type = p_tax_type
AND source_register_id = jrg.register_id );
SELECT closing_balance
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type = p_tax_type
AND source_register_id = ( SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year
AND slno = cp_slno) ;
SELECT max(slno)
FROM JAI_CMN_RG_PLA_TRXS jpl
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND slno < p_slno
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND tax_type = p_tax_type
AND source_register_id = jpl.register_id );
SELECT max(slno)
FROM JAI_CMN_RG_PLA_TRXS jpl
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year - 1
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND tax_type = p_tax_type
AND source_register_id = jpl.register_id );
SELECT closing_balance
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND tax_type = p_tax_type
AND source_register_id = ( SELECT register_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND slno = cp_slno );
SELECT max(slno)
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year
AND slno < p_slno ;
SELECT max(slno)
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year - 1;
SELECT closing_balance
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year
AND slno = cp_slno ;
SELECT max(slno)
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND slno < p_slno ;
SELECT max(slno)
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year - 1;
SELECT closing_balance
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND slno = cp_slno ;
UPDATE JAI_CMN_RG_23AC_II_TRXS
SET slno = slno + p_dup_cnt - 1
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year
AND slno > p_slno;
UPDATE JAI_CMN_RG_SLNOS
SET slno = slno + p_dup_cnt - 1
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND current_fin_year = p_fin_year;
FOR dup_rec in ( SELECT *
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = p_fin_year
AND slno = p_slno
ORDER BY register_id ) LOOP
UPDATE JAI_CMN_RG_23AC_II_TRXS
SET slno = slno + ln_cnt
WHERE register_id = dup_rec.register_id;
UPDATE JAI_CMN_RG_PLA_TRXS
SET slno = slno + p_dup_cnt - 1
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND slno > p_slno;
UPDATE JAI_CMN_RG_SLNOS
SET slno = slno + p_dup_cnt - 1
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND current_fin_year = p_fin_year;
FOR dup_rec in ( SELECT *
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = p_fin_year
AND slno = p_slno
ORDER BY register_id ) LOOP
UPDATE JAI_CMN_RG_PLA_TRXS
SET slno = slno + ln_cnt
WHERE register_id = dup_rec.register_id;
DELETE JAI_CMN_RG_PERIOD_BALS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
AND start_date >= p_start_date;
UPDATE JAI_CMN_RG_23AC_II_TRXS
SET period_balance_id = NULL
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND trunc(creation_date) >= p_start_date;
SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
- nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND trunc(creation_date) between cp_start_date and cp_end_date
AND inventory_item_id <> 0;
SELECT closing_balance - opening_balance
FROM JAI_CMN_RG_PERIOD_BALS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
AND start_date = cp_start_date
AND end_date = cp_end_date;
SELECT sum(nvl(b.credit,0) - nvl(b.debit,0)) total_cess
FROM JAI_CMN_RG_23AC_II_TRXS a, JAI_CMN_RG_OTHERS b
WHERE a.organization_id = p_organization_id
AND a.location_id = p_location_id
AND a.register_type = p_register_type
AND trunc(a.creation_date) between cp_start_date and cp_end_date
AND a.inventory_item_id <> 0
AND b.source_register_id = a.register_id
AND b.source_type = 1
AND b.tax_type = cp_tax_type;
SELECT exc_edu_cess_cl_bal,
cvd_edu_cess_cl_bal,
sh_exc_edu_cess_cl_bal,
sh_cvd_edu_cess_cl_bal
FROM JAI_CMN_RG_PERIOD_BALS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
AND start_date = cp_start_date
AND end_date = cp_end_date;
p_last_updated_by NUMBER
)
IS
CURSOR cur_rg23_next_records IS
SELECT jrg.opening_balance ,
jrg.closing_balance ,
nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
jrg.last_updated_by,
jrg.last_update_date,
jrg.rg_other_id,
rg23.slno,
rg23.organization_id,
rg23.location_id
FROM JAI_CMN_RG_OTHERS jrg,
JAI_CMN_RG_23AC_II_TRXS rg23
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND ((fin_year = p_fin_year
AND slno >= p_slno)
OR fin_year > p_fin_year )
AND register_type = p_register_type
AND source_type = 1
AND source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
AND tax_type = p_tax_type
AND source_register_id = rg23.register_id
ORDER BY fin_year,slno
FOR UPDATE OF jrg.opening_balance,
jrg.closing_balance,
jrg.last_updated_by,
jrg.last_update_date;
SELECT jrg.opening_balance ,
jrg.closing_balance ,
nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
jrg.last_updated_by,
jrg.last_update_date,
jrg.rg_other_id,
jpl.slno,
jpl.organization_id,
jpl.location_id
FROM JAI_CMN_RG_OTHERS jrg,
JAI_CMN_RG_PLA_TRXS jpl
WHERE source_type = 2
and tax_type = p_tax_type
and source_register_id = jpl.register_id
and organization_id = p_organization_id
AND location_id = p_location_id
AND ((fin_year = p_fin_year
AND slno >= p_slno)
OR fin_year > p_fin_year )
ORDER BY fin_year,slno
FOR UPDATE OF jrg.opening_balance,
jrg.closing_balance,
jrg.last_updated_by,
jrg.last_update_date;
UPDATE JAI_CMN_RG_OTHERS
SET opening_balance = ln_opening_balance,
closing_balance = ln_closing_balance,
last_updated_by = p_last_updated_by,
last_update_date = sysdate
WHERE CURRENT OF cur_rg23_next_records;
UPDATE JAI_CMN_RG_OTH_BALANCES
SET balance = ln_prev_balance,
last_updated_by = p_last_updated_by,
last_update_date = sysdate
WHERE org_unit_id = ( SELECT org_unit_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id )
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C')
AND tax_type = p_tax_type;
UPDATE JAI_CMN_RG_OTHERS
SET opening_balance = ln_opening_balance,
closing_balance = ln_closing_balance,
last_updated_by = p_last_updated_by,
last_update_date = sysdate
WHERE CURRENT OF cur_pla_next_records;
UPDATE JAI_CMN_RG_OTH_BALANCES
SET balance = ln_prev_balance,
last_updated_by = p_last_updated_by,
last_update_date = sysdate
WHERE org_unit_id = ( SELECT org_unit_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id )
AND register_type = 'PLA'
AND tax_type = p_tax_type;
UPDATE JAI_CMN_RG_BALANCES
SET rg23a_balance = p_closing_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND rg23a_balance <> p_closing_balance;
UPDATE JAI_CMN_RG_BALANCES
SET rg23c_balance = p_closing_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND rg23a_balance <> p_closing_balance;
UPDATE JAI_CMN_RG_BALANCES
SET pla_balance = p_closing_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND rg23a_balance <> p_closing_balance;
UPDATE JAI_CMN_RG_SLNOS
SET balance = p_closing_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND balance <> p_closing_balance ;
UPDATE JAI_CMN_RG_OTH_BALANCES
SET balance = p_closing_balance
WHERE org_unit_id = ( SELECT org_unit_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id )
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
AND tax_type = p_tax_type
AND balance <> p_closing_balance;
UPDATE JAI_CMN_RG_SLNOS
SET slno = p_slno
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND slno <> p_slno ;
SELECT jrg.opening_balance ,
jrg.closing_balance ,
nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
jrg.last_updated_by,
jrg.last_update_date,
jrg.rg_other_id,
rg23.slno,
rg23.organization_id,
rg23.location_id
FROM JAI_CMN_RG_OTHERS jrg,
JAI_CMN_RG_23AC_II_TRXS rg23
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND register_type = p_register_type
AND slno > nvl(cp_slno,1)
AND source_type = 1
AND source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
AND tax_type = cp_tax_type
AND source_register_id = rg23.register_id
ORDER BY slno;
SELECT jrg.opening_balance ,
jrg.closing_balance ,
nvl(jrg.credit,0) - nvl(jrg.debit,0) transaction_amount,
jrg.last_updated_by,
jrg.last_update_date,
jrg.rg_other_id,
jpl.slno,
jpl.organization_id,
jpl.location_id
FROM JAI_CMN_RG_OTHERS jrg,
JAI_CMN_RG_PLA_TRXS jpl
WHERE source_type = 2
and tax_type = cp_tax_type
and source_register_id = jpl.register_id
and organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND slno > nvl(cp_slno,1)
ORDER BY slno;
SELECT fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = p_organization_id
AND fin_active_flag = 'Y';
SELECT fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = p_organization_id
AND p_date between fin_year_start_date and fin_year_end_date;
SELECT max(slno),min(slno)
FROM JAI_CMN_RG_23AC_II_TRXS jrg
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND register_type = p_register_type
AND trunc(creation_date) < cp_date
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND source_register_id = jrg.register_id
AND tax_type = cp_tax_type );
SELECT 1
FROM JAI_CMN_RG_23AC_II_TRXS jrg
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND register_type = p_register_type
AND trunc(creation_date) < cp_date
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND source_register_id = jrg.register_id
AND tax_type = cp_tax_type );
SELECT max(slno),min(slno)
FROM JAI_CMN_RG_PLA_TRXS jpl
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND trunc(creation_date) < cp_date
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND source_register_id = jpl.register_id
AND tax_type = cp_tax_type );
SELECT 1
FROM JAI_CMN_RG_PLA_TRXS jpl
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND trunc(creation_date) < cp_date
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND source_register_id = jpl.register_id
AND tax_type = cp_tax_type );
SELECT closing_balance
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type = cp_tax_type
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = cp_fin_year
AND slno = nvl(cp_slno,1) );
SELECT closing_balance
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND tax_type = cp_tax_type
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND slno = nvl(cp_slno,1) );
SELECT balance
FROM JAI_CMN_RG_OTH_BALANCES
WHERE org_unit_id = ( SELECT org_unit_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = p_organization_id
AND location_id = p_location_id )
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
AND tax_type = cp_tax_type;
p_last_updated_by => -5451134
);
p_last_updated_by => -5451134
);
p_last_updated_by => -5451134
);
p_last_updated_by => -5451134
);
p_last_updated_by NUMBER )
IS
CURSOR cur_next_pla_records IS
SELECT opening_balance,
closing_balance,
nvl(cr_basic_ed,0) + nvl(cr_additional_ed,0) + nvl(cr_other_ed,0) -
nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0) transaction_amount,
register_id,
slno
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND ((fin_year = p_fin_year
AND slno >= p_slno ) OR
( fin_year > p_fin_year ))
ORDER BY FIN_YEAR,SLNO
FOR UPDATE ;
SELECT 1
FROM all_tab_cols
WHERE table_name = 'JAI_CMN_RG_23AC_II_TRXS'
AND column_name IN ( 'DR_ADDITIONAL_CVD', 'CR_ADDITIONAL_CVD')
AND owner = l_apps_short_name ; /*added by ssawant*/
lv_cursor_str := 'SELECT '||
lv_trans_str||' ,
register_id,
slno
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id ='|| p_organization_id||'
AND location_id ='|| p_location_id||'
AND register_type ='''|| p_register_type||'''
AND ((fin_year ='|| p_fin_year||'
AND slno >='|| p_slno||' ) OR
( fin_year >'|| p_fin_year||' ))
ORDER BY fin_year,slno
FOR UPDATE ';
UPDATE JAI_CMN_RG_23AC_II_TRXS
SET opening_balance = ln_opening_balance,
closing_balance = ln_closing_balance,
last_updated_by = p_last_updated_by,
last_update_date = sysdate
WHERE register_id = ln_register_id;
UPDATE JAI_CMN_RG_BALANCES
SET rg23a_balance = ln_prev_balance,
last_updated_by = p_last_updated_by
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
UPDATE JAI_CMN_RG_SLNOS
SET balance = ln_prev_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = 'A' ;
UPDATE JAI_CMN_RG_BALANCES
SET rg23c_balance = ln_prev_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
UPDATE JAI_CMN_RG_SLNOS
SET balance = ln_prev_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = 'C' ;
UPDATE JAI_CMN_RG_PLA_TRXS
SET opening_balance = ln_opening_balance,
closing_balance = ln_closing_balance,
last_updated_by = p_last_updated_by,
last_update_date = sysdate
WHERE CURRENT OF cur_next_pla_records;
UPDATE JAI_CMN_RG_BALANCES
SET pla_balance = ln_prev_balance,
last_updated_by = p_last_updated_by
WHERE organization_id = p_organization_id
AND location_id = p_location_id ;
UPDATE JAI_CMN_RG_SLNOS
SET balance = ln_prev_balance
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = 'PLA' ;
UPDATE JAI_CMN_RG_23AC_II_TRXS
SET other_tax_credit = ( SELECT sum(credit)
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND source_register_id = p_register_id ),
other_tax_debit = ( SELECT sum(debit)
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND source_register_id = p_register_id )
WHERE register_id = p_register_id;
UPDATE JAI_CMN_RG_PLA_TRXS
SET other_tax_credit = ( SELECT sum(credit)
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND source_register_id = p_register_id ),
other_tax_debit = ( SELECT sum(debit)
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND source_register_id = p_register_id )
WHERE register_id = p_register_id;
select slno, count(*) rowcount
from
JAI_CMN_RG_PLA_TRXS
where
organization_id = p_organization_id and
location_id = p_location_id and
fin_year = p_fin_year and
trunc(creation_date) >= gd_date
group by slno
having count(*) > 1 ;
select slno
from
JAI_CMN_RG_PLA_TRXS
where
closing_balance <> nvl(opening_balance,0) +
( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) ) -
( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) ) AND
organization_id = p_organization_id AND
location_id = p_location_id AND
fin_year = p_fin_year AND
trunc(creation_date) >= gd_date
ORDER BY
register_id ;
select
rowid ,
slno ,
organization_id ,
location_id ,
register_id ,
fin_year ,
opening_balance ,
closing_balance ,
nvl(other_tax_credit,other_tax_debit) rg_other_amt ,
transaction_source_num
FROM
JAI_CMN_RG_PLA_TRXS
WHERE
organization_id = p_organization_id AND
location_id = p_location_id AND
fin_year = p_fin_year AND
trunc(creation_date) >= gd_date
ORDER BY
slno ;
select
nvl(closing_balance,0)
from
JAI_CMN_RG_PLA_TRXS
where
organization_id = cp_organization_id AND
location_id = cp_location_id AND
fin_year = cp_fin_year AND
slno =
( select max(slno)
from
JAI_CMN_RG_PLA_TRXS
where
organization_id = cp_organization_id and
location_id = cp_location_id and
fin_year = cp_fin_year and
slno < cp_slno
);
select nvl(closing_balance,0)
from JAI_CMN_RG_PLA_TRXS
where organization_id = cp_organization_id
and location_id = cp_location_id
and fin_year = cp_fin_year
and slno in
( select nvl(max(slno),0)
from JAI_CMN_RG_PLA_TRXS
where organization_id = cp_organization_id
and location_id = cp_location_id
and fin_year = cp_fin_year
);
select nvl(sum(credit), sum(debit))
from JAI_CMN_RG_OTHERS
where source_register = 'PLA'
and source_register_id = cp_source_register_id
and source_type = 2 ;
p_last_updated_by => -5451134 );
p_last_updated_by => -5451134 );
select slno , count(*) rowcount
from
JAI_CMN_RG_23AC_II_TRXS
where
organization_id = p_organization_id and
location_id = p_location_id and
fin_year = p_fin_year and
register_type = p_register_type and
trunc(creation_date) >= gd_date
group by slno
having count(*) > 1 ;
select slno
from
JAI_CMN_RG_23AC_II_TRXS
where
closing_balance <> nvl(opening_balance,0) +
( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) ) -
( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) ) AND
register_type = p_register_type AND
organization_id = p_organization_id AND
location_id = p_location_id AND
fin_year = p_fin_year AND
trunc(creation_date) >= gd_date
ORDER BY
register_id ;
select
rowid ,
slno ,
organization_id ,
location_id ,
register_id ,
register_type ,
fin_year ,
opening_balance ,
closing_balance ,
nvl(other_tax_credit,other_tax_debit) rg_other_amt
FROM
JAI_CMN_RG_23AC_II_TRXS
WHERE
organization_id = p_organization_id AND
location_id = p_location_id AND
fin_year = p_fin_year AND
register_type = p_register_type AND
trunc(creation_date) >= gd_date
ORDER BY
organization_id ,
location_id ,
fin_year ,
register_type ,
slno ;
select
nvl(closing_balance,0)
from
JAI_CMN_RG_23AC_II_TRXS
where
organization_id = cp_organization_id AND
location_id = cp_location_id AND
fin_year = cp_fin_year AND
register_type = cp_register_type AND
slno =
( select max(slno)
from
JAI_CMN_RG_23AC_II_TRXS
where
organization_id = cp_organization_id and
location_id = cp_location_id and
fin_year = cp_fin_year and
register_type = cp_register_type and
slno < cp_slno ) ;
select nvl(closing_balance,0)
from JAI_CMN_RG_23AC_II_TRXS
where organization_id = cp_organization_id
and location_id = cp_location_id
and register_type = cp_register_type
and fin_year = cp_fin_year
and slno in
( select nvl(max(slno),0)
from JAI_CMN_RG_23AC_II_TRXS
where organization_id = cp_organization_id
and location_id = cp_location_id
and fin_year = cp_fin_year
and register_type = cp_register_type);
select nvl(sum(credit), sum(debit))
from JAI_CMN_RG_OTHERS
where source_register = decode(cp_register_type,'A','RG23A_P2','C','RG23C_P2')
and source_register_id = cp_source_register_id
and source_type = 1 ;
' select slno
from
JAI_CMN_RG_23AC_II_TRXS
where
closing_balance <> nvl(opening_balance,0) +
( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0)) -
( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) + nvl(dr_additional_cvd,0)) AND
register_type = ''' || p_register_type || ''' AND
organization_id = ' || p_organization_id || ' AND
location_id = ' || p_location_id || ' AND
fin_year = ' || p_fin_year || ' AND
trunc(creation_date) >= to_date(''' || gd_date || ''',''dd-mon-rrrr'')
and rownum =1 ORDER BY register_id '
into ln_register_id ;
' select count(1)
from
JAI_CMN_RG_23AC_II_TRXS
where
closing_balance <> nvl(opening_balance,0) +
( nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) ) -
( nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) ) AND
register_type = ''' || p_register_type || ''' AND
organization_id = ' || p_organization_id || ' AND
location_id = ' || p_location_id || ' AND
fin_year = ' || p_fin_year || ' AND
trunc(creation_date) >= to_date(''' || gd_date || ''',''dd-mon-rrrr'')
and rownum =1 ORDER BY register_id '
into ln_rowcount ;
p_last_updated_by => -5451134 );
p_last_updated_by => -5451134 );
insert into JAI_TRX_GT
( JAI_INFO_N1 ,
JAI_INFO_N2 ,
JAI_INFO_V1 ,
JAI_INFO_N3 ,
JAI_INFO_N4 ,
JAI_INFO_V3 ,
JAI_INFO_N8 ,
JAI_INFO_N9 ,
JAI_INFO_N10 ,
JAI_INFO_V4 ,
JAI_INFO_D1 ,
JAI_INFO_V5 ,
JAI_INFO_N11
)
values
( p_organization_id ,
p_location_id ,
p_register_type ,
p_fin_year ,
p_opening_balance ,
p_error_codes ,
p_slno ,
p_register_id ,
p_rowcount ,
p_tax_type ,
p_date ,
p_month ,
p_year
) ;
select closing_balance , slno
from JAI_CMN_RG_23AC_II_TRXS
where organization_id = cp_organization_id
and location_id = cp_location_id
and register_type = cp_register_type
order by fin_year desc , slno desc ;
select decode(cp_register_type, 'A', rg23a_balance, 'C', rg23c_balance, 'PLA', pla_balance)
from JAI_CMN_RG_BALANCES
where organization_id = cp_organization_id
and location_id = cp_location_id ;
select closing_balance, slno
from JAI_CMN_RG_PLA_TRXS
where organization_id = cp_organization_id
and location_id = cp_location_id
order by fin_year desc , slno desc ;
SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
- nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id;
select balance , slno
from JAI_CMN_RG_SLNOS
where organization_id = cp_organization_id
and location_id = cp_location_id
and register_type = cp_register_type;
select
sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) pla_cons_amt ,
sum(other_tax_credit) pla_oth_amt
from JAI_CMN_RG_PLA_TRXS
where
transaction_source_num is null and
organization_id = cp_organization_id and
location_id = cp_location_id and
trunc(creation_date) = cp_creation_date ;
select 1
from all_tab_cols
where
table_name = 'JAI_CMN_RG_23AC_II_TRXS'
and column_name IN ( 'DR_ADDITIONAL_CVD', 'CR_ADDITIONAL_CVD')
AND owner = l_apps_short_name ; /*added by ssawant*/
select
nvl(opening_balance,0)
from
JAI_CMN_RG_23AC_II_TRXS
where
organization_id = cp_organization_id AND
location_id = cp_location_id AND
register_type = cp_register_type AND
trunc(creation_date) >= cp_date
order by fin_year, slno ;
select
sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) credit_amount ,
sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0)) debit_amount
from
JAI_CMN_RG_23AC_II_TRXS
where
organization_id = cp_organization_id AND
location_id = cp_location_id AND
register_type = cp_register_type AND
trunc(creation_date) >= cp_date ;
select balance
from JAI_CMN_RG_SLNOS
where
organization_id = cp_organization_id AND
location_id = cp_location_id AND
register_type = cp_register_type ;
select count(1)
from JAI_TRX_GT
where
JAI_INFO_N1 = cp_organization_id and
JAI_INFO_N2 = cp_location_id and
JAI_INFO_V1 = cp_register_type ;
select count(1)
from JAI_TRX_GT
where
JAI_INFO_N1 = cp_organization_id AND
JAI_INFO_N2 = cp_location_id AND
JAI_INFO_V1 = cp_register_type AND
JAI_INFO_V4 = cp_tax_type;
SELECT sum(nvl(cr_basic_ed,0)+ nvl(cr_additional_ed,0) + nvl(cr_other_ed,0)
- nvl(dr_basic_ed,0) - nvl(dr_additional_ed,0) - nvl(dr_other_ed,0)) total_modvat_amount
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id
and trunc(creation_date) < cp_date ;
select
sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0)) credit_amount ,
sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0)) debit_amount
from
JAI_CMN_RG_PLA_TRXS
where
organization_id = cp_organization_id AND
location_id = cp_location_id AND
trunc(creation_date) >= cp_date ;
SELECT fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = cp_organization_id
AND cp_date between fin_year_start_date and fin_year_end_date;
SELECT max(slno)
FROM JAI_CMN_RG_23AC_II_TRXS jrg
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND register_type = p_register_type
AND trunc(creation_date) < cp_date
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND source_register_id = jrg.register_id
AND tax_type = cp_tax_type );
SELECT sum(credit),sum(debit)
FROM JAI_CMN_RG_OTHERS
WHERE source_register = decode(p_register_type,'A','RG23A_P2','C','RG23C_P2')
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id
AND register_type = p_register_type
AND trunc(creation_date) >= cp_date)
AND tax_type = cp_tax_type;
SELECT sum(credit),sum(debit)
FROM JAI_CMN_RG_OTHERS
WHERE source_register = 'PLA'
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id
AND trunc(creation_date) >= cp_date)
AND tax_type = cp_tax_type;
SELECT max(slno)
FROM JAI_CMN_RG_PLA_TRXS jpl
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND trunc(creation_date) < cp_date
AND EXISTS ( SELECT 1
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND source_register_id = jpl.register_id
AND tax_type = cp_tax_type );
SELECT closing_balance
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 1
AND tax_type = cp_tax_type
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_23AC_II_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND register_type = p_register_type
AND fin_year = cp_fin_year
AND slno = nvl(cp_slno,1) );
SELECT closing_balance
FROM JAI_CMN_RG_OTHERS
WHERE source_type = 2
AND tax_type = cp_tax_type
AND source_register_id in ( SELECT register_id
FROM JAI_CMN_RG_PLA_TRXS
WHERE organization_id = p_organization_id
AND location_id = p_location_id
AND fin_year = cp_fin_year
AND slno = nvl(cp_slno,1) );
SELECT balance
FROM JAI_CMN_RG_OTH_BALANCES
WHERE org_unit_id = ( SELECT org_unit_id
FROM JAI_CMN_INVENTORY_ORGS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id )
AND register_type = decode(p_register_type,'A','RG23A','C','RG23C',p_register_type)
AND tax_type = cp_tax_type;
for rec in ( select
distinct organization_id, location_id, fin_year, register_type
from JAI_CMN_RG_23AC_II_TRXS
where
trunc(creation_date) >= pv_date and
register_type = nvl(p_register_type, register_type) and
location_id = nvl(p_location_id , location_id) and
organization_id = nvl(p_organization_id, organization_id)
order by register_type, organization_id, location_id, fin_year )
loop
-- call to validate records in JAI_CMN_RG_23AC_II_TRXS
rg23_part_ii_validation( p_organization_id => rec.organization_id ,
p_location_id => rec.location_id ,
p_fin_year => rec.fin_year ,
p_register_type => rec.register_type
) ;
for rg23_bal_rec in ( select
distinct organization_id, location_id, register_type
from JAI_CMN_RG_23AC_II_TRXS
where
register_type = nvl(p_register_type, register_type) and
organization_id = nvl(p_organization_id, organization_id) and
location_id = nvl(p_location_id , location_id) and
trunc(creation_date) >= pv_date
order by register_type, organization_id, location_id )
loop
validate_period_balances( p_organization_id => rg23_bal_rec.organization_id,
p_location_id => rg23_bal_rec.location_id,
p_register_type => rg23_bal_rec.register_type,
p_date => pv_date
) ;
for rec in ( select
distinct organization_id, location_id, fin_year
from JAI_CMN_RG_PLA_TRXS
where
trunc(creation_date) >= pv_date and
location_id = nvl(p_location_id , location_id) and
organization_id = nvl(p_organization_id, organization_id)
order by organization_id, location_id, fin_year )
loop
pla_validation( p_organization_id => rec.organization_id ,
p_location_id => rec.location_id ,
p_fin_year => rec.fin_year
) ;
for pla_bal_rec in ( select
distinct organization_id, location_id
from JAI_CMN_RG_PLA_TRXS
where
organization_id = nvl(p_organization_id, organization_id) and
location_id = nvl(p_location_id , location_id) and
trunc(creation_date) >= pv_date
order by organization_id, location_id
)
loop
-- call to validate pla cess info
validate_rg_others
( p_organization_id => pla_bal_rec.organization_id,
p_location_id => pla_bal_rec.location_id,
p_register_type => 'PLA',
p_date => pv_date
) ;
For cons_rec in ( select
distinct organization_id, location_id
from JAI_CMN_RG_23AC_II_TRXS
where
organization_id = nvl(p_organization_id, organization_id) and
location_id = nvl(p_location_id , location_id) and
trunc(creation_date) >= pv_date
order by organization_id, location_id
)
loop
For rg23_cons_rec in ( select
sum( nvl(dr_basic_ed,0) +nvl(dr_additional_ed,0) +nvl(dr_other_ed,0) ) rg23_cons_amt ,
sum(other_tax_debit) rg23_oth_amt,
trunc(creation_date) cons_date
from JAI_CMN_RG_23AC_II_TRXS
where
transaction_source_num is null and
organization_id = cons_rec.organization_id and
location_id = cons_rec.location_id and
trunc(creation_date) >= pv_date
group by trunc(creation_date)
)
loop
open pla_cons_amt(cons_rec.organization_id,cons_rec.location_id , rg23_cons_rec.cons_date) ;
for rg23_log_rec in ( select
distinct organization_id, location_id, register_type
from JAI_CMN_RG_23AC_II_TRXS
where
register_type = nvl(p_register_type, register_type) and
organization_id = nvl(p_organization_id, organization_id) and
location_id = nvl(p_location_id , location_id) and
trunc(creation_date) >= pv_date
order by organization_id, location_id, register_type )
loop
ln_open_bal := null ;
' select
sum(nvl(cr_basic_ed,0)+nvl(cr_additional_ed,0)+nvl(cr_other_ed,0) + nvl(cr_additional_cvd,0)) credit_amount ,
sum(nvl(dr_basic_ed,0)+nvl(dr_additional_ed,0)+nvl(dr_other_ed,0) + nvl(dr_additional_cvd,0)) debit_amount
from
JAI_CMN_RG_23AC_II_TRXS
where
organization_id = ' || rg23_log_rec.organization_id || ' AND
location_id = ' || rg23_log_rec.location_id || ' AND
register_type = ''' || rg23_log_rec.register_type || ''' AND
trunc(creation_date) >= ''' || pv_date || ''''
into ln_credit_amount, ln_debit_amount ;
for pla_log_rec in ( select
distinct organization_id, location_id
from JAI_CMN_RG_PLA_TRXS
where
organization_id = nvl(p_organization_id, organization_id) and
location_id = nvl(p_location_id , location_id) and
trunc(creation_date) >= pv_date
order by organization_id, location_id )
loop
ln_open_bal := null ;
for rec in ( select * from JAI_TRX_GT)
loop
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, ' ') || '|'
|| 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, ' ')
|| '|' || RPAD(nvl(to_char(rec.JAI_INFO_N9),' '), 13, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_N10),' '), 10, ' ') || '|'
|| RPAD(nvl(rec.JAI_INFO_V4,' '), 23, ' ') || '|' || RPAD(nvl(to_char(rec.JAI_INFO_D1),' '), 10, ' ') || '|' || RPAD(nvl(rec.JAI_INFO_V5,' '), 7, ' ') || '|'
|| RPAD(nvl(to_char(rec.JAI_INFO_N11),' '), 6, ' ') ) ;