All about Person payroll

 WITH

PERSON

AS

(

SELECT  

PAPF.person_number,

paam.assignment_id,

ORG.ORGANIZATION_CODE AS OU,

TRIM(PPNF.FIRST_NAME||' '||PPNF.MIDDLE_NAMES||' '||PPNF.LAST_NAME) Employee_Name,

PPNF.DISPLAY_NAME AS Legal_Employee_Name,

--PAAM.ASSIGNMENT_NUMBER AS Legal_Employee_Code,

hr_general.decode_lookup('NATIONALITY',PC.LEGISLATION_CODE) NATIONALITY,

PJ.NAME AS Designation,

PAAM.ASS_ATTRIBUTE7 AS Ext_Job_Title,

hr_general.decode_lookup('EMPLOYEE_CATG',PAAM.EMPLOYEE_CATEGORY) Employee_Type,

PG.NAME AS Grade,

PD.NAME AS DEPARTMENT,

PAAM.ASS_ATTRIBUTE5 AS Visa_Unit_Code,

LOC.LOCATION_NAME,

TO_CHAR(PP.DATE_OF_BIRTH,'yyyy-mm-dd') AS DATE_OF_BIRTH,

TO_CHAR(PPOS.ORIGINAL_DATE_OF_HIRE,'yyyy-mm-dd') AS Date_of_Joining,

VC.ATTRIBUTE2 AS Identification_Number

FROM

HR_LOCATIONS_ALL_F_VL LOC,

PER_DEPARTMENTS PD,

PER_GRADES_F_VL PG,

PER_JOBS_F_VL PJ,

per_visas_permits_f vc,

PER_CITIZENSHIPS PC,

HR_ALL_ORGANIZATION_UNITS_F org,

PER_PERIODS_OF_SERVICE PPOS,

per_all_assignments_m paam,

PER_PERSONS PP,

per_person_names_f ppnf,

per_all_people_f papf

WHERE 

1=1

AND      (:p_end_date) BETWEEN LOC.effective_start_date(+) AND LOC.effective_end_date(+)

AND LOC.LOCATION_ID(+) = PAAM.LOCATION_ID

AND      (:p_end_date) BETWEEN PD.effective_start_date(+) AND PD.effective_end_date(+)

AND PD.ORGANIZATION_ID(+) = PAAM.ORGANIZATION_ID

AND      (:p_end_date) BETWEEN PG.effective_start_date(+) AND PG.effective_end_date(+)

AND PG.GRADE_ID(+) = PAAM.GRADE_ID

AND      (:p_end_date) BETWEEN PJ.effective_start_date(+) AND PJ.effective_end_date(+)

AND PJ.JOB_ID(+) = PAAM.JOB_ID

AND      (:p_end_date) BETWEEN VC.effective_start_date(+) AND VC.effective_end_date(+)

AND VC.PERSON_ID(+) = PAAM.PERSON_ID

AND PC.PERSON_ID(+) = papf.PERSON_ID

AND      (:p_end_date) BETWEEN ORG.effective_start_date(+) AND ORG.effective_end_date(+)

AND ORG.ORGANIZATION_ID(+) = PAAM.LEGAL_ENTITY_ID

AND ppos.date_start =

(SELECT MAX(ppos1.date_start)

FROM PER_PERIODS_OF_SERVICE ppos1

WHERE ppos1.person_id = ppos.person_id

AND ppos1.period_type = ppos.period_type

AND ppos1 .PRIMARY_FLAG = 'Y')

AND ppos.PRIMARY_FLAG = 'Y'

AND      PPOS.period_of_service_id = paam.period_of_service_id

AND      (:p_end_date) BETWEEN paam.effective_start_date(+) AND paam.effective_end_date(+)

AND      paam.effective_latest_change = 'Y'

AND      paam.assignment_type = 'E'

AND paam.PRIMARY_FLAG = 'Y'

AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

AND      paam.person_id = papf.person_id

AND PP.person_id(+) = papf.person_id

AND      (:p_end_date) BETWEEN ppnf.effective_start_date(+) AND ppnf.effective_end_date(+)

AND      ppnf.name_type = 'GLOBAL'

AND      ppnf.person_id = papf.person_id

AND      (:p_end_date) BETWEEN PAPF.effective_start_date(+) AND PAPF.effective_end_date(+)

---AND PAPF.person_number IN ('106985','213574','244284','145742','141947','140002','140475')

),

