[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
'Select gcc.code_combination_id
From gl_code_combinations gcc,
gl_sets_of_books gsob
Where gsob.set_of_books_id = :l_sob_id
And gcc.chart_of_accounts_id = gsob.chart_of_accounts_id
And gcc.summary_flag = :x_summary_flag
And gcc.template_id IS NULL '
|| l_Where_Clause ;
select gl_sob.currency_code as CURRENCY_CODE
from gl_sets_of_books gl_sob
where gl_sob.set_of_books_id = sob_id;
select gbv.budget_version_id
from gl_budgets gb,
gl_budget_versions gbv
where gb.ledger_id = sob_id
and gb.budget_name = name
and gbv.budget_name = gb.budget_name
order by gb.current_version_id;
Select sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
From gl_balances gb
Where gb.code_combination_id = ccid
And gb.ledger_id = sob_id
And gb.currency_code = currency
And gb.actual_flag = 'A'
And gb.template_id IS NULL
And gb.period_name IN
(Select gps.period_name
From gl_period_statuses gps
Where application_id = 101
And ledger_id = sob_id
And gps.start_date >= start_dt
And gps.end_date <= end_dt);
Select sum(nvl(gb.period_net_dr,0) - nvl(gb.period_net_cr,0))
From gl_balances gb
Where code_combination_id = ccid
And ledger_id = sob_id
And currency_code = currency
And actual_flag = 'B'
And budget_version_id = bud_id
And template_id IS NULL
And period_name IN
(Select gps.period_name
From gl_period_statuses gps
Where application_id = 101
And ledger_id = sob_id
And gps.start_date >= start_dt
And gps.end_date <= end_dt);
l_insert_ctr NUMBER := 0;
select gl_sob.currency_code as CURRENCY_CODE
from gl_sets_of_books gl_sob
where gl_sob.set_of_books_id = sob_id;
l_info := 'inserting into pn_rec_exp_itf table for CCID: '||to_char(p_ccid_t(i).ccid);
insert into pn_rec_exp_itf
(
EXPENSE_LINE_DTL_ID,
PROPERTY_ID,
LOCATION_ID,
EXPENSE_TYPE_CODE,
EXPENSE_ACCOUNT_ID,
ACCOUNT_DESCRIPTION,
ACTUAL_AMOUNT,
BUDGETED_AMOUNT,
CURRENCY_CODE,
FROM_DATE,
TO_DATE,
TRANSFER_FLAG,
MODE_FLAG,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
ORG_ID
)
values
(
null,
p_map_t(p_ccid_t(i).map_row).PROPERTY_ID,
p_map_t(p_ccid_t(i).map_row).LOCATION_ID,
p_map_t(p_ccid_t(i).map_row).EXPENSE_TYPE_CODE,
p_ccid_t(i).CCID,
null,
nvl(p_ccid_t(i).ACT_AMOUNT,0),
nvl(p_ccid_t(i).BUD_AMOUNT,0),
l_currency_code,
P_FROM_DATE,
P_TO_DATE,
'N',
null,
SYSDATE,
nvl(fnd_profile.value('USER_ID'),-1),
SYSDATE,
nvl(fnd_profile.value('USER_ID'),-1),
nvl(fnd_profile.value('USER_ID'),-1),
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
null,
to_number(pn_mo_cache_utils.get_current_org_id)
);
l_insert_ctr := l_insert_ctr + 1;
Put_Log('Number of records inserted: '||to_char(l_insert_ctr));
Select g.period_name,
g.start_date,
g.end_date
From gl_period_statuses g,
gl_sets_of_books b
Where g.application_id = 101
And g.ledger_id = p_set_of_books_id
And b.set_of_books_id = g.ledger_id
And g.period_type = b.accounted_period_type
And g.period_name = period;
Select g.period_name,
g.start_date,
g.end_date
From gl_period_statuses g,
gl_sets_of_books b
Where g.application_id = 101
And g.ledger_id = p_set_of_books_id
And b.set_of_books_id = g.set_of_books_id
And g.period_type = b.accounted_period_type
And g.start_date >= start_dt
And g.end_date <= end_dt
Order by g.start_date;
Select map.*
From PN_LOC_ACC_MAP_ALL map
Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
And map.RECOVERABLE_FLAG = 'Y'
And map.effective_from_date <= l_period_end_enddt
And map.effective_to_date >= l_period_start_stdt;
Select map.*
From PN_LOC_ACC_MAP_ALL map
Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
And map.LOCATION_ID = p_location_id
And map.RECOVERABLE_FLAG = 'Y'
And map.effective_from_date <= l_period_end_enddt
And map.effective_to_date >= l_period_start_stdt;
Select map.*
From PN_LOC_ACC_MAP_ALL map
Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
And map.PROPERTY_ID = p_property_id
And map.RECOVERABLE_FLAG = 'Y'
And map.effective_from_date <= l_period_end_enddt
And map.effective_to_date >= l_period_start_stdt;
Select map.*
From PN_LOC_ACC_MAP_ALL map
Where map.LOC_ACC_MAP_HDR_ID = p_loc_acc_map_hdr_id
And map.PROPERTY_ID = p_property_id
And map.LOCATION_ID = p_location_id
And map.RECOVERABLE_FLAG = 'Y'
And map.effective_from_date <= l_period_end_enddt
And map.effective_to_date >= l_period_start_stdt;
select gl_sob.currency_code as CURRENCY_CODE
from gl_sets_of_books gl_sob
where gl_sob.set_of_books_id = sob_id;
select prop.PROPERTY_CODE
from PN_PROPERTIES_ALL prop
where prop.property_id = prop_id;
select loc.LOCATION_CODE
from pn_locations_ALL loc
where loc.LOCATION_ID = loc_id;
map_tbl.delete; /* reset the table */
code_combinations_tbl.DELETE;
/* --- insert data into the ITF table --- */
populate_rec_exp_itf(p_sob_id => to_number(p_set_of_books_id),
p_from_date => l_period_start_date,
p_to_date => l_period_end_date,
p_map_t => map_tbl,
p_ccid_t => code_combinations_tbl);