JL Mexican Fixed Assets ISR - draft

Description
Categories: BI Publisher
Application: Latin America Localizations
Source: Mexican Fixed Assets ISR Report (XML) - Not Supported: Reserved For Future Use
Short Name: JLMXFISR_XML
DB package: JL_JLMXFISR_XMLP_PKG
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
Parameter Name SQL text Validation
Include Zero NBV Assets
 
LOV Oracle
Include Retirement Month for Periods of Use Calculation
 
LOV Oracle
Include DPIS Month for Periods of Use Calculation
 
LOV Oracle
Fiscal Year
 
LOV Oracle
Ledger Currency
 
LOV Oracle
Book Type Code
 
LOV Oracle