JL Mexican Fixed Assets Fiscal Gain and Loss on Assets Retirements - draft

SELECT FADD.ASSET_ID  ASSET_ID,
              FADD.ASSET_NUMBER||' - '||FADD.DESCRIPTION ASSET_NUMBER,
              BKS.TRANSACTION_HEADER_ID_OUT   TRANSACTION_HEADER_ID,
              BKS.DATE_PLACED_IN_SERVICE  ACQDATE,
              RET.COST_RETIRED ORIGINAL_COST,
              RET.DATE_RETIRED DATE_RETIRED,
              RET.PROCEEDS_OF_SALE PROCEEDS_OF_SALE,
              FADD.ASSET_CATEGORY_ID ASSET_CATEGORY,
              BKS.RETIREMENT_ID RETIREMENT_ID,
              FDP.PERIOD_COUNTER,
	JL_JLMXFGLR_XMLP_PKG.c_nbvformula(FADD.ASSET_ID, BKS.TRANSACTION_HEADER_ID_OUT, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED) C_NBV, 
	--&PROCEEDS_OF_SALE C_PROC_SALE,
	RET.PROCEEDS_OF_SALE C_PROC_SALE,
	JL_JLMXFGLR_XMLP_PKG.c_ret_dateformula() C_RET_DATE, 
	JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_prev_yrformula(FADD.ASSET_ID, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED) C_ACCUM_DEPRN_PREV_YR, 
	JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFGLR_XMLP_PKG.c_nbvformula(FADD.ASSET_ID, BKS.TRANSACTION_HEADER_ID_OUT, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED),JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_prev_yrformula(FADD.ASSET_ID, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED)) C_ACCUM_DEPRN_CURR_YR, 
	JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE) C_PRICE_INDEX, 
	JL_JLMXFGLR_XMLP_PKG.c_index_valueformula(JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE) C_INDEX_VALUE, 
	JL_JLMXFGLR_XMLP_PKG.c_index_value_half_periodformu(BKS.DATE_PLACED_IN_SERVICE, RET.DATE_RETIRED,JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)) C_INDEX_VALUE_HALF_PERIOD, 
	JL_JLMXFGLR_XMLP_PKG.c_corr_factorformula(JL_JLMXFGLR_XMLP_PKG.c_index_value_half_periodformu(BKS.DATE_PLACED_IN_SERVICE, RET.DATE_RETIRED,JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFGLR_XMLP_PKG.c_index_valueformula(JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE)) C_CORR_FACTOR, 
	JL_JLMXFGLR_XMLP_PKG.c_adj_nbvformula(RET.COST_RETIRED,JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFGLR_XMLP_PKG.c_nbvformula(FADD.ASSET_ID, BKS.TRANSACTION_HEADER_ID_OUT, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED),JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_prev_yrformula(FADD.ASSET_ID, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED)),JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_prev_yrformula(FADD.ASSET_ID, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED),JL_JLMXFGLR_XMLP_PKG.c_corr_factorformula(JL_JLMXFGLR_XMLP_PKG.c_index_value_half_periodformu(BKS.DATE_PLACED_IN_SERVICE, RET.DATE_RETIRED,JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFGLR_XMLP_PKG.c_index_valueformula(JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE))) C_ADJ_NBV, 
	JL_JLMXFGLR_XMLP_PKG.c_gain_lossformula(JL_JLMXFGLR_XMLP_PKG.c_adj_nbvformula(RET.COST_RETIRED,JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_curr_yrformula(JL_JLMXFGLR_XMLP_PKG.c_nbvformula(FADD.ASSET_ID, BKS.TRANSACTION_HEADER_ID_OUT, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED),JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_prev_yrformula(FADD.ASSET_ID, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED)),JL_JLMXFGLR_XMLP_PKG.c_accum_deprn_prev_yrformula(FADD.ASSET_ID, FDP.PERIOD_COUNTER, BKS.RETIREMENT_ID, RET.COST_RETIRED),JL_JLMXFGLR_XMLP_PKG.c_corr_factorformula(JL_JLMXFGLR_XMLP_PKG.c_index_value_half_periodformu(BKS.DATE_PLACED_IN_SERVICE, RET.DATE_RETIRED,JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE)), JL_JLMXFGLR_XMLP_PKG.c_index_valueformula(JL_JLMXFGLR_XMLP_PKG.c_price_indexformula(FADD.ASSET_CATEGORY_ID, BKS.DATE_PLACED_IN_SERVICE), BKS.DATE_PLACED_IN_SERVICE))),RET.PROCEEDS_OF_SALE) C_GAIN_LOSS
FROM FA_ADDITIONS                FADD,
              &lp_fa_books                      BKS,
              &lp_fa_retirements         RET,
              &lp_fa_deprn_periods     FDP
 WHERE ret.book_type_code                = :p_book_type_code
       AND bks.book_type_code             = ret.book_type_code
       AND bks.asset_id                         = ret.asset_id
       AND bks.retirement_id                 = ret.retirement_id
       AND bks.cost <> 0
       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 fadd.asset_id                    = bks.asset_id
       AND FADD.ASSET_TYPE='CAPITALIZED'
  ORDER BY fadd.asset_number,
           fadd.description