JL Mexican Fixed Assets ISR - draft

SELECT
    &C_ALL_SEGS ASSET_CATEGORY,
    FADD.ASSET_CATEGORY_ID ASSET_CAT_ID, 
    FDS.ASSET_ID ASSET_ID,
    BKS.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN,
    BKS.TRANSACTION_HEADER_ID_OUT TRANSACTION_HEADER_ID_OUT,
    FADD.ASSET_NUMBER ASSET_NUMBER,
    FADD.DESCRIPTION DESCRIPTION,
    BKS.DATE_PLACED_IN_SERVICE ACQDATE,
    BKS.PRORATE_DATE PRORATE_DATE,
    TO_NUMBER(NULL) RETIREMENT_ID,
    NVL( BKS.COST,0) ORIGINAL_COST,
    NVL( FDS.DEPRN_RESERVE,0) DEPRN_RESERVE,
    FDS.PERIOD_COUNTER,
    BKS.PERIOD_COUNTER_FULLY_RESERVED,
   'N' STATUS,
    0   RETIREMENT_ID_ORDER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_segs', 'OFA', 'CAT#', 101, NULL, ct.CATEGORY_ID, 'ALL', 'Y', 'DESCRIPTION') cat_flex_all_segs, 
	JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('N', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, TO_NUMBER ( NULL ), NVL ( BKS.COST , 0 )) C_ACCUM_DEPRN, 
	JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL )) C_ACCUM_DEPRN_PREV_YR, 
	JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('N', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, TO_NUMBER ( NULL ), NVL ( BKS.COST , 0 )), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL ))) C_ACCUM_DEPRN_CURR_YR, 
	JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE) C_PRICE_INDEX, 
	JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE) C_INDEX_VALUE, 
	JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(TO_NUMBER ( NULL ), BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) C_INDEX_VALUE_HALF_PERIOD, 
	JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(TO_NUMBER ( NULL ), BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)) C_CORR_FACTOR, 
	JL_JLMXFISR_XMLP_PKG.c_adj_accum_deprnformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('N', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, TO_NUMBER ( NULL ), NVL ( BKS.COST , 0 )), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL ))),JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(TO_NUMBER ( NULL ), BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)), FDS.ASSET_ID, BKS.TRANSACTION_HEADER_ID_IN, BKS.TRANSACTION_HEADER_ID_OUT, FADD.DESCRIPTION, FADD.ASSET_CATEGORY_ID, FADD.ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE, BKS.PRORATE_DATE, TO_NUMBER ( NULL ),JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE), NVL ( BKS.COST , 0 ),JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL ( BKS.COST , 0 ), TO_NUMBER ( NULL )), BKS.PERIOD_COUNTER_FULLY_RESERVED) C_ADJ_ACCUM_DEPRN, 
	JL_JLMXFISR_XMLP_PKG.cf_retirement_date(TO_NUMBER ( NULL )) CF_RETIREMENT_DATE
