FA Asset Reclassification - draft

            SELECT
                --&ACCT_FLEX_BAL_SEG            COMP_CODE,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') D_COMP_CODE1,
                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,
                        fnd_flex_xml_publisher_apis.process_kff_combination_1('d_from_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_FROM_CATEGORY1,
                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,
                        fnd_flex_xml_publisher_apis.process_kff_combination_1('d_to_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_TO_CATEGORY1,
                AD.ASSET_NUMBER         ASSET_NUMBER,
                sum (DECODE(COST_ADJ.DEBIT_CREDIT_FLAG, 'DR', 1, 'CR', -1) *
                    COST_ADJ.ADJUSTMENT_AMOUNT) COST_ADJ,
                sum (DECODE(RES_ADJ.DEBIT_CREDIT_FLAG, 'DR', -1, 'CR', 1) *
                    NVL(RES_ADJ.ADJUSTMENT_AMOUNT, 0))  RES_ADJ,
                TH.TRANSACTION_HEADER_ID        TH_ID
            /*  ,fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE') d_comp_code,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('d_from_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_FROM_CATEGORY,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('d_to_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE') D_TO_CATEGORY*/
            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  AND
                START_DP.BOOK_TYPE_CODE     =  UPPER (:P_BOOK)              AND
                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.TRANSACTION_TYPE_CODE    =  'RECLASS'            AND
                TH.DATE_EFFECTIVE       >= DP.PERIOD_OPEN_DATE      AND
                TH.DATE_EFFECTIVE       <= NVL (DP.PERIOD_CLOSE_DATE, SYSDATE)
            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.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     =  :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.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 = :DIST_SOURCE_BOOK  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,
                fnd_flex_xml_publisher_apis.process_kff_combination_1('d_comp_code', 'SQLGL', 'GL#', dhcc.CHART_OF_ACCOUNTS_ID, NULL, dhcc.CODE_COMBINATION_ID, 'GL_BALANCING', 'Y', 'VALUE'),
                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,
                    fnd_flex_xml_publisher_apis.process_kff_combination_1('d_from_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
                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,
                        fnd_flex_xml_publisher_apis.process_kff_combination_1('d_to_category', 'OFA', 'CAT#', 101, NULL, fcat.CATEGORY_ID, 'ALL', 'Y', 'VALUE'),
                ad.asset_number,
                th.transaction_header_id
            ORDER BY
            /*  1,
                lpad(DECODE(FAH.ASSET_TYPE,
                                'CIP',FCB.CIP_COST_ACCT,
                                FCB.ASSET_COST_ACCT),25,'0'),
                AD.ASSET_NUMBER*/
            1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11