VIEW: APPS.FA_PROJ_INTERIM_V

Source

View Text - Preformatted

select "REQUEST_ID","BOOK_TYPE_CODE","ASSET_ID","PERIOD_NAME","PERIOD_INDEX","FISCAL_YEAR","CODE_COMBINATION_ID","DEPRECIATION","AMOUNT_TYPE","ORDER_ROW"  from
	  (
	   select request_id,book_type_code,asset_id,period_name,
	          period_index,fiscal_year,code_combination_id, depreciation,amount_type,
	      row_number () over
	      (partition by request_id,book_type_code,asset_id,period_name,code_combination_id order by amount_type)
	      as order_row
	   from
	      (
	       select request_id,book_type_code,asset_id,period_name,
	              period_index,fiscal_year,code_combination_id, depreciation,'P' amount_type
	       from FA_PROJ_INTERIM_REP
	      union
	       select par.request_id,dd.book_type_code,dd.asset_id,
	              dp1.period_name,dp1.period_num,dp1.fiscal_year, dh.code_combination_id,
	              dd.deprn_amount,'A' amount_type
	      from fa_deprn_detail dd,FA_PROJ_PARAM_RUN par,
	           fa_deprn_periods dp,fa_deprn_periods dp1,
	           fa_distribution_history dh,fa_book_controls bc
	      where
	        dd.book_type_code in (par.book_type_code1,par.book_type_code2,par.book_type_code3,par.book_type_code4)
	        and dd.deprn_source_code = 'D'
	        and dp.book_type_code = dd.book_type_code
	        and dp.period_name = par.period_name
	        and dd.period_counter >= dp.period_counter
	        and dd.period_counter < dp.period_counter + par.period_count
	        and dp1.book_type_code = dd.book_type_code
	        and dp1.period_counter = dd.period_counter
	        and bc.book_type_code = dd.book_type_code
	        and dh.asset_id = dd.asset_id
	        and dh.book_type_code = bc.distribution_source_book
	        and dd.distribution_id = dh.distribution_id
	      union
	      select par.request_id,dd.book_type_code,dd.asset_id,
	             dp1.period_name,dp1.period_num,dp1.fiscal_year, dh.code_combination_id,
	             dd.deprn_amount,'A' amount_type
	      from fa_mc_deprn_detail dd,FA_PROJ_PARAM_RUN par,
	           fa_mc_deprn_periods dp,fa_mc_deprn_periods dp1,
	           fa_distribution_history dh,fa_mc_book_controls bcm,fa_book_controls bc
	      where
	        dd.book_type_code in (par.book_type_code1,par.book_type_code2,par.book_type_code3,par.book_type_code4)
	        and dd.deprn_source_code = 'D'
	        and dd.set_of_books_id = par.set_of_books_id
	        and dp.set_of_books_id = par.set_of_books_id
	        and dp.book_type_code = dd.book_type_code
	        and dp.period_name = par.period_name
	        and dd.period_counter >= dp.period_counter
	        and dd.period_counter < dp.period_counter + par.period_count
	        and dp1.book_type_code = dd.book_type_code
	        and dp1.set_of_books_id = par.set_of_books_id
	        and dp1.period_counter = dd.period_counter
	        and bcm.set_of_books_id = par.set_of_books_id
	        and bcm.book_type_code = dd.book_type_code
	        and bc.book_type_code = bcm.book_type_code
	        and bc.set_of_books_id = bcm.primary_set_of_books_id
	        and dh.asset_id = dd.asset_id
	        and dh.book_type_code = bc.distribution_source_book
	        and dd.distribution_id = dh.distribution_id
	        )
	  ) where order_row = 1

View Text - HTML Formatted

