FA to Gl Link

/* Formatted on 2013/06/12 12:20 (Formatter Plus v4.8.8) */
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,
       ---dep_expense_account_r12,
        bal.asset_clearing_account,
       ---asset_clearing_account_r12,
       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,
       ad.attribute_category_code, 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,
--               (SELECT    xgl.new_segment1
--                       || '-'
--                       || xgl.new_segment2
--                       || '-'
--                       || xgl.new_segment3
--                       || '-'
--                       || xgl.new_segment4
--                       || '-'
--                       || xgl.new_segment5
--                       || '-'
--                       || xgl.new_segment6
--                       || '-'
--                       || xgl.new_segment7
--                       || '-'
--                       || xgl.new_segment8
--                  FROM xxmtz.xxmtz_gl_code_comb_mapp xgl
--                 WHERE new_coa = 'MAS_US_GL_ACCT_KFF'
--                   AND old_ccid = glcc1.code_combination_id)
--                                                       asset_cost_account_r12,
                  glcc2.segment1
               || '-'
               || glcc2.segment2
               || '-'
               || glcc2.segment3
               || '-'
               || glcc2.segment4
               || '-'
               || glcc2.segment5 asset_clearing_account,
--               (SELECT    xgl.new_segment1
--                       || '-'
--                       || xgl.new_segment2
--                       || '-'
--                       || xgl.new_segment3
--                       || '-'
--                       || xgl.new_segment4
--                       || '-'
--                       || xgl.new_segment5
--                       || '-'
--                       || xgl.new_segment6
--                       || '-'
--                       || xgl.new_segment7
--                       || '-'
--                       || xgl.new_segment8
--                  FROM xxmtz.xxmtz_gl_code_comb_mapp xgl
--                 WHERE new_coa = 'MAS_US_GL_ACCT_KFF'
--                   AND old_ccid = glcc2.code_combination_id)
--                                                   asset_clearing_account_r12,
                  glcc3.segment1
               || '-'
               || glcc3.segment2
               || '-'
               || glcc3.segment3
               || '-'
               || glcc3.segment4
               || '-'
               || glcc3.segment5 depreciation_expense_account,
--               (SELECT    xgl.new_segment1
--                       || '-'
--                       || xgl.new_segment2
--                       || '-'
--                       || xgl.new_segment3
--                       || '-'
--                       || xgl.new_segment4
--                       || '-'
--                       || xgl.new_segment5
--                       || '-'
--                       || xgl.new_segment6
--                       || '-'
--                       || xgl.new_segment7
--                       || '-'
--                       || xgl.new_segment8
--                  FROM xxmtz.xxmtz_gl_code_comb_mapp xgl
--                 WHERE new_coa = 'MAS_US_GL_ACCT_KFF'
--                   AND old_ccid = glcc3.code_combination_id)
--                                                      dep_expense_account_r12,
                  glcc4.segment1
               || '-'
               || glcc4.segment2
               || '-'
               || glcc4.segment3
               || '-'
               || glcc4.segment4
               || '-'
               || glcc4.segment5 depreciation_reserve_account,
--               (SELECT    xgl.new_segment1
--                       || '-'
--                       || xgl.new_segment2
--                       || '-'
--                       || xgl.new_segment3
--                       || '-'
--                       || xgl.new_segment4
--                       || '-'
--                       || xgl.new_segment5
--                       || '-'
--                       || xgl.new_segment6
--                       || '-'
--                       || xgl.new_segment7
--                       || '-'
--                       || xgl.new_segment8
--                  FROM xxmtz.xxmtz_gl_code_comb_mapp xgl
--                 WHERE new_coa = 'MAS_US_GL_ACCT_KFF'
--                   AND old_ccid = glcc4.code_combination_id)
--                                                      dep_reserve_account_r12,
               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
          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 = 'JAN-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 ('JAN-13', 'MM-YY')
           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 ('55188','36870','34191')
   AND fak.code_combination_id(+) = ad.asset_key_ccid