-------------------------------- Monthly Employee payroll report -------------------
-----------Parameters---------------------
1.p_legal_entity
2.p_department
3.p_Grade
4.p_person
5.p_payroll_name
6.p_pay_date
----------list of values
------L_Payroll_Name lov
SELECT DISTINCT py.payroll_name,
py.payroll_id
FROM per_all_assignments_m PAAM,
per_all_people_f PAPF,
pay_all_payrolls_f py,
pay_consolidation_sets pcs,
pay_payroll_actions ppa,
pay_payroll_rel_actions PRA,
pay_assigned_payrolls_dn apd,
pay_payroll_terms pt,
pay_time_periods ptp,
pay_rel_groups_dn PRG
WHERE 1 = 1
AND PPA.earn_time_period_id = PTP.time_period_id
AND PRA.payroll_action_id = PPA.payroll_action_id
AND ppa.action_type IN ( 'R' )
AND PTP.period_category = 'E'
AND PRG.payroll_relationship_id = PRA.payroll_relationship_id
AND pt.payroll_term_id = apd.payroll_term_id
AND py.payroll_id = apd.payroll_id
AND ptp.payroll_id = py.payroll_id
AND PRA.source_action_id IS NULL
AND PAAM.primary_flag = 'Y'
AND pt.hr_term_id = PRG.term_id
AND Trunc(sysdate) BETWEEN PY.effective_start_date AND
PY.effective_end_date
AND Trunc(sysdate) BETWEEN PAAM.effective_start_date AND
PAAM.effective_end_date
AND PAAM.assignment_id = PRG.assignment_id
AND PCS.consolidation_set_id =
Nvl(PPA.consolidation_set_id, PY.consolidation_set_id)
AND PAAM.assignment_type = 'E'
AND PAPF.person_id = PAAM.person_id
AND Trunc(sysdate) BETWEEN PAPF.effective_start_date AND
PAPF.effective_end_date
ORDER BY 1
---------L_Pay_Date lov
SELECT DISTINCT To_char(default_paydate, 'MM/DD/YYYY',
'NLS_DATE_LANGUAGE=AMERICAN') SD,
time_period_id
FROM pay_time_periods
WHERE period_category = 'E'
ORDER BY time_period_id
---------L_Grade lov
SELECT DISTINCT NAME,
grade_id
FROM per_grades_f_vl
WHERE active_status = 'A'
ORDER BY NAME
---------L_Department lov
SELECT DISTINCT NAME,
organization_id
FROM per_departments
--------L_LE lov
SELECT DISTINCT haou1.NAME,
haou1.organization_id
FROM hr_organization_units_f_tl haou1,
per_all_assignments_f paam
WHERE 1 = 1
AND Trunc(sysdate) BETWEEN Trunc(paam.effective_start_date) AND
Trunc(paam.effective_end_date)
AND paam.assignment_type IN ( 'E', 'C' )
AND paam.primary_flag = 'Y'
AND paam.system_person_type = 'EMP'
AND paam.effective_latest_change = 'Y'
AND paam.legal_entity_id = haou1.organization_id
AND Trunc(sysdate) BETWEEN Trunc(haou1.effective_start_date) AND
Trunc(haou1.effective_end_date)
AND haou1.language = 'US'
--and rownum<=1
------------L_Person lov
SELECT DISTINCT ppnf.display_name,
ppnf.person_id
FROM per_person_names_f ppnf
WHERE Trunc(sysdate) BETWEEN Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date)
AND ppnf.name_type = 'GLOBAL'
ORDER BY ppnf.display_name
------------------------------------main query
with earn_deduct_detail as
(select * from
(select
papf.person_id,
pet.BASE_ELEMENT_NAME,
pec.base_classification_name,
piv.base_name,
prrv.RESULT_VALUE,
petl.reporting_name reporting_name1,
ptp.period_name payroll_month,
ppa.effective_date
from per_all_people_f papf,
per_all_assignments_f paaf,
pay_pay_relationships_dn pprd,
pay_payroll_rel_actions ppra,
pay_payroll_actions ppa,
pay_run_results prr,
pay_run_result_values prrv,
pay_element_types_f pet,
pay_input_values_f piv,
pay_element_types_tl petl,
pay_ele_classifications pec,
pay_time_periods ptp
where 1=1
and papf.person_id=paaf.person_id
AND pprd.person_id = papf.person_id
AND ppra.payroll_relationship_id = pprd.payroll_relationship_id
AND ppa.payroll_action_id = ppra.payroll_action_id
AND prr.payroll_rel_action_id = ppra.payroll_rel_action_id
AND prr.run_result_id = prrv.run_result_id
AND prr.element_type_id = pet.element_type_id
AND prrv.input_value_id = piv.input_value_id
AND pet.element_type_id = petl.element_type_id
AND pet.classification_id = pec.classification_id
AND ptp.time_period_id = ppa.earn_time_period_id
AND ppa.payroll_id = ptp.payroll_id
AND petl.language = 'US'
AND piv.uom = 'M'
AND UPPER (piv.base_name) = 'PAY VALUE'
and paaf.ASSIGNMENT_TYPE in ('E','C')
AND ppa.action_type = ('Q')
--and TRUNC(ppa.effective_date) < TRUNC(SYSDATE)
and trunc(ppa.effective_date) between papf.effective_start_date and papf.effective_end_date
and trunc(ppa.effective_date) between paaf.effective_start_date and paaf.effective_end_date
and papf.person_number=nvl(:p_person,papf.person_number)
and ptp.TIME_PERIOD_ID = nvl(:p_pay_date,ptp.TIME_PERIOD_ID)
and ptp.payroll_id=nvl(:p_payroll_name,ptp.payroll_id)
))
SELECT B.*,
total_row_earnings + total_row_benefits - total_row_deductions
Total_Row_Total
FROM (SELECT ( basic_salary + housing_allowance
+ telephone_allowance
+ personal_car_allowance + casheir_allowance
+ honoring_old_employee + fuel_allowance
+ over_time + airfare_allowance
+ basic_salary_retro
+ transport_allowance_retro + bonus
+ housing_allowance_retro
+ furniture_movement + personal_loan_payment
+ almg_work_condition_allowance
+ transport_allowance + eos_payment ) Total_Row_Earnings,
( vacation_allowance_final_disbursment
+ employee_gosi_saned
+ employee_gosi_annuities_retro
+ employee_gosi_annuities
+ employee_gosi_saned_retro
+ employer_gosi_saned
+ employer_gosi_annuities_retro
+ employer_gosi_saned_retro
+ employer_gosi_hazards_retro
+ employer_gosi_hazards
+ employer_gosi_annuities ) Total_Row_Benefits,
personal_loan_deduction_results Total_row_deductions,
A.*
FROM (SELECT
haou_leb.name
Business_Unit,
Decode(paaf.assignment_type, 'E', 'Employee',
'C', 'Contingent worker',
NULL)
person_type,
ppos.worker_number,
pd.name
Department,
hap.name
Position,
pj.name
Job,
pg.name
Grade,
To_char(ppos.date_start, 'mm/dd/yyyy')
hire_date,
haou.name
Branch,
papf.person_number,
paaf.person_id,
(SELECT display_name
FROM per_person_names_f
WHERE papf.person_id = person_id
AND Trunc(SYSDATE) BETWEEN
effective_start_date AND
effective_end_date
AND ROWNUM = 1)
Person_name,
edd.payroll_month,
Nvl((SELECT hrd.dei_attribute_number1
FROM hr_documents_of_record hrd
WHERE hrd.person_id = paaf.person_id
AND hrd.dei_attribute_category = 'GLB_IQAMA'
AND Trunc(SYSDATE) BETWEEN hrd.date_from AND
hrd.date_to), '0000')
National_identifier,
edd.effective_date,
-----------------------------------------------------earnings-----------------------------------------------
Nvl((SELECT SUM (e2.result_value)
FROM earn_deduct_detail e2
WHERE e2.reporting_name1 = 'Basic Salary'
AND e2.person_id = papf.person_id
AND
Trunc(e2.effective_date) = Trunc(edd.effective_date)), 0)
Basic_Salary,
Nvl((SELECT SUM (e2.result_value)
FROM earn_deduct_detail e2
WHERE e2.reporting_name1 = 'Housing Allowance'
AND e2.person_id = papf.person_id
AND
Trunc(e2.effective_date) = Trunc(edd.effective_date)), 0)
Housing_Allowance
,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Telephone Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Telephone_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Personal Car Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Personal_Car_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Casheir Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Casheir_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Honoring Old Employee'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Honoring_Old_Employee,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Fuel Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Fuel_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Over Time'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Over_Time,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Airfare Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Airfare_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Basic Salary Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Basic_Salary_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Transport Allowance Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Transport_Allowance_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Bonus'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Bonus,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Housing Allowance Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Housing_Allowance_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Furniture Movement'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Furniture_Movement,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Personal Loan Payment'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Personal_Loan_Payment,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 =
'ALMG_Work Condition Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
ALMG_Work_Condition_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Transport Allowance'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Transport_Allowance,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'EOS Payment'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
EOS_Payment,
----------------------------------------------------------benefits-----------------------------------
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 =
'Vacation Allowance Final Disbursment'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Vacation_Allowance_Final_Disbursment,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employee GOSI Saned'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employee_GOSI_Saned,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 =
'Employee GOSI Annuities Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employee_GOSI_Annuities_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employee GOSI Annuities'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employee_GOSI_Annuities,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employee GOSI Saned Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employee_GOSI_Saned_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employer GOSI Saned'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employer_GOSI_Saned,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 =
'Employer GOSI Annuities Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employer_GOSI_Annuities_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employer GOSI Saned Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employer_GOSI_Saned_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 =
'Employer GOSI Hazards Retro'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employer_GOSI_Hazards_Retro,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employer GOSI Hazards'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employer_GOSI_Hazards,
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 = 'Employer GOSI Annuities'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Employer_GOSI_Annuities,
----------------------------------------------------------deductions-----------------------------------
Nvl((SELECT SUM(result_value)
FROM earn_deduct_detail
WHERE reporting_name1 =
'Personal Loan Deduction Results'
AND person_id = edd.person_id
AND Trunc(effective_date) =
Trunc(edd.effective_date)), 0)
Personal_Loan_Deduction_Results
FROM per_all_people_f papf,
per_all_assignments_f paaf,
hr_all_organization_units haou,
hr_all_organization_units haou_leb,
per_periods_of_service ppos,
per_departments pd,
per_jobs pj,
hr_all_positions_f_vl hap,
per_grades_f_vl pg,
(SELECT DISTINCT person_id,
payroll_month,
effective_date
FROM earn_deduct_detail) edd
WHERE 1 = 1
AND papf.person_id = paaf.person_id
AND paaf.business_unit_id = haou_leb.organization_id (+)
AND paaf.organization_id = haou.organization_id (+)
AND paaf.person_id = edd.person_id(+)
AND paaf.assignment_type IN ( 'E', 'C' )
AND paaf.assignment_status_type = 'ACTIVE'
AND paaf.period_of_service_id = ppos.period_of_service_id
AND papf.person_id = ppos.person_id
AND paaf.organization_id = pd.organization_id(+)
AND Trunc(SYSDATE) BETWEEN Trunc(
pd.effective_start_date(+)) AND
Trunc(pd.effective_end_date(+))
AND paaf.job_id = pj.job_id(+)
AND Trunc(SYSDATE) BETWEEN Trunc(pj.effective_start_date(+)) AND
Trunc(pj.effective_end_date(+))
AND paaf.position_id = hap.position_id(+)
AND Trunc(SYSDATE) BETWEEN Trunc(hap.effective_start_date(+))
AND
Trunc(hap.effective_end_date(+))
AND paaf.grade_id = pg.grade_id(+)
AND Trunc(SYSDATE) BETWEEN Trunc(pg.effective_start_date(+)) AND
Trunc(pg.effective_end_date(+))
AND Trunc(edd.effective_date(+)) BETWEEN
papf.effective_start_date AND papf.effective_end_date
AND Trunc(edd.effective_date(+)) BETWEEN
paaf.effective_start_date AND paaf.effective_end_date
AND papf.person_id = Nvl(:p_person, papf.person_id)
AND haou_leb.organization_id =
Nvl(:p_legal_entity, haou_leb.organization_id)
AND ( ( paaf.organization_id ) IN ( :p_department )
OR 'All' IN ( :p_department
|| 'All' ) )
AND ( ( paaf.grade_id ) IN ( :p_Grade )
OR 'All' IN ( :p_Grade
|| 'All' ) )) A) B
ORDER BY worker_number
x
No comments:
Post a Comment