Active and Terminated Employee Details:
WITH EMP_DETAILS AS
(
SELECT
PAPF1.PERSON_ID,
PAYREL.PAYROLL_RELATIONSHIP_ID,
PAAF.ORGANIZATION_ID,
PAPF1.PERSON_NUMBER,
PPNF2.FULL_NAME FULL_NAME_US,
PPNF1.FULL_NAME FULL_NAME_AR,
--------------------------Added to Safty App--------------
PPNF2.LAST_NAME LAST_NAME_US,
PPNF2.NAM_INFORMATION1 MIDDLE_NAMES_US,
PPNF2.FIRST_NAME FIRST_NAME_US,
PAAF.SYSTEM_PERSON_TYPE PERSON_TYPE_CODE,
PPTT.USER_PERSON_TYPE PERSON_TYPE_NAME,
PJF1.JOB_CODE,
-------------------------------------------------
PJFT1.NAME JOB_NAME_US,
PJFT2.NAME JOB_NAME_AR,
HAPFT1.NAME POSITION_NAME_US,
HAPFT2.NAME POSITION_NAME_AR,
PGF1.GRADE_CODE GRADE,
FLVT1.MEANING NATIONALITY_US,
FLVT2.MEANING NATIONALITY_AR,
PAPF1.START_DATE HIRE_DATE,
PAAF.ASSIGNMENT_STATUS_TYPE ASSIGNMENT_STATUS_TYPE,
PAAF.ASSIGNMENT_TYPE ASSIGNMENT_TYPE,
PAAF.ASSIGNMENT_NUMBER ASSIGNMENT_NUMBER,
PAAF.LEGISLATION_CODE LEGISLATION_CODE,
(SELECT PNI.NATIONAL_IDENTIFIER_NUMBER
FROM PER_NATIONAL_IDENTIFIERS PNI
WHERE
UPPER(TRIM(PNI.NATIONAL_IDENTIFIER_TYPE)) IN (TRIM('IQAMA_NUMBER'),TRIM('CIVIL_IDENTITY_NUMBER'))
AND PAPF1.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_ID(+)
AND PAPF1.PERSON_ID = PNI.PERSON_ID(+)) NATIONAL_IDENTIFIER,
(SELECT TO_CHAR(PNI.EXPIRATION_DATE,'DD/MM/YYYY')
FROM PER_NATIONAL_IDENTIFIERS PNI
WHERE
UPPER(TRIM(PNI.NATIONAL_IDENTIFIER_TYPE)) IN (TRIM('IQAMA_NUMBER'),TRIM('CIVIL_IDENTITY_NUMBER'))
AND PAPF1.PRIMARY_NID_ID = PNI.NATIONAL_IDENTIFIER_ID(+)
AND PAPF1.PERSON_ID = PNI.PERSON_ID(+)) EXPIRATION_DATE,
PAAF.NOTICE_PERIOD NOTICE_PERIOD,
PAAF.PROBATION_PERIOD,
PEA1.EMAIL_ADDRESS,
'+' || PP.COUNTRY_CODE_NUMBER || PP.PHONE_NUMBER PHONE_NUMBER,
PPLF1.SEX,
-----------------------------------Safty App-----------------
(
SELECT
T.MEANING
FROM
FND_LOOKUP_VALUES_TL T
WHERE
T.LANGUAGE = 'US'
AND UPPER(T.LOOKUP_TYPE) ='SEX'
AND LOOKUP_CODE = PPLF1.SEX
) GENDER_DESC_EN,
(SELECT
T.MEANING
FROM
FND_LOOKUP_VALUES_TL T
WHERE
T.LANGUAGE = 'AR'
AND UPPER(T.LOOKUP_TYPE) ='SEX'
AND LOOKUP_CODE = PPLF1.SEX
) GENDER_DESC_AR,
----------------------------------------------------------
PPLF1.MARITAL_STATUS,
-----------------------------------Safty App-----------------
(
SELECT
T.MEANING
FROM
FND_LOOKUP_VALUES_TL T
WHERE
T.LANGUAGE = 'US'
AND UPPER(T.LOOKUP_TYPE) ='MARITAL_STATUS'
AND LOOKUP_CODE =PPLF1.MARITAL_STATUS
) MARITAL_STATUS_DESC_EN,
(SELECT
T.MEANING
FROM
FND_LOOKUP_VALUES_TL T
WHERE
T.LANGUAGE = 'AR'
AND UPPER(T.LOOKUP_TYPE) ='MARITAL_STATUS'
AND LOOKUP_CODE =PPLF1.MARITAL_STATUS
) MARITAL_STATUS_DESC_AR,
--------------------------------------------------------------------
-----------------------------------Line Manager--------------------------------------------------------------
PAPF2.START_DATE HIRE_DATE_SUP,
PAPF2.PERSON_NUMBER PERSON_NUMBER_SUP,
PAAF1.ASSIGNMENT_STATUS_TYPE ASSIGNMENT_STATUS_TYPE_SUP,
PAAF1.ASSIGNMENT_TYPE ASSIGNMENT_TYPE_SUP,
PPNF3.FULL_NAME FULL_NAME_SUP_US,
PPNF4.FULL_NAME FULL_NAME_SUP_AR,
PEA2.EMAIL_ADDRESS EMAIL_ADDRESS_SUP,
'+' || PP1.COUNTRY_CODE_NUMBER || PP1.PHONE_NUMBER PHONE_NUMBER_SUP,
TRIM(REPLACE(LOWER(PEA2.EMAIL_ADDRESS), '@xx.com','')) USER_NAME_SUP,
---------------------------------------------------------------------------------------
PLOC.INTERNAL_LOCATION_CODE LOCATION_CODE,
PLDFT.LOCATION_NAME LOCATION_NAME_US,
PLDFT2.LOCATION_NAME LOCATION_NAME_AR,
PERS.DATE_OF_BIRTH DATE_OF_BIRTH,
PASTT.USER_STATUS ASSIGNMENT_STATUS_FULL,
PCA.SEGMENT3 PER_COSTING_COST_CENTER,
PCA.SEGMENT4 PER_COSTING_LOCATION,
PCAA2.SEGMENT3 DEPT_COSTING_COST_CENTER,
NVL(PCA.SEGMENT3,
PCAA2.SEGMENT3) PER_COSTING_COST_CENTER_NET,
ROUND((TRUNC(SYSDATE) - PERS.DATE_OF_BIRTH) / 365,
2) AGE,
ROUND((TRUNC(SYSDATE) - PAPF1.START_DATE) / 365,
2) PERIOD_OF_SERVICE,
TRIM(REPLACE(LOWER(PEA1.EMAIL_ADDRESS),
'@nahdi.sa',
'')) USER_NAME,
PJF1.ATTRIBUTE1 PHARMA_CREW,
---------------------------Safty App------------------------
(SELECT DESCRIPTION
FROM FND_FLEX_VALUES_VL
WHERE
FLEX_VALUE_SET_ID=66341 --- Region LOV
AND FLEX_VALUE=PLDF.ATTRIBUTE1
) REGION_CODE,
(
SELECT
FLEX_VALUE_MEANING
FROM FND_FLEX_VALUES_VL
WHERE
FLEX_VALUE_SET_ID=66341
AND FLEX_VALUE=PLDF.ATTRIBUTE1
) REGION_AR,
--------------------------------------------------
PLDF.ATTRIBUTE1 LOCATION_REGION,
PLDF.ATTRIBUTE2 LOCATION_SPONSOR,
PPG.SEGMENT1 PPG_EMPLOYMENT_TYPE,
PPG.SEGMENT2 PPG_PAYROLL_MARITAL_STATUS,
SUBSTR(PGF1.GRADE_CODE,
INSTR(PGF1.GRADE_CODE,
'.') + 1) GRADE_CODE,
SUBSTR(PGF1.GRADE_CODE,
1,
INSTR(PGF1.GRADE_CODE,
'.') - 1) GRADE_NAME,
PC1.LEGISLATION_CODE NATIONALITY_CODE,
PAAF.DATE_PROBATION_END,
PAAF.ASSIGNMENT_STATUS_TYPE_ID,
ROUND(MONTHS_BETWEEN(SYSDATE,
PAPF1.START_DATE) / 12,
2) SERVICE_PERIOD,
'N' KAFALA_TRANSFER,
TO_CHAR(PPS.ACTUAL_TERMINATION_DATE,'DD/MM/YYYY') ACTUAL_TERMINATION_DATE,
---------------------Emergancy Info---Safty App----------------------------
PERS.ATTRIBUTE1 RELATIVE_CONTACT_NAME,
PERS.ATTRIBUTE8 CONTACT_PHONE,
PERS.ATTRIBUTE2 CONTACT_RELATIONSHIP,
PERS.ATTRIBUTE9 CONTACT_MOBILE,
------------------------ Home Address------Safty App---------------------
PPAUF.ADDRESS_TYPE,
PAF.ADDRESS_LINE_1 ADDRESS_LINE1,
PAF.ADDRESS_LINE_2 ADDRESS_LINE2,
PAF.TOWN_OR_CITY,
PAF.COUNTRY ADDRESS_COUNTRY_CODE,
FTT.TERRITORY_SHORT_NAME ADDRESS_COUNTRY_NAME
--------------------------------------------------------------------------
FROM
PER_ALL_PEOPLE_F PAPF1,
PER_ALL_ASSIGNMENTS_F PAAF,
PER_PERSON_NAMES_F PPNF2,
PER_PERSON_NAMES_F PPNF1,
PER_JOBS_F_TL PJFT2,
PER_JOBS_F PJF2,
PER_JOBS_F_TL PJFT1,
PER_JOBS_F PJF1,
HR_ALL_POSITIONS_F_TL HAPFT2,
HR_ALL_POSITIONS_F_TL HAPFT1,
PER_GRADES_F PGF1,
FND_LOOKUP_VALUES_TL FLVT2,
FND_LOOKUP_VALUES_TL FLVT1,
PER_CITIZENSHIPS PC2,
PER_CITIZENSHIPS PC1,
PER_EMAIL_ADDRESSES PEA1,
PER_PHONES PP,
PER_PEOPLE_LEGISLATIVE_F PPLF1,
PER_ASSIGNMENT_SUPERVISORS_F PASF1,
PER_ALL_PEOPLE_F PAPF2,
PER_ALL_ASSIGNMENTS_F PAAF1,
PER_PERSON_NAMES_F PPNF4,
PER_PERSON_NAMES_F PPNF3,
PER_EMAIL_ADDRESSES PEA2,
PER_PHONES PP1,
PER_LOCATIONS PLOC,
PER_LOCATION_DETAILS_F PLDF,
PER_LOCATION_DETAILS_F_TL PLDFT,
PER_LOCATION_DETAILS_F_TL PLDFT2,
PER_PERSONS PERS,
PER_ASSIGNMENT_STATUS_TYPES_TL PASTT,
PER_PEOPLE_GROUPS PPG,
PAY_PAY_RELATIONSHIPS_DN PPR,
PAY_RELATIONSHIP_TYPES PRT,
PAY_COST_ALLOCATIONS_F PCAF,
PAY_COST_ALLOC_ACCOUNTS PCA,
PAY_COST_ALLOCATIONS_F PCAF2,
PAY_COST_ALLOC_ACCOUNTS PCAA2,
PAY_REL_GROUPS_DN PAYREL,
PER_PERIODS_OF_SERVICE PPS,
PER_PERSON_TYPES PPT,
PER_PERSON_TYPES_TL PPTT,
------------------------ Home Address------------------------------
PER_PERSON_ADDR_USAGES_F PPAUF,
PER_ADDRESSES_F PAF,
FND_TERRITORIES_TL FTT
-------------------------------------------------------------------
WHERE
1 = 1
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) between PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
AND PAPF1.PERSON_ID = PAAF.PERSON_ID
AND TRUNC(SYSDATE) BETWEEN PAAF.EFFECTIVE_START_DATE AND PAAF.EFFECTIVE_END_DATE
AND PAPF1.PERSON_ID = PPNF2.PERSON_ID
AND PPNF2.NAME_TYPE = 'GLOBAL'
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PPNF2.EFFECTIVE_START_DATE AND PPNF2.EFFECTIVE_END_DATE
AND PAPF1.PERSON_ID = PPNF1.PERSON_ID
AND PPNF1.NAME_TYPE = 'SA'
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PPNF1.EFFECTIVE_START_DATE AND PPNF1.EFFECTIVE_END_DATE
AND PAAF.JOB_ID = PJFT1.JOB_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PJFT1.EFFECTIVE_START_DATE AND PJFT1.EFFECTIVE_END_DATE
AND PJF1.JOB_ID = PJFT1.JOB_ID
AND PJFT1.LANGUAGE = 'US'
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PJF1.EFFECTIVE_START_DATE AND PJF1.EFFECTIVE_END_DATE
AND PAAF.JOB_ID = PJFT2.JOB_ID
AND PJFT2.LANGUAGE = 'AR'
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PJFT2.EFFECTIVE_START_DATE AND PJFT2.EFFECTIVE_END_DATE
AND PJF2.JOB_ID = PJFT2.JOB_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PJF2.EFFECTIVE_START_DATE AND PJF2.EFFECTIVE_END_DATE
AND HAPFT1.LANGUAGE = 'US'
AND HAPFT2.LANGUAGE = 'AR'
AND PAAF.POSITION_ID = HAPFT1.POSITION_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN HAPFT1.EFFECTIVE_START_DATE AND HAPFT1.EFFECTIVE_END_DATE
AND PAAF.POSITION_ID = HAPFT2.POSITION_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN HAPFT2.EFFECTIVE_START_DATE AND HAPFT2.EFFECTIVE_END_DATE
AND PAAF.GRADE_ID = PGF1.GRADE_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PGF1.EFFECTIVE_START_DATE AND PGF1.EFFECTIVE_END_DATE
AND PAPF1.PERSON_ID = PC1.PERSON_ID
AND PAPF1.PERSON_ID = PC2.PERSON_ID
AND FLVT1.LANGUAGE = 'US'
AND FLVT1.LOOKUP_TYPE = 'NATIONALITY'
AND FLVT2.LOOKUP_TYPE = 'NATIONALITY'
AND FLVT2.LANGUAGE = 'AR'
AND PC1.LEGISLATION_CODE = FLVT1.LOOKUP_CODE
AND PC2.LEGISLATION_CODE = FLVT2.LOOKUP_CODE
AND PAPF1.PRIMARY_EMAIL_ID = PEA1.EMAIL_ADDRESS_ID(+)
AND PAPF1.PRIMARY_PHONE_ID = PP.PHONE_ID(+)
AND PAPF1.PERSON_ID = PPLF1.PERSON_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PPLF1.EFFECTIVE_START_DATE(+) AND PPLF1.EFFECTIVE_END_DATE(+)
AND PAPF1.PERSON_ID = PASF1.PERSON_ID(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PASF1.EFFECTIVE_START_DATE(+) AND PASF1.EFFECTIVE_END_DATE(+)
AND PAPF1.PERSON_ID = PPLF1.PERSON_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PPLF1.EFFECTIVE_START_DATE(+) AND PPLF1.EFFECTIVE_END_DATE(+)
----------------------------------------------------------------Line Manager----------------------------------------------------------
AND PASF1.MANAGER_ID = PAPF2.PERSON_ID(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PAPF2.EFFECTIVE_START_DATE(+) AND PAPF2.EFFECTIVE_END_DATE(+)
AND PAPF2.PERSON_ID = PAAF1.PERSON_ID(+)
AND PAPF2.PERSON_ID = PPNF4.PERSON_ID(+)
AND PAPF2.PERSON_ID = PPNF3.PERSON_ID(+)
AND PPNF3.NAME_TYPE(+) = 'GLOBAL'
AND PPNF4.NAME_TYPE(+) = 'SA'
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PPNF4.EFFECTIVE_START_DATE(+) AND PPNF4.EFFECTIVE_END_DATE(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PPNF3.EFFECTIVE_START_DATE(+) AND PPNF3.EFFECTIVE_END_DATE(+)
AND PAPF2.PRIMARY_EMAIL_ID = PEA2.EMAIL_ADDRESS_ID(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PAAF1.EFFECTIVE_START_DATE(+) AND PAAF1.EFFECTIVE_END_DATE(+)
AND PAAF1.ASSIGNMENT_TYPE(+) = 'E'
AND PAPF2.PRIMARY_EMAIL_ID = PEA2.EMAIL_ADDRESS_ID(+)
AND PAPF2.PRIMARY_PHONE_ID = PP1.PHONE_ID(+)
----------------------------------------------------------------------------------------------------------------------------------------
AND PAAF.LOCATION_ID = PLOC.LOCATION_ID(+)
AND PLOC.LOCATION_ID = PLDF.LOCATION_ID(+)
AND PLDFT.LANGUAGE(+) = 'US'
AND PLDFT2.LANGUAGE(+) = 'AR'
AND PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)
AND PLDF.LOCATION_DETAILS_ID = PLDFT2.LOCATION_DETAILS_ID(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PLDF.EFFECTIVE_START_DATE(+) AND PLDF.EFFECTIVE_END_DATE(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PLDFT.EFFECTIVE_START_DATE(+) AND PLDFT.EFFECTIVE_END_DATE(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PLDFT2.EFFECTIVE_START_DATE(+) AND PLDFT2.EFFECTIVE_END_DATE(+)
AND PERS.PERSON_ID(+) = PAPF1.PERSON_ID
AND PASTT.LANGUAGE = 'US'
AND PASTT.ASSIGNMENT_STATUS_TYPE_ID = PAAF.ASSIGNMENT_STATUS_TYPE_ID
AND PPG.PEOPLE_GROUP_ID(+) = PAAF.PEOPLE_GROUP_ID
AND PAYREL.ASSIGNMENT_ID = PAAF.ASSIGNMENT_ID
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE
AND PAPF1.PERSON_ID = PPR.PERSON_ID(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PPR.START_DATE(+) AND PPR.END_DATE(+)
AND PPR.RELATIONSHIP_TYPE_ID = PRT.RELATIONSHIP_TYPE_ID(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE) BETWEEN PCAF.EFFECTIVE_START_DATE(+) AND PCAF.EFFECTIVE_END_DATE(+)
AND NVL(PPS.ACTUAL_TERMINATION_DATE,
SYSDATE) BETWEEN PCAF2.EFFECTIVE_START_DATE(+) AND PCAF2.EFFECTIVE_END_DATE(+)
AND PCAF.PAYROLL_RELATIONSHIP_ID(+) = PPR.PAYROLL_RELATIONSHIP_ID
AND PCA.COST_ALLOCATION_RECORD_ID(+) = PCAF.COST_ALLOCATION_RECORD_ID
AND PCAF2.COST_ALLOCATION_RECORD_ID = PCAA2.COST_ALLOCATION_RECORD_ID(+)
AND PCAF2.SOURCE_TYPE(+) = 'ORG'
AND PCA.SOURCE_SUB_TYPE(+) = 'COST'
AND PCAF.SOURCE_TYPE(+) = 'ASG'
AND PCAF2.SOURCE_ID(+) = PAAF.ORGANIZATION_ID
AND PPS.PERSON_ID = PAAF.PERSON_ID
AND PAAF.PERIOD_OF_SERVICE_ID = PPS.PERIOD_OF_SERVICE_ID
AND PPT.SYSTEM_PERSON_TYPE=PAAF.SYSTEM_PERSON_TYPE
AND PPT.PERSON_TYPE_ID = PPTT.PERSON_TYPE_ID
AND PPTT.LANGUAGE = 'US'
------------------------ Home Address------------------------------
AND PPAUF.ADDRESS_ID = PAF.ADDRESS_ID(+)
AND PPAUF.EFFECTIVE_START_DATE = PAF.EFFECTIVE_START_DATE(+)
AND PPAUF.ADDRESS_TYPE(+)='HOME'
and PAF.COUNTRY=FTT.TERRITORY_CODE(+)
AND FTT.LANGUAGE(+)='US'
AND NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE)
BETWEEN PPAUF.EFFECTIVE_START_DATE(+) AND PPAUF.EFFECTIVE_END_DATE(+)
AND
NVL(PPS.ACTUAL_TERMINATION_DATE,SYSDATE)
BETWEEN PAF.EFFECTIVE_START_DATE(+) AND PAF.EFFECTIVE_END_DATE(+)
AND PAPF1.PERSON_ID=PPAUF.PERSON_ID(+)
-------------------------------------------------------------------
--AND (PAPF1.PERSON_NUMBER = :P_PERSON_NUMBER OR :P_PERSON_NUMBER is NULL)
),
SCHEDULE_DETAILS AS
(SELECT
TO_CHAR(ZPD.DAY_STOP_NUM) as WORK_SCHEDULE
-- ,PSA.primary_flag
-- ,PSA.resource_type
,PAAM.assignment_number
-- ,PAAM.assignment_id
--,PSA.schedule_id
--,psa.Resource_id
--,ZSSV.SCHEDULE_NAME
--,ZSPV.PATTERN_NAME
,(SELECT MIN (CASE PSA2.Resource_id
WHEN paam2.Assignment_id THEN 1
WHEN paam2.position_id THEN 2
WHEN paam2.Job_id THEN 3
WHEN paam2.organization_id THEN 4
WHEN paam2.Location_id THEN 5
WHEN paam2.legal_entity_id THEN 6
WHEN 1 THEN 7
END) AS old_value
FROM per_schedule_assignments PSA2
,per_all_assignments_f paam2
WHERE PSA.schedule_id = PSA2.schedule_id
AND paam2.assignment_id = paam.assignment_id) AS Sequence_Value
FROM per_schedule_assignments PSA,
zmm_sr_schedule_patterns ZSP,
zmm_sr_pattern_dtls ZPD,
per_all_assignments_f PAAM,
zmm_sr_schedules_vl zssv,
zmm_sr_patterns_vl zspv
WHERE psa.schedule_id = zsp.schedule_id
AND zsp.pattern_id = zpd.pattern_id
AND PSA.SCHEDULE_ID = ZSSV.SCHEDULE_ID
AND ZPD.PATTERN_ID = ZSPV.PATTERN_ID
AND TRUNC (SYSDATE) BETWEEN TRUNC(paam.effective_start_date) AND TRUNC(paam.effective_end_date)
--AND paam.primary_flag = 'Y'
--AND PSA.schedule_id = 300000008572702
AND psa.Resource_id = DECODE ((SELECT MIN ( CASE PSA2.Resource_id
WHEN paam2.Assignment_id THEN 1
WHEN paam2.position_id THEN 2
WHEN paam2.Job_id THEN 3
WHEN paam2.organization_id THEN 4
WHEN paam2.Location_id THEN 5
WHEN paam2.legal_entity_id THEN 6
WHEN 1 THEN 7
END) AS old_value
FROM per_schedule_assignments PSA2
,per_all_assignments_f paam2
WHERE paam2.assignment_id = paam.assignment_id), 1, paam.Assignment_id, 2, paam.position_id,
3, paam.Job_id,4, paam.organization_id, 5, paam.Location_id, 6, paam.legal_entity_id,1)
),
ORG_DETAILS AS
(SELECT HAUFT.ORGANIZATION_ID,
HAUFT.NAME ASSIGNMENT_ORGANIZATION,
HAOUF.ATTRIBUTE1 ASSIGNMENT_ORGANIZATION_TYPE,
HAUFT_MASTER.NAME MASTER_ORGANIZATION,
HAUFT_DIVISION.NAME DIVISION_ORGANIZATION,
HAUFT_DEPARTMENT.NAME DEPARTMENT_ORGANIZATION,
HAUFT_SECTION.NAME SECTION_ORGANIZATION,
HAUFT_UNIT.NAME UNIT_ORGANIZATION,
HAUFT_SUB_UNIT.NAME SUB_UNIT_ORGANIZATION
FROM HR_ORGANIZATION_UNITS_F_TL HAUFT,
HR_ALL_ORGANIZATION_UNITS_F HAOUF,
(SELECT PDTN.PK1_START_VALUE ORG_ID,
CONNECT_BY_ISCYCLE CYCLE,
LEVEL,
SYS_CONNECT_BY_PATH(PDTN.PK1_START_VALUE,
'/') ORGPATH
FROM PER_DEPT_TREE_NODE PDTN
START WITH PDTN.PK1_START_VALUE = 300000002759598
CONNECT BY NOCYCLE PRIOR PDTN.PK1_START_VALUE = PDTN.PARENT_PK1_VALUE) ORG_TREE,
HR_ORGANIZATION_UNITS_F_TL HAUFT_MASTER,
HR_ORGANIZATION_UNITS_F_TL HAUFT_DIVISION,
HR_ORGANIZATION_UNITS_F_TL HAUFT_DEPARTMENT,
HR_ORGANIZATION_UNITS_F_TL HAUFT_SECTION,
HR_ORGANIZATION_UNITS_F_TL HAUFT_UNIT,
HR_ORGANIZATION_UNITS_F_TL HAUFT_SUB_UNIT
WHERE 1 = 1
AND HAUFT.LANGUAGE = 'US'
AND TRUNC(SYSDATE) BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE
--AND HAUFT.ORGANIZATION_ID = 300000002760176 --PAAF.ORGANIZATION_ID
AND HAUFT.EFFECTIVE_START_DATE = HAOUF.EFFECTIVE_START_DATE
AND HAUFT.EFFECTIVE_END_DATE = HAOUF.EFFECTIVE_END_DATE
AND HAOUF.ORGANIZATION_ID = HAUFT.ORGANIZATION_ID
AND ORG_TREE.ORG_ID(+) = HAUFT.ORGANIZATION_ID
AND SUBSTR(ORG_TREE.ORGPATH,
2,
INSTR(ORG_TREE.ORGPATH,
'/',
1,
2) - 2) = HAUFT_MASTER.ORGANIZATION_ID(+)
AND HAUFT_MASTER.LANGUAGE(+) = 'US'
AND TRUNC(SYSDATE) BETWEEN HAUFT_MASTER.EFFECTIVE_START_DATE(+) AND HAUFT_MASTER.EFFECTIVE_END_DATE(+)
AND SUBSTR(ORG_TREE.ORGPATH,
18,
INSTR(ORG_TREE.ORGPATH,
'/',
1,
2) - 2) = HAUFT_DIVISION.ORGANIZATION_ID(+)
AND HAUFT_DIVISION.LANGUAGE(+) = 'US'
AND TRUNC(SYSDATE) BETWEEN HAUFT_DIVISION.EFFECTIVE_START_DATE(+) AND HAUFT_DIVISION.EFFECTIVE_END_DATE(+)
AND SUBSTR(ORG_TREE.ORGPATH,
34,
INSTR(ORG_TREE.ORGPATH,
'/',
1,
2) - 2) = HAUFT_DEPARTMENT.ORGANIZATION_ID(+)
AND HAUFT_DEPARTMENT.LANGUAGE(+) = 'US'
AND TRUNC(SYSDATE) BETWEEN HAUFT_DEPARTMENT.EFFECTIVE_START_DATE(+) AND HAUFT_DEPARTMENT.EFFECTIVE_END_DATE(+)
AND SUBSTR(ORG_TREE.ORGPATH,
50,
INSTR(ORG_TREE.ORGPATH,
'/',
1,
2) - 2) = HAUFT_SECTION.ORGANIZATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN HAUFT_SECTION.EFFECTIVE_START_DATE(+) AND HAUFT_SECTION.EFFECTIVE_END_DATE(+)
AND SUBSTR(ORG_TREE.ORGPATH,
66,
INSTR(ORG_TREE.ORGPATH,
'/',
1,
2) - 2) = HAUFT_UNIT.ORGANIZATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN HAUFT_UNIT.EFFECTIVE_START_DATE(+) AND HAUFT_UNIT.EFFECTIVE_END_DATE(+)
AND SUBSTR(ORG_TREE.ORGPATH,
82,
INSTR(ORG_TREE.ORGPATH,
'/',
1,
2) - 2) = HAUFT_SUB_UNIT.ORGANIZATION_ID(+)
AND TRUNC(SYSDATE) BETWEEN HAUFT_SUB_UNIT.EFFECTIVE_START_DATE(+) AND HAUFT_SUB_UNIT.EFFECTIVE_END_DATE(+)
AND HAUFT_SECTION.LANGUAGE(+) = 'US'
AND HAUFT_UNIT.LANGUAGE(+) = 'US'
AND HAUFT_SUB_UNIT.LANGUAGE(+) = 'US'
--AND HAUFT.ORGANIZATION_ID = 300000002760176
--AND PAPF1.PERSON_NUMBER = '10556'
),
BANK_DETAILS AS
(SELECT
DISTINCT
PBA.BANK_ACCOUNT_NAME,
PBA.BANK_NAME,
PBA.BANK_BRANCH_NAME,
PBA.BANK_ACCOUNT_NUM,
PBA.IBAN_NUMBER,
POPF.CURRENCY_CODE,
POPF.BASE_ORG_PAY_METHOD_NAME,
PPPMF.PAYROLL_RELATIONSHIP_ID
FROM PAY_BANK_ACCOUNTS PBA,
PAY_PERSON_PAY_METHODS_F PPPMF,
PAY_ORG_PAY_METHODS_F POPF
WHERE PPPMF.BANK_ACCOUNT_ID = PBA.BANK_ACCOUNT_ID
AND PPPMF.ORG_PAYMENT_METHOD_ID = POPF.ORG_PAYMENT_METHOD_ID
AND TRUNC(SYSDATE) BETWEEN PPPMF.EFFECTIVE_START_DATE AND PPPMF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN POPF.EFFECTIVE_START_DATE AND POPF.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PBA.START_DATE AND PBA.END_DATE
--AND PPPMF.PAYROLL_RELATIONSHIP_ID = 300000016783113
),
EMP_SPOUSE AS
(SELECT PAPF1.PERSON_ID,
PAPF_CONT.FULL_NAME SPOUSE_NAME,
TRUNC(PCR.EFFECTIVE_START_DATE) REL_START_DATE
FROM PER_CONTACT_RELSHIPS_F PCR,
PER_ALL_PEOPLE_F PAPF1,
PER_PERSON_NAMES_F PAPF_CONT,
HR_LOOKUPS HL
WHERE 1 = 1
AND PAPF1.PERSON_ID = PCR.PERSON_ID
AND PCR.CONTACT_PERSON_ID = PAPF_CONT.PERSON_ID(+)
AND SYSDATE BETWEEN PCR.EFFECTIVE_START_DATE AND PCR.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE AND PAPF1.EFFECTIVE_END_DATE
AND SYSDATE BETWEEN PAPF_CONT.EFFECTIVE_START_DATE AND PAPF_CONT.EFFECTIVE_END_DATE
AND UPPER(TRIM(PAPF_CONT.NAME_TYPE)) <> UPPER(TRIM('GLOBAL'))
AND HL.LOOKUP_TYPE(+) = 'CONTACT'
AND PCR.CONTACT_TYPE = 'S'
AND HL.LOOKUP_CODE(+) = PCR.CONTACT_TYPE
AND PCR.CONTACT_PERSON_ID = (SELECT MAX(PCRF.CONTACT_PERSON_ID)
FROM PER_CONTACT_RELSHIPS_F PCRF
WHERE PCRF.CONTACT_TYPE = 'S'
AND SYSDATE BETWEEN PCRF.EFFECTIVE_START_DATE AND PCRF.EFFECTIVE_END_DATE
AND PCRF.PERSON_ID = PAPF1.PERSON_ID))
SELECT ED.PERSON_NUMBER,
ED.FULL_NAME_US,
ED.FULL_NAME_AR,
ED.LAST_NAME_US,
ED.MIDDLE_NAMES_US,
ED.ASSIGNMENT_NUMBER,
ED.FIRST_NAME_US,
ED.PERSON_TYPE_CODE,
ED.PERSON_TYPE_NAME,
ED.JOB_CODE,
ED.JOB_NAME_US,
ED.JOB_NAME_AR,
ED.POSITION_NAME_US,
ED.POSITION_NAME_AR,
ED.GRADE,
ED.NATIONALITY_US,
ED.NATIONALITY_AR,
ED.HIRE_DATE,
ED.ASSIGNMENT_STATUS_TYPE,
ED.ASSIGNMENT_TYPE,
ED.NOTICE_PERIOD,
ED.PROBATION_PERIOD,
ED.EMAIL_ADDRESS,
ED.PHONE_NUMBER,
ED.SEX,
ED.GENDER_DESC_EN,
ED.GENDER_DESC_AR,
ED.MARITAL_STATUS,
ED.MARITAL_STATUS_DESC_EN,
ED.MARITAL_STATUS_DESC_AR,
ED.HIRE_DATE_SUP,
ED.PERSON_NUMBER_SUP,
--ED.ASSIGNMENT_TYPE_SUP,
ED.ASSIGNMENT_STATUS_TYPE_SUP,
ED.EXPIRATION_DATE ID_EXPIRY,
ED.FULL_NAME_SUP_US,
ED.FULL_NAME_SUP_AR,
ED.EMAIL_ADDRESS_SUP,
ED.PHONE_NUMBER_SUP,
OD.ASSIGNMENT_ORGANIZATION,
OD.ASSIGNMENT_ORGANIZATION_TYPE,
OD.MASTER_ORGANIZATION,
OD.DIVISION_ORGANIZATION,
OD.DEPARTMENT_ORGANIZATION,
OD.SECTION_ORGANIZATION,
OD.UNIT_ORGANIZATION,
OD.SUB_UNIT_ORGANIZATION,
ED.LOCATION_CODE,
ED.LOCATION_NAME_US,
ED.LOCATION_NAME_AR,
ED.DATE_OF_BIRTH,
ED.ASSIGNMENT_STATUS_FULL,
ED.PER_COSTING_COST_CENTER,
ED.PER_COSTING_LOCATION,
ED.DEPT_COSTING_COST_CENTER,
ED.PER_COSTING_COST_CENTER_NET,
ED.AGE,
ED.PERIOD_OF_SERVICE,
ED.USER_NAME,
ED.USER_NAME_SUP,
BA.BANK_ACCOUNT_NAME,
BA.BANK_NAME,
BA.BANK_BRANCH_NAME,
BA.BANK_ACCOUNT_NUM,
BA.IBAN_NUMBER,
BA.CURRENCY_CODE,
BA.BASE_ORG_PAY_METHOD_NAME,
ED.PHARMA_CREW,
ED.REGION_CODE,
ED.REGION_AR,
ED.LOCATION_REGION LOCATION_REGION_DFF,
ED.LOCATION_SPONSOR,
ED.RELATIVE_CONTACT_NAME,
ED.CONTACT_PHONE,
ED.CONTACT_RELATIONSHIP,
ED.CONTACT_MOBILE,
ED.ADDRESS_TYPE,
ED.ADDRESS_LINE1,
ED.ADDRESS_LINE2,
ED.TOWN_OR_CITY,
ED.ADDRESS_COUNTRY_CODE,
ED.ADDRESS_COUNTRY_NAME,
ED.PPG_EMPLOYMENT_TYPE,
ED.PPG_PAYROLL_MARITAL_STATUS,
ES.SPOUSE_NAME,
NULL MARRIAGESUBVENTIONEFLAG,
NULL ISTEQDAM_GUARANTOR,
NULL ADV_HOUSING_GUARANTOR,
NULL ADV_HOUSING_FLAG,
NULL ADVANCEHOUSINGCEILING,
NULL STOCK_GUARANTOR1,
NULL STOCK_GUARANTOR2,
ED.NATIONAL_IDENTIFIER,
ED.ACTUAL_TERMINATION_DATE,
SD.WORK_SCHEDULE,
case
when (ED.LEGISLATION_CODE = 'SA') then 'SA Legislative Data Group'
when (ED.LEGISLATION_CODE = 'AE') then 'AE Legislative Data Group'
END AS "LEGISLATION_CODE"
-- 'XXWEEKLY WORKING SCHEDULE' AS WORK_SCHEDULE
FROM EMP_DETAILS ED,
ORG_DETAILS OD,
BANK_DETAILS BA,
EMP_SPOUSE ES,
SCHEDULE_DETAILS SD
WHERE ED.ORGANIZATION_ID = OD.ORGANIZATION_ID(+)
AND ED.PAYROLL_RELATIONSHIP_ID = BA.PAYROLL_RELATIONSHIP_ID(+)
AND ED.PERSON_ID = ES.PERSON_ID(+)
AND SD.ASSIGNMENT_NUMBER(+) = ED.assignment_number
No comments:
Post a Comment