eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.FA_DISTRIBUTION_INQ_WEB_V

Source

View Text - Preformatted


SELECT DH_OUT.ROWID ROW_ID , DH_OUT.DISTRIBUTION_ID ID , DH_OUT.TRANSACTION_UNITS TRANS_UNITS , DH_OUT.UNITS_ASSIGNED UNITS , TH_OUT.TRANSACTION_TYPE_CODE TRANSTYPE , DH_OUT.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID , DH_OUT.LOCATION_ID , DH_OUT.ASSIGNED_TO , P.EMPLOYEE_NUMBER ASSIGNED_TO_NUMBER , P.FULL_NAME ASSIGNED_TO_NAME , DH_OUT.CODE_COMBINATION_ID , A.ASSET_NUMBER , A.ASSET_ID , DH_OUT.BOOK_TYPE_CODE , DH_OUT.TRANSACTION_HEADER_ID_OUT TRANSACTION_HEADER_ID_KEY , DH_OUT.LAST_UPDATE_DATE , DH_OUT.LAST_UPDATED_BY , DH_OUT.LAST_UPDATE_LOGIN , glcc.segment1 || decode(glcc.segment2, NULL, '', ids.concatenated_segment_delimiter||glcc.segment2)|| decode(glcc.segment3, NULL, '', ids.concatenated_segment_delimiter||glcc.segment3)|| decode(glcc.segment4, NULL, '', ids.concatenated_segment_delimiter||glcc.segment4)|| decode(glcc.segment5, NULL, '', ids.concatenated_segment_delimiter||glcc.segment5)|| decode(glcc.segment6, NULL, '', ids.concatenated_segment_delimiter||glcc.segment6)|| decode(glcc.segment7, NULL, '', ids.concatenated_segment_delimiter||glcc.segment7)|| decode(glcc.segment8, NULL, '', ids.concatenated_segment_delimiter||glcc.segment8)|| decode(glcc.segment9, NULL, '', ids.concatenated_segment_delimiter||glcc.segment9)|| decode(glcc.segment10, NULL, '', ids.concatenated_segment_delimiter||glcc.segment10)|| decode(glcc.segment11, NULL, '', ids.concatenated_segment_delimiter||glcc.segment11)|| decode(glcc.segment12, NULL, '', ids.concatenated_segment_delimiter||glcc.segment12)|| decode(glcc.segment13, NULL, '', ids.concatenated_segment_delimiter||glcc.segment13)|| decode(glcc.segment14, NULL, '', ids.concatenated_segment_delimiter||glcc.segment14)|| decode(glcc.segment15, NULL, '', ids.concatenated_segment_delimiter||glcc.segment15)|| decode(glcc.segment16, NULL, '', ids.concatenated_segment_delimiter||glcc.segment16)|| decode(glcc.segment17, NULL, '', ids.concatenated_segment_delimiter||glcc.segment17)|| decode(glcc.segment18, NULL, '', ids.concatenated_segment_delimiter||glcc.segment18)|| decode(glcc.segment19, NULL, '', ids.concatenated_segment_delimiter||glcc.segment19)|| decode(glcc.segment20, NULL, '', ids.concatenated_segment_delimiter||glcc.segment20)|| decode(glcc.segment21, NULL, '', ids.concatenated_segment_delimiter||glcc.segment21)|| decode(glcc.segment22, NULL, '', ids.concatenated_segment_delimiter||glcc.segment22)|| decode(glcc.segment23, NULL, '', ids.concatenated_segment_delimiter||glcc.segment23)|| decode(glcc.segment24, NULL, '', ids.concatenated_segment_delimiter||glcc.segment24)|| decode(glcc.segment25, NULL, '', ids.concatenated_segment_delimiter||glcc.segment25)|| decode(glcc.segment26, NULL, '', ids.concatenated_segment_delimiter||glcc.segment26)|| decode(glcc.segment27, NULL, '', ids.concatenated_segment_delimiter||glcc.segment27)|| decode(glcc.segment28, NULL, '', ids.concatenated_segment_delimiter||glcc.segment28)|| decode(glcc.segment29, NULL, '', ids.concatenated_segment_delimiter||glcc.segment29) || decode(glcc.segment30, NULL, '', ids.concatenated_segment_delimiter||glcc.segment30) ACCT_CONC_SEGMENTS, LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4 LOC_CONC_SEGMENTS , glcc.segment1 || decode(glcc.segment2, NULL, '', ids.concatenated_segment_delimiter||glcc.segment2)|| decode(glcc.segment3, NULL, '', ids.concatenated_segment_delimiter||glcc.segment3)|| decode(glcc.segment4, NULL, '', ids.concatenated_segment_delimiter||glcc.segment4)|| decode(glcc.segment5, NULL, '', ids.concatenated_segment_delimiter||glcc.segment5)|| decode(glcc.segment6, NULL, '', ids.concatenated_segment_delimiter||glcc.segment6)|| decode(glcc.segment7, NULL, '', ids.concatenated_segment_delimiter||glcc.segment7)|| decode(glcc.segment8, NULL, '', ids.concatenated_segment_delimiter||glcc.segment8)|| decode(glcc.segment9, NULL, '', ids.concatenated_segment_delimiter||glcc.segment9)|| decode(glcc.segment10, NULL, '', ids.concatenated_segment_delimiter||glcc.segment10)|| decode(glcc.segment11, NULL, '', ids.concatenated_segment_delimiter||glcc.segment11)|| decode(glcc.segment12, NULL, '', ids.concatenated_segment_delimiter||glcc.segment12)|| decode(glcc.segment13, NULL, '', ids.concatenated_segment_delimiter||glcc.segment13)|| decode(glcc.segment14, NULL, '', ids.concatenated_segment_delimiter||glcc.segment14)|| decode(glcc.segment15, NULL, '', ids.concatenated_segment_delimiter||glcc.segment15)|| decode(glcc.segment16, NULL, '', ids.concatenated_segment_delimiter||glcc.segment16)|| decode(glcc.segment17, NULL, '', ids.concatenated_segment_delimiter||glcc.segment17)|| decode(glcc.segment18, NULL, '', ids.concatenated_segment_delimiter||glcc.segment18)|| decode(glcc.segment19, NULL, '', ids.concatenated_segment_delimiter||glcc.segment19)|| decode(glcc.segment20, NULL, '', ids.concatenated_segment_delimiter||glcc.segment20)|| decode(glcc.segment21, NULL, '', ids.concatenated_segment_delimiter||glcc.segment21)|| decode(glcc.segment22, NULL, '', ids.concatenated_segment_delimiter||glcc.segment22)|| decode(glcc.segment23, NULL, '', ids.concatenated_segment_delimiter||glcc.segment23)|| decode(glcc.segment24, NULL, '', ids.concatenated_segment_delimiter||glcc.segment24)|| decode(glcc.segment25, NULL, '', ids.concatenated_segment_delimiter||glcc.segment25)|| decode(glcc.segment26, NULL, '', ids.concatenated_segment_delimiter||glcc.segment26)|| decode(glcc.segment27, NULL, '', ids.concatenated_segment_delimiter||glcc.segment27)|| decode(glcc.segment28, NULL, '', ids.concatenated_segment_delimiter||glcc.segment28)|| decode(glcc.segment29, NULL, '', ids.concatenated_segment_delimiter||glcc.segment29) || decode(glcc.segment30, NULL, '', ids.concatenated_segment_delimiter||glcc.segment30) ACCOUNTING_CONCATENATED, LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4 LOCATION_CONCATENATED FROM FA_DISTRIBUTION_HISTORY DH_OUT, FA_TRANSACTION_HEADERS TH_OUT, FND_ID_FLEX_STRUCTURES IDS, (select * from per_all_people_f where TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', PERSON_ID, PERSON_TYPE_ID, EMPLOYEE_NUMBER, APPLICANT_NUMBER )) = 'TRUE') P, PER_PERIODS_OF_SERVICE S, FA_SYSTEM_CONTROLS SC, FA_LOCATIONS LOC, FA_BOOK_CONTROLS BC, GL_CODE_COMBINATIONS GLCC, FA_ADDITIONS_B A WHERE A.ASSET_ID = DH_OUT.ASSET_ID and ids.id_flex_code = 'GL#' and ids.id_flex_num = bc.accounting_flex_structure AND DH_OUT.TRANSACTION_HEADER_ID_IN = TH_OUT.TRANSACTION_HEADER_ID AND DH_OUT.BOOK_TYPE_CODE = TH_OUT.BOOK_TYPE_CODE AND DH_OUT.ASSET_ID = TH_OUT.ASSET_ID AND DH_OUT.ASSIGNED_TO = P.PERSON_ID(+) AND P.PERSON_ID = S.PERSON_ID(+) AND GLCC.CODE_COMBINATION_ID = DH_OUT.CODE_COMBINATION_ID AND GLCC.CHART_OF_ACCOUNTS_ID = BC.ACCOUNTING_FLEX_STRUCTURE AND BC.BOOK_TYPE_CODE = DH_OUT.BOOK_TYPE_CODE AND LOC.LOCATION_ID = DH_OUT.LOCATION_ID UNION SELECT DH.ROWID ROW_ID, DH.DISTRIBUTION_ID, DH.UNITS_ASSIGNED, DH.UNITS_ASSIGNED, TH_IN.TRANSACTION_TYPE_CODE, TH_IN.TRANSACTION_HEADER_ID, DH.LOCATION_ID, DH.ASSIGNED_TO, P.EMPLOYEE_NUMBER, P.FULL_NAME , DH.CODE_COMBINATION_ID, A.ASSET_NUMBER, A.ASSET_ID, DH.BOOK_TYPE_CODE, DH.TRANSACTION_HEADER_ID_IN, DH.LAST_UPDATE_DATE, DH.LAST_UPDATED_BY, DH.LAST_UPDATE_LOGIN, glcc.segment1 || decode(glcc.segment2, NULL, '', ids.concatenated_segment_delimiter||glcc.segment2)|| decode(glcc.segment3, NULL, '', ids.concatenated_segment_delimiter||glcc.segment3)|| decode(glcc.segment4, NULL, '', ids.concatenated_segment_delimiter||glcc.segment4)|| decode(glcc.segment5, NULL, '', ids.concatenated_segment_delimiter||glcc.segment5)|| decode(glcc.segment6, NULL, '', ids.concatenated_segment_delimiter||glcc.segment6)|| decode(glcc.segment7, NULL, '', ids.concatenated_segment_delimiter||glcc.segment7)|| decode(glcc.segment8, NULL, '', ids.concatenated_segment_delimiter||glcc.segment8)|| decode(glcc.segment9, NULL, '', ids.concatenated_segment_delimiter||glcc.segment9)|| decode(glcc.segment10, NULL, '', ids.concatenated_segment_delimiter||glcc.segment10)|| decode(glcc.segment11, NULL, '', ids.concatenated_segment_delimiter||glcc.segment11)|| decode(glcc.segment12, NULL, '', ids.concatenated_segment_delimiter||glcc.segment12)|| decode(glcc.segment13, NULL, '', ids.concatenated_segment_delimiter||glcc.segment13)|| decode(glcc.segment14, NULL, '', ids.concatenated_segment_delimiter||glcc.segment14)|| decode(glcc.segment15, NULL, '', ids.concatenated_segment_delimiter||glcc.segment15)|| decode(glcc.segment16, NULL, '', ids.concatenated_segment_delimiter||glcc.segment16)|| decode(glcc.segment17, NULL, '', ids.concatenated_segment_delimiter||glcc.segment17)|| decode(glcc.segment18, NULL, '', ids.concatenated_segment_delimiter||glcc.segment18)|| decode(glcc.segment19, NULL, '', ids.concatenated_segment_delimiter||glcc.segment19)|| decode(glcc.segment20, NULL, '', ids.concatenated_segment_delimiter||glcc.segment20)|| decode(glcc.segment21, NULL, '', ids.concatenated_segment_delimiter||glcc.segment21)|| decode(glcc.segment22, NULL, '', ids.concatenated_segment_delimiter||glcc.segment22)|| decode(glcc.segment23, NULL, '', ids.concatenated_segment_delimiter||glcc.segment23)|| decode(glcc.segment24, NULL, '', ids.concatenated_segment_delimiter||glcc.segment24)|| decode(glcc.segment25, NULL, '', ids.concatenated_segment_delimiter||glcc.segment25)|| decode(glcc.segment26, NULL, '', ids.concatenated_segment_delimiter||glcc.segment26)|| decode(glcc.segment27, NULL, '', ids.concatenated_segment_delimiter||glcc.segment27)|| decode(glcc.segment28, NULL, '', ids.concatenated_segment_delimiter||glcc.segment28)|| decode(glcc.segment29, NULL, '', ids.concatenated_segment_delimiter||glcc.segment29) || decode(glcc.segment30, NULL, '', ids.concatenated_segment_delimiter||glcc.segment30) , LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4 , glcc.segment1 || decode(glcc.segment2, NULL, '', ids.concatenated_segment_delimiter||glcc.segment2)|| decode(glcc.segment3, NULL, '', ids.concatenated_segment_delimiter||glcc.segment3)|| decode(glcc.segment4, NULL, '', ids.concatenated_segment_delimiter||glcc.segment4)|| decode(glcc.segment5, NULL, '', ids.concatenated_segment_delimiter||glcc.segment5)|| decode(glcc.segment6, NULL, '', ids.concatenated_segment_delimiter||glcc.segment6)|| decode(glcc.segment7, NULL, '', ids.concatenated_segment_delimiter||glcc.segment7)|| decode(glcc.segment8, NULL, '', ids.concatenated_segment_delimiter||glcc.segment8)|| decode(glcc.segment9, NULL, '', ids.concatenated_segment_delimiter||glcc.segment9)|| decode(glcc.segment10, NULL, '', ids.concatenated_segment_delimiter||glcc.segment10)|| decode(glcc.segment11, NULL, '', ids.concatenated_segment_delimiter||glcc.segment11)|| decode(glcc.segment12, NULL, '', ids.concatenated_segment_delimiter||glcc.segment12)|| decode(glcc.segment13, NULL, '', ids.concatenated_segment_delimiter||glcc.segment13)|| decode(glcc.segment14, NULL, '', ids.concatenated_segment_delimiter||glcc.segment14)|| decode(glcc.segment15, NULL, '', ids.concatenated_segment_delimiter||glcc.segment15)|| decode(glcc.segment16, NULL, '', ids.concatenated_segment_delimiter||glcc.segment16)|| decode(glcc.segment17, NULL, '', ids.concatenated_segment_delimiter||glcc.segment17)|| decode(glcc.segment18, NULL, '', ids.concatenated_segment_delimiter||glcc.segment18)|| decode(glcc.segment19, NULL, '', ids.concatenated_segment_delimiter||glcc.segment19)|| decode(glcc.segment20, NULL, '', ids.concatenated_segment_delimiter||glcc.segment20)|| decode(glcc.segment21, NULL, '', ids.concatenated_segment_delimiter||glcc.segment21)|| decode(glcc.segment22, NULL, '', ids.concatenated_segment_delimiter||glcc.segment22)|| decode(glcc.segment23, NULL, '', ids.concatenated_segment_delimiter||glcc.segment23)|| decode(glcc.segment24, NULL, '', ids.concatenated_segment_delimiter||glcc.segment24)|| decode(glcc.segment25, NULL, '', ids.concatenated_segment_delimiter||glcc.segment25)|| decode(glcc.segment26, NULL, '', ids.concatenated_segment_delimiter||glcc.segment26)|| decode(glcc.segment27, NULL, '', ids.concatenated_segment_delimiter||glcc.segment27)|| decode(glcc.segment28, NULL, '', ids.concatenated_segment_delimiter||glcc.segment28)|| decode(glcc.segment29, NULL, '', ids.concatenated_segment_delimiter||glcc.segment29) || decode(glcc.segment30, NULL, '', ids.concatenated_segment_delimiter||glcc.segment30) , LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4 FROM FA_DISTRIBUTION_HISTORY DH, FA_TRANSACTION_HEADERS TH_IN, fnd_id_flex_structures ids, (select * from PER_ALL_PEOPLE_F where TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE and DECODE(HR_SECURITY.VIEW_ALL ,'Y' , 'TRUE', HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F', PERSON_ID, PERSON_TYPE_ID, EMPLOYEE_NUMBER, APPLICANT_NUMBER )) = 'TRUE') P, PER_PERIODS_OF_SERVICE S, FA_ADDITIONS_B A, FA_SYSTEM_CONTROLS SC, FA_LOCATIONS LOC, FA_BOOK_CONTROLS BC, GL_CODE_COMBINATIONS GLCC WHERE A.ASSET_ID = DH.ASSET_ID and ids.id_flex_code = 'GL#' and ids.id_flex_num = bc.accounting_flex_structure AND DH.TRANSACTION_HEADER_ID_IN = TH_IN.TRANSACTION_HEADER_ID AND TH_IN.ASSET_ID = A.ASSET_ID AND TH_IN.BOOK_TYPE_CODE = DH.BOOK_TYPE_CODE AND DH.ASSIGNED_TO = P.PERSON_ID(+) AND P.PERSON_ID = S.PERSON_ID(+) AND GLCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID AND GLCC.CHART_OF_ACCOUNTS_ID = BC.ACCOUNTING_FLEX_STRUCTURE AND BC.BOOK_TYPE_CODE = DH.BOOK_TYPE_CODE AND LOC.LOCATION_ID = DH.LOCATION_ID AND (DH.CODE_COMBINATION_ID,DH.LOCATION_ID,NVL(DH.ASSIGNED_TO,-99)) NOT IN (SELECT DHX.CODE_COMBINATION_ID, DHX.LOCATION_ID, NVL(DHX.ASSIGNED_TO, -99) FROM FA_DISTRIBUTION_HISTORY DHX WHERE DHX.TRANSACTION_HEADER_ID_OUT = DH.TRANSACTION_HEADER_ID_IN)

View Text - HTML Formatted

SELECT DH_OUT.ROWID ROW_ID
, DH_OUT.DISTRIBUTION_ID ID
, DH_OUT.TRANSACTION_UNITS TRANS_UNITS
, DH_OUT.UNITS_ASSIGNED UNITS
, TH_OUT.TRANSACTION_TYPE_CODE TRANSTYPE
, DH_OUT.TRANSACTION_HEADER_ID_IN TRANSACTION_HEADER_ID
, DH_OUT.LOCATION_ID
, DH_OUT.ASSIGNED_TO
, P.EMPLOYEE_NUMBER ASSIGNED_TO_NUMBER
, P.FULL_NAME ASSIGNED_TO_NAME
, DH_OUT.CODE_COMBINATION_ID
, A.ASSET_NUMBER
, A.ASSET_ID
, DH_OUT.BOOK_TYPE_CODE
, DH_OUT.TRANSACTION_HEADER_ID_OUT TRANSACTION_HEADER_ID_KEY
, DH_OUT.LAST_UPDATE_DATE
, DH_OUT.LAST_UPDATED_BY
, DH_OUT.LAST_UPDATE_LOGIN
, GLCC.SEGMENT1 || DECODE(GLCC.SEGMENT2
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT2)|| DECODE(GLCC.SEGMENT3
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT3)|| DECODE(GLCC.SEGMENT4
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT4)|| DECODE(GLCC.SEGMENT5
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT5)|| DECODE(GLCC.SEGMENT6
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT6)|| DECODE(GLCC.SEGMENT7
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT7)|| DECODE(GLCC.SEGMENT8
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT8)|| DECODE(GLCC.SEGMENT9
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT9)|| DECODE(GLCC.SEGMENT10
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT10)|| DECODE(GLCC.SEGMENT11
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT11)|| DECODE(GLCC.SEGMENT12
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT12)|| DECODE(GLCC.SEGMENT13
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT13)|| DECODE(GLCC.SEGMENT14
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT14)|| DECODE(GLCC.SEGMENT15
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT15)|| DECODE(GLCC.SEGMENT16
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT16)|| DECODE(GLCC.SEGMENT17
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT17)|| DECODE(GLCC.SEGMENT18
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT18)|| DECODE(GLCC.SEGMENT19
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT19)|| DECODE(GLCC.SEGMENT20
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT20)|| DECODE(GLCC.SEGMENT21
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT21)|| DECODE(GLCC.SEGMENT22
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT22)|| DECODE(GLCC.SEGMENT23
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT23)|| DECODE(GLCC.SEGMENT24
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT24)|| DECODE(GLCC.SEGMENT25
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT25)|| DECODE(GLCC.SEGMENT26
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT26)|| DECODE(GLCC.SEGMENT27
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT27)|| DECODE(GLCC.SEGMENT28
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT28)|| DECODE(GLCC.SEGMENT29
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT29) || DECODE(GLCC.SEGMENT30
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT30) ACCT_CONC_SEGMENTS
, LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4 LOC_CONC_SEGMENTS
, GLCC.SEGMENT1 || DECODE(GLCC.SEGMENT2
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT2)|| DECODE(GLCC.SEGMENT3
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT3)|| DECODE(GLCC.SEGMENT4
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT4)|| DECODE(GLCC.SEGMENT5
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT5)|| DECODE(GLCC.SEGMENT6
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT6)|| DECODE(GLCC.SEGMENT7
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT7)|| DECODE(GLCC.SEGMENT8
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT8)|| DECODE(GLCC.SEGMENT9
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT9)|| DECODE(GLCC.SEGMENT10
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT10)|| DECODE(GLCC.SEGMENT11
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT11)|| DECODE(GLCC.SEGMENT12
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT12)|| DECODE(GLCC.SEGMENT13
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT13)|| DECODE(GLCC.SEGMENT14
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT14)|| DECODE(GLCC.SEGMENT15
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT15)|| DECODE(GLCC.SEGMENT16
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT16)|| DECODE(GLCC.SEGMENT17
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT17)|| DECODE(GLCC.SEGMENT18
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT18)|| DECODE(GLCC.SEGMENT19
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT19)|| DECODE(GLCC.SEGMENT20
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT20)|| DECODE(GLCC.SEGMENT21
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT21)|| DECODE(GLCC.SEGMENT22
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT22)|| DECODE(GLCC.SEGMENT23
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT23)|| DECODE(GLCC.SEGMENT24
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT24)|| DECODE(GLCC.SEGMENT25
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT25)|| DECODE(GLCC.SEGMENT26
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT26)|| DECODE(GLCC.SEGMENT27
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT27)|| DECODE(GLCC.SEGMENT28
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT28)|| DECODE(GLCC.SEGMENT29
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT29) || DECODE(GLCC.SEGMENT30
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT30) ACCOUNTING_CONCATENATED
, LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4 LOCATION_CONCATENATED
FROM FA_DISTRIBUTION_HISTORY DH_OUT
, FA_TRANSACTION_HEADERS TH_OUT
, FND_ID_FLEX_STRUCTURES IDS
, (SELECT *
FROM PER_ALL_PEOPLE_F
WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, PERSON_ID
, PERSON_TYPE_ID
, EMPLOYEE_NUMBER
, APPLICANT_NUMBER )) = 'TRUE') P
, PER_PERIODS_OF_SERVICE S
, FA_SYSTEM_CONTROLS SC
, FA_LOCATIONS LOC
, FA_BOOK_CONTROLS BC
, GL_CODE_COMBINATIONS GLCC
, FA_ADDITIONS_B A
WHERE A.ASSET_ID = DH_OUT.ASSET_ID
AND IDS.ID_FLEX_CODE = 'GL#'
AND IDS.ID_FLEX_NUM = BC.ACCOUNTING_FLEX_STRUCTURE
AND DH_OUT.TRANSACTION_HEADER_ID_IN = TH_OUT.TRANSACTION_HEADER_ID
AND DH_OUT.BOOK_TYPE_CODE = TH_OUT.BOOK_TYPE_CODE
AND DH_OUT.ASSET_ID = TH_OUT.ASSET_ID
AND DH_OUT.ASSIGNED_TO = P.PERSON_ID(+)
AND P.PERSON_ID = S.PERSON_ID(+)
AND GLCC.CODE_COMBINATION_ID = DH_OUT.CODE_COMBINATION_ID
AND GLCC.CHART_OF_ACCOUNTS_ID = BC.ACCOUNTING_FLEX_STRUCTURE
AND BC.BOOK_TYPE_CODE = DH_OUT.BOOK_TYPE_CODE
AND LOC.LOCATION_ID = DH_OUT.LOCATION_ID UNION SELECT DH.ROWID ROW_ID
, DH.DISTRIBUTION_ID
, DH.UNITS_ASSIGNED
, DH.UNITS_ASSIGNED
, TH_IN.TRANSACTION_TYPE_CODE
, TH_IN.TRANSACTION_HEADER_ID
, DH.LOCATION_ID
, DH.ASSIGNED_TO
, P.EMPLOYEE_NUMBER
, P.FULL_NAME
, DH.CODE_COMBINATION_ID
, A.ASSET_NUMBER
, A.ASSET_ID
, DH.BOOK_TYPE_CODE
, DH.TRANSACTION_HEADER_ID_IN
, DH.LAST_UPDATE_DATE
, DH.LAST_UPDATED_BY
, DH.LAST_UPDATE_LOGIN
, GLCC.SEGMENT1 || DECODE(GLCC.SEGMENT2
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT2)|| DECODE(GLCC.SEGMENT3
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT3)|| DECODE(GLCC.SEGMENT4
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT4)|| DECODE(GLCC.SEGMENT5
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT5)|| DECODE(GLCC.SEGMENT6
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT6)|| DECODE(GLCC.SEGMENT7
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT7)|| DECODE(GLCC.SEGMENT8
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT8)|| DECODE(GLCC.SEGMENT9
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT9)|| DECODE(GLCC.SEGMENT10
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT10)|| DECODE(GLCC.SEGMENT11
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT11)|| DECODE(GLCC.SEGMENT12
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT12)|| DECODE(GLCC.SEGMENT13
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT13)|| DECODE(GLCC.SEGMENT14
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT14)|| DECODE(GLCC.SEGMENT15
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT15)|| DECODE(GLCC.SEGMENT16
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT16)|| DECODE(GLCC.SEGMENT17
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT17)|| DECODE(GLCC.SEGMENT18
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT18)|| DECODE(GLCC.SEGMENT19
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT19)|| DECODE(GLCC.SEGMENT20
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT20)|| DECODE(GLCC.SEGMENT21
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT21)|| DECODE(GLCC.SEGMENT22
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT22)|| DECODE(GLCC.SEGMENT23
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT23)|| DECODE(GLCC.SEGMENT24
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT24)|| DECODE(GLCC.SEGMENT25
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT25)|| DECODE(GLCC.SEGMENT26
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT26)|| DECODE(GLCC.SEGMENT27
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT27)|| DECODE(GLCC.SEGMENT28
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT28)|| DECODE(GLCC.SEGMENT29
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT29) || DECODE(GLCC.SEGMENT30
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT30)
, LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4
, GLCC.SEGMENT1 || DECODE(GLCC.SEGMENT2
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT2)|| DECODE(GLCC.SEGMENT3
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT3)|| DECODE(GLCC.SEGMENT4
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT4)|| DECODE(GLCC.SEGMENT5
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT5)|| DECODE(GLCC.SEGMENT6
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT6)|| DECODE(GLCC.SEGMENT7
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT7)|| DECODE(GLCC.SEGMENT8
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT8)|| DECODE(GLCC.SEGMENT9
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT9)|| DECODE(GLCC.SEGMENT10
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT10)|| DECODE(GLCC.SEGMENT11
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT11)|| DECODE(GLCC.SEGMENT12
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT12)|| DECODE(GLCC.SEGMENT13
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT13)|| DECODE(GLCC.SEGMENT14
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT14)|| DECODE(GLCC.SEGMENT15
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT15)|| DECODE(GLCC.SEGMENT16
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT16)|| DECODE(GLCC.SEGMENT17
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT17)|| DECODE(GLCC.SEGMENT18
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT18)|| DECODE(GLCC.SEGMENT19
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT19)|| DECODE(GLCC.SEGMENT20
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT20)|| DECODE(GLCC.SEGMENT21
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT21)|| DECODE(GLCC.SEGMENT22
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT22)|| DECODE(GLCC.SEGMENT23
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT23)|| DECODE(GLCC.SEGMENT24
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT24)|| DECODE(GLCC.SEGMENT25
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT25)|| DECODE(GLCC.SEGMENT26
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT26)|| DECODE(GLCC.SEGMENT27
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT27)|| DECODE(GLCC.SEGMENT28
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT28)|| DECODE(GLCC.SEGMENT29
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT29) || DECODE(GLCC.SEGMENT30
, NULL
, ''
, IDS.CONCATENATED_SEGMENT_DELIMITER||GLCC.SEGMENT30)
, LOC.SEGMENT1 || '.' || LOC.SEGMENT2 || '.' || LOC.SEGMENT3 || '.' || LOC.SEGMENT4
FROM FA_DISTRIBUTION_HISTORY DH
, FA_TRANSACTION_HEADERS TH_IN
, FND_ID_FLEX_STRUCTURES IDS
, (SELECT *
FROM PER_ALL_PEOPLE_F
WHERE TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE
AND EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD('PER_ALL_PEOPLE_F'
, PERSON_ID
, PERSON_TYPE_ID
, EMPLOYEE_NUMBER
, APPLICANT_NUMBER )) = 'TRUE') P
, PER_PERIODS_OF_SERVICE S
, FA_ADDITIONS_B A
, FA_SYSTEM_CONTROLS SC
, FA_LOCATIONS LOC
, FA_BOOK_CONTROLS BC
, GL_CODE_COMBINATIONS GLCC
WHERE A.ASSET_ID = DH.ASSET_ID
AND IDS.ID_FLEX_CODE = 'GL#'
AND IDS.ID_FLEX_NUM = BC.ACCOUNTING_FLEX_STRUCTURE
AND DH.TRANSACTION_HEADER_ID_IN = TH_IN.TRANSACTION_HEADER_ID
AND TH_IN.ASSET_ID = A.ASSET_ID
AND TH_IN.BOOK_TYPE_CODE = DH.BOOK_TYPE_CODE
AND DH.ASSIGNED_TO = P.PERSON_ID(+)
AND P.PERSON_ID = S.PERSON_ID(+)
AND GLCC.CODE_COMBINATION_ID = DH.CODE_COMBINATION_ID
AND GLCC.CHART_OF_ACCOUNTS_ID = BC.ACCOUNTING_FLEX_STRUCTURE
AND BC.BOOK_TYPE_CODE = DH.BOOK_TYPE_CODE
AND LOC.LOCATION_ID = DH.LOCATION_ID
AND (DH.CODE_COMBINATION_ID
, DH.LOCATION_ID
, NVL(DH.ASSIGNED_TO
, -99)) NOT IN (SELECT DHX.CODE_COMBINATION_ID
, DHX.LOCATION_ID
, NVL(DHX.ASSIGNED_TO
, -99)
FROM FA_DISTRIBUTION_HISTORY DHX
WHERE DHX.TRANSACTION_HEADER_ID_OUT = DH.TRANSACTION_HEADER_ID_IN)