VIEW: APPS.PER_HU_EMPLOYMENT_CERT_V

Source

View Text - Preformatted

SELECT ppa.payroll_action_id ,ppa.request_id ,paa.assignment_action_id ,pai1.action_information1 organization_id ,pai1.action_information5 employer_location_name ,pai1.action_information6 employer_location_type ,pai1.action_information7 employer_street_no ,pai1.action_information8 employer_town ,pai1.action_information13 employer_county ,pai1.action_information12 employer_postal_code ,pai1.action_information26 employer_building ,pai1.action_information27 employer_stairway ,pai1.action_information28 employer_floor ,pai1.action_information29 employer_door ,pai2.action_information1 person_id ,pai2.action_information5 company_name ,pai2.action_information6 full_name ,pai2.action_information7 maiden_name ,pai2.action_information8 taj_code ,pai2.action_information9 place_of_birth ,pai2.action_information10 date_of_birth ,pai2.action_information11 mother_maiden_name ,pai2.action_information12 hire_date ,pai2.action_information13 termination_date ,pai2.action_information14 sickness_holiday ,pai2.action_information15 railway_benefit ,pai2.action_information16 pension_fund_date_of_entrance ,pai2.action_information17 private_pension_fund_code ,pai2.action_information18 private_pension_fund_name ,pai2.action_information19 private_pension_fund_address ,pai2.action_information20 private_pension_fund_acc_no ,pai2.action_information21 voluntary_pension_fund_name1 ,pai2.action_information22 voluntary_pension_fund_name2 ,pai2.action_information23 voluntary_pension_fund_name3 ,pai2.action_information24 voluntary_pension_fund_name4 ,pai2.action_information25 voluntary_pension_fund_name5 ,pai2.action_information26 issue_date ,pai3.action_information5 employee_location_name ,pai3.action_information6 employee_location_type ,pai3.action_information7 employee_street_no ,pai3.action_information8 employee_town ,pai3.action_information12 employee_postal_code ,pai3.action_information26 employee_building ,pai3.action_information27 employee_stairway ,pai3.action_information28 employee_floor ,pai3.action_information29 employee_door ,' ' settlement_paid FROM pay_action_information pai1 ,pay_action_information pai2 ,pay_action_information pai3 ,pay_assignment_actions paa ,pay_payroll_actions ppa WHERE ppa.payroll_action_id = paa.payroll_action_id AND ppa.payroll_action_id = pai1.action_context_id AND paa.assignment_action_id = pai2.action_context_id AND pai2.action_context_id = pai3.action_context_id (+) AND pai1.action_information_category = 'ADDRESS DETAILS' AND pai1.action_context_type = 'PA' AND pai2.action_information_category = 'HU_EMP_CERTIFICATION' AND pai2.action_context_type = 'AAP' AND pai3.action_information_category(+) = 'ADDRESS DETAILS' AND pai3.action_context_type(+) = 'AAP' AND pai1.action_information1 = pai2.action_information2 AND pai2.action_information1 = pai3.action_information1(+) AND pai1.action_information14 = 'EMPLOYER' AND pai3.action_information14(+)= 'EMPLOYEE'

View Text - HTML Formatted

