View: FII_BIS_FADST_V

View Text

SELECT MAJOR_CATEGORY
, COST_CENTER
, BOOK_TYPE_CODE
, ATTRIBUTE_CATEGORY_CODE
, CHART_OF_ACCTS_ID
, SUM_BK_COST
, RATIO_TO_REPORT(SUM_BK_COST) OVER (PARTITION BY COST_CENTER
, BOOK_TYPE_CODE) RR
FROM (SELECT DECODE(FII_BIS_FADST_PKG.GET_MAJOR_SEG
, 'SEGMENT1'
, CAT.SEGMENT1
, 'SEGMENT2'
, CAT.SEGMENT2
, 'SEGMENT3'
, CAT.SEGMENT3
, 'SEGMENT4'
, CAT.SEGMENT4
, 'SEGMENT5'
, CAT.SEGMENT5
, 'SEGMENT6'
, CAT.SEGMENT6
, 'SEGMENT7'
, CAT.SEGMENT7
, NULL) MAJOR_CATEGORY
, DECODE(FII_BIS_FADST_PKG.GET_ACCT_FLEX_SEG(BC.BOOK_TYPE_CODE)
, 'SEGMENT1'
, DHCC.SEGMENT1
, 'SEGMENT2'
, DHCC.SEGMENT2
, 'SEGMENT3'
, DHCC.SEGMENT3
, 'SEGMENT4'
, DHCC.SEGMENT4
, 'SEGMENT5'
, DHCC.SEGMENT5
, 'SEGMENT6'
, DHCC.SEGMENT6
, 'SEGMENT7'
, DHCC.SEGMENT7
, 'SEGMENT8'
, DHCC.SEGMENT8
, 'SEGMENT9'
, DHCC.SEGMENT9
, 'SEGMENT10'
, DHCC.SEGMENT10
, 'SEGMENT11'
, DHCC.SEGMENT11
, 'SEGMENT12'
, DHCC.SEGMENT12
, 'SEGMENT13'
, DHCC.SEGMENT13
, 'SEGMENT14'
, DHCC.SEGMENT14
, 'SEGMENT15'
, DHCC.SEGMENT15
, 'SEGMENT16'
, DHCC.SEGMENT16
, 'SEGMENT17'
, DHCC.SEGMENT17
, 'SEGMENT18'
, DHCC.SEGMENT18
, 'SEGMENT19'
, DHCC.SEGMENT19
, 'SEGMENT20'
, DHCC.SEGMENT20
, 'SEGMENT21'
, DHCC.SEGMENT21
, 'SEGMENT22'
, DHCC.SEGMENT22
, 'SEGMENT23'
, DHCC.SEGMENT23
, 'SEGMENT24'
, DHCC.SEGMENT24
, 'SEGMENT25'
, DHCC.SEGMENT25
, 'SEGMENT26'
, DHCC.SEGMENT26
, 'SEGMENT27'
, DHCC.SEGMENT27
, 'SEGMENT28'
, DHCC.SEGMENT28
, 'SEGMENT29'
, DHCC.SEGMENT29
, 'SEGMENT30'
, DHCC.SEGMENT30
, '%') COST_CENTER
, BC.BOOK_TYPE_CODE BOOK_TYPE_CODE
, AD.ATTRIBUTE_CATEGORY_CODE ATTRIBUTE_CATEGORY_CODE
, BC.ACCOUNTING_FLEX_STRUCTURE CHART_OF_ACCTS_ID
, ROUND(SUM(BK.COST/1000)
, 2) SUM_BK_COST
FROM FA_DISTRIBUTION_HISTORY DH
, GL_CODE_COMBINATIONS DHCC
, FA_ADDITIONS AD
, FA_BOOKS BK
, FA_BOOK_CONTROLS BC
, FA_CATEGORIES CAT
WHERE DH.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND DH.ASSET_ID = AD.ASSET_ID
AND DH.DATE_INEFFECTIVE IS NULL
AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND AD.ASSET_CATEGORY_ID = CAT.CATEGORY_ID
AND BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND BK.ASSET_ID = AD.ASSET_ID
AND BK.DATE_INEFFECTIVE IS NULL GROUP BY DECODE(FII_BIS_FADST_PKG.GET_MAJOR_SEG
, 'SEGMENT1'
, CAT.SEGMENT1
, 'SEGMENT2'
, CAT.SEGMENT2
, 'SEGMENT3'
, CAT.SEGMENT3
, 'SEGMENT4'
, CAT.SEGMENT4
, 'SEGMENT5'
, CAT.SEGMENT5
, 'SEGMENT6'
, CAT.SEGMENT6
, 'SEGMENT7'
, CAT.SEGMENT7
, NULL)
, DECODE(FII_BIS_FADST_PKG.GET_ACCT_FLEX_SEG(BC.BOOK_TYPE_CODE)
, 'SEGMENT1'
, DHCC.SEGMENT1
, 'SEGMENT2'
, DHCC.SEGMENT2
, 'SEGMENT3'
, DHCC.SEGMENT3
, 'SEGMENT4'
, DHCC.SEGMENT4
, 'SEGMENT5'
, DHCC.SEGMENT5
, 'SEGMENT6'
, DHCC.SEGMENT6
, 'SEGMENT7'
, DHCC.SEGMENT7
, 'SEGMENT8'
, DHCC.SEGMENT8
, 'SEGMENT9'
, DHCC.SEGMENT9
, 'SEGMENT10'
, DHCC.SEGMENT10
, 'SEGMENT11'
, DHCC.SEGMENT11
, 'SEGMENT12'
, DHCC.SEGMENT12
, 'SEGMENT13'
, DHCC.SEGMENT13
, 'SEGMENT14'
, DHCC.SEGMENT14
, 'SEGMENT15'
, DHCC.SEGMENT15
, 'SEGMENT16'
, DHCC.SEGMENT16
, 'SEGMENT17'
, DHCC.SEGMENT17
, 'SEGMENT18'
, DHCC.SEGMENT18
, 'SEGMENT19'
, DHCC.SEGMENT19
, 'SEGMENT20'
, DHCC.SEGMENT20
, 'SEGMENT21'
, DHCC.SEGMENT21
, 'SEGMENT22'
, DHCC.SEGMENT22
, 'SEGMENT23'
, DHCC.SEGMENT23
, 'SEGMENT24'
, DHCC.SEGMENT24
, 'SEGMENT25'
, DHCC.SEGMENT25
, 'SEGMENT26'
, DHCC.SEGMENT26
, 'SEGMENT27'
, DHCC.SEGMENT27
, 'SEGMENT28'
, DHCC.SEGMENT28
, 'SEGMENT29'
, DHCC.SEGMENT29
, 'SEGMENT30'
, DHCC.SEGMENT30
, '%')
, BC.BOOK_TYPE_CODE
, AD.ATTRIBUTE_CATEGORY_CODE
, BC.ACCOUNTING_FLEX_STRUCTURE )

Columns