FROM   FA_CATEGORIES CT,
       FA_ADDITIONS FADD,
       &lp_fa_books BKS,
       &lp_fa_deprn_summary FDS, 
       FA_TRANSACTION_HEADERS TRH
 WHERE FDS.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
  AND BKS.BOOK_TYPE_CODE    = FDS.BOOK_TYPE_CODE
  AND BKS.ASSET_ID                    = FDS.ASSET_ID
  AND BKS.TRANSACTION_HEADER_ID_IN = TRH.TRANSACTION_HEADER_ID
  AND TRH.TRANSACTION_TYPE_CODE <> 'FULL RETIREMENT'
  AND BKS.TRANSACTION_HEADER_ID_IN =   (SELECT MAX(BKS1.TRANSACTION_HEADER_ID_IN)
                                                        FROM    &lp_fa_books BKS1,
                                                                FA_TRANSACTION_HEADERS TRH1
                                                        WHERE  BKS1.BOOK_TYPE_CODE = BKS.BOOK_TYPE_CODE
                                                        AND    BKS1.ASSET_ID  =  BKS.ASSET_ID
                                                        AND    BKS1.TRANSACTION_HEADER_ID_IN = TRH1.TRANSACTION_HEADER_ID
                                                        AND    TRH1.TRANSACTION_DATE_ENTERED <= :C_FISCAL_END_DATE)
  AND BKS.DATE_PLACED_IN_SERVICE <= :C_FISCAL_END_DATE
  AND FADD.ASSET_ID =  BKS.ASSET_ID  
  AND FADD.ASSET_TYPE = 'CAPITALIZED'
  AND FDS.PERIOD_COUNTER >= (SELECT  MIN(FDP2.PERIOD_COUNTER)
                                                    FROM     FA_ASSET_HISTORY FAH2,
                                                                  FA_DEPRN_PERIODS FDP2
                                                    WHERE   FAH2.ASSET_ID              = FADD.ASSET_ID
                                                    AND       FAH2.ASSET_TYPE          = 'CAPITALIZED'
                                                    AND       FDP2.FISCAL_YEAR         <= :P_CURR_FY
                                                    AND       FDP2.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
                                                    AND       FAH2.DATE_EFFECTIVE BETWEEN FDP2.PERIOD_OPEN_DATE
                                                                                                    AND           FDP2.PERIOD_CLOSE_DATE)
  AND CT.CATEGORY_ID = FADD.ASSET_CATEGORY_ID
  AND FDS.PERIOD_COUNTER = (SELECT MAX(PERIOD_COUNTER)
                                          FROM &lp_fa_deprn_summary FDS2
                                          WHERE FDS2.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE
                                           AND     FDS2.ASSET_ID                 = FDS.ASSET_ID
                                           AND    FDS2.PERIOD_COUNTER >=  decode(:p_include_zero_nbv_assets,'Y',fds2.period_counter,:C_MIN_PERIOD_COUNTER)
                                           AND    FDS2.PERIOD_COUNTER <= :C_MAX_PERIOD_COUNTER)
UNION
SELECT
    &C_ALL_SEGS ASSET_CATEGORY,
    FADD.ASSET_CATEGORY_ID ASSET_CAT_ID, 
    FDS.ASSET_ID ASSET_ID,
    BKS.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID_IN,
    BKS.TRANSACTION_HEADER_ID_OUT TRANSACTION_HEADER_ID_OUT,
    FADD.ASSET_NUMBER ASSET_NUMBER,
    FADD.DESCRIPTION DESCRIPTION,
    BKS.DATE_PLACED_IN_SERVICE ACQDATE,
    BKS.PRORATE_DATE PRORATE_DATE,
    BKS.RETIREMENT_ID RETIREMENT_ID,
    NVL( RET.COST_RETIRED,0) ORIGINAL_COST,
    NVL( FDS.DEPRN_RESERVE,0) DEPRN_RESERVE,
    FDS.PERIOD_COUNTER,
    BKS.PERIOD_COUNTER_FULLY_RESERVED,
   'R' STATUS,
    BKS.RETIREMENT_ID  RETIREMENT_ID_ORDER,
	fnd_flex_xml_publisher_apis.process_kff_combination_1('cat_flex_all_segs', 'OFA', 'CAT#', 101, NULL, ct.CATEGORY_ID, 'ALL', 'Y', 'DESCRIPTION') cat_flex_all_segs, 
	JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('R', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, BKS.RETIREMENT_ID, NVL( RET.COST_RETIRED,0)) C_ACCUM_DEPRN, 
	JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID) C_ACCUM_DEPRN_PREV_YR, 
	JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('R', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, BKS.RETIREMENT_ID, NVL( RET.COST_RETIRED,0)), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID)) C_ACCUM_DEPRN_CURR_YR, 
	JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE) C_PRICE_INDEX, 
	JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE) C_INDEX_VALUE, 
	JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(BKS.RETIREMENT_ID, BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) C_INDEX_VALUE_HALF_PERIOD, 
	JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(BKS.RETIREMENT_ID, BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) ,JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)) C_CORR_FACTOR, 
	JL_JLMXFISR_XMLP_PKG.c_adj_accum_deprnformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFISR_XMLP_PKG.c_accum_deprnformula('R', BKS.TRANSACTION_HEADER_ID_OUT, BKS.TRANSACTION_HEADER_ID_IN, FDS.ASSET_ID, FDS.PERIOD_COUNTER, BKS.RETIREMENT_ID, NVL( RET.COST_RETIRED,0)), JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID)),JL_JLMXFISR_XMLP_PKG.c_corr_factorformula(JL_JLMXFISR_XMLP_PKG.c_index_value_half_periodformu(BKS.RETIREMENT_ID, BKS.DATE_PLACED_IN_SERVICE,JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)), FDS.ASSET_ID, BKS.TRANSACTION_HEADER_ID_IN, BKS.TRANSACTION_HEADER_ID_OUT, FADD.DESCRIPTION, FADD.ASSET_CATEGORY_ID, FADD.ASSET_NUMBER, BKS.DATE_PLACED_IN_SERVICE, BKS.PRORATE_DATE, BKS.RETIREMENT_ID,JL_JLMXFISR_XMLP_PKG.c_index_valueformula(JL_JLMXFISR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE), NVL( RET.COST_RETIRED,0),JL_JLMXFISR_XMLP_PKG.c_accum_deprn_prev_yrformula(FDS.ASSET_ID, FDS.PERIOD_COUNTER, NVL( RET.COST_RETIRED,0), BKS.RETIREMENT_ID), BKS.PERIOD_COUNTER_FULLY_RESERVED) C_ADJ_ACCUM_DEPRN,
	JL_JLMXFISR_XMLP_PKG.cf_retirement_date(BKS.RETIREMENT_ID) CF_RETIREMENT_DATE
