Sunday, March 20, 2016

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'

No comments:

Post a Comment

Best Blogger TipsGet Flower Effect