[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_Status(x_projection_id igi_iac_projections.projection_id%TYPE,
x_status igi_iac_projections.status%TYPE
)
IS
BEGIN
UPDATE igi_iac_projections
SET status = x_status
WHERE projection_id = x_projection_id;
END Update_Status;
SELECT count(*)
INTO l_exists
FROM fa_books
WHERE depreciate_flag = 'NO'
AND transaction_header_id_out IS NULL
AND book_type_code = x_book_code
AND asset_id = x_asset_id;
SELECT report_id
INTO l_report_id
FROM fa_rx_reports r,
fnd_concurrent_programs c,
fnd_application a
WHERE r.application_id = a.application_id
AND r.application_id = c.application_id
AND r.concurrent_program_id = c.concurrent_program_id
AND a.application_short_name = 'IGI'
AND c.concurrent_program_name = 'RXIGIIAP';
SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code = n_book_type_code;
SELECT current_price_index_value
FROM igi_iac_cal_idx_values
WHERE date_from = n_start_date
AND date_to = n_end_date
AND cal_price_index_link_id = (SELECT cal_price_index_link_id
FROM igi_iac_cal_price_indexes
WHERE calendar_type= n_calendar_type
AND price_index_id = (SELECT price_index_id
FROM igi_iac_category_books
WHERE book_type_code = n_book_code
AND category_id= n_category_id));
SELECT MAX(irr.period_counter) period_counter
FROM igi_iac_revaluation_rates irr
WHERE irr.book_type_code = n_book_code
AND asset_id = n_asset_id
AND irr.adjustment_id = (SELECT MAX(adjustment_id)
FROM igi_iac_transaction_headers
WHERE book_type_code = n_book_code
AND asset_id = n_asset_id
AND transaction_type_code = 'REVALUATION'
AND adjustment_status<>'PREVIEW');
SELECT date_placed_in_service
FROM fa_books
WHERE book_type_code = n_book_code
AND asset_id = n_asset_id
AND date_ineffective IS NULL;
SELECT number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = n_calendar;
SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code = n_book_code;
SELECT date_placed_in_service,
life_in_months
FROM fa_books
WHERE book_type_code = n_book_code
AND date_ineffective is NULL -- Bug 5850597
AND asset_id = n_asset_id;
SELECT number_per_fiscal_year
FROM fa_calendar_types
WHERE calendar_type = (SELECT deprn_calendar
FROM fa_book_controls
WHERE book_type_code = n_book_code);
SELECT book_type_code,
start_period_counter,
end_period,
category_id,
revaluation_period,
status
FROM igi_iac_projections
WHERE projection_id = n_projection_id;
SELECT DISTINCT fh.asset_id asset_id
FROM fa_books fb,
fa_additions fh,
igi_iac_category_books fcb
WHERE fb.book_type_code = n_book_code
AND fb.book_type_code = fcb.book_type_code
AND fcb.category_id = fh.asset_category_id
AND fh.asset_category_id = n_category_id
AND fb.asset_id=fh.asset_id
AND fb.period_counter_fully_retired IS NULL
AND fh.asset_type <> 'CIP'
-- AND fb.asset_id IN (SELECT asset_id
-- FROM fa_deprn_summary
-- WHERE book_type_code = n_book_code
-- AND period_counter = n_period_counter - 1
-- AND deprn_source_code <> 'BOOKS')
ORDER BY fh.asset_id;
SELECT DISTINCT fh.asset_id asset_id
FROM fa_books fb,
fa_additions fh,
igi_iac_category_books fcb
WHERE fb.book_type_code = n_book_code
AND fb.book_type_code = fcb.book_type_code
AND fcb.category_id = fh.asset_category_id
AND fb.asset_id=fh.asset_id
AND fb.period_counter_fully_retired IS NULL
AND fh.asset_type <> 'CIP'
-- AND fb.asset_id IN (SELECT asset_id
-- FROM fa_deprn_summary
-- WHERE book_type_code = n_book_code
-- AND period_counter = n_period_counter - 1
-- AND deprn_source_code <> 'BOOKS')
ORDER BY fh.asset_id;
SELECT ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter,
sum(nvl(id.adjustment_cost,0) + nvl(dd.cost,0)) adjusted_cost,
sum(nvl(id.Deprn_Period+dd.deprn_amount-dd.deprn_adjustment_amount, 0)) deprn_period,
sum(nvl(id.Deprn_YTD+ifd.deprn_ytd, 0)) deprn_ytd,
'IAC' source_type
FROM fa_additions ad ,
fa_Books bk ,
fa_distribution_history dh,
fa_deprn_Detail dd ,
igi_iac_det_balances id ,
igi_iac_fa_deprn ifd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah
WHERE ad.asset_id = dh.asset_id
AND cf.category_id = ah.category_id
AND bk.book_Type_code = n_book_type_code
AND ad.asset_id = n_asset_id
AND dh.book_type_Code = bk.book_type_code
AND dh.book_type_code = dd.book_type_code
AND dh.asset_id = dd.asset_id
AND dh.distribution_id = dd.distribution_id
AND dh.asset_id = ah.asset_id
AND bk.depreciate_flag <> 'NO'
AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
AND dd.period_counter = (SELECT period_counter - 1
FROM fa_deprn_periods
WHERE book_type_code = n_book_type_code
AND period_close_date IS NULL)
AND bk.date_ineffective IS NULL
AND dh.distribution_id = id.distribution_id
AND dh.code_Combination_id = cc.code_combination_id
AND id.adjustment_id = ifd.adjustment_id
AND id.distribution_id = ifd.distribution_id
AND id.period_counter = ifd.period_counter
AND id.adjustment_id = ( SELECT max(adjustment_id)
FROM igi_iac_transaction_headers it
WHERE it.asset_id = bk.asset_id
AND it.book_type_code = bk.book_type_code
AND it.period_counter = dd.period_counter
AND it.adjustment_status not in( 'PREVIEW', 'OBSOLETE'))
GROUP BY ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter
UNION
SELECT ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter,
sum(nvl(dd.cost,0)) adjusted_cost,
sum(nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0)) deprn_period,
sum(nvl(dd.ytd_deprn,0)) deprn_ytd,
'FA' source_type
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ad.asset_type <> 'CIP'
AND cf.category_id = ah.category_id
AND bk.transaction_header_id_out is NULL
AND bk.book_type_code = n_book_type_code
AND dd.asset_id = n_asset_id
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND dh.transaction_header_id_out is NULL
AND dh.code_combination_id = cc.code_combination_id
AND dh.asset_id = ah.asset_id
AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
AND dd.period_counter = (SELECT period_counter -1
FROM fa_deprn_periods
WHERE book_type_code = n_book_type_code
AND period_close_date IS NULL)
AND bk.asset_id NOT IN
(SELECT asset_id
FROM igi_iac_asset_balances
WHERE book_type_code = bk.book_type_code
AND asset_id = bk.asset_id)
AND bk.depreciate_flag <> 'NO'
GROUP BY ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter
UNION
SELECT ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter,
sum(nvl(dd.cost,0)) adjusted_cost,
0 deprn_period,
0 deprn_ytd,
'NONDEPFA' source_type
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND cf.category_id = ah.category_id
AND ad.asset_type <> 'CIP'
AND bk.transaction_header_id_out is NULL
AND bk.book_type_code = n_book_type_code
AND dd.asset_id = n_asset_id
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND dh.transaction_header_id_out is NULL
AND dh.code_combination_id = cc.code_combination_id
AND dh.asset_id = ah.asset_id
AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
AND dd.period_counter = (SELECT max(period_counter)
FROM fa_deprn_detail
WHERE book_type_code = n_book_type_code
AND asset_id = n_asset_id)
AND bk.depreciate_flag = 'NO'
GROUP BY ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter
UNION
SELECT ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter,
sum(nvl(dd.cost,0)) adjusted_cost,
sum(nvl(dd.deprn_amount,0)-nvl(dd.deprn_adjustment_amount,0)) deprn_period,
sum(nvl(dd.ytd_deprn,0)) deprn_ytd,
'FULLRSVDFA' source_type
FROM fa_additions ad,
fa_Books bk,
fa_distribution_history dh,
fa_deprn_Detail dd,
gl_code_combinations cc,
fa_categories cf,
fa_asset_history ah
WHERE ad.asset_id = bk.asset_id
AND ad.asset_type <> 'CIP'
AND cf.category_id = ah.category_id
AND bk.transaction_header_id_out is NULL
AND bk.book_type_code = n_book_type_code
AND dd.asset_id = n_asset_id
AND dd.asset_id = bk.asset_id
AND dd.book_type_code = bk.book_type_code
AND dh.distribution_id = dd.distribution_id
AND dh.transaction_header_id_out is NULL
AND dh.code_combination_id = cc.code_combination_id
AND dh.asset_id = ah.asset_id
AND nvl(dh.date_ineffective,sysdate+1) > ah.date_effective
AND nvl(dh.date_ineffective,sysdate+1) <= nvl(ah.date_ineffective,sysdate+1)
AND dd.period_counter = (SELECT period_counter_fully_reserved
FROM fa_books
WHERE book_type_code = n_book_type_code
AND asset_id = n_asset_id
AND date_ineffective IS NULL
AND transaction_header_id_out IS NULL)
GROUP BY ad.asset_id,
dh.code_combination_id,
ah.category_id,
dd.period_counter;
SELECT NVL(SUM(NVL(dd.adjustment_cost,0)),0)
FROM igi_iac_det_balances dd,
fa_books fb
WHERE dd.book_type_code = fb.book_type_code
AND dd.asset_id = fb.asset_id
AND dd.book_type_code = n_book_code
AND dd.asset_id = n_asset_id
AND fb.transaction_header_id_out IS NULL
AND fb.depreciate_flag = 'NO'
AND dd.adjustment_id = (SELECT max(ith.adjustment_id)
FROM igi_iac_transaction_headers ith
WHERE ith.book_type_code = n_book_code
AND ith.asset_id = n_Asset_id
AND ith.adjustment_status NOT IN( 'PREVIEW', 'OBSOLETE'));
SELECT NVL(SUM(NVL(dd.adjustment_cost,0)),0) adjustment_cost,
NVL(sum(nvl(dd.Deprn_Period, 0)),0) deprn_period,
NVL(sum(nvl(dd.Deprn_YTD+ifd.deprn_ytd, 0)),0) deprn_ytd
FROM igi_iac_det_balances dd,
igi_iac_fa_deprn ifd,
fa_books fb
WHERE dd.book_type_code = fb.book_type_code
AND dd.asset_id = fb.asset_id
AND dd.distribution_id = ifd.distribution_id
AND dd.adjustment_id = ifd.adjustment_id
AND dd.asset_id = ifd.asset_id
AND dd.book_type_code = n_book_code
AND dd.asset_id = n_asset_id
AND fb.transaction_header_id_out IS NULL
AND fb.period_counter_fully_reserved IS NOT NULL
AND dd.adjustment_id = (SELECT max(ith.adjustment_id)
FROM igi_iac_transaction_headers ith
WHERE ith.book_type_code = n_book_code
AND ith.asset_id = n_Asset_id
AND ith.adjustment_status NOT IN( 'PREVIEW', 'OBSOLETE'));
SELECT period_counter_fully_reserved
FROM fa_books where book_type_code = n_book_code
AND asset_id = n_asset_id;
SELECT date_placed_in_service
FROM fa_books
WHERE asset_id = n_asset_id
AND book_type_code = n_book_type_code
AND date_ineffective IS NULL;
SELECT category_flex_structure
INTO l_cat_struct
FROM fa_system_controls;
SELECT set_of_books_id
INTO l_sob_id
FROM fa_book_controls
WHERE book_type_code =l_book_type_code;
igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Before insertion into igi_iac_proj_details');
INSERT INTO igi_iac_proj_details(
projection_id,
period_counter,
category_id,
fiscal_year,
company,
cost_center,
asset_id,
latest_reval_cost,
deprn_period,
deprn_ytd,
asset_exception,
created_by,
creation_date,
last_updated_by,
last_update_date,
last_update_login
) VALUES (
l_proj_rec.projection_id(l_count),
l_proj_rec.period_counter(l_count),
l_proj_rec.category_id(l_count),
l_proj_rec.fiscal_year(l_count),
l_proj_rec.company(l_count),
l_proj_rec.cost_center(l_count),
l_proj_rec.asset_id(l_count),
l_proj_rec.latest_reval_cost(l_count),
l_proj_rec.deprn_period(l_count),
l_proj_rec.deprn_ytd(l_count),
l_proj_rec.asset_exception(l_count),
l_user_id,
sysdate,
l_user_id,
sysdate,
l_login_id);
igi_iac_debug_pkg.debug_other_string(g_state_level,g_path||'Do_Proj_Calc' ,'Insert complete');
Update_Status(p_projection_id, 'ERROR');
Update_Status(p_projection_id, 'ERROR');
Update_Status(p_projection_id, 'ERROR');
Update_Status(p_projection_id, 'ERROR');
igi_iac_debug_pkg.debug_other_string(g_error_level,g_path||'Do_Proj_Calc' ,'No assets have been selected for Projections. Hence, report not submitted');
FND_MESSAGE.SET_TOKEN('ERROR_MESSAGE','No assets have been selected for Projections. Hence, report not submitted');
Update_Status(p_projection_id, 'ERROR');
PROCEDURE Delete_Projections(
p_from_projection IN igi_iac_projections.projection_id%TYPE,
p_to_projection IN igi_iac_projections.projection_id%TYPE
) IS
BEGIN
DELETE FROM igi_iac_proj_details
WHERE projection_id BETWEEN p_from_projection AND p_to_projection;
DELETE FROM igi_iac_projections
WHERE projection_id BETWEEN p_from_projection AND p_to_projection;
DELETE FROM igi_iac_proj_rep_itf
WHERE projection_id BETWEEN p_from_projection AND p_to_projection;
END Delete_Projections;