[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT
DISTINCT fps.period_name
, fps.start_date
, fps.end_date
FROM
gl_period_statuses fps
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND fps.start_date >= ld_start_date
AND fps.end_date <= ld_end_date
AND fps.adjustment_period_flag = 'N';
SELECT
start_date
INTO
ld_start_date
FROM
gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT
end_date
INTO
ld_end_date
FROM
gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT
DISTINCT faav.asset_id
, faav.asset_number
, faav.attribute_category_code
, faav.description
, bk.date_placed_in_service
, bk.book_type_code
, faav.current_units
, bk.deprn_method_code
, bk.life_in_months
, fncv.name
, bk.cost
, bk.salvage_value
, bk.unit_of_measure --UOM
, bk.production_capacity
, fcb.asset_cost_acct
, fcb.impair_reserve_acct
, fcb.deprn_reserve_acct
, fbc.book_class
, faav.asset_key_ccid
FROM
fa_additions_v faav
, fa_books bk
, fa_book_controls_sec fbc
, fa_category_books fcb
, fa_methods mth
, fa_distribution_history fdh
, gl_ledgers gl
, fa_transaction_history_trx_v fthv
, fnd_currencies_vl fncv
-- Check ledger
WHERE bk.date_ineffective IS NULL
AND bk.transaction_header_id_out IS NULL
AND faav.asset_id = bk.asset_id
AND bk.book_type_code = fbc.book_type_code
AND fbc.book_class IN ('CORPORATE', 'TAX')
AND fbc.gl_posting_allowed_flag = 'YES'
AND fbc.set_of_books_id = pn_ledger_id
AND fdh.asset_id = faav.asset_id
-- Check legal entity
AND EXISTS
(
SELECT fdp.period_close_date
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = bk.book_type_code
AND fdp.calendar_period_close_date
BETWEEN ld_start_date AND ld_end_date
AND fdp.period_close_date IS NOT NULL
)
AND fdh.date_effective < nvl(
(SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = bk.book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
AND (fdh.date_ineffective > nvl(
(SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = bk.book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
OR fdh.date_ineffective IS NULL)
AND EXISTS
(
SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_CFS_DATA_CLT_PKG.
get_balancing_segment(fdh.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id
)
-- Check the asset type
AND faav.asset_type = 'CAPITALIZED'
--get category accounts
AND fcb.category_id = faav.asset_category_id
AND fcb.book_type_code = bk.book_type_code
-- get the book window elements.
AND bk.asset_id = faav.asset_id
AND bk.date_ineffective IS NULL
AND bk.book_type_code = fbc.book_type_code
AND bk.deprn_method_code = mth.method_code
AND (
bk.life_in_months = mth.life_in_months
OR
(bk.life_in_months IS NULL AND mth.life_in_months IS NULL)
)
--get the currency
AND gl.ledger_id = pn_ledger_id
AND gl.currency_code = fncv.currency_code
-- Check the asset added date
AND fthv.asset_id = faav.asset_id
AND fthv.transaction_type_code = 'ADDITION'
AND fthv.book_type_code = bk.book_type_code
AND
(
(SELECT greatest(greatest( dp.calendar_period_open_date
, least( SYSDATE
, dp.calendar_period_close_date)
)
, dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE)
) <= ld_end_date
)
ORDER BY faav.asset_number;
SELECT
concatenated_segment_delimiter
FROM
fnd_id_flex_structures
WHERE id_flex_code = 'CAT#';
SELECT
start_date
, period_num
INTO
ld_start_date
, lv_accounting_period
FROM
gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_from
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT
end_date
INTO
ld_end_date
FROM
gl_period_statuses
WHERE ledger_id = pn_ledger_id
AND application_id = 101
AND period_name = pv_period_to
AND TO_CHAR(period_year) = pv_accounting_year;
SELECT
application_column_name
INTO
lv_nature_segment
FROM
fnd_segment_attribute_values
WHERE application_id = 101
AND id_flex_num = pn_coa_id
AND id_flex_code = 'GL#'
AND attribute_value = 'Y'
AND segment_attribute_type = 'GL_ACCOUNT';
SELECT
dffa.attribute_column
INTO
lv_usage_segment
FROM
ja_cn_dff_assignments_v dffa
WHERE dffa.lookup_code = 'FAAU'
AND dffa.chart_of_accounts_id = pn_coa_id;
SELECT
dffa.attribute_column
INTO
lv_measure_segment
FROM
ja_cn_dff_assignments_v dffa
WHERE dffa.lookup_code = 'FAUM'
AND dffa.chart_of_accounts_id = pn_coa_id;
SELECT
COUNT(*)
INTO
ln_full_retirement_count
FROM
fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'FULL RETIREMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective
BETWEEN dp.period_open_date
AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
SELECT COUNT(*) INTO ln_reinstate_count
FROM fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'REINSTATEMENT'
AND
(
(
SELECT greatest(greatest( dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date
AND nvl(dp.period_close_date, SYSDATE)
) <= ld_end_date
);
SELECT
greatest(date_effective)
INTO
ld_retirement_date
FROM
fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'FULL RETIREMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date
AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
SELECT
greatest(date_effective)
INTO
ld_reinstated_date
FROM
fa_transaction_history_trx_v fthv
WHERE fthv.asset_id = lv_asset_id
AND fthv.book_type_code = lv_book_type_code
AND fthv.transaction_type_code = 'REINSTATEMENT'
AND ((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date
AND nvl(dp.period_close_date, SYSDATE)) <= ld_end_date);
SELECT
MAX(dp.period_counter)
INTO
ln_depreciation_counter
FROM
fa_deprn_periods dp
WHERE dp.book_type_code = lv_book_type_code
AND dp.calendar_period_open_date <= ld_end_date
AND dp.period_close_date IS NOT NULL;
SELECT
nvl(MAX(period_counter), 0)
INTO
ln_real_deprn_counter
FROM
fa_deprn_summary
WHERE asset_id = lv_asset_id
AND book_type_code = lv_book_type_code
AND deprn_source_code = 'DEPRN';
SELECT
MIN(thv.transaction_header_id)
INTO
ln_transaction_header_id
FROM fa_deprn_periods dp
, fa_transaction_history_trx_v thv
, fa_transaction_headers fthr
WHERE thv.book_type_code = dp.book_type_code
AND thv.asset_id = lv_asset_id
AND dp.book_type_code = lv_book_type_code
AND thv.transaction_type_code
IN
( 'ADJUSTMENT' , 'FULL RETIREMENT'
, 'PARTIAL RETIREMENT'
, 'REINSTATEMENT', 'REVALUATION'
)
AND thv.transaction_header_id = fthr.transaction_header_id
AND (fthr.transaction_key <> 'UA'
OR fthr.transaction_key IS NULL)
AND thv.date_effective BETWEEN dp.period_open_date
AND nvl(dp.period_close_date, SYSDATE)
AND greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date) > ld_end_date;
SELECT
cost
, salvage_value
, adjusted_recoverable_cost
, deprn_method_code
, production_capacity
, date_placed_in_service
INTO
ln_original_value
, ln_salvage_value
, ln_recoverable_cost
, lv_depn_meth_code
, ln_production_capacity
, ld_in_service_date
FROM
fa_books
WHERE transaction_header_id_out = ln_transaction_header_id;
SELECT
MIN(thv.transaction_header_id)
INTO
ln_transaction_header_id
FROM
fa_deprn_periods dp
, fa_transaction_history_trx_v thv
WHERE asset_id = lv_asset_id
AND dp.book_type_code = lv_book_type_code
AND transaction_type_code IN('UNIT ADJUSTMENT', 'RECLASS')
AND thv.date_effective BETWEEN dp.period_open_date
AND nvl(dp.period_close_date, SYSDATE)
AND greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date) > ld_end_date;
SELECT
fhv.units
, ckfv.concatenated_segments
INTO
ln_asset_unit
, lv_category_code
FROM
fa_asset_history_v fhv
, fa_categories_b_kfv ckfv
WHERE fhv.key = ln_transaction_header_id
AND fhv.transaction_header_id_out = ln_transaction_header_id
AND fhv.category_id = ckfv.category_id;
SELECT deprn_reserve
, impairment_reserve
, ltd_production
, deprn_amount
, nvl(impairment_amount, 0)
INTO ln_deprn_reserve
, ln_impairment_rsv
, ln_life_production
, ln_monthly_deprn_amount
, ln_mth_impairment_amount
FROM fa_deprn_summary
WHERE asset_id = lv_asset_id
AND period_counter = ln_depreciation_counter
AND book_type_code = lv_book_type_code;
SELECT SUM(fdh.units_assigned)
INTO ln_assigned_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = lv_asset_id
AND fdh.date_effective < nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
AND (fdh.date_ineffective > nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_end_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
OR fdh.date_ineffective IS NULL)
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_UTILITY.get_balancing_segment(fdh.code_combination_id)
= jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
GROUP BY fdh.asset_id;
SELECT MIN(fthv.transaction_header_id)
INTO ln_retire_tran_header_id
FROM fa_transaction_history_trx_v fthv
, fa_retirements fr
WHERE fthv.book_type_code = lv_book_type_code
AND fthv.asset_id = lv_asset_id
AND fthv.transaction_type_code IN
( 'FULL RETIREMENT', 'PARTIAL RETIREMENT')
AND
((SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE fthv.book_type_code = dp.book_type_code
AND fthv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE))
BETWEEN ld_start_date --from date
AND ld_end_date --to date)
)
AND fr.transaction_header_id_in = fthv.transaction_header_id
AND fr.status <> 'PENDING'
AND fr.asset_id = fthv.asset_id
AND (
TRANSACTION_HEADER_ID_OUT IS NULL
OR NOT EXISTS(
SELECT thv.transaction_header_id
FROM fa_transaction_history_trx_v thv
WHERE thv.transaction_header_id = fr.transaction_header_id_out
AND thv.book_type_code = lv_book_type_code
AND thv.asset_id = lv_asset_id
AND thv.transaction_type_code = 'REINSTATEMENT'
AND(
(SELECT greatest(greatest(dp.calendar_period_open_date,
least(SYSDATE, dp.calendar_period_close_date)),
dp.calendar_period_open_date)
FROM fa_deprn_periods dp
WHERE thv.book_type_code = dp.book_type_code
AND thv.date_effective BETWEEN dp.period_open_date AND
nvl(dp.period_close_date, SYSDATE))
BETWEEN ld_start_date --from date
AND ld_end_date --to date)
)
)
);
SELECT fb.cost
, fb.salvage_value
INTO ln_original_value
, ln_salvage_value
FROM fa_books fb
WHERE fb.transaction_header_id_out = ln_retire_tran_header_id;
SELECT nvl(fds.deprn_reserve, 0)
, nvl(fds.impairment_reserve, 0)
INTO ln_deprn_rsv_last
, ln_impt_rsv_last
FROM fa_deprn_summary fds
WHERE fds.asset_id = lv_asset_id
AND fds.book_type_code = lv_book_type_code
AND fds.period_counter =
(SELECT MAX(period_counter)
FROM fa_deprn_summary fds2
WHERE fds2.asset_id = lv_asset_id
AND fds2.book_type_code = lv_book_type_code
AND fds2.period_counter < ln_depreciation_counter);
SELECT SUM(fdh.units_assigned)
INTO ln_asset_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = lv_asset_id
AND fdh.date_effective < nvl(
(SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_start_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
AND (fdh.date_ineffective > nvl(
(SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_start_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
OR fdh.date_ineffective IS NULL);
SELECT SUM(fdh.units_assigned)
INTO ln_assigned_unit
FROM fa_distribution_history fdh
WHERE fdh.asset_id = lv_asset_id
AND fdh.date_effective < nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_start_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
AND (fdh.date_ineffective > nvl((SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = lv_book_type_code
AND fdp.calendar_period_close_date <= ld_start_date
AND fdp.period_close_date IS NOT NULL), SYSDATE)
OR fdh.date_ineffective IS NULL)
AND EXISTS
(SELECT jclllbg.bal_seg_value
FROM ja_cn_ledger_le_bsv_gt jclllbg
WHERE JA_CN_UTILITY
.get_balancing_segment(fdh.code_combination_id) =
jclllbg.bal_seg_value
AND jclllbg.Ledger_Id = pn_ledger_id
AND jclllbg.Legal_Entity_Id = pn_legal_entity_id)
GROUP BY fdh.asset_id;
SELECT COUNT(period_counter)
INTO ln_dep_month
FROM fa_deprn_summary
WHERE asset_id = lv_asset_id
AND period_counter <= ln_depreciation_counter
AND book_type_code = lv_book_type_code
AND deprn_source_code = 'DEPRN'
AND deprn_amount <> 0;
SELECT nvl(fdp.calendar_period_close_date, ld_in_service_date)
INTO ld_addition_date
FROM fa_deprn_periods fdp,
fa_transaction_history_trx_v thv
WHERE thv.asset_id = lv_asset_id
AND fdp.book_type_code = lv_book_type_code
AND thv.book_type_code = fdp.book_type_code
AND thv.transaction_type_code = 'ADDITION'
AND thv.period_entered = fdp.period_name;
SELECT decode( lv_measure_segment
, 'SEGMENT1',faak.segment1, 'SEGMENT2',faak.segment2
, 'SEGMENT3',faak.segment3, 'SEGMENT4',faak.segment4
, 'SEGMENT5',faak.segment5, 'SEGMENT6',faak.segment6
, 'SEGMENT7',faak.segment7, 'SEGMENT8',faak.segment8
, 'SEGMENT9',faak.segment9, 'SEGMENT10',faak.segment10
),
decode( lv_usage_segment
, 'SEGMENT1',faak.segment1, 'SEGMENT2',faak.segment2
, 'SEGMENT3',faak.segment3, 'SEGMENT4',faak.segment4
, 'SEGMENT5',faak.segment5, 'SEGMENT6',faak.segment6
, 'SEGMENT7',faak.segment7, 'SEGMENT8',faak.segment8
, 'SEGMENT9',faak.segment9, 'SEGMENT10',faak.segment10
)
INTO lv_asset_measure
, lv_asset_usage
FROM fa_asset_keywords faak
WHERE faak.code_combination_id = ln_asset_key_ccid;