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

Source

View Text - Preformatted


SELECT /*+ LEADING(pf) INDEX(pf.pap PAY_PAYROLLS_F_PK) */ last_name, First_name, middle_names, employee_number, assignment_number, national_identifier, pf.payroll_name, hou_org.name Organization_Name, paa.tax_unit_id, pay_costing_detail_rep_pkg.get_costing_tax_unit_name(paa.tax_unit_id) GRE_NAME, pettl.element_name Retro_Element, peev.screen_entry_value Retro_Pay_Value, ptp1.PERIOD_NAME Originating_Pay_Period , CASE when pee.CREATOR_TYPE = 'EE' then PAY_ADHOC_UTILS_PKG.get_element_name(pee.source_id,pparet.effective_date,nvl(ppa.date_earned,ppa.effective_date)) when pee.CREATOR_TYPE <> 'EE' then PAY_ADHOC_UTILS_PKG.decode_element_type(prr.element_type_id,pparet.effective_date) END Originating_Element_type, ptp.start_date, ptp.end_date, ppa.business_group_id, pet.element_type_id, pee.element_entry_id, piv.input_value_id, pf.payroll_id, CASE WHEN ppa.business_group_id = nvl(hr_bis.get_sec_profile_bg_id, ppa.business_group_id) THEN 'Y' ELSE 'N' END bg_security_flag_code, hl.location_code location, ptp.PERIOD_NAME Target_Pay_Period, pparet.effective_date Date_of_Retro_Run, paaret.assignment_action_id Retro_Assignment_action_id, paf.assignment_id, ppf.person_id, pectl.classification_name Retro_Element_Class, NULL ASSIGNMENT_SET_NAME from pay_payroll_actions pparet, pay_assignment_Actions paaret, pay_payrolls_f pf, pay_payroll_actions ppa, pay_assignment_actions paa , pay_element_entries_f pee, pay_element_types_f pet, pay_element_types_f_tl pettl, pay_element_entry_values_f peev, pay_input_values_f piv, pay_input_values_f_tl pivtl, per_people_f ppf, per_assignments_f paf, per_time_periods ptp, per_time_periods ptp1, pay_run_results prr, hr_organization_units hou_org , hr_locations hl, pay_element_classifications pec, pay_element_classifications_tl pectl WHERE pee.element_type_id = pet.element_type_id and pee.source_id = prr.run_result_id(+) and pet.element_type_id = pettl.element_type_id and pettl.language = userenv('LANG') and piv.input_value_id = pivtl.input_value_id and pivtl.language = userenv('LANG') and peev.input_value_id = piv.input_value_id and pet.element_type_id = piv.element_type_id and pee.element_entry_id = peev.element_entry_id and pee.CREATOR_TYPE in ('EE','NR','PR','R','RR') and paaret.assignment_id = pee.assignment_id and paaret.assignment_id = paf.assignment_id and paf.person_id = ppf.person_id and pparet.payroll_id = pf.payroll_id and hou_org.organization_id = paf.organization_id and hl.location_id = paf.location_id and ptp.payroll_id = pf.payroll_id and ppa.payroll_action_id = paa.payroll_action_id and ppa.time_period_id = ptp1.time_period_id and pparet.effective_date between ptp.start_date and ptp.end_date and paa.assignment_action_id = pee.Source_asg_action_id and pparet.effective_date between peev.effective_start_date and peev.effective_end_date and pparet.effective_date between pee.effective_start_date and pee.effective_end_date and pparet.effective_date between pet.effective_start_date and pet.effective_end_date and pparet.effective_date between piv.effective_start_date and piv.effective_end_date and pparet.effective_date between ppf.effective_start_date and ppf.effective_end_date and pparet.effective_date between paf.effective_start_date and paf.effective_end_date and pparet.effective_date between pf.effective_start_date and pf.effective_end_date and piv.name = 'Pay Value' and pparet.payroll_action_id = paaret.payroll_action_id and paaret.assignment_action_id = pee.creator_id and pec.classification_id = pectl.classification_id and pectl.language = userenv('LANG') and pec.classification_id = pet.classification_id and pparet.action_type in ('G','L','O') and ppa.action_type in ('R','Q')

View Text - HTML Formatted

