Query to Get Asset Categories

SELECT DISTINCT

  a.segment1||'.'||a.segment2 CATEGORY

, a.segment1 MAJOR_CATEGORY

, a.segment2 MINOR_CATEGORY

, gl1.segment1||'.'||gl1.SEGMENT2||'.'||gl1.SEGMENT3||'.'||gl1.SEGMENT4||'.'||gl1.SEGMENT5 ASSET_COST

, gl2.segment1||'.'||gl2.SEGMENT2||'.'||gl2.SEGMENT3||'.'||gl2.SEGMENT4||'.'||gl2.SEGMENT5 ASSET_COST_CLEARING

, c.DEPRN_EXPENSE_ACCT

, gl3.segment1||'.'||gl3.SEGMENT2||'.'||gl3.SEGMENT3||'.'||gl3.SEGMENT4||'.'||gl3.SEGMENT5 DEPRN_RESERVE_ACCOUNT

, deprn_method

, life_in_months

, (life_in_months/12) lIFE

, prorate_convention_code

FROM fa_categories a

, FA_CATEGORY_BOOK_DEFAULTS b

, fa_category_books c

, gl_code_combinations gl1

, gl_code_combinations gl2

, gl_code_combinations gl3

WHERE a.category_id = b.category_id

AND c.category_id = b.category_id

AND c.category_id = a.category_id

AND a.ENABLED_FLAG = 'Y'

AND c.ASSET_COST_ACCOUNT_CCID = gl1.CODE_COMBINATION_ID

AND c.ASSET_CLEARING_ACCOUNT_CCID = gl2.CODE_COMBINATION_ID

AND c.RESERVE_ACCOUNT_CCID = gl3.CODE_COMBINATION_ID

--AND c.WIP_COST_ACCOUNT_CCID = gl4.CODE_COMBINATION_ID

--AND c.WIP_CLEARING_ACCOUNT_CCID = gl5.CODE_COMBINATION_ID

AND b.book_type_code = c.book_type_code

AND c.book_type_code = 'CORP BOOK'