FA Revalued Asset Retirements - draft

➜ Back to Library
Description
Categories: BI Publisher
Application: Assets
Source: Revalued Asset Retirements Report (XML) - Not Supported: Reserved For Future Use
Short Name: FASRREV_XML
DB package: FA_FASRREV_XMLP_PKG
SELECT
        null                             comp_code,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') comp_code_dsp,
        cb.asset_cost_acct                                      account,
        ad.asset_number                                         asset,
        Round(sum(decode(ret.units, NUll,
        nvl(ret.proceeds_of_sale,0.00)
        * (dh.units_assigned /ah.units),
        nvl(ret.proceeds_of_sale,0.00)
        * abs(dh.transaction_units) / ret.units)), :PRECISION)
                                                                proceeds,
        Round(sum(decode(ret.units, NUll,
        ret.cost_retired * (dh.units_assigned /ah.units),
        ret.cost_retired * abs(dh.transaction_units) / ret.units)), :PRECISION)
                                                                cost,
        Round(sum(decode(ret.units, NUll,
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)
        * (dh.units_assigned /ah.units),
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)
        * abs(dh.transaction_units) / ret.units)), :PRECISION)
                                                                recoverable,
        decode(greatest(months_between(ret.date_retired,
        books.date_placed_in_service)/12, 1), 1, 1,
        decode(greatest(piv2.price_index_value/
        piv.price_index_value, 1), 1, 1, piv2.price_index_value/
        piv.price_index_value))
                                                                ifactor,
        Round(sum((decode(ret.units, NUll,
        ret.cost_retired * (dh.units_assigned /ah.units),
        ret.cost_retired * abs(dh.transaction_units) / ret.units)) *
        (decode(greatest(months_between(ret.date_retired,
        books.date_placed_in_service)/12, 1), 1, 1,
        decode(greatest(piv2.price_index_value/
        piv.price_index_value, 1), 1, 1, piv2.price_index_value/
        piv.price_index_value))) + ret.cost_of_removal), :PRECISION)
                                                                icbase,
        Round(sum(decode(ret.units, NULL,
        least(ret.cost_retired + ret.cost_of_removal,
        greatest(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
        / ret.cost_retired,ret.nbv_retired +
        ret.cost_of_removal))
        * (dh.units_assigned /ah.units),
        least(ret.cost_retired + ret.cost_of_removal,
        greatest(ret.proceeds_of_sale
        * (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
        / ret.cost_retired,ret.nbv_retired +
        ret.cost_of_removal))
        * abs(dh.transaction_units) / ret.units)), :PRECISION)
                                                                rcbase,
        Round(sum(decode(ret.units, NULL,
        greatest(0, least(ret.proceeds_of_sale
        * (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
        / ret.cost_retired - ret.nbv_retired -
        ret.cost_of_removal, ret.cost_retired - ret.nbv_retired))
        * (dh.units_assigned /ah.units),
        greatest(0, least(ret.proceeds_of_sale
        * (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost )
        / ret.cost_retired - ret.nbv_retired -
        ret.cost_of_removal, ret.cost_retired - ret.nbv_retired))
        * abs(dh.transaction_units) / ret.units)), :PRECISION)
                                                oincome,
        Round(sum(decode(sign(
        decode(ret.units, NUll,
        nvl(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,0.00)
        * (dh.units_assigned /ah.units),
        nvl(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,0.00)
        * abs(dh.transaction_units) / ret.units) -
        decode(ret.units, NULL,
        least(ret.cost_retired + ret.cost_of_removal,
        greatest(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,
        ((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
        (ret.cost_retired  -  ret.nbv_retired  )) +
        ret.cost_of_removal))
        * (dh.units_assigned /ah.units),
        least(ret.cost_retired + ret.cost_of_removal,
        greatest(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,
        ((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
        (ret.cost_retired  -  ret.nbv_retired  )) +
        ret.cost_of_removal))
        * abs(dh.transaction_units) / ret.units)), -1,
        (decode(ret.units, NUll,
        nvl(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,0.00)
        * (dh.units_assigned /ah.units),
        nvl(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,0.00)
        * abs(dh.transaction_units) / ret.units) -
        decode(ret.units, NULL,
        least(ret.cost_retired + ret.cost_of_removal,
        greatest(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,
        ((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
        (ret.cost_retired  -  ret.nbv_retired  )) +
        ret.cost_of_removal))
        * (dh.units_assigned /ah.units),
        least(ret.cost_retired + ret.cost_of_removal,
        greatest(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,
        ((books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost ) -
        (ret.cost_retired  -  ret.nbv_retired  )) +
        ret.cost_of_removal))
        * abs(dh.transaction_units) / ret.units)),
        greatest(0,(decode(ret.units, NUll,
        nvl(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,0.00)
        * (dh.units_assigned /ah.units),
        nvl(ret.proceeds_of_sale *
        (books.adjusted_recoverable_cost - books_new.adjusted_recoverable_cost)  /
        ret.cost_retired,0.00)
        * abs(dh.transaction_units) / ret.units) -
        (decode(ret.units, NUll,
        ret.cost_retired * (dh.units_assigned /ah.units),
        ret.cost_retired * abs(dh.transaction_units) / ret.units)) *
        (decode(greatest(months_between(ret.date_retired,
        books.date_placed_in_service)/12, 1), 1, 1,
        decode(greatest(piv2.price_index_value/
        piv.price_index_value, 1), 1, 1, piv2.price_index_value/
        piv.price_index_value))) + ret.cost_of_removal)))), :PRECISION)
                                                        gainloss
FROM
        fa_retirements                          ret,
        fa_books                                books,
        fa_books                                books_new,
        fa_transaction_headers                  th,
        fa_additions                            ad,
        fa_asset_history                        ah,
        fa_category_books                       cb,
        fa_category_book_defaults               cbd,
        fa_price_index_values                   piv,
        fa_price_index_values                   piv2,
        fa_price_indexes                        pdx,
        fa_distribution_history                 dh,
        gl_code_combinations                    dhcc
WHERE
        th.date_effective        >= :Period1_POD AND
        th.date_effective        <= :Period2_PCD AND
        th.book_type_code = :P_BOOK                            AND
        (th.transaction_type_code = 'FULL RETIREMENT'           OR
        th.transaction_type_code = 'PARTIAL RETIREMENT')
AND
        ret.transaction_header_id_in = th.transaction_header_id
AND
        piv.price_index_id      = pdx.price_index_id            and
        piv2.price_index_id     = pdx.price_index_id            and
        piv.from_date           <= books.date_placed_in_service and
        piv.to_date             >= books.date_placed_in_service and
        piv2.from_date          <= ret.date_retired             and
        piv2.to_date            >= ret.date_retired
AND
        ad.asset_id = th.asset_id
AND
        books.transaction_header_id_out = th.transaction_header_id      AND
        books.book_type_code = :P_BOOK                                 AND
        books.date_placed_in_service >= to_date('1985/09/20','YYYY/MM/DD') AND
        books.asset_id = th.asset_id
AND
        books_new.transaction_header_id_in = th.transaction_header_id
AND
        cb.category_id = ah.category_id                                 AND
        cbd.price_index_name    = pdx.price_index_name                  AND
        cbd.book_type_code      = cb.book_type_code                     AND
        cbd.category_id         = cb.category_id                        AND
        books.date_placed_in_service between cbd.start_dpis
                                     and nvl(cbd.end_dpis,sysdate)      AND
        cb.book_type_code = :P_BOOK
AND
        ah.asset_id = ad.asset_id                               AND
        ah.date_effective <= th.date_effective                  AND
        (ah.date_ineffective is null                            OR
        ah.date_ineffective > th.date_effective)
AND
        dh.book_type_code = :Distribution_Source_Book
and
        dh.asset_id = th.asset_id                               AND
        (dh.retirement_id = ret.retirement_id
or      (ret.date_effective >= dh.date_effective       and
        ret.date_effective <= nvl(dh.date_ineffective,sysdate)
        and ret.units is null))
AND
        dhcc.code_combination_id = dh.code_combination_id
GROUP BY
        null,
		fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
        cb.asset_cost_acct,
        ad.asset_number,
        decode(greatest(months_between(ret.date_retired,
        books.date_placed_in_service)/12, 1), 1, 1,
        decode(greatest(piv2.price_index_value/
        piv.price_index_value, 1), 1, 1, piv2.price_index_value/
        piv.price_index_value))
ORDER BY fnd_flex_xml_publisher_apis.process_kff_combination_1('comp_code_dsp', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
        cb.asset_cost_acct,
        lpad(ad.asset_number,15,0)
Parameter NameSQL textValidation
Book
 
LOV Oracle
From Period
 
LOV Oracle
To Period
 
LOV Oracle
Download
Blitz Report™