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.HR_ADP_ACCRUAL_PLAN_V

Source

View Text - Preformatted


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(pva.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 pva, 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 pva.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 (pva.input_value_id = piv.input_value_id AND piv.display_sequence = 1 AND pb.element_entry_id = pva.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 ((pb2.effective_end_date > trunc(hr_adp.get_adp_extract_date)) or (pb2.effective_end_date >= hr_adp.get_end_deduction_date))) 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, pva.screen_entry_value, pb.last_update_date, pp.last_update_date, pasg.last_update_date, pap.last_update_date

View Text - HTML Formatted

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(PVA.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 PVA
, 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 PVA.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 (PVA.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.DISPLAY_SEQUENCE = 1
AND PB.ELEMENT_ENTRY_ID = PVA.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 ((PB2.EFFECTIVE_END_DATE > TRUNC(HR_ADP.GET_ADP_EXTRACT_DATE)) OR (PB2.EFFECTIVE_END_DATE >= HR_ADP.GET_END_DEDUCTION_DATE)))
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
, PVA.SCREEN_ENTRY_VALUE
, PB.LAST_UPDATE_DATE
, PP.LAST_UPDATE_DATE
, PASG.LAST_UPDATE_DATE
, PAP.LAST_UPDATE_DATE