[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
x_last_updated_by NUMBER(15);
x_last_update_login NUMBER(15);
x_last_updated_by := fnd_global.user_id;
x_last_update_login := fnd_global.login_id;
SELECT a.set_of_books_id ,
a.global_attribute6,
a.gl_je_source,
b.currency_code,
b.chart_of_accounts_id,
a.deprn_calendar,
a.last_period_counter,
a.distribution_source_book
INTO g_set_of_books_id,
g_je_retirement_category,
g_gl_je_source,
g_currency_code,
g_chart_of_accounts_id,
g_calendar_type,
g_last_period_counter,
g_distribution_source_book
FROM fa_book_controls a,
gl_sets_of_books b
WHERE a.book_type_code = p_book_type_code
AND a.set_of_books_id = b.set_of_books_id;
SELECT a.set_of_books_id ,
a.global_attribute6,
b.currency_code,
b.chart_of_accounts_id,
a.deprn_calendar,
a.last_period_counter,
a.distribution_source_book
INTO g_set_of_books_id,
g_je_retirement_category,
g_currency_code,
g_chart_of_accounts_id,
g_calendar_type,
g_last_period_counter,
g_distribution_source_book
FROM fa_book_controls a,
gl_sets_of_books b
WHERE a.book_type_code = p_book_type_code
AND a.set_of_books_id = b.set_of_books_id;
SELECT xs.je_source_name,
js.user_je_source_name
INTO g_gl_je_source,
g_user_je_source_name
FROM gl_je_sources js,
xla_subledgers xs
WHERE js.je_source_name = xs.je_source_name
AND xs.application_id = 140;
SELECT precision
INTO g_curr_precision
FROM fnd_currencies_vl
WHERE UPPER(currency_code) = UPPER(g_currency_code);
SELECT user_je_category_name
INTO g_user_je_category_name
FROM gl_je_categories
WHERE je_category_name = g_je_retirement_category;
SELECT a.calendar_period_open_date,
a.calendar_period_close_date,
a.fiscal_year,
a.period_num,
a.period_counter,
a.period_name
INTO
g_current_period_from_date1,
g_current_period_to_date1,
g_current_fiscal_year,
g_current_month_number,
g_period_counter1,
g_period_name
FROM fa_deprn_periods a
WHERE a.book_type_code = p_book_type_code
AND a.period_counter = g_last_period_counter;
SELECT number_per_fiscal_year
INTO g_number_per_fy
FROM fa_calendar_types
WHERE calendar_type = g_calendar_type;
SELECT period_num
INTO g_period_num
FROM fa_calendar_periods
WHERE calendar_type = g_calendar_type
AND start_date = g_current_period_from_date1;
SELECT start_date,end_date
INTO g_current_period_from_date2,
g_current_period_to_date2
FROM fa_calendar_periods
WHERE calendar_type = g_calendar_type
AND period_num = decode(g_period_num,1,g_number_per_fy,g_period_num-1)
AND end_date = g_current_period_from_date1 - 1;
SELECT TO_CHAR(round(num_amount,g_curr_precision))
INTO char_amount
FROM DUAL;
SELECT TO_CHAR(ROUND(num_amount,g_curr_precision))
INTO tmp_amnt
FROM DUAL;
select rpad(tmp_amnt,
decode(sign(length(tmp_amnt)+g_curr_precision-(length(tmp_amnt) - instr(tmp_amnt,'.')))
,-1,length(tmp_amnt)
,0 ,length(tmp_amnt)
,length(tmp_amnt) + g_curr_precision - (length(tmp_amnt) -
decode(instr(tmp_amnt,'.'),0,1,instr(tmp_amnt,'.')))),'0')
into char_amount
from dual;
SELECT rpad(g_conc_segs,45,' ')
INTO g_conc_segs
FROM DUAL;
SELECT price_index_value
INTO p_index_value
FROM fa_price_index_values
WHERE price_index_id = p_index_id
AND p_period_date BETWEEN from_date AND to_date;
SELECT b.price_index_id
INTO l_price_index
FROM fa_category_book_defaults a, fa_price_indexes b
WHERE a.book_type_code = p_book_type_code
AND a.category_id = p_current_category
AND p_date_placed_in_service >= a.start_dpis
AND p_date_placed_in_service <= NVL(a.end_dpis,p_date_placed_in_service)
AND a.price_index_name = b.price_index_name;
SELECT a.retirement_id,a.asset_id asset_id,
b.transaction_header_id transaction_header_id,
rpad(c.asset_number||'-'||substr(c.description,1,30),45,' ') asset_desc
FROM fa_books d,
fa_additions c,
fa_transaction_headers b,
fa_retirements a
WHERE a.book_type_code = p_book_type_code
AND a.transaction_header_id_in = b.transaction_header_id
AND b.transaction_date_entered BETWEEN g_current_period_from_date1
AND g_current_period_to_date1
AND b.transaction_type_code = 'FULL RETIREMENT'
AND c.asset_id = a.asset_id
AND c.asset_type <> 'CIP'
AND d.book_type_code = a.book_type_code
AND d.asset_id = a.asset_id
AND d.date_ineffective IS NULL
AND NVL(d.global_attribute1,'N') = 'Y'
ORDER BY c.asset_number;
SELECT distribution_id,code_combination_id
FROM fa_distribution_history
WHERE book_type_code = g_distribution_source_book
AND asset_id = l_asset_id
AND transaction_header_id_out is null;
SELECT sum(ytd_deprn),sum(deprn_amount)
INTO l_accum_deprn,l_deprn_amount
FROM fa_deprn_detail
WHERE book_type_code = p_book_type_code
AND asset_id = fa_ret_rec.asset_id
AND period_counter = g_period_counter1
AND distribution_id = fa_cat_rec.distribution_id;
INSERT INTO jl_zz_fa_retiremnt_adjs
(retirement_id,
period_counter,
distribution_id,
book_type_code,
asset_id,
transaction_header_id,
je_line_id,
original_ytd_depreciation,
total_adjustment_amount,
period_adjustment_amount,
status,
retire_reinst_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(fa_ret_rec.retirement_id,
g_period_counter1,
fa_cat_rec.distribution_id,
p_book_type_code,
fa_ret_rec.asset_id,
fa_ret_rec.transaction_header_id,
null,
round(l_accum_deprn,g_curr_precision),
round(l_accum_deprn,g_curr_precision),
0,
'Y',
'RET',
x_sysdate,
x_last_updated_by,
x_sysdate,
x_created_by,
x_last_update_login);
SELECT a.retirement_id retirement_id,a.asset_id asset_id,
b.transaction_header_id transaction_header_id,
rpad(c.asset_number||'-'||substr(c.description,1,30),45,' ') asset_desc
FROM fa_books d,
fa_additions c,
fa_transaction_headers b,
fa_retirements a
WHERE a.book_type_code = p_book_type_code
AND a.transaction_header_id_out = b.transaction_header_id
AND b.transaction_type_code = 'REINSTATEMENT'
AND b.transaction_date_entered BETWEEN g_current_period_from_date1
AND g_current_period_to_date1
AND c.asset_id = a.asset_id
AND d.book_type_code = a.book_type_code
AND d.asset_id = a.asset_id
AND d.date_ineffective IS NULL
AND NVL(d.global_attribute1,'N') = 'Y'
ORDER BY c.asset_number;
SELECT b.distribution_id,b.code_combination_id
FROM fa_distribution_history b, fa_distribution_history a
WHERE a.book_type_code = g_distribution_source_book
AND a.asset_id = l_asset_id
AND a.transaction_header_id_out is null
AND b.book_type_code = a.book_type_code
AND b.asset_id = a.asset_id
AND b.transaction_header_id_out = a.transaction_header_id_in;
SELECT nvl(sum(period_adjustment_amount) * -1,0),
max(original_ytd_depreciation) ,
max(total_adjustment_amount) - sum(period_adjustment_amount)
INTO l_deprn_amount,l_ytd_deprn,l_total_amount
FROM jl_zz_fa_retiremnt_adjs
WHERE retirement_id = fa_ret_rec.retirement_id
AND distribution_id = fa_cat_rec.distribution_id;
INSERT INTO jl_zz_fa_retiremnt_adjs
(retirement_id, period_counter, distribution_id,
book_type_code, asset_id, transaction_header_id,
je_line_id, original_ytd_depreciation, total_adjustment_amount,
period_adjustment_amount, status, retire_reinst_flag,
last_update_date, last_updated_by, creation_date,
created_by, last_update_login)
VALUES
(fa_ret_rec.retirement_id,
g_period_counter1,
fa_cat_rec.distribution_id,
p_book_type_code,
fa_ret_rec.asset_id,
fa_ret_rec.transaction_header_id,
null,
round(l_ytd_deprn,g_curr_precision),
round(l_total_amount,g_curr_precision),
round(l_deprn_amount,g_curr_precision),
'N',
'REI',
x_sysdate,
x_last_updated_by,
x_sysdate,
x_created_by,
x_last_update_login);
SELECT a.retirement_id retirement_id, a.asset_id asset_id,
a.distribution_id distribution_id,
a.transaction_header_id transaction_header_id,
a.original_ytd_depreciation orig_deprn,
a.total_adjustment_amount accum_deprn,
a.period_adjustment_amount adjst_amount,
b.asset_category_id asset_category,
rpad(b.asset_number||'-'||substr(b.description,1,30),45,' ') asset_desc
FROM jl_zz_fa_retiremnt_adjs a,
fa_additions b
WHERE a.book_type_code = p_book_type_code
AND a.period_counter = g_previous_period_counter
AND a.asset_id = b.asset_id
AND NOT EXISTS (SELECT 1
FROM jl_zz_fa_retiremnt_adjs c
WHERE c.retirement_id = a.retirement_id
AND c.period_counter = g_period_counter1
AND c.retire_reinst_flag = 'REI')
GROUP BY b.asset_category_id,
rpad(b.asset_number||'-'||substr(b.description,1,30),45,' '),
a.retirement_id,
a.asset_id , a.distribution_id,
a.transaction_header_id,
a.original_ytd_depreciation,
a.total_adjustment_amount,
a.period_adjustment_amount;
SELECT b.date_placed_in_service
INTO cur_date_placed_in_service
FROM fa_books b
WHERE b.book_type_code = p_book_type_code
AND b.asset_id = fa_adjst_rec.asset_id
AND b.retirement_id = fa_adjst_rec.retirement_id;
INSERT INTO jl_zz_fa_retiremnt_adjs
(retirement_id,
period_counter,
distribution_id,
book_type_code,
asset_id,
transaction_header_id,
je_line_id,
original_ytd_depreciation,
total_adjustment_amount,
period_adjustment_amount,
status,
retire_reinst_flag,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login)
VALUES
(fa_adjst_rec.retirement_id,
g_period_counter1,
fa_adjst_rec.distribution_id,
p_book_type_code,
fa_adjst_rec.asset_id,
fa_adjst_rec.transaction_header_id,
null,
round(fa_adjst_rec.orig_deprn,g_curr_precision),
round(l_adj_accum_deprn,g_curr_precision),
round(l_period_adj_amount,g_curr_precision),
'N',
'INF',
x_sysdate,
x_last_updated_by,
x_sysdate,
x_created_by,
x_last_update_login);
SELECT code_combination_id
INTO l_dist_ccid
FROM fa_distribution_history
WHERE distribution_id = fa_adjst_rec.distribution_id;
PROCEDURE insert_retiremnt_jes IS
CURSOR fa_adjst IS
SELECT nvl(sum(a.period_adjustment_amount),0) adjst_amount,
b.code_combination_id ccid, 1 ident
FROM fa_distribution_history b,
jl_zz_fa_retiremnt_adjs a
WHERE a.book_type_code = p_book_type_code
AND a.period_counter = g_period_counter1
AND a.status = 'N'
AND a.distribution_id = b.distribution_id
GROUP BY b.code_combination_id,1
UNION
SELECT nvl(sum(a.period_adjustment_amount),0) adjst_amount,
c.reval_reserve_account_ccid ccid, 2 ident
FROM fa_category_books c,
fa_additions b,
jl_zz_fa_retiremnt_adjs a
WHERE a.book_type_code = p_book_type_code
AND a.period_counter = g_period_counter1
AND a.status = 'N'
AND b.asset_id = a.asset_id
AND c.book_type_code = p_book_type_code
AND c.category_id = b.asset_category_id
GROUP BY c.reval_reserve_account_ccid,2;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_RETIREMENT_JES';
x_char := 'Procedure Insert_retiremnt_jes';
INSERT INTO jl_zz_fa_retiremnt_jes
(je_line_id, book_type_code, period_counter,
code_combination_id, set_of_books_id, request_id,
currency_code, adjustment_amount, debit_credit_flag,
posting_flag, last_update_date, last_updated_by,
creation_date, created_by, last_update_login)
VALUES (
jl_zz_fa_retiremnt_jes_s.nextval,
p_book_type_code,
g_period_counter1,
fa_adjst_rec.ccid,
g_set_of_books_id,
x_request_id,
g_currency_code,
ABS(fa_adjst_rec.adjst_amount),
decode (fa_adjst_rec.ident,1,decode(sign(fa_adjst_rec.adjst_amount),-1,'CR','DR'),
2,decode(sign(fa_adjst_rec.adjst_amount),-1,'DR','CR')),
null,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login);
UPDATE jl_zz_fa_retiremnt_adjs
SET je_line_id = jl_zz_fa_retiremnt_jes_s.currval,
status = 'Y'
WHERE rowid in (
SELECT a.rowid
FROM fa_distribution_history b,
jl_zz_fa_retiremnt_adjs a
WHERE a.book_type_code = p_book_type_code
AND a.period_counter = g_period_counter1
AND a.status = 'N'
AND a.distribution_id = b.distribution_id
AND b.code_combination_id = fa_adjst_rec.ccid);
x_char := 'End of Procedure insert_retiremnt_jes';
END insert_retiremnt_jes;
PROCEDURE insert_gl_interface IS
--
CURSOR jes_lines IS
SELECT set_of_books_id,currency_code,adjustment_amount,
code_combination_id,je_line_id,debit_credit_flag flag
FROM jl_zz_fa_retiremnt_jes
WHERE request_id = x_request_id
FOR UPDATE OF posting_flag;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_GL_INTERFACE';
x_char := 'Procedure insert_gl_interface';
SELECT gl_interface_control_s.nextval
INTO l_group_id
FROM sys.dual;
INSERT INTO gl_interface (
status,
set_of_books_id,
accounting_date,
currency_code,
date_created,
created_by,
actual_flag,
user_je_category_name,
user_je_source_name,
entered_dr,
entered_cr,
period_name,
code_combination_id,
reference25,
group_id)
VALUES (
'NEW',
jes_lin_rec.set_of_books_id,
g_current_period_to_date1,
jes_lin_rec.currency_code,
x_sysdate,
x_last_updated_by,
'A',
g_user_je_category_name,
g_user_je_source_name,
decode (jes_lin_rec.flag,'DR',jes_lin_rec.adjustment_amount,'0'),
decode (jes_lin_rec.flag,'CR',jes_lin_rec.adjustment_amount,'0'),
G_PERIOD_name,
jes_lin_rec.code_combination_id,
jes_lin_rec.je_line_id,
l_group_id);
UPDATE jl_zz_fa_retiremnt_jes
SET posting_flag = 'Y'
WHERE request_id = x_request_id;
x_char := 'End of procedure insert_gl_interface';
END insert_gl_interface;
insert_retiremnt_jes;
insert_gl_interface;