VIEW: APPS.FA_EMP_ASSET_WEB_V

Source

View Text - Preformatted

SELECT DH.DISTRIBUTION_ID , DH.BOOK_TYPE_CODE , DH.UNITS_ASSIGNED , DH.CODE_COMBINATION_ID , DH.LOCATION_ID , DH.TRANSACTION_HEADER_ID_IN , DH.DATE_EFFECTIVE DIST_DATE_EFFECTIVE , DH.DATE_INEFFECTIVE DIST_DATE_INEFFECTIVE , DH.ASSIGNED_TO , DH.TRANSACTION_HEADER_ID_OUT , DH.TRANSACTION_UNITS , DH.RETIREMENT_ID , PP.PERSON_ID , PP.EFFECTIVE_START_DATE , PP.EFFECTIVE_END_DATE , PP.BUSINESS_GROUP_ID , PP.PERSON_TYPE_ID , PP.LAST_NAME , PP.START_DATE , PP.APPLICANT_NUMBER , PP.COMMENT_ID , PP.CURRENT_APPLICANT_FLAG , PP.CURRENT_EMP_OR_APL_FLAG , PP.CURRENT_EMPLOYEE_FLAG , PP.DATE_EMPLOYEE_DATA_VERIFIED , PP.DATE_OF_BIRTH , PP.EMAIL_ADDRESS , PP.EMPLOYEE_NUMBER , PP.EXPENSE_CHECK_SEND_TO_ADDRESS , PP.FIRST_NAME , PP.FULL_NAME , PP.KNOWN_AS , PP.MARITAL_STATUS , PP.MIDDLE_NAMES , PP.NATIONALITY , PP.NATIONAL_IDENTIFIER , PP.PREVIOUS_LAST_NAME , PP.REGISTERED_DISABLED_FLAG , PP.SEX , PP.TITLE , PP.VENDOR_ID , PP.WORK_TELEPHONE , PP.PER_INFORMATION_CATEGORY , PP.PER_INFORMATION1 , PP.PER_INFORMATION2 , PP.PER_INFORMATION3 , PP.PER_INFORMATION4 , PP.PER_INFORMATION5 , PP.PER_INFORMATION6 , PP.PER_INFORMATION7 , PP.PER_INFORMATION8 , PP.PER_INFORMATION9 , PP.PER_INFORMATION10 , PP.PER_INFORMATION11 , PP.PER_INFORMATION12 , PP.PER_INFORMATION13 , PP.PER_INFORMATION14 , PP.PER_INFORMATION15 , PP.PER_INFORMATION16 , PP.PER_INFORMATION17 , PP.PER_INFORMATION18 , PP.PER_INFORMATION19 , PP.PER_INFORMATION20 , AD.ASSET_ID , AD.ASSET_NUMBER , AD.ASSET_KEY_CCID , AD.CURRENT_UNITS , AD.ASSET_TYPE , AD.TAG_NUMBER , AD.DESCRIPTION ASSET_DESCRIPTION , AD.ASSET_CATEGORY_ID , AD.PARENT_ASSET_ID , AD.MANUFACTURER_NAME , AD.SERIAL_NUMBER , AD.MODEL_NUMBER , AD.PROPERTY_TYPE_CODE , AD.PROPERTY_1245_1250_CODE , AD.IN_USE_FLAG , AD.OWNED_LEASED , AD.NEW_USED , AD.UNIT_ADJUSTMENT_FLAG , AD.ADD_COST_JE_FLAG , AD.LEASE_ID , CA.CATEGORY_TYPE , CA.CAPITALIZE_FLAG , rtrim( decode (AK.SEGMENT1, NULL, '', AK.SEGMENT1 || '.') || decode (AK.SEGMENT2, NULL, '', AK.SEGMENT2 || '.') || decode (AK.SEGMENT3, NULL, '', AK.SEGMENT3 || '.') || decode (AK.SEGMENT4, NULL, '', AK.SEGMENT4 || '.') || decode (AK.SEGMENT5, NULL, '', AK.SEGMENT5 || '.') || decode (AK.SEGMENT6, NULL, '', AK.SEGMENT6 || '.') || decode (AK.SEGMENT7, NULL, '', AK.SEGMENT7 || '.') || decode (AK.SEGMENT8, NULL, '', AK.SEGMENT8 || '.') || decode (AK.SEGMENT9, NULL, '', AK.SEGMENT9 || '.') || decode (AK.SEGMENT10, NULL, '', AK.SEGMENT10 || '.'), '.') ASSET_KEY , CK.CONCATENATED_SEGMENTS CATEGORY , LK.CONCATENATED_SEGMENTS LOC_CONC_SEGMENTS , GLCC.CONCATENATED_SEGMENTS ACCT_CONC_SEGMENTS FROM FA_CATEGORIES CA, FA_ADDITIONS AD, FA_CATEGORIES_B_KFV CK, FA_ASSET_KEYWORDS AK, FA_LOCATIONS_KFV LK, GL_CODE_COMBINATIONS_KFV GLCC, PER_ALL_PEOPLE_F PP, FA_DISTRIBUTION_HISTORY DH WHERE DH.ASSET_ID = AD.ASSET_ID AND DH.DATE_INEFFECTIVE IS NULL AND AD.ASSET_CATEGORY_ID = CA.CATEGORY_ID AND DH.LOCATION_ID = LK.LOCATION_ID AND DH.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID AND AD.ASSET_KEY_CCID = AK.CODE_COMBINATION_ID(+) AND AD.ASSET_CATEGORY_ID = CK.CATEGORY_ID AND DH.ASSIGNED_TO = PP.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE AND PP.EFFECTIVE_END_DATE AND DECODE(HR_SECURITY.VIEW_ALL , 'Y', 'TRUE', HR_SECURITY.SHOW_RECORD ('PER_ALL_PEOPLE_F', PP.PERSON_ID, PP.PERSON_TYPE_ID, PP.EMPLOYEE_NUMBER,PP.APPLICANT_NUMBER )) = 'TRUE' UNION SELECT DH.DISTRIBUTION_ID , DH.BOOK_TYPE_CODE , DH.UNITS_ASSIGNED , DH.CODE_COMBINATION_ID , DH.LOCATION_ID , DH.TRANSACTION_HEADER_ID_IN , DH.DATE_EFFECTIVE DIST_DATE_EFFECTIVE , DH.DATE_INEFFECTIVE DIST_DATE_INEFFECTIVE , DH.ASSIGNED_TO , DH.TRANSACTION_HEADER_ID_OUT , DH.TRANSACTION_UNITS , DH.RETIREMENT_ID , to_number (null) , to_date (null) , to_date (null) , to_number (null) , to_number (null) , null , to_date (null) , null , to_number (null) i, null , null , null , to_date (null) , to_date (null) , null , null , null , null , null , null , null , null , null , null , null , null , null , null , to_number (null) , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , null , AD.ASSET_ID , AD.ASSET_NUMBER , AD.ASSET_KEY_CCID , AD.CURRENT_UNITS , AD.ASSET_TYPE , AD.TAG_NUMBER , AD.DESCRIPTION ASSET_DESCRIPTION , AD.ASSET_CATEGORY_ID , AD.PARENT_ASSET_ID , AD.MANUFACTURER_NAME , AD.SERIAL_NUMBER , AD.MODEL_NUMBER , AD.PROPERTY_TYPE_CODE , AD.PROPERTY_1245_1250_CODE , AD.IN_USE_FLAG , AD.OWNED_LEASED , AD.NEW_USED , AD.UNIT_ADJUSTMENT_FLAG , AD.ADD_COST_JE_FLAG , AD.LEASE_ID , CA.CATEGORY_TYPE , CA.CAPITALIZE_FLAG , rtrim( decode (AK.SEGMENT1, NULL, '', AK.SEGMENT1 || '.') || decode (AK.SEGMENT2, NULL, '', AK.SEGMENT2 || '.') || decode (AK.SEGMENT3, NULL, '', AK.SEGMENT3 || '.') || decode (AK.SEGMENT4, NULL, '', AK.SEGMENT4 || '.') || decode (AK.SEGMENT5, NULL, '', AK.SEGMENT5 || '.') || decode (AK.SEGMENT6, NULL, '', AK.SEGMENT6 || '.') || decode (AK.SEGMENT7, NULL, '', AK.SEGMENT7 || '.') || decode (AK.SEGMENT8, NULL, '', AK.SEGMENT8 || '.') || decode (AK.SEGMENT9, NULL, '', AK.SEGMENT9 || '.') || decode (AK.SEGMENT10, NULL, '', AK.SEGMENT10 || '.'), '.') ASSET_KEY , CK.CONCATENATED_SEGMENTS CATEGORY , LK.CONCATENATED_SEGMENTS LOC_CONC_SEGMENTS , GLCC.CONCATENATED_SEGMENTS ACCT_CONC_SEGMENTS FROM FA_CATEGORIES CA, FA_ADDITIONS AD, FA_CATEGORIES_B_KFV CK, FA_ASSET_KEYWORDS AK, FA_LOCATIONS_KFV LK, GL_CODE_COMBINATIONS_KFV GLCC, FA_DISTRIBUTION_HISTORY DH WHERE DH.ASSET_ID = AD.ASSET_ID AND DH.DATE_INEFFECTIVE IS NULL AND AD.ASSET_CATEGORY_ID = CA.CATEGORY_ID AND DH.LOCATION_ID = LK.LOCATION_ID AND DH.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID AND AD.ASSET_KEY_CCID = AK.CODE_COMBINATION_ID(+) AND AD.ASSET_CATEGORY_ID = CK.CATEGORY_ID AND DH.ASSIGNED_TO IS NULL

