View: HXC_RESOURCE_INFORMATION_V

Product:	HXC - Time and Labor Engine
Description:
Implementation/DBA Data:	APPS.HXC_RESOURCE_INFORMATION_V

View Text

SELECT PAP.PERSON_ID RESOURCE_ID
, PAP.FULL_NAME RESOURCE_DISPLAY_NAME
, PAP.EMPLOYEE_NUMBER RESOURCE_NUMBER
, PAP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, DECODE(PAP.CURRENT_EMPLOYEE_FLAG
, 'Y'
, PPS.DATE_START
, NULL) HIRE_DATE
, PJ.NAME JOB_TITLE
, HOU.NAME ORGANIZATION_NAME
, PAP2.FULL_NAME SUPERVISOR_NAME
, PAP2.EMPLOYEE_NUMBER SUPERVISOR_NUMBER
, PAP.PER_INFORMATION_CATEGORY PER_INFORMATION_CATEGORY
, PAP.PER_INFORMATION1 PER_INFORMATION1
, PAP.PER_INFORMATION2 PER_INFORMATION2
, PAP.PER_INFORMATION3 PER_INFORMATION3
, PAP.PER_INFORMATION4 PER_INFORMATION4
, PAP.PER_INFORMATION5 PER_INFORMATION5
, PAP.PER_INFORMATION6 PER_INFORMATION6
, PAP.PER_INFORMATION7 PER_INFORMATION7
, PAP.PER_INFORMATION8 PER_INFORMATION8
, PAP.PER_INFORMATION9 PER_INFORMATION9
, PAP.PER_INFORMATION10 PER_INFORMATION10
, PAP.PER_INFORMATION11 PER_INFORMATION11
, PAP.PER_INFORMATION12 PER_INFORMATION12
, PAP.PER_INFORMATION13 PER_INFORMATION13
, PAP.PER_INFORMATION14 PER_INFORMATION14
, PAP.PER_INFORMATION15 PER_INFORMATION15
, PAP.PER_INFORMATION16 PER_INFORMATION16
, PAP.PER_INFORMATION17 PER_INFORMATION17
, PAP.PER_INFORMATION18 PER_INFORMATION18
, PAP.PER_INFORMATION19 PER_INFORMATION19
, PAP.PER_INFORMATION20 PER_INFORMATION20
, PAP.PER_INFORMATION21 PER_INFORMATION21
, PAP.PER_INFORMATION22 PER_INFORMATION22
, PAP.PER_INFORMATION23 PER_INFORMATION23
, PAP.PER_INFORMATION24 PER_INFORMATION24
, PAP.PER_INFORMATION25 PER_INFORMATION25
, PAP.PER_INFORMATION26 PER_INFORMATION26
, PAP.PER_INFORMATION27 PER_INFORMATION27
, PAP.PER_INFORMATION28 PER_INFORMATION28
, PAP.PER_INFORMATION29 PER_INFORMATION29
, PAP.PER_INFORMATION30 PER_INFORMATION30
, PAP.OFFICE_NUMBER OFFICE_NUMBER
, PAP.INTERNAL_LOCATION INTERNAL_LOCATION
, PAP.MAILSTOP MAILSTOP
, HL1.MEANING AVAILABILITY_SCHEDULE
, PAP.FTE_CAPACITY FULLTIME_AVAILABILTY
, PAP.ATTRIBUTE_CATEGORY ATTRIBUTE_CATEGORY
, PAP.ATTRIBUTE1 ATTRIBUTE1
, PAP.ATTRIBUTE2 ATTRIBUTE2
, PAP.ATTRIBUTE3 ATTRIBUTE3
, PAP.ATTRIBUTE4 ATTRIBUTE4
, PAP.ATTRIBUTE5 ATTRIBUTE5
, PAP.ATTRIBUTE6 ATTRIBUTE6
, PAP.ATTRIBUTE7 ATTRIBUTE7
, PAP.ATTRIBUTE8 ATTRIBUTE8
, PAP.ATTRIBUTE9 ATTRIBUTE9
, PAP.ATTRIBUTE10 ATTRIBUTE10
, PAP.ATTRIBUTE11 ATTRIBUTE11
, PAP.ATTRIBUTE12 ATTRIBUTE12
, PAP.ATTRIBUTE13 ATTRIBUTE13
, PAP.ATTRIBUTE14 ATTRIBUTE14
, PAP.ATTRIBUTE15 ATTRIBUTE15
, PAP.ATTRIBUTE16 ATTRIBUTE16
, PAP.ATTRIBUTE17 ATTRIBUTE17
, PAP.ATTRIBUTE18 ATTRIBUTE18
, PAP.ATTRIBUTE19 ATTRIBUTE19
, PAP.ATTRIBUTE20 ATTRIBUTE20
, PAP.ATTRIBUTE21 ATTRIBUTE21
, PAP.ATTRIBUTE22 ATTRIBUTE22
, PAP.ATTRIBUTE23 ATTRIBUTE23
, PAP.ATTRIBUTE24 ATTRIBUTE24
, PAP.ATTRIBUTE25 ATTRIBUTE25
, PAP.ATTRIBUTE26 ATTRIBUTE26
, PAP.ATTRIBUTE27 ATTRIBUTE27
, PAP.ATTRIBUTE28 ATTRIBUTE28
, PAP.ATTRIBUTE29 ATTRIBUTE29
, PAP.ATTRIBUTE30 ATTRIBUTE30
, PGR.NAME GRADE_NAME
, HRL.LOCATION_CODE ASSIGNMENT_LOCATION
, PPG.GROUP_NAME ASSIGNMENT_PEOPLE_GROUP
, PP.NAME ASSIGNMENT_POSITION
, PPAY.PAYROLL_NAME ASSIGNMENT_PAYROLL
, PAST.USER_STATUS ASSIGNMENT_STATUS
, PAA.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER
, PAA.EMPLOYMENT_CATEGORY ASSIGNMENT_CATEGORY
, PAA.NORMAL_HOURS NORMAL_HOURS
, HL2.MEANING NORMAL_HOURS_FREQUENCY
, PAA.TIME_NORMAL_START NORMAL_START_TIME
, PAA.TIME_NORMAL_FINISH NORMAL_FINISH_TIME
, HSCK.SEGMENT1 SOFT_CODED_KEYFLEX1
, HSCK.SEGMENT2 SOFT_CODED_KEYFLEX2
, HSCK.SEGMENT3 SOFT_CODED_KEYFLEX3
, HSCK.SEGMENT4 SOFT_CODED_KEYFLEX4
, HSCK.SEGMENT5 SOFT_CODED_KEYFLEX5
, HSCK.SEGMENT6 SOFT_CODED_KEYFLEX6
, HSCK.SEGMENT7 SOFT_CODED_KEYFLEX7
, HSCK.SEGMENT8 SOFT_CODED_KEYFLEX8
, HSCK.SEGMENT9 SOFT_CODED_KEYFLEX9
, HSCK.SEGMENT10 SOFT_CODED_KEYFLEX10
, HSCK.SEGMENT11 SOFT_CODED_KEYFLEX11
, HSCK.SEGMENT12 SOFT_CODED_KEYFLEX12
, HSCK.SEGMENT13 SOFT_CODED_KEYFLEX13
, HSCK.SEGMENT14 SOFT_CODED_KEYFLEX14
, HSCK.SEGMENT15 SOFT_CODED_KEYFLEX15
, HSCK.SEGMENT16 SOFT_CODED_KEYFLEX16
, HSCK.SEGMENT17 SOFT_CODED_KEYFLEX17
, HSCK.SEGMENT18 SOFT_CODED_KEYFLEX18
, HSCK.SEGMENT19 SOFT_CODED_KEYFLEX19
, HSCK.SEGMENT20 SOFT_CODED_KEYFLEX20
, HSCK.SEGMENT21 SOFT_CODED_KEYFLEX21
, HSCK.SEGMENT22 SOFT_CODED_KEYFLEX22
, HSCK.SEGMENT23 SOFT_CODED_KEYFLEX23
, HSCK.SEGMENT24 SOFT_CODED_KEYFLEX24
, HSCK.SEGMENT25 SOFT_CODED_KEYFLEX25
, HSCK.SEGMENT26 SOFT_CODED_KEYFLEX26
, HSCK.SEGMENT27 SOFT_CODED_KEYFLEX27
, HSCK.SEGMENT28 SOFT_CODED_KEYFLEX28
, HSCK.SEGMENT29 SOFT_CODED_KEYFLEX29
, HSCK.SEGMENT30 SOFT_CODED_KEYFLEX30
, PAA.BARGAINING_UNIT_CODE BARGINING_UNIT_CODE
, PPB.NAME SALARY_BASIS
, PCA.NAME COLLECTIVE_AGREEMENT
, CGD.SEGMENT1 AGREEMENT_GRADE1
, CGD.SEGMENT2 AGREEMENT_GRADE2
, CGD.SEGMENT3 AGREEMENT_GRADE3
, CGD.SEGMENT4 AGREEMENT_GRADE4
, CGD.SEGMENT5 AGREEMENT_GRADE5
, CGD.SEGMENT6 AGREEMENT_GRADE6
, CGD.SEGMENT7 AGREEMENT_GRADE7
, CGD.SEGMENT8 AGREEMENT_GRADE8
, CGD.SEGMENT9 AGREEMENT_GRADE9
, CGD.SEGMENT10 AGREEMENT_GRADE10
, CGD.SEGMENT11 AGREEMENT_GRADE11
, CGD.SEGMENT12 AGREEMENT_GRADE12
, CGD.SEGMENT13 AGREEMENT_GRADE13
, CGD.SEGMENT14 AGREEMENT_GRADE14
, CGD.SEGMENT15 AGREEMENT_GRADE15
, CGD.SEGMENT16 AGREEMENT_GRADE16
, CGD.SEGMENT17 AGREEMENT_GRADE17
, CGD.SEGMENT18 AGREEMENT_GRADE18
, CGD.SEGMENT19 AGREEMENT_GRADE19
, CGD.SEGMENT20 AGREEMENT_GRADE20
, COST.SEGMENT1 COST_ALLOCATION1
, COST.SEGMENT2 COST_ALLOCATION2
, COST.SEGMENT3 COST_ALLOCATION3
, COST.SEGMENT4 COST_ALLOCATION4
, COST.SEGMENT5 COST_ALLOCATION5
, COST.SEGMENT6 COST_ALLOCATION6
, COST.SEGMENT7 COST_ALLOCATION7
, COST.SEGMENT8 COST_ALLOCATION8
, COST.SEGMENT9 COST_ALLOCATION9
, COST.SEGMENT10 COST_ALLOCATION10
, COST.SEGMENT11 COST_ALLOCATION11
, COST.SEGMENT12 COST_ALLOCATION12
, COST.SEGMENT13 COST_ALLOCATION13
, COST.SEGMENT14 COST_ALLOCATION14
, COST.SEGMENT15 COST_ALLOCATION15
, COST.SEGMENT16 COST_ALLOCATION16
, COST.SEGMENT17 COST_ALLOCATION17
, COST.SEGMENT18 COST_ALLOCATION18
, COST.SEGMENT19 COST_ALLOCATION19
, COST.SEGMENT20 COST_ALLOCATION20
, COST.SEGMENT21 COST_ALLOCATION21
, COST.SEGMENT22 COST_ALLOCATION22
, COST.SEGMENT23 COST_ALLOCATION23
, COST.SEGMENT24 COST_ALLOCATION24
, COST.SEGMENT25 COST_ALLOCATION25
, COST.SEGMENT26 COST_ALLOCATION26
, COST.SEGMENT27 COST_ALLOCATION27
, COST.SEGMENT28 COST_ALLOCATION28
, COST.SEGMENT29 COST_ALLOCATION29
, COST.SEGMENT30 COST_ALLOCATION30
, PJ.ATTRIBUTE1 JOB_DFF_ATTRIBUTE1
, PJ.ATTRIBUTE2 JOB_DFF_ATTRIBUTE2
, PJ.ATTRIBUTE3 JOB_DFF_ATTRIBUTE3
, PJ.ATTRIBUTE4 JOB_DFF_ATTRIBUTE4
, PJ.ATTRIBUTE5 JOB_DFF_ATTRIBUTE5
, PJ.ATTRIBUTE6 JOB_DFF_ATTRIBUTE6
, PJ.ATTRIBUTE7 JOB_DFF_ATTRIBUTE7
, PJ.ATTRIBUTE8 JOB_DFF_ATTRIBUTE8
, PJ.ATTRIBUTE9 JOB_DFF_ATTRIBUTE9
, PJ.ATTRIBUTE10 JOB_DFF_ATTRIBUTE10
, PJ.ATTRIBUTE11 JOB_DFF_ATTRIBUTE11
, PJ.ATTRIBUTE12 JOB_DFF_ATTRIBUTE12
, PJ.ATTRIBUTE13 JOB_DFF_ATTRIBUTE13
, PJ.ATTRIBUTE14 JOB_DFF_ATTRIBUTE14
, PJ.ATTRIBUTE15 JOB_DFF_ATTRIBUTE15
, PJ.ATTRIBUTE16 JOB_DFF_ATTRIBUTE16
, PJ.ATTRIBUTE17 JOB_DFF_ATTRIBUTE17
, PJ.ATTRIBUTE18 JOB_DFF_ATTRIBUTE18
, PJ.ATTRIBUTE19 JOB_DFF_ATTRIBUTE19
, PJ.ATTRIBUTE20 JOB_DFF_ATTRIBUTE20
, PP.ATTRIBUTE1 POSITION_DFF_ATTRIBUTE1
, PP.ATTRIBUTE2 POSITION_DFF_ATTRIBUTE2
, PP.ATTRIBUTE3 POSITION_DFF_ATTRIBUTE3
, PP.ATTRIBUTE4 POSITION_DFF_ATTRIBUTE4
, PP.ATTRIBUTE5 POSITION_DFF_ATTRIBUTE5
, PP.ATTRIBUTE6 POSITION_DFF_ATTRIBUTE6
, PP.ATTRIBUTE7 POSITION_DFF_ATTRIBUTE7
, PP.ATTRIBUTE8 POSITION_DFF_ATTRIBUTE8
, PP.ATTRIBUTE9 POSITION_DFF_ATTRIBUTE9
, PP.ATTRIBUTE10 POSITION_DFF_ATTRIBUTE10
, PP.ATTRIBUTE11 POSITION_DFF_ATTRIBUTE11
, PP.ATTRIBUTE12 POSITION_DFF_ATTRIBUTE12
, PP.ATTRIBUTE13 POSITION_DFF_ATTRIBUTE13
, PP.ATTRIBUTE14 POSITION_DFF_ATTRIBUTE14
, PP.ATTRIBUTE15 POSITION_DFF_ATTRIBUTE15
, PP.ATTRIBUTE16 POSITION_DFF_ATTRIBUTE16
, PP.ATTRIBUTE17 POSITION_DFF_ATTRIBUTE17
, PP.ATTRIBUTE18 POSITION_DFF_ATTRIBUTE18
, PP.ATTRIBUTE19 POSITION_DFF_ATTRIBUTE19
, PP.ATTRIBUTE20 POSITION_DFF_ATTRIBUTE20
, PAA.ASS_ATTRIBUTE1 ASSIGNMENT_ATTRIBUTE1
, PAA.ASS_ATTRIBUTE2 ASSIGNMENT_ATTRIBUTE2
, PAA.ASS_ATTRIBUTE3 ASSIGNMENT_ATTRIBUTE3
, PAA.ASS_ATTRIBUTE4 ASSIGNMENT_ATTRIBUTE4
, PAA.ASS_ATTRIBUTE5 ASSIGNMENT_ATTRIBUTE5
, PAA.ASS_ATTRIBUTE6 ASSIGNMENT_ATTRIBUTE6
, PAA.ASS_ATTRIBUTE7 ASSIGNMENT_ATTRIBUTE7
, PAA.ASS_ATTRIBUTE8 ASSIGNMENT_ATTRIBUTE8
, PAA.ASS_ATTRIBUTE9 ASSIGNMENT_ATTRIBUTE9
, PAA.ASS_ATTRIBUTE10 ASSIGNMENT_ATTRIBUTE10
, PAA.ASS_ATTRIBUTE11 ASSIGNMENT_ATTRIBUTE11
, PAA.ASS_ATTRIBUTE12 ASSIGNMENT_ATTRIBUTE12
, PAA.ASS_ATTRIBUTE13 ASSIGNMENT_ATTRIBUTE13
, PAA.ASS_ATTRIBUTE14 ASSIGNMENT_ATTRIBUTE14
, PAA.ASS_ATTRIBUTE15 ASSIGNMENT_ATTRIBUTE15
, PAA.ASS_ATTRIBUTE16 ASSIGNMENT_ATTRIBUTE16
, PAA.ASS_ATTRIBUTE17 ASSIGNMENT_ATTRIBUTE17
, PAA.ASS_ATTRIBUTE18 ASSIGNMENT_ATTRIBUTE18
, PAA.ASS_ATTRIBUTE19 ASSIGNMENT_ATTRIBUTE19
, PAA.ASS_ATTRIBUTE20 ASSIGNMENT_ATTRIBUTE20
, PAA.ASS_ATTRIBUTE21 ASSIGNMENT_ATTRIBUTE21
, PAA.ASS_ATTRIBUTE22 ASSIGNMENT_ATTRIBUTE22
, PAA.ASS_ATTRIBUTE23 ASSIGNMENT_ATTRIBUTE23
, PAA.ASS_ATTRIBUTE24 ASSIGNMENT_ATTRIBUTE24
, PAA.ASS_ATTRIBUTE25 ASSIGNMENT_ATTRIBUTE25
, PAA.ASS_ATTRIBUTE26 ASSIGNMENT_ATTRIBUTE26
, PAA.ASS_ATTRIBUTE27 ASSIGNMENT_ATTRIBUTE27
, PAA.ASS_ATTRIBUTE28 ASSIGNMENT_ATTRIBUTE28
, PAA.ASS_ATTRIBUTE29 ASSIGNMENT_ATTRIBUTE29
, PAA.ASS_ATTRIBUTE30 ASSIGNMENT_ATTRIBUTE30
, PTP.START_DATE PAYROLL_PERIOD_START_DATE
, PTP.END_DATE PAYROLL_PERIOD_END_DATE
FROM HR_LOOKUPS HL1
, HR_LOOKUPS HL2
, PER_GRADES PGR
, PAY_PEOPLE_GROUPS PPG
, PER_COLLECTIVE_AGREEMENTS PCA
, PER_CAGR_GRADES_DEF CGD
, PER_TIME_PERIODS PTP
, PER_POSITIONS PP
, PAY_PAYROLLS PPAY
, PER_PAY_BASES PPB
, PAY_COST_ALLOCATIONS_F PCST
, PAY_COST_ALLOCATION_KEYFLEX COST
, PER_ASSIGNMENT_STATUS_TYPES_V PAST
, HR_LOCATIONS HRL
, HR_SOFT_CODING_KEYFLEX HSCK
, PER_JOBS PJ
, HR_ORGANIZATION_UNITS HOU
, PER_PERIODS_OF_SERVICE PPS
, PER_PEOPLE_F PAP2
, PER_PEOPLE_F PAP
, PER_ASSIGNMENTS_F PAA
WHERE PAP.PERSON_ID = PAA.PERSON_ID
AND PAP.BUSINESS_GROUP_ID = PAA.BUSINESS_GROUP_ID
AND SYSDATE BETWEEN PAP.EFFECTIVE_START_DATE
AND PAP.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PAA.ORGANIZATION_ID = HOU.ORGANIZATION_ID
AND PAA.SUPERVISOR_ID = PAP2.PERSON_ID (+)
AND PAP2.BUSINESS_GROUP_ID (+) = PAA.BUSINESS_GROUP_ID
AND PPS.PERSON_ID = PAP.PERSON_ID
AND PAA.JOB_ID = PJ.JOB_ID (+)
AND HL1.LOOKUP_TYPE(+) = 'WORK_SCHEDULE'
AND HL1.LOOKUP_CODE(+) = PAP.WORK_SCHEDULE
AND HL2.LOOKUP_TYPE(+) = 'FREQUENCY'
AND HL2.LOOKUP_CODE(+) = PAA.FREQUENCY
AND PGR.GRADE_ID(+) = PAA.GRADE_ID
AND HRL.LOCATION_ID (+) = PAA.LOCATION_ID
AND PPG.PEOPLE_GROUP_ID (+) = PAA.PEOPLE_GROUP_ID
AND PP.POSITION_ID (+) = PAA.POSITION_ID
AND PPAY.PAYROLL_ID (+) = PAA.PAYROLL_ID
AND PAST.ASSIGNMENT_STATUS_TYPE_ID = PAA.ASSIGNMENT_STATUS_TYPE_ID
AND HSCK.SOFT_CODING_KEYFLEX_ID (+) = PAA.SOFT_CODING_KEYFLEX_ID
AND PPB.PAY_BASIS_ID (+) = PAA.PAY_BASIS_ID
AND PCA.COLLECTIVE_AGREEMENT_ID (+) = PAA.COLLECTIVE_AGREEMENT_ID
AND CGD.CAGR_GRADE_DEF_ID (+)= PAA.CAGR_GRADE_DEF_ID
AND PCST.ASSIGNMENT_ID (+) = PAA.ASSIGNMENT_ID
AND PCST.COST_ALLOCATION_KEYFLEX_ID = COST.COST_ALLOCATION_KEYFLEX_ID (+)
AND PCST.EFFECTIVE_START_DATE (+) BETWEEN PAA.EFFECTIVE_START_DATE
AND PAA.EFFECTIVE_END_DATE
AND PTP.PAYROLL_ID (+) = PAA.PAYROLL_ID
AND SYSDATE BETWEEN PTP.START_DATE
AND PTP.END_DATE

Columns