eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

VIEW: APPS.PAY_IE_P35_INSURABLE_WEEKS_V

Source

View Text - Preformatted


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

View Text - HTML Formatted

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