Payroll details of employees with all the attributes.
SELECT TO_NUMBER (pap.employee_number) employee_number,
INITCAP (pap.title) || ' ' || (pap.full_name) full_name,
NVL (pet.attribute1, pet.element_name) Description,
SUM(DECODE (pec.classification_name,
'Earnings', result_value,
'Deductions', result_value * 1,
'Voluntary Deductions', result_value * 1,
'Involuntary Deductions', result_value * 1,
'Employer Charges', result_value * 1,
'Statutory Deductions', result_value * 1,
result_value))
AS "Details",
hou.name Department
FROM pay_payroll_actions ppact,
hr_all_organization_units hou,
per_time_periods ptp,
pay_assignment_actions paact,
pay_run_results prr,
pay_run_result_values prrv,
per_assignments_f paa,
per_people_f pap,
pay_element_classifications_tl pec,
pay_element_types_f pet,
pay_input_values_f piv,
pay_personal_payment_methods_f pppm,
pay_org_payment_methods_f popm,
pay_external_accounts pea,
pay_consolidation_sets ab
WHERE ppact.effective_date BETWEEN paa.effective_start_date
AND paa.effective_end_date
AND ppact.effective_date BETWEEN pap.effective_start_date
AND pap.effective_end_date
AND ppact.effective_date BETWEEN pet.effective_start_date
AND pet.effective_end_date
AND ppact.effective_date BETWEEN piv.effective_start_date
AND piv.effective_end_date
AND ppact.effective_date BETWEEN popm.effective_start_date
AND popm.effective_end_date
AND ppact.effective_date BETWEEN pppm.effective_start_date
AND pppm.effective_end_date
AND pppm.assignment_id(+) = PAA.assignment_id
AND hou.organization_id = paa.organization_id
AND ab.consolidation_set_id = ppact.CONSOLIDATION_SET_ID
AND ab.business_group_id = pap.business_group_id
AND pppm.org_payment_method_id = popm.org_payment_method_id(+)
AND pea.external_account_id(+) = pppm.external_account_id
AND ppact.payroll_action_id = paact.payroll_action_id
AND ppact.time_period_id = ptp.time_period_id
AND ppact.payroll_id = paa.payroll_id
AND paact.assignment_action_id = prr.assignment_action_id
AND prr.run_result_id = prrv.run_result_id
AND paact.assignment_id = paa.assignment_id
AND paa.person_id = pap.person_id
AND pec.classification_id = pet.classification_id
AND pec.language = 'US'
-- and pap.employee_number=:P_emp_no --Specific Employee
AND pet.element_type_id = piv.element_type_id
AND pet.element_type_id = pet.element_type_id
AND prr.element_type_id = pet.element_type_id
AND prrv.input_value_id = piv.input_value_id
AND piv.NAME = 'Pay Value'
AND paa.primary_flag = 'Y'
AND (NVL (prrv.result_value, '0')) <> '0'
AND pec.classification_name IN
('Earnings',
'Employer Charges',
'Statutory Deductions',
'Voluntary Deductions',
'Involuntary Deductions')
AND TO_CHAR (ppact.effective_date, 'MM/DD/YYYY') = '09/30/2020'
AND pap.business_group_id = '83'
AND ppact.action_type IN ('R', 'Q')
AND pet.element_name IN
('Air Ticket Payment',
'Air Ticket Provision',
'Annual Leave Adjustment',
'Annual Leave Element',
'Annual Leave Encashment',
'Annual Leave Opening Balance',
'Annual Salary Recovery',
'Annual Vacation Payment',
'Basic Salary',
'Basic Salary Arrears',
'Basic Salary Deduction',
'Bonus',
'Bonus Dedcution',
'Business Leave',
'Car Allowance',
'Car Allowance Arrears',
'Car Allowance Deduction',
'Death Leave',
'Emergency Leave',
'Employee GOSI Annuities',
'Employee GOSI Arrears',
'Employee GOSI Arrears Payment',
'Employee Loan',
'Employee Loan Adjustment',
'Employee Loan Information',
'Employee Loan Installment',
'Employee Loan2',
'Employee Loan2 Adjustment',
'Employee Loan2 Information',
'Employee Loan2 Installment',
'Employee Loan3',
'Employee Loan3 Adjustment',
'Employee Loan3 Information',
'Employee Loan3 Installment',
'EOS Leave Advance Deduction',
'EOS Leave Advance Due',
'EOSB Information',
'Exam Leave',
'Exam Leave Unpaid',
'Exam Leave Unpaid Deduction',
'Food Allowance',
'Food Allowance Arrears',
'Food Allowance Deduction',
'GOSI Employee Contribution',
'GOSI Employer Contribution',
'GOSI Information',
'GOSI Reference Salary',
'GOSI Reference Salary Adjustment',
'Gratuity Payment EOS',
'Gratuity Provision',
'Gross Salary',
'Group Decision Information',
'Hajj Leave Absence',
'HRA',
'HRA Arrears',
'Iddah Leave Absence',
'Iqama Renewal',
'Marriage leave',
'Maternity Child Sick Leave',
'Maternity Leave Absence',
'Medical Insurance Provision',
'Night Shift Allowance',
'Night Shift Allowance Deduction',
'Notice Period',
'Other Deductions',
'Other Payments',
'Overtime Non Regular',
'Overtime Regular',
'Parental Leave',
'Provision',
'Salary Deduction',
'Sick Leave Absence',
'Sick Leave Unpaid Deduction',
'Suspended Leave',
'Suspended Leave Deduction',
'Telephone Allowance',
'Tell Allowance Deduction',
'Training Leave Absence',
'Transport Allowance',
'Transport Allowance Arrears',
'Transport Allowance Deduction',
'Unpaid Leave Absence',
'Unpaid Leave Deduction',
'Unpaid Leave Information',
'Vacation Provision')
GROUP BY pap.employee_number,
INITCAP (pap.title)
|| ' '
|| NVL (pap.per_information4, pap.first_name),
( PER_INFORMATION3
|| ' '
|| PER_INFORMATION4
|| ' '
|| PER_INFORMATION5
|| ' '
|| PER_INFORMATION6),
pap.first_name,
pet.attribute1,
popm.ORG_PAYMENT_METHOD_NAME,
ppact.CONSOLIDATION_SET_ID,
DECODE (pec.classification_name, 'Earnings', result_value),
DECODE (pec.classification_name,
'Deductions', result_value * 1,
'Voluntary Deductions', result_value * 1,
'Involuntary Deductions', result_value * 1,
'Employer Charges', result_value * 1,
'Statutory Deductions', result_value * 1),
pet.reporting_name,
pet.element_name,
paa.business_group_id,
pap.full_name,
pap.NATIONALITY,
paa.position_id,
pap.effective_start_date,
pap.title,
pap.business_group_id,
pap.person_id,
paa.assignment_id,
pap.person_id,
ppact.effective_date,
paa.location_id,
paa.organization_id,
pec.classification_name,
paa.grade_id,
paa.payroll_id,
ptp.time_period_id,
pap.national_identifier,
AB.CONSOLIDATION_SET_NAME,
hou.name
ORDER BY 1