FA - Query to Get Fixed Asset Reclassification Details

  SELECT                           --ACCT_FLEX_BAL_SEG              COMP_CODE,

        th.book_type_code,

         dhcc.segment1 comp_code,

         DECODE (fah.asset_type, 'CIP', fcb.cip_cost_acct, fcb.asset_cost_acct)

            fr_as_account,

         DECODE (fah.asset_type, 'CIP', ' ', fcb.deprn_reserve_acct)

            fr_re_account,

         ---CAT_FLEX_ALL_SEG_FR                 FR_CATEGORY,

         fcat.segment1 fr_category,

         DECODE (tah.asset_type, 'CIP', tcb.cip_cost_acct, tcb.asset_cost_acct)

            to_as_account,

         DECODE (tah.asset_type, 'CIP', ' ', tcb.deprn_reserve_acct)

            to_re_account,

         ---CAT_FLEX_ALL_SEG_TO                TO_CATEGORY,

         tcat.segment1 to_category,

         ad.asset_number asset_number,

         AVG (

            DECODE (cost_adj.debit_credit_flag,  'DR', 1,  'CR', -1)

            * cost_adj.adjustment_amount)

            cost_adj,

         AVG (

            DECODE (res_adj.debit_credit_flag,  'DR', -1,  'CR', 1)

            * NVL (res_adj.adjustment_amount, 0))

            res_adj,

         th.transaction_header_id th_id,

         th.transaction_date_entered,

         th.asset_id,

         apps_fa_util_api.

          get_deprn_period_name_by_date (th.book_type_code, th.date_effective)

            trans_period_name

    FROM fa_deprn_periods dp,

         fa_deprn_periods start_dp,

         fa_deprn_periods end_dp,

         fa_additions ad,

         gl_code_combinations dhcc,

         fa_categories fcat,

         fa_categories tcat,

         fa_category_books fcb,

         fa_category_books tcb,

         fa_transaction_headers th,

         fa_adjustments cost_adj,

         fa_adjustments res_adj,

         fa_asset_history fah,

         fa_asset_history tah,

         fa_distribution_history dh

   WHERE                                   --start_dp.period_name = :p_period1

             --AND end_dp.period_name = :p_period2

             -- start_dp.book_type_code = UPPER (:p_book)

             end_dp.book_type_code = start_dp.book_type_code

         AND dp.book_type_code = start_dp.book_type_code

         AND dp.period_counter >= start_dp.period_counter

         AND dp.period_counter <=

                NVL (end_dp.period_counter, dp.period_counter)

         ---AND th.book_type_code = :p_book

         AND th.book_type_code = dp.book_type_code

         AND th.transaction_type_code = 'RECLASS'

         AND th.date_effective >= dp.period_open_date

         AND th.date_effective <= NVL (dp.period_close_date, SYSDATE)

         --AND fah.book_type_code = th.book_type_code

         AND ad.asset_id = th.asset_id

         AND fah.asset_id = th.asset_id

         AND fah.date_ineffective = th.date_effective

         AND tah.asset_id = th.asset_id

         AND tah.date_effective = th.date_effective

         AND tcat.category_id = tah.category_id

         AND fcat.category_id = fah.category_id

         ---AND tcb.book_type_code = UPPER (:p_book)

         --AND tcb.book_type_code = tah.book_type_code

         AND tcb.book_type_code = fcb.book_type_code

         AND tcb.category_id = tah.category_id

         ---AND fcb.book_type_code = UPPER (:p_book)

         AND fcb.category_id = fah.category_id

         AND cost_adj.transaction_header_id = th.transaction_header_id

         AND cost_adj.book_type_code = th.book_type_code

         ---AND cost_adj.book_type_code = :p_book

         AND cost_adj.source_type_code = 'RECLASS'

         AND cost_adj.adjustment_type IN ('COST', 'CIP COST')

         AND cost_adj.period_counter_created >= start_dp.period_counter

         AND cost_adj.period_counter_created <=

                NVL (end_dp.period_counter, dp.period_counter)

         AND res_adj.transaction_header_id(+) = cost_adj.transaction_header_id

         AND res_adj.asset_id(+) = cost_adj.asset_id

         AND res_adj.distribution_id(+) = cost_adj.distribution_id

         ---AND res_adj.book_type_code(+) = UPPER (:p_book)

         AND res_adj.book_type_code(+) = cost_adj.book_type_code

         AND res_adj.source_type_code(+) = 'RECLASS'

         AND res_adj.adjustment_type(+) = 'RESERVE'

         AND res_adj.period_counter_created(+) =

                cost_adj.period_counter_created

         AND res_adj.adjustment_amount(+) != 0

         --AND dh.book_type_code = :p_book

         AND dh.book_type_code = th.book_type_code

         AND dh.asset_id = th.asset_id

         AND dh.transaction_header_id_in =

                NVL (th.source_transaction_header_id, th.transaction_header_id)

         AND dh.distribution_id = cost_adj.distribution_id

         AND dhcc.code_combination_id = dh.code_combination_id

GROUP BY                                                  --ACCT_FLEX_BAL_SEG,

        th.book_type_code,

         dhcc.segment1,

         DECODE (fah.asset_type,

                 'CIP', fcb.cip_cost_acct,

                 fcb.asset_cost_acct),

         DECODE (fah.asset_type, 'CIP', ' ', fcb.deprn_reserve_acct),

         --CAT_FLEX_ALL_SEG_FR,

         fcat.segment1,

         DECODE (tah.asset_type,

                 'CIP', tcb.cip_cost_acct,

                 tcb.asset_cost_acct),

         DECODE (tah.asset_type, 'CIP', ' ', tcb.deprn_reserve_acct),

         --CAT_FLEX_ALL_SEG_TO,

         tcat.segment1,

         ad.asset_number,

         th.transaction_header_id,

         th.transaction_date_entered,

         th.asset_id,

         apps_fa_util_api.

          get_deprn_period_name_by_date (th.book_type_code,

                                         th.date_effective)

ORDER BY 1,

         LPAD (

            DECODE (fah.asset_type,

                    'CIP', fcb.cip_cost_acct,

                    fcb.asset_cost_acct),

            25,

            '0'),

         ad.asset_number;