SELECT /*+ LEADING(PF) INDEX(PF.PAP PAY_PAYROLLS_F_PK) */ LAST_NAME
, FIRST_NAME
, MIDDLE_NAMES
, EMPLOYEE_NUMBER
, ASSIGNMENT_NUMBER
, NATIONAL_IDENTIFIER
, PF.PAYROLL_NAME
, HOU_ORG.NAME ORGANIZATION_NAME
, PAA.TAX_UNIT_ID
, PAY_COSTING_DETAIL_REP_PKG.GET_COSTING_TAX_UNIT_NAME(PAA.TAX_UNIT_ID) GRE_NAME
, PETTL.ELEMENT_NAME RETRO_ELEMENT
, PEEV.SCREEN_ENTRY_VALUE RETRO_PAY_VALUE
, PTP1.PERIOD_NAME ORIGINATING_PAY_PERIOD
, CASE WHEN PEE.CREATOR_TYPE = 'EE' THEN PAY_ADHOC_UTILS_PKG.GET_ELEMENT_NAME(PEE.SOURCE_ID
, PPARET.EFFECTIVE_DATE
, NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE)) WHEN PEE.CREATOR_TYPE <> 'EE' THEN PAY_ADHOC_UTILS_PKG.DECODE_ELEMENT_TYPE(PRR.ELEMENT_TYPE_ID
, PPARET.EFFECTIVE_DATE) END ORIGINATING_ELEMENT_TYPE
, PTP.START_DATE
, PTP.END_DATE
, PPA.BUSINESS_GROUP_ID
, PET.ELEMENT_TYPE_ID
, PEE.ELEMENT_ENTRY_ID
, PIV.INPUT_VALUE_ID
, PF.PAYROLL_ID
, CASE WHEN PPA.BUSINESS_GROUP_ID = NVL(HR_BIS.GET_SEC_PROFILE_BG_ID
, PPA.BUSINESS_GROUP_ID) THEN 'Y' ELSE 'N' END BG_SECURITY_FLAG_CODE
, HL.LOCATION_CODE LOCATION
, PTP.PERIOD_NAME TARGET_PAY_PERIOD
, PPARET.EFFECTIVE_DATE DATE_OF_RETRO_RUN
, PAARET.ASSIGNMENT_ACTION_ID RETRO_ASSIGNMENT_ACTION_ID
, PAF.ASSIGNMENT_ID
, PPF.PERSON_ID
, PECTL.CLASSIFICATION_NAME RETRO_ELEMENT_CLASS
, NULL ASSIGNMENT_SET_NAME
FROM PAY_PAYROLL_ACTIONS PPARET
, PAY_ASSIGNMENT_ACTIONS PAARET
, PAY_PAYROLLS_F PF
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_ELEMENT_ENTRIES_F PEE
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_ELEMENT_ENTRY_VALUES_F PEEV
, PAY_INPUT_VALUES_F PIV
, PAY_INPUT_VALUES_F_TL PIVTL
, PER_PEOPLE_F PPF
, PER_ASSIGNMENTS_F PAF
, PER_TIME_PERIODS PTP
, PER_TIME_PERIODS PTP1
, PAY_RUN_RESULTS PRR
, HR_ORGANIZATION_UNITS HOU_ORG
, HR_LOCATIONS HL
, PAY_ELEMENT_CLASSIFICATIONS PEC
, PAY_ELEMENT_CLASSIFICATIONS_TL PECTL
WHERE PEE.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PEE.SOURCE_ID = PRR.RUN_RESULT_ID(+)
AND PET.ELEMENT_TYPE_ID = PETTL.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PIV.INPUT_VALUE_ID = PIVTL.INPUT_VALUE_ID
AND PIVTL.LANGUAGE = USERENV('LANG')
AND PEEV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PET.ELEMENT_TYPE_ID = PIV.ELEMENT_TYPE_ID
AND PEE.ELEMENT_ENTRY_ID = PEEV.ELEMENT_ENTRY_ID
AND PEE.CREATOR_TYPE IN ('EE'
, 'NR'
, 'PR'
, 'R'
, 'RR')
AND PAARET.ASSIGNMENT_ID = PEE.ASSIGNMENT_ID
AND PAARET.ASSIGNMENT_ID = PAF.ASSIGNMENT_ID
AND PAF.PERSON_ID = PPF.PERSON_ID
AND PPARET.PAYROLL_ID = PF.PAYROLL_ID
AND HOU_ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND HL.LOCATION_ID = PAF.LOCATION_ID
AND PTP.PAYROLL_ID = PF.PAYROLL_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.TIME_PERIOD_ID = PTP1.TIME_PERIOD_ID
AND PPARET.EFFECTIVE_DATE BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PAA.ASSIGNMENT_ACTION_ID = PEE.SOURCE_ASG_ACTION_ID
AND PPARET.EFFECTIVE_DATE BETWEEN PEEV.EFFECTIVE_START_DATE
AND PEEV.EFFECTIVE_END_DATE
AND PPARET.EFFECTIVE_DATE BETWEEN PEE.EFFECTIVE_START_DATE
AND PEE.EFFECTIVE_END_DATE
AND PPARET.EFFECTIVE_DATE BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND PPARET.EFFECTIVE_DATE BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PPARET.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPARET.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPARET.EFFECTIVE_DATE BETWEEN PF.EFFECTIVE_START_DATE
AND PF.EFFECTIVE_END_DATE
AND PIV.NAME = 'PAY VALUE'
AND PPARET.PAYROLL_ACTION_ID = PAARET.PAYROLL_ACTION_ID
AND PAARET.ASSIGNMENT_ACTION_ID = PEE.CREATOR_ID
AND PEC.CLASSIFICATION_ID = PECTL.CLASSIFICATION_ID
AND PECTL.LANGUAGE = USERENV('LANG')
AND PEC.CLASSIFICATION_ID = PET.CLASSIFICATION_ID
AND PPARET.ACTION_TYPE IN ('G'
, 'L'
, 'O')
AND PPA.ACTION_TYPE IN ('R'
, 'Q')