ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F1813869E053B46B63588994 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 Y JL JLMXFISR_XML US JL Mexican Fixed Assets ISR - draft 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 BI Publisher &c_all_segs &lp_fa_books &lp_fa_deprn_periods &lp_fa_deprn_summary &lp_fa_retirements :c_all_segs :c_base_currency_code :c_base_description :c_base_min_acct_unit :c_base_precision :c_fiscal_end_date :c_fiscal_start_date :c_last_period_counter :c_max_period_counter :c_min_period_counter :c_organization_name :c_ratio_precision :c_user_id :cat_flex_struct :lp_currency_code :lp_fa_adjustments :lp_fa_asset_invoices :lp_fa_book_controls :lp_fa_books :lp_fa_deferred_deprn :lp_fa_deprn_detail :lp_fa_deprn_periods :lp_fa_deprn_summary :lp_fa_retirements :p_book_type_code :p_ca_set_of_books_id :p_conc_request_id :p_curr_fy :p_currency_code :p_end_of_report :p_include_dpis :p_include_ret :p_include_zero_nbv_assets :p_mrcsobtype :p_no_data_found :p_process_id 1 10 :p_book_type_code LOV Oracle JLMX_FA_LEGAL_BOOKS Y select book_type_code id, book_type_code value, book_type_name description from fa_book_controls where global_attribute4='Y' order by book_type_code Y US Book Type Code 2 20 :p_ca_set_of_books_id LOV Oracle JLMX_FA_CA_SOB_CURRENCY Y select gsob.set_of_books_id id, gsob.currency_code value, gsob.name description from gl_sets_of_books gsob where gsob.set_of_books_id in ( select set_of_books_id from fa_book_controls where book_type_code=:$flex$.jlmx_fa_legal_books union all select set_of_books_id from fa_mc_book_controls where book_type_code=:$flex$.jlmx_fa_legal_books) order by gsob.currency_code select currency_code from gl_sets_of_books where set_of_books_id = (select set_of_books_id from fa_book_controls where book_type_code=:$FLEX$.Book_Type_Code) Y US Ledger Currency 3 30 :p_curr_fy LOV Oracle JLMX_FA_LEGAL_BOOK_FY Y select fiscal_year id, fiscal_year value, null description from fa_fiscal_year where fiscal_year_name=(select fiscal_year_name from fa_book_controls where book_type_code=:$flex$.jlmx_fa_legal_books) and fiscal_year in (select distinct fiscal_year from fa_deprn_periods where book_type_code=:$flex$.jlmx_fa_legal_books) order by fiscal_year Y US Fiscal Year 4 40 :p_include_dpis LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' Y US Include DPIS Month for Periods of Use Calculation 5 50 :p_include_ret LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'N' Y US Include Retirement Month for Periods of Use Calculation 6 60 :p_include_zero_nbv_assets LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description select meaning from fnd_lookups where lookup_type = 'YES_NO' and lookup_code = 'Y' Y US Include Zero NBV Assets