Monday 2 January 2017

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

No comments:

Post a Comment