SELECT PPA.PAYROLL_ACTION_ID
, PPA.REQUEST_ID
, PAA.ASSIGNMENT_ACTION_ID
, PAI1.ACTION_INFORMATION1 ORGANIZATION_ID
, PAI1.ACTION_INFORMATION5 EMPLOYER_LOCATION_NAME
, PAI1.ACTION_INFORMATION6 EMPLOYER_LOCATION_TYPE
, PAI1.ACTION_INFORMATION7 EMPLOYER_STREET_NO
, PAI1.ACTION_INFORMATION8 EMPLOYER_TOWN
, PAI1.ACTION_INFORMATION13 EMPLOYER_COUNTY
, PAI1.ACTION_INFORMATION12 EMPLOYER_POSTAL_CODE
, PAI1.ACTION_INFORMATION26 EMPLOYER_BUILDING
, PAI1.ACTION_INFORMATION27 EMPLOYER_STAIRWAY
, PAI1.ACTION_INFORMATION28 EMPLOYER_FLOOR
, PAI1.ACTION_INFORMATION29 EMPLOYER_DOOR
, PAI2.ACTION_INFORMATION1 PERSON_ID
, PAI2.ACTION_INFORMATION5 COMPANY_NAME
, PAI2.ACTION_INFORMATION6 FULL_NAME
, PAI2.ACTION_INFORMATION7 MAIDEN_NAME
, PAI2.ACTION_INFORMATION8 TAJ_CODE
, PAI2.ACTION_INFORMATION9 PLACE_OF_BIRTH
, PAI2.ACTION_INFORMATION10 DATE_OF_BIRTH
, PAI2.ACTION_INFORMATION11 MOTHER_MAIDEN_NAME
, PAI2.ACTION_INFORMATION12 HIRE_DATE
, PAI2.ACTION_INFORMATION13 TERMINATION_DATE
, PAI2.ACTION_INFORMATION14 SICKNESS_HOLIDAY
, PAI2.ACTION_INFORMATION15 RAILWAY_BENEFIT
, PAI2.ACTION_INFORMATION16 PENSION_FUND_DATE_OF_ENTRANCE
, PAI2.ACTION_INFORMATION17 PRIVATE_PENSION_FUND_CODE
, PAI2.ACTION_INFORMATION18 PRIVATE_PENSION_FUND_NAME
, PAI2.ACTION_INFORMATION19 PRIVATE_PENSION_FUND_ADDRESS
, PAI2.ACTION_INFORMATION20 PRIVATE_PENSION_FUND_ACC_NO
, PAI2.ACTION_INFORMATION21 VOLUNTARY_PENSION_FUND_NAME1
, PAI2.ACTION_INFORMATION22 VOLUNTARY_PENSION_FUND_NAME2
, PAI2.ACTION_INFORMATION23 VOLUNTARY_PENSION_FUND_NAME3
, PAI2.ACTION_INFORMATION24 VOLUNTARY_PENSION_FUND_NAME4
, PAI2.ACTION_INFORMATION25 VOLUNTARY_PENSION_FUND_NAME5
, PAI2.ACTION_INFORMATION26 ISSUE_DATE
, PAI3.ACTION_INFORMATION5 EMPLOYEE_LOCATION_NAME
, PAI3.ACTION_INFORMATION6 EMPLOYEE_LOCATION_TYPE
, PAI3.ACTION_INFORMATION7 EMPLOYEE_STREET_NO
, PAI3.ACTION_INFORMATION8 EMPLOYEE_TOWN
, PAI3.ACTION_INFORMATION12 EMPLOYEE_POSTAL_CODE
, PAI3.ACTION_INFORMATION26 EMPLOYEE_BUILDING
, PAI3.ACTION_INFORMATION27 EMPLOYEE_STAIRWAY
, PAI3.ACTION_INFORMATION28 EMPLOYEE_FLOOR
, PAI3.ACTION_INFORMATION29 EMPLOYEE_DOOR
, ' ' SETTLEMENT_PAID
FROM PAY_ACTION_INFORMATION PAI1
, PAY_ACTION_INFORMATION PAI2
, PAY_ACTION_INFORMATION PAI3
, PAY_ASSIGNMENT_ACTIONS PAA
, PAY_PAYROLL_ACTIONS PPA
WHERE PPA.PAYROLL_ACTION_ID = PAA.PAYROLL_ACTION_ID
AND PPA.PAYROLL_ACTION_ID = PAI1.ACTION_CONTEXT_ID
AND PAA.ASSIGNMENT_ACTION_ID = PAI2.ACTION_CONTEXT_ID
AND PAI2.ACTION_CONTEXT_ID = PAI3.ACTION_CONTEXT_ID (+)
AND PAI1.ACTION_INFORMATION_CATEGORY = 'ADDRESS DETAILS'
AND PAI1.ACTION_CONTEXT_TYPE = 'PA'
AND PAI2.ACTION_INFORMATION_CATEGORY = 'HU_EMP_CERTIFICATION'
AND PAI2.ACTION_CONTEXT_TYPE = 'AAP'
AND PAI3.ACTION_INFORMATION_CATEGORY(+) = 'ADDRESS DETAILS'
AND PAI3.ACTION_CONTEXT_TYPE(+) = 'AAP'
AND PAI1.ACTION_INFORMATION1 = PAI2.ACTION_INFORMATION2
AND PAI2.ACTION_INFORMATION1 = PAI3.ACTION_INFORMATION1(+)
AND PAI1.ACTION_INFORMATION14 = 'EMPLOYER'
AND PAI3.ACTION_INFORMATION14(+)= 'EMPLOYEE'