Asset transactions query

/* Formatted on 6/27/2013 4:35:36 PM (QP5 v5.114.809.3010) */
SELECT   bal.book_type_code asset_book_type,
         ad.description,
         bal.category_segment1,
         bal.category_segment2,
         bal.COST COST,
         bal.state,
         bal.county,
         bal.city,
         bal.depreciation_expense_account,
         bal.asset_clearing_account,
         bal.date_placed_in_service,
         ad.asset_number asset_number,
         fak.segment1 asset_key,
         ad.tag_number tag_number,
         ad.owned_leased,
         bal.deprn_reserve,
         ad.manufacturer_name,
         (bal.COST - bal.deprn_reserve) net_book_value,
         BAL.ytd_deprn,
         ad.attribute_category_code,
-- Added Asset Books Detail
         bal.method,
         bal.life_year,
         bal.months,
-- Added Asset By Books Detail
         ad.attribute1,
         ad.attribute2,
         ad.attribute3,
         ad.attribute4,
         ad.attribute5,
         ad.attribute6,
         ad.attribute7,
         ad.attribute8,
         ad.attribute9,
         ad.attribute10,
         ad.attribute11,
         ad.attribute12,
         ad.attribute13,
         ad.attribute14,
         ad.attribute15,
         ad.attribute16,
         ad.attribute17,
         ad.attribute18,
         ad.attribute19,
         ad.attribute20,
         ad.attribute21,
         ad.attribute22,
         ad.attribute23,
         ad.attribute24,
         ad.attribute25,
         ad.attribute26,
         ad.attribute27,
         ad.attribute28,
         ad.attribute29,
         ad.attribute30
  FROM   (SELECT   bk.date_placed_in_service,
                   bk.deprn_start_date,
                   dp.period_name,
                   bk.book_type_code,
                   dd.deprn_run_date,
                   dd.period_counter,
                   fl.segment1 state,
                   fl.segment2 county,
                   fl.segment3 city,
                   fl.attribute1 site,
                   fcb.segment1 category_segment1,
                   fcb.segment2 category_segment2,
                   fcb.segment3 category_segment3,
                   fcb.segment4 category_segment4,
                   fcb.segment5 category_segment5,
                      glcc1.segment1
                   || '-'
                   || glcc1.segment2
                   || '-'
                   || glcc1.segment3
                   || '-'
                   || glcc1.segment4
                   || '-'
                   || glcc1.segment5
                      asset_cost_account,
                      glcc2.segment1
                   || '-'
                   || glcc2.segment2
                   || '-'
                   || glcc2.segment3
                   || '-'
                   || glcc2.segment4
                   || '-'
                   || glcc2.segment5
                      asset_clearing_account,
                      glcc3.segment1
                   || '-'
                   || glcc3.segment2
                   || '-'
                   || glcc3.segment3
                   || '-'
                   || glcc3.segment4
                   || '-'
                   || glcc3.segment5
                      depreciation_expense_account,
                      glcc4.segment1
                   || '-'
                   || glcc4.segment2
                   || '-'
                   || glcc4.segment3
                   || '-'
                   || glcc4.segment4
                   || '-'
                   || glcc4.segment5
                      depreciation_reserve_account,
                   DECODE (ah.asset_type, 'CIP', NULL, cb.deprn_reserve_acct)
                      "ACCOUNT",
                   dd.deprn_source_code,
                   dh.asset_id,
                   dh.code_combination_id,
                   DECODE ('COST',
                           'COST',
                           cb.asset_cost_acct,
                           'CIP COST',
                           cb.cip_cost_acct,
                           'RESERVE',
                           cb.deprn_reserve_acct,
                           'REVAL RESERVE',
                           cb.reval_reserve_acct)
                      "GL_ACCOUNT",
                   DECODE ('COST',
                           'COST',
                           dd.COST,
                           'CIP COST',
                           dd.COST,
                           'RESERVE',
                           dd.deprn_reserve,
                           'REVAL RESERVE',
                           dd.reval_reserve)
                      "COST",
                   DECODE (dd.deprn_source_code,
                           'D', 'DEPRECIATION',
                           'ADDITION')
                      "DEPRECIATION",
                   NVL (dd.deprn_reserve, 0) "DEPRN_RESERVE",
                   NVL (
                      (SELECT   NVL (deprn_amount, 0)
                         FROM   fa_deprn_detail
                        WHERE   period_counter IN
                                      (SELECT   p1.period_counter
                                         FROM   fa_deprn_periods p1,
                                                fa_book_controls bc
                                        WHERE   1 = 1
                                                AND bc.book_type_code =
                                                      'USA CORP'
                                                AND p1.book_type_code =
                                                      'USA CORP'
                                 AND p1.period_name = :per_name )---'JAN-13')
                                AND asset_id = dh.asset_id
                                AND book_type_code = dh.book_type_code
                                AND distribution_id = dh.distribution_id),
                      0
                   )
                      deprn_amount,
                   (NVL (dd.COST, 0)) - (NVL (dd.deprn_reserve, 0))
                      net_book_value,
                   dd.ytd_deprn,
                   (SELECT   MAX (full_name)
                      FROM   per_all_people_f
                     WHERE   person_id = dh.assigned_to)
                      employee_name,
                   bk.recoverable_cost,
                   fcb.owned_leased,
                   bk.life_in_months,
                   (SELECT   fb.COST old_cost
                      FROM   fa_books fb, fa_transaction_headers th
                     WHERE       1 = 1
                             AND fb.asset_id = bk.asset_id
                             AND fb.book_type_code = 'USA CORP'
                             AND th.book_type_code = 'USA CORP'
                             AND th.transaction_type_code IN
                                      ('ADJUSTMENT', 'CIP ADJUSTMENT')
                             AND fb.transaction_header_id_out =
                                   th.transaction_header_id
                             AND th.date_effective BETWEEN dp.period_open_date
                                                       AND  NVL (
                                                               dp.period_close_date,
                                                               SYSDATE
                                                            )
                             AND ROWNUM = 1)
                      old_cost,
                   (SELECT   th.transaction_date_entered
                                transaction_date_entered
                      FROM   fa_books fb, fa_transaction_headers th
                     WHERE       1 = 1
                             AND fb.asset_id = bk.asset_id
                             AND fb.book_type_code = 'USA CORP'
                             AND th.book_type_code = 'USA CORP'
                             AND th.transaction_type_code IN
                                      ('ADJUSTMENT', 'CIP ADJUSTMENT')
                             AND fb.transaction_header_id_out =
                                   th.transaction_header_id
                             AND th.date_effective BETWEEN dp.period_open_date
                                                       AND  NVL (
                                                               dp.period_close_date,
                                                               SYSDATE
                                                            )
                             AND ROWNUM = 1)
                      transaction_date_entered,
