ENGINATICSSYSTEM_ADMINISTRATORSYSADMIN1772B3EAC0C36715E0630100007FF9DEselect
null action_,
null status_,
null message_,
null modified_columns_,
null row_id,
null request_id_,
null request_output,
to_number(null) group_id,
glv.name ledger_name,
gbe.name budget_organization,
gbve.budget_name,
xxen_util.meaning(gbv.require_budget_journals_flag ,'YES_NO',0) budget_requires_journal,
null journal_source_name,
null journal_category_name,
gbauv.currency_code,
gbpr.period_year fiscal_year,
null add_or_replace,
gcck.concatenated_segments account,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=1 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period1_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=1 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=1 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period1_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=2 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period2_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=2 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=2 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period2_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=3 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period3_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=3 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=3 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period3_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=4 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period4_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=4 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=4 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period4_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=5 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period5_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=5 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=5 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period5_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=6 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period6_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=6 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=6 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period6_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=7 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period7_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=7 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=7 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period7_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=8 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period8_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=8 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=8 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period8_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=9 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period9_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=9 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=9 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period9_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=10 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period10_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=10 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=10 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period10_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=11 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period11_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=11 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=11 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period11_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=12 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period12_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=12 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=12 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period12_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=13 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period13_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=13 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=13 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period13_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=14 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period14_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=14 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=14 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period14_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=15 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period15_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=15 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=15 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period15_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=16 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period16_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=16 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=16 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period16_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=17 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period17_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=17 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=17 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period17_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=18 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period18_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=18 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=18 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period18_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=19 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period19_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=19 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=19 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period19_amount,
(select
gps.period_name
from
gl_period_statuses gps
where
gps.ledger_id=glv.ledger_id and
gps.period_year=gbpr.period_year and
gps.application_id=101 and
gps.period_num=20 and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num
) period20_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=20 and
gbp.period_year=gbpr.period_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=20 and
gb.period_year=gbpr.period_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period20_amount,
to_number(null) upload_row
from
gl_budget_assignments_unique_v gbauv,
gl_budgets_v gbv,
gl_budget_entities gbe,
gl_entity_budgets geb,
gl_budget_versions gbve,
gl_ledgers_v glv,
gl_budget_period_ranges gbpr,
gl_code_combinations_kfv gcck
where
1=1 and
gbauv.entry_code='E' and
gbauv.code_combination_id=gcck.code_combination_id and
gbauv.ledger_id=glv.ledger_id and
glv.chart_of_accounts_id=gcck.chart_of_accounts_id and
gbe.budget_entity_id=gbauv.budget_entity_id and
gbv.budget_name=gbve.budget_name and
gbv.budget_name=gbve.budget_name and
glv.ledger_id=gbe.ledger_id and
geb.budget_version_id=gbve.budget_version_id and
gbe.budget_entity_id=geb.budget_entity_id and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.open_flag='O' and
gcck.enabled_flag='Y' and
gcck.summary_flag='N' and
gcck.detail_budgeting_allowed='Y' and
gcck.template_id is null and
sysdate between nvl(gcck.start_date_active,sysdate) and nvl(gcck.end_date_active,(sysdate+1))Upload Row column added to keep the uploaded row order in the result.YU:p_upload_mode=xxen_upload.action_create or
(
:budget_organization is not null and
:budget is not null and
:currency is not null
)Axxen_gl_upload.populate_budget_interfacexxen_gl_upload.submit_budget_importxxen_gl_upload.validate_budget_entry(
p_user_je_source_name=>:$column$.journal_source_name,
p_user_je_category_name=>:$column$.journal_category_name,
p_budget_name=>:$column$.budget_name
)USGL Budget Amounts UploadGL Budget Amounts Upload
===================
Loads budget amounts into a budget organization of a GL budget. Each row holds one account and fiscal year with up to 20 period amounts. A blank period amount leaves the period unchanged.
For a budget that does not require budget journals, the rows go to the budget interface and the Upload Budget Amounts program runs after the upload. Add Or Replace decides whether an entered amount is added to the period balance or replaces it.
For a budget that requires budget journals, each entered period amount becomes a budget journal line of the row's Journal Source and Journal Category, imported through Journal Import and funds reserved. Such amounts are always added to the balance: the Create, Update download shows the period balances, so a downloaded amount uploaded again unchanged is added a second time. Enter only the change.Please enter Budget Organization, Budget and Currency criteria for downloadEnginaticsUpload1=11101=1glv.name=:ledgerLOVGL Ledger8E2FF36EDEB879D2E0530100007F1FF2select
gl.name value,
fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
(:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and
gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code='GL#' and
fifsv.application_id=101
order by
fifsv.id_flex_structure_name,
decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4),
gl.namecoalesce(xxen_util.previous_parameter_value(:parameter_id),xxen_util.default_ledger)YUSLedger2201=1:p_upload_mode like '%'||xxen_upload.action_updateLOVBlitz Upload ModeE811F942597A2075E0530100007FF4F2select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all
select xxen_upload.action_create||','||xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dualCYUSUpload Mode3301=1gbe.name=:budget_organizationLOV customselect
gbe.name value,
gbe.description
from
gl_budget_entities gbe,
gl_ledgers_v glv
where
gbe.ledger_id=glv.ledger_id and
glv.name=:$flex$.ledgerUSBudget Organization4401=1gbv.budget_name=:budgetLOV customselect
gbv.budget_name value,
gbv.description
from
gl_entity_budgets geb,
gl_budget_versions gbv,
gl_budget_entities gbe,
gl_ledgers_v glv
where
geb.budget_version_id=gbv.budget_version_id and
geb.budget_entity_id=gbe.budget_entity_id and
gbe.name=:$flex$.budget_organization and
gbe.ledger_id=glv.ledger_id and
glv.name=:$flex$.ledger and
gbv.status='O'USBudget5501=1gbauv.currency_code=:currencyLOVFND Currency CodesDD3BDFF9582F51D0E0530100007FEDB0select
fct.currency_code id,
fct.currency_code value,
fct.description
from
fnd_currencies_tl fct
where
fct.source_lang=userenv('LANG')
order by
fct.currency_codeUSCurrency6601=1gbpr.period_year=:fiscal_yearLOV customselect distinct
gbpr.period_year value,
null description
from
gl_budget_period_ranges gbpr,
gl_budget_versions gbv,
gl_budgets gb
where
gb.budget_name=:$flex$.budget and
gbv.budget_name=gb.budget_name and
gbv.budget_version_id=gbpr.budget_version_id and
gbpr.open_flag='O'USFiscal Year7701=1gcck.concatenated_segments=:accountLOV customselect
gcck.concatenated_segments value,
null description
from
gl_budget_assignments_unique_v gbauv,
gl_ledgers_v glv,
gl_code_combinations_kfv gcck,
gl_budget_entities gbe
where
glv.name=:$flex$.ledger and
gbauv.currency_code=:$flex$.currency and
gbauv.entry_code='E' and
gbauv.code_combination_id=gcck.code_combination_id and
glv.ledger_id=gbauv.ledger_id and
glv.chart_of_accounts_id=gcck.chart_of_accounts_id and
gbe.name=:$flex$.budget_organization and
gbe.ledger_id=glv.ledger_id and
gbe.budget_entity_id=gbauv.budget_entity_id and
gcck.enabled_flag='Y' and
gcck.summary_flag='N' and
gcck.detail_budgeting_allowed='Y' and
gcck.template_id is null and
sysdate between nvl(gcck.start_date_active,sysdate) and nvl(gcck.end_date_active,(sysdate+1))
order by
gbauv.ordering_value,
gbauv.budget_entity_id,
gbauv.code_combination_idUSAccountFD0C61D6D8894AE2A72DDFA4827502DADefaultAll columns.ENGINATICS11ACCOUNT10ADD_OR_REPLACE4BUDGET_NAME3BUDGET_ORGANIZATION5BUDGET_REQUIRES_JOURNAL8CURRENCY_CODE9FISCAL_YEAR7JOURNAL_CATEGORY_NAME6JOURNAL_SOURCE_NAME2LEDGER_NAME31PERIOD10_AMOUNT30PERIOD10_NAME33PERIOD11_AMOUNT32PERIOD11_NAME35PERIOD12_AMOUNT34PERIOD12_NAME37PERIOD13_AMOUNT36PERIOD13_NAME39PERIOD14_AMOUNT38PERIOD14_NAME41PERIOD15_AMOUNT40PERIOD15_NAME43PERIOD16_AMOUNT42PERIOD16_NAME45PERIOD17_AMOUNT44PERIOD17_NAME47PERIOD18_AMOUNT46PERIOD18_NAME49PERIOD19_AMOUNT48PERIOD19_NAME13PERIOD1_AMOUNT12PERIOD1_NAME51PERIOD20_AMOUNT50PERIOD20_NAME15PERIOD2_AMOUNT14PERIOD2_NAME17PERIOD3_AMOUNT16PERIOD3_NAME19PERIOD4_AMOUNT18PERIOD4_NAME21PERIOD5_AMOUNT20PERIOD5_NAME23PERIOD6_AMOUNT22PERIOD6_NAME25PERIOD7_AMOUNT24PERIOD7_NAME27PERIOD8_AMOUNT26PERIOD8_NAME29PERIOD9_AMOUNT28PERIOD9_NAME1REQUEST_OUTPUTYSSiteFD0C61D6D8894AE2A72DDFA4827502DA1ACTION_Charvarchar22STATUS_Charvarchar23MESSAGE_Charvarchar24MODIFIED_COLUMNS_Charvarchar2Y5ROW_IDCharvarchar2Y6REQUEST_ID_Charvarchar2Y7REQUEST_OUTPUTCharvarchar2Budget Import concurrent request output8GROUP_IDNumbernumberY9LEDGER_NAMELOVvarchar230GL Ledger8E2FF36EDEB879D2E0530100007F1FF2select
gl.name value,
fifsv.id_flex_structure_name||': '||decode(gl.ledger_category_code,'NONE',xxen_util.meaning(gl.object_type_code,'LEDGERS',101),xxen_util.meaning(gl.ledger_category_code,'GL_ASF_LEDGER_CATEGORY',101))||': '||gl.description description
from
gl_ledgers gl,
fnd_id_flex_structures_vl fifsv
where
(:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y')) and
(:$flex$.chart_of_accounts is null or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y') and
gl.ledger_id in (select xroa.id from xxen_report_org_access xroa where xroa.access_type='LEDGER') and
gl.chart_of_accounts_id=fifsv.id_flex_num and
fifsv.id_flex_code='GL#' and
fifsv.application_id=101
order by
fifsv.id_flex_structure_name,
decode(gl.ledger_category_code,'PRIMARY',1,'SECONDARY',2,'ALC',3,'NONE',4),
gl.nameselect
gl.ledger_id id,
gl.name value
from
gl_ledgers gl:$flex$.ledgerY10BUDGET_ORGANIZATIONLOV customvarchar225select
gbe.name value,
gbe.description
from
gl_budget_entities gbe,
gl_ledgers_v glv
where
gbe.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_nameY11BUDGET_NAMELOV customvarchar215select
gbv.budget_name value,
gbv.description
from
gl_entity_budgets geb,
gl_budget_versions gbv,
gl_budget_entities gbe,
gl_ledgers_v glv
where
geb.budget_version_id=gbv.budget_version_id and
geb.budget_entity_id=gbe.budget_entity_id and
gbe.name=:$column$.budget_organization and
gbe.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gbv.status='O'Y12BUDGET_REQUIRES_JOURNALCharvarchar2select xxen_util.meaning(gb.require_budget_journals_flag ,'YES_NO',0) from gl_budgets gb where gb.budget_name=:$column$.budget_nameYes when the budget requires budget journals. Its amounts are then imported as budget journals and added to the balance.13JOURNAL_SOURCE_NAMELOV customvarchar2select
gjsv.user_je_source_name value,
gjsv.description
from
gl_je_sources_v gjsv,
gl_budgets gb
where
gb.budget_name=:$column$.budget_name and
gb.require_budget_journals_flag='Y'Journal source of the budget journals, required for a budget that requires budget journals.14JOURNAL_CATEGORY_NAMELOV customvarchar2select
gjcv.user_je_category_name value,
gjcv.description
from
gl_je_categories_v gjcv,
gl_budgets gb
where
gb.budget_name=:$column$.budget_name and
gb.require_budget_journals_flag='Y'Journal category of the budget journals, required for a budget that requires budget journals.15CURRENCY_CODELOVvarchar215FND Currency8E2FF36EDE9079D2E0530100007F1FF2select
fcv.currency_code id,
fcv.currency_code value,
fcv.description
from
fnd_currencies_vl fcv
where
fcv.enabled_flag='Y'
order by
fcv.currency_codeY16FISCAL_YEARLOV customnumberselect distinct
gbpr.period_year value,
null description
from
gl_budget_period_ranges gbpr,
gl_budget_versions gbv,
gl_budgets gb
where
gb.budget_name=:$column$.budget_name and
gbv.budget_name=gb.budget_name and
gbv.budget_version_id=gbpr.budget_version_id and
gbpr.open_flag='O'Fiscal year of an open budget period range. The period name columns default from it.Y17ADD_OR_REPLACELOV customvarchar2select
flvv.lookup_code id,
flvv.meaning value,
flvv.description
from
fnd_lookup_values_vl flvv,
gl_budgets gb
where
flvv.lookup_type ='GL_BUDGET_UPDATE_MODE' and
gb.budget_name=:$column$.budget_name and
gb.require_budget_journals_flag='N'
union all
select
flvv.lookup_code id,
flvv.meaning value,
flvv.description
from
fnd_lookup_values_vl flvv,
gl_budgets gb
where
flvv.lookup_type ='GL_BUDGET_UPDATE_MODE' and
gb.budget_name=:$column$.budget_name and
gb.require_budget_journals_flag='Y' and
flvv.lookup_code='A'Add adds the period amounts to the budget balance, Replace replaces the balance. A budget that requires budget journals allows Add only.Y18ACCOUNTLOV customvarchar2311Yselect
gcck.concatenated_segments value,
null description
from
gl_budget_assignments_unique_v gbauv,
gl_ledgers_v glv,
gl_code_combinations_kfv gcck,
gl_budget_entities gbe
where
glv.name=:$column$.ledger_name and
gbauv.currency_code=:$column$.currency_code and
gbauv.entry_code='E' and
gbauv.code_combination_id=gcck.code_combination_id and
glv.ledger_id=gbauv.ledger_id and
glv.chart_of_accounts_id=gcck.chart_of_accounts_id and
gbe.name=:$column$.budget_organization and
gbe.ledger_id=glv.ledger_id and
gbe.budget_entity_id=gbauv.budget_entity_id and
gcck.enabled_flag='Y' and
gcck.summary_flag='N' and
gcck.detail_budgeting_allowed='Y' and
gcck.template_id is null and
sysdate between nvl(gcck.start_date_active,sysdate) and nvl(gcck.end_date_active,(sysdate+1))
order by
gbauv.ordering_value,
gbauv.budget_entity_id,
gbauv.code_combination_idAccount assigned to the budget organization for the currency, with entry type Entered.Y19PERIOD1_NAMECharvarchar215select
gps.period_name
from
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=1 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num20PERIOD1_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.21PERIOD2_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=2 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num22PERIOD2_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.23PERIOD3_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=3 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num24PERIOD3_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.25PERIOD4_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=4 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num26PERIOD4_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.27PERIOD5_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=5 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num28PERIOD5_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.29PERIOD6_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=6 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num30PERIOD6_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.31PERIOD7_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=7 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num32PERIOD7_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.33PERIOD8_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=8 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num34PERIOD8_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.35PERIOD9_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=9 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num36PERIOD9_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.37PERIOD10_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=10 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num38PERIOD10_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.39PERIOD11_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=11 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num40PERIOD11_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.41PERIOD12_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=12 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num42PERIOD12_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.43PERIOD13_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=13 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num44PERIOD13_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.45PERIOD14_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=14 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num46PERIOD14_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.47PERIOD15_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=15 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num48PERIOD15_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.49PERIOD16_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=16 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num50PERIOD16_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.51PERIOD17_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=17 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num52PERIOD17_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.53PERIOD18_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=18 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num54PERIOD18_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.55PERIOD19_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=19 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num56PERIOD19_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.57PERIOD20_NAMECharvarchar215select
gps.period_name
from
gl_budgets gb,
gl_period_statuses gps,
gl_ledgers_v glv,
gl_budget_versions gbv,
gl_budget_period_ranges gbpr
where
gbv.budget_name=gb.budget_name and
gb.budget_name=:$column$.budget_name and
gps.ledger_id=glv.ledger_id and
glv.name=:$column$.ledger_name and
gps.period_year=:$column$.fiscal_year and
gps.application_id=101 and
gps.period_num=20 and
gbpr.budget_version_id=gbv.budget_version_id and
gbpr.period_year=gps.period_year and
gps.period_num between gbpr.start_period_num and gbpr.end_period_num58PERIOD20_AMOUNTNumbernumberBudget amount of the period. Blank leaves the period unchanged. For a budget that requires budget journals the amount is added to the balance, so enter the change, not the downloaded balance.59UPLOAD_ROWNumbernumberYU1p_ledger_idinNumberLEDGER_NAMEU2p_budget_nameinCharBUDGET_NAMEU3p_budget_entity_nameinCharBUDGET_ORGANIZATIONU4p_currency_codeinCharCURRENCY_CODEU5p_fiscal_yearinCharFISCAL_YEARU6p_update_logic_typeinCharADD_OR_REPLACEU7p_accountinCharACCOUNTvalueU8p_period1_amountinNumberPERIOD1_AMOUNTU9p_period2_amountinNumberPERIOD2_AMOUNTU10p_period3_amountinNumberPERIOD3_AMOUNTU11p_period4_amountinNumberPERIOD4_AMOUNTU12p_period5_amountinNumberPERIOD5_AMOUNTU13p_period6_amountinNumberPERIOD6_AMOUNTU14p_period7_amountinNumberPERIOD7_AMOUNTU15p_period8_amountinNumberPERIOD8_AMOUNTU16p_period9_amountinNumberPERIOD9_AMOUNTU17p_period10_amountinNumberPERIOD10_AMOUNTU18p_period11_amountinNumberPERIOD11_AMOUNTU19p_period12_amountinNumberPERIOD12_AMOUNTU20p_period13_amountinNumberPERIOD13_AMOUNTU21p_period14_amountinNumberPERIOD14_AMOUNTU22p_period15_amountinNumberPERIOD15_AMOUNTU23p_period16_amountinNumberPERIOD16_AMOUNTU24p_period17_amountinNumberPERIOD17_AMOUNTU25p_period18_amountinNumberPERIOD18_AMOUNTU26p_period19_amountinNumberPERIOD19_AMOUNTU27p_period20_amountinNumberPERIOD20_AMOUNTU28p_period21_amountinNumberU29p_period22_amountinNumberU30p_period23_amountinNumberU31p_period24_amountinNumberU32p_period25_amountinNumberU33p_period26_amountinNumberU34p_period27_amountinNumberU35p_period28_amountinNumberU36p_period29_amountinNumberU37p_period30_amountinNumberU38p_period31_amountinNumberU39p_period32_amountinNumberU40p_period33_amountinNumberU41p_period34_amountinNumberU42p_period35_amountinNumberU43p_period36_amountinNumberU44p_period37_amountinNumberU45p_period38_amountinNumberU46p_period39_amountinNumberU47p_period40_amountinNumberU48p_period41_amountinNumberU49p_period42_amountinNumberU50p_period43_amountinNumberU51p_period44_amountinNumberU52p_period45_amountinNumberU53p_period46_amountinNumberU54p_period47_amountinNumberU55p_period48_amountinNumberU56p_period49_amountinNumberU57p_period50_amountinNumberU58p_period51_amountinNumberU59p_period52_amountinNumberU60p_period53_amountinNumberU61p_period54_amountinNumberU62p_period55_amountinNumberU63p_period56_amountinNumberU64p_period57_amountinNumberU65p_period58_amountinNumberU66p_period59_amountinNumberU67p_period60_amountinNumberU68p_user_je_category_nameinCharJOURNAL_CATEGORY_NAMEU69p_user_je_source_nameinCharJOURNAL_SOURCE_NAMEU71status_outCharSTATUS_CODE_U72message_outCharMESSAGE_U73row_id_outCharROW_IDP1p_user_je_source_nameinCharJOURNAL_SOURCE_NAMEP2p_ledger_nameinCharLEDGER_NAMEvalueP3p_budget_organizationinCharBUDGET_ORGANIZATIONP4p_budget_nameinCharBUDGET_NAMEP5request_id_outNumberREQUEST_ID_P6action_inCharACTION_CODE_P7status_outCharSTATUS_CODE_P8message_outCharMESSAGE_P9group_idoutNumberGROUP_IDError SQLselect
xxen_upload.action_meaning(xu.action_code_) action_,
xxen_upload.status_meaning(xxen_upload.status_error) status_,
xu.message_||' Please check the request output for details.' message_,
xu.modified_columns_,
xu.row_id,
xu.request_id_,
case when xu.request_id_ is not null then 'HYPERLINK("'||xxen_util.concurrent_request_file_url('O',xu.request_id_)||'")' end request_output,
xu.group_id,
xu.ledger_name,
xu.budget_organization,
xu.budget_name,
xu.budget_requires_journal,
xu.journal_source_name,
xu.journal_category_name,
xu.currency_code,
to_number(xu.fiscal_year) fiscal_year,
xu.add_or_replace,
xu.account,
xu.period1_name,
xu.period1_amount,
xu.period2_name,
xu.period2_amount,
xu.period3_name,
xu.period3_amount,
xu.period4_name,
xu.period4_amount,
xu.period5_name,
xu.period5_amount,
xu.period6_name,
xu.period6_amount,
xu.period7_name,
xu.period7_amount,
xu.period8_name,
xu.period8_amount,
xu.period9_name,
xu.period9_amount,
xu.period10_name,
xu.period10_amount,
xu.period11_name,
xu.period11_amount,
xu.period12_name,
xu.period12_amount,
xu.period13_name,
xu.period13_amount,
xu.period14_name,
xu.period14_amount,
xu.period15_name,
xu.period15_amount,
xu.period16_name,
xu.period16_amount,
xu.period17_name,
xu.period17_amount,
xu.period18_name,
xu.period18_amount,
xu.period19_name,
xu.period19_amount,
xu.period20_name,
xu.period20_amount,
xu.upload_row
from
xxen_gl_budget_amounts_1858_u xu,
gl_budgets gb
where
gb.budget_name=xu.budget_name and
( xu.status_code_=xxen_upload.status_error or
(
(gb.require_budget_journals_flag='Y' and
exists (select 1 from gl_interface gi where gi.group_id=xu.group_id and nvl(gi.status,'PROCESSED')<>'PROCESSED'))
or
(gb.require_budget_journals_flag='N' and
exists (select 1 from gl_budget_interface gbi where gbi.rowid=chartorowid(xu.row_id)))
)
)Order Byupload_row,
budget_organization,
budget_name,
accountSuccess SQLselect
xxen_upload.action_meaning(xu.action_code_) action_,
xxen_upload.status_meaning(xxen_upload.status_success) status_,
'Successfully processed' message_,
xu.modified_columns_,
xu.row_id,
xu.request_id_,
case when xu.request_id_ is not null then 'HYPERLINK("'||xxen_util.concurrent_request_file_url('O',xu.request_id_)||'")' end request_output,
to_number(null) group_id,
glv.name ledger_name,
gbe.name budget_organization,
gbve.budget_name,
xu.budget_requires_journal,
xu.journal_source_name,
xu.journal_category_name,
xu.currency_code,
to_number(xu.fiscal_year) fiscal_year,
null add_or_replace,
gcck.concatenated_segments account,
xu.period1_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=1 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=1 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period1_amount,
xu.period2_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=2 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=2 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period2_amount,
xu.period3_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=3 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=3 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period3_amount,
xu.period4_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=4 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=4 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period4_amount,
xu.period5_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=5 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=5 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period5_amount,
xu.period6_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=6 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=6 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period6_amount,
xu.period7_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=7 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=7 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period7_amount,
xu.period8_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=8 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=8 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period8_amount,
xu.period9_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=9 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=9 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period9_amount,
xu.period10_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=10 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=10 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period10_amount,
xu.period11_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=11 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=11 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period11_amount,
xu.period12_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=12 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=12 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period12_amount,
xu.period13_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=13 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=13 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period13_amount,
xu.period14_name,
(
select
(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.budget_version_id=geb.budget_version_id and
gb.period_num=14 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id and
gb.last_update_date>sysdate-.1
) period14_amount,
xu.period15_name,
(
select
(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.budget_version_id=geb.budget_version_id and
gb.period_num=15 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id and
gb.last_update_date>sysdate-.1
) period15_amount,
xu.period16_name,
(
select
(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.budget_version_id=geb.budget_version_id and
gb.period_num=16 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id and
gb.last_update_date>sysdate-.1
) period16_amount,
xu.period17_name,
(
select
(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.budget_version_id=geb.budget_version_id and
gb.period_num=17 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id and
gb.last_update_date>sysdate-.1
) period17_amount,
xu.period18_name,
(
select
(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.budget_version_id=geb.budget_version_id and
gb.period_num=18 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id and
gb.last_update_date>sysdate-.1
) period18_amount,
xu.period19_name,
(
select
(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.budget_version_id=geb.budget_version_id and
gb.period_num=19 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id and
gb.last_update_date>sysdate-.1
) period19_amount,
xu.period20_name,
case when gbv.require_budget_journals_flag='Y' then
(select
sum(nvl(gbp.entered_dr,0) - nvl(gbp.entered_cr,0))
from
gl_bc_packets gbp
where
gbp.budget_version_id=gbve.budget_version_id and
gbp.ledger_id=gbe.ledger_id and
gbp.period_num=20 and
gbp.period_year=xu.fiscal_year and
gbp.actual_flag='B' and
gbp.status_code='A' and
gbp.code_combination_id=gcck.code_combination_id)
else
(
select
sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
from
gl_balances gb
where
gb.budget_version_id=gbve.budget_version_id and
gb.ledger_id=gbe.ledger_id and
gb.period_num=20 and
gb.period_year=xu.fiscal_year and
gb.actual_flag='B' and
gcck.code_combination_id=gb.code_combination_id
)
end period20_amount,
xu.upload_row
from
xxen_gl_budget_amounts_1858_u xu,
gl_budgets_v gbv,
gl_budget_entities gbe,
gl_entity_budgets geb,
gl_budget_versions gbve,
gl_ledgers_v glv,
gl_code_combinations_kfv gcck
where
xu.status_code_=xxen_upload.status_success and
gcck.chart_of_accounts_id=glv.chart_of_accounts_id and
gbv.budget_name=gbve.budget_name and
gbv.budget_name=gbve.budget_name and
glv.ledger_id=gbe.ledger_id and
geb.budget_version_id=gbve.budget_version_id and
gbe.budget_entity_id=geb.budget_entity_id and
gbv.budget_name=xu.budget_name and
gbe.name=xu.budget_organization and
xu.account=gcck.concatenated_segments and
gcck.enabled_flag='Y' and
gcck.summary_flag='N' and
gcck.detail_budgeting_allowed='Y' and
gcck.template_id is null and
sysdate between nvl(gcck.start_date_active,sysdate) and nvl(gcck.end_date_active,(sysdate+1)) and
(
(gbv.require_budget_journals_flag='Y' and
not exists (select 1 from gl_interface gi where gi.group_id=xu.group_id and nvl(gi.status,'PROCESSED')<>'PROCESSED'))
or
(gbv.require_budget_journals_flag='N' and
not exists (select 1 from gl_budget_interface gbi where gbi.rowid=chartorowid(xu.row_id)))
)