BANK

AS

(

SELECT 

ppa.hr_assignment_id,

ppa.payroll_assignment_id,

popmt.ORG_PAYMENT_METHOD_NAME AS Payment_Method,

PAY_BANK.BANK_NAME AS Bank,

PAY_BANK.BRANCH_NUMBER Bank_Routing_Code,

PAY_BANK.IBAN_NUMBER Bank_Account_Number,

PAY_BANK.bank_account_num AS Account_Number,

TO_CHAR(ptp.regular_process_date,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=ENGLISH') AS Month

FROM

pay_time_periods ptp,

PAY_ALL_PAYROLLS_F PAY_ALL,

pay_consolidation_sets PCS,

pay_bank_accounts PAY_BANK,

pay_org_pay_methods_tl popmt,

PAY_ORG_PAY_METHODS_F PPOM,

pay_person_pay_methods_f pppmf,

pay_payroll_assignments ppa,

per_all_assignments_m paam,

per_all_people_f papf

WHERE 1=1

AND (:p_end_date) BETWEEN PTP.start_date AND PTP.end_date

AND ptp.cut_off_date IS NOT NULL

AND ptp.payroll_id = PAY_ALL.payroll_id 

AND      (:p_end_date) BETWEEN PAY_ALL.effective_start_date(+) AND PAY_ALL.effective_end_date(+)

AND PAY_ALL.consolidation_set_id(+)  = PCS.consolidation_set_id 

AND PCS.LEGISLATIVE_DATA_GROUP_ID(+) = PPOM.LEGISLATIVE_DATA_GROUP_ID

AND PAY_BANK.BANK_ACCOUNT_ID(+) = PPPMF.BANK_ACCOUNT_ID

AND      popmt.language(+) = 'US'

AND      popmt.org_payment_method_id(+) = PPOM.org_payment_method_id

AND      (:p_end_date) BETWEEN PPOM.effective_start_date(+) AND PPOM.effective_end_date(+)

AND PPOM.org_payment_method_id(+) = pppmf.org_payment_method_id

AND      (:p_end_date) BETWEEN pppmf.effective_start_date(+) AND pppmf.effective_end_date(+)

AND      pppmf.payroll_relationship_id(+) = ppa.payroll_relationship_id

AND      ppa.hr_assignment_id(+) = paam.assignment_id

AND      (:p_end_date) BETWEEN paam.effective_start_date(+) AND paam.effective_end_date(+)

AND      paam.effective_latest_change = 'Y'

AND      paam.assignment_type = 'E'

AND paam.PRIMARY_FLAG = 'Y'

AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

AND      paam.person_id = papf.person_id

AND      (:p_end_date) BETWEEN PAPF.effective_start_date(+) AND PAPF.effective_end_date(+)

),

ELEMENT

AS

(

SELECT peu.payroll_assignment_id,

(

NVL(sum(decode(pett.element_name || pivt.name, 'Basic SalaryAmount',TO_NUMBER(peevf.screen_entry_value))),0)

+

NVL(sum(decode(pett.element_name || pivt.name,  'Housing AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0)

+

NVL(sum(decode(pett.element_name || pivt.name,  'Other AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0)

+

NVL(sum(decode(pett.element_name || pivt.name,  'Individual AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0)

)AS Fixed_Gross_Pay,

NVL(sum(decode(pett.element_name || pivt.name, 'AIB DAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS AIB_D,

NVL(sum(decode(pett.element_name || pivt.name, 'Air Fare AllowanceAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS AIR_FARE_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name, 'Air Ticket ArrearAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS AIR_TICKET_ARREAR,

NVL(sum(decode(pett.element_name || pivt.name, 'Arrear Employee AccomodationAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS ARREAR_EMPLOYEE_ACCOMODATION,

NVL(sum(decode(pett.element_name || pivt.name, 'Arrear Loss of Pay deductAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS ARREAR_LOSS_OF_PAY_DEDUCT,

NVL(sum(decode(pett.element_name || pivt.name, 'Basic SalaryAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS BASIC_SALARY,

NVL(sum(decode(pett.element_name || pivt.name, 'Dependent insurance RecoveryAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS DEPENDENT_INSURANCE_RECOVERY,

NVL(sum(decode(pett.element_name || pivt.name, 'DHA RecoveryAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS DHA_RECOVERY,

NVL(sum(decode(pett.element_name || pivt.name, 'Employee Accommodation deductionAmount',TO_NUMBER(peevf.screen_entry_value))),0) AS EMPLOYEE_ACCOMMODATION_DEDUCTION,

NVL(sum(decode(pett.element_name || pivt.name,  'Employee Pension ContributionAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS EMPLOYEE_PENSION_CONTRIBUTION,

NVL(sum(decode(pett.element_name || pivt.name,  'Employer Pension ContributionAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS EMPLOYER_PENSION_CONTRIBUTION,

NVL(sum(decode(pett.element_name || pivt.name,  'Food AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS FOOD_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Food Allowance FixedAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS FOOD_ALLOWANCE_FIXED,

NVL(sum(decode(pett.element_name || pivt.name,  'Gratuity AccrualAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS GRATUITY_ACCRUAL,

NVL(sum(decode(pett.element_name || pivt.name,  'Gratuity ProvisionAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS GRATUITY_PROVISION,

NVL(sum(decode(pett.element_name || pivt.name,  'Gross SalaryAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS GROSS_PAY,

NVL(sum(decode(pett.element_name || pivt.name,  'Healthcare AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS HEALTHCARE_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Home DeliveryAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS HOME_DELIVERY,

NVL(sum(decode(pett.element_name || pivt.name,  'Housing AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS HOUSING_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'ILOE FeeAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS ILOE_FEE,

NVL(sum(decode(pett.element_name || pivt.name,  'Incentive ArrearsAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS INCENTIVE_ARREARS,

NVL(sum(decode(pett.element_name || pivt.name,  'Individual AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS INDIVIDUAL_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Loan Advance DeductionsAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS LOAN_ADVANCE_DEDUCTIONS,

NVL(sum(decode(pett.element_name || pivt.name,  'Loss of Pay deductionsAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS LOSS_OF_PAY_DEDUCTIONS,

NVL(sum(decode(pett.element_name || pivt.name,  'Marriage AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS MARRIAGE_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Net PayAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS NET_PAY,

NVL(sum(decode(pett.element_name || pivt.name,  'Night Shift AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS NIGHT_SHIFT_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Normal OvertimeAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS NORMAL_OVERTIME,

NVL(sum(decode(pett.element_name || pivt.name,  'One Time IncentiveAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS ONE_TIME_INCENTIVE,

NVL(sum(decode(pett.element_name || pivt.name,  'Optics Performance IncentiveAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS OPTICS_PERFORMANCE_INCENTIVE,

NVL(sum(decode(pett.element_name || pivt.name,  'Optics Product IncentiveAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS OPTICS_PRODUCT_INCENTIVE,

NVL(sum(decode(pett.element_name || pivt.name,  'Other AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS OTHER_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Over Time ArrearAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS OVER_TIME_ARREAR,

NVL(sum(decode(pett.element_name || pivt.name,  'Pharmacy Product IncentivesAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS PHARMACY_PRODUCT_INCENTIVES,

NVL(sum(decode(pett.element_name || pivt.name,  'Pharmacy Special IncentiveAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS PHARMACY_SPECIAL_INCENTIVE,

NVL(sum(decode(pett.element_name || pivt.name,  'PPI - Per Piece IncentivesAmount ',TO_NUMBER(peevf.screen_entry_value))),0) AS PPI_PER_PIECE_INCENTIVES,

NVL(sum(decode(pett.element_name || pivt.name,  'Salary Arrear RecoveryAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS SALARY_ARREAR_RECOVERY,

NVL(sum(decode(pett.element_name || pivt.name,  'Salary Arrear ReversalAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS SALARY_ARREAR_REVERSAL,

NVL(sum(decode(pett.element_name || pivt.name,  'Shortage Hrs DeductionAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS SHORTAGE_HRS_DEDUCTION,

NVL(sum(decode(pett.element_name || pivt.name,  'Shortage Hrs ReversalAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS SHORTAGE_HRS_REVERSAL,

NVL(sum(decode(pett.element_name || pivt.name,  'Special AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS SPECIAL_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Special Allowance EAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS SPECIAL_ALLOWANCE_E,

NVL(sum(decode(pett.element_name || pivt.name,  'Store Category AllowancesAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS STORE_CATEGORY_ALLOWANCES,

NVL(sum(decode(pett.element_name || pivt.name,  'Telephone AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS TELEPHONE_ALLOWANCE,

NVL(sum(decode(pett.element_name || pivt.name,  'Vehicle AllowanceAmount' ,TO_NUMBER(peevf.screen_entry_value))),0) AS VEHICLE_ALLOWANCE

FROM

pay_element_entry_values_f peevf,

pay_input_values_tl pivt,

pay_input_values_f pivf,

pay_element_types_tl pett,

pay_element_types_f petf,

pay_element_entries_f peef,

pay_entry_usages peu,

pay_payroll_assignments ppa,

per_all_assignments_m paam,

per_all_people_f papf

WHERE 1=1

AND  (:p_end_date) between peevf.effective_start_date and peevf.effective_end_date

AND peevf.element_entry_id = peef.element_entry_id

AND peevf.screen_entry_value IS NOT NULL

AND peevf.input_value_id = pivf.input_value_id

AND  pivt.name in ('Amount','Pay Value')

AND pivt.language = 'US'

AND pivt.input_value_id = pivf.input_value_id

AND (:p_end_date) between pivf.effective_start_date and pivf.effective_end_date

AND pivf.element_type_id = pett.element_type_id

/*AND pett.element_name IN ('Pharmacy Special Incentive','AIB D','Pharmacy Product Incentives',

'Shortage Hrs Reversal','Food Allowance Fixed','Gross Salary',

'Optics Performance Incentive','Special Allowance E','Air Ticket Arrear',

'One Time Incentive','Store Category Allowances','Incentive Arrears',

'Normal Overtime','Over Time Arrear','Optics Product Incentive',

'PPI - Per Piece Incentives','Home Delivery','Healthcare Allowance',

'Salary Arrear Reversal','Individual Allowance','Basic Salary',

'Housing Allowance','Telephone Allowance','Vehicle Allowance',

'Other Allowance','Air Fare Allowance','Special Allowance',

'Marriage Allowance','Night Shift Allowance','Food Allowance',

'Salary Arrear Recovery','Dependent insurance Recovery','Shortage Hrs Deduction',

'DHA Recovery','ILOE Fee','Employee Accommodation deduction',

'Arrear Employee Accomodation','Employee Pension Contribution','Loan Advance Deductions',

'Arrear Loss of Pay deduct','Loss of Pay deductions','Net Pay',

'Gratuity Provision','Employer Pension Contribution','Gratuity Accrual',

'')*/

AND pett.language = 'US'

AND pett.element_type_id = peef.element_type_id

AND (:p_end_date) between petf.effective_start_date and petf.effective_end_date

AND petf.element_type_id = peef.element_type_id

AND peef.element_entry_id = peu.element_entry_id

AND  peu.payroll_assignment_id = ppa.payroll_assignment_id

AND      ppa.hr_assignment_id(+) = paam.assignment_id

AND      (:p_end_date) BETWEEN paam.effective_start_date(+) AND paam.effective_end_date(+)

AND      paam.effective_latest_change = 'Y'

AND      paam.assignment_type = 'E'

AND paam.PRIMARY_FLAG = 'Y'

AND paam.ASSIGNMENT_STATUS_TYPE = 'ACTIVE'

AND      paam.person_id = papf.person_id

AND      (:p_end_date) BETWEEN PAPF.effective_start_date(+) AND PAPF.effective_end_date(+)

GROUP BY peu.payroll_assignment_id

ORDER BY peu.payroll_assignment_id

)

SELECT 

P.person_number,

P.OU,

P.Employee_Name,

P.Legal_Employee_Name,

P.NATIONALITY,

P.Designation,

P.Ext_Job_Title,

P.Employee_Type,

P.Grade,

P.DEPARTMENT,

P.LOCATION_NAME,

B.Payment_Method,

B.Bank,

B.Bank_Routing_Code,

B.Bank_Account_Number,

B.Account_Number,

P.Identification_Number,

P.DATE_OF_BIRTH,

P.Date_of_Joining,

B.Month,

E.Fixed_Gross_Pay,

'' AS Separation_Date,

'' AS Payable_Days,

'' AS LOP_Days,

'' AS AL_Days,

'' AS Standard_Hours,

E.PHARMACY_SPECIAL_INCENTIVE,

E.AIB_D,

E.PHARMACY_PRODUCT_INCENTIVES,

E.SHORTAGE_HRS_REVERSAL,

E.FOOD_ALLOWANCE_FIXED,

E.OPTICS_PERFORMANCE_INCENTIVE,

E.SPECIAL_ALLOWANCE_E,

E.AIR_TICKET_ARREAR,

E.ONE_TIME_INCENTIVE,

E.STORE_CATEGORY_ALLOWANCES,

E.INCENTIVE_ARREARS,

E.NORMAL_OVERTIME,

E.OVER_TIME_ARREAR,

E.OPTICS_PRODUCT_INCENTIVE,

E.PPI_PER_PIECE_INCENTIVES,

E.HOME_DELIVERY,

E.HEALTHCARE_ALLOWANCE,

E.SALARY_ARREAR_REVERSAL,

E.INDIVIDUAL_ALLOWANCE,

E.BASIC_SALARY,

E.HOUSING_ALLOWANCE,

E.TELEPHONE_ALLOWANCE,

E.VEHICLE_ALLOWANCE,

E.OTHER_ALLOWANCE,

E.AIR_FARE_ALLOWANCE,

E.SPECIAL_ALLOWANCE,

E.MARRIAGE_ALLOWANCE,

E.NIGHT_SHIFT_ALLOWANCE,

E.FOOD_ALLOWANCE,

E.GROSS_PAY,

E.SALARY_ARREAR_RECOVERY,

E.DEPENDENT_INSURANCE_RECOVERY,

E.SHORTAGE_HRS_DEDUCTION,

E.DHA_RECOVERY,

E.ILOE_FEE,

E.EMPLOYEE_ACCOMMODATION_DEDUCTION,

E.ARREAR_EMPLOYEE_ACCOMODATION,

E.EMPLOYEE_PENSION_CONTRIBUTION,

E.LOAN_ADVANCE_DEDUCTIONS,

E.ARREAR_LOSS_OF_PAY_DEDUCT,

E.LOSS_OF_PAY_DEDUCTIONS,

E.NET_PAY,

E.GRATUITY_PROVISION,

E.EMPLOYER_PENSION_CONTRIBUTION,

E.GRATUITY_ACCRUAL

FROM 

PERSON P,

BANK B,

ELEMENT E

WHERE 

1=1

AND B.payroll_assignment_id = E.payroll_assignment_id

AND P.assignment_id = B.hr_assignment_id

AND p.person_number IN ('145742','141947','140002','140475')

ORDER BY P.PERSON_NUMBER