View: PAY_IE_P35_INSURABLE_WEEKS_V

Product:	PAY - Payroll
Description:	View used in the IE P35 Process
Implementation/DBA Data:	APPS.PAY_IE_P35_INSURABLE_WEEKS_V

View Text

SELECT ASG.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, ASG.PERSON_ID PERSON_ID
, PER.FULL_NAME FULL_NAME
, PER.ORIGINAL_DATE_OF_HIRE ORIGINAL_HIRE_DATE
, MIN(PPA.EFFECTIVE_DATE) MINIMUM_EFFECTIVE_DATE
, RRV1.RESULT_VALUE||RRV2.RESULT_VALUE COMBINED_CLASS
, SUM(RRV3.RESULT_VALUE) INSURABLE_WEEKS
FROM PER_ALL_ASSIGNMENTS_F ASG
, PER_ALL_PEOPLE_F PER
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
, PAY_ELEMENT_TYPES_F PET
, PAY_RUN_RESULTS PRR
, PAY_RUN_RESULT_VALUES RRV1
, PAY_INPUT_VALUES_F PIV1
, PAY_RUN_RESULT_VALUES RRV2
, PAY_INPUT_VALUES_F PIV2
, PAY_RUN_RESULT_VALUES RRV3
, PAY_INPUT_VALUES_F PIV3
WHERE PPA.ACTION_TYPE IN ('Q'
, 'R'
, 'B'
, 'V')
AND PPA.EFFECTIVE_DATE BETWEEN (SELECT FND_DATE.CANONICAL_TO_DATE(SUBSTR(FPOV.PROFILE_OPTION_VALUE
, 1
, 4)||'01/01 00:00:00')
FROM FND_PROFILE_OPTION_VALUES FPOV
, FND_PROFILE_OPTIONS FPO
WHERE FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.APPLICATION_ID = FPOV.APPLICATION_ID
AND FPO.PROFILE_OPTION_NAME = 'PAY_IE_P35_REPORTING_YEAR'
AND FPOV.LEVEL_ID = 10001
AND FPOV.LEVEL_VALUE = 0)
AND (SELECT FND_DATE.CANONICAL_TO_DATE(SUBSTR(FPOV.PROFILE_OPTION_VALUE
, 1
, 4)||'12/31 23:59:59')
FROM FND_PROFILE_OPTION_VALUES FPOV
, FND_PROFILE_OPTIONS FPO
WHERE FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND FPO.APPLICATION_ID = FPOV.APPLICATION_ID
AND FPO.PROFILE_OPTION_NAME = 'PAY_IE_P35_REPORTING_YEAR'
AND FPOV.LEVEL_ID = 10001
AND FPOV.LEVEL_VALUE = 0)
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND ASG.PRIMARY_FLAG = 'Y'
AND ASG.ASSIGNMENT_TYPE ='E'
AND ASG.ASSIGNMENT_STATUS_TYPE_ID = 1
AND ASG.EFFECTIVE_START_DATE = (SELECT MIN(ASG2.EFFECTIVE_START_DATE)
FROM PER_ASSIGNMENTS_F ASG2
WHERE ASG.ASSIGNMENT_ID = ASG2.ASSIGNMENT_ID
AND ASG2.EFFECTIVE_START_DATE <= TO_DATE('31-12-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY')
AND NVL(ASG2.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) >= TO_DATE('01-01-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY'))
AND PPA.EFFECTIVE_DATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.PERSON_ID = PER.PERSON_ID
AND PER.CURRENT_EMPLOYEE_FLAG = 'Y'
AND PER.EFFECTIVE_START_DATE = (SELECT MIN(PER2.EFFECTIVE_START_DATE)
FROM PER_ALL_PEOPLE_F PER2
WHERE PER.PERSON_ID = PER2.PERSON_ID
AND PER2.EFFECTIVE_START_DATE <= TO_DATE('31-12-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY')
AND NVL(PER2.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) >= TO_DATE('01-01-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY') )
AND PET.ELEMENT_NAME = 'IE PRSI DETAIL'
AND PET.LEGISLATION_CODE = 'IE'
AND PET.EFFECTIVE_START_DATE = (SELECT MAX(PET2.EFFECTIVE_START_DATE)
FROM PAY_ELEMENT_TYPES_F PET2
WHERE PET.ELEMENT_TYPE_ID = PET2.ELEMENT_TYPE_ID
AND PET2.EFFECTIVE_START_DATE <= TO_DATE('31-12-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY')
AND NVL(PET2.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) >= TO_DATE('01-01-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY' ) )
AND PET.ELEMENT_TYPE_ID = PIV1.ELEMENT_TYPE_ID
AND PIV1.NAME = 'CONTRIBUTION CLASS'
AND PIV1.LEGISLATION_CODE = 'IE'
AND PIV1.EFFECTIVE_START_DATE = (SELECT MAX(PIV1A.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV1A
WHERE PIV1.INPUT_VALUE_ID = PIV1A.INPUT_VALUE_ID
AND PIV1A.EFFECTIVE_START_DATE <= TO_DATE('31-12-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY')
AND NVL(PIV1A.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) >= TO_DATE('01-01-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY') )
AND PET.ELEMENT_TYPE_ID = PIV2.ELEMENT_TYPE_ID
AND PIV2.NAME = 'SUBCLASS'
AND PIV2.LEGISLATION_CODE = 'IE'
AND PIV2.EFFECTIVE_START_DATE = (SELECT MAX(PIV2A.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV2A
WHERE PIV2.INPUT_VALUE_ID = PIV2A.INPUT_VALUE_ID
AND PIV2A.EFFECTIVE_START_DATE <= TO_DATE('31-12-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY')
AND NVL(PIV2A.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) >= TO_DATE('01-01-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY') )
AND PET.ELEMENT_TYPE_ID = PIV3.ELEMENT_TYPE_ID
AND PIV3.NAME = 'INSURABLE WEEKS'
AND PIV3.LEGISLATION_CODE = 'IE'
AND PIV3.EFFECTIVE_START_DATE = (SELECT MAX(PIV3A.EFFECTIVE_START_DATE)
FROM PAY_INPUT_VALUES_F PIV3A
WHERE PIV3.INPUT_VALUE_ID = PIV3A.INPUT_VALUE_ID
AND PIV3A.EFFECTIVE_START_DATE <= TO_DATE('31-12-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY')
AND NVL(PIV3A.EFFECTIVE_END_DATE
, TO_DATE('31-12-4712'
, 'DD-MM-YYYY')) >= TO_DATE('01-01-'||TO_CHAR(PPA.EFFECTIVE_DATE
, 'YYYY')
, 'DD-MM-YYYY') )
AND PAA.ASSIGNMENT_ACTION_ID = PRR.ASSIGNMENT_ACTION_ID
AND PRR.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND RRV1.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND RRV1.INPUT_VALUE_ID = PIV1.INPUT_VALUE_ID
AND RRV2.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND RRV2.INPUT_VALUE_ID = PIV2.INPUT_VALUE_ID
AND RRV3.RUN_RESULT_ID = PRR.RUN_RESULT_ID
AND RRV3.INPUT_VALUE_ID = PIV3.INPUT_VALUE_ID GROUP BY ASG.BUSINESS_GROUP_ID
, ASG.PERSON_ID
, PER.FULL_NAME
, PER.ORIGINAL_DATE_OF_HIRE
, RRV1.RESULT_VALUE||RRV2.RESULT_VALUE

Columns