VIEW: APPS.PAY_CA_SOE_EMPINFO_V

Source

View Text - Preformatted

SELECT distinct ppf.full_name, ppf.national_identifier, ppf.employee_number, ppf.person_id, pay_ca_emp_tax_inf.get_address(ppf.person_id, decode(ppa.action_type, 'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.effective_date, 'C',ppa.effective_date, 'P',ppa.effective_date, 'U',ppa.effective_date), 1) emp_add_ln1, pay_ca_emp_tax_inf.get_address(ppf.person_id, decode(ppa.action_type, 'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.effective_date, 'C',ppa.effective_date, 'P',ppa.effective_date, 'U',ppa.effective_date), 2) emp_add_ln2, pay_ca_emp_tax_inf.get_address(ppf.person_id, decode(ppa.action_type, 'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.effective_date, 'C',ppa.effective_date, 'P',ppa.effective_date, 'U',ppa.effective_date), 3) emp_add_ln3, pay_ca_emp_tax_inf.get_address(ppf.person_id, decode(ppa.action_type, 'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.effective_date, 'C',ppa.effective_date, 'P',ppa.effective_date, 'U',ppa.effective_date), 4) emp_add_ln4, loc.address_line_1, loc.address_line_2, loc.address_line_3, loc.town_or_city, loc.region_1, loc.postal_code, paf.payroll_id, paf.assignment_number, paf.normal_hours, paf.frequency, ppa.action_type, ppa.effective_date, ppa.date_earned, paa.assignment_id, paa.payroll_action_id, paa.assignment_action_id, ppp.pre_payment_id, paa.action_status, paa.action_sequence, paa.chunk_number, paa.serial_number, paa.object_version_number, paa.tax_unit_id, hou.name, hou.organization_id, loc.location_id, loc.location_code, scl.segment4 work_schedule_id , ptp.start_date, ptp.end_date, pay_ca_emp_tax_inf.get_base_salary(paf.assignment_id, decode(ppa.action_type, 'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'P',ppa.effective_date, 'V',ppa.effective_date, 'U',ppa.effective_date), paf.pay_basis_id) base_salary, pay_ca_emp_tax_inf.get_salary_basis(paf.pay_basis_id) pay_basis, ppa.assignment_set_id, ppa.consolidation_set_id, paf.effective_start_date, paf.effective_end_date, ppa.legislative_parameters, ppf.effective_start_date person_effective_start_date, ppf.effective_end_date person_effective_end_date, ppf.correspondence_language person_language FROM hr_organization_units hou, per_time_periods ptp, per_people_f ppf, hr_soft_coding_keyflex scl, per_assignments_f paf, hr_locations loc, pay_payroll_actions ppa, pay_assignment_actions paa, pay_pre_payments ppp WHERE hou.organization_id = paf.organization_id AND nvl( decode(ppa.action_type,'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.date_earned, 'C',ppa.effective_date, 'P',ppa.date_earned, 'U',ppa.date_earned) , ppa.effective_date) between ptp.start_date AND ptp.end_date AND ptp.payroll_id = ppa.payroll_id AND nvl( decode(ppa.action_type,'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.effective_date, 'C',ppa.effective_date, 'P',ppa.date_earned, 'U',ppa.date_earned) , ppa.effective_date) between ppf.effective_start_date AND ppf.effective_end_date AND ppf.person_id = paf.person_id AND loc.location_id = paf.location_id AND nvl( decode(ppa.action_type,'R',ppa.date_earned, 'Q',ppa.date_earned, 'I',ppa.date_earned, 'B',ppa.date_earned, 'V',ppa.effective_date, 'C',ppa.effective_date, 'P',ppa.date_earned, 'U',ppa.date_earned) , ppa.effective_date) between paf.effective_start_date AND paf.effective_end_date AND scl.soft_coding_keyflex_id = paf.soft_coding_keyflex_id AND paf.assignment_id = paa.assignment_id AND ppa.payroll_action_id = paa.payroll_action_id AND paa.assignment_action_id = ppp.assignment_action_id(+)

View Text - HTML Formatted

SELECT DISTINCT PPF.FULL_NAME
, PPF.NATIONAL_IDENTIFIER
, PPF.EMPLOYEE_NUMBER
, PPF.PERSON_ID
, PAY_CA_EMP_TAX_INF.GET_ADDRESS(PPF.PERSON_ID
, DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.EFFECTIVE_DATE
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.EFFECTIVE_DATE
, 'U'
, PPA.EFFECTIVE_DATE)
, 1) EMP_ADD_LN1
, PAY_CA_EMP_TAX_INF.GET_ADDRESS(PPF.PERSON_ID
, DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.EFFECTIVE_DATE
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.EFFECTIVE_DATE
, 'U'
, PPA.EFFECTIVE_DATE)
, 2) EMP_ADD_LN2
, PAY_CA_EMP_TAX_INF.GET_ADDRESS(PPF.PERSON_ID
, DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.EFFECTIVE_DATE
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.EFFECTIVE_DATE
, 'U'
, PPA.EFFECTIVE_DATE)
, 3) EMP_ADD_LN3
, PAY_CA_EMP_TAX_INF.GET_ADDRESS(PPF.PERSON_ID
, DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.EFFECTIVE_DATE
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.EFFECTIVE_DATE
, 'U'
, PPA.EFFECTIVE_DATE)
, 4) EMP_ADD_LN4
, LOC.ADDRESS_LINE_1
, LOC.ADDRESS_LINE_2
, LOC.ADDRESS_LINE_3
, LOC.TOWN_OR_CITY
, LOC.REGION_1
, LOC.POSTAL_CODE
, PAF.PAYROLL_ID
, PAF.ASSIGNMENT_NUMBER
, PAF.NORMAL_HOURS
, PAF.FREQUENCY
, PPA.ACTION_TYPE
, PPA.EFFECTIVE_DATE
, PPA.DATE_EARNED
, PAA.ASSIGNMENT_ID
, PAA.PAYROLL_ACTION_ID
, PAA.ASSIGNMENT_ACTION_ID
, PPP.PRE_PAYMENT_ID
, PAA.ACTION_STATUS
, PAA.ACTION_SEQUENCE
, PAA.CHUNK_NUMBER
, PAA.SERIAL_NUMBER
, PAA.OBJECT_VERSION_NUMBER
, PAA.TAX_UNIT_ID
, HOU.NAME
, HOU.ORGANIZATION_ID
, LOC.LOCATION_ID
, LOC.LOCATION_CODE
, SCL.SEGMENT4 WORK_SCHEDULE_ID
, PTP.START_DATE
, PTP.END_DATE
, PAY_CA_EMP_TAX_INF.GET_BASE_SALARY(PAF.ASSIGNMENT_ID
, DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'P'
, PPA.EFFECTIVE_DATE
, 'V'
, PPA.EFFECTIVE_DATE
, 'U'
, PPA.EFFECTIVE_DATE)
, PAF.PAY_BASIS_ID) BASE_SALARY
, PAY_CA_EMP_TAX_INF.GET_SALARY_BASIS(PAF.PAY_BASIS_ID) PAY_BASIS
, PPA.ASSIGNMENT_SET_ID
, PPA.CONSOLIDATION_SET_ID
, PAF.EFFECTIVE_START_DATE
, PAF.EFFECTIVE_END_DATE
, PPA.LEGISLATIVE_PARAMETERS
, PPF.EFFECTIVE_START_DATE PERSON_EFFECTIVE_START_DATE
, PPF.EFFECTIVE_END_DATE PERSON_EFFECTIVE_END_DATE
, PPF.CORRESPONDENCE_LANGUAGE PERSON_LANGUAGE
FROM HR_ORGANIZATION_UNITS HOU
, PER_TIME_PERIODS PTP
, PER_PEOPLE_F PPF
, HR_SOFT_CODING_KEYFLEX SCL
, PER_ASSIGNMENTS_F PAF
, HR_LOCATIONS LOC
, PAY_PAYROLL_ACTIONS PPA
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PRE_PAYMENTS PPP
WHERE HOU.ORGANIZATION_ID = PAF.ORGANIZATION_ID
AND NVL( DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.DATE_EARNED
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.DATE_EARNED
, 'U'
, PPA.DATE_EARNED)
, PPA.EFFECTIVE_DATE) BETWEEN PTP.START_DATE
AND PTP.END_DATE
AND PTP.PAYROLL_ID = PPA.PAYROLL_ID
AND NVL( DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.EFFECTIVE_DATE
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.DATE_EARNED
, 'U'
, PPA.DATE_EARNED)
, PPA.EFFECTIVE_DATE) BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND PPF.PERSON_ID = PAF.PERSON_ID
AND LOC.LOCATION_ID = PAF.LOCATION_ID
AND NVL( DECODE(PPA.ACTION_TYPE
, 'R'
, PPA.DATE_EARNED
, 'Q'
, PPA.DATE_EARNED
, 'I'
, PPA.DATE_EARNED
, 'B'
, PPA.DATE_EARNED
, 'V'
, PPA.EFFECTIVE_DATE
, 'C'
, PPA.EFFECTIVE_DATE
, 'P'
, PPA.DATE_EARNED
, 'U'
, PPA.DATE_EARNED)
, PPA.EFFECTIVE_DATE) BETWEEN PAF.EFFECTIVE_START_DATE
AND PAF.EFFECTIVE_END_DATE
AND SCL.SOFT_CODING_KEYFLEX_ID = PAF.SOFT_CODING_KEYFLEX_ID
AND PAF.ASSIGNMENT_ID = PAA.ASSIGNMENT_ID
AND PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PAA.ASSIGNMENT_ACTION_ID = PPP.ASSIGNMENT_ACTION_ID(+)