View: FII_BIS_FARSP_V

View Text

SELECT EMP.FULL_NAME EMPLOYEE
, DECODE(FII_BIS_FADST_PKG.GET_ACCT_FLEX_SEG(BC.BOOK_TYPE_CODE)
, 'SEGMENT1'
, DHCC2.SEGMENT1
, 'SEGMENT2'
, DHCC2.SEGMENT2
, 'SEGMENT3'
, DHCC2.SEGMENT3
, 'SEGMENT4'
, DHCC2.SEGMENT4
, 'SEGMENT5'
, DHCC2.SEGMENT5
, 'SEGMENT6'
, DHCC2.SEGMENT6
, 'SEGMENT7'
, DHCC2.SEGMENT7
, 'SEGMENT8'
, DHCC2.SEGMENT8
, 'SEGMENT9'
, DHCC2.SEGMENT9
, 'SEGMENT10'
, DHCC2.SEGMENT10
, 'SEGMENT11'
, DHCC2.SEGMENT11
, 'SEGMENT12'
, DHCC2.SEGMENT12
, 'SEGMENT13'
, DHCC2.SEGMENT13
, 'SEGMENT14'
, DHCC2.SEGMENT14
, 'SEGMENT15'
, DHCC2.SEGMENT15
, 'SEGMENT16'
, DHCC2.SEGMENT16
, 'SEGMENT17'
, DHCC2.SEGMENT17
, 'SEGMENT18'
, DHCC2.SEGMENT18
, 'SEGMENT19'
, DHCC2.SEGMENT19
, 'SEGMENT20'
, DHCC2.SEGMENT20
, 'SEGMENT21'
, DHCC2.SEGMENT21
, 'SEGMENT22'
, DHCC2.SEGMENT22
, 'SEGMENT23'
, DHCC2.SEGMENT23
, 'SEGMENT24'
, DHCC2.SEGMENT24
, 'SEGMENT25'
, DHCC2.SEGMENT25
, 'SEGMENT26'
, DHCC2.SEGMENT26
, 'SEGMENT27'
, DHCC2.SEGMENT27
, 'SEGMENT28'
, DHCC2.SEGMENT28
, 'SEGMENT29'
, DHCC2.SEGMENT29
, 'SEGMENT30'
, DHCC2.SEGMENT30
, '%') EMP_CC
, 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
, SUM(BK.COST * (DH.UNITS_ASSIGNED /AH.UNITS)) / 1000 ASSET_COST
FROM PER_ALL_PEOPLE_F EMP
, PER_ALL_ASSIGNMENTS_F EMP2
, GL_CODE_COMBINATIONS DHCC2
, GL_CODE_COMBINATIONS DHCC
, FA_BOOKS BK
, FA_DISTRIBUTION_HISTORY DH
, FA_BOOK_CONTROLS BC
, FA_ASSET_HISTORY AH
WHERE BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND DH.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND DH.ASSET_ID = BK.ASSET_ID
AND DH.ASSIGNED_TO IS NOT NULL
AND DH.DATE_INEFFECTIVE IS NULL
AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND AH.ASSET_ID = BK.ASSET_ID
AND AH.DATE_INEFFECTIVE IS NULL
AND BK.DATE_INEFFECTIVE IS NULL
AND DH.ASSIGNED_TO = EMP.PERSON_ID
AND EMP.PERSON_ID = EMP2.PERSON_ID
AND EMP2.DEFAULT_CODE_COMB_ID IS NOT NULL
AND EMP2.DEFAULT_CODE_COMB_ID = DHCC2.CODE_COMBINATION_ID
AND EMP2.SET_OF_BOOKS_ID IS NOT NULL
AND EMP2.SET_OF_BOOKS_ID = BC.SET_OF_BOOKS_ID
AND TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE
AND EMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN EMP2.EFFECTIVE_START_DATE
AND EMP2.EFFECTIVE_END_DATE GROUP BY EMP.FULL_NAME
, DECODE(FII_BIS_FADST_PKG.GET_ACCT_FLEX_SEG(BC.BOOK_TYPE_CODE)
, 'SEGMENT1'
, DHCC2.SEGMENT1
, 'SEGMENT2'
, DHCC2.SEGMENT2
, 'SEGMENT3'
, DHCC2.SEGMENT3
, 'SEGMENT4'
, DHCC2.SEGMENT4
, 'SEGMENT5'
, DHCC2.SEGMENT5
, 'SEGMENT6'
, DHCC2.SEGMENT6
, 'SEGMENT7'
, DHCC2.SEGMENT7
, 'SEGMENT8'
, DHCC2.SEGMENT8
, 'SEGMENT9'
, DHCC2.SEGMENT9
, 'SEGMENT10'
, DHCC2.SEGMENT10
, 'SEGMENT11'
, DHCC2.SEGMENT11
, 'SEGMENT12'
, DHCC2.SEGMENT12
, 'SEGMENT13'
, DHCC2.SEGMENT13
, 'SEGMENT14'
, DHCC2.SEGMENT14
, 'SEGMENT15'
, DHCC2.SEGMENT15
, 'SEGMENT16'
, DHCC2.SEGMENT16
, 'SEGMENT17'
, DHCC2.SEGMENT17
, 'SEGMENT18'
, DHCC2.SEGMENT18
, 'SEGMENT19'
, DHCC2.SEGMENT19
, 'SEGMENT20'
, DHCC2.SEGMENT20
, 'SEGMENT21'
, DHCC2.SEGMENT21
, 'SEGMENT22'
, DHCC2.SEGMENT22
, 'SEGMENT23'
, DHCC2.SEGMENT23
, 'SEGMENT24'
, DHCC2.SEGMENT24
, 'SEGMENT25'
, DHCC2.SEGMENT25
, 'SEGMENT26'
, DHCC2.SEGMENT26
, 'SEGMENT27'
, DHCC2.SEGMENT27
, 'SEGMENT28'
, DHCC2.SEGMENT28
, 'SEGMENT29'
, DHCC2.SEGMENT29
, 'SEGMENT30'
, DHCC2.SEGMENT30
, '%')
, 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 UNION SELECT EMP.FULL_NAME EMPLOYEE
, 'N/A' EMP_CC
, 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
, SUM(BK.COST* (DH.UNITS_ASSIGNED /AH.UNITS)) / 1000 ASSET_COST
FROM PER_ALL_PEOPLE_F EMP
, PER_ALL_ASSIGNMENTS_F EMP2
, GL_CODE_COMBINATIONS DHCC2
, GL_CODE_COMBINATIONS DHCC
, FA_BOOKS BK
, FA_DISTRIBUTION_HISTORY DH
, FA_BOOK_CONTROLS BC
, FA_ASSET_HISTORY AH
WHERE BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND DH.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND DH.ASSET_ID = BK.ASSET_ID
AND DH.ASSIGNED_TO IS NOT NULL
AND AH.ASSET_ID = BK.ASSET_ID
AND AH.DATE_INEFFECTIVE IS NULL
AND DH.DATE_INEFFECTIVE IS NULL
AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND BK.DATE_INEFFECTIVE IS NULL
AND DH.ASSIGNED_TO = EMP.PERSON_ID
AND EMP.PERSON_ID = EMP2.PERSON_ID
AND EMP2.DEFAULT_CODE_COMB_ID IS NOT NULL
AND EMP2.DEFAULT_CODE_COMB_ID = DHCC2.CODE_COMBINATION_ID
AND EMP2.SET_OF_BOOKS_ID IS NOT NULL
AND EMP2.SET_OF_BOOKS_ID <> BC.SET_OF_BOOKS_ID
AND TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE
AND EMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN EMP2.EFFECTIVE_START_DATE
AND EMP2.EFFECTIVE_END_DATE GROUP BY EMP.FULL_NAME
, 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 UNION SELECT EMP.FULL_NAME EMPLOYEE
, 'N/A' EMP_CC
, DECODE(FII_BIS_FADST_PKG.GET_ACCT_FLEX_SEG(BK.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
, BK.BOOK_TYPE_CODE BOOK_TYPE_CODE
, SUM(BK.COST* (DH.UNITS_ASSIGNED /AH.UNITS)) / 1000 ASSET_COST
FROM PER_ALL_PEOPLE_F EMP
, PER_ALL_ASSIGNMENTS_F EMP2
, GL_CODE_COMBINATIONS DHCC
, FA_BOOKS BK
, FA_DISTRIBUTION_HISTORY DH
, FA_ASSET_HISTORY AH
WHERE DH.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND DH.ASSET_ID = BK.ASSET_ID
AND DH.ASSIGNED_TO IS NOT NULL
AND DH.DATE_INEFFECTIVE IS NULL
AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND BK.DATE_INEFFECTIVE IS NULL
AND AH.ASSET_ID = BK.ASSET_ID
AND AH.DATE_INEFFECTIVE IS NULL
AND DH.ASSIGNED_TO = EMP.PERSON_ID
AND EMP.PERSON_ID NOT IN ( SELECT EMP.PERSON_ID
FROM PER_ALL_PEOPLE_F EMP
, PER_ALL_ASSIGNMENTS_F EMP2
, GL_CODE_COMBINATIONS DHCC2
, GL_CODE_COMBINATIONS DHCC
, FA_DISTRIBUTION_HISTORY DH
, FA_BOOK_CONTROLS BC
, FA_BOOKS BK
WHERE BK.BOOK_TYPE_CODE = BC.BOOK_TYPE_CODE
AND DH.BOOK_TYPE_CODE = BK.BOOK_TYPE_CODE
AND DH.ASSET_ID = BK.ASSET_ID
AND DH.ASSIGNED_TO IS NOT NULL
AND DH.DATE_INEFFECTIVE IS NULL
AND DHCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND BK.DATE_INEFFECTIVE IS NULL
AND DH.ASSIGNED_TO = EMP.PERSON_ID
AND EMP.PERSON_ID = EMP2.PERSON_ID
AND EMP2.DEFAULT_CODE_COMB_ID IS NOT NULL
AND EMP2.DEFAULT_CODE_COMB_ID = DHCC2.CODE_COMBINATION_ID
AND EMP2.SET_OF_BOOKS_ID IS NOT NULL
AND TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE
AND EMP.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN EMP2.EFFECTIVE_START_DATE
AND EMP2.EFFECTIVE_END_DATE)
AND EMP.PERSON_ID = EMP2.PERSON_ID
AND EMP2.DEFAULT_CODE_COMB_ID IS NULL
AND TRUNC(SYSDATE) BETWEEN EMP.EFFECTIVE_START_DATE
AND EMP.EFFECTIVE_END_DATE
AND EMP2.ROWID = (SELECT MAX(ROWID)
FROM PER_ALL_ASSIGNMENTS_F EMP3
WHERE EMP3.PERSON_ID = EMP.PERSON_ID
AND EMP3.DEFAULT_CODE_COMB_ID IS NULL
AND TRUNC(SYSDATE) BETWEEN EMP3.EFFECTIVE_START_DATE
AND EMP3.EFFECTIVE_END_DATE) GROUP BY EMP.FULL_NAME
, DECODE(FII_BIS_FADST_PKG.GET_ACCT_FLEX_SEG(BK.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
, '%')
, BK.BOOK_TYPE_CODE

Columns