View: HR_PAY_INTERFACE_OAB_VALUE1_V

Product:	PER - Human Resources
Description:
Implementation/DBA Data:	APPS.HR_PAY_INTERFACE_OAB_VALUE1_V

View Text

SELECT DISTINCT PEV.SCREEN_ENTRY_VALUE
, BPRV.ACTY_TYP_CD
, ASG.ASSIGNMENT_ID
, ASG.ASSIGNMENT_NUMBER
, ASG.EFFECTIVE_START_DATE
, ASG.EFFECTIVE_END_DATE
, BPRV.RT_STRT_DT
, BPRV.RT_END_DT
, BPRV1.RT_STRT_DT
, BPRV2.RT_END_DT
, BPRV.RT_VAL
, BPRV.ACTY_REF_PERD_CD
, PTD_LIMIT.MX_VAL
, PEE.EFFECTIVE_START_DATE
, PEE.EFFECTIVE_END_DATE
, BPRV.PRTT_ENRT_RSLT_ID
, BPRV.PRTT_RT_VAL_ID
, PEV.ELEMENT_ENTRY_VALUE_ID
, ASG.PERSON_ID
, BPRV.PER_IN_LER_ID
, BPRV.RT_TYP_CD
, BPRV.LAST_UPDATE_DATE
FROM PER_ALL_ASSIGNMENTS_F ASG
, PAY_ELEMENT_ENTRIES_F PEE
, (SELECT BPL.MX_VAL MX_VAL
, BPRV.PRTT_RT_VAL_ID PRTT_RT_VAL_ID
FROM BEN_PTD_LMT_F BPL
, BEN_ACTY_RT_PTD_LMT_F BARPL
, BEN_ACTY_BASE_RT_F BABR
, BEN_PRTT_RT_VAL BPRV
WHERE BPRV.ACTY_BASE_RT_ID = BABR.ACTY_BASE_RT_ID
AND BABR.ACTY_BASE_RT_ID = BARPL.ACTY_BASE_RT_ID
AND BARPL.PTD_LMT_ID = BPL.PTD_LMT_ID
AND BPL.EFFECTIVE_START_DATE = (SELECT MAX (BPL2.EFFECTIVE_START_DATE)
FROM BEN_PTD_LMT_F BPL2
WHERE BPL.PTD_LMT_ID = BPL2.PTD_LMT_ID
AND BPL2.EFFECTIVE_START_DATE <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))
AND BABR.EFFECTIVE_START_DATE = (SELECT MAX (BABR2.EFFECTIVE_START_DATE)
FROM BEN_ACTY_BASE_RT_F BABR2
WHERE BABR.ACTY_BASE_RT_ID = BABR2.ACTY_BASE_RT_ID
AND BABR2.EFFECTIVE_START_DATE <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))
AND BARPL.EFFECTIVE_START_DATE = (SELECT MAX (BARPL2.EFFECTIVE_START_DATE)
FROM BEN_ACTY_RT_PTD_LMT_F BARPL2
WHERE BARPL.ACTY_RT_PTD_LMT_ID = BARPL2.ACTY_RT_PTD_LMT_ID
AND BARPL2.EFFECTIVE_START_DATE <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))) PTD_LIMIT
, PAY_ELEMENT_ENTRY_VALUES_F PEV
, BEN_PRTT_RT_VAL BPRV
, BEN_PRTT_RT_VAL BPRV1
, BEN_PRTT_RT_VAL BPRV2
WHERE BPRV.ELEMENT_ENTRY_VALUE_ID = PEV.ELEMENT_ENTRY_VALUE_ID
AND TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE) >= BPRV.RT_STRT_DT
AND BPRV.ACTY_TYP_CD = 'EEPYC'
AND BPRV1.ACTY_TYP_CD = 'EEPYC'
AND BPRV2.ACTY_TYP_CD = 'EEPYC'
AND BPRV.PRTT_ENRT_RSLT_ID = BPRV1.PRTT_ENRT_RSLT_ID
AND BPRV1.PRTT_ENRT_RSLT_ID = BPRV2.PRTT_ENRT_RSLT_ID
AND BPRV.RT_STRT_DT = (SELECT MAX (BPRVO.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVO
WHERE BPRVO.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVO.ACTY_TYP_CD = BPRV.ACTY_TYP_CD
AND BPRVO.RT_STRT_DT <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))
AND BPRV1.RT_STRT_DT = (SELECT MIN (BPRVS.RT_STRT_DT)
FROM BEN_PRTT_RT_VAL BPRVS
WHERE BPRVS.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVS.ACTY_TYP_CD = 'EEPYC'
AND BPRVS.RT_STRT_DT <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))
AND BPRV2.RT_END_DT = (SELECT MAX (BPRVE.RT_END_DT)
FROM BEN_PRTT_RT_VAL BPRVE
WHERE BPRVE.PRTT_ENRT_RSLT_ID = BPRV.PRTT_ENRT_RSLT_ID
AND BPRVE.ACTY_TYP_CD = 'EEPYC')
AND PEV.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND PEV.EFFECTIVE_START_DATE = PEE.EFFECTIVE_START_DATE
AND PEV.EFFECTIVE_END_DATE = PEE.EFFECTIVE_END_DATE
AND PEE.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE = 'E'
AND PTD_LIMIT.PRTT_RT_VAL_ID(+) = BPRV.PRTT_RT_VAL_ID
AND ASG.EFFECTIVE_START_DATE = (SELECT MAX (A2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F A2
WHERE A2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND A2.PRIMARY_FLAG = 'Y'
AND A2.EFFECTIVE_START_DATE <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))
AND PEE.EFFECTIVE_START_DATE = (SELECT MAX (EE2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F EE2
WHERE EE2.ELEMENT_ENTRY_ID = PEE.ELEMENT_ENTRY_ID
AND EE2.EFFECTIVE_START_DATE <= TRUNC (HR_PAY_INTERFACE_PKG.GET_EXTRACT_DATE))

Columns