SELECT "REQUEST_ID"
, "BOOK_TYPE_CODE"
, "ASSET_ID"
, "PERIOD_NAME"
, "PERIOD_INDEX"
, "FISCAL_YEAR"
, "CODE_COMBINATION_ID"
, "DEPRECIATION"
, "AMOUNT_TYPE"
, "ORDER_ROW" FROM ( SELECT REQUEST_ID
, BOOK_TYPE_CODE
, ASSET_ID
, PERIOD_NAME
, PERIOD_INDEX
, FISCAL_YEAR
, CODE_COMBINATION_ID
, DEPRECIATION
, AMOUNT_TYPE
, ROW_NUMBER () OVER (PARTITION BY REQUEST_ID
, BOOK_TYPE_CODE
, ASSET_ID
, PERIOD_NAME
, CODE_COMBINATION_ID ORDER BY AMOUNT_TYPE) AS ORDER_ROW FROM ( SELECT REQUEST_ID
, BOOK_TYPE_CODE
, ASSET_ID
, PERIOD_NAME
, PERIOD_INDEX
, FISCAL_YEAR
, CODE_COMBINATION_ID
, DEPRECIATION
, 'P' AMOUNT_TYPE
FROM FA_PROJ_INTERIM_REP UNION SELECT PAR.REQUEST_ID
, DD.BOOK_TYPE_CODE
, DD.ASSET_ID
, DP1.PERIOD_NAME
, DP1.PERIOD_NUM
, DP1.FISCAL_YEAR
, DH.CODE_COMBINATION_ID
, DD.DEPRN_AMOUNT
, 'A' AMOUNT_TYPE
FROM FA_DEPRN_DETAIL DD
, FA_PROJ_PARAM_RUN PAR
, FA_DEPRN_PERIODS DP
, FA_DEPRN_PERIODS DP1
, FA_DISTRIBUTION_HISTORY DH
, FA_BOOK_CONTROLS BC WHERE DD.BOOK_TYPE_CODE IN (PAR.BOOK_TYPE_CODE1
, PAR.BOOK_TYPE_CODE2
, PAR.BOOK_TYPE_CODE3
, PAR.BOOK_TYPE_CODE4)
AND DD.DEPRN_SOURCE_CODE = 'D'
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_NAME = PAR.PERIOD_NAME
AND DD.PERIOD_COUNTER >= DP.PERIOD_COUNTER
AND DD.PERIOD_COUNTER < DP.PERIOD_COUNTER + PAR.PERIOD_COUNT
AND DP1.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP1.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND BC.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DH.ASSET_ID = DD.ASSET_ID
AND DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID UNION SELECT PAR.REQUEST_ID
, DD.BOOK_TYPE_CODE
, DD.ASSET_ID
, DP1.PERIOD_NAME
, DP1.PERIOD_NUM
, DP1.FISCAL_YEAR
, DH.CODE_COMBINATION_ID
, DD.DEPRN_AMOUNT
, 'A' AMOUNT_TYPE
FROM FA_MC_DEPRN_DETAIL DD
, FA_PROJ_PARAM_RUN PAR
, FA_MC_DEPRN_PERIODS DP
, FA_MC_DEPRN_PERIODS DP1
, FA_DISTRIBUTION_HISTORY DH
, FA_MC_BOOK_CONTROLS BCM
, FA_BOOK_CONTROLS BC WHERE DD.BOOK_TYPE_CODE IN (PAR.BOOK_TYPE_CODE1
, PAR.BOOK_TYPE_CODE2
, PAR.BOOK_TYPE_CODE3
, PAR.BOOK_TYPE_CODE4)
AND DD.DEPRN_SOURCE_CODE = 'D'
AND DD.SET_OF_BOOKS_ID = PAR.SET_OF_BOOKS_ID
AND DP.SET_OF_BOOKS_ID = PAR.SET_OF_BOOKS_ID
AND DP.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP.PERIOD_NAME = PAR.PERIOD_NAME
AND DD.PERIOD_COUNTER >= DP.PERIOD_COUNTER
AND DD.PERIOD_COUNTER < DP.PERIOD_COUNTER + PAR.PERIOD_COUNT
AND DP1.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND DP1.SET_OF_BOOKS_ID = PAR.SET_OF_BOOKS_ID
AND DP1.PERIOD_COUNTER = DD.PERIOD_COUNTER
AND BCM.SET_OF_BOOKS_ID = PAR.SET_OF_BOOKS_ID
AND BCM.BOOK_TYPE_CODE = DD.BOOK_TYPE_CODE
AND BC.BOOK_TYPE_CODE = BCM.BOOK_TYPE_CODE
AND BC.SET_OF_BOOKS_ID = BCM.PRIMARY_SET_OF_BOOKS_ID
AND DH.ASSET_ID = DD.ASSET_ID
AND DH.BOOK_TYPE_CODE = BC.DISTRIBUTION_SOURCE_BOOK
AND DD.DISTRIBUTION_ID = DH.DISTRIBUTION_ID ) )
WHERE ORDER_ROW = 1