FROM   FA_CATEGORIES CT,
             FA_ADDITIONS FADD,
             &lp_fa_books BKS,
             &lp_fa_deprn_summary FDS,
             &lp_fa_retirements RET,
             &lp_fa_deprn_periods FDP
 WHERE FDS.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
  AND BKS.BOOK_TYPE_CODE    = FDS.BOOK_TYPE_CODE
  AND BKS.ASSET_ID          = FDS.ASSET_ID
  AND BKS.DATE_PLACED_IN_SERVICE <= :C_FISCAL_END_DATE 
  AND BKS.RETIREMENT_ID = RET.RETIREMENT_ID
  AND RET.STATUS <> 'DELETED'
  AND (RET.DATE_RETIRED BETWEEN :C_FISCAL_START_DATE  AND :C_FISCAL_END_DATE)
  AND RET.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE
  AND RET.DATE_RETIRED BETWEEN FDP.CALENDAR_PERIOD_OPEN_DATE AND FDP.CALENDAR_PERIOD_CLOSE_DATE
  AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER
  AND FADD.ASSET_ID =  BKS.ASSET_ID
  AND FADD.ASSET_TYPE = 'CAPITALIZED'
  AND FDP.PERIOD_COUNTER >= (
SELECT  MIN(FDP2.PERIOD_COUNTER)
FROM     FA_ASSET_HISTORY FAH2,
                                                                  FA_DEPRN_PERIODS FDP2
                                                    WHERE   FAH2.ASSET_ID              = FADD.ASSET_ID
                                                    AND       FAH2.ASSET_TYPE          = 'CAPITALIZED'
                                                    AND       FDP2.FISCAL_YEAR         <= :P_CURR_FY
                                                    AND       FDP2.BOOK_TYPE_CODE = :P_BOOK_TYPE_CODE
                                                    AND       FAH2.DATE_EFFECTIVE BETWEEN FDP2.PERIOD_OPEN_DATE
                                                                                                    AND           FDP2.PERIOD_CLOSE_DATE)
  AND CT.CATEGORY_ID = FADD.ASSET_CATEGORY_ID
ORDER BY 1,6,7,16