JL Mexican Fixed Assets IMPAC - draft

SELECT IMPAC.ASSET_ID, 
              IMPAC.ASSET_NUMBER,
              IMPAC.TRANSACTION_HEADER_ID_IN,
              IMPAC.TRANSACTION_HEADER_ID_OUT,
              IMPAC.DESCRIPTION DESCRIPTION,
              IMPAC.DATE_PLACED_IN_SERVICE ACQDATE,
              IMPAC.PRORATE_DATE PRORATE_DATE,
              IMPAC.RETIREMENT_ID RETIREMENT_ID,
              IMPAC.ORIGINAL_COST,
              IMPAC.INPC,
              IMPAC.ADJUSTED_DEPRN_CURRENT_FY,
              IMPAC.ACCUM_DEPRN_PREVIOUS_FY,
              IMPAC.ASSET_CATEGORY_ID ASSET_CATEGORY,
              IMPAC.PERIOD_COUNTER_FULLY_RESERVED,
              &C_ALL_SEGS CATEG_DESC, 
	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_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE) C_PRICE_INDEX, 
	JL_JLMXFIMP_XMLP_PKG.c_index_value_juneformula(JL_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE)) C_INDEX_VALUE_JUNE, 
	JL_JLMXFIMP_XMLP_PKG.c_corr_factorformula(IMPAC.DATE_PLACED_IN_SERVICE,JL_JLMXFIMP_XMLP_PKG.c_index_value_juneformula(JL_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE)), IMPAC.INPC) C_CORR_FACTOR, 
	JL_JLMXFIMP_XMLP_PKG.c_nbv_end_prev_fyformula(IMPAC.ORIGINAL_COST, nvl(IMPAC.ACCUM_DEPRN_PREVIOUS_FY,0)) C_NBV_END_PREV_FY, 
	JL_JLMXFIMP_XMLP_PKG.c_adj_nbv_end_prev_fyformula(JL_JLMXFIMP_XMLP_PKG.c_nbv_end_prev_fyformula(IMPAC.ORIGINAL_COST, nvl(IMPAC.ACCUM_DEPRN_PREVIOUS_FY,0)),JL_JLMXFIMP_XMLP_PKG.c_corr_factorformula(IMPAC.DATE_PLACED_IN_SERVICE,JL_JLMXFIMP_XMLP_PKG.c_index_value_juneformula(JL_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE)), IMPAC.INPC)) C_ADJ_NBV_END_PREV_FY, 
	JL_JLMXFIMP_XMLP_PKG.c_50per_adj_deprn_curr_fyformu(IMPAC.ADJUSTED_DEPRN_CURRENT_FY) C_50PER_ADJ_DEPRN_CURR_FY, 
	JL_JLMXFIMP_XMLP_PKG.c_adj_nbv_less_50performula(	JL_JLMXFIMP_XMLP_PKG.c_adj_nbv_end_prev_fyformula(JL_JLMXFIMP_XMLP_PKG.c_nbv_end_prev_fyformula(IMPAC.ORIGINAL_COST, nvl(IMPAC.ACCUM_DEPRN_PREVIOUS_FY,0)),JL_JLMXFIMP_XMLP_PKG.c_corr_factorformula(IMPAC.DATE_PLACED_IN_SERVICE,JL_JLMXFIMP_XMLP_PKG.c_index_value_juneformula(JL_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE)), IMPAC.INPC)),JL_JLMXFIMP_XMLP_PKG.c_50per_adj_deprn_curr_fyformu(IMPAC.ADJUSTED_DEPRN_CURRENT_FY)) C_ADJ_NBV_LESS_50PER, 
	JL_JLMXFIMP_XMLP_PKG.c_impac_base_valueformula(IMPAC.DATE_PLACED_IN_SERVICE, IMPAC.RETIREMENT_ID,JL_JLMXFIMP_XMLP_PKG.cf_retirement_date(IMPAC.RETIREMENT_ID), IMPAC.PERIOD_COUNTER_FULLY_RESERVED, JL_JLMXFIMP_XMLP_PKG.c_adj_nbv_less_50performula(	JL_JLMXFIMP_XMLP_PKG.c_adj_nbv_end_prev_fyformula(JL_JLMXFIMP_XMLP_PKG.c_nbv_end_prev_fyformula(IMPAC.ORIGINAL_COST, nvl(IMPAC.ACCUM_DEPRN_PREVIOUS_FY,0)),JL_JLMXFIMP_XMLP_PKG.c_corr_factorformula(IMPAC.DATE_PLACED_IN_SERVICE,JL_JLMXFIMP_XMLP_PKG.c_index_value_juneformula(JL_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE)), IMPAC.INPC)),JL_JLMXFIMP_XMLP_PKG.c_50per_adj_deprn_curr_fyformu(IMPAC.ADJUSTED_DEPRN_CURRENT_FY))) C_IMPAC_BASE_VALUE, 
	JL_JLMXFIMP_XMLP_PKG.c_index_value_acqformula(JL_JLMXFIMP_XMLP_PKG.c_price_indexformula(IMPAC.ASSET_CATEGORY_ID, IMPAC.DATE_PLACED_IN_SERVICE), IMPAC.DATE_PLACED_IN_SERVICE) C_index_value_acq, 
	JL_JLMXFIMP_XMLP_PKG.cf_retirement_date(IMPAC.RETIREMENT_ID) CF_RETIREMENT_DATE
FROM  FA_CATEGORIES CT, JL_MX_FA_ISR_IMPAC IMPAC
 WHERE  IMPAC.PROCESS_ID = :P_PROCESS_ID
  AND     CT.CATEGORY_ID = IMPAC.ASSET_CATEGORY_ID
  ORDER BY CATEG_DESC,
                     IMPAC.ASSET_NUMBER,
                     IMPAC.DESCRIPTION,
                     NVL(RETIREMENT_ID,0)