VIEW: APPS.HR_PAY_INTERFACE_SPECIAL_IF_V

Source

View Text - Preformatted

SELECT DISTINCT
   bgr.name
 , pp.full_name
 , pa.date_from
 , pa.date_to
 , fs1.id_flex_structure_name
 , pa.business_group_id + 0
 , fs1.id_flex_num
 , pa.person_id
 , ac.segment1
 , ac.segment2
 , ac.segment3
 , ac.segment4
 , ac.segment5
 , ac.segment6
 , ac.segment7
 , ac.segment8
 , ac.segment9
 , ac.segment10
 , ac.segment11
 , ac.segment12
 , ac.segment13
 , ac.segment14
 , ac.segment15
 , ac.segment16
 , ac.segment17
 , ac.segment18
 , ac.segment19
 , ac.segment20
 , greatest(
   GREATEST(NVL(pp.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
 	  ,pp.effective_start_date)
 , GREATEST(NVL(pa.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,pa.date_from)
 , GREATEST(NVL(ac.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(ac.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(pa.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,pa.date_from)
 , GREATEST(NVL(ac.last_update_date,TO_DATE('1900/01/01','YYYY/MM/DD'))
	  ,NVL(ac.start_date_active,TO_DATE('1900/01/01','YYYY/MM/DD')))
FROM
  per_analysis_criteria      ac
 ,per_person_analyses        pa
 ,hr_all_organization_units  bgr
 ,fnd_id_flex_structures_vl  fs1
 ,per_people_f               pp
WHERE ac.analysis_criteria_id = pa.analysis_criteria_id
AND   pp.person_id            = pa.person_id
AND   ac.id_flex_num          =
     (
      SELECT DISTINCT fs1.id_flex_num
      FROM            per_special_info_types    si
      WHERE si.id_flex_num    = fs1.id_flex_num
      AND   ac.id_flex_num    = fs1.id_flex_num
      AND   si.enabled_flag   = 'Y'
      AND   fs1.application_id = 800
      AND   fs1.id_flex_code   = 'PEA'
     )
AND  pa.business_group_id+0  = bgr.organization_id
AND  pa.business_group_id+0   =
     NVL(hr_general.get_business_group_id,pa.business_group_id)
and HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE between
    pp.effective_start_date and pp.effective_end_date
WITH READ ONLY

View Text - HTML Formatted

SELECT DISTINCT BGR.NAME
, PP.FULL_NAME
, PA.DATE_FROM
, PA.DATE_TO
, FS1.ID_FLEX_STRUCTURE_NAME
, PA.BUSINESS_GROUP_ID + 0
, FS1.ID_FLEX_NUM
, PA.PERSON_ID
, AC.SEGMENT1
, AC.SEGMENT2
, AC.SEGMENT3
, AC.SEGMENT4
, AC.SEGMENT5
, AC.SEGMENT6
, AC.SEGMENT7
, AC.SEGMENT8
, AC.SEGMENT9
, AC.SEGMENT10
, AC.SEGMENT11
, AC.SEGMENT12
, AC.SEGMENT13
, AC.SEGMENT14
, AC.SEGMENT15
, AC.SEGMENT16
, AC.SEGMENT17
, AC.SEGMENT18
, AC.SEGMENT19
, AC.SEGMENT20
, GREATEST( GREATEST(NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PP.EFFECTIVE_START_DATE)
, GREATEST(NVL(PA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PA.DATE_FROM)
, GREATEST(NVL(AC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(AC.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(PA.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, PA.DATE_FROM)
, GREATEST(NVL(AC.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(AC.START_DATE_ACTIVE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) FROM PER_ANALYSIS_CRITERIA AC
, PER_PERSON_ANALYSES PA
, HR_ALL_ORGANIZATION_UNITS BGR
, FND_ID_FLEX_STRUCTURES_VL FS1
, PER_PEOPLE_F PP WHERE AC.ANALYSIS_CRITERIA_ID = PA.ANALYSIS_CRITERIA_ID AND PP.PERSON_ID = PA.PERSON_ID AND AC.ID_FLEX_NUM = ( SELECT DISTINCT FS1.ID_FLEX_NUM
FROM PER_SPECIAL_INFO_TYPES SI
WHERE SI.ID_FLEX_NUM = FS1.ID_FLEX_NUM
AND AC.ID_FLEX_NUM = FS1.ID_FLEX_NUM
AND SI.ENABLED_FLAG = 'Y'
AND FS1.APPLICATION_ID = 800
AND FS1.ID_FLEX_CODE = 'PEA' ) AND PA.BUSINESS_GROUP_ID+0 = BGR.ORGANIZATION_ID AND PA.BUSINESS_GROUP_ID+0 = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, PA.BUSINESS_GROUP_ID) AND HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE BETWEEN PP.EFFECTIVE_START_DATE
AND PP.EFFECTIVE_END_DATE WITH READ ONLY