VIEW: APPS.HR_PAY_INTERFACE_ACCOUNTING_V

Source

View Text - Preformatted

SELECT
  pp.employee_number
, asg.primary_flag
, asg.assignment_number
, glc.segment1
, glc.segment2
, glc.segment3
, glc.segment4
, glc.segment5
, glc.segment6
, glc.segment7
, glc.segment8
, glc.segment9
, glc.segment10
, glc.segment11
, glc.segment12
, glc.segment13
, glc.segment14
, glc.segment15
, glc.segment16
, glc.segment17
, glc.segment18
, glc.segment19
, glc.segment20
, glc.segment21
, glc.segment22
, glc.segment23
, glc.segment24
, glc.segment25
, glc.segment26
, glc.segment27
, glc.segment28
, glc.segment29
, glc.segment30
, bgr.name
, org.name
, pp.person_id
, asg.assignment_id
, bgr.business_group_id
, org.organization_id
, glc.code_combination_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(glc.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,NVL(glc.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(glc.last_update_date,
           TO_DATE('1900/01/01','YYYY/MM/DD'))
          ,NVL(glc.start_date_active,
           TO_DATE('1900/01/01','YYYY/MM/DD')))
FROM
  per_all_people_f                pp
, hr_all_organization_units       bgr
, hr_all_organization_units       org
, per_all_assignments_f           asg
, gl_code_combinations            glc
WHERE
  pp.business_group_id = bgr.organization_id
  and asg.organization_id = org.organization_id
  and asg.default_code_comb_id   = glc.code_combination_id
  and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
      NVL(glc.start_date_active,to_date('01-01-0001','DD-MM-YYYY'))
      and
      NVL(glc.end_date_active,to_date('31-12-4712','DD-MM-YYYY'))
  and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
      pp.effective_start_date and pp.effective_end_date
-- 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))

View Text - HTML Formatted

SELECT PP.EMPLOYEE_NUMBER
, ASG.PRIMARY_FLAG
, ASG.ASSIGNMENT_NUMBER
, GLC.SEGMENT1
, GLC.SEGMENT2
, GLC.SEGMENT3
, GLC.SEGMENT4
, GLC.SEGMENT5
, GLC.SEGMENT6
, GLC.SEGMENT7
, GLC.SEGMENT8
, GLC.SEGMENT9
, GLC.SEGMENT10
, GLC.SEGMENT11
, GLC.SEGMENT12
, GLC.SEGMENT13
, GLC.SEGMENT14
, GLC.SEGMENT15
, GLC.SEGMENT16
, GLC.SEGMENT17
, GLC.SEGMENT18
, GLC.SEGMENT19
, GLC.SEGMENT20
, GLC.SEGMENT21
, GLC.SEGMENT22
, GLC.SEGMENT23
, GLC.SEGMENT24
, GLC.SEGMENT25
, GLC.SEGMENT26
, GLC.SEGMENT27
, GLC.SEGMENT28
, GLC.SEGMENT29
, GLC.SEGMENT30
, BGR.NAME
, ORG.NAME
, PP.PERSON_ID
, ASG.ASSIGNMENT_ID
, BGR.BUSINESS_GROUP_ID
, ORG.ORGANIZATION_ID
, GLC.CODE_COMBINATION_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(GLC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(GLC.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(GLC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(GLC.START_DATE_ACTIVE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) FROM PER_ALL_PEOPLE_F PP
, HR_ALL_ORGANIZATION_UNITS BGR
, HR_ALL_ORGANIZATION_UNITS ORG
, PER_ALL_ASSIGNMENTS_F ASG
, GL_CODE_COMBINATIONS GLC WHERE PP.BUSINESS_GROUP_ID = BGR.ORGANIZATION_ID
AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID
AND ASG.DEFAULT_CODE_COMB_ID = GLC.CODE_COMBINATION_ID
AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN NVL(GLC.START_DATE_ACTIVE
, TO_DATE('01-01-0001'
, 'DD-MM-YYYY')) AND NVL(GLC.END_DATE_ACTIVE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY'))
AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE -- 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))