[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE insert_fa_whatif_itf (
x_errbuf OUT NOCOPY VARCHAR2
, x_retcode OUT NOCOPY NUMBER
, p_request_id IN NUMBER
, p_checkbox_check IN VARCHAR2
, p_book_type_code IN fa_books.book_type_code%TYPE
, p_number_of_periods IN NUMBER
, p_asset_id IN fa_books.asset_id%TYPE
, p_period_name IN fa_deprn_periods.period_name%TYPE
, p_first_begin_prd IN fa_deprn_periods.period_name%TYPE
, p_dep_amt IN NUMBER
, p_dep_amt_annual IN NUMBER
, p_date_placed_in_service IN fa_books.date_placed_in_service%TYPE
, p_life_in_months IN fa_books.life_in_months%TYPE
, p_original_cost IN fa_books.original_cost%TYPE
, p_asset_number IN fa_additions_b.asset_number%TYPE
, p_description IN fa_additions.description%TYPE
, p_tag_number IN fa_additions_b.tag_number%TYPE
, p_serial_number IN fa_additions_b.serial_number%TYPE
, p_location IN fa_locations_kfv.concatenated_segments%TYPE
, p_expense_account IN gl_code_combinations_kfv.concatenated_segments%TYPE
, p_round_value IN NUMBER
, p_deprn_value IN NUMBER
, p_flag IN VARCHAR2
)
IS
CURSOR lcr_deprn_periods (
p_book_type_code IN fa_books.book_type_code%TYPE
, p_period_name IN fa_deprn_periods.period_name%TYPE
)
/***********************************************************************
*
* CURSOR
* lcr_deprn_periods
*
* DESCRIPTION
* Cursor lcr_deprn_periods is a private cursor of procedure whatif_main.
* Cursor will return the end_date
*
* PARAMETERS
* ==========
* NAME TYPE DESCRIPTION
* ----------------- -------- ------------------------------------------
* p_book_type_code
* p_period_name
* *
* None
*
* PREREQUISITES
* None
*
* CALLED BY
* load
*
***********************************************************************/
IS
SELECT fcp.end_date
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.period_name = p_period_name;
SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.period_name = p_period_name;
* Cursor lcr_distribution is a private cursor of procedure insert_fa_whatif_itf.
* Cursor will return distribution records
*
* PARAMETERS
* ==========
* NAME TYPE DESCRIPTION
* ----------------- -------- ------------------------------------------
* p_book_type_code
* p_asset_id
*
*
* None
*
* PREREQUISITES
* None
*
* CALLED BY
* insert_fa_whatif_itf
*
***********************************************************************/
IS
SELECT fdh.units_assigned
, papf.full_name
, papf.employee_number
FROM fa_distribution_history fdh
, per_all_people_f papf
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fdh.book_type_code = fbc.distribution_source_book
AND fdh.asset_id = p_asset_id
AND fdh.assigned_to = papf.person_id(+);
* insert_fa_whatif_itf
*
***********************************************************************/
IS
SELECT cp1.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
, fa_calendar_types fc
, fa_calendar_periods cp1
WHERE cp.period_name = p_start_period
AND cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = p_book_type
AND cp.calendar_type = fc.calendar_type
AND cp.start_date >= fy.start_date
AND cp.end_date <= fy.end_date
AND fy.fiscal_year_name = fb.fiscal_year_name
AND cp1.period_num = 1
AND fb.deprn_calendar = cp1.calendar_type
AND cp1.start_date >= cp.start_date
AND ROWNUM = 1;
* Cursor lcr_periods is a private cursor of procedure insert_fa_whatif_itf.
* Cursor will return the Peiord
*
* PARAMETERS
* ==========
* NAME TYPE DESCRIPTION
* ----------------- -------- ------------------------------------------
* p_book_type_code
* p_end_date
*
*
* None
*
* PREREQUISITES
* None
*
* CALLED BY
* insert_fa_whatif_itf
*
***********************************************************************/
IS
SELECT fcp.period_name
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.end_date = p_end_date;
l_whatif_period_tbl2.DELETE;
'SELECT months_between(''' || l_first_period_date
|| ''',''' || l_start_period_date
|| ''') counter
FROM DUAL';
SELECT DECODE (SIGN (l_first_period_date - l_start_period_date)
, -1, l_start_period_date
, l_first_period_date
)
INTO l_last_date
FROM DUAL;
SELECT DECODE (SIGN (l_first_period_date - l_start_period_date)
, -1, l_first_period_date
, l_start_period_date
)
INTO l_start_date
FROM DUAL;
'SELECT FCP.period_name
FROM fa_calendar_periods FCP
, fa_calendar_types FCT
, fa_book_controls FBC
WHERE FBC.book_type_code = '''
|| p_book_type_code
|| '''
AND FCP.calendar_type = FCT.calendar_type
AND FCP.calendar_type = FBC.deprn_calendar
AND FCP.end_date BETWEEN '''
|| l_start_date || ''' AND ''' || l_last_date || '''';
INSERT INTO fa_whatif_itf
(request_id
, book_type_code
, asset_id
, period_name
, depreciation
, new_depreciation
, current_method
, current_cost
, current_life
, units
, employee_name
, employee_number
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, date_placed_in_service
, asset_number
, description
, tag_number
, serial_number
, LOCATION
, expense_acct
)
VALUES (p_request_id
, p_book_type_code
, p_asset_id
, l_whatif_period_tbl (i).period_name
, 0
, 0
, 'STL'
, p_original_cost
, p_life_in_months
, lr_distribution.units_assigned
, lr_distribution.full_name
, lr_distribution.employee_number
, gn_user_id
, SYSDATE
, SYSDATE
, gn_user_id
, gn_login_id
, p_date_placed_in_service
, p_asset_number
, p_description
, p_tag_number
, p_serial_number
, p_location
, p_expense_account
);
UPDATE fa_whatif_itf
SET accumulated_deprn = l_deprn_value
WHERE period_name = l_whatif_period_tbl (i).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id;
UPDATE fa_whatif_itf
SET depreciation = 0
, new_depreciation = 0
, units = lr_distribution.units_assigned
, employee_name = lr_distribution.full_name
, employee_number = lr_distribution.employee_number
, accumulated_deprn = l_deprn_value --p_deprn_value
WHERE period_name = l_whatif_period_tbl (i).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id
AND asset_id = p_asset_id;
UPDATE fa_whatif_itf
SET depreciation = p_round_value*5
, new_depreciation = p_round_value*5
, units = lr_distribution.units_assigned
, employee_name = lr_distribution.full_name
, employee_number = lr_distribution.employee_number
, accumulated_deprn = l_deprn_value - l_round_value
WHERE period_name = l_whatif_period_tbl2(i).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id;
'SELECT FCP.period_name
FROM fa_calendar_periods FCP
, fa_calendar_types FCT
, fa_book_controls FBC
WHERE FBC.book_type_code = '''
|| p_book_type_code
|| '''
AND FCP.calendar_type = FCT.calendar_type
AND FCP.calendar_type = FBC.deprn_calendar
AND FCP.end_date BETWEEN '''
|| l_date || ''' AND ''' || l_end_date || '''';
select round(months_between(l_start_period_date, l_first_period_date),0)
INTO l_count
FROM DUAL;
INSERT INTO fa_whatif_itf
(request_id
, book_type_code
, asset_id
, period_name
, depreciation
, new_depreciation
, current_method
, current_cost
, current_life
, units
, employee_name
, employee_number
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, date_placed_in_service
, asset_number
, description
, tag_number
, serial_number
, LOCATION
, expense_acct
)
VALUES (p_request_id
, p_book_type_code
, p_asset_id
, l_whatif_period_tbl2 (i).period_name
, p_dep_amt
, p_dep_amt
, 'STL'
, p_original_cost
, p_life_in_months
, lr_distribution.units_assigned
, lr_distribution.full_name
, lr_distribution.employee_number
, gn_user_id
, SYSDATE
, SYSDATE
, gn_user_id
, gn_login_id
, p_date_placed_in_service
, p_asset_number
, p_description
, p_tag_number
, p_serial_number
, p_location
, p_expense_account
);
UPDATE fa_whatif_itf
SET accumulated_deprn = l_deprn_value
WHERE period_name = l_whatif_period_tbl2 (i).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id;
UPDATE fa_whatif_itf
SET depreciation = 0
, new_depreciation = 0
, units = lr_distribution.units_assigned
, employee_name = lr_distribution.full_name
, employee_number = lr_distribution.employee_number
, accumulated_deprn = l_deprn_value
WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id
AND asset_id = p_asset_id;
UPDATE fa_whatif_itf
SET depreciation = p_round_value * 5 --l_round_value*5
, new_depreciation = p_round_value * 5
, units = lr_distribution.units_assigned
, employee_name = lr_distribution.full_name
, employee_number = lr_distribution.employee_number
, accumulated_deprn = l_deprn_value - l_round_value
WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id
AND asset_id = p_asset_id;
UPDATE fa_whatif_itf
SET depreciation = p_dep_amt
, new_depreciation = p_dep_amt
, units = lr_distribution.units_assigned
, employee_name = lr_distribution.full_name
, employee_number = lr_distribution.employee_number
, accumulated_deprn = l_deprn_value
WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id
AND asset_id = p_asset_id;
UPDATE fa_whatif_itf
SET depreciation = (FLOOR (p_dep_amt_annual) - p_dep_amt * 11)
, new_depreciation =
(FLOOR (p_dep_amt_annual) - p_dep_amt * 11
)
, units = lr_distribution.units_assigned
, employee_name = lr_distribution.full_name
, employee_number = lr_distribution.employee_number
, accumulated_deprn = l_deprn_value - l_round_value
WHERE period_name = l_whatif_period_tbl2 (i-l_count).period_name
AND book_type_code = p_book_type_code
AND request_id = p_request_id
AND asset_id = p_asset_id;
END insert_fa_whatif_itf;
SELECT user_profile_option_name
FROM fnd_profile_options_vl
WHERE profile_option_name = p_option_name;
SELECT fb.book_type_code
, cp.period_name
, fy.fiscal_year
, fb.asset_id
, fb.last_update_date
, fb.last_updated_by
, fb.last_update_login
, fb.original_cost
, fb.life_in_months
, fb.date_placed_in_service
, fb.period_counter_fully_reserved
, fa.asset_number
, fa.description
, fa.tag_number
, fa.serial_number
, flk.concatenated_segments LOCATION
, gcck.concatenated_segments expense_account
, (fb.allowed_deprn_limit_amount - 1) / 5
- FLOOR ((fb.allowed_deprn_limit_amount - 1) / 5) round_value
-- , CEIL(((FB.cost - FB.salvage_value)*FB.basic_rate)/12) Deprn_Value
, ROUND ((fb.allowed_deprn_limit_amount - 1) / 60) deprn_value
, (fb.allowed_deprn_limit_amount - 1) / 5 annual_deprn_value
FROM fa_books fb
, fa_book_controls fbc
,fa_calendar_periods cp
, fa_fiscal_year fy
, fa_calendar_types fct
, fa_additions fa
, fa_distribution_history fdh
, fa_locations_kfv flk
, gl_code_combinations_kfv gcck
WHERE fa.asset_id = fb.asset_id
AND fb.asset_id = p_asset_id
AND fdh.asset_id = fa.asset_id
-- AND FDH.book_type_code = FB.book_type_code -- As per Bug No .7183390 (For Tax Books)
AND fbc.book_type_code = fb.book_type_code
-- As per Bug No .7183390 (For Tax Books)
AND fbc.distribution_source_book = fdh.book_type_code
-- As per Bug No .7183390 (For Tax Books)
AND fdh.location_id = flk.location_id
AND gcck.code_combination_id = fdh.code_combination_id
AND fb.book_type_code = p_book_type_code
AND cp.calendar_type = fbc.deprn_calendar
AND fy.fiscal_year_name = fbc.fiscal_year_name
AND cp.calendar_type = fct.calendar_type
AND cp.start_date >= fy.start_date
and cp.end_date <= fy.end_date
AND fb.period_counter_fully_reserved = (fy.fiscal_year * fct.number_per_fiscal_year + cp.period_num)
AND fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
AND fdh.transaction_header_id_out IS NULL
-- As per Bug No .7183390 (For Tax Books)
AND fb.period_counter_fully_reserved IS NOT NULL
AND fb.deprn_method_code <> 'JP-STL-EXTND'
AND fb.allowed_deprn_limit_amount > 1;
SELECT fdp.period_counter
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = p_book_type_code
AND fdp.period_name = p_period_name;
SELECT cp1.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
, fa_calendar_types fc
, fa_calendar_periods cp1
WHERE cp.period_name = p_start_period
AND cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = p_book_type
AND cp.calendar_type = fc.calendar_type
AND cp.start_date >= fy.start_date
AND cp.end_date <= fy.end_date
AND fy.fiscal_year_name = fb.fiscal_year_name
AND cp1.period_num = 1
AND fb.deprn_calendar = cp1.calendar_type
AND cp1.start_date > cp.start_date
AND ROWNUM = 1;
SELECT CEIL ((fb.COST - fds.deprn_reserve) / 60)
FROM fa_books fb
, fa_deprn_summary fds
WHERE fds.book_type_code = fb.book_type_code
AND fb.asset_id = fds.asset_id
AND fds.period_counter = p_period_counter
AND fb.asset_id = p_asset_id
AND fb.book_type_code = p_book_type_code
AND fb.transaction_header_id_out IS NULL;
SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.period_name = p_period_name;
SELECT fcp.period_name
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.end_date = p_end_date;
SELECT deprn_reserve
INTO ln_acc_deprn
FROM (SELECT ROWNUM a
, deprn_reserve
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id
ORDER BY deprn_reserve DESC)
WHERE ROWNUM = 1;
SELECT max(nvl(deprn_reserve,0))
INTO ln_acc_deprn
FROM fa_deprn_summary
WHERE book_type_code = p_book_type_code
AND asset_id = p_asset_id;
insert_fa_whatif_itf
(x_errbuf => lc_errmsg
, x_retcode => ln_retcode
, p_request_id => p_request_id
, p_checkbox_check => p_full_rsrv_checkbox
, p_book_type_code => p_book_type_code
, p_number_of_periods => p_number_of_periods
, p_asset_id => lr_books.asset_id
, p_period_name => p_start_period
, p_first_begin_prd => l_first_eligible_period
--lr_books.period_name
-- ,p_dep_amt => l_deprn_amount
, p_dep_amt => lr_books.deprn_value
, p_dep_amt_annual => lr_books.annual_deprn_value
, p_date_placed_in_service => lr_books.date_placed_in_service
, p_life_in_months => lr_books.life_in_months
, p_original_cost => lr_books.original_cost
, p_asset_number => lr_books.asset_number
, p_description => lr_books.description
, p_tag_number => lr_books.tag_number
, p_serial_number => lr_books.serial_number
, p_location => lr_books.LOCATION
, p_expense_account => lr_books.expense_account
, p_round_value => lr_books.round_value
, p_deprn_value => ln_acc_deprn
--ln_acc_deprnlr_books.Deprn_Value
, p_flag => 'N'
);
insert_fa_whatif_itf
(x_errbuf => lc_errmsg
, x_retcode => ln_retcode
, p_request_id => p_request_id
, p_checkbox_check => p_full_rsrv_checkbox
, p_book_type_code => p_book_type_code
, p_number_of_periods => p_number_of_periods
, p_asset_id => lr_books.asset_id
, p_period_name => p_start_period
--lr_books.period_name
, p_first_begin_prd => p_first_begin_period
-- ,p_dep_amt => l_deprn_amount
, p_dep_amt => lr_books.deprn_value
, p_dep_amt_annual => lr_books.annual_deprn_value
, p_date_placed_in_service => lr_books.date_placed_in_service
, p_life_in_months => lr_books.life_in_months
, p_original_cost => lr_books.original_cost
, p_asset_number => lr_books.asset_number
, p_description => lr_books.description
, p_tag_number => lr_books.tag_number
, p_serial_number => lr_books.serial_number
, p_location => lr_books.LOCATION
, p_expense_account => lr_books.expense_account
, p_round_value => lr_books.round_value
, p_deprn_value => ln_acc_deprn
--lr_books.Deprn_Value
, p_flag => 'N'
);
insert_fa_whatif_itf
(x_errbuf => lc_errmsg
, x_retcode => ln_retcode
, p_request_id => p_request_id
, p_checkbox_check => p_full_rsrv_checkbox
, p_book_type_code => p_book_type_code
, p_number_of_periods => p_number_of_periods
, p_asset_id => lr_books.asset_id
, p_period_name => p_start_period
, p_first_begin_prd => l_first_eligible_period
--lr_books.period_name
-- ,p_dep_amt => l_deprn_amount
, p_dep_amt => lr_books.deprn_value
, p_dep_amt_annual => lr_books.annual_deprn_value
, p_date_placed_in_service => lr_books.date_placed_in_service
, p_life_in_months => lr_books.life_in_months
, p_original_cost => lr_books.original_cost
, p_asset_number => lr_books.asset_number
, p_description => lr_books.description
, p_tag_number => lr_books.tag_number
, p_serial_number => lr_books.serial_number
, p_location => lr_books.LOCATION
, p_expense_account => lr_books.expense_account
, p_round_value => lr_books.round_value
, p_deprn_value => ln_acc_deprn
--lr_books.Deprn_Value
, p_flag => 'N'
);
SELECT user_profile_option_name
FROM fnd_profile_options_vl
WHERE profile_option_name = p_option_name;
SELECT fb.book_type_code
, fb.COST -- change
, fb.basic_rate -- change
, fb.asset_id
, fb.last_update_date
, fb.last_updated_by
, fb.last_update_login
, fb.original_cost
, fb.life_in_months
, fb.date_placed_in_service
, fb.period_counter_fully_reserved
, fa.asset_number
, fa.description
, fa.tag_number
, fa.serial_number
, flk.concatenated_segments LOCATION
, gcck.concatenated_segments expense_account
-- ,(CEIL(((FB.cost - FB.salvage_value)*FB.basic_rate)/12)-((FB.cost - FB.salvage_value)*FB.basic_rate)/12) Round_Value
-- , CEIL(((FB.cost - FB.salvage_value)*FB.basic_rate)/12) Acc_Deprn_Value
-- ,CEIL((FB.allowed_deprn_limit_amount -1)/60) Deprn_value
, (fb.allowed_deprn_limit_amount - 1) / 5
- FLOOR ((fb.allowed_deprn_limit_amount - 1) / 5) round_value
, ROUND ((fb.allowed_deprn_limit_amount - 1) / 60) deprn_value
, (fb.allowed_deprn_limit_amount - 1) / 5 annual_deprn_value
, fb.adjusted_cost
, fb.allowed_deprn_limit_amount
FROM fa_books fb
, fa_book_controls fbc -- As per Bug No .7183390 (For Tax Books)
, fa_additions fa
, fa_distribution_history fdh
, fa_locations_kfv flk
, gl_code_combinations_kfv gcck
WHERE fa.asset_id = fb.asset_id
AND fb.asset_id = p_asset_id
AND fdh.asset_id = fa.asset_id
-- AND FDH.book_type_code = FB.book_type_code -- As per Bug No .7183390 (For Tax Books)
AND fbc.book_type_code = fb.book_type_code
-- As per Bug No .7183390 (For Tax Books)
AND fbc.distribution_source_book = fdh.book_type_code
-- As per Bug No .7183390 (For Tax Books)
AND fdh.location_id = flk.location_id
AND gcck.code_combination_id = fdh.code_combination_id
AND fb.book_type_code = p_book_type_code
AND fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
AND fb.period_counter_fully_reserved IS NULL
AND fdh.transaction_header_id_out IS NULL
-- As per Bug No .7183390 (For Tax Books)
AND fb.deprn_method_code <> 'JP-STL-EXTND'
AND fb.allowed_deprn_limit_amount > 1;
SELECT fdp.period_counter
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = p_book_type_code
AND fdp.period_name = p_period_name;
SELECT cp1.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
, fa_calendar_types fc
, fa_calendar_periods cp1
WHERE cp.period_name = p_start_period
AND cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = p_book_type
AND cp.calendar_type = fc.calendar_type
AND cp.start_date >= fy.start_date
AND cp.end_date <= fy.end_date
AND fy.fiscal_year_name = fb.fiscal_year_name
AND cp1.period_num = 1
AND fb.deprn_calendar = cp1.calendar_type
AND cp1.start_date >= cp.start_date -- BUG# 7264516: Added = condition
AND ROWNUM = 1;
SELECT CEIL ((fb.COST - fds.deprn_reserve) / 60)
-- need to change for variable
FROM fa_books fb
, fa_deprn_summary fds
WHERE fds.book_type_code = fb.book_type_code
AND fb.asset_id = fds.asset_id
AND fds.period_counter = p_period_counter
AND fb.asset_id = p_asset_id
AND fb.book_type_code = p_book_type_code
AND fb.transaction_header_id_out IS NULL;
SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.period_name = p_period_name;
SELECT LAST_DAY (ADD_MONTHS (fcp.end_date, p_counter))
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND p_end_date BETWEEN fcp.start_date AND fcp.end_date;
SELECT fcp.period_name
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.end_date = p_end_date;
SELECT fcp.end_date
FROM fa_calendar_periods fcp
, fa_calendar_types fct
, fa_book_controls fbc
WHERE fbc.book_type_code = p_book_type_code
AND fcp.calendar_type = fct.calendar_type
AND fcp.calendar_type = fbc.deprn_calendar
AND fcp.period_name = p_period_name;
SELECT fb.deprn_method_code
FROM fa_books fb
WHERE fb.book_type_code = p_book_type_code
AND fb.asset_id = p_asset_id
AND fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
AND fb.deprn_method_code LIKE '%STL%';
SELECT period_name
INTO lc_period
FROM fa_deprn_periods
WHERE book_type_code = p_book_type_code
AND period_close_date IS NULL;
select period_name
into l_last_deprn_run_pc
FROM (
select period_name
from fa_deprn_periods
where book_type_code = p_book_type_code
and deprn_run = 'Y'
order by period_counter desc)
where rownum = 1;
/* SELECT MONTHS_BETWEEN (TO_DATE (lc_period, 'MM-RRRR')
, TO_DATE (p_start_period, 'MM-RRRR'))
INTO l_temp
FROM DUAL;
SELECT ADD_MONTHS (l_date_extn_end, i-1)
-- SELECT ADD_MONTHS(l_date_extn_end,i-1) (For Bug : 6971130)
INTO l_extended_date
FROM DUAL;
SELECT ADD_MONTHS (l_date_extn_end, i-1)
--SELECT ADD_MONTHS(l_date_extn_end,i-1) (For Bug : 6971130)
INTO l_extended_date
FROM DUAL;
insert_fa_whatif_itf
(x_errbuf => lc_errmsg
, x_retcode => ln_retcode
, p_request_id => p_request_id
, p_checkbox_check => p_full_rsrv_checkbox
, p_book_type_code => p_book_type_code
, p_number_of_periods => p_number_of_periods
, p_asset_id => lr_books.asset_id
, p_period_name => lc_period
, p_first_begin_prd => l_first_eligible_period
--lr_books.period_name
, p_dep_amt => lr_books.deprn_value
--l_deprn_amount
, p_dep_amt_annual => lr_books.annual_deprn_value
, p_date_placed_in_service => lr_books.date_placed_in_service
, p_life_in_months => lr_books.life_in_months
, p_original_cost => lr_books.original_cost
, p_asset_number => lr_books.asset_number
, p_description => lr_books.description
, p_tag_number => lr_books.tag_number
, p_serial_number => lr_books.serial_number
, p_location => lr_books.LOCATION
, p_expense_account => lr_books.expense_account
, p_round_value => lr_books.round_value
, p_deprn_value => ln_acc_deprn
--lr_books.Acc_Deprn_Value
, p_flag => 'Y'
);
insert_fa_whatif_itf
(x_errbuf => lc_errmsg
, x_retcode => ln_retcode
, p_request_id => p_request_id
, p_checkbox_check => p_full_rsrv_checkbox
, p_book_type_code => p_book_type_code
, p_number_of_periods => p_number_of_periods
, p_asset_id => lr_books.asset_id
, p_period_name => lc_period
--lr_books.period_name
, p_first_begin_prd => p_first_begin_period
, p_dep_amt => lr_books.deprn_value
--l_deprn_amount
, p_dep_amt_annual => lr_books.annual_deprn_value
, p_date_placed_in_service => lr_books.date_placed_in_service
, p_life_in_months => lr_books.life_in_months
, p_original_cost => lr_books.original_cost
, p_asset_number => lr_books.asset_number
, p_description => lr_books.description
, p_tag_number => lr_books.tag_number
, p_serial_number => lr_books.serial_number
, p_location => lr_books.LOCATION
, p_expense_account => lr_books.expense_account
, p_round_value => lr_books.round_value
, p_deprn_value => ln_acc_deprn
--lr_books.Acc_Deprn_Value
, p_flag => 'Y'
);
insert_fa_whatif_itf
(x_errbuf => lc_errmsg
, x_retcode => ln_retcode
, p_request_id => p_request_id
, p_checkbox_check => p_full_rsrv_checkbox
, p_book_type_code => p_book_type_code
, p_number_of_periods => p_number_of_periods
, p_asset_id => lr_books.asset_id
, p_period_name => lc_period
--p_start_period
, p_first_begin_prd => l_first_eligible_period
--lr_books.period_name
, p_dep_amt => lr_books.deprn_value
--l_deprn_amount
, p_dep_amt_annual => lr_books.annual_deprn_value
, p_date_placed_in_service => lr_books.date_placed_in_service
, p_life_in_months => lr_books.life_in_months
, p_original_cost => lr_books.original_cost
, p_asset_number => lr_books.asset_number
, p_description => lr_books.description
, p_tag_number => lr_books.tag_number
, p_serial_number => lr_books.serial_number
, p_location => lr_books.LOCATION
, p_expense_account => lr_books.expense_account
, p_round_value => lr_books.round_value
, p_deprn_value => ln_acc_deprn
--lr_books.Acc_Deprn_Value
, p_flag => 'Y'
);
SELECT fb.period_counter_fully_reserved
, fb.asset_id
FROM fa_books fb
WHERE fb.book_type_code = p_book_type_code
AND fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
AND fb.asset_id = p_asset_id
AND fb.deprn_method_code <> 'JP-STL-EXTND'
AND fb.allowed_deprn_limit_amount > 1;
SELECT request_id
, book_type_code
, asset_id
, asset_number
, description
, tag_number
, serial_number
, period_name
, fiscal_year
, expense_acct
, LOCATION
, units
, employee_name
, employee_number
, asset_key
, current_cost
, current_prorate_conv
, current_method
, current_life
, current_basic_rate
, current_adjusted_rate
, current_salvage_value
, depreciation
, new_depreciation
, created_by
, creation_date
, last_update_date
, last_updated_by
, last_update_login
, date_placed_in_service
, CATEGORY
, accumulated_deprn
, bonus_depreciation
, new_bonus_depreciation
, current_bonus_rule
, period_num
, currency_code
FROM fa_whatif_itf
WHERE request_id = x_request_id
AND book_type_code = x_book
AND asset_id = x_asset_id
AND EXISTS (
SELECT fiscal_year
FROM fa_whatif_itf
WHERE request_id = x_request_id
AND book_type_code = x_book
AND asset_id = x_asset_id)
ORDER BY fiscal_year ASC;
SELECT cp1.period_name period_name
, fa_jp_tax_extn_pvt.ret_counter (x_book_type, cp1.period_name)
counter
, cp1.period_num
, bc.fiscal_year_name
FROM fa_calendar_periods cp
, fa_book_controls bc
, fa_deprn_periods dp
, fa_calendar_periods cp1
, (SELECT MAX (cp.start_date) max_start_date
FROM fa_calendar_periods cp
, fa_calendar_periods cp1
, fa_book_controls bc
, fa_deprn_periods dp
WHERE dp.book_type_code = x_book_type
AND dp.period_close_date IS NULL
AND dp.calendar_period_open_date <= cp.start_date
AND cp.calendar_type = bc.deprn_calendar
AND bc.book_type_code = x_book_type
AND bc.deprn_calendar = cp1.calendar_type
AND cp.start_date >= cp1.start_date
AND cp1.period_name = x_start_period
AND ROWNUM <= x_num_periods) x
WHERE dp.book_type_code = x_book_type
AND dp.period_close_date IS NULL
AND dp.calendar_period_open_date <= cp.start_date
AND cp.calendar_type = bc.deprn_calendar
AND bc.book_type_code = x_book_type
AND bc.deprn_calendar = cp1.calendar_type
AND cp1.start_date >= cp.start_date
AND cp.period_name = x_start_period
AND cp1.start_date <= x.max_start_date;
SELECT th.asset_id
FROM fa_calendar_periods cp
, fa_deprn_periods dp
, fa_transaction_headers th
, fa_book_controls bc
WHERE dp.book_type_code = bc.book_type_code
AND cp.calendar_type = bc.deprn_calendar
AND th.book_type_code = dp.book_type_code
AND th.transaction_date_entered BETWEEN cp.start_date AND cp.end_date
AND th.date_effective BETWEEN dp.period_open_date
AND NVL (dp.period_close_date
, th.date_effective)
AND bc.book_type_code = x_book_type
AND cp.period_name <> dp.period_name
AND th.transaction_type_code = 'ADDITION';
lt_whatitf.DELETE;
lt_nbv_typ.DELETE;
SELECT number_per_fiscal_year
INTO h_nop
FROM fa_calendar_types fc
, fa_book_controls fb
WHERE fc.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book;
SELECT period_name
INTO lc_period
FROM fa_deprn_periods
WHERE book_type_code = x_book
AND period_close_date IS NULL;
SELECT fc.period_name
INTO l_dtin_serv
FROM fa_calendar_periods fc
, fa_book_controls fb
WHERE fc.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book
AND TRUNC (TO_DATE (x_dtin_serv, 'dd/mm/rrrr'))
BETWEEN fc.start_date
AND fc.end_date;
SELECT COUNT (1)
INTO ln_temp1
FROM fa_deprn_summary
WHERE asset_id = x_asset_id
AND deprn_source_code = 'DEPRN';
SELECT COUNT (1)
INTO ln_temp1
FROM fa_deprn_summary
WHERE asset_id = x_asset_id
AND deprn_source_code = 'DEPRN';
SELECT DECODE (adjusted_cost
, COST, 1
, 0
)
INTO l_tmp
FROM fa_books
WHERE book_type_code = x_book
AND date_ineffective IS NULL
AND period_counter_fully_reserved IS NULL
AND asset_id = x_asset_id;
SELECT deprn_amount
, ROUND (deprn_reserve, 0)
INTO l_dep
, g_dep
FROM fa_deprn_summary fds
, fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
AND fds.asset_id = x_asset_id
AND fdp.book_type_code = x_book
AND fdp.period_name =
(SELECT cp.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book
AND fb.fiscal_year_name = fy.fiscal_year_name
AND cp.period_num = 1
AND fy.fiscal_year =
(SELECT fy.fiscal_year
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.period_name = lc_period
AND cp.calendar_type =
fb.deprn_calendar
AND fb.book_type_code = x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.start_date
BETWEEN fy.start_date
AND fy.end_date)
AND cp.start_date BETWEEN fy.start_date
AND fy.end_date);
SELECT period_num
INTO l_per_dum
FROM fa_deprn_summary fds
, fa_deprn_periods fdp
WHERE fdp.book_type_code = fds.book_type_code
AND fdp.period_counter = fds.period_counter
AND fds.book_type_code = x_book
AND fds.asset_id = x_asset_id
AND fds.deprn_source_code = 'BOOKS';
SELECT deprn_amount
, ROUND (deprn_reserve, 0)
INTO l_dep
, g_dep
FROM fa_deprn_summary fds
, fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
AND fds.asset_id = x_asset_id
AND fdp.book_type_code = x_book
AND fdp.period_name =
(SELECT cp.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.period_num = 1
AND fy.fiscal_year =
(SELECT fy.fiscal_year
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.period_name =
lc_period
AND cp.calendar_type =
fb.deprn_calendar
AND fb.book_type_code =
x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.start_date
BETWEEN fy.start_date
AND fy.end_date)
AND cp.start_date BETWEEN fy.start_date
AND fy.end_date);
SELECT fdp.period_counter
INTO ln_stcnt
FROM fa_deprn_periods fdp
WHERE fdp.period_name =
(SELECT cp.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.calendar_type =
fb.deprn_calendar
AND fb.book_type_code = x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.period_num = 1
AND fy.fiscal_year =
(SELECT fy.fiscal_year
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.period_name =
lc_period
AND cp.calendar_type =
fb.deprn_calendar
AND fb.book_type_code =
x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.start_date
BETWEEN fy.start_date
AND fy.end_date)
AND cp.start_date BETWEEN fy.start_date
AND fy.end_date)
AND fdp.book_type_code = x_book;
SELECT deprn_amount
, ytd_deprn
, deprn_reserve
INTO l_dep
, y_dep
, g_dep
FROM fa_deprn_summary fds
, fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
AND fds.asset_id = x_asset_id
AND fdp.book_type_code = x_book
AND fdp.period_counter = l_opn_cntr - 1;
SELECT deprn_amount
, deprn_reserve
INTO l_dep
, g_dep
FROM fa_deprn_summary fds
, fa_deprn_periods fdp
WHERE fds.period_counter = fdp.period_counter
AND fds.book_type_code = fdp.book_type_code
AND fds.asset_id = x_asset_id
AND fdp.book_type_code = x_book
AND fdp.period_name =
(SELECT cp.period_name
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.period_num = 1
AND fy.fiscal_year =
(SELECT fy.fiscal_year
FROM fa_calendar_periods cp
, fa_fiscal_year fy
, fa_book_controls fb
WHERE cp.period_name =
lc_period
AND cp.calendar_type =
fb.deprn_calendar
AND fb.book_type_code =
x_book
AND fb.fiscal_year_name =
fy.fiscal_year_name
AND cp.start_date
BETWEEN fy.start_date
AND fy.end_date)
AND cp.start_date BETWEEN fy.start_date
AND fy.end_date);
SELECT DECODE (adjusted_cost
, COST, 1
, 0
)
INTO l_tmp
FROM fa_books
WHERE book_type_code = x_book
AND date_ineffective IS NULL
AND period_counter_fully_reserved IS NULL
AND asset_id = x_asset_id;
UPDATE fa_whatif_itf
SET depreciation = lt_whatitf (j).depreciation
, new_depreciation = lt_whatitf (j).new_depreciation
, accumulated_deprn = lt_whatitf (j).accumulated_deprn
WHERE period_name = lt_whatitf (j).period_name
AND asset_id = x_asset_id
AND book_type_code = x_book
AND request_id = lt_whatitf (j).request_id;
SELECT period_num
INTO h_periodnum
FROM fa_calendar_periods fc
, fa_book_controls fb
WHERE fc.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book_type
AND period_name = x_period;
SELECT (ffy.fiscal_year * fct.number_per_fiscal_year + fc.period_num)
period_counter
INTO l_dtcntr1
FROM fa_calendar_periods fc
, fa_book_controls fb
, fa_fiscal_year ffy
, fa_calendar_types fct
WHERE fc.calendar_type = fb.deprn_calendar
AND fb.book_type_code = x_book_typ
AND ffy.fiscal_year_name = fb.fiscal_year_name
AND ffy.fiscal_year_name = fct.fiscal_year_name
AND fc.calendar_type = fct.calendar_type
AND fct.calendar_type = fb.deprn_calendar
AND fc.start_date >= ffy.start_date
AND fc.end_date <= ffy.end_date
AND fc.period_name = x_periodname;