2) Click on worklist
3)Click on Administration
4)Click on Task Configuration
Rule 1: To send the approval to the project manager
7)Make the comment mandatory for Rejection.
/* Formatted on 2019/11/04 16:35 (Formatter Plus v4.8.8) */
WITH tru AS
(SELECT org_unit.organization_id, org_unit_tl.NAME
FROM hr_org_unit_classifications_f org_unit_cls,
hr_all_organization_units_f org_unit,
hr_organization_units_f_tl org_unit_tl
WHERE org_unit.organization_id = org_unit_cls.organization_id
AND org_unit.organization_id = org_unit_tl.organization_id
AND org_unit.effective_start_date
BETWEEN org_unit_cls.effective_start_date
AND org_unit_cls.effective_end_date
AND org_unit_tl.effective_start_date = org_unit.effective_start_date
AND org_unit_tl.effective_end_date = org_unit.effective_end_date
AND org_unit_tl.LANGUAGE = USERENV ('LANG')
AND org_unit_cls.classification_code = 'HCM_TRU'),
component_details AS
(SELECT m_comp_dtl.source_system_owner, m_comp_dtl.source_system_id,
comp_dtl.dir_comp_detail_id, comp_dtl.dir_card_comp_id,
comp_dtl.dir_information_category
FROM hrc_integration_key_map m_comp_dtl,
pay_dir_comp_details_f comp_dtl
WHERE m_comp_dtl.object_name = 'CalculationComponentDetails'
AND m_comp_dtl.surrogate_id = comp_dtl.dir_comp_detail_id
AND TRUNC (SYSDATE) BETWEEN comp_dtl.effective_start_date
AND comp_dtl.effective_end_date)
SELECT DISTINCT p.person_number, pay_rel.payroll_relationship_number,
rel_grp.assignment_number, term.assignment_number term_number,
card_def.display_name, card_comp_def.component_name,
ldg.NAME ldg, tru.NAME tru,
m_card.source_system_owner card_source_system_owner,
m_card.source_system_id card_source_system_id,
TO_CHAR (card.dir_card_id) dir_card_id,
TO_CHAR (card.effective_start_date,
'DD/MM/YYYY'
) effective_start_date,
TO_CHAR (card.effective_end_date,
'DD/MM/YYYY'
) effective_end_date,
m_card_comp.source_system_owner card_comp_source_system_owner,
m_card_comp.source_system_id card_comp_source_system_id,
TO_CHAR (card_comp.dir_card_comp_id) dir_card_comp_id,
TO_CHAR
(card_comp.parent_dir_card_comp_id)
parent_dir_card_comp_id,
dtl.source_system_owner comp_dtl_source_system_owner,
dtl.source_system_id comp_dtl_source_system_id,
TO_CHAR (dtl.dir_comp_detail_id) dir_comp_detail_id,
dtl.dir_information_category, TO_CHAR(term.relationship_group_id) relationship_group_id
,rel_grp.end_date rel_grp_end_date
,pay_rel.end_date pay_rel_end_date
,term.end_date term_end_date,
pdcdf1.DIR_INFORMATION_CHAR1,
pdcdf1.DIR_INFORMATION_CHAR2,
pdcdf1.DIR_INFORMATION_CHAR3,
pdcdf1.DIR_INFORMATION_CHAR4,
pdcdf1.DIR_INFORMATION_CHAR5,
pdcdf1.DIR_INFORMATION_CHAR6,
pdcdf1.DIR_INFORMATION_CHAR7,
pdcdf1.DIR_INFORMATION_CHAR8,
pdcdf1.DIR_INFORMATION_CHAR9,
pdcdf1.DIR_INFORMATION_CHAR10,
pdcdf1.DIR_INFORMATION_CHAR11,
pdcdf1.DIR_INFORMATION_CHAR12,
pdcdf1.DIR_INFORMATION_CHAR13,
pdcdf1.DIR_INFORMATION_CHAR14,
pdcdf1.DIR_INFORMATION_CHAR15,
pdcdf1.DIR_INFORMATION_CHAR16,
pdcdf1.DIR_INFORMATION_NUMBER1,
pdcdf1.DIR_INFORMATION_NUMBER2,
pdcdf1.DIR_INFORMATION_NUMBER3,
pdcdf1.DIR_INFORMATION_NUMBER4,
pdcdf1.DIR_INFORMATION_NUMBER5,
pdcdf1.DIR_INFORMATION_NUMBER6,
pdcdf1.DIR_INFORMATION_NUMBER7,
pdcdf1.DIR_INFORMATION_DATE1,
pdcdf1.DIR_INFORMATION_DATE2,
pdcdf1.DIR_INFORMATION_DATE3,
pdcdf1.DIR_INFORMATION_DATE4,
pdcdf1.DIR_INFORMATION_DATE5,
pdcdf1.DIR_INFORMATION_DATE6,
pdcdf1.DIR_INFORMATION_DATE7,
pdcdf1.DIR_INFORMATION_DATE8,
pdcdf1.DIR_INFORMATION_DATE9,
pdcdf1.DIR_INFORMATION_DATE10,
pdcdf1.DIR_INFORMATION_DATE11,
pdcdf1.DIR_INFORMATION_DATE12,
pdcdf1.DIR_INFORMATION_DATE13,
pdcdf1.DIR_INFORMATION_DATE14,
pdcdf1.STATUS,
pdcdf1.EFFECTIVE_START_DATE as "Comp Details Start Date",
pdcdf1.EFFECTIVE_END_DATE as "Comp Details End Date"
FROM pay_dir_cards_f card,
pay_dir_card_definitions_tl card_def,
per_legislative_data_groups_tl ldg,
pay_dir_card_components_f card_comp,
pay_dir_card_comp_defs_tl card_comp_def,
hrc_integration_key_map m_card,
hrc_integration_key_map m_card_comp,
pay_pay_relationships_dn pay_rel,
pay_rel_groups_dn rel_grp,
pay_rel_groups_dn term,
per_all_people_f p,
pay_dir_rep_cards_f rep_card,
tru,
component_details dtl,
pay_dir_comp_details_f pdcdf1
WHERE card_comp.dir_card_id = card.dir_card_id
AND card_def.LANGUAGE = 'US'
AND card.dir_card_definition_id = card_def.dir_card_definition_id
AND card_comp_def.dir_card_comp_def_id =
card_comp.dir_card_comp_def_id
AND card_comp_def.LANGUAGE = 'US'
AND card_def.display_name = :p_card_definition_name
-- ('Pensions Automatic Enrolment', 'Statutory Deductions')
AND m_card.object_name = 'CalculationCard'
AND m_card.surrogate_id = card.dir_card_id
AND m_card_comp.object_name = 'CalculationCardComponents'
AND m_card_comp.surrogate_id = card_comp.dir_card_comp_id
AND ldg.legislative_data_group_id = card.legislative_data_group_id
AND ldg.LANGUAGE = 'US'
AND pay_rel.payroll_relationship_id = card.payroll_relationship_id
AND TRUNC (SYSDATE) BETWEEN pay_rel.start_date AND pay_rel.end_date
AND rel_grp.payroll_relationship_id =
pay_rel.payroll_relationship_id
AND rel_grp.group_type = 'A'
AND TRUNC (SYSDATE) BETWEEN rel_grp.start_date AND rel_grp.end_date
AND rel_grp.payroll_relationship_id = term.payroll_relationship_id
AND term.group_type = 'T'
AND rel_grp.term_id = term.term_id
AND TRUNC (SYSDATE) BETWEEN term.start_date AND term.end_date
AND TRUNC (SYSDATE) BETWEEN card.effective_start_date
AND card.effective_end_date
AND pay_rel.person_id = p.person_id
AND p.object_version_number =
(SELECT MAX (p2.object_version_number)
FROM per_all_people_f p2
WHERE p.person_id = p2.person_id
AND p.effective_start_date = p2.effective_start_date)
AND card_comp.dir_card_comp_id = dtl.dir_card_comp_id(+)
AND card_comp.dir_card_comp_id = pdcdf1.dir_card_comp_id(+)
AND card.dir_card_id = rep_card.dir_card_id(+)
AND rep_card.tax_unit_id = tru.organization_id(+)
AND p.person_number = NVL (:p_person_number, p.person_number)
ORDER BY p.person_number,
rel_grp.assignment_number,
card_def.display_name,
card_comp_def.component_name
SELECT distinct * FROM(
SELECT papf.person_id,
papf.person_number,
ppnf.display_name
employee_name,
pasf_Sup.manager_name
manager_name,
pjft.name
job_name,
pgft.name
grade_name,
--abt.absence_type_id,
abt.name
absence_type,
(SELECT DISTINCT( pd.name )
FROM per_departments pd
WHERE pd.organization_id = paam.organization_id
AND pd.effective_start_date =
(SELECT Max(pd1.effective_start_date)
FROM per_departments pd1
WHERE
pd1.organization_id = pd.organization_id
))
department_name,
paam.ass_attribute2 sector,
paam.ass_attribute1
unit,
To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')
start_date,
To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')
end_date,
apae.duration,
apae.start_date order_by_date,
apae.attribute1 Delegated_Person
FROM anc_per_abs_entries apae,
per_periods_of_service pps,
anc_absence_types_vl abt,
per_all_people_f papf,
per_person_names_f ppnf,
(SELECT ppnf2.full_name manager_name,
pasf.person_id
FROM per_assignment_supervisors_f pasf,
per_person_names_f ppnf2
WHERE pasf.manager_id = ppnf2.person_id
AND pasf.manager_type = 'LINE_MANAGER'
AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND
pasf.effective_end_date
AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND
ppnf2.effective_end_date
AND ppnf2.name_type = 'GLOBAL') pasf_Sup,
per_all_assignments_m paam,
per_jobs_f_vl pjft,
per_grades_f_vl pgft,
hr_organization_units bu,
hr_organization_units le,
per_asg_responsibilities resp
--hr_organization_units dept
WHERE apae.period_of_service_id = pps.period_of_service_id
AND apae.absence_type_id = abt.absence_type_id
AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'
AND apae.APPROVAL_STATUS_CD = 'APPROVED'
AND abt.name = 'Tentative Leave Plan'
AND pps.person_id = papf.person_id
AND papf.person_id = ppnf.person_id
AND papf.person_id = pasf_Sup.person_id(+)
AND papf.person_id = paam.person_id(+)
AND paam.primary_assignment_flag(+) = 'Y'
--AND paam.assignment_type = 'E'
--AND paam.effective_latest_change = 'Y'
AND paam.job_id = pjft.job_id (+)
AND paam.grade_id = pgft.grade_id (+)
AND paam.business_unit_id = bu.organization_id
AND paam.legal_entity_id = le.organization_id
AND resp.person_id = HRC_SESSION_UTIL.get_user_personid
AND resp.responsibility_type ='HR_REP'
AND resp.status = 'Active'
AND resp.legal_entity_id = paam.legal_entity_id
--AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID
--AND resp.organization_id =paam.organization_id
AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))
AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND
pjft.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND
pgft.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND
paam.effective_end_date
AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND
abt.effective_end_date
AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND
ppnf.effective_end_date
AND ppnf.name_type = 'GLOBAL'
--AND papf.person_number IN ( '0015', '0033' )
AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)
UNION
SELECT papf.person_id,
papf.person_number,
ppnf.display_name
employee_name,
pasf_Sup.manager_name
manager_name,
pjft.name
job_name,
pgft.name
grade_name,
--abt.absence_type_id,
abt.name
absence_type,
(SELECT DISTINCT( pd.name )
FROM per_departments pd
WHERE pd.organization_id = paam.organization_id
AND pd.effective_start_date =
(SELECT Max(pd1.effective_start_date)
FROM per_departments pd1
WHERE
pd1.organization_id = pd.organization_id))
department_name,
paam.ass_attribute2 sector,
paam.ass_attribute1
unit,
To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')
start_date,
To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')
end_date,
apae.duration,
apae.start_date order_by_date,
apae.attribute1 Delegated_Person
FROM anc_per_abs_entries apae,
per_periods_of_service pps,
anc_absence_types_vl abt,
per_all_people_f papf,
per_person_names_f ppnf,
(SELECT ppnf2.full_name manager_name,
pasf.person_id
FROM per_assignment_supervisors_f pasf,
per_person_names_f ppnf2
WHERE pasf.manager_id = ppnf2.person_id
AND pasf.manager_type = 'LINE_MANAGER'
AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND
pasf.effective_end_date
AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND
ppnf2.effective_end_date
AND ppnf2.name_type = 'GLOBAL') pasf_Sup,
per_all_assignments_m paam,
per_jobs_f_vl pjft,
per_grades_f_vl pgft,
hr_organization_units bu,
hr_organization_units le,
per_asg_responsibilities resp
--hr_organization_units dept
WHERE apae.period_of_service_id = pps.period_of_service_id
AND apae.absence_type_id = abt.absence_type_id
AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'
AND apae.APPROVAL_STATUS_CD = 'APPROVED'
AND abt.name = 'Tentative Leave Plan'
AND pps.person_id = papf.person_id
AND papf.person_id = ppnf.person_id
AND papf.person_id = pasf_Sup.person_id(+)
AND papf.person_id = paam.person_id(+)
AND paam.primary_assignment_flag(+) = 'Y'
--AND paam.assignment_type = 'E'
--AND paam.effective_latest_change = 'Y'
AND paam.job_id = pjft.job_id (+)
AND paam.grade_id = pgft.grade_id (+)
AND paam.business_unit_id = bu.organization_id
AND paam.legal_entity_id = le.organization_id
AND resp.person_id = HRC_SESSION_UTIL.get_user_personid
AND resp.responsibility_type IN ('XXXXXXXXXX')
AND resp.status = 'Active'
--AND resp.legal_entity_id = paam.legal_entity_id
--AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID
AND resp.organization_id =paam.organization_id
AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))
AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND
pjft.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND
pgft.effective_end_date(+)
AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND
paam.effective_end_date
AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND
abt.effective_end_date
AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND
papf.effective_end_date
AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND
ppnf.effective_end_date
AND ppnf.name_type = 'GLOBAL'
--AND papf.person_number IN ( '0015', '0033' )
AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)
)
WHERE 1=1
AND (LEAST(:p_sector) IS NULL
OR trim(sector) IN trim( :p_sector ))
--AND ( (coalesce(null, :p_sector) is null) or (trim(sector) in trim(:p_sector)))
AND (LEAST(:p_dept) IS NULL
OR department_name IN ( :p_dept ))
ORDER BY department_name,order_by_date
SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT DISTINCT
Abs_name_type saw_0,
Abs_entry_created_by saw_1,
Abs_last_updated_by saw_2,
Abs_End_Date saw_3,
Abs_Start_Date saw_4,
Abs_Entry_Identifier saw_5,
Dep_Name saw_6,
Grade_Name saw_7,
sector_2 saw_8,
Job_Name saw_9,
Legal_employer_name saw_10,
Postion_name saw_11,
Emp_name saw_12,
Hire_date saw_13,
Emp_num saw_14,
Abs_status saw_15,
Approval_status saw_16,
Abs_UOM saw_17,
BU_Name saw_18,
Leave_Duration saw_19
FROM (select
"Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name" Abs_name_type,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Created By" Abs_entry_created_by,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Last Updated By" Abs_last_updated_by,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status" Abs_status,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status" Approval_status,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Date End" Abs_End_Date,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Date Start" Abs_Start_Date,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Per Absence Entry Identifier" Abs_Entry_Identifier,
"Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM" Abs_UOM,
"Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name" BU_Name,
"Workforce Management - Absence Real Time"."Department"."Department Name" Dep_Name,
"Workforce Management - Absence Real Time"."Grade"."Grade Name" Grade_Name,
"Workforce Management - Absence Real Time"."Job"."Job Name" Job_Name,
"Workforce Management - Absence Real Time"."Legal Employer"."Name" Legal_employer_name,
"Workforce Management - Absence Real Time"."Position"."Position Name" Postion_name,
"Workforce Management - Absence Real Time"."Worker"."Employee Name" Emp_name,
"Workforce Management - Absence Real Time"."Worker"."Enterprise Hire Date" Hire_date,
"Workforce Management - Absence Real Time"."Worker"."Person Number" Emp_num,
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status") Abs_status_des,
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status") Approval_status_des,
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM") Abs_UOM_des,
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name")BU_Name_des ,
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Job"."Job Name") Job_Name_des,
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Position"."Position Name") Postion_name_des,
"Workforce Management - Absence Real Time"."Assignment Absences"."Leave Duration" Leave_Duration,
REPORT_SUM("Workforce Management - Absence Real Time"."Assignment Absences"."Leave Duration"
BY "Workforce Management - Absence Real Time"."Worker"."Person Number",
"Workforce Management - Absence Real Time"."Worker"."Enterprise Hire Date",
"Workforce Management - Absence Real Time"."Worker"."Employee Name",
"Workforce Management - Absence Real Time"."Department"."Department Name",
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Business Unit"."Business Unit Name"),
"Workforce Management - Absence Real Time"."Absence Type"."Absence Type Name",
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Date Start",
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Date End",
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."UOM"),
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Status"),
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Assignment Absences Details"."Approval Status"),
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Created By",
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Absence Entry Last Updated By",
"Workforce Management - Absence Real Time"."Assignment Absences Details"."Per Absence Entry Identifier",
DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Position"."Position Name"),"Workforce Management - Absence Real Time"."Grade"."Grade Name",DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Job"."Job Name")) Duration
FROM "Workforce Management - Absence Real Time"
WHERE
((DESCRIPTOR_IDOF("Workforce Management - Absence Real Time"."Worker"."HR Status") NOT IN ('*)nqgtn(*', 'INACTIVE')))) TABLE_A INNER JOIN (
SELECT
"Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_SECTOR_" sector_2,
"Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" person_number2
FROM "Workforce Management - Worker Assignment Real Time"
WHERE
(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") IN (1, 2))
) TABLE_B ON TABLE_A.Emp_num = TABLE_B.person_number2
WHERE
(Emp_num IN ('XYZ'))
ORDER BY saw_14, saw_4 DESC
SET VARIABLE PREFERRED_CURRENCY='User Preferred Currency 1',PREFERRED_CURRENCY='User Preferred Currency 1';SELECT
EMP_NUMBER saw_0,
ASSIGNMENT_NUMBER saw_1,
EMP_NAME saw_2,
HIRE_DATE saw_3,
GRADE saw_4,
JOB saw_5,
POSITION_NAME saw_6,
DEPT_NAME1 saw_7,
sector_2 saw_8,
BU_NAME1 saw_9,
PAYROLL_PERIOD_NAME saw_10,
PERIOD_NAME saw_11,
DATE_EARNED saw_12,
RUN_TYPE saw_13,
ELEMENT_CLASSI_NAME saw_14,
ELEMENT_NAME saw_15,
AMOUNT saw_16,
CASE WHEN ELEMENT_CLASSI_NAME in ('Involuntary Deductions','Social Insurance Deductions','Voluntary Deductions') THEN (AMOUNT*(-1)) ELSE AMOUNT END saw_17,
PROCESS_FLOW_NAME saw_18
FROM (SELECT
"Payroll - Payroll Run Results Real Time"."Business Unit"."Business Unit Name" BU_NAME1,
"Payroll - Payroll Run Results Real Time"."Department"."Department Name" DEPT_NAME1,
"Payroll - Payroll Run Results Real Time"."Element"."Element Classification Name" ELEMENT_CLASSI_NAME,
"Payroll - Payroll Run Results Real Time"."Element"."Element Name" ELEMENT_NAME,
"Payroll - Payroll Run Results Real Time"."Grade"."Grade Name" GRADE,
"Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Name" INPUT_VALUE_NAME,
"Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Unit of Measure" INPUT_VALUE_UOM,
"Payroll - Payroll Run Results Real Time"."Job"."Job Name" JOB,
"Payroll - Payroll Run Results Real Time"."Worker"."Enterprise Hire Date" HIRE_DATE,
"Payroll - Payroll Run Results Real Time"."Payroll Period"."Period Name" PERIOD_NAME,
"Payroll - Payroll Run Results Real Time"."Payroll Run Result Details"."Date Earned" DATE_EARNED,
"Payroll - Payroll Run Results Real Time"."Payroll Run Results"."Run Result Value" RUN_RESULT_VALUE,
"Payroll - Payroll Run Results Real Time"."Payroll Statutory Unit"."Payroll Statutory Unit Name" Payroll_Statutory_Unit_Name,
"Payroll - Payroll Run Results Real Time"."Payroll"."Payroll Period Name" PAYROLL_PERIOD_NAME,
"Payroll - Payroll Run Results Real Time"."Position"."Position Name" POSITION_NAME,
"Payroll - Payroll Run Results Real Time"."Run Type"."Run Type Name" RUN_TYPE,
"Payroll - Payroll Run Results Real Time"."Worker"."Assignment Number" ASSIGNMENT_NUMBER,
"Payroll - Payroll Run Results Real Time"."Worker"."Assignment Status Code" ASSIGN_STATUS_CODE,
"Payroll - Payroll Run Results Real Time"."Worker"."Employee Display Name" EMP_NAME,
"Payroll - Payroll Run Results Real Time"."Worker"."Person Number" EMP_NUMBER,
"Payroll - Payroll Run Results Real Time"."Payroll Flows"."Flow Instance Name" PROCESS_FLOW_NAME,
"Payroll - Payroll Run Results Real Time"."Payroll Run Results"."Amount" AMOUNT
FROM "Payroll - Payroll Run Results Real Time"
WHERE
((DESCRIPTOR_IDOF("Payroll - Payroll Run Results Real Time"."Input Value"."Input Value Unit of Measure") = 'M') AND ("Payroll - Payroll Run Results Real Time"."Element"."Element Classification Name" in ('Involuntary Deductions','Social Insurance Deductions','Standard Earnings','Supplemental Earnings','Voluntary Deductions','Information') )AND ("Input Value"."Input Value Name" = 'Pay Value'))
) TABLE_A LEFT OUTER JOIN (
SELECT
"Workforce Management - Worker Assignment Real Time"."Worker Assignment Details"."PER_ASG_DF_SECTOR_" sector_2,
"Workforce Management - Worker Assignment Real Time"."Worker"."Person Number" person_number2
FROM "Workforce Management - Worker Assignment Real Time"
WHERE
(DESCRIPTOR_IDOF("Workforce Management - Worker Assignment Real Time"."Worker"."Assignment Status") IN (1, 2))
) TABLE_B ON TABLE_A.EMP_NUMBER = TABLE_B.person_number2
WHERE
(EMP_NUMBER IN ('ABC')) AND (DATE_EARNED >= ANY
(
SELECT saw_0 FROM (SELECT
"Payroll Period"."Default Pay Date" saw_0
FROM "Payroll - Payroll Run Results Real Time"
WHERE
"Payroll Period"."Period Name" IN ('4 2023 Monthly Calendar')
) nqw_1
)) AND (DATE_EARNED <= ANY
(
SELECT saw_0 FROM (SELECT
"Payroll Period"."Default Pay Date" saw_0
FROM "Payroll - Payroll Balances Real Time"
WHERE
"Payroll Period"."Period Name" IN ('4 2023 Monthly Calendar')
) nqw_1
))
ORDER BY saw_12 DESC
SELECT
PERSON_NUMBER,
assignment_number,
Date_of_Joining,
grade,
Job,
Position,
Department,
BU_Name,
payroll_name,
PERIOD_NAME,
DEFAULT_PAYDATE,
Order_date,
Amount,
entitle_amount,
child_name_age,
child_school_type,
child_academic_year,
element_name,
base_element_name,
reporting_name
FROM (
SELECT
(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME = 'Pay Value'
AND pivf.uom = 'M'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)aMOUNT
,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)entitle_amount
,((select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)||'_'||SUBSTR(pett.element_name,20))child_name_age
,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)child_school_type
,(SELECT MEANING FROM HCM_LOOKUPS WHERE LOOKUP_TYPE = 'XXXXXXXXX' AND LOOKUP_CODE = (select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE
from PAY_RUN_RESULT_VALUES prrv
,PAY_RUN_RESULTS prr
,PAY_INPUT_VALUES_F pivf
,PAY_INPUT_VALUES_TL pivt
,PAY_REL_GROUPS_DN pasg
where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID
AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID
AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID
AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID
AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID
AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID
AND (USERENV('LANG')) = pivt.LANGUAGE
AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE
AND pivt.NAME in ('XXXXXXXXXXXXX')
AND pivf.uom = 'C'
AND prr.element_entry_id(+) = peeF.element_entry_id
AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID))child_academic_year
,ptp.PERIOD_NAME
,TO_CHAR(ptp.DEFAULT_PAYDATE,'DD-Mon-YYYY') DEFAULT_PAYDATE
,Trim(DEFAULT_PAYDATE) Order_date
,petf.base_element_name
,petT.reporting_name
,to_char(PAPF.PERSON_NUMBER) PERSON_NUMBER
,to_char(ASG.assignment_number) assignment_number
,To_char(papf.start_date,'DD-Mon-YYYY') Date_of_Joining
,peTt.element_name
,pgft.name grade
,pjft.name Job
,hapft.name Position
,pd.name Department
,haou.name BU_Name
,pay.PAYROLL_NAME
FROM
PAY_PAYROLL_REL_ACTIONS ppra
,PAY_PAYROLL_ACTIONS ppa
,PAY_PAY_RELATIONSHIPS_DN pprd
,PAY_REQUESTS pr
,PAY_FLOW_TASK_INSTANCES pfti
,PER_ALL_ASSIGNMENTS_M asg
,PAY_TIME_PERIODS ptp
,PAY_ELEMENT_TYPES_F petf
,PAY_ELEMENT_TYPES_TL pett
,PAY_ELE_CLASSIFICATIONS pec
,PAY_ELE_CLASSIFICATIONS pec1
,PAY_ELE_CLASSIFICATIONS_TL pect
,PAY_TIME_DEFINITIONS ptd
,PAY_TIME_DEFINITIONS ptd1
,PER_LEGISLATIVE_DATA_GROUPS pldg
,pay_all_payrolls_f pay
,per_all_people_f papf
,per_grades_f pgf
,per_grades_f_tl pgft
,per_jobs_f_tl pjft
,hr_all_positions_f_tl hapft
,per_departments PD
,hr_all_organization_units haou
,pay_element_entries_f peef
WHERE 1=1
AND ppra.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID
AND ppra.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID
AND ppa.PAY_REQUEST_ID = pr.PAY_REQUEST_ID(+)
AND pr.FLOW_TASK_INSTANCE_ID = pfti.FLOW_TASK_INSTANCE_ID(+)
AND asg.PERSON_ID = pprd.PERSON_ID
AND asg.PRIMARY_FLAG = 'Y'
AND asg.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL
AND ppa.EFFECTIVE_DATE BETWEEN asg.effective_start_Date AND asg.effective_end_date
AND asg.assignment_status_type <> 'INACTIVE'
AND ppra.RETRO_COMPONENT_ID IS NULL
AND ppa.EARN_TIME_PERIOD_ID = ptp.TIME_PERIOD_ID(+)
AND petf.ELEMENT_TYPE_ID = pett.ELEMENT_TYPE_ID(+)
AND (USERENV('LANG')) = pett.LANGUAGE (+)
AND petf.CLASSIFICATION_ID = pec1.CLASSIFICATION_ID
AND pec1.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID
AND petf.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID
AND pec.CLASSIFICATION_ID = pect.CLASSIFICATION_ID(+)
AND (USERENV('LANG')) = pect.LANGUAGE (+)
AND petf.STARTING_TIME_DEF_ID = ptd.TIME_DEFINITION_ID(+)
AND petf.ENDING_TIME_DEF_ID = ptd1.TIME_DEFINITION_ID(+)
AND pec.LEGISLATION_CODE = NVL(petf.LEGISLATION_CODE, pldg.LEGISLATION_CODE)
AND petf.LEGISLATIVE_DATA_GROUP_ID = pldg.LEGISLATIVE_DATA_GROUP_ID(+)
AND sysdate BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE
AND pett.ELEMENT_NAME IN (
'XXXXXXXXXXX'
)
AND pay.payroll_id = ptp.payroll_id(+)
AND pay.payroll_id = ppa.payroll_id(+)
AND ASG.person_id = papf.person_id
--AND papf.person_id = ppnf.person_id
AND pgf.grade_id = ASG.grade_id
AND pgft.grade_id = pgf.grade_id
AND pgft.LANGUAGE = 'US'
AND pjft.job_id(+) = ASG.job_id
AND pjft.LANGUAGE = 'US'
AND hapft.position_id(+) = ASG.position_id
AND hapft.LANGUAGE = 'US'
AND ASG.organization_id = pd.organization_id(+)
AND haou.organization_id(+) = ASG.business_unit_id
AND sysdate between papf.effective_start_date and papf.effective_end_date
and sysdate between hapft.effective_start_date and hapft.effective_end_date
AND ASG.assignment_type IN ( 'E' )
AND papf.person_id = peef.PERSON_ID
AND peef.element_type_id = petf.element_type_id
AND peef.EFFECTIVE_START_DATE >= ptp.START_DATE
AND peef.EFFECTIVE_END_DATE <= ptp.END_DATE
AND (COALESCE(NULL, :P_Emp_No) IS null OR PAPF.PERSON_NUMBER IN (:P_Emp_No))
AND (COALESCE(NULL, :p_ass_no) IS null OR ASG.assignment_number IN (:p_ass_no))
AND (COALESCE(NULL, :p_payroll) IS null OR pay.payroll_name IN (:p_payroll))
AND (COALESCE(NULL, :P_Element_Name) IS null OR petT.element_name IN (:P_Element_Name))
AND ptp.DEFAULT_PAYDATE between (:P_From) and (:P_To)
)
WHERE 1=1
AND (COALESCE(NULL, :P_child_academic_year) IS null OR child_academic_year IN (:P_child_academic_year))
AND (Amount IS NOT NULL OR Amount >0)
--and entitle_amount is not null
ORDER BY PERSON_NUMBER,Order_date desc
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