WITH basic_pay
AS (SELECT peef.person_id,
(SELECT Round(( peevf.screen_entry_value / 12 ), 2)
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'Amount'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)
Basic_Amount
FROM per_all_assignments_m asg,
pay_rel_groups_dn payrel,
pay_assigned_payrolls_dn papd,
pay_all_payrolls_f pay,
per_legislative_data_groups_vl ldg,
pay_element_types_vl pet,
pay_element_entries_f peef,
pay_element_types_f petf,
pay_entry_usages peu
WHERE 1 = 1
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ( 'E' )
--AND asg.assignment_status_type = 'ACTIVE'
AND asg.assignment_id = payrel.assignment_id
AND payrel.group_type = 'A'
AND papd.payroll_term_id = payrel.parent_rel_group_id
AND papd.payroll_id = pay.payroll_id
AND ldg.legislation_code = asg.legislation_code
--AND peef.person_id(+) = papf.person_id
AND pet.element_type_id = peef.element_type_id
AND ldg.legislative_data_group_id =
pet.legislative_data_group_id
AND pet.element_type_id = petf.element_type_id
-- and ser.ATTRIBUTE6 = 'Y'
AND petf.base_element_name IN ( 'XXXXXXXXXX' ) --Hrdcode elements
AND peu.element_entry_id = peef.element_entry_id
AND peu.payroll_relationship_id = payrel.payroll_relationship_id
-- AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
-- asg.effective_end_date
AND Trunc (SYSDATE) BETWEEN papd.start_date AND
Nvl (papd.lspd, To_date
(
'31/12/4712', 'DD/MM/YYYY'))
AND Trunc (SYSDATE) BETWEEN pay.effective_start_date AND
pay.effective_end_date
AND Trunc (SYSDATE) BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND peu.date_from BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND peu.date_from BETWEEN payrel.start_date AND payrel.end_date
AND Trunc (SYSDATE) BETWEEN peef.effective_start_date AND
peef.effective_end_date
ORDER BY peef.person_id),
air_elements
AS (SELECT peef.person_id,
(SELECT peevf.screen_entry_value
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'XXXXXXXXXXXXXXXX'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)
Dependents_Eligible,
(SELECT peevf.screen_entry_value
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'XXXXXXXXXXXX'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)Destination,
(SELECT peevf.screen_entry_value
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'XXXXXXXXXXXXXXXX'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)Class_Of_Travel,
(SELECT peevf.screen_entry_value
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'XXXXXXXXXX'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)Frequency_Of_Travel,
(SELECT peevf.screen_entry_value
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'XXXXXXXX'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)Number_of_Children_Eligible,
(SELECT peevf.screen_entry_value
FROM pay_element_entry_values_f peevf,
pay_input_values_f pivf
WHERE peevf.element_entry_id = peef.element_entry_id
AND peef.last_update_date = peevf.last_update_date
AND peef.effective_start_date =
peevf.effective_start_date
AND peevf.input_value_id = pivf.input_value_id
AND pivf.base_name = 'XXXXXXX'
AND Trunc (SYSDATE) BETWEEN pivf.effective_start_date
AND
pivf.effective_end_date
AND Trunc (SYSDATE) BETWEEN peevf.effective_start_date
AND
peevf.effective_end_date)Paying_Month
--select peu.*
FROM per_all_assignments_m asg,
pay_rel_groups_dn payrel,
pay_assigned_payrolls_dn papd,
pay_all_payrolls_f pay,
per_legislative_data_groups_vl ldg,
pay_element_types_vl pet,
pay_element_entries_f peef,
pay_element_types_f petf,
pay_entry_usages peu
WHERE 1 = 1
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ( 'E' )
AND asg.assignment_status_type = 'ACTIVE'
AND asg.assignment_id = payrel.assignment_id
AND payrel.group_type = 'A'
and asg.EFFECTIVE_LATEST_CHANGE ='Y'
AND papd.payroll_term_id = payrel.parent_rel_group_id
AND papd.payroll_id = pay.payroll_id
AND ldg.legislation_code = asg.legislation_code
AND pet.element_type_id = peef.element_type_id
AND ldg.legislative_data_group_id = pet.legislative_data_group_id
AND pet.element_type_id = petf.element_type_id
-- and ser.ATTRIBUTE6 = 'Y'
AND petf.base_element_name IN ( 'XXXXXXX' ) --Hrdcode elements
AND peu.element_entry_id = peef.element_entry_id
AND peu.payroll_relationship_id = payrel.payroll_relationship_id
-- AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
-- asg.effective_end_date
AND Trunc (SYSDATE) BETWEEN papd.start_date AND
Nvl (papd.lspd, To_date
(
'31/12/4712', 'DD/MM/YYYY'))
AND Trunc (SYSDATE) BETWEEN pay.effective_start_date AND
pay.effective_end_date
AND Trunc (SYSDATE) BETWEEN pet.effective_start_date AND
pet.effective_end_date
AND peu.date_from BETWEEN asg.effective_start_date(+) AND
asg.effective_end_date(+)
AND peu.date_from BETWEEN payrel.start_date AND payrel.end_date
AND Trunc (SYSDATE) BETWEEN peef.effective_start_date AND
peef.effective_end_date
--and asg.person_id in (100000000786587)
),
prev_emp_exp
AS (SELECT SUM(exp) pre_exp,
person_id
FROM (SELECT hrbpei.item_text240_1,
hrbpei.item_date_1,
hrbpei.item_date_2,
Trunc(To_date(hrbpei.item_date_2, 'YYYY-MM-DD') -
To_date(hrbpei.item_date_1, 'YYYY-MM-DD')) exp,
asg.person_id
FROM hrt_profiles_b hrpb,
hrt_bi_prev_employment_items_v hrbpei,
hrt_content_types_b hrct,
hrt_content_types_tl hrctt,
hrt_profile_items hrpi,
per_all_assignments_m asg
WHERE hrpb.profile_id = hrbpei.profile_id(+)
AND hrbpei.content_type_id = hrct.content_type_id(+)
AND hrbpei.business_group_id = hrct.business_group_id(+)
AND hrct.content_type_id = hrctt.content_type_id(+)
AND hrct.business_group_id = hrctt.business_group_id(+)
AND hrctt.content_type_name = 'XXXXXXXX'
AND hrpb.profile_id = hrpi.profile_id(+)
AND hrpi.profile_item_id = hrbpei.profile_item_id(+)
AND hrpi.business_group_id = hrbpei.business_group_id(+)
AND asg.effective_latest_change = 'Y'
AND asg.primary_flag = 'Y'
AND asg.assignment_type = 'E'
AND asg.ASSIGNMENT_STATUS_TYPE <> 'INACTIVE'
AND hrpb.person_id = asg.person_id
AND hrpi.date_to BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND SYSDATE BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND Userenv('LANG') = hrctt.LANGUAGE(+))a
WHERE 1 = 1
--and a.person_id = paaf.person_id ----0122
GROUP BY person_id)
SELECT DISTINCT person_id,
person_number,
assignment_number,
title,
employee_full_name,
first_name,
middle_name,
family_name,
display_name,
employee_name_arabic,
arabic_first_name,
arabic_middle_name,
arabic_family_name,
date_of_joining,
date_of_birth,
age,
gender,
marital_status,
religion,
nationality,
personal_phone_number,
personal_mobile_number,
work_phone_number,
work_mobile_number,
line_manager_number,
line_manager_name,
legal_employer_name,
personal_email_addreess,
company_email_addreess,
business_unit_name,
location_name,
payroll_name,
department,
sector,
grade_name,
job,
position,
employee_category,
assignment_category,
person_type,
assignment_status,
primary_assignment_number,
(trunc(years_of_service_with_company / 365.25) || ' Years '|| trunc(mod(years_of_service_with_company,365.25) / 30) || ' Months ')years_of_service_with_company,
highest_education_level,
segment1,
segment2,
segment3,
segment4,
citizenship,
citizenship_status,
citizenship_from_date,
passport_number,
paassport_expiry_date,
length_of_service_with_company,
bank_name,
bank_account_num,
iban,
spouse,
child1,
child2,
child3,
direct_reporters,
air_ticket_destination,
air_ticket_class_of_travel,
air_ticket_frequency_of_travel,
air_ticket_dependents_eligible,
air_ticket_number_of_children_eligible,
air_ticket_paying_month,
civil_id,
civil_id_expiration_date,
probation_start_date,
probation_end_date,
length_of_service,
leaving_reason,
termination_action,
last_working_date,
notification_on_date,
rehire_recommendation,
employer_comments,
annual_leave_balance,
annual_leave_availed_in_current_year,
"Annual Leave Availed(As of now)",
"Sick Leave Count Current Year",
"Sick Leave Availed (as of now)",
"Unpaid Leave Count Current Year",
"Unpaid Leave Availed (as of now)",
"Air Ticket - Total",
grosss_salary,
basic_salary,
(trunc(over_all_exp / 365.25) || ' Years '|| trunc(mod(over_all_exp,365.25) / 30) || ' Months ') total_years_of_exp
FROM (SELECT DISTINCT papf.person_id,
papf.person_number,
paaf.assignment_number,
(SELECT ppnf.title
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date)) Title,
(SELECT ppnf.full_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Employee_Full_Name,
(SELECT ppnf.first_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
First_Name,
(SELECT ppnf.middle_names
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Middle_Name,
(SELECT ppnf.last_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Family_Name,
(SELECT ppnf.display_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Display_Name,
(SELECT ppnf.full_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Employee_Name_Arabic,
(SELECT ppnf.first_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Arabic_First_Name
,
(SELECT ppnf.middle_names
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Arabic_Middle_Name,
(SELECT ppnf.last_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Arabic_Family_Name,
To_char(Trunc(papf.start_date), 'DD-Mon-YYYY')
Date_of_Joining,
To_char(Trunc(pp.date_of_birth), 'DD-Mon-YYYY')
Date_of_Birth,
Trunc(( SYSDATE - To_date(pp.date_of_birth, 'YYYY-MM-DD'
) ) /
365.25) Age,
Decode (pplf.sex, 'M', 'Male',
'F', 'Female',
pplf.sex)
Gender,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE pplf.marital_status = lookup_code
AND lookup_type = 'MAR_STATUS'
AND LANGUAGE = 'US')
Marital_Status,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE pr.religion = lookup_code
AND lookup_type = 'PER_RELIGION'
AND LANGUAGE = 'US')
Religion,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE PC.legislation_code = lookup_code
AND lookup_type = 'NATIONALITY'
AND LANGUAGE = 'US')
Nationality,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'H1')
Personal_Phone_Number,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'MOBILE')
Personal_Mobile_Number,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'W1')
Work_Phone_Number,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'WM')
Work_Mobile_Number,
manager_info.line_manager_number,
manager_info.line_manager_name,
ple.name
Legal_Employer_Name,
(SELECT pea.email_address
FROM per_email_addresses pea
WHERE pea.email_type = 'H1'
AND pea.person_id = papf.person_id)
Personal_Email_addreess,
(SELECT pea.email_address
FROM per_email_addresses pea
WHERE pea.email_type = 'W1'
AND pea.person_id = papf.person_id)
Company_Email_addreess,
hou.name
Business_Unit_Name,
pldft.location_name,
pay.payroll_name,
pd.name
Department,
paaf.ass_attribute1
sector,
pgft.name
grade_name,
pjft.name
Job,
hapft.name
Position,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE paaf.employee_category = lookup_code
AND lookup_type = 'EMPLOYEE_CATG'
AND LANGUAGE = 'US')
Employee_Category,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE paaf.employment_category = lookup_code
AND lookup_type = 'EMP_CAT'
AND LANGUAGE = 'US')
Assignment_Category,
ppt.user_person_type
person_type,
paaf.assignment_status_type
assignment_status,
paaf.assignment_number
primary_assignment_number,
--Trunc(((MONTHS_BETWEEN(SYSDATE,papf.start_date) /12) - Trunc(MONTHS_BETWEEN(SYSDATE,papf.start_date) /12))*12)) years
Trunc(( SYSDATE - To_date(papf.start_date, 'YYYY-MM-DD')
))
Years_of_Service_with_company,
--(MONTHS_BETWEEN(SYSDATE,papf.start_date) /12) years
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE pplf.highest_education_level = lookup_code
AND lookup_type = 'ORA_PER_HIGHEST_EDUCATION_LEVE'
AND LANGUAGE = 'US')highest_education_level,
ppg.segment1,
ppg.segment2,
ppg.segment3,
ppg.segment4,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE PC.legislation_code = lookup_code
AND lookup_type = 'NATIONALITY'
AND LANGUAGE = 'US')
citizenship,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE PC.citizenship_status = lookup_code
AND lookup_type = 'PER_CITIZENSHIP_STATUS'
AND LANGUAGE = 'US')
citizenship_status,
To_char(pc.date_from, 'DD-Mon-YYYY')
citizenship_From_Date,
pass.passport_number,
To_char(pass.expiration_date, 'DD-Mon-YYYY')
paassport_expiry_date,
Trunc(SYSDATE - To_date(papf.start_date, 'YYYY-MM-DD'))
length_of_Service_with_company,
Bank_Details.bank_name,
Bank_Details.bank_account_num,
Bank_Details.iban,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'S'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) SPOUSE,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'C'
AND PCR.cont_attribute1 = 'Child1'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) Child1,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'C'
AND PCR.cont_attribute1 = 'Child2'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) Child2,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'C'
AND PCR.cont_attribute1 = 'Child3'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) Child3,
direct_reporters.count_no
direct_reporters,
ae.destination
Air_Ticket_Destination,
ae.class_of_travel
Air_Ticket_Class_Of_Travel,
ae.frequency_of_travel
Air_Ticket_Frequency_Of_Travel,
ae.dependents_eligible
Air_Ticket_Dependents_Eligible,
ae.number_of_children_eligible
Air_Ticket_Number_of_Children_Eligible,
ae.paying_month
Air_Ticket_Paying_Month,
(SELECT national_identifier_number
FROM per_national_identifiers
WHERE person_id = papf.person_id
AND national_identifier_type = 'CI')
Civil_Id,
(SELECT expiration_date
FROM per_national_identifiers
WHERE person_id = papf.person_id
AND national_identifier_type = 'CI')
Civil_Id_EXPIRATION_DATE,
paaf.probation_period
probation_start_date,
To_char(paaf.date_probation_end, 'DD-Mon-YYYY')
probation_end_date,
NULL
length_of_service,
NULL
leaving_reason,
NULL
termination_action,
NULL
last_working_date,
NULL
notification_on_date,
NULL
REHIRE_RECOMMENDATION,
NULL
Employer_Comments,
(SELECT DISTINCT Round(SUM(acrl1.end_bal), 2)
plan_balance
FROM per_all_assignments_m paam,
per_all_people_f papf2,
anc_per_plan_enrollment appe,
anc_absence_plans_f_tl aapft,
anc_per_accrual_entries acrl1
WHERE paam.person_id = papf2.person_id
AND appe.person_id = papf2.person_id
AND aapft.absence_plan_id = appe.plan_id
AND paam.assignment_status_type = 'ACTIVE'
AND aapft.name = 'Annual Leave'
AND appe.work_term_asg_id(+) =
paam.work_terms_assignment_id
AND acrl1.person_id = papf2.person_id
AND acrl1.work_term_asg_id(+) =
paam.work_terms_assignment_id
AND acrl1.plan_id = ( appe.plan_id )
AND acrl1.accrual_period = (SELECT
Max(acrl2.accrual_period)
FROM
anc_per_accrual_entries acrl2
WHERE
person_id = papf2.person_id
AND acrl2.plan_id = appe.plan_id)
AND aapft.LANGUAGE = 'US'
AND Trunc(SYSDATE) BETWEEN
paam.effective_start_date
AND
paam.effective_end_date
AND Trunc(SYSDATE) BETWEEN
paam.effective_start_date
AND
paam.effective_end_date
AND Trunc(SYSDATE) BETWEEN
aapft.effective_start_date
AND
aapft.effective_end_date
AND paam.primary_flag = 'Y'
AND papf2.person_id = papf.person_id
--AND papf.person_number = '0047'
AND paam.assignment_type = 'E')
annual_leave_balance,
(SELECT DISTINCT SUM(apate.leave_duration)
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_person_names_f per_name,
per_periods_of_service ser,
per_person_types_tl per_typ,
per_legal_employers ple,
anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf,
anc_per_accrual_entries acc_ent,
anc_per_abs_type_entries apate
WHERE asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND acc_ent.person_id = peo.person_id
AND asg.period_of_service_id =
ser.period_of_service_id
AND acc_ent.prd_of_svc_id =
asg.period_of_service_id
AND asg.person_type_id = per_typ.person_type_id
AND asg.legal_entity_id = ple.organization_id
AND aapft.absence_plan_id = aapf.absence_plan_id
AND acc_ent.plan_id = aapf.absence_plan_id
AND acc_ent.accrual_period =
(SELECT
Max (acc_ent1.accrual_period)
FROM
anc_per_accrual_entries acc_ent1
WHERE
acc_ent1.accrual_period <=
To_date (Substr (Trunc (SYSDATE), 1
, 10))
AND acc_ent1.person_id =
acc_ent.person_id
AND
acc_ent1.prd_of_svc_id =
acc_ent.prd_of_svc_id
AND
acc_ent1.plan_id =
acc_ent.plan_id)
AND asg.assignment_id = apate.assignment_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ( 'E', 'C', 'N', 'P' )
AND asg.assignment_status_type = 'ACTIVE'
AND per_name.name_type = 'GLOBAL'
AND ple.status = 'A'
AND per_typ.LANGUAGE = Userenv ('LANG')
AND aapf.plan_status = 'A'
AND aapft.LANGUAGE = 'US'
AND acc_ent.end_bal <> 0
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND Trunc (SYSDATE) BETWEEN per_name.effective_start_date AND
per_name.effective_end_date
AND Trunc (SYSDATE) BETWEEN ple.effective_start_date AND
ple.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND apate.start_datetime >= ( Trunc(SYSDATE, 'YY') )
AND aapft.name = 'Annual Leave'
AND papf.person_id = peo.person_id
AND apate.uom = 'D')
Annual_Leave_availed_in_current_year,
(SELECT DISTINCT SUM(apate.leave_duration)
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_person_names_f per_name,
per_periods_of_service ser,
per_person_types_tl per_typ,
per_legal_employers ple,
anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf,
anc_per_accrual_entries acc_ent,
anc_per_abs_type_entries apate
WHERE asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND acc_ent.person_id = peo.person_id
AND asg.period_of_service_id = ser.period_of_service_id
AND acc_ent.prd_of_svc_id = asg.period_of_service_id
AND asg.person_type_id = per_typ.person_type_id
AND asg.legal_entity_id = ple.organization_id
AND aapft.absence_plan_id = aapf.absence_plan_id
AND acc_ent.plan_id = aapf.absence_plan_id
AND acc_ent.accrual_period = (SELECT Max (acc_ent1.accrual_period)
FROM anc_per_accrual_entries acc_ent1
WHERE acc_ent1.accrual_period <=
To_date (Substr (Trunc (SYSDATE), 1
, 10))
AND acc_ent1.person_id =
acc_ent.person_id
AND
acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id
AND acc_ent1.plan_id =
acc_ent.plan_id)
AND asg.assignment_id = apate.assignment_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ( 'E', 'C', 'N', 'P' )
AND asg.assignment_status_type = 'ACTIVE'
AND per_name.name_type = 'GLOBAL'
AND ple.status = 'A'
AND per_typ.LANGUAGE = Userenv ('LANG')
AND aapf.plan_status = 'A'
AND aapft.LANGUAGE = 'US'
AND acc_ent.end_bal <> 0
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND Trunc (SYSDATE) BETWEEN per_name.effective_start_date AND
per_name.effective_end_date
AND Trunc (SYSDATE) BETWEEN ple.effective_start_date AND
ple.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND apate.start_datetime >= (SELECT Trunc(asg1.start_date, 'YY')
FROM per_all_people_f asg1
WHERE 1 = 1
AND asg.person_id = asg1.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(asg1.effective_start_date)
AND
Trunc(asg1.effective_end_date))
AND peo.person_id = papf.person_id
--and peo.person_number ='0047'
AND aapft.name = 'Annual Leave'
AND apate.uom = 'D')
"Annual Leave Availed(As of now)",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Sick Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
AND apate.start_datetime >= ( Trunc(SYSDATE, 'YY') ))
"Sick Leave Count Current Year",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Sick Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
AND apate.start_datetime >= (SELECT Trunc(start_date, 'YY')
FROM per_all_people_f
WHERE 1 = 1
AND asg.person_id = person_id
AND
Trunc(SYSDATE) BETWEEN Trunc(effective_start_date) AND Trunc(
effective_end_date)))
"Sick Leave Availed (as of now)",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Unpaid Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
--and peo.person_number = '0160'
AND apate.uom = 'C'
AND apate.start_datetime >= ( Trunc(SYSDATE, 'YY') ))
"Unpaid Leave Count Current Year",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Unpaid Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
--and peo.person_number = '0160'
AND apate.uom = 'C'
AND apate.start_datetime >= (SELECT Trunc(start_date, 'YY')
FROM per_all_people_f
WHERE 1 = 1
AND asg.person_id = person_id
AND
Trunc(SYSDATE) BETWEEN Trunc(effective_start_date) AND Trunc(
effective_end_date)))
"Unpaid Leave Availed (as of now)",
(SELECT DISTINCT ( prv.result_value * 12 ) Air_Tickets
FROM pay_payroll_actions G,
pay_payroll_rel_actions H,
pay_run_results PR,
pay_run_result_values PRV,
pay_balance_feeds_f PBF,
pay_balance_types_vl pbt,
pay_payroll_assignments payass,
pay_input_values_vl piv,
pay_element_types_vl pet
WHERE G.payroll_action_id = H.payroll_action_id
--AND G.ACTION_TYPE IN ('B','I','Q','R')
AND G.action_status IN ( 'C', 'P' )
AND pbt.balance_type_id = pbf.balance_type_id
AND H.action_status = 'C'
AND pbt.balance_name LIKE 'Gross Earnings'
AND G.date_earned >= ( Trunc(SYSDATE, 'YY') )
AND PBF.input_value_id = PRV.input_value_id
AND PR.run_result_id = PRV.run_result_id
AND PR.payroll_rel_action_id = H.payroll_rel_action_id
AND PBF.balance_type_id = pbt.balance_type_id
AND pbf.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pet.element_name = 'Air Ticket Payment'
--and g.payroll_id = 300000018200440
AND g.legislation_code = 'KW'
AND g.assignment_set_id IS NOT NULL
AND payass.hr_assignment_id IN (SELECT DISTINCT assignment_id
FROM per_all_assignments_m
WHERE person_id = paaf.person_id
AND assignment_type = 'E'
AND SYSDATE BETWEEN
effective_start_date
AND
effective_end_date
AND primary_flag = 'Y'
AND effective_latest_change = 'Y'
)) "Air Ticket - Total",
cs.annual_salary
grosss_salary,
bp.basic_amount
basic_salary,
(nvl(pee.pre_exp,0)+ Trunc(SYSDATE - To_date(papf.start_date, 'YYYY-MM-DD'))) over_all_exp
FROM per_all_people_f papf,
per_all_assignments_m paaf,
per_persons pp,
per_people_legislative_f pplf,
per_religions pr,
per_citizenships PC,
per_legal_employers ple,
hr_all_organization_units hou,
per_location_details_f pldf,
per_location_details_f_tl pldft,
pay_rel_groups_dn payrel,
pay_assigned_payrolls_dn papd,
pay_all_payrolls_f pay,
per_departments PD,
per_grades_f_tl pgft,
per_jobs_f_tl pjft,
hr_all_positions_f_tl hapft,
per_person_types_tl ppt,
per_people_groups ppg,
per_passports pass,
basic_pay bp,
air_elements ae,
cmp_salary cs,
prev_emp_exp pee,
(SELECT pasf.assignment_id,
papf.person_number line_manager_number,
ppnf.display_name line_manager_name
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
per_assignment_supervisors_f pasf
WHERE papf.person_id = pasf.manager_id
AND ppnf.person_id = pasf.manager_id
AND paaf.person_id = pasf.person_id
AND paaf.assignment_id = pasf.assignment_id
AND pasf.manager_type = 'LINE_MANAGER'
------to get line manager
AND ppnf.name_type = 'GLOBAL' ---For english name
AND paaf.effective_latest_change = 'Y'
------to get latest changed records
AND paaf.assignment_type = 'E' -- Employee only
AND Trunc (SYSDATE) BETWEEN
Trunc (papf.effective_start_date)
AND
Trunc (papf.effective_end_date)
AND Trunc (SYSDATE) BETWEEN
Trunc (ppnf.effective_start_date)
AND
Trunc (ppnf.effective_end_date)
AND Trunc (SYSDATE) BETWEEN
Trunc (paaf.effective_start_date)
AND
Trunc (paaf.effective_end_date)
AND Trunc (SYSDATE) BETWEEN
Trunc (pasf.effective_start_date)
AND
Trunc (pasf.effective_end_date))
manager_info,
(SELECT
pprd.PERSON_ID,
EBA.BANK_ACCOUNT_NAME,
EBA.bank_account_num,
EBA.MASKED_BANK_ACCOUNT_NUM,
EBA.BANK_ACCOUNT_NUM_ELECTRONIC,
EBA.IBAN,
BANK.bank_name,
BANK.BANK_NUMBER
FROM PAY_PERSON_PAY_METHODS_F ppmf,
PAY_PAY_RELATIONSHIPS_DN pprd,
CE_ALL_BANKS_V BANK,
CE_ALL_BANK_BRANCHES_V BRANCH,
IBY_EXT_BANK_ACCOUNTS EBA,
fnd_lookups LKP
WHERE ppmf.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
and EBA.BANK_ID = BANK.BANK_PARTY_ID
and ppmf.BANK_ACCOUNT_ID = EBA.EXT_BANK_ACCOUNT_ID(+)
AND EBA.BRANCH_ID = BRANCH.BRANCH_PARTY_ID
AND LKP.LOOKUP_CODE(+) = EBA.BANK_ACCOUNT_TYPE
AND LKP.LOOKUP_TYPE(+) = 'IBY_BANKACCT_TYPES'
--and pprd.PERSON_ID in ('300000030302137','300000033468350')
AND sysdate BETWEEN ppmf.EFFECTIVE_START_DATE AND ppmf.EFFECTIVE_END_DATE
) Bank_Details,
(SELECT Count(papf1.person_number) count_no,
papf_s.person_id
FROM per_all_people_f papf1,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_assignment_supervisors_f pasf,
per_all_people_f papf_s,
per_person_names_f ppnf_s,
per_all_assignments_m paam_s
WHERE papf1.person_id = ppnf.person_id
AND papf1.person_id = paam.person_id
AND papf1.person_id = pasf.person_id
AND paam.assignment_id = pasf.assignment_id
AND papf1.person_id = pasf.person_id
AND pasf.manager_assignment_id = paam_s.assignment_id
AND pasf.manager_id = papf_s.person_id
AND papf_s.person_id = ppnf_s.person_id
AND ppnf.name_type = 'GLOBAL'
AND ppnf_s.name_type = 'GLOBAL'
--AND pasf.person_id = papf.person_id
AND SYSDATE BETWEEN ppnf.effective_start_date AND
ppnf.effective_end_date
AND SYSDATE BETWEEN ppnf_s.effective_start_date AND
ppnf_s.effective_end_date
AND SYSDATE BETWEEN papf1.effective_start_date AND
papf1.effective_end_date
AND SYSDATE BETWEEN papf_s.effective_start_date AND
papf_s.effective_end_date
AND SYSDATE BETWEEN paam_s.effective_start_date AND
paam_s.effective_end_date
AND SYSDATE BETWEEN paam.effective_start_date AND
paam.effective_end_date
GROUP BY papf_s.person_id)direct_reporters
WHERE papf.person_id = paaf.person_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.assignment_status_type = 'ACTIVE'
AND pp.person_id(+) = papf.person_id
AND pplf.person_id(+) = papf.person_id
AND pr.person_id(+) = papf.person_id
AND PC.person_id(+) = papf.person_id
AND pc.citizenship_status(+) = 'A'
AND manager_info.assignment_id(+) = paaf.assignment_id
AND ple.organization_id(+) = paaf.legal_entity_id
AND ple.status(+) = 'A'
AND hou.organization_id(+) = paaf.business_unit_id
AND paaf.location_id = pldf.location_id(+)
AND pldft.LANGUAGE(+) = 'US'
AND pldf.location_details_id = pldft.location_details_id(+)
AND paaf.assignment_id = payrel.assignment_id(+)
AND payrel.group_type(+) = 'A'
AND papd.payroll_term_id = payrel.parent_rel_group_id
AND papd.payroll_id = pay.payroll_id
AND paaf.organization_id = pd.organization_id(+)
AND pgft.grade_id = paaf.grade_id
AND PGFt.LANGUAGE(+) = 'US'
AND pjft.job_id(+) = paaf.job_id
AND PJFt.LANGUAGE(+) = 'US'
AND hapft.position_id(+) = paaf.position_id
AND hapft.LANGUAGE (+)= 'US'
AND ppt.person_type_id(+) = paaf.person_type_id
AND ppt.LANGUAGE(+) = 'US'
AND ppg.people_group_id(+) = paaf.people_group_id
AND papf.person_id = pass.person_id(+)
AND Bank_Details.person_id(+) =(papf.person_id)
AND direct_reporters.person_id(+) = papf.person_id
AND bp.person_id(+) = papf.person_id
AND ae.person_id(+) = papf.person_id
AND cs.person_id = papf.person_id
AND pee.person_id(+) = paaf.person_id
AND Trunc(:p_effective_date) BETWEEN
Trunc(papf.effective_start_date)
AND
Trunc(papf.effective_end_date)
AND Trunc(:p_effective_date) BETWEEN
Trunc(paaf.effective_start_date)
AND
Trunc(paaf.effective_end_date)
AND Trunc(SYSDATE) BETWEEN Nvl(PPLF.effective_start_date, SYSDATE
)AND
Nvl(PPLF.effective_end_date, SYSDATE)
AND Trunc(SYSDATE) BETWEEN Trunc(cs.date_from) AND
Trunc(cs.date_to)
AND ( Coalesce(NULL, :legal_emp) IS NULL
OR ple.organization_id IN ( :legal_emp ) )
AND ( Coalesce(NULL, :assignment_status) IS NULL
OR paaf.assignment_status_type IN ( :assignment_status ) )
AND ( Coalesce(NULL, :department_name) IS NULL
OR pd.name IN ( :department_name ) )
UNION ALL
SELECT DISTINCT papf.person_id,
papf.person_number,
paaf.assignment_number,
(SELECT ppnf.title
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date)) Title,
(SELECT ppnf.full_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Employee_Full_Name,
(SELECT ppnf.first_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
First_Name,
(SELECT ppnf.middle_names
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Middle_Name,
(SELECT ppnf.last_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Family_Name,
(SELECT ppnf.display_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'GLOBAL'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Display_Name,
(SELECT ppnf.full_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Employee_Name_Arabic,
(SELECT ppnf.first_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Arabic_First_Name
,
(SELECT ppnf.middle_names
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Arabic_Middle_Name,
(SELECT ppnf.last_name
FROM per_person_names_f ppnf
WHERE ppnf.name_type = 'KW'
AND ppnf.person_id = papf.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnf.effective_start_date) AND
Trunc(ppnf.effective_end_date))
Arabic_Family_Name,
To_char(Trunc(papf.start_date), 'DD-Mon-YYYY')
Date_of_Joining,
To_char(Trunc(pp.date_of_birth), 'DD-Mon-YYYY')
Date_of_Birth,
Trunc(( SYSDATE - To_date(pp.date_of_birth, 'YYYY-MM-DD'
) ) /
365.25) Age,
Decode (pplf.sex, 'M', 'Male',
'F', 'Female',
pplf.sex)
Gender,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE pplf.marital_status = lookup_code
AND lookup_type = 'MAR_STATUS'
AND LANGUAGE = 'US')
Marital_Status,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE pr.religion = lookup_code
AND lookup_type = 'PER_RELIGION'
AND LANGUAGE = 'US')
Religion,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE PC.legislation_code = lookup_code
AND lookup_type = 'NATIONALITY'
AND LANGUAGE = 'US')
Nationality,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'H1')
Personal_Phone_Number,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'MOBILE')
Personal_Mobile_Number,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'W1')
Work_Phone_Number,
(SELECT pph.phone_number
FROM per_phones pph
WHERE pph.person_id = papf.person_id
AND Pph.phone_type = 'WM')
Work_Mobile_Number,
manager_info.line_manager_number,
manager_info.line_manager_name,
ple.name
Legal_Employer_Name,
(SELECT pea.email_address
FROM per_email_addresses pea
WHERE pea.email_type = 'H1'
AND pea.person_id = papf.person_id)
Personal_Email_addreess,
(SELECT pea.email_address
FROM per_email_addresses pea
WHERE pea.email_type = 'W1'
AND pea.person_id = papf.person_id)
Company_Email_addreess,
hou.name
Business_Unit_Name,
pldft.location_name,
pay.payroll_name,
pd.name
Department,
paaf.ass_attribute1
sector,
pgft.name
grade_name,
pjft.name
Job,
hapft.name
Position,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE paaf.employee_category = lookup_code
AND lookup_type = 'EMPLOYEE_CATG'
AND LANGUAGE = 'US')
Employee_Category,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE paaf.employment_category = lookup_code
AND lookup_type = 'EMP_CAT'
AND LANGUAGE = 'US')
Assignment_Category,
ppt.user_person_type
person_type,
paaf.assignment_status_type
assignment_status,
paaf.assignment_number
primary_assignment_number,
--Trunc(((MONTHS_BETWEEN(SYSDATE,papf.start_date) /12) - Trunc(MONTHS_BETWEEN(SYSDATE,papf.start_date) /12))*12)) years
Trunc(( SYSDATE - To_date(papf.start_date, 'YYYY-MM-DD')
))
Years_of_Service_with_company,
--(MONTHS_BETWEEN(SYSDATE,papf.start_date) /12) years
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE pplf.highest_education_level = lookup_code
AND lookup_type = 'ORA_PER_HIGHEST_EDUCATION_LEVE'
AND LANGUAGE = 'US')highest_education_level,
ppg.segment1,
ppg.segment2,
ppg.segment3,
ppg.segment4,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE PC.legislation_code = lookup_code
AND lookup_type = 'NATIONALITY'
AND LANGUAGE = 'US')
citizenship,
(SELECT meaning
FROM fnd_lookup_values_tl
WHERE PC.citizenship_status = lookup_code
AND lookup_type = 'PER_CITIZENSHIP_STATUS'
AND LANGUAGE = 'US')
citizenship_status,
To_char(pc.date_from, 'DD-Mon-YYYY')
citizenship_From_Date,
pass.passport_number,
To_char(pass.expiration_date, 'DD-Mon-YYYY')
paassport_expiry_date,
Trunc(SYSDATE - To_date(papf.start_date, 'YYYY-MM-DD'))
length_of_Service_with_company,
Bank_Details.bank_name,
Bank_Details.bank_account_num,
Bank_Details.iban,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'S'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) SPOUSE,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'C'
AND PCR.cont_attribute1 = 'Child1'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) Child1,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'C'
AND PCR.cont_attribute1 = 'Child2'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) Child2,
(SELECT ppnfc.full_name
FROM per_person_names_f ppnfc,
per_all_people_f papfc,
per_contact_relships_f pcr
WHERE pcr.contact_person_id = papfc.person_id
AND pcr.person_id = papf.person_id
AND ppnfc.person_id = papfc.person_id
AND pcr.contact_type = 'C'
AND PCR.cont_attribute1 = 'Child3'
AND ppnfc.name_type = 'GLOBAL'
AND Trunc(SYSDATE) BETWEEN
Trunc(ppnfc.effective_start_date) AND
Trunc(ppnfc.effective_end_date)) Child3,
direct_reporters.count_no
direct_reporters,
ae.destination
Air_Ticket_Destination,
ae.class_of_travel
Air_Ticket_Class_Of_Travel,
ae.frequency_of_travel
Air_Ticket_Frequency_Of_Travel,
ae.dependents_eligible
Air_Ticket_Dependents_Eligible,
ae.number_of_children_eligible
Air_Ticket_Number_of_Children_Eligible,
ae.paying_month
Air_Ticket_Paying_Month,
(SELECT national_identifier_number
FROM per_national_identifiers
WHERE person_id = papf.person_id
AND national_identifier_type = 'CI')
Civil_Id,
(SELECT expiration_date
FROM per_national_identifiers
WHERE person_id = papf.person_id
AND national_identifier_type = 'CI')
Civil_Id_EXPIRATION_DATE,
paaf.probation_period
probation_start_date,
To_char(paaf.date_probation_end, 'DD-Mon-YYYY')
probation_end_date,
Trunc(To_date(pps.actual_termination_date, 'YYYY-MM-DD')
-
To_date(papf.start_date, 'YYYY-MM-DD'))
length_of_service,
part.action_reason
leaving_reason,
pat.action_name
termination_action,
To_char(pps.actual_termination_date, 'DD-Mon-YYYY')
last_working_date,
To_char(pps.notified_termination_date, 'DD-Mon-YYYY')
notification_on_date,
pps.worker_comments
Employer_Comments,
Decode(pps.rehire_recommendation, 'Y', 'Yes',
'N', 'No',
'Not Specified')
REHIRE_RECOMMENDATION,
(SELECT DISTINCT Round(SUM(acrl1.end_bal), 2)
plan_balance
FROM per_all_assignments_m paam,
per_all_people_f papf2,
anc_per_plan_enrollment appe,
anc_absence_plans_f_tl aapft,
anc_per_accrual_entries acrl1
WHERE paam.person_id = papf2.person_id
AND appe.person_id = papf2.person_id
AND aapft.absence_plan_id = appe.plan_id
--AND paam.assignment_status_type = 'ACTIVE'
AND aapft.name = 'Annual Leave'
AND appe.work_term_asg_id(+) =
paam.work_terms_assignment_id
AND acrl1.person_id = papf2.person_id
AND acrl1.work_term_asg_id(+) =
paam.work_terms_assignment_id
AND acrl1.plan_id = ( appe.plan_id )
AND acrl1.accrual_period = (SELECT
Max(acrl2.accrual_period)
FROM
anc_per_accrual_entries acrl2
WHERE
person_id = papf2.person_id
AND acrl2.plan_id = appe.plan_id)
AND aapft.LANGUAGE = 'US'
AND Trunc(SYSDATE) BETWEEN
paam.effective_start_date
AND
paam.effective_end_date
AND Trunc(SYSDATE) BETWEEN
paam.effective_start_date
AND
paam.effective_end_date
AND Trunc(SYSDATE) BETWEEN
aapft.effective_start_date
AND
aapft.effective_end_date
AND paam.primary_flag = 'Y'
AND paam.assignment_type = 'E'
AND papf.person_id = papf2.person_id)
annual_leave_balance,
(SELECT DISTINCT SUM(apate.leave_duration)
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_person_names_f per_name,
per_periods_of_service ser,
per_person_types_tl per_typ,
per_legal_employers ple,
anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf,
anc_per_accrual_entries acc_ent,
anc_per_abs_type_entries apate
WHERE asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND acc_ent.person_id = peo.person_id
AND asg.period_of_service_id =
ser.period_of_service_id
AND acc_ent.prd_of_svc_id =
asg.period_of_service_id
AND asg.person_type_id = per_typ.person_type_id
AND asg.legal_entity_id = ple.organization_id
AND aapft.absence_plan_id = aapf.absence_plan_id
AND acc_ent.plan_id = aapf.absence_plan_id
AND acc_ent.accrual_period =
(SELECT
Max (acc_ent1.accrual_period)
FROM
anc_per_accrual_entries acc_ent1
WHERE
acc_ent1.accrual_period <=
To_date (Substr (Trunc (SYSDATE), 1
, 10))
AND acc_ent1.person_id =
acc_ent.person_id
AND
acc_ent1.prd_of_svc_id =
acc_ent.prd_of_svc_id
AND
acc_ent1.plan_id =
acc_ent.plan_id)
AND asg.assignment_id = apate.assignment_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ( 'E' )--, 'C', 'N', 'P')
--AND asg.assignment_status_type = 'ACTIVE'
AND per_name.name_type = 'GLOBAL'
AND ple.status = 'A'
AND per_typ.LANGUAGE = Userenv ('LANG')
AND aapf.plan_status = 'A'
AND aapft.LANGUAGE = 'US'
AND acc_ent.end_bal <> 0
AND Trunc (ser.actual_termination_date) BETWEEN
peo.effective_start_date AND peo.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
per_name.effective_start_date AND per_name.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
ple.effective_start_date AND ple.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
aapf.effective_start_date AND aapf.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
aapft.effective_start_date AND aapft.effective_end_date
AND apate.start_datetime >= ( Trunc(SYSDATE, 'YY') )
AND aapft.name = 'Annual Leave'
AND paaf.person_id = peo.person_id
AND apate.uom = 'D')
Annual_Leave_availed_in_current_year,
(SELECT DISTINCT SUM(apate.leave_duration)
FROM per_all_people_f peo,
per_all_assignments_f asg,
per_person_names_f per_name,
per_periods_of_service ser,
per_person_types_tl per_typ,
per_legal_employers ple,
anc_absence_plans_f_tl aapft,
anc_absence_plans_f aapf,
anc_per_accrual_entries acc_ent,
anc_per_abs_type_entries apate
WHERE asg.person_id = peo.person_id
AND ser.person_id = peo.person_id
AND per_name.person_id = peo.person_id
AND acc_ent.person_id = peo.person_id
AND asg.period_of_service_id = ser.period_of_service_id
AND acc_ent.prd_of_svc_id = asg.period_of_service_id
AND asg.person_type_id = per_typ.person_type_id
AND asg.legal_entity_id = ple.organization_id
AND aapft.absence_plan_id = aapf.absence_plan_id
AND acc_ent.plan_id = aapf.absence_plan_id
AND acc_ent.accrual_period = (SELECT Max (acc_ent1.accrual_period)
FROM anc_per_accrual_entries acc_ent1
WHERE acc_ent1.accrual_period <=
To_date (Substr (Trunc (SYSDATE), 1
, 10))
AND acc_ent1.person_id =
acc_ent.person_id
AND
acc_ent1.prd_of_svc_id = acc_ent.prd_of_svc_id
AND acc_ent1.plan_id =
acc_ent.plan_id)
AND asg.assignment_id = apate.assignment_id
AND asg.primary_flag = 'Y'
AND asg.assignment_type IN ( 'E', 'C', 'N', 'P' )
--AND asg.assignment_status_type = 'ACTIVE'
AND per_name.name_type = 'GLOBAL'
AND ple.status = 'A'
AND per_typ.LANGUAGE = Userenv ('LANG')
AND aapf.plan_status = 'A'
AND aapft.LANGUAGE = 'US'
AND acc_ent.end_bal <> 0
AND Trunc (ser.actual_termination_date) BETWEEN
peo.effective_start_date AND peo.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
asg.effective_start_date AND asg.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
per_name.effective_start_date AND per_name.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
ple.effective_start_date AND ple.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
aapf.effective_start_date AND aapf.effective_end_date
AND Trunc (ser.actual_termination_date) BETWEEN
aapft.effective_start_date AND aapft.effective_end_date
AND apate.start_datetime >= (SELECT Trunc(asg1.start_date, 'YY')
FROM per_all_people_f asg1
WHERE 1 = 1
AND asg.person_id = asg1.person_id
AND Trunc(SYSDATE) BETWEEN
Trunc(asg1.effective_start_date)
AND
Trunc(asg1.effective_end_date))
AND peo.person_id = papf.person_id
--and peo.person_number ='0047'
AND aapft.name = 'Annual Leave'
AND apate.uom = 'D')
"Annual Leave Availed(As of now)",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Sick Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
AND apate.start_datetime >= ( Trunc(SYSDATE, 'YY') ))
"Sick Leave Count Current Year",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Sick Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
AND apate.start_datetime >= (SELECT Trunc(start_date, 'YY')
FROM per_all_people_f
WHERE 1 = 1
AND asg.person_id = person_id
AND
Trunc(SYSDATE) BETWEEN Trunc(effective_start_date) AND Trunc(
effective_end_date)))
"Sick Leave Availed (as of now)",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Unpaid Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
--and peo.person_number = '0160'
AND apate.uom = 'C'
AND apate.start_datetime >= ( Trunc(SYSDATE, 'YY') ))
"Unpaid Leave Count Current Year",
(SELECT SUM(apate.leave_duration)
FROM anc_absence_types_f_tl aapft,
anc_absence_types_f aapf,
per_all_people_f peo,
per_all_assignments_f asg,
anc_per_abs_type_entries apate
WHERE aapft.absence_type_id = aapf.absence_type_id
AND apate.absence_type_id = aapf.absence_type_id
AND aapft.name = 'Unpaid Leave'
AND aapf.status = 'A'
AND aapft.LANGUAGE = 'US'
AND asg.assignment_id = apate.assignment_id
AND asg.person_id = peo.person_id
AND Trunc (SYSDATE) BETWEEN aapf.effective_start_date AND
aapf.effective_end_date
AND Trunc (SYSDATE) BETWEEN aapft.effective_start_date AND
aapft.effective_end_date
AND Trunc (SYSDATE) BETWEEN peo.effective_start_date AND
peo.effective_end_date
AND Trunc (SYSDATE) BETWEEN asg.effective_start_date AND
asg.effective_end_date
AND papf.person_id = peo.person_id
AND apate.uom = 'C'
AND apate.start_datetime >= (SELECT Trunc(start_date, 'YY')
FROM per_all_people_f
WHERE 1 = 1
AND asg.person_id = person_id
AND
Trunc(SYSDATE) BETWEEN Trunc(effective_start_date) AND Trunc(
effective_end_date)))
"Unpaid Leave Availed (as of now)",
(SELECT DISTINCT ( prv.result_value * 12 ) Air_Tickets
FROM pay_payroll_actions G,
pay_payroll_rel_actions H,
pay_run_results PR,
pay_run_result_values PRV,
pay_balance_feeds_f PBF,
pay_balance_types_vl pbt,
pay_payroll_assignments payass,
pay_input_values_vl piv,
pay_element_types_vl pet
WHERE G.payroll_action_id = H.payroll_action_id
--AND G.ACTION_TYPE IN ('B','I','Q','R')
AND G.action_status IN ( 'C', 'P' )
AND pbt.balance_type_id = pbf.balance_type_id
AND H.action_status = 'C'
AND pbt.balance_name LIKE 'Gross Earnings'
AND G.date_earned >= ( Trunc(SYSDATE, 'YY') )
AND PBF.input_value_id = PRV.input_value_id
AND PR.run_result_id = PRV.run_result_id
AND PR.payroll_rel_action_id = H.payroll_rel_action_id
AND PBF.balance_type_id = pbt.balance_type_id
AND pbf.input_value_id = piv.input_value_id
AND piv.element_type_id = pet.element_type_id
AND pet.element_name = 'Air Ticket Payment'
--and g.payroll_id = 300000018200440
AND g.legislation_code = 'KW'
AND g.assignment_set_id IS NOT NULL
AND payass.hr_assignment_id IN (SELECT DISTINCT assignment_id
FROM per_all_assignments_m
WHERE person_id = paaf.person_id
AND assignment_type = 'E'
AND SYSDATE BETWEEN
effective_start_date
AND
effective_end_date
AND primary_flag = 'Y'
AND effective_latest_change = 'Y'
)) "Air Ticket - Total",
cs.annual_salary
grosss_salary,
bp.basic_amount
basic_salary,
(nvl(pee.pre_exp,0)+ Trunc(SYSDATE - To_date(papf.start_date, 'YYYY-MM-DD'))) over_all_exp
FROM per_all_people_f papf,
per_all_assignments_m paaf,
per_persons pp,
per_people_legislative_f pplf,
per_religions pr,
per_citizenships PC,
per_legal_employers ple,
hr_all_organization_units hou,
per_location_details_f pldf,
per_location_details_f_tl pldft,
pay_rel_groups_dn payrel,
pay_assigned_payrolls_dn papd,
pay_all_payrolls_f pay,
per_departments PD,
per_grades_f_tl pgft,
per_jobs_f_tl pjft,
hr_all_positions_f_tl hapft,
per_person_types_tl ppt,
per_people_groups ppg,
per_passports pass,
basic_pay bp,
air_elements ae,
prev_emp_exp pee,
per_periods_of_service pps,
per_action_occurrences pao,
per_action_reasons_b parb,
per_action_reasons_tl part,
per_actions_b pab,
per_actions_tl pat,
cmp_salary cs,
(SELECT pasf.assignment_id,
papf.person_number line_manager_number,
ppnf.display_name line_manager_name
FROM per_all_people_f papf,
per_person_names_f ppnf,
per_all_assignments_f paaf,
per_assignment_supervisors_f pasf
WHERE papf.person_id = pasf.manager_id
AND ppnf.person_id = pasf.manager_id
AND paaf.person_id = pasf.person_id
AND paaf.assignment_id = pasf.assignment_id
AND pasf.manager_type = 'LINE_MANAGER'
------to get line manager
AND ppnf.name_type = 'GLOBAL' ---For english name
AND paaf.effective_latest_change = 'Y'
------to get latest changed records
AND paaf.assignment_type = 'E' -- Employee only
AND Trunc(:p_effective_date) BETWEEN
Trunc (papf.effective_start_date)
AND Trunc
(
papf.effective_end_date)
AND Trunc (:p_effective_date) BETWEEN
Trunc (ppnf.effective_start_date)
AND
Trunc (
ppnf.effective_end_date)
AND Trunc (:p_effective_date) BETWEEN
Trunc (paaf.effective_start_date)
AND
Trunc (
paaf.effective_end_date)
AND Trunc (:p_effective_date) BETWEEN
Trunc (pasf.effective_start_date)
AND Trunc (
pasf.effective_end_date))
manager_info,
(SELECT
pprd.PERSON_ID,
EBA.BANK_ACCOUNT_NAME,
EBA.bank_account_num,
EBA.MASKED_BANK_ACCOUNT_NUM,
EBA.BANK_ACCOUNT_NUM_ELECTRONIC,
EBA.IBAN,
BANK.bank_name,
BANK.BANK_NUMBER
FROM PAY_PERSON_PAY_METHODS_F ppmf,
PAY_PAY_RELATIONSHIPS_DN pprd,
CE_ALL_BANKS_V BANK,
CE_ALL_BANK_BRANCHES_V BRANCH,
IBY_EXT_BANK_ACCOUNTS EBA,
fnd_lookups LKP
WHERE ppmf.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
and EBA.BANK_ID = BANK.BANK_PARTY_ID
and ppmf.BANK_ACCOUNT_ID = EBA.EXT_BANK_ACCOUNT_ID(+)
AND EBA.BRANCH_ID = BRANCH.BRANCH_PARTY_ID
AND LKP.LOOKUP_CODE(+) = EBA.BANK_ACCOUNT_TYPE
AND LKP.LOOKUP_TYPE(+) = 'IBY_BANKACCT_TYPES'
--and pprd.PERSON_ID in ('300000030302137','300000033468350')
AND sysdate BETWEEN ppmf.EFFECTIVE_START_DATE AND ppmf.EFFECTIVE_END_DATE
) Bank_Details,
(SELECT Count(papf1.person_number) count_no,
papf_s.person_id
FROM per_all_people_f papf1,
per_person_names_f ppnf,
per_all_assignments_m paam,
per_assignment_supervisors_f pasf,
per_all_people_f papf_s,
per_person_names_f ppnf_s,
per_all_assignments_m paam_s
WHERE papf1.person_id = ppnf.person_id
AND papf1.person_id = paam.person_id
AND papf1.person_id = pasf.person_id
AND paam.assignment_id = pasf.assignment_id
AND papf1.person_id = pasf.person_id
AND pasf.manager_assignment_id = paam_s.assignment_id
AND pasf.manager_id = papf_s.person_id
AND papf_s.person_id = ppnf_s.person_id
AND ppnf.name_type = 'GLOBAL'
AND ppnf_s.name_type = 'GLOBAL'
--AND pasf.person_id = papf.person_id
AND Trunc(:p_effective_date) BETWEEN
ppnf.effective_start_date AND ppnf.effective_end_date
AND Trunc(:p_effective_date) BETWEEN
ppnf_s.effective_start_date AND
ppnf_s.effective_end_date
AND Trunc(:p_effective_date) BETWEEN
papf1.effective_start_date AND
papf1.effective_end_date
AND Trunc(:p_effective_date) BETWEEN
papf_s.effective_start_date AND
papf_s.effective_end_date
AND Trunc(:p_effective_date) BETWEEN
paam_s.effective_start_date AND
paam_s.effective_end_date
AND Trunc(:p_effective_date) BETWEEN
paam.effective_start_date AND paam.effective_end_date
GROUP BY papf_s.person_id)direct_reporters
WHERE papf.person_id = paaf.person_id
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.assignment_status_type = 'INACTIVE'
AND pp.person_id(+) = papf.person_id
AND pplf.person_id(+) = papf.person_id
AND pr.person_id(+) = papf.person_id
AND PC.person_id(+) = papf.person_id
AND pc.citizenship_status(+) = 'A'
AND manager_info.assignment_id(+) = paaf.assignment_id
AND ple.organization_id(+) = paaf.legal_entity_id
AND ple.status(+) = 'A'
AND hou.organization_id(+) = paaf.business_unit_id
AND paaf.location_id = pldf.location_id(+)
AND pldft.LANGUAGE(+) = 'US'
AND pldf.location_details_id = pldft.location_details_id(+)
AND paaf.assignment_id = payrel.assignment_id(+)
AND payrel.group_type(+) = 'A'
AND papd.payroll_term_id = payrel.parent_rel_group_id
AND papd.payroll_id = pay.payroll_id
AND paaf.organization_id = pd.organization_id(+)
AND pgft.grade_id = paaf.grade_id
AND PGFt.LANGUAGE(+) = 'US'
AND pjft.job_id(+) = paaf.job_id
AND PJFt.LANGUAGE (+)= 'US'
AND hapft.position_id(+) = paaf.position_id
AND hapft.LANGUAGE(+) = 'US'
AND ppt.person_type_id(+) = paaf.person_type_id
AND ppt.LANGUAGE(+) = 'US'
AND ppg.people_group_id(+) = paaf.people_group_id
AND papf.person_id = pass.person_id(+)
AND Bank_Details.person_id(+) =papf.person_id
AND direct_reporters.person_id(+) = papf.person_id
AND bp.person_id(+) = papf.person_id
AND ae.person_id(+) = papf.person_id
AND pee.person_id(+) = paaf.person_id
AND pps.person_id = papf.person_id
AND paaf.period_of_service_id = pps.period_of_service_id
AND pps.actual_termination_date IS NOT NULL
AND cs.person_id = papf.person_id
--and papf.person_number = '0154'
AND pao.action_occurrence_id = pps.action_occurrence_id
AND pao.parent_entity_key_id = papf.person_id
AND parb.action_reason_id = pao.action_reason_id
AND part.action_reason_id = parb.action_reason_id
AND pao.action_id = pab.action_id
AND pat.action_id = pab.action_id
AND part.LANGUAGE = 'US'
AND pat.source_lang = 'US'
AND Trunc(:p_effective_date) BETWEEN
Trunc(papf.effective_start_date)
AND
Trunc(papf.effective_end_date)
AND Trunc(:p_effective_date) BETWEEN
Trunc(paaf.effective_start_date)
AND
Trunc(paaf.effective_end_date)
AND Trunc(:p_effective_date) BETWEEN
Nvl(PPLF.effective_start_date, SYSDATE)AND
Nvl(
PPLF.effective_end_date, SYSDATE)
AND Trunc(:p_effective_date) BETWEEN
Trunc(cs.date_from) AND Trunc(cs.date_to)
AND ( Coalesce(NULL, :legal_emp) IS NULL
OR ple.organization_id IN ( :legal_emp ) )
AND ( Coalesce(NULL, :assignment_status) IS NULL
OR paaf.assignment_status_type IN ( :assignment_status ) )
AND ( Coalesce(NULL, :department_name) IS NULL
OR pd.name IN ( :department_name ) )
-- AND papf.person_number = '0122'
--where 1=1
)
ORDER BY 2
No comments:
Post a Comment