[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);
PROCEDURE insert_row( p_adjustment_type VARCHAR2,
p_debit_credit_flag VARCHAR2,
p_code_combination_id NUMBER,
p_book_type_code VARCHAR2,
p_asset_id NUMBER,
p_adjustment_amount NUMBER,
p_period_counter NUMBER,
p_distribution_id fa_distribution_history.distribution_id%TYPE,
p_je_category_name VARCHAR2,
p_reference VARCHAR2);
SELECT appraisal_id,
currency_code,
appraisal_date,
fiscal_year
FROM jl_co_fa_appraisals
WHERE appraisal_id = p_appraisal_id
FOR UPDATE OF appraisal_status;
SELECT ap.asset_number,
ap.appraisal_value,
ad.asset_category_id,
ad.asset_id,
ad.current_units,
ab.cost,
fnd_number.canonical_to_number(nvl(ab.global_attribute2,0)) prev_revaluation,
dr.deprn_reserve,
ab.date_placed_in_service
FROM jl_co_fa_asset_apprs ap,
fa_additions ad,
fa_books ab,
fa_deprn_summary dr
WHERE ap.asset_number = ad.asset_number
AND ap.appraisal_id = p_appr_id
AND ad.asset_id = ab.asset_id
AND ab.book_type_code = p_book
AND dr.book_type_code (+) = p_book
AND dr.asset_id (+) = ad.asset_id
AND dr.period_counter (+) = p_period_counter
AND ab.transaction_header_id_out IS NULL
AND ab.date_ineffective IS NULL
ORDER BY ad.asset_category_id
FOR UPDATE OF ap.status;
SELECT distribution_id,
units_assigned,
code_combination_id,
transaction_units
FROM fa_distribution_history
WHERE book_type_code = p_book_type
AND transaction_header_id_out IS NULL
AND date_ineffective IS NULL
AND asset_id = p_asset_id;
SELECT bc.set_of_books_id,
bc.deprn_calendar,
bc.book_class,
bc.gl_posting_allowed_flag,
bc.current_fiscal_year,
bc.accounting_flex_structure,
bc.gl_je_source,
bc.distribution_source_book,
bc.last_period_counter,
bc.deprn_status,
bc.global_attribute13,
sb.currency_code,
js.user_je_source_name,
dp.period_name,
cp.end_date
INTO x_set_of_book_id,
x_deprn_calendar,
x_book_class,
x_gl_posting_allowed_flag,
x_current_fiscal_year,
x_accounting_flex_structure,
x_gl_je_source,
x_distribution_source_book,
x_period_counter,
x_deprn_status,
x_je_category_name,
x_currency_code,
x_user_je_source_name,
x_period_name,
x_end_date
FROM fa_book_controls bc,
gl_sets_of_books sb,
gl_je_sources js,
fa_deprn_periods dp,
fa_calendar_periods cp
WHERE bc.book_type_code = p_book
AND sb.set_of_books_id = bc.set_of_books_id
AND js.je_source_name = bc.gl_je_source
AND dp.book_type_code = p_book
AND dp.period_counter = bc.last_period_counter
AND cp.calendar_type = bc.deprn_calendar
AND cp.period_name = dp.period_name;
SELECT bc.set_of_books_id,
bc.deprn_calendar,
bc.book_class,
bc.gl_posting_allowed_flag,
bc.current_fiscal_year,
bc.accounting_flex_structure,
bc.distribution_source_book,
bc.last_period_counter,
bc.deprn_status,
bc.global_attribute13,
sb.currency_code,
dp.period_name,
cp.end_date
INTO x_set_of_book_id,
x_deprn_calendar,
x_book_class,
x_gl_posting_allowed_flag,
x_current_fiscal_year,
x_accounting_flex_structure,
x_distribution_source_book,
x_period_counter,
x_deprn_status,
x_je_category_name,
x_currency_code,
x_period_name,
x_end_date
FROM fa_book_controls bc,
gl_sets_of_books sb,
fa_deprn_periods dp,
fa_calendar_periods cp
WHERE bc.book_type_code = p_book
AND sb.set_of_books_id = bc.set_of_books_id
AND dp.book_type_code = p_book
AND dp.period_counter = bc.last_period_counter
AND cp.calendar_type = bc.deprn_calendar
AND cp.period_name = dp.period_name;
SELECT xs.je_source_name,
js.user_je_source_name
INTO x_gl_je_source,
x_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 count(*)
INTO x_count1
FROM jl_co_fa_adjustments
WHERE book_type_code = p_book
AND reference = rec_appraisal.appraisal_id
AND rownum < 2; */
SELECT asset_cost_account_ccid,
asset_cost_acct,
NVL(global_attribute11, 0),
NVL(global_attribute12, 0),
NVL(global_attribute13, 0),
NVL(global_attribute14, 0),
NVL(global_attribute15, 0)
INTO x_asset_cost_account_ccid,
x_asset_cost_acct_segval,
x_revaluation_account,
x_surplus_account,
x_reserve_account,
x_expense_account,
x_recovery_account
FROM fa_category_books
WHERE category_id = x_category_id
AND book_type_code = p_book;
x_statement := 'INSERT_ADJ';
insert_row( p_adjustment_type => 'APPR_REVAL',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_SURPL',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation * (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_SURPL',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_REVAL',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_prev_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV_REC',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_prev_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_REVAL',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_appr_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_SURPL',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_appr_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_SURPL',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_prev_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_REVAL',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_prev_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV_EXP',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_appr_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_appr_revaluation * (rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV_REC',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV_EXP',
p_debit_credit_flag => 'DR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
insert_row( p_adjustment_type => 'APPR_RESRV',
p_debit_credit_flag => 'CR',
p_code_combination_id => x_ccid,
p_book_type_code => p_book,
p_asset_id => rec_asset.asset_id,
p_adjustment_amount => ROUND((x_net_revaluation *
(rec_dist.units_assigned/rec_asset.current_units)), x_precision),
p_period_counter => x_period_counter,
p_distribution_id => rec_dist.distribution_id,
p_je_category_name => x_je_category_name,
p_reference => to_char(rec_appraisal.appraisal_id));
UPDATE jl_co_fa_asset_apprs SET status = 'P',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
WHERE CURRENT OF c_asset;
fnd_file.put_line( 1, 'Updated JL_CO_FA_ASSET_APPRS');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_ASSET_APPRS');
UPDATE fa_books SET
global_attribute2 = fnd_number.number_to_canonical(x_appr_revaluation),
global_attribute3 = fnd_number.number_to_canonical(x_prev_revaluation),
global_attribute4 = rec_appraisal.appraisal_id,
global_attribute5 = fnd_date.date_to_canonical(rec_appraisal.appraisal_date),
global_attribute6 = fnd_number.number_to_canonical(rec_asset.appraisal_value),
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
WHERE book_type_code = p_book
AND asset_id = rec_asset.asset_id
AND transaction_header_id_out IS NULL
AND date_ineffective IS NULL ;
fnd_file.put_line( 1, 'Updated FA_BOOKS');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated FA_BOOKS');
UPDATE jl_co_fa_appraisals SET appraisal_status = 'P',
last_update_date = x_sysdate,
last_updated_by = x_last_updated_by,
last_update_login = x_last_update_login
WHERE CURRENT OF c_appraisal;
fnd_file.put_line( 1, 'Updated JL_CO_FA_APPRAISALS');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Updated JL_CO_FA_APPRAISALS');
INSERT INTO jl_co_fa_appraisal_books (appraisal_id,
book_type_code,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES (rec_appraisal.appraisal_id,
p_book,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
SELECT LTRIM(RTRIM(segment1))||LTRIM(RTRIM(segment2))||
LTRIM(RTRIM(segment3))||LTRIM(RTRIM(segment4))||
LTRIM(RTRIM(segment5))||LTRIM(RTRIM(segment6))||
LTRIM(RTRIM(segment7))
INTO x_category
FROM fa_categories
WHERE category_id = x_category_id;
SELECT LTRIM(RTRIM(segment1))||LTRIM(RTRIM(segment2))||
LTRIM(RTRIM(segment3))||LTRIM(RTRIM(segment4))||
LTRIM(RTRIM(segment5))||LTRIM(RTRIM(segment6))||
LTRIM(RTRIM(segment7))
INTO x_category
FROM fa_categories
WHERE category_id = x_category_id;
ELSIF x_statement = 'INSERT_ADJ' THEN
fnd_message.set_name('JL', 'JL_ZZ_FA_EXEC_FAILURE');
PROCEDURE insert_row(
p_adjustment_type VARCHAR2,
p_debit_credit_flag VARCHAR2,
p_code_combination_id NUMBER,
p_book_type_code VARCHAR2,
p_asset_id NUMBER,
p_adjustment_amount NUMBER,
p_period_counter NUMBER,
p_distribution_id fa_distribution_history.distribution_id%TYPE,
P_je_category_name VARCHAR2,
p_reference VARCHAR2) IS
x_period_counter NUMBER;
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_ROW';
SELECT period_counter
INTO x_period_counter
FROM fa_deprn_periods
WHERE book_type_code = p_book_type_code
AND period_close_date IS NULL;
fnd_file.put_line( 1, 'Inserting Row into JL_CO_FA_ADJUSTMENTS');
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserting Row into JL_CO_FA_ADJUSTMENTS');
INSERT INTO jl_co_fa_adjustments(
source_type_code,
je_category_name,
adjustment_type,
debit_credit_flag,
code_combination_id,
book_type_code,
asset_id,
adjustment_amount,
distribution_id,
period_counter_adjusted,
period_counter_created,
reference,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date)
VALUES(
'TECH_APPR_REVAL',
p_je_category_name,
p_adjustment_type,
p_debit_credit_flag,
p_code_combination_id,
p_book_type_code,
p_asset_id,
ABS(p_adjustment_amount),
p_distribution_id,
x_period_counter,
x_period_counter,
p_reference,
x_sysdate,
x_last_updated_by,
x_sysdate,
x_last_updated_by,
x_last_update_login,
x_request_id,
x_program_application_id,
x_program_id,
x_sysdate);
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'Inserted Row into JL_CO_FA_ADJUSTMENTS');
END insert_row;
x_last_updated_by := fnd_global.user_id;
x_last_update_login := fnd_global.login_id;
fnd_file.put_line( 1, 'last_update_login:'||to_char(x_last_update_login));
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_update_login:'||to_char(x_last_update_login));
fnd_file.put_line( 1, 'last_updated_by:'||to_char(x_last_updated_by));
FND_LOG.STRING(G_LEVEL_STATEMENT,G_MODULE_NAME||l_api_name, 'last_updated_by:'||to_char(x_last_updated_by));
SELECT asset_number
FROM jl_co_fa_asset_apprs
WHERE appraisal_id = p_appraisal_id
AND asset_number NOT IN (SELECT ap.asset_number
FROM jl_co_fa_asset_apprs ap,
fa_additions ad,
fa_books ab
WHERE ap.asset_number = ad.asset_number
AND ap.appraisal_id = p_appraisal_id
AND ad.asset_id = ab.asset_id
AND ab.book_type_code = p_book_type_code
AND ab.transaction_header_id_out IS NULL
AND ab.date_ineffective IS NULL );