[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT *
FROM JAI_CMN_RG_PERIOD_BALS
WHERE organization_id = cp_organization_id
AND location_id = cp_location_id
AND register_type = cp_register_type
AND (start_date, end_date) =
(
SELECT max(start_date), max(end_date)
FROM JAI_CMN_RG_PERIOD_BALS
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) + 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)) total_modvat_amount,
min(fin_year)
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) between cp_start_date and cp_end_date
and period_balance_id is null
and inventory_item_id <> 0;
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 = cp_organization_id
AND a.location_id = cp_location_id
AND a.register_type = cp_register_type
AND trunc(a.creation_date) between cp_start_date and cp_end_date
and a.period_balance_id is null
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 fin_year
FROM JAI_CMN_FIN_YEARS
WHERE organization_id = cp_organization_id
AND cp_period_start_date between fin_year_start_date and fin_year_end_date;
SELECT min(creation_date)
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 period_balance_id is null
AND trunc(creation_date) <= cp_consolidate_till;/*bug 5241875*/
select JAI_CMN_RG_PERIOD_BALS_S.nextval
from dual;
ln_no_balances_updated number;
FOR org IN (select organization_id, location_id
from JAI_CMN_INVENTORY_ORGS
where location_id > 0
order by organization_id, location_id)
LOOP
r_last_record := NULL;
insert into JAI_CMN_RG_PERIOD_BALS
(
period_balance_id,
organization_id,
location_id,
register_type,
start_date,
end_date,
fin_year,
opening_balance,
closing_balance,
misc_adjustment,
rounding_adjustment,
cumulative_misc_adjustment,
cumulative_rounding_adjustment,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login
/*following columns added by bgowrava for forward porting bug#5674376 */
, exc_edu_cess_cl_bal
, exc_edu_cess_adj
, exc_edu_cess_adj_op_bal
, cvd_edu_cess_cl_bal
, cvd_edu_cess_adj
, cvd_edu_cess_adj_op_bal
/*Bug 5989740 bduvarag*/
, sh_exc_edu_cess_cl_bal
, sh_exc_edu_cess_adj
, sh_exc_edu_cess_adj_op_bal
, sh_cvd_edu_cess_cl_bal
, sh_cvd_edu_cess_adj
, sh_cvd_edu_cess_adj_op_bal
)
values
(
ln_period_balance_id,
org.organization_id,
org.location_id,
lv_register_type,
ld_start_date,
ld_end_date,
ln_fin_year,
ln_period_opening_bal,
ln_period_closing_bal,
0,
0,
nvl(r_last_record.cumulative_misc_adjustment, 0)+ nvl(r_last_record.misc_adjustment, 0),
nvl(r_last_record.cumulative_rounding_adjustment, 0)+nvl(r_last_record.rounding_adjustment, 0),
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
fnd_global.login_id
/*following columns added by bgowrava for forward porting bug#5674376 */
, ln_exc_edu_cess_cl_bal
, 0
, nvl(r_last_record.exc_edu_cess_adj_op_bal,0) + nvl(r_last_record.exc_edu_cess_adj,0)
, ln_cvd_edu_cess_cl_bal
, 0
, nvl(r_last_record.cvd_edu_cess_adj_op_bal,0) + nvl(r_last_record.cvd_edu_cess_adj,0)
/*Bug 5989740 bduvarag*/
, ln_sh_exc_edu_cess_cl_bal
, 0
, nvl(r_last_record.sh_exc_edu_cess_adj_op_bal,0) + nvl(r_last_record.sh_exc_edu_cess_adj,0)
, ln_sh_cvd_edu_cess_cl_bal
, 0
, nvl(r_last_record.sh_cvd_edu_cess_adj_op_bal,0) + nvl(r_last_record.sh_cvd_edu_cess_adj,0)
);
update JAI_CMN_RG_23AC_II_TRXS
set period_balance_id = ln_period_balance_id
WHERE organization_id = org.organization_id
AND location_id = org.location_id
AND register_type = p_register_type
AND trunc(creation_date) between ld_start_date and ld_end_date
and period_balance_id is null
and inventory_item_id <> 0;
select register_id
from JAI_CMN_RG_23AC_II_TRXS
where inventory_item_id = 0
and period_balance_id is null
and register_type = p_register_type /* added by bgowrava for forward porting bug#5674376*/
--and trunc(creation_date) <= ld_consolidate_till /*commented by bgowrava for forward porting bug#5674376 */
)
loop
/* Call the rounding adjustment proc for each of the rounding */
ln_period_balance_id := null;
ln_no_balances_updated := null;
p_no_balances_updated => ln_no_balances_updated
);
p_no_balances_updated OUT NOCOPY number
)
is
/* Added by Ramananda for bug#4407165 */
lv_object_name CONSTANT VARCHAR2(61) := 'jai_cmn_rg_period_bals_pkg.adjust_rounding';
select period_balance_id
from JAI_CMN_RG_23AC_II_TRXS
where register_id = cp_register_id;
select (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)) rounding_amount
from JAI_CMN_RG_23AC_II_TRXS
where register_id = cp_register_id;
select organization_id, location_id, register_type, end_date
from JAI_CMN_RG_PERIOD_BALS
where period_balance_id = cp_starting_period_balance_id;
select nvl(credit,0) - nvl(debit,0)
from JAI_CMN_RG_OTHERS
where source_register_id = cp_register_id
and source_type = 1
and tax_type = cp_tax_type;
/* update the parent_balance record */
update JAI_CMN_RG_PERIOD_BALS
set rounding_adjustment = nvl(rounding_adjustment, 0) + ln_round_amount
, exc_edu_cess_adj = nvl(exc_edu_cess_adj, 0) + ln_exc_edu_cess_adj -- bgowrava for forward porting bug#5674376
, cvd_edu_cess_adj = nvl(cvd_edu_cess_adj, 0) + ln_cvd_edu_cess_adj -- bgowrava for forward porting bug#5674376
, sh_exc_edu_cess_adj = nvl(sh_exc_edu_cess_adj, 0) + ln_sh_exc_edu_cess_adj /*Bug 5989740 bduvarag*/
, sh_cvd_edu_cess_adj = nvl(sh_cvd_edu_cess_adj, 0) + ln_sh_cvd_edu_cess_adj /*Bug 5989740 bduvarag*/
where period_balance_id = ln_parent_period_balance_id;
update JAI_CMN_RG_23AC_II_TRXS
set period_balance_id = ln_parent_period_balance_id
where register_id = p_register_id_rounding;
/* update all subsequent balance records */
open c_get_start_balance_detail(ln_parent_period_balance_id);
update JAI_CMN_RG_PERIOD_BALS
set cumulative_rounding_adjustment =
nvl(cumulative_rounding_adjustment, 0) + ln_round_amount
-- bgowrava for forward porting bug#5674376
, exc_edu_cess_adj_op_bal = nvl(exc_edu_cess_adj_op_bal, 0) + ln_exc_edu_cess_adj
, cvd_edu_cess_adj_op_bal = nvl(cvd_edu_cess_adj_op_bal, 0) + ln_cvd_edu_cess_adj
, sh_exc_edu_cess_adj_op_bal = nvl(sh_exc_edu_cess_adj_op_bal, 0) + ln_sh_exc_edu_cess_adj/*Bug 5989740 bduvarag*/
, sh_cvd_edu_cess_adj_op_bal = nvl(sh_cvd_edu_cess_adj_op_bal, 0) + ln_sh_cvd_edu_cess_adj/*Bug 5989740 bduvarag*/
where organization_id = r_get_start_balance_detail.organization_id
and location_id = r_get_start_balance_detail.location_id
and register_type = r_get_start_balance_detail.register_type
and start_date > r_get_start_balance_detail.end_date;
p_no_balances_updated := null;
select *
from JAI_CMN_RG_PERIOD_BALS
where organization_id = cp_organization_id
and location_id = cp_location_id
and register_type = cp_register_type
and end_date = cp_period_start_date-1;