VIEW: APPS.PAYBV_PAYROLL_ACT_RR_EMP_DET

Source

View Text - Preformatted

SELECT hr_bis.bis_decode_lookup('ACTION_TYPE',ppa.ACTION_TYPE) ACTION_TYPE, ppa.effective_date, ppa.business_group_id, ppa.date_earned, ppa.payroll_action_id, paa.assignment_action_id, paa.tax_unit_id, paa.assignment_id, pf.payroll_name, pf.payroll_id, PPF.FULL_NAME, PPF.FIRST_NAME, PPF.LAST_NAME, PPF.MIDDLE_NAMES, PPF.EMPLOYEE_NUMBER, PPF.NATIONAL_IDENTIFIER, PAF.ASSIGNMENT_NUMBER, ppf.date_of_birth, ppf.sex, ppf.original_date_of_hire, ppf.projected_start_date Latest_Hire_date, ppf.person_id, hr_bis.bis_decode_lookup('EMP_CAT',paf.employment_category) Employment_Category, pettl.element_name, nvl(pettl.reporting_name,pettl.element_name) Reporting_name, pet.element_type_id, pivtl.name Input_value_name, hr_bis.bis_decode_lookup('UNITS',UOM) Unit_of_measure, piv.input_value_id, prrv.result_value, pcs.consolidation_set_name, pcs.consolidation_set_id, PAY_COSTING_DETAIL_REP_PKG.get_costing_tax_unit_name( paa.tax_unit_id) GRE_NAME, HOU_ORG.name Organization, HL.LOCATION_ID LOCATION_ID, HL.LOCATION_CODE LOCATION_CODE, 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 from pay_payroll_actions ppa, pay_assignment_actions paa, per_assignments_f paf, per_people_f ppf, pay_payrolls_f pf, pay_element_types_f pet, pay_element_types_f_tl pettl, pay_input_values_f piv, pay_run_result_values prrv, pay_run_results prr, pay_input_values_f_tl pivtl, pay_consolidation_sets pcs, hr_organization_units hou_org, hr_locations hl WHERE pf.payroll_id = paf.payroll_id and ppa.effective_date between pf.effective_start_date and pf.effective_end_date and paa.payroll_action_id = ppa.payroll_action_id and paf.assignment_id = paa.assignment_id and ppa.effective_date between paf.effective_start_date and paf.effective_end_date and ppf.person_id = paf.person_id and ppa.effective_date between ppf.effective_start_date and ppf.effective_end_date and pivtl.language = userenv('LANG') and pivtl.input_value_id = piv.input_value_id and prrv.input_value_id = piv.input_value_id and piv.element_type_id = pet.element_type_id and prr.run_result_id = prrv.run_result_id and prr.assignment_action_id = paa.assignment_action_id and pet.element_type_id = prr.element_type_id and pettl.language = userenv('LANG') and pettl.element_type_id = pet.element_type_id and nvl(ppa.date_earned,ppa.effective_date) between pet.effective_start_date and pet.effective_end_date and nvl(ppa.date_earned,ppa.effective_date) between piv.effective_start_date and piv.effective_end_date and pcs.consolidation_set_id = pf.consolidation_set_id and hou_org.organization_id = paf.organization_id and hl.location_id = paf.location_id

View Text - HTML Formatted

SELECT HR_BIS.BIS_DECODE_LOOKUP('ACTION_TYPE'
, PPA.ACTION_TYPE) ACTION_TYPE
, PPA.EFFECTIVE_DATE
, PPA.BUSINESS_GROUP_ID
, PPA.DATE_EARNED
, PPA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAA.TAX_UNIT_ID
, PAA.ASSIGNMENT_ID
, PF.PAYROLL_NAME
, PF.PAYROLL_ID
, PPF.FULL_NAME
, PPF.FIRST_NAME
, PPF.LAST_NAME
, PPF.MIDDLE_NAMES
, PPF.EMPLOYEE_NUMBER
, PPF.NATIONAL_IDENTIFIER
, PAF.ASSIGNMENT_NUMBER
, PPF.DATE_OF_BIRTH
, PPF.SEX
, PPF.ORIGINAL_DATE_OF_HIRE
, PPF.PROJECTED_START_DATE LATEST_HIRE_DATE
, PPF.PERSON_ID
, HR_BIS.BIS_DECODE_LOOKUP('EMP_CAT'
, PAF.EMPLOYMENT_CATEGORY) EMPLOYMENT_CATEGORY
, PETTL.ELEMENT_NAME
, NVL(PETTL.REPORTING_NAME
, PETTL.ELEMENT_NAME) REPORTING_NAME
, PET.ELEMENT_TYPE_ID
, PIVTL.NAME INPUT_VALUE_NAME
, HR_BIS.BIS_DECODE_LOOKUP('UNITS'
, UOM) UNIT_OF_MEASURE
, PIV.INPUT_VALUE_ID
, PRRV.RESULT_VALUE
, PCS.CONSOLIDATION_SET_NAME
, PCS.CONSOLIDATION_SET_ID
, PAY_COSTING_DETAIL_REP_PKG.GET_COSTING_TAX_UNIT_NAME( PAA.TAX_UNIT_ID) GRE_NAME
, HOU_ORG.NAME ORGANIZATION
, HL.LOCATION_ID LOCATION_ID
, HL.LOCATION_CODE LOCATION_CODE
, 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
FROM PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PER_ASSIGNMENTS_F PAF
, PER_PEOPLE_F PPF
, PAY_PAYROLLS_F PF
, PAY_ELEMENT_TYPES_F PET
, PAY_ELEMENT_TYPES_F_TL PETTL
, PAY_INPUT_VALUES_F PIV
, PAY_RUN_RESULT_VALUES PRRV
, PAY_RUN_RESULTS PRR
, PAY_INPUT_VALUES_F_TL PIVTL
, PAY_CONSOLIDATION_SETS PCS
, HR_ORGANIZATION_UNITS HOU_ORG
, HR_LOCATIONS HL
WHERE PF.PAYROLL_ID = PAF.PAYROLL_ID
AND PPA.EFFECTIVE_DATE BETWEEN PF.EFFECTIVE_START_DATE
AND PF.EFFECTIVE_END_DATE
AND PAA.PAYROLL_ACTION_ID = PPA.PAYROLL_ACTION_ID
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.EFFECTIVE_DATE BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID = PAF.PERSON_ID
AND PPA.EFFECTIVE_DATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PIVTL.LANGUAGE = USERENV('LANG')
AND PIVTL.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PRRV.INPUT_VALUE_ID = PIV.INPUT_VALUE_ID
AND PIV.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND PRR.RUN_RESULT_ID = PRRV.RUN_RESULT_ID
AND PRR.ASSIGNMENT_ACTION_ID = PAA.ASSIGNMENT_ACTION_ID
AND PET.ELEMENT_TYPE_ID = PRR.ELEMENT_TYPE_ID
AND PETTL.LANGUAGE = USERENV('LANG')
AND PETTL.ELEMENT_TYPE_ID = PET.ELEMENT_TYPE_ID
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PET.EFFECTIVE_START_DATE
AND PET.EFFECTIVE_END_DATE
AND NVL(PPA.DATE_EARNED
, PPA.EFFECTIVE_DATE) BETWEEN PIV.EFFECTIVE_START_DATE
AND PIV.EFFECTIVE_END_DATE
AND PCS.CONSOLIDATION_SET_ID = PF.CONSOLIDATION_SET_ID
AND HOU_ORG.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND HL.LOCATION_ID = PAF.LOCATION_ID