-- Added By Asset Books Detail
                      bk.deprn_method_code method,
                      (life_in_months/12) life_year,
                      mod(life_in_months,12) months
   -- Added By Asset Books Detail      
   FROM   fa_books bk,
                   fa_category_books cb,
                   fa_asset_history ah,
                   fa_deprn_detail dd,
                   fa_distribution_history dh,
                   fa_deprn_periods dp,
                   fa_locations fl,
                   fa_categories_b fcb,
                   gl_code_combinations glcc1,
                   fa_distribution_accounts da,
                   gl_code_combinations glcc2,
                   gl_code_combinations glcc3,
                   gl_code_combinations glcc4
           WHERE       1 = 1
                   AND fcb.category_id = ah.category_id
                   AND fl.location_id = dh.location_id
                   AND dp.book_type_code = cb.book_type_code
                   AND dh.book_type_code || '' = 'USA CORP'
                   AND dd.asset_id = dh.asset_id + 0
                   AND dd.book_type_code = 'USA CORP'
                   AND dd.distribution_id = dh.distribution_id + 0
                   AND da.distribution_id = dh.distribution_id
                   AND dd.period_counter =
                         (SELECT   MAX (sub_dd.period_counter)
                            FROM   fa_deprn_detail sub_dd
                           WHERE   sub_dd.book_type_code = 'USA CORP'
                                   AND sub_dd.distribution_id =
                                         dh.distribution_id + 0
                                   AND sub_dd.period_counter <=
                                         (SELECT   p2.period_counter
                                            FROM   fa_deprn_periods p2,
                                                   fa_book_controls bc
                                           WHERE   1 = 1
                                                   AND bc.book_type_code =
                                                         'USA CORP'
                                                   AND p2.book_type_code =
                                                         'USA CORP'
                                                   AND p2.period_name = :per_name ))--'MAY-13'))                                                
                   AND ah.asset_id = dh.asset_id + 0
                   AND ( (ah.asset_type != 'EXPENSED'
                          AND 'COST' IN ('COST', 'CIP COST'))
                        OR (ah.asset_type = 'CAPITALIZED'
                            AND 'RESERVE' IN ('RESERVE', 'REVAL RESERVE')))
                   AND DECODE (dd.deprn_source_code,
                               'D', dp.period_close_date,
                               SYSDATE) BETWEEN ah.date_effective
                                            AND  NVL (ah.date_ineffective,
                                                      SYSDATE)
                   AND dd.deprn_source_code = 'D'
                   AND cb.category_id = ah.category_id
                   AND cb.book_type_code = 'USA CORP'
                   AND bk.book_type_code = 'USA CORP'
                   AND bk.asset_id = dd.asset_id
                   AND glcc1.code_combination_id(+) =
                         da.asset_cost_account_ccid
                   AND glcc2.code_combination_id(+) =
                         da.asset_clearing_account_ccid
                   AND glcc3.code_combination_id(+) =
                         da.deprn_expense_account_ccid
                   AND glcc4.code_combination_id(+) =
                         da.deprn_reserve_account_ccid
                   AND DECODE (dd.deprn_source_code, 'D', SYSDATE, SYSDATE) BETWEEN bk.date_effective
                                                                                AND  NVL (
                                                                                        bk.date_ineffective,
                                                                                        SYSDATE
                                                                                     )
                   AND DECODE (
                         'COST',
                         'COST',
                         DECODE (ah.asset_type,
                                 'CAPITALIZED', cb.asset_cost_acct,
                                 NULL),
                         'CIP COST',
                         DECODE (ah.asset_type,
                                 'CIP', cb.cip_cost_acct,
                                 NULL),
                         'RESERVE',
                         'RESERVE',
                         cb.deprn_reserve_acct,
                         'REVAL RESERVE',
                         cb.reval_reserve_acct
                      ) IS NOT NULL
                   AND TO_DATE (dp.period_name, 'MM-YY') = TO_DATE (:per_name, 'MM-YY')  -- MAY-13 
                   AND (dp.period_close_date BETWEEN dh.date_effective
                                                 AND  dh.date_ineffective
                        OR dh.date_ineffective IS NULL)
                   AND (NVL (bk.period_counter_fully_retired, 0) = 0
                        OR bk.period_counter_fully_retired >
                             dd.period_counter)) bal,
         fa_additions ad,
         fa_asset_keywords fak,
         gl_code_combinations dhcc
 WHERE       ad.asset_id = bal.asset_id
         AND dhcc.code_combination_id = bal.code_combination_id
         -- AND dhcc.segment1 IN ('1018', '1026')
          -- and asset_number in ('37629')
         AND fak.code_combination_id(+) = ad.asset_key_ccid