[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.tag_number
, faav.current_units
, fb.book_type_code
FROM
fa_additions_v faav
, fa_books fb
, fa_book_controls_sec fbc
, fa_distribution_history fdh
, fa_transaction_history_trx_v fthv
WHERE fb.date_ineffective IS NULL
AND fb.transaction_header_id_out IS NULL
AND faav.asset_id = fb.asset_id
AND fb.book_type_code = fbc.book_type_code
AND fbc.book_class IN ('CORPORATE', 'TAX')
AND fbc.gl_posting_allowed_flag = 'YES'
-- Check ledger
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 = fb.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 = fb.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 = fb.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)
-- Check the asset type
AND faav.asset_type = 'CAPITALIZED'
-- Check the asset added date
AND fthv.asset_id = faav.asset_id
AND fthv.transaction_type_code = 'ADDITION'
AND fthv.book_type_code = fb.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
SUM(fdh.units_assigned)
, paf.organization_id
FROM
fa_distribution_history fdh
, per_people_f ppf
, per_assignments_f paf
, per_periods_of_service pos
, fa_book_controls_sec fbc
WHERE fbc.book_type_code = pv_book_type_code
AND fdh.book_type_code = fbc.distribution_source_book
AND fdh.asset_id = pv_asset_id
AND (ld_end_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
OR
(pos.final_process_date BETWEEN ld_start_date
AND ld_end_date
AND pos.final_process_date BETWEEN paf.effective_start_date
AND paf.effective_end_date
)
)
AND ((nvl( pos.final_process_date, ld_end_date) >= ld_end_date
AND ld_end_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date
)
OR (pos.final_process_date BETWEEN ld_start_date
AND ld_end_date
AND pos.final_process_date BETWEEN ppf.effective_start_date
AND ppf.effective_end_date)
)
-- Check legal entity
AND fdh.date_effective < nvl((
SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = pv_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 = pv_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 fdh.assigned_to IS NOT 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)
AND ppf.business_group_id = paf.business_group_id
AND pos.period_of_service_id = paf.period_of_service_id
AND paf.person_id = ppf.person_id
AND paf.person_id = fdh.assigned_to
AND ppf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND paf.business_group_id = fnd_profile.value('PER_BUSINESS_GROUP_ID')
AND paf.primary_flag = 'Y'
GROUP BY paf.organization_id
UNION
SELECT
SUM(fdh.units_assigned)
, fdh.assigned_to
FROM
fa_distribution_history fdh
, fa_book_controls_sec fbc
WHERE fbc.book_type_code = pv_book_type_code
AND fdh.book_type_code = fbc.distribution_source_book
AND fdh.asset_id = pv_asset_id
-- Check legal entity
AND fdh.date_effective < nvl((
SELECT MAX(fdp.period_close_date)
FROM fa_deprn_periods fdp
WHERE fdp.book_type_code = pv_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 = pv_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 fdh.assigned_to 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 assigned_to;
SELECT
start_date
, period_num
INTO
ld_start_date
, lv_period_name
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
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_corp_book
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_corp_book
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_corp_book
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_corp_book
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
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 transaction_type_code = 'UNIT ADJUSTMENT'
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 INTO ln_asset_unit
FROM fa_asset_history_v fhv
WHERE fhv.key = ln_transaction_header_id
AND fhv.transaction_header_id_out = ln_transaction_header_id ;