View: HR_ADP_ACCRUAL_PLAN_V

Product:	PER - Human Resources
Description:	ADP payroll interface view
Implementation/DBA Data:	APPS.HR_ADP_ACCRUAL_PLAN_V

View Text

SELECT REF.COMPANY_CODE_EQUIVALENT
, /* COMPANY_CODE_EQUIVALENT */ HOU.NAME
, /* GRE_NAME */ HS.SEGMENT1
, /* GRE_ID */ PASG.ORGANIZATION_ID
, /* ORGANIZATION_ID */ PP.EMPLOYEE_NUMBER
, /* EMPLOYEE_NUMBER */ PP.PERSON_ID
, /* PERSON_ID */ PP.BUSINESS_GROUP_ID
, /* BUSINESS_GROUP_ID */ PASG.ASSIGNMENT_ID
, /* ASSIGNMENT_ID */ DECODE(PASG.PRIMARY_FLAG
, 'Y'
, 0
, PASG.ASSIGNMENT_SEQUENCE)
, /* ASSIGNMENT_SEQUENCE */ PASG.ASSIGNMENT_NUMBER
, /* ASSIGNMENT_NUMBER */ PASG.PRIMARY_FLAG
, /* PRIMARY_FLAG */ DECODE(PTP.NUMBER_PER_FISCAL_YEAR
, 52
, 'W'
, 24
, 'S'
, 26
, 'B'
, 12
, 'M'
, PTP.NUMBER_PER_FISCAL_YEAR)
, /* PAY_FREQUENCY */ PAP.ACCRUAL_PLAN_ID
, /* PLAN_ID */ PAP.ACCRUAL_PLAN_NAME
, /* PLAN_NAME */ PAP.ACCRUAL_CATEGORY
, /* ACCRUAL_CATEGORY */ MIN(PB.EFFECTIVE_START_DATE)
, /* START_DATE */ MAX(PB.EFFECTIVE_END_DATE)
, /* END_DATE */ TO_DATE(PV.SCREEN_ENTRY_VALUE
, 'YYYY/MM/DD HH24:MI:SS')
, /* CONTINUOUS_SERVICE_DATE */ GREATEST(NVL(PAP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PB.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PP.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))
, NVL(PASG.LAST_UPDATE_DATE
, TO_DATE('1900/01/01'
, 'YYYY/MM/DD'))) /*GREATEST_LAST_UPDATE_DATE*/
FROM PAY_ELEMENT_ENTRIES_F PB
, PAY_INPUT_VALUES_F PIV
, PER_ALL_ASSIGNMENTS_F PASG
, HR_SOFT_CODING_KEYFLEX HS
, HR_ALL_ORGANIZATION_UNITS HOU
, PAY_PAYROLLS_F PPR
, PER_TIME_PERIOD_TYPES PTP
, PAY_ELEMENT_ENTRY_VALUES_F PV
, PER_ALL_PEOPLE_F PP
, HR_ADP_EMP_REF_V REF
, PAY_ACCRUAL_PLANS PAP
, PAY_ELEMENT_LINKS_F PEL
, PAY_ELEMENT_TYPES_F PET
WHERE /* JOIN PERSON
AND ASSIGNMENT */ PASG.PERSON_ID = PP.PERSON_ID
AND /* JOIN ASSIGNMENT TO ELEMENT ENTRY */ PASG.ASSIGNMENT_ID = PB.ASSIGNMENT_ID
AND /* JOIN THE ASSIGNMENT TO REF VIEW BY ROWID */ PASG.ROWID = REF.ASG_ROWID
AND /* JOIN THE PERSON TO REF VIEW BY ROWID */ PP.ROWID = REF.PER_ROWID
AND /* JOIN THE SOFT CODING KEYFLEX TO ASSIGNMENT */ HS.SOFT_CODING_KEYFLEX_ID = PASG.SOFT_CODING_KEYFLEX_ID
AND HS.SEGMENT1 = HOU.ORGANIZATION_ID /* JOIN THE ELEMENT LINK TO ACCRUAL PLAN */
AND PB.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID
AND PET.ELEMENT_TYPE_ID = PEL.ELEMENT_TYPE_ID
AND PET.ELEMENT_TYPE_ID = PAP.ACCRUAL_PLAN_ELEMENT_TYPE_ID
AND PV.ELEMENT_ENTRY_ID = PB.ELEMENT_ENTRY_ID /* JOIN THE INPUT/ENTRY VALUES NOTE: THE PAY_ELEMENT_ENTRY_VALUES_F TABLES - NO NEED FOR SUBQUERIES BECAUSE THE PAY_ELEMENT_ENTRIES_F
AND PAY_ELEMENT_ENTRY_VALUES_F ARE LOGICALLY MODELLED ON DT ENTITY. */
AND (PV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PV.ELEMENT_ENTRY_ID
AND PIV.NAME = 'CONTINUOUS SERVICE DATE') /* JOIN PAYROLL/TIME PERIOD TYPES */
AND PTP.PERIOD_TYPE = PPR.PERIOD_TYPE
AND PPR.PAYROLL_ID = PASG.PAYROLL_ID /* RESTRICT THE PAYROLL BY SYSDATE */
AND TRUNC(SYSDATE) BETWEEN PPR.EFFECTIVE_START_DATE
AND PPR.EFFECTIVE_END_DATE /* =========== DATE RESTRICTION ON DT TABLES SECTION =========== */
AND PB.EFFECTIVE_START_DATE = (SELECT MAX (PB2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_ENTRIES_F PB2
WHERE PB2.EFFECTIVE_START_DATE<=TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PB2.ELEMENT_ENTRY_ID = PB.ELEMENT_ENTRY_ID)
AND PIV.EFFECTIVE_START_DATE = (SELECT MAX (PIV2.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV2
WHERE PIV2.EFFECTIVE_START_DATE<=TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PIV2.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID)
AND PET.EFFECTIVE_START_DATE = (SELECT MAX (PET2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PET2
WHERE PET2.EFFECTIVE_START_DATE<=TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PET2.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID)
AND PEL.EFFECTIVE_START_DATE = (SELECT MAX (PEL2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_LINKS_F PEL2
WHERE PEL2.EFFECTIVE_START_DATE<=TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PEL2.ELEMENT_LINK_ID = PEL.ELEMENT_LINK_ID)
AND PASG.EFFECTIVE_START_DATE = (SELECT MAX (PASG2.EFFECTIVE_START_DATE)
FROM PER_ALL_ASSIGNMENTS_F PASG2
WHERE PASG2.EFFECTIVE_START_DATE<=TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)
AND PASG2.ASSIGNMENT_ID = PASG.ASSIGNMENT_ID) GROUP BY REF.COMPANY_CODE_EQUIVALENT
, HOU.NAME
, HS.SEGMENT1
, PASG.ORGANIZATION_ID
, PP.EMPLOYEE_NUMBER
, PP.PERSON_ID
, PP.BUSINESS_GROUP_ID
, PASG.ASSIGNMENT_ID
, PASG.PRIMARY_FLAG
, PASG.ASSIGNMENT_SEQUENCE
, PASG.ASSIGNMENT_NUMBER
, PASG.PRIMARY_FLAG
, PTP.NUMBER_PER_FISCAL_YEAR
, PAP.ACCRUAL_PLAN_ID
, PAP.ACCRUAL_PLAN_NAME
, PAP.ACCRUAL_CATEGORY
, PB.EFFECTIVE_START_DATE
, PB.EFFECTIVE_END_DATE
, PV.SCREEN_ENTRY_VALUE
, PB.LAST_UPDATE_DATE
, PP.LAST_UPDATE_DATE
, PASG.LAST_UPDATE_DATE
, PAP.LAST_UPDATE_DATE

Columns