VIEW: APPS.HR_PAY_INTERFACE_ASSIGNMENTS_V

Source

View Text - Preformatted

SELECT
  pp.employee_number
, pp.full_name
, asg.assignment_number
, asg.primary_flag
, pp2.employee_number
, job.name
, grade.name
, ppb.name
, hl.location_code
, asg.normal_hours
, ppr.payroll_name
, ptp.number_per_fiscal_year
, nvl(asta.pay_system_status, ast.pay_system_status)
, bgr.name
, org.name
, asg2.effective_start_date
, asg3.effective_end_date
, asg.ass_attribute1
, asg.ass_attribute2
, asg.ass_attribute3
, asg.ass_attribute4
, asg.ass_attribute5
, asg.ass_attribute6
, asg.ass_attribute7
, asg.ass_attribute8
, asg.ass_attribute9
, asg.ass_attribute10
, asg.ass_attribute11
, asg.ass_attribute12
, asg.ass_attribute13
, asg.ass_attribute14
, asg.ass_attribute15
, asg.ass_attribute16
, asg.ass_attribute17
, asg.ass_attribute18
, asg.ass_attribute19
, asg.ass_attribute20
, asg.ass_attribute21
, asg.ass_attribute22
, asg.ass_attribute23
, asg.ass_attribute24
, asg.ass_attribute25
, asg.ass_attribute26
, asg.ass_attribute27
, asg.ass_attribute28
, asg.ass_attribute29
, asg.ass_attribute30
, org.attribute1
, org.attribute2
, org.attribute3
, org.attribute4
, org.attribute5
, org.attribute6
, org.attribute7
, org.attribute8
, org.attribute9
, org.attribute10
, org.attribute11
, org.attribute12
, org.attribute13
, org.attribute14
, org.attribute15
, org.attribute16
, org.attribute17
, org.attribute18
, org.attribute19
, org.attribute20
, job.attribute1
, job.attribute2
, job.attribute3
, job.attribute4
, job.attribute5
, job.attribute6
, job.attribute7
, job.attribute8
, job.attribute9
, job.attribute10
, job.attribute11
, job.attribute12
, job.attribute13
, job.attribute14
, job.attribute15
, job.attribute16
, job.attribute17
, job.attribute18
, job.attribute19
, job.attribute20
, jdef.segment1
, jdef.segment2
, jdef.segment3
, jdef.segment4
, jdef.segment5
, jdef.segment6
, jdef.segment7
, jdef.segment8
, jdef.segment9
, jdef.segment10
, jdef.segment11
, jdef.segment12
, jdef.segment13
, jdef.segment14
, jdef.segment15
, jdef.segment16
, jdef.segment17
, jdef.segment18
, jdef.segment19
, jdef.segment20
, jdef.segment21
, jdef.segment22
, jdef.segment23
, jdef.segment24
, jdef.segment25
, jdef.segment26
, jdef.segment27
, jdef.segment28
, jdef.segment29
, jdef.segment30
, ppg.segment1
, ppg.segment2
, ppg.segment3
, ppg.segment4
, ppg.segment5
, ppg.segment6
, ppg.segment7
, ppg.segment8
, ppg.segment9
, ppg.segment10
, ppg.segment11
, ppg.segment12
, ppg.segment13
, ppg.segment14
, ppg.segment15
, ppg.segment16
, ppg.segment17
, ppg.segment18
, ppg.segment19
, ppg.segment20
, ppg.segment21
, ppg.segment22
, ppg.segment23
, ppg.segment24
, ppg.segment25
, ppg.segment26
, ppg.segment27
, ppg.segment28
, ppg.segment29
, ppg.segment30
, pp.person_id
, pp2.person_id
, asg.assignment_id
, ppb.pay_basis_id
, job.job_id
, grade.grade_id
, hl.location_id
, bgr.business_group_id
, org.organization_id
, ppr.payroll_id
,GREATEST(
  GREATEST(NVL(pp.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
, GREATEST(NVL(asg.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,asg.effective_start_date)
, GREATEST(NVL(ppg.last_update_date, TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,NVL(ppg.start_date_active, TO_DATE('1900/01/01','YYYY/MM/DD')))
 )
, GREATEST(NVL(pp.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,pp.effective_start_date)
, GREATEST(NVL(asg.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,asg.effective_start_date)
, GREATEST(NVL(hl.last_update_date, TO_DATE('1900/01/01','YYYY/MM/DD'))
           ,TO_DATE('1900/01/01','YYYY/MM/DD'))
, GREATEST(NVL(ppg.last_update_date, TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,NVL(ppg.start_date_active, TO_DATE('1900/01/01','YYYY/MM/DD')))
FROM
  pay_people_groups               ppg
, per_all_people_f                pp
, per_all_people_f                pp2
, hr_all_organization_units       bgr
, hr_all_organization_units       org
, per_all_assignments_f           asg
, per_all_assignments_f           asg2
, per_all_assignments_f           asg3
, per_time_period_types           ptp
, pay_all_payrolls_f              ppr
, per_ass_status_type_amends      asta
, per_assignment_status_types     ast
, hr_locations                    hl
, per_job_definitions             jdef
, per_jobs                        job
, per_grades                      grade
, per_pay_bases                   ppb
WHERE
  pp.business_group_id = bgr.organization_id
  and ptp.period_type         = ppr.period_type
  and ppr.payroll_id          = asg.payroll_id
  and ast.assignment_status_type_id = asg.assignment_status_type_id
  and ast.assignment_status_type_id = asta.assignment_status_type_id(+)
  and asg.people_group_id = ppg.people_group_id (+)
  and asg.job_id = job.job_id
  and job.job_definition_id = jdef.job_definition_id
  and asg.location_id = hl.location_id (+)
  and asg.grade_id = grade.grade_id (+)
  and asg.pay_basis_id = ppb.pay_basis_id (+)
  and asg.organization_id = org.organization_id
  and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
      pp.effective_start_date and pp.effective_end_date
  and ppr.effective_start_date=(select max(ppr2.effective_start_date)
                                from pay_all_payrolls_f ppr2
                                where (ppr2.effective_start_date <=
                                  HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
                                and ppr2.payroll_id  = ppr.payroll_id))
-- Join to the assignments table.
-- Can return more than one row.
  and pp.person_id = asg.person_id
  and asg.effective_start_date=(select max(asgi2.effective_start_date)
                                from per_all_assignments_f asgi2
                                where (asgi2.effective_start_date <=
                                  HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
                                and asgi2.assignment_id  = asg.assignment_id))
  and asg2.assignment_id = asg.assignment_id
  and asg2.effective_start_date=(select min(asgi2.effective_start_date)
                                from per_all_assignments_f asgi2
                                where asgi2.assignment_id = asg2.assignment_id
				and asgi2.assignment_type = 'E')
  and asg3.assignment_id = asg.assignment_id
  and asg3.effective_start_date=(select max(asgi3.effective_start_date)
                                from per_all_assignments_f asgi3
                                where asgi3.assignment_id = asg3.assignment_id
				and asgi3.assignment_type = 'E')
  and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
      NVL(ppg.start_date_active(+), to_date('01-01-0001','DD-MM-YYYY'))
      and
      NVL(ppg.end_date_active(+), to_date('31-12-4712','DD-MM-YYYY'))
-- Obtain the supervisor details if there is one
  and asg.supervisor_id = pp2.person_id (+)
  and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
      NVL(pp2.effective_start_date(+), to_date('01-01-0001','DD-MM-YYYY'))
      and NVL(pp2.effective_end_date(+), to_date('31-12-4712','DD-MM-YYYY'))

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, PP.FULL_NAME
, ASG.ASSIGNMENT_NUMBER
, ASG.PRIMARY_FLAG
, PP2.EMPLOYEE_NUMBER
, JOB.NAME
, GRADE.NAME
, PPB.NAME
, HL.LOCATION_CODE
, ASG.NORMAL_HOURS
, PPR.PAYROLL_NAME
, PTP.NUMBER_PER_FISCAL_YEAR
, NVL(ASTA.PAY_SYSTEM_STATUS
, AST.PAY_SYSTEM_STATUS)
, BGR.NAME
, ORG.NAME
, ASG2.EFFECTIVE_START_DATE
, ASG3.EFFECTIVE_END_DATE
, ASG.ASS_ATTRIBUTE1
, ASG.ASS_ATTRIBUTE2
, ASG.ASS_ATTRIBUTE3
, ASG.ASS_ATTRIBUTE4
, ASG.ASS_ATTRIBUTE5
, ASG.ASS_ATTRIBUTE6
, ASG.ASS_ATTRIBUTE7
, ASG.ASS_ATTRIBUTE8
, ASG.ASS_ATTRIBUTE9
, ASG.ASS_ATTRIBUTE10
, ASG.ASS_ATTRIBUTE11
, ASG.ASS_ATTRIBUTE12
, ASG.ASS_ATTRIBUTE13
, ASG.ASS_ATTRIBUTE14
, ASG.ASS_ATTRIBUTE15
, ASG.ASS_ATTRIBUTE16
, ASG.ASS_ATTRIBUTE17
, ASG.ASS_ATTRIBUTE18
, ASG.ASS_ATTRIBUTE19
, ASG.ASS_ATTRIBUTE20
, ASG.ASS_ATTRIBUTE21
, ASG.ASS_ATTRIBUTE22
, ASG.ASS_ATTRIBUTE23
, ASG.ASS_ATTRIBUTE24
, ASG.ASS_ATTRIBUTE25
, ASG.ASS_ATTRIBUTE26
, ASG.ASS_ATTRIBUTE27
, ASG.ASS_ATTRIBUTE28
, ASG.ASS_ATTRIBUTE29
, ASG.ASS_ATTRIBUTE30
, ORG.ATTRIBUTE1
, ORG.ATTRIBUTE2
, ORG.ATTRIBUTE3
, ORG.ATTRIBUTE4
, ORG.ATTRIBUTE5
, ORG.ATTRIBUTE6
, ORG.ATTRIBUTE7
, ORG.ATTRIBUTE8
, ORG.ATTRIBUTE9
, ORG.ATTRIBUTE10
, ORG.ATTRIBUTE11
, ORG.ATTRIBUTE12
, ORG.ATTRIBUTE13
, ORG.ATTRIBUTE14
, ORG.ATTRIBUTE15
, ORG.ATTRIBUTE16
, ORG.ATTRIBUTE17
, ORG.ATTRIBUTE18
, ORG.ATTRIBUTE19
, ORG.ATTRIBUTE20
, JOB.ATTRIBUTE1
, JOB.ATTRIBUTE2
, JOB.ATTRIBUTE3
, JOB.ATTRIBUTE4
, JOB.ATTRIBUTE5
, JOB.ATTRIBUTE6
, JOB.ATTRIBUTE7
, JOB.ATTRIBUTE8
, JOB.ATTRIBUTE9
, JOB.ATTRIBUTE10
, JOB.ATTRIBUTE11
, JOB.ATTRIBUTE12
, JOB.ATTRIBUTE13
, JOB.ATTRIBUTE14
, JOB.ATTRIBUTE15
, JOB.ATTRIBUTE16
, JOB.ATTRIBUTE17
, JOB.ATTRIBUTE18
, JOB.ATTRIBUTE19
, JOB.ATTRIBUTE20
, JDEF.SEGMENT1
, JDEF.SEGMENT2
, JDEF.SEGMENT3
, JDEF.SEGMENT4
, JDEF.SEGMENT5
, JDEF.SEGMENT6
, JDEF.SEGMENT7
, JDEF.SEGMENT8
, JDEF.SEGMENT9
, JDEF.SEGMENT10
, JDEF.SEGMENT11
, JDEF.SEGMENT12
, JDEF.SEGMENT13
, JDEF.SEGMENT14
, JDEF.SEGMENT15
, JDEF.SEGMENT16
, JDEF.SEGMENT17
, JDEF.SEGMENT18
, JDEF.SEGMENT19
, JDEF.SEGMENT20
, JDEF.SEGMENT21
, JDEF.SEGMENT22
, JDEF.SEGMENT23
, JDEF.SEGMENT24
, JDEF.SEGMENT25
, JDEF.SEGMENT26
, JDEF.SEGMENT27
, JDEF.SEGMENT28
, JDEF.SEGMENT29
, JDEF.SEGMENT30
, PPG.SEGMENT1
, PPG.SEGMENT2
, PPG.SEGMENT3
, PPG.SEGMENT4
, PPG.SEGMENT5
, PPG.SEGMENT6
, PPG.SEGMENT7
, PPG.SEGMENT8
, PPG.SEGMENT9
, PPG.SEGMENT10
, PPG.SEGMENT11
, PPG.SEGMENT12
, PPG.SEGMENT13
, PPG.SEGMENT14
, PPG.SEGMENT15
, PPG.SEGMENT16
, PPG.SEGMENT17
, PPG.SEGMENT18
, PPG.SEGMENT19
, PPG.SEGMENT20
, PPG.SEGMENT21
, PPG.SEGMENT22
, PPG.SEGMENT23
, PPG.SEGMENT24
, PPG.SEGMENT25
, PPG.SEGMENT26
, PPG.SEGMENT27
, PPG.SEGMENT28
, PPG.SEGMENT29
, PPG.SEGMENT30
, PP.PERSON_ID
, PP2.PERSON_ID
, ASG.ASSIGNMENT_ID
, PPB.PAY_BASIS_ID
, JOB.JOB_ID
, GRADE.GRADE_ID
, HL.LOCATION_ID
, BGR.BUSINESS_GROUP_ID
, ORG.ORGANIZATION_ID
, PPR.PAYROLL_ID
, GREATEST( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(PPG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPG.START_DATE_ACTIVE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) )
, GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(ASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, ASG.EFFECTIVE_START_DATE)
, GREATEST(NVL(HL.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, GREATEST(NVL(PPG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PPG.START_DATE_ACTIVE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) FROM PAY_PEOPLE_GROUPS PPG
, PER_ALL_PEOPLE_F PP
, PER_ALL_PEOPLE_F PP2
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_ALL_ASSIGNMENTS_F ASG
, PER_ALL_ASSIGNMENTS_F ASG2
, PER_ALL_ASSIGNMENTS_F ASG3
, PER_TIME_PERIOD_TYPES PTP
, PAY_ALL_PAYROLLS_F PPR
, PER_ASS_STATUS_TYPE_AMENDS ASTA
, PER_ASSIGNMENT_STATUS_TYPES AST
, HR_LOCATIONS HL
, PER_JOB_DEFINITIONS JDEF
, PER_JOBS JOB
, PER_GRADES GRADE
, PER_PAY_BASES PPB WHERE PP.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = ASG.PAYROLL_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASG.ASSIGNMENT_STATUS_TYPE_ID
AND AST.ASSIGNMENT_STATUS_TYPE_ID = ASTA.ASSIGNMENT_STATUS_TYPE_ID(+)
AND ASG.PEOPLE_GROUP_ID = PPG.PEOPLE_GROUP_ID (+)
AND ASG.JOB_ID = JOB.JOB_ID
AND JOB.JOB_DEFINITION_ID = JDEF.JOB_DEFINITION_ID
AND ASG.LOCATION_ID = HL.LOCATION_ID (+)
AND ASG.GRADE_ID = GRADE.GRADE_ID (+)
AND ASG.PAY_BASIS_ID = PPB.PAY_BASIS_ID (+)
AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE
AND PPR.EFFECTIVE_START_DATE=(SELECT MAX(PPR2.EFFECTIVE_START_DATE)
FROM PAY_ALL_PAYROLLS_F PPR2
WHERE (PPR2.EFFECTIVE_START_DATE <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
AND PPR2.PAYROLL_ID = PPR.PAYROLL_ID)) -- JOIN TO THE ASSIGNMENTS TABLE. -- CAN RETURN MORE THAN ONE ROW.
AND PP.PERSON_ID = ASG.PERSON_ID
AND ASG.EFFECTIVE_START_DATE=(SELECT MAX(ASGI2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASGI2
WHERE (ASGI2.EFFECTIVE_START_DATE <= HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE
AND ASGI2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID))
AND ASG2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_START_DATE=(SELECT MIN(ASGI2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASGI2
WHERE ASGI2.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID AND ASGI2.ASSIGNMENT_TYPE = 'E')
AND ASG3.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG3.EFFECTIVE_START_DATE=(SELECT MAX(ASGI3.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F ASGI3
WHERE ASGI3.ASSIGNMENT_ID = ASG3.ASSIGNMENT_ID AND ASGI3.ASSIGNMENT_TYPE = 'E')
AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(PPG.START_DATE_ACTIVE(+)
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY')) AND NVL(PPG.END_DATE_ACTIVE(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) -- OBTAIN THE SUPERVISOR DETAILS IF THERE IS ONE
AND ASG.SUPERVISOR_ID = PP2.PERSON_ID (+)
AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(PP2.EFFECTIVE_START_DATE(+)
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY'))
AND NVL(PP2.EFFECTIVE_END_DATE(+)
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))