[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT NVL(SUM(fdh.units_assigned),0)
INTO ln_units
FROM fa_distribution_history fdh
WHERE fdh.asset_id = p_asset_id_in
AND fdh.book_type_code = P_DIST_SOURCE_BOOK
AND fdh.code_combination_id = p_ccid_in
AND fdh.transaction_header_id_in =
(SELECT MAX(fdh1.transaction_header_id_in)
FROM fa_distribution_history fdh1
,fa_transaction_headers fth1
WHERE fdh1.asset_id = p_asset_id_in
AND fdh1.book_type_code = P_DIST_SOURCE_BOOK /* why P_BOOK_NAME it should be P_BOOK_TYPE_CODE, both are not synonymous */
AND fdh1.code_combination_id = p_ccid_in
AND fdh1.transaction_header_id_in = fth1.transaction_header_id
AND fth1.date_effective <= gd_per_close_date);
SELECT (p_original_cost_in/p_units_in) * ln_units
INTO ln_amount
FROM DUAL;
SELECT NVL(fdd.cost,0)
INTO ln_amount
FROM fa_deprn_detail fdd
,fa_distribution_history fdh
,fa_asset_history fah
,fa_transaction_headers fth
WHERE fdd.period_counter = (
SELECT MAX(fdd1.period_counter)
FROM fa_deprn_detail fdd1
WHERE fdd1.book_type_code = P_BOOK_NAME
AND fdd1.distribution_id = fdd.distribution_id
AND fdd1.asset_id = fdd.asset_id
AND fdd1.period_counter < gn_lex_begin_period_counter)
AND fdd.book_type_code = P_BOOK_NAME
AND fdh.asset_id = fth.asset_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fah.category_id = p_category_id_in
AND fah.asset_type = p_asset_type_in
AND fah.asset_id = p_asset_id_in
AND fdd.asset_id = p_asset_id_in
AND fdd.distribution_id = fdh.distribution_id
AND fah.asset_type IN ('CAPITALIZED','CIP')
AND fdh.location_id = p_location_id_in
AND ((fdh.date_effective >= fah.date_effective
AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1)
AND fdh.transaction_header_id_in = fth.transaction_header_id)
OR(fah.date_effective > fdh.date_effective
AND fah.transaction_header_id_in = fth.transaction_header_id
AND fth.transaction_type_code = 'ADDITION' /* CIP ADDITION is not considered? */
AND fah.date_effective < gd_per_open_date))
AND fdh.code_combination_id = p_ccid_in
AND fdd.distribution_id = (
SELECT MAX(fdd1.distribution_id)
FROM fa_deprn_detail fdd1
,fa_distribution_history fdh1
WHERE fdd1.book_type_code = fdd.book_type_code
AND fdd1.asset_id = fdd.asset_id
AND fdd1.distribution_id = fdh1.distribution_id
AND fdh1.code_combination_id = fdh.code_combination_id
AND fdh1.location_id = p_location_id_in
AND fdd1.period_counter < gn_lex_begin_period_counter);
SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount
, fadj.adjustment_amount), 0)),0) additions
INTO ln_amount
FROM fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fah.category_id = p_category_id_in
AND fah.asset_type = p_asset_type_in
AND fth.asset_id = p_asset_id_in
AND fth.book_type_code = P_BOOK_NAME
AND fadj.transaction_header_id = fth.transaction_header_id
AND ((fth.transaction_type_code IN ('CIP ADDITION','CIP ADJUSTMENT')
AND fah.asset_type = 'CIP'
AND fadj.adjustment_type = 'CIP COST')
OR (fth.transaction_type_code = 'ADDITION' AND fah.asset_type = 'CAPITALIZED'
AND fadj.adjustment_type = 'COST'))
AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND ((fdh.date_effective >= fah.date_effective
AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1))
OR (fah.date_effective > fdh.date_effective
AND fth.transaction_type_code = 'ADDITION'))
AND fah.transaction_header_id_in = p_fah_trx_header_id
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND (fah.asset_type <> 'CAPITALIZED' OR ((fah.asset_type='CAPITALIZED') AND (NOT EXISTS (-- Added these lines as part of the fix to the SR 7284007.992
SELECT 'Y'
FROM fa_asset_history fah1
WHERE fah1.asset_type='CIP'
and fah1.transaction_header_id_out = fah.transaction_header_id_in
and fah1.asset_id = fah.asset_id
))));
SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'DR',(-1)*fadj.adjustment_amount,fadj.adjustment_amount), 0)),0) retirements
INTO ln_amount
FROM fa_books fb
,fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fah.asset_type = p_asset_type_in
AND fdh.distribution_id = fadj.distribution_id
AND fah.category_id = p_category_id_in
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fth.book_type_code = P_BOOK_NAME
AND fb.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fadj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_type_code IN ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT')
AND fadj.adjustment_type IN ('COST', 'CIP COST')
AND fadj.source_type_code IN ('RETIREMENT', 'CIP RETIREMENT')
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
AND fth.transaction_header_id >= fah.transaction_header_id_in
AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1)
AND fb.transaction_header_id_in = fth.transaction_header_id
AND fb.book_type_code = P_BOOK_NAME
AND fdh.location_id = p_location_id_in;
SELECT NVL(SUM(x.all_transferred),0)
INTO ln_amount
FROM (
SELECT NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount),0) all_transferred
FROM fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah1
,fa_distribution_history fdh
WHERE EXISTS (
SELECT 1
FROM fa_deprn_detail fdd
WHERE fdd.asset_id = fth.asset_id
AND fdd.book_type_code = P_BOOK_NAME
AND fdd.deprn_source_code = 'D'
)
AND (fah1.asset_type = p_asset_type_in
OR (fah1.asset_type = 'CIP'
AND EXISTS (
SELECT period_counter_capitalized
FROM fa_books fb2
WHERE fb2.asset_id = fah1.asset_id
AND fb2.book_type_code = P_BOOK_NAME
AND fb2.period_counter_capitalized < gn_lex_begin_period_counter
)))
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND fadj.source_type_code = 'TRANSFER'
AND fadj.adjustment_type IN ('COST', 'CIP COST')
AND fth.transaction_type_code <> 'TRANSFER IN'
AND fth.asset_id = fah1.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fth.asset_id = p_asset_id_in
AND fah1.category_id = p_category_id_in
AND fth.book_type_code = P_BOOK_NAME
AND fadj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_date_entered <= gd_period_close_date
AND fadj.asset_id = fah1.asset_id
AND fadj.book_type_code = P_BOOK_NAME
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND fth.transaction_header_id >= fah1.transaction_header_id_in
AND fth.transaction_header_id < NVL(fah1.transaction_header_id_out, fth.transaction_header_id + 1)
UNION ALL -- Added this query as part of the fix to the SR 7284007.992
SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount
, fadj.adjustment_amount),0)),0) all_transferred
FROM fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fah.category_id = p_category_id_in
AND fah.asset_type = p_asset_type_in
AND fth.asset_id = p_asset_id_in
AND fth.book_type_code = P_BOOK_NAME
AND fadj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_type_code = 'ADDITION'
AND fah.asset_type = 'CAPITALIZED'
AND fadj.adjustment_type = 'COST'
AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND ((fdh.date_effective >= fah.date_effective
AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1))
OR (fah.date_effective > fdh.date_effective
AND fth.transaction_type_code = 'ADDITION'))
AND fah.transaction_header_id_in = p_fah_trx_header_id
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND ((fah.asset_type='CAPITALIZED') AND (EXISTS (-- Added these lines as part of the fix to the SR 7284007.992
SELECT 'Y'
FROM fa_asset_history fah1
WHERE fah1.asset_type='CIP'
and fah1.transaction_header_id_out = fah.transaction_header_id_in
and fah1.asset_id = fah.asset_id
)))
UNION ALL
SELECT (-1)*(NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount -- Added this query as part of the fix to the SR 7284007.992
, fadj.adjustment_amount), 0)),0)) all_transferred
FROM fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fah.category_id = p_category_id_in
AND fah.asset_type = p_asset_type_in
AND fth.asset_id = p_asset_id_in
AND fth.book_type_code = P_BOOK_NAME
AND fadj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_type_code IN ('CIP ADDITION','CIP ADJUSTMENT')
AND fah.asset_type = 'CIP'
AND fadj.adjustment_type = 'CIP COST'
AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
AND fdh.date_effective >= fah.date_effective
AND fdh.date_effective < NVL(fah.date_ineffective, fdh.date_effective + 1)
AND fah.transaction_header_id_in = p_fah_trx_header_id
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND (EXISTS (
SELECT 'Y'
FROM fa_asset_history fah1
,fa_transaction_headers fth1
WHERE fah1.asset_type='CAPITALIZED'
AND fah1.transaction_header_id_in = fah.transaction_header_id_out
AND fah1.asset_id = fah.asset_id
AND fth1.transaction_header_id = fah1.transaction_header_id_in
AND fth1.transaction_type_code = 'ADDITION'
AND fth1.transaction_date_entered BETWEEN gd_period_open_date AND gd_period_close_date
))
UNION ALL
SELECT NVL(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount),0) all_transferred
FROM fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah1
,fa_asset_history fah2
,fa_distribution_history fdh
WHERE EXISTS (
SELECT 1
FROM fa_deprn_detail fdd
WHERE fdd.asset_id = fth.asset_id
AND fdd.book_type_code = P_BOOK_NAME
AND fdd.deprn_source_code = 'D'
)
AND (fah1.asset_type = p_asset_type_in
OR (fah1.asset_type = 'CIP'
AND EXISTS (
SELECT fb2.period_counter_capitalized
FROM fa_books fb2
WHERE fb2.asset_id = fah1.asset_id
AND fb2.book_type_code = P_BOOK_NAME
AND fb2.period_counter_capitalized < gn_lex_begin_period_counter
)
)
)
AND fadj.source_type_code = 'RECLASS'
AND fadj.adjustment_type IN ('COST', 'CIP COST')
AND fth.transaction_type_code <> 'TRANSFER IN'
AND fth.asset_id = fah1.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fdh.location_id = p_location_id_in
AND fth.book_type_code = P_BOOK_NAME
AND fah2.category_id = p_category_id_in
AND fth.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fadj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_date_entered <= gd_period_close_date
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND fadj.asset_id = fah1.asset_id
AND fadj.book_type_code = P_BOOK_NAME
AND fah1.asset_id = fah2.asset_id
AND fth.transaction_header_id >= fah1.transaction_header_id_in
AND fth.transaction_header_id < NVL(fah1.transaction_header_id_out, fth.transaction_header_id + 1)
AND fdh.date_effective >= fah2.date_effective
AND fdh.date_effective < NVL(fah2.date_ineffective, fdh.date_effective + 1)
) X;
SELECT NVL(SUM(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount)),0)
INTO ln_amount
FROM fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fah.category_id = p_category_id_in
AND fdh.location_id = p_location_id_in
AND fah.asset_type = p_asset_type_in
AND fth.book_type_code = P_BOOK_NAME
AND fdh.code_combination_id = p_ccid_in
AND fth.asset_id = p_asset_id_in
AND fadj.transaction_header_id = fth.transaction_header_id
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND fth.transaction_type_code IN ('ADJUSTMENT') /* CIP ADJUSTMENT excluded ? */
AND fadj.adjustment_type IN ('COST')
AND fth.transaction_date_entered <= gd_period_close_date
AND fth.transaction_header_id >= fah.transaction_header_id_in
AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1);
SELECT NVL(SUM(DECODE(fadj.debit_credit_flag, 'CR', -1 * fadj.adjustment_amount, fadj.adjustment_amount)),0)
INTO ln_amount
FROM fa_books fb
,fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fdh.distribution_id = fadj.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fah.category_id = p_category_id_in
AND fdh.location_id = p_location_id_in
AND fah.asset_type = p_asset_type_in
AND fth.book_type_code = P_BOOK_NAME
AND fdh.code_combination_id = p_ccid_in
AND fth.asset_id = p_asset_id_in
AND fadj.transaction_header_id = fth.transaction_header_id
AND fadj.period_counter_adjusted BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND fth.transaction_type_code IN ('ADJUSTMENT','CIP ADJUSTMENT')
AND fadj.adjustment_type = 'EXPENSE'
AND fth.transaction_subtype = 'APPREC'
AND fth.transaction_date_entered <= gd_period_close_date
AND fth.transaction_header_id >= fah.transaction_header_id_in
AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1)
AND fb.transaction_header_id_in = fth.transaction_header_id
AND fb.book_type_code = P_BOOK_NAME;
SELECT NVL(fdd.deprn_reserve,0)
INTO ln_amount
FROM fa_deprn_summary fds
,fa_deprn_detail fdd
,fa_distribution_history fdh
WHERE fds.book_type_code = fdd.book_type_code
AND fds.asset_id = fdd.asset_id
AND fds.period_counter = fdd.period_counter
AND fdd.distribution_id = fdh.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fdh.book_type_code = P_DIST_SOURCE_BOOK
AND fdd.asset_id = fdh.asset_id
AND fds.book_type_code = P_BOOK_NAME
AND fds.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND fds.period_counter = (
SELECT MAX(fds1.period_counter)
FROM fa_deprn_summary fds1
,fa_deprn_detail fdd1
,fa_distribution_history fdh1
WHERE fds1.book_type_code = fdd1.book_type_code
AND fds1.asset_id = fdd1.asset_id
AND fds1.period_counter = fdd1.period_counter
AND fdd1.distribution_id = fdh1.distribution_id
AND fdh1.book_type_code = P_DIST_SOURCE_BOOK
AND fdd1.asset_id = fdh1.asset_id
AND fds1.book_type_code = fds.book_type_code
AND fds1.asset_id = fds.asset_id
AND fdh1.code_combination_id = fdh.code_combination_id
AND fdh1.location_id = p_location_id_in
AND fds1.period_counter BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
);
SELECT fdd.deprn_reserve
INTO ln_amount
FROM fa_deprn_summary fds
,fa_deprn_detail fdd
,fa_distribution_history fdh
,fa_books fb
WHERE fds.book_type_code = fdd.book_type_code
AND fds.asset_id = fdd.asset_id
AND fds.period_counter = fdd.period_counter
AND fdd.distribution_id = fdh.distribution_id
AND fdh.book_type_code = P_DIST_SOURCE_BOOK
AND fdd.asset_id = fdh.asset_id
AND fds.book_type_code = P_BOOK_NAME
AND fds.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND fb.asset_id = fdd.asset_id
AND fb.book_type_code = fdd.book_type_code
AND fb.date_ineffective IS NULL
AND fb.period_counter_life_complete IS NOT NULL
AND fds.period_counter = (
SELECT MAX(fds1.period_counter)
FROM fa_deprn_summary fds1
,fa_deprn_detail fdd1
,fa_distribution_history fdh1
WHERE fds1.book_type_code = fdd1.book_type_code
AND fds1.asset_id = fdd1.asset_id
AND fds1.period_counter = fdd1.period_counter
AND fdd1.distribution_id = fdh1.distribution_id
AND fdh1.book_type_code = P_DIST_SOURCE_BOOK
AND fdd1.asset_id = fdh1.asset_id
AND fds1.book_type_code = fds.book_type_code
AND fds1.asset_id = fds.asset_id
AND fdh1.code_combination_id = fdh.code_combination_id
AND fdh1.location_id = p_location_id_in
AND fds1.period_counter < = gn_lex_begin_period_counter);
SELECT NVL(MAX(fdd.deprn_reserve),0)
INTO ln_amount
FROM fa_deprn_summary fds
,fa_deprn_detail fdd
,fa_distribution_history fdh
WHERE fds.book_type_code = fdd.book_type_code
AND fds.asset_id = fdd.asset_id
AND fds.period_counter = fdd.period_counter
AND fdd.distribution_id = fdh.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fdh.book_type_code = P_DIST_SOURCE_BOOK
AND fdd.asset_id = fdh.asset_id
AND fds.book_type_code = P_BOOK_NAME
AND fds.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND fds.period_counter = (
SELECT MAX(fds1.period_counter)
FROM fa_deprn_summary fds1
,fa_deprn_detail fdd1
,fa_distribution_history fdh1
WHERE fds1.book_type_code = fdd1.book_type_code
AND fds1.asset_id = fdd1.asset_id
AND fds1.period_counter = fdd1.period_counter
AND fdd1.distribution_id = fdh1.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fdh1.book_type_code = P_DIST_SOURCE_BOOK
AND fdd1.asset_id = fdh1.asset_id
AND fds1.book_type_code = fds.book_type_code
AND fds1.asset_id = fds.asset_id
AND fdh1.code_combination_id = fdh.code_combination_id
AND fdh1.location_id = p_location_id_in
AND fds1.period_counter BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
);
SELECT NVL(SUM(DECODE(fdd.deprn_source_code,'B',fdd.deprn_reserve,fdd.deprn_amount)),0)
INTO ln_amount
FROM fa_deprn_summary fds
,fa_deprn_detail fdd
,fa_distribution_history fdh
WHERE fds.book_type_code = fdd.book_type_code
AND fds.asset_id = fdd.asset_id
AND fds.period_counter = fdd.period_counter
AND fdd.distribution_id = fdh.distribution_id
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fdh.book_type_code = P_DIST_SOURCE_BOOK
AND fdd.asset_id = fdh.asset_id
AND fds.book_type_code = P_BOOK_NAME
AND fds.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in
AND fds.period_counter <= gn_lex_end_period_counter;
SELECT NVL(fdd.deprn_reserve,0)
INTO ln_amount
FROM fa_books fb
,fa_deprn_summary fds
,fa_deprn_detail fdd
,fa_distribution_history fdh
WHERE fb.book_type_code = P_BOOK_NAME
AND fb.date_ineffective IS NULL
AND fb.book_type_code = fds.book_type_code
AND fb.asset_id = fds.asset_id
AND fds.period_counter = gn_lex_begin_period_counter - 1
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fb.asset_id = p_asset_id_in
AND fds.book_type_code = fdd.book_type_code
AND fds.asset_id = fdd.asset_id
AND fds.period_counter = fdd.period_counter
AND fdd.distribution_id = fdh.distribution_id
AND fdh.book_type_code = P_DIST_SOURCE_BOOK
AND fdd.asset_id = fdh.asset_id
AND fdh.code_combination_id = p_ccid_in
AND fdh.location_id = p_location_id_in;
SELECT NVL(SUM(fdd.deprn_amount - fdd.deprn_adjustment_amount + NVL(fx.adjustment_amount,0)),0)
INTO ln_amount
FROM fa_deprn_detail fdd
,fa_distribution_history fdh
,(SELECT fadj.distribution_id
,fadj.period_counter_created
,SUM(DECODE(fth.transaction_subtype,'APPREC',0,
DECODE(fadj.debit_credit_flag,'CR',-1*fadj.adjustment_amount
,fadj.adjustment_amount))) adjustment_amount
FROM fa_adjustments fadj
,fa_transaction_headers fth
WHERE fadj.transaction_header_id = fth.transaction_header_id(+)
and fth.transaction_type_code IN ('ADJUSTMENT','ADDITION')
and fadj.adjustment_type(+) = 'EXPENSE'
and fadj.book_type_code = P_BOOK_NAME
AND fadj.asset_id = p_asset_id_in
and fadj.asset_id = fth.asset_id
and fadj.book_type_code = fth.book_type_code
group by fadj.distribution_id
,fadj.period_counter_created) fx
WHERE fdd.distribution_id = fx.distribution_id(+)
and fdd.period_counter = fx.period_counter_created(+)
and fdd.distribution_id = fdh.distribution_id
and fdd.period_counter BETWEEN gn_lex_begin_period_counter AND gn_lex_end_period_counter
AND fdd.book_type_code = P_BOOK_NAME
AND fdd.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fdh.location_id = p_location_id_in;
SELECT NVL(SUM(NVL(DECODE(fadj.debit_credit_flag, 'DR',(-1)*fadj.adjustment_amount,fadj.adjustment_amount), 0)),0) retirements
INTO ln_amount
FROM fa_books fb
,fa_transaction_headers fth
,fa_adjustments fadj
,fa_asset_history fah
,fa_distribution_history fdh
WHERE fth.asset_id = fah.asset_id
AND fth.asset_id = fdh.asset_id
AND fah.asset_type = p_asset_type_in
AND fdh.distribution_id = fadj.distribution_id
AND fah.category_id = p_category_id_in
AND fdh.transaction_header_id_in = p_transaction_header_id
AND fth.book_type_code = P_BOOK_NAME
AND fb.asset_id = p_asset_id_in
AND fdh.code_combination_id = p_ccid_in
AND fadj.transaction_header_id = fth.transaction_header_id
AND fth.transaction_type_code IN ('FULL RETIREMENT', 'PARTIAL RETIREMENT', 'REINSTATEMENT')
AND fadj.adjustment_type IN ('RESERVE')
AND fadj.source_type_code IN ('RETIREMENT', 'CIP RETIREMENT')
AND fadj.period_counter_adjusted <= gn_lex_end_period_counter
AND fth.transaction_date_entered <= NVL(gd_period_close_date,fth.transaction_date_entered)
AND fth.transaction_header_id >= fah.transaction_header_id_in
AND fth.transaction_header_id < NVL(fah.transaction_header_id_out, fth.transaction_header_id + 1)
AND fb.transaction_header_id_in = fth.transaction_header_id
AND fb.book_type_code = P_BOOK_NAME
AND fdh.location_id = p_location_id_in;
SELECT FSC.category_flex_structure
INTO gc_cat_flex_struc
FROM fa_system_controls FSC;
SELECT fsav.application_column_name
INTO lc_maj_seg
FROM fnd_segment_attribute_values fsav
WHERE fsav.id_flex_code = 'CAT#'
AND fsav.id_flex_num = gc_cat_flex_struc
AND fsav.attribute_value = 'Y'
AND fsav.segment_attribute_type = 'BASED_CATEGORY';
SELECT fsav.application_column_name
INTO lc_min_seg
FROM fnd_segment_attribute_values fsav
WHERE fsav.id_flex_code = 'CAT#'
AND fsav.id_flex_num = gc_cat_flex_struc
AND fsav.attribute_value = 'Y'
AND fsav.segment_attribute_type = 'MINOR_CATEGORY';
SELECT fifs.concatenated_segment_delimiter
INTO lc_separator
FROM fnd_id_flex_structures fifs
WHERE fifs.id_flex_num = gc_cat_flex_struc
AND fifs.id_flex_code = 'CAT#';
select substr(lc_maj_seg,8) into lc_maj_segnum from dual;
select substr(lc_min_seg,8) into lc_min_segnum from dual;
SELECT FDP.period_counter
,FDP.calendar_period_open_date
,fdp.period_open_date
INTO gn_lex_begin_period_counter
,gd_period_open_date
,gd_per_open_date
FROM fa_deprn_periods FDP
WHERE FDP.book_type_code = P_BOOK_NAME
AND FDP.period_name = P_BEGIN_PERIOD;
SELECT FDP.period_counter
,NVL(FDP.calendar_period_close_date,SYSDATE)
,NVL(fdp.period_close_date,SYSDATE)
INTO gn_lex_end_period_counter
,gd_period_close_date
,gd_per_close_date
FROM fa_deprn_periods FDP
WHERE FDP.book_type_code = P_BOOK_NAME
AND FDP.period_name = P_END_PERIOD;
SELECT FBC.book_class
,FBC.accounting_flex_structure
,FBC.set_of_books_id
,FBC.distribution_source_book
INTO gc_book_class
,gc_acct_flex_struc
,lc_ledger_id
,P_DIST_SOURCE_BOOK
FROM fa_book_controls FBC
WHERE FBC.book_type_code = P_BOOK_NAME;
SELECT GLED.name
,GLED.currency_code
INTO gc_ledger_name
,gc_currency_code
FROM gl_ledgers GLED
WHERE GLED.ledger_id = lc_ledger_id;