[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select accounting_flex_structure
into structure_num
from fa_book_controls
where book_type_code = BOOK;
SELECT s.segment_num INTO this_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = structure_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = structure_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = 'GL_BALANCING';
SELECT count(segment_num) INTO balancing_segnum
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = structure_num
AND enabled_flag = 'Y'
AND segment_num <= this_segment_num;
SELECT s.segment_num INTO this_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = structure_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = structure_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = 'GL_ACCOUNT';
SELECT count(segment_num) INTO account_segnum
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = structure_num
AND enabled_flag = 'Y'
AND segment_num <= this_segment_num;
SELECT s.segment_num INTO this_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = structure_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = structure_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = 'FA_COST_CTR';
SELECT count(segment_num) INTO cc_segnum
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = structure_num
AND enabled_flag = 'Y'
AND segment_num <= this_segment_num;
select accounting_flex_structure
into structure_num
from fa_book_controls
where book_type_code = BOOK;
SELECT to_number(substr(s.application_column_name,8,2)) INTO this_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = structure_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = structure_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = 'GL_BALANCING';
SELECT count(segment_num) INTO balancing_segnum
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = structure_num
AND enabled_flag = 'Y'
AND to_number(substr(application_column_name,8,2)) <= this_segment_num;
SELECT to_number(substr(s.application_column_name,8,2)) INTO this_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = structure_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = structure_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = 'GL_ACCOUNT';
SELECT count(segment_num) INTO account_segnum
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = structure_num
AND enabled_flag = 'Y'
AND to_number(substr(application_column_name,8,2)) <= this_segment_num;
SELECT to_number(substr(s.application_column_name,8,2)) INTO this_segment_num
FROM fnd_id_flex_segments s, fnd_segment_attribute_values sav,
fnd_segment_attribute_types sat
WHERE s.application_id = 101
AND s.id_flex_code = 'GL#'
AND s.id_flex_num = structure_num
AND s.enabled_flag = 'Y'
AND s.application_column_name = sav.application_column_name
AND sav.application_id = 101
AND sav.id_flex_code = 'GL#'
AND sav.id_flex_num = structure_num
AND sav.attribute_value = 'Y'
AND sav.segment_attribute_type = sat.segment_attribute_type
AND sat.application_id = 101
AND sat.id_flex_code = 'GL#'
AND sat.unique_flag = 'Y'
AND sat.segment_attribute_type = 'FA_COST_CTR';
SELECT count(segment_num) INTO cc_segnum
FROM fnd_id_flex_segments
WHERE application_id = 101
AND id_flex_code = 'GL#'
AND id_flex_num = structure_num
AND enabled_flag = 'Y'
AND to_number(substr(application_column_name,8,2)) <= this_segment_num;
selectedsegs Seg_Array;
select segment1, segment2, segment3, segment4, segment5,
segment6, segment7, segment8, segment9, segment10,
segment11, segment12, segment13, segment14, segment15,
segment16, segment17, segment18, segment19, segment20,
segment21, segment22, segment23, segment24, segment25,
segment26, segment27, segment28, segment29, segment30
into selectedsegs(1), selectedsegs(2), selectedsegs(3), selectedsegs(4), selectedsegs(5),
selectedsegs(6), selectedsegs(7), selectedsegs(8), selectedsegs(9), selectedsegs(10),
selectedsegs(11), selectedsegs(12), selectedsegs(13), selectedsegs(14), selectedsegs(15),
selectedsegs(16), selectedsegs(17), selectedsegs(18), selectedsegs(19), selectedsegs(20),
selectedsegs(21), selectedsegs(22), selectedsegs(23), selectedsegs(24), selectedsegs(25),
selectedsegs(26), selectedsegs(27), selectedsegs(28), selectedsegs(29), selectedsegs(30)
from gl_code_combinations
where code_combination_id = l_ccid;
if (selectedsegs(i) is not null) then
n_segments := n_segments + 1;
segments(n_segments) := selectedsegs(i);
h_mesg_name := 'FA_SHARED_DELETE_FAILED';
DELETE FROM FA_RESERVE_LEDGER;
SELECT
BC.DISTRIBUTION_SOURCE_BOOK dbk,
nvl (DP.PERIOD_CLOSE_DATE, sysdate) ucd,
DP.PERIOD_COUNTER upc,
min (DP_FY.PERIOD_OPEN_DATE) tod,
min (DP_FY.PERIOD_COUNTER) tpc
INTO
dist_book,
ucd,
upc,
tod,
tpc
FROM
FA_DEPRN_PERIODS DP,
FA_DEPRN_PERIODS DP_FY,
FA_BOOK_CONTROLS BC
WHERE
DP.BOOK_TYPE_CODE = book AND
DP.PERIOD_NAME = period AND
DP_FY.BOOK_TYPE_CODE = book AND
DP_FY.FISCAL_YEAR = DP.FISCAL_YEAR
AND BC.BOOK_TYPE_CODE = book
GROUP BY
BC.DISTRIBUTION_SOURCE_BOOK,
DP.PERIOD_CLOSE_DATE,
DP.PERIOD_COUNTER;
operation := 'Inserting into FA_RESERVE_LEDGER_GT';
h_mesg_name := 'FA_SHARED_INSERT_FAILED';
INSERT INTO FA_RESERVE_LEDGER_GT
(ASSET_ID,
DH_CCID,
DEPRN_RESERVE_ACCT,
DATE_PLACED_IN_SERVICE,
METHOD_CODE,
LIFE,
RATE,
CAPACITY,
COST,
DEPRN_AMOUNT,
YTD_DEPRN,
DEPRN_RESERVE,
PERCENT,
TRANSACTION_TYPE,
PERIOD_COUNTER,
DATE_EFFECTIVE,
DISTRIBUTION_ID)
SELECT
DH.ASSET_ID ASSET_ID,
DH.CODE_COMBINATION_ID DH_CCID,
CB.DEPRN_RESERVE_ACCT RSV_ACCOUNT,
BOOKS.DATE_PLACED_IN_SERVICE START_DATE,
BOOKS.DEPRN_METHOD_CODE METHOD,
BOOKS.LIFE_IN_MONTHS LIFE,
BOOKS.ADJUSTED_RATE RATE,
BOOKS.PRODUCTION_CAPACITY CAPACITY,
DD.COST COST,
decode (DD.PERIOD_COUNTER, upc, DD.DEPRN_AMOUNT, 0)
DEPRN_AMOUNT,
decode (sign (tpc - DD.PERIOD_COUNTER), 1, 0, DD.YTD_DEPRN)
YTD_DEPRN,
DD.DEPRN_RESERVE DEPRN_RESERVE,
round (decode (TH.TRANSACTION_TYPE_CODE, null,
DH.UNITS_ASSIGNED / AH.UNITS * 100),2)
PERCENT,
decode (TH.TRANSACTION_TYPE_CODE, null,
decode (TH_RT.TRANSACTION_TYPE_CODE,
'FULL RETIREMENT', 'F',
decode (BOOKS.DEPRECIATE_FLAG, 'NO', 'N')),
'TRANSFER', 'T',
'TRANSFER OUT', 'P',
'RECLASS', 'R') T_TYPE,
DD.PERIOD_COUNTER,
NVL(TH.DATE_EFFECTIVE, ucd),
DH.DISTRIBUTION_ID
FROM
FA_DEPRN_DETAIL DD,
FA_ASSET_HISTORY AH,
FA_TRANSACTION_HEADERS TH,
FA_TRANSACTION_HEADERS TH_RT,
FA_BOOKS BOOKS,
FA_DISTRIBUTION_HISTORY DH,
FA_CATEGORY_BOOKS CB
WHERE
CB.BOOK_TYPE_CODE = book AND
CB.CATEGORY_ID = AH.CATEGORY_ID
AND
AH.ASSET_ID = DH.ASSET_ID AND
AH.DATE_EFFECTIVE < nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(AH.DATE_INEFFECTIVE,sysdate)
>= nvl(TH.DATE_EFFECTIVE, ucd) AND
-- AH.ASSET_TYPE = 'CAPITALIZED'
( ( AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP') AND
BOOKS.GROUP_ASSET_ID is null
) OR
( AH.ASSET_TYPE = 'CAPITALIZED' AND
BOOKS.GROUP_ASSET_ID is not null
and exists (select 1
from fa_books oldbk
, fa_transaction_headers oldth
, fa_deprn_periods dp
where oldbk.transaction_header_id_out = books.transaction_header_id_in
and oldbk.transaction_header_id_out = oldth.transaction_header_id
and dp.book_type_code = book
and dp.period_counter = dd.period_counter
and oldth.date_effective between dp.period_open_date
and nvl(dp.period_close_date, oldth.date_effective)
and oldbk.group_asset_id is null)
) OR
( nvl(report_style,'S') = 'D' AND
AH.ASSET_TYPE in ('CAPITALIZED', 'GROUP')
)
)
AND
DD.BOOK_TYPE_CODE = book AND
DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID AND
DD.PERIOD_COUNTER =
(SELECT max (DD_SUB.PERIOD_COUNTER)
FROM FA_DEPRN_DETAIL DD_SUB
WHERE DD_SUB.BOOK_TYPE_CODE = book
AND DD_SUB.ASSET_ID = DH.ASSET_ID
AND DD_SUB.DISTRIBUTION_ID = DH.DISTRIBUTION_ID
AND DD_SUB.PERIOD_COUNTER <= upc)
AND
TH_RT.BOOK_TYPE_CODE = book AND
TH_RT.TRANSACTION_HEADER_ID = BOOKS.TRANSACTION_HEADER_ID_IN
AND
BOOKS.BOOK_TYPE_CODE = book AND
BOOKS.ASSET_ID = DH.ASSET_ID AND
-- Commented for bugfix #4610445
-- nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= tpc AND
-- Added for bugfix #4610445
nvl(BOOKS.PERIOD_COUNTER_FULLY_RETIRED, upc) >= upc AND
BOOKS.DATE_EFFECTIVE <= nvl(TH.DATE_EFFECTIVE, ucd) AND
nvl(BOOKS.DATE_INEFFECTIVE,sysdate+1) > nvl(TH.DATE_EFFECTIVE, ucd)
AND
TH.BOOK_TYPE_CODE (+) = dist_book AND
TH.TRANSACTION_HEADER_ID (+) = DH.TRANSACTION_HEADER_ID_OUT AND
TH.DATE_EFFECTIVE (+) BETWEEN tod and ucd
AND
DH.BOOK_TYPE_CODE = dist_book AND
-- Commented for bugfix #4610445
-- DH.DATE_EFFECTIVE <= ucd AND
-- nvl(DH.DATE_INEFFECTIVE, sysdate) > tod and
-- Added for bugfix #4610445
ucd between dh.date_effective and nvl(dh.date_ineffective,ucd);
if h_mesg_name in ('FA_FLEX_DELETE_FAILED','FA_FLEX_INSERT_FAILED') then
fnd_message.set_token('TABLE',h_table_token,FALSE);
select g.application_column_name, g.segment_num
from fnd_columns c, fnd_id_flex_segments g
WHERE g.application_id = appl_id
AND g.id_flex_code = flex_code
AND g.id_flex_num = struct_id
AND g.enabled_flag = 'Y'
AND c.application_id = appl_id
AND c.table_id = table_id
AND c.column_name = g.application_column_name
group by g.application_column_name, g.segment_num
ORDER BY g.segment_num;
v_sqlstmt := 'select ';
Select s.concatenated_segment_delimiter into delim
FROM fnd_id_flex_structures s, fnd_application a
WHERE s.application_id = a.application_id
AND s.id_flex_code = flex_code
AND s.id_flex_num = struct_id
AND a.application_short_name = appl_short_name;
select table_id into h_table_id from fnd_tables
where table_name = 'FA_CATEGORIES_B' and application_id = 140;
select table_id into h_table_id from fnd_tables
where table_name = 'FA_LOCATIONS' and application_id = 140;
select table_id into h_table_id from fnd_tables
where table_name = 'FA_ASSET_KEYWORDS' and application_id = 140;
select table_id into h_table_id from fnd_tables
where table_name = 'GL_CODE_COMBINATIONS' and application_id = 101;
select ltrim(ltrim(t.user_concurrent_program_name, 'RX-only:')),
b.concurrent_program_name, cr.argument_text,
cr.request_id, lp.meaning, ls.meaning, cr.phase_code, cr.status_code,
to_char(cr.request_date, dateform || ' HH24:MI:SS')
from fnd_lookups ls, fnd_lookups lp, fnd_concurrent_programs_tl t,
fnd_concurrent_programs b, fnd_concurrent_requests cr
where lp.lookup_type = 'CP_PHASE_CODE' and
lp.lookup_code = cr.phase_code and
ls.lookup_type = 'CP_STATUS_CODE' and
ls.lookup_code = cr.status_code and
cr.requested_by = userid and
b.concurrent_program_id = cr.concurrent_program_id and
b.application_id = applid and
B.CONCURRENT_PROGRAM_ID = T.CONCURRENT_PROGRAM_ID and
B.APPLICATION_ID = T.APPLICATION_ID and
T.LANGUAGE = userenv('LANG') and
b.concurrent_program_name like prog_name_template
order by cr.request_id desc;
select argument1, argument2, argument3, argument4, argument5,
argument6, argument7, argument8, argument9, argument10,
argument11, argument12, argument13, argument14, argument15,
argument16, argument17, argument18, argument19, argument20,
argument21, argument22, argument23, argument24, argument25
into arg1, arg2, arg3, arg4, arg5, arg6, arg7, arg8, arg9, arg10,
arg11, arg12, arg13, arg14, arg15, arg16, arg17, arg18,
arg19, arg20, arg21, arg22, arg23, arg24, arg25
from fnd_concurrent_requests
where request_id = req_id;
insert into fa_rx_dynamic_columns (
request_id, attribute_name, column_name, ordering, break,
display_length, display_format, display_status, last_update_date,
last_update_login, last_updated_by, created_by, creation_date)
values (X_request_id, X_attribute_name, X_column_name, X_ordering,
X_break, X_display_length, X_display_format, X_display_status,
sysdate, h_login_id, h_user_id, h_user_id, sysdate);
fnd_message.set_name('OFA','FA_FLEX_INSERT_FAILED');
select it and move on with the rest of the function. */
if (v_flex_value_set_name is null)
then
if (v_flex_value_set_id is null)
then
return(v_flex_value);
select flex_value_set_name
into vsname
from fnd_flex_value_sets
where flex_value_set_id = v_flex_value_set_id;
select flex_value_set_id
into vsid
from fnd_flex_value_sets
where flex_value_set_name = v_flex_value_set_name;
select it and move on with the rest of the function. */
if (v_flex_value_set_name is null)
then
if (v_flex_value_set_id is null)
then
return(v_flex_value);
select flex_value_set_name
into vsname
from fnd_flex_value_sets
where flex_value_set_id = v_flex_value_set_id;
select flex_value_set_id
into vsid
from fnd_flex_value_sets
where flex_value_set_name = v_flex_value_set_name;
select
distinct v.vendor_name
from
po_vendors v,
fa_asset_invoices i
where
i.asset_id = c_asset_id and
i.date_effective <= c_to_date and
nvl(i.date_ineffective,
sysdate) > c_to_date
and
v.vendor_id = i.po_vendor_id;
select
distinct ap_i.invoice_num, ap_i.description
from
ap_invoices_all ap_i,
fa_asset_invoices i
where
i.asset_id = c_asset_id and
i.date_effective <= c_to_date and
nvl(i.date_ineffective,
sysdate) > c_to_date
and
ap_i.invoice_id = i.invoice_id;
select
distinct lu.meaning
from
fa_lookups lu,
fa_transaction_headers th,
fa_retirements r
where
r.asset_id = c_asset_id and
r.book_type_code = c_book_type_code and
th.transaction_header_id = r.transaction_header_id_in and
th.date_effective between c_from_date and c_to_date
and
lu.lookup_type = 'RETIREMENT' and
lu.lookup_code = r.retirement_type_code;
select
distinct dh.location_id, dh.code_combination_id
from
fa_distribution_history dh
where
dh.asset_id = c_asset_id and
dh.book_type_code = c_book_type_code and
dh.date_effective <= c_to_date and
nvl(dh.date_ineffective,
sysdate) > c_to_date;
select location_flex_structure
into g_loc_flex_struct
from fa_system_controls;