View Text - HTML Formatted

SELECT DH.DISTRIBUTION_ID
, DH.BOOK_TYPE_CODE
, DH.UNITS_ASSIGNED
, DH.CODE_COMBINATION_ID
, DH.LOCATION_ID
, DH.TRANSACTION_HEADER_ID_IN
, DH.DATE_EFFECTIVE DIST_DATE_EFFECTIVE
, DH.DATE_INEFFECTIVE DIST_DATE_INEFFECTIVE
, DH.ASSIGNED_TO
, DH.TRANSACTION_HEADER_ID_OUT
, DH.TRANSACTION_UNITS
, DH.RETIREMENT_ID
, PP.PERSON_ID
, PP.EFFECTIVE_START_DATE
, PP.EFFECTIVE_END_DATE
, PP.BUSINESS_GROUP_ID
, PP.PERSON_TYPE_ID
, PP.LAST_NAME
, PP.START_DATE
, PP.APPLICANT_NUMBER
, PP.COMMENT_ID
, PP.CURRENT_APPLICANT_FLAG
, PP.CURRENT_EMP_OR_APL_FLAG
, PP.CURRENT_EMPLOYEE_FLAG
, PP.DATE_EMPLOYEE_DATA_VERIFIED
, PP.DATE_OF_BIRTH
, PP.EMAIL_ADDRESS
, PP.EMPLOYEE_NUMBER
, PP.EXPENSE_CHECK_SEND_TO_ADDRESS
, PP.FIRST_NAME
, PP.FULL_NAME
, PP.KNOWN_AS
, PP.MARITAL_STATUS
, PP.MIDDLE_NAMES
, PP.NATIONALITY
, PP.NATIONAL_IDENTIFIER
, PP.PREVIOUS_LAST_NAME
, PP.REGISTERED_DISABLED_FLAG
, PP.SEX
, PP.TITLE
, PP.VENDOR_ID
, PP.WORK_TELEPHONE
, PP.PER_INFORMATION_CATEGORY
, PP.PER_INFORMATION1
, PP.PER_INFORMATION2
, PP.PER_INFORMATION3
, PP.PER_INFORMATION4
, PP.PER_INFORMATION5
, PP.PER_INFORMATION6
, PP.PER_INFORMATION7
, PP.PER_INFORMATION8
, PP.PER_INFORMATION9
, PP.PER_INFORMATION10
, PP.PER_INFORMATION11
, PP.PER_INFORMATION12
, PP.PER_INFORMATION13
, PP.PER_INFORMATION14
, PP.PER_INFORMATION15
, PP.PER_INFORMATION16
, PP.PER_INFORMATION17
, PP.PER_INFORMATION18
, PP.PER_INFORMATION19
, PP.PER_INFORMATION20
, AD.ASSET_ID
, AD.ASSET_NUMBER
, AD.ASSET_KEY_CCID
, AD.CURRENT_UNITS
, AD.ASSET_TYPE
, AD.TAG_NUMBER
, AD.DESCRIPTION ASSET_DESCRIPTION
, AD.ASSET_CATEGORY_ID
, AD.PARENT_ASSET_ID
, AD.MANUFACTURER_NAME
, AD.SERIAL_NUMBER
, AD.MODEL_NUMBER
, AD.PROPERTY_TYPE_CODE
, AD.PROPERTY_1245_1250_CODE
, AD.IN_USE_FLAG
, AD.OWNED_LEASED
, AD.NEW_USED
, AD.UNIT_ADJUSTMENT_FLAG
, AD.ADD_COST_JE_FLAG
, AD.LEASE_ID
, CA.CATEGORY_TYPE
, CA.CAPITALIZE_FLAG
, RTRIM( DECODE (AK.SEGMENT1
, NULL
, ''
, AK.SEGMENT1 || '.') || DECODE (AK.SEGMENT2
, NULL
, ''
, AK.SEGMENT2 || '.') || DECODE (AK.SEGMENT3
, NULL
, ''
, AK.SEGMENT3 || '.') || DECODE (AK.SEGMENT4
, NULL
, ''
, AK.SEGMENT4 || '.') || DECODE (AK.SEGMENT5
, NULL
, ''
, AK.SEGMENT5 || '.') || DECODE (AK.SEGMENT6
, NULL
, ''
, AK.SEGMENT6 || '.') || DECODE (AK.SEGMENT7
, NULL
, ''
, AK.SEGMENT7 || '.') || DECODE (AK.SEGMENT8
, NULL
, ''
, AK.SEGMENT8 || '.') || DECODE (AK.SEGMENT9
, NULL
, ''
, AK.SEGMENT9 || '.') || DECODE (AK.SEGMENT10
, NULL
, ''
, AK.SEGMENT10 || '.')
, '.') ASSET_KEY
, CK.CONCATENATED_SEGMENTS CATEGORY
, LK.CONCATENATED_SEGMENTS LOC_CONC_SEGMENTS
, GLCC.CONCATENATED_SEGMENTS ACCT_CONC_SEGMENTS
FROM FA_CATEGORIES CA
, FA_ADDITIONS AD
, FA_CATEGORIES_B_KFV CK
, FA_ASSET_KEYWORDS AK
, FA_LOCATIONS_KFV LK
, GL_CODE_COMBINATIONS_KFV GLCC
, PER_ALL_PEOPLE_F PP
, FA_DISTRIBUTION_HISTORY DH
WHERE DH.ASSET_ID = AD.ASSET_ID
AND DH.DATE_INEFFECTIVE IS NULL
AND AD.ASSET_CATEGORY_ID = CA.CATEGORY_ID
AND DH.LOCATION_ID = LK.LOCATION_ID
AND DH.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
AND AD.ASSET_KEY_CCID = AK.CODE_COMBINATION_ID(+)
AND AD.ASSET_CATEGORY_ID = CK.CATEGORY_ID
AND DH.ASSIGNED_TO = PP.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND DECODE(HR_SECURITY.VIEW_ALL
, 'Y'
, 'TRUE'
, HR_SECURITY.SHOW_RECORD ('PER_ALL_PEOPLE_F'
, PP.PERSON_ID
, PP.PERSON_TYPE_ID
, PP.EMPLOYEE_NUMBER
, PP.APPLICANT_NUMBER )) = 'TRUE' UNION SELECT DH.DISTRIBUTION_ID
, DH.BOOK_TYPE_CODE
, DH.UNITS_ASSIGNED
, DH.CODE_COMBINATION_ID
, DH.LOCATION_ID
, DH.TRANSACTION_HEADER_ID_IN
, DH.DATE_EFFECTIVE DIST_DATE_EFFECTIVE
, DH.DATE_INEFFECTIVE DIST_DATE_INEFFECTIVE
, DH.ASSIGNED_TO
, DH.TRANSACTION_HEADER_ID_OUT
, DH.TRANSACTION_UNITS
, DH.RETIREMENT_ID
, TO_NUMBER (NULL)
, TO_DATE (NULL)
, TO_DATE (NULL)
, TO_NUMBER (NULL)
, TO_NUMBER (NULL)
, NULL
, TO_DATE (NULL)
, NULL
, TO_NUMBER (NULL) I
, NULL
, NULL
, NULL
, TO_DATE (NULL)
, TO_DATE (NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER (NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, AD.ASSET_ID
, AD.ASSET_NUMBER
, AD.ASSET_KEY_CCID
, AD.CURRENT_UNITS
, AD.ASSET_TYPE
, AD.TAG_NUMBER
, AD.DESCRIPTION ASSET_DESCRIPTION
, AD.ASSET_CATEGORY_ID
, AD.PARENT_ASSET_ID
, AD.MANUFACTURER_NAME
, AD.SERIAL_NUMBER
, AD.MODEL_NUMBER
, AD.PROPERTY_TYPE_CODE
, AD.PROPERTY_1245_1250_CODE
, AD.IN_USE_FLAG
, AD.OWNED_LEASED
, AD.NEW_USED
, AD.UNIT_ADJUSTMENT_FLAG
, AD.ADD_COST_JE_FLAG
, AD.LEASE_ID
, CA.CATEGORY_TYPE
, CA.CAPITALIZE_FLAG
, RTRIM( DECODE (AK.SEGMENT1
, NULL
, ''
, AK.SEGMENT1 || '.') || DECODE (AK.SEGMENT2
, NULL
, ''
, AK.SEGMENT2 || '.') || DECODE (AK.SEGMENT3
, NULL
, ''
, AK.SEGMENT3 || '.') || DECODE (AK.SEGMENT4
, NULL
, ''
, AK.SEGMENT4 || '.') || DECODE (AK.SEGMENT5
, NULL
, ''
, AK.SEGMENT5 || '.') || DECODE (AK.SEGMENT6
, NULL
, ''
, AK.SEGMENT6 || '.') || DECODE (AK.SEGMENT7
, NULL
, ''
, AK.SEGMENT7 || '.') || DECODE (AK.SEGMENT8
, NULL
, ''
, AK.SEGMENT8 || '.') || DECODE (AK.SEGMENT9
, NULL
, ''
, AK.SEGMENT9 || '.') || DECODE (AK.SEGMENT10
, NULL
, ''
, AK.SEGMENT10 || '.')
, '.') ASSET_KEY
, CK.CONCATENATED_SEGMENTS CATEGORY
, LK.CONCATENATED_SEGMENTS LOC_CONC_SEGMENTS
, GLCC.CONCATENATED_SEGMENTS ACCT_CONC_SEGMENTS
FROM FA_CATEGORIES CA
, FA_ADDITIONS AD
, FA_CATEGORIES_B_KFV CK
, FA_ASSET_KEYWORDS AK
, FA_LOCATIONS_KFV LK
, GL_CODE_COMBINATIONS_KFV GLCC
, FA_DISTRIBUTION_HISTORY DH
WHERE DH.ASSET_ID = AD.ASSET_ID
AND DH.DATE_INEFFECTIVE IS NULL
AND AD.ASSET_CATEGORY_ID = CA.CATEGORY_ID
AND DH.LOCATION_ID = LK.LOCATION_ID
AND DH.CODE_COMBINATION_ID = GLCC.CODE_COMBINATION_ID
AND AD.ASSET_KEY_CCID = AK.CODE_COMBINATION_ID(+)
AND AD.ASSET_CATEGORY_ID = CK.CATEGORY_ID
AND DH.ASSIGNED_TO IS NULL