ORACLE MASTERMINDS

Asset Retirements Report

Description

Asset Retirements Report is used to review the assets you retired for the Book and accounting Period range you choose. The report is sorted by balancing segment, asset type, asset account, cost center, and asset number. It prints totals for each cost center, account, asset type, and balancing segment.

For your asset cost accounts, this report provides supporting detail for the Cost Detail Report and the Reserve Detail Report. For your CIP cost accounts, this report provides supporting detail for the CIP Detail Report. Retirements in the current period do not show up on this report until you use the Calculate Gains and Losses form to process retirements.

SELECT   /*+ ordered */

         falu.meaning asset_type,

         DECODE (ah.asset_type,'CIP', cb.cip_cost_acct,cb.asset_cost_acct) ACCOUNT,

         ad.asset_number, ret.date_retired, ad.description asset_num_desc,

         th.transaction_type_code, th.asset_id, books.date_placed_in_service,

         SUM (  DECODE (aj.adjustment_type, 'COST', 1, 'CIP COST', 1, 0)

              * DECODE (aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)

              * aj.adjustment_amount

             ) COST,

         SUM (  DECODE (aj.adjustment_type, 'NBV RETIRED', -1, 0)

              * DECODE (aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)

              * aj.adjustment_amount

             ) nbv,

         SUM (  DECODE (aj.adjustment_type,

                        'PROCEEDS CLR', 1,

                        'PROCEEDS', 1,

                        0

                       )

              * DECODE (aj.debit_credit_flag, 'DR', 1, 'CR', -1, 0)

              * aj.adjustment_amount

             ) proceeds,

         SUM (  DECODE (aj.adjustment_type, 'REMOVALCOST', -1, 0)

              * DECODE (aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)

              * aj.adjustment_amount

             ) removal,

         SUM (  DECODE (aj.adjustment_type, 'REVAL RSV RET', 1, 0)

              * DECODE (aj.debit_credit_flag, 'DR', -1, 'CR', 1, 0)

              * aj.adjustment_amount

             ) reval_rsv_ret,

         th.transaction_header_id,

         DECODE (th.transaction_type_code,

                 'REINSTATEMENT', '*',

                 'PARTIAL RETIREMENT', 'P',

                 TO_CHAR (NULL)

                ) code,

         dhcc.segment4 product, dhcc.segment2 district,

         fac.segment1 major_category, ad.attribute2 cer_number

    FROM fa_transaction_headers th,

         fa_additions ad,

         fa_books books,

         fa_retirements ret,

         fa_adjustments aj,

         fa_distribution_history dh,

         gl_code_combinations dhcc,

         fa_asset_history ah,

         fa_category_books cb,

         fa_lookups falu,

         fa_categories fac

   WHERE ad.asset_category_id = fac.category_id

     AND th.date_effective >= :period1_pod

     AND th.date_effective <= :period2_pcd

     AND th.book_type_code = :p_book

     AND th.transaction_key = 'R'

     AND ret.book_type_code = :p_book

     AND ret.asset_id = books.asset_id

     AND DECODE (th.transaction_type_code,

                 'REINSTATEMENT', ret.transaction_header_id_out,

                 ret.transaction_header_id_in

                ) = th.transaction_header_id

     AND ad.asset_id = th.asset_id

     AND aj.asset_id = ret.asset_id

     AND aj.book_type_code = :p_book

     AND aj.adjustment_type NOT IN (

            SELECT 'PROCEEDS'

              FROM fa_adjustments aj1

             WHERE aj1.book_type_code = aj.book_type_code

               AND aj1.asset_id = aj.asset_id

               AND aj1.transaction_header_id = aj.transaction_header_id

               AND aj1.adjustment_type = 'PROCEEDS CLR')

     AND aj.transaction_header_id = th.transaction_header_id

     AND ah.asset_id = ad.asset_id

     AND ah.date_effective <= th.date_effective

     AND NVL (ah.date_ineffective, th.date_effective + 1) > th.date_effective

     AND falu.lookup_code = ah.asset_type

     AND falu.lookup_type = 'ASSET TYPE'

     AND books.transaction_header_id_out = th.transaction_header_id

     AND books.book_type_code = :p_book

     AND books.asset_id = ad.asset_id

     AND cb.category_id = ah.category_id

     AND cb.book_type_code = :p_book

     AND dh.distribution_id = aj.distribution_id

     AND th.asset_id = dh.asset_id

     AND dhcc.code_combination_id = dh.code_combination_id

GROUP BY falu.meaning,

         th.transaction_type_code,

         th.asset_id,

         cb.asset_cost_acct,

         cb.cip_cost_acct,

         ad.asset_number,

         ad.description,

         books.date_placed_in_service,

         ret.date_retired,

         th.transaction_header_id,

         ah.asset_type,

         ret.gain_loss_amount,

         dhcc.segment4,

         dhcc.segment2,

         fac.segment1,

         ad.attribute2

ORDER BY 1, 2, 3, 4, 5, 6;

By

Deepak J