Active Employee Report

 Active Employee Report

SELECT 

PPPMF.PRIORITY,

"PER_ALL_PEOPLE_F_1"."PERSON_NUMBER"             AS "PERSON_NUMBER",

"PER_PERSON_NAMES_F_2"."FULL_NAME"               AS "FULL_NAME_US",

"PER_PERSON_NAMES_F_1"."FULL_NAME"               AS "FULL_NAME_AR",

"PER_JOBS_F_TL_1"."NAME"                         AS "JOB_NAME_US",

"PER_JOBS_F_TL_2"."NAME"                         AS "JOB_NAME_AR",

"HR_ALL_POSITIONS_F_TL_1"."NAME"                 AS "POSITION_NAME_US",

"HR_ALL_POSITIONS_F_TL_2"."NAME"                 AS "POSITION_NAME_AR",

"PER_GRADES_F_1"."GRADE_CODE"                    AS "GRADE",

"FND_LOOKUP_VALUES_TL_1"."MEANING"               AS "NATIONALITY_US",

"FND_LOOKUP_VALUES_TL_2"."MEANING"               AS "NATIONALITY_AR",

DECODE("FND_LOOKUP_VALUES_TL_1"."MEANING" ,'Saudi','Saudi','Non Saudi') NATIONALITY_CATEGORY,

--"PER_ALL_PEOPLE_F_1"."START_DATE"       AS "HIRE_DATE",

TO_CHAR("PER_ALL_PEOPLE_F_1"."START_DATE",'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN') HIRE_DATE1,

--TO_CHAR("PER_ALL_PEOPLE_F_1"."START_DATE",'DD/MM/YYYY')             AS "HIRE_DATE1",

 PASTT.USER_STATUS AS ASSIGNMENT_STATUS ,

--"PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_TYPE"        AS "ASSIGNMENT_TYPE",

"PER_ALL_ASSIGNMENTS_F"."NOTICE_PERIOD"          AS "NOTICE_PERIOD",

"PER_ALL_ASSIGNMENTS_F"."NOTICE_PERIOD_UOM"      AS "NOTICE_PERIOD_UOM",

"PER_ALL_ASSIGNMENTS_F"."PROBATION_PERIOD"       AS "PROBATION_PERIOD",

"PER_ALL_ASSIGNMENTS_F"."PROBATION_UNIT"         AS "PROBATION_UNIT",

"PER_EMAIL_ADDRESSES_1"."EMAIL_ADDRESS" AS "EMAIL_ADDRESS",

"PER_ALL_PEOPLE_F_2"."PERSON_NUMBER"               AS "PERSON_NUMBER_SUP",

"PER_ALL_ASSIGNMENTS_F_1"."ASSIGNMENT_STATUS_TYPE" AS "ASSIGNMENT_STATUS_SUP",

--"PER_ALL_ASSIGNMENTS_F_1"."ASSIGNMENT_TYPE"        AS "ASSIGNMENT_TYPE_SUP",

"PER_PERSON_NAMES_F_3"."FULL_NAME" AS "FULL_NAME_SUP_US",

"PER_PERSON_NAMES_F_4"."FULL_NAME" AS "FULL_NAME_SUP_AR",

"PER_EMAIL_ADDRESSES_2"."EMAIL_ADDRESS" AS "EMAIL_ADDRESS_SUP",

HAUFT.NAME       AS ASSIGNMENT_ORGANIZATION,  -- department

HAOUF.ATTRIBUTE1 AS ASSIGNMENT_ORGANIZATION_TYPE,

--   HAUFT_MASTER.NAME     AS MASTER_ORGANIZATION,

HAUFT_DIVISION.NAME   AS DIVISION_ORGANIZATION, ---division

HAUFT_DEPARTMENT.NAME AS DEPARTMENT_ORGANIZATION,

HAUFT_SECTION.NAME    AS SECTION_ORGANIZATION,

HAUFT_UNIT.NAME       AS UNIT_ORGANIZATION,

HAUFT_SUB_UNIT.NAME   AS SUB_UNIT_ORGANIZATION,

PLOC.INTERNAL_LOCATION_CODE AS LOCATION_CODE,

PLDFT.LOCATION_NAME AS LOCATION_NAME_US,

PLDFT2.LOCATION_NAME AS LOCATION_NAME_AR,

PLDF.ATTRIBUTE1 LOCATION_REGION ,

PLDF.ATTRIBUTE2 LOCATION_SPONSOR,

POPF.BASE_ORG_PAY_METHOD_NAME PAYMENT_SPONSOR,

PJF1.ATTRIBUTE1 PHARMA_CREW,

PPG.SEGMENT1    PPG_EMPLOYMENT_TYPE,

 ------------Personnel Deprtment Data ------------------------ 

 "PER_PEOPLE_LEGISLATIVE_F_1"."SEX"            AS "Gender",

"PER_PEOPLE_LEGISLATIVE_F_1"."MARITAL_STATUS" AS "HR_MARITAL_STATUS",

PPG.SEGMENT2    PAYROLL_MARITAL_STATUS,

"PER_PEOPLE_LEGISLATIVE_F_1"."HIGHEST_EDUCATION_LEVEL" AS "EDUCATION_LEVEL",

------------- Iqama Information ---------------------

(select PNI.NATIONAL_IDENTIFIER_TYPE

FROM PER_NATIONAL_IDENTIFIERS PNI 

WHERE UPPER(TRIM(PNI.NATIONAL_IDENTIFIER_TYPE)) IN (TRIM('IQAMA_NUMBER'),TRIM('CIVIL_IDENTITY_NUMBER'), TRIM(' ORA_HRX_AE_EM_ID'))

AND "PER_ALL_PEOPLE_F_1".PRIMARY_NID_ID=PNI.NATIONAL_IDENTIFIER_ID(+)

AND "PER_ALL_PEOPLE_F_1"."PERSON_ID"=PNI.PERSON_ID(+)) NATIONAL_IDENTIFIER_TYPE,

(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'), TRIM(' ORA_HRX_AE_EM_ID'))

AND "PER_ALL_PEOPLE_F_1".PRIMARY_NID_ID=PNI.NATIONAL_IDENTIFIER_ID(+)

AND "PER_ALL_PEOPLE_F_1"."PERSON_ID"=PNI.PERSON_ID(+)) NATIONAL_IDENTIFIER_NUMBER,

(select TO_CHAR(PNI.EXPIRATION_DATE,'DD-Mon-YYYY','NLS_DATE_LANGUAGE=AMERICAN')

FROM PER_NATIONAL_IDENTIFIERS PNI 

WHERE UPPER(TRIM(PNI.NATIONAL_IDENTIFIER_TYPE)) IN (TRIM('IQAMA_NUMBER'),TRIM('CIVIL_IDENTITY_NUMBER'), TRIM(' ORA_HRX_AE_EM_ID'))

AND "PER_ALL_PEOPLE_F_1".PRIMARY_NID_ID=PNI.NATIONAL_IDENTIFIER_ID(+)

AND "PER_ALL_PEOPLE_F_1"."PERSON_ID"=PNI.PERSON_ID(+)) ID_EXPIRATION_DATE,

(select PNI.NATIONAL_IDENTIFIER_NUMBER 

FROM PER_NATIONAL_IDENTIFIERS PNI 

WHERE 

UPPER(TRIM(PNI.NATIONAL_IDENTIFIER_TYPE)) IN (TRIM( 'GOSI_NUMBER'))

--AND "PER_ALL_PEOPLE_F_1".PRIMARY_NID_ID=PNI.NATIONAL_IDENTIFIER_ID(+)

AND "PER_ALL_PEOPLE_F_1"."PERSON_ID"=PNI.PERSON_ID(+)) GOSI,

-------------------Added by------------------------

CASE 

WHEN LENGTH("PER_PHONES"."PHONE_NUMBER")=12 AND SUBSTR("PER_PHONES"."PHONE_NUMBER",1,3) ='966'   THEN SUBSTR("PER_PHONES"."PHONE_NUMBER",4)

 WHEN LENGTH("PER_PHONES"."PHONE_NUMBER")=13 AND SUBSTR("PER_PHONES"."PHONE_NUMBER",1,4) ='9660'   THEN SUBSTR("PER_PHONES"."PHONE_NUMBER",5)

 WHEN LENGTH("PER_PHONES"."PHONE_NUMBER")=10 AND SUBSTR("PER_PHONES"."PHONE_NUMBER",1,1) ='0'   THEN SUBSTR("PER_PHONES"."PHONE_NUMBER",2)

ELSE "PER_PHONES"."PHONE_NUMBER" END "PHONE_NUMBER",

--"PER_PHONES"."PHONE_NUMBER" AS "PHONE_NUMBER",

"PER_PHONES_1"."PHONE_NUMBER" AS "PHONE_NUMBER_SUP",

 

-- '+' || "PER_PHONES"."COUNTRY_CODE_NUMBER" || "PER_PHONES"."PHONE_NUMBER" AS "PHONE_NUMBER",

 --'+' || "PER_PHONES_1"."COUNTRY_CODE_NUMBER" || "PER_PHONES_1"."PHONE_NUMBER" AS "PHONE_NUMBER_SUP",

 

 PERS.DATE_OF_BIRTH AS DATE_OF_BIRTH,    

 --  PCA.SEGMENT3 AS PER_COSTING_COST_CENTER,

 PCA.SEGMENT4 AS COST_LOCATION,

 --PCAA2.SEGMENT3 AS DEPT_COSTING_COST_CENTER,

NVL(PCA.SEGMENT3,PCAA2.SEGMENT3) COST_CENTER,  

ROUND((TRUNC(SYSDATE) - PERS.DATE_OF_BIRTH) / 365,2) AGE,    

ROUND((TRUNC(SYSDATE) - "PER_ALL_PEOPLE_F_1"."START_DATE") / 365,2) PERIOD_OF_SERVICE,    

TRIM(REPLACE(LOWER("PER_EMAIL_ADDRESSES_1"."EMAIL_ADDRESS"),'@nahdi.sa','')) AS USER_NAME,

TRIM(REPLACE(LOWER("PER_EMAIL_ADDRESSES_2"."EMAIL_ADDRESS"),'@nahdi.sa','')) AS USER_NAME_SUP,

---------------------Bank Column ------------

PBA.BANK_ACCOUNT_NAME,

NVL(PBA.BANK_NAME,'CASH') BANK_NAME,

PBA.BANK_BRANCH_NAME,

PBA.BANK_ACCOUNT_NUM,

PBA.IBAN_NUMBER  ,

-----------------------------------------

(

SELECT

HDOR.DEI_ATTRIBUTE1 MAKFOOL_FLAG

FROM 

HR_DOCUMENTS_OF_RECORD  HDOR 

WHERE 

DEI_ATTRIBUTE_CATEGORY = 'SA_MAKFOOL'

AND HDOR.PERSON_ID= "PER_ALL_PEOPLE_F_1"."PERSON_ID"

AND HDOR.DOCUMENTS_OF_RECORD_ID = (

SELECT MAX(HDOR1.DOCUMENTS_OF_RECORD_ID) FROM 

HR_DOCUMENTS_OF_RECORD  HDOR1

WHERE DEI_ATTRIBUTE_CATEGORY = 'SA_MAKFOOL'

AND HDOR1.PERSON_ID= HDOR.PERSON_ID

AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE))  BETWEEN NVL(DATE_FROM,(SYSDATE-1))  AND NVL(DATE_TO,(SYSDATE+1)) 

)

) MAKFOOL,

(

SELECT 

PP.PASSPORT_NUMBER --,

--PP.EXPIRATION_DATE 

FROM 

PER_PASSPORTS PP

WHERE

 PP.PERSON_ID = "PER_ALL_PEOPLE_F_1"."PERSON_ID"

 And ROWNUM=1

 ) PASSPORT_NUMBER,

 

HAUFT_BU.NAME BUSINESS_UNIT,

(

SELECT 

PLAN_DOR.DEI_ATTRIBUTE1

FROM 

HR_DOCUMENTS_OF_RECORD PLAN_DOR,

HR_DOCUMENT_TYPES_B DOR_TYPE

WHERE    

PLAN_DOR.PERSON_ID ="PER_ALL_PEOPLE_F_1".PERSON_ID 

AND UPPER (DOR_TYPE.SYSTEM_DOCUMENT_TYPE) = 'SA_NMC_ATTENDANCE_PLAN'

AND PLAN_DOR.DOCUMENT_TYPE_ID = DOR_TYPE.DOCUMENT_TYPE_ID

AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PLAN_DOR.DATE_FROM AND  NVL(PLAN_DOR.DATE_TO,(SYSDATE+1))

) PLAN_NAME

FROM   

"FUSION"."PER_PHONES"                   "PER_PHONES_1",

"FUSION"."PER_EMAIL_ADDRESSES"          "PER_EMAIL_ADDRESSES_2",

 "FUSION"."PER_PERSON_NAMES_F"           "PER_PERSON_NAMES_F_4",

 "FUSION"."PER_PERSON_NAMES_F"           "PER_PERSON_NAMES_F_3",

       "FUSION"."PER_ALL_ASSIGNMENTS_F"        "PER_ALL_ASSIGNMENTS_F_1",

       "FUSION"."PER_ASSIGNMENT_SUPERVISORS_F" "PER_ASSIGNMENT_SUPERVISORS_F_1",

       "FUSION"."PER_ALL_PEOPLE_F"             "PER_ALL_PEOPLE_F_2",

       "FUSION"."PER_PEOPLE_LEGISLATIVE_F"     "PER_PEOPLE_LEGISLATIVE_F_1",

       "FUSION"."PER_PHONES"                   "PER_PHONES",

       "FUSION"."PER_CITIZENSHIPS"             "PER_CITIZENSHIPS_2",

       "FUSION"."PER_CITIZENSHIPS"             "PER_CITIZENSHIPS_1",

       "FUSION"."FND_LOOKUP_VALUES_TL"         "FND_LOOKUP_VALUES_TL_2",

       "FUSION"."FND_LOOKUP_VALUES_TL"         "FND_LOOKUP_VALUES_TL_1",

       "FUSION"."PER_EMAIL_ADDRESSES"          "PER_EMAIL_ADDRESSES_1",

       "FUSION"."PER_PERSON_NAMES_F"           "PER_PERSON_NAMES_F_2",

       "FUSION"."PER_PERSON_NAMES_F"           "PER_PERSON_NAMES_F_1",

       "FUSION"."HR_ALL_POSITIONS_F_TL"        "HR_ALL_POSITIONS_F_TL_2",

       "FUSION"."HR_ALL_POSITIONS_F_TL"        "HR_ALL_POSITIONS_F_TL_1",

       "FUSION"."PER_JOBS_F_TL"                "PER_JOBS_F_TL_2",

       PER_JOBS_F                              PJF2,

       "FUSION"."PER_JOBS_F_TL"                "PER_JOBS_F_TL_1",

       PER_JOBS_F                              PJF1,

       "FUSION"."PER_GRADES_F"                 "PER_GRADES_F_1",

       "FUSION"."PER_ALL_ASSIGNMENTS_F"        "PER_ALL_ASSIGNMENTS_F",

       PER_PERSON_SECURED_LIST_V               "PER_ALL_PEOPLE_F_1",

       -- "FUSION"."PER_ALL_PEOPLE_F" "PER_ALL_PEOPLE_F_1",   

       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

        ORDER  BY LEVEL DESC,

            PDTN.PK1_START_VALUE,

            "Cycle",

            "OrgPath") 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,

       PER_LOCATIONS PLOC,

       ----------------------------------------------

       PER_LOCATION_DETAILS_F PLDF,

       PER_LOCATION_DETAILS_F_TL PLDFT,

       PER_LOCATION_DETAILS_F_TL PLDFT2,

       ---------------------------------------------------

       --PER_ADDRESSES_F PADRS,

       PER_PERSONS PERS,

       PER_ASSIGNMENT_STATUS_TYPES_TL PASTT,

       ----------------

       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,

       ----------------------------------Bank Tabels---------

        PAY_BANK_ACCOUNTS        PBA,

         PAY_PERSON_PAY_METHODS_F PPPMF,

         PAY_ORG_PAY_METHODS_F    POPF,

-----------------------------Business Unit -----------------------

HR_ORG_UNIT_CLASSIFICATIONS_F HOUCF_BU, 

HR_ALL_ORGANIZATION_UNITS_F HAOUF_BU,

HR_ORGANIZATION_UNITS_F_TL HAUFT_BU,

     ----------------------------------------------------------------

      /* (SELECT 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) BANK_DTLS,*/

         

       ----------------

       PER_PEOPLE_GROUPS        PPG--,

    --   PER_NATIONAL_IDENTIFIERS PNI

       

WHERE

 

"PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_ALL_ASSIGNMENTS_F"."PERSON_ID"

       AND "PER_ALL_ASSIGNMENTS_F"."GRADE_ID" = "PER_GRADES_F_1"."GRADE_ID"

       AND "PER_ALL_ASSIGNMENTS_F"."JOB_ID" = "PER_JOBS_F_TL_1"."JOB_ID"

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_JOBS_F_TL_1".EFFECTIVE_START_DATE AND "PER_JOBS_F_TL_1".EFFECTIVE_END_DATE

       

       AND PJF1.JOB_ID = "PER_JOBS_F_TL_1"."JOB_ID"

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PJF1.EFFECTIVE_START_DATE AND PJF1.EFFECTIVE_END_DATE

       

       AND "PER_ALL_ASSIGNMENTS_F"."JOB_ID" = "PER_JOBS_F_TL_2"."JOB_ID"

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_JOBS_F_TL_2".EFFECTIVE_START_DATE AND "PER_JOBS_F_TL_2".EFFECTIVE_END_DATE

       

       AND PJF2.JOB_ID = "PER_JOBS_F_TL_2"."JOB_ID"

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PJF2.EFFECTIVE_START_DATE AND PJF2.EFFECTIVE_END_DATE

       AND "PER_ALL_ASSIGNMENTS_F"."POSITION_ID" = "HR_ALL_POSITIONS_F_TL_1"."POSITION_ID"

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "HR_ALL_POSITIONS_F_TL_1".EFFECTIVE_START_DATE AND "HR_ALL_POSITIONS_F_TL_1".EFFECTIVE_END_DATE

       

       

       AND "PER_ALL_ASSIGNMENTS_F"."POSITION_ID" = "HR_ALL_POSITIONS_F_TL_2"."POSITION_ID"

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "HR_ALL_POSITIONS_F_TL_2".EFFECTIVE_START_DATE AND "HR_ALL_POSITIONS_F_TL_2".EFFECTIVE_END_DATE

       

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_PERSON_NAMES_F_1"."PERSON_ID"

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_PERSON_NAMES_F_2"."PERSON_ID"

       AND "PER_ALL_PEOPLE_F_1"."PRIMARY_EMAIL_ID" = "PER_EMAIL_ADDRESSES_1"."EMAIL_ADDRESS_ID"(+)

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_CITIZENSHIPS_1"."PERSON_ID"

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_CITIZENSHIPS_2"."PERSON_ID"

       AND "PER_CITIZENSHIPS_1"."LEGISLATION_CODE" = "FND_LOOKUP_VALUES_TL_1"."LOOKUP_CODE"

       AND "PER_CITIZENSHIPS_2"."LEGISLATION_CODE" = "FND_LOOKUP_VALUES_TL_2"."LOOKUP_CODE"

       AND "PER_ALL_PEOPLE_F_1"."PRIMARY_PHONE_ID" = "PER_PHONES"."PHONE_ID"(+)

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_PEOPLE_LEGISLATIVE_F_1"."PERSON_ID"

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_PEOPLE_LEGISLATIVE_F_1".EFFECTIVE_START_DATE(+) AND "PER_PEOPLE_LEGISLATIVE_F_1".EFFECTIVE_END_DATE(+)

       

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = "PER_ASSIGNMENT_SUPERVISORS_F_1"."PERSON_ID"(+)

       AND "PER_ASSIGNMENT_SUPERVISORS_F_1"."MANAGER_ID" = "PER_ALL_PEOPLE_F_2"."PERSON_ID"(+)

       AND "PER_ALL_PEOPLE_F_2"."PERSON_ID" = "PER_ALL_ASSIGNMENTS_F_1"."PERSON_ID"(+)

       AND "PER_ALL_PEOPLE_F_2"."PERSON_ID" = "PER_PERSON_NAMES_F_4"."PERSON_ID"(+)

       AND "PER_ALL_PEOPLE_F_2"."PERSON_ID" = "PER_PERSON_NAMES_F_3"."PERSON_ID"(+)

       AND "PER_ALL_PEOPLE_F_2"."PRIMARY_EMAIL_ID" = "PER_EMAIL_ADDRESSES_2"."EMAIL_ADDRESS_ID"(+)

       AND "PER_ALL_PEOPLE_F_2"."PRIMARY_PHONE_ID" = "PER_PHONES_1"."PHONE_ID"(+)

       AND "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_STATUS_TYPE" IN ('ACTIVE','SUSPENDED')

       AND "PER_ALL_ASSIGNMENTS_F"."ASSIGNMENT_TYPE" = 'E'

       

        AND  NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN   "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_START_DATE"  AND "PER_ALL_ASSIGNMENTS_F"."EFFECTIVE_END_DATE"

       

       AND  NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN  "PER_ALL_PEOPLE_F_2"."EFFECTIVE_START_DATE"(+) AND "PER_ALL_PEOPLE_F_2"."EFFECTIVE_END_DATE"(+)

       

      --AND "PER_ALL_ASSIGNMENTS_F_1"."ASSIGNMENT_STATUS_TYPE" = 'ACTIVE'

      

       AND "PER_ALL_ASSIGNMENTS_F_1"."ASSIGNMENT_TYPE"(+) = 'E'

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_ALL_ASSIGNMENTS_F_1"."EFFECTIVE_START_DATE"(+) AND  "PER_ALL_ASSIGNMENTS_F_1"."EFFECTIVE_END_DATE"(+)

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN  "PER_PERSON_NAMES_F_4"."EFFECTIVE_START_DATE"(+)  AND "PER_PERSON_NAMES_F_4"."EFFECTIVE_END_DATE"(+)

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN   "PER_PERSON_NAMES_F_3"."EFFECTIVE_START_DATE"(+) AND "PER_PERSON_NAMES_F_3"."EFFECTIVE_END_DATE"(+)

       

       AND "PER_PERSON_NAMES_F_3"."NAME_TYPE"(+) = 'GLOBAL'

       AND "PER_PERSON_NAMES_F_4"."NAME_TYPE"(+) IN ('SA','AE')

       AND "FND_LOOKUP_VALUES_TL_1"."LANGUAGE" = 'US'

       AND "FND_LOOKUP_VALUES_TL_1"."LOOKUP_TYPE" = 'NATIONALITY'

       AND "FND_LOOKUP_VALUES_TL_2"."LOOKUP_TYPE" = 'NATIONALITY'

       AND "FND_LOOKUP_VALUES_TL_2"."LANGUAGE" = 'AR'

       AND "PER_JOBS_F_TL_2"."LANGUAGE" = 'AR'

       AND "HR_ALL_POSITIONS_F_TL_1"."LANGUAGE" = 'US'

       AND "HR_ALL_POSITIONS_F_TL_2"."LANGUAGE" = 'AR'

       AND "PER_JOBS_F_TL_1"."LANGUAGE" = 'US'

       AND "PER_PERSON_NAMES_F_1"."NAME_TYPE" IN ('SA','AE')

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_PERSON_NAMES_F_1"."EFFECTIVE_START_DATE" AND "PER_PERSON_NAMES_F_1"."EFFECTIVE_END_DATE"

       

       AND "PER_PERSON_NAMES_F_2"."NAME_TYPE" = 'GLOBAL'

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_PERSON_NAMES_F_2"."EFFECTIVE_START_DATE" AND "PER_PERSON_NAMES_F_2"."EFFECTIVE_END_DATE"

       

      

       AND  NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN  "PER_ALL_PEOPLE_F_1"."EFFECTIVE_START_DATE" AND "PER_ALL_PEOPLE_F_1"."EFFECTIVE_END_DATE" 

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN "PER_ASSIGNMENT_SUPERVISORS_F_1"."EFFECTIVE_START_DATE"(+) AND "PER_ASSIGNMENT_SUPERVISORS_F_1"."EFFECTIVE_END_DATE"(+)

       

       AND HAUFT.LANGUAGE = 'US'

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN HAUFT.EFFECTIVE_START_DATE AND HAUFT.EFFECTIVE_END_DATE

       

       AND HAUFT.ORGANIZATION_ID = "PER_ALL_ASSIGNMENTS_F".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(+) = "PER_ALL_ASSIGNMENTS_F".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 NVL(:P_AS_OF_DATE, 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 NVL(:P_AS_OF_DATE, 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 NVL(:P_AS_OF_DATE, 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 HAUFT_SECTION.LANGUAGE(+) = 'US'

       

       AND NVL(:P_AS_OF_DATE, 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 HAUFT_UNIT.LANGUAGE(+) = 'US'

       

       AND NVL(:P_AS_OF_DATE, 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 HAUFT_SUB_UNIT.LANGUAGE(+) = 'US'

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN HAUFT_SUB_UNIT.EFFECTIVE_START_DATE(+) AND HAUFT_SUB_UNIT.EFFECTIVE_END_DATE(+)

       AND "PER_ALL_ASSIGNMENTS_F".LOCATION_ID = PLOC.LOCATION_ID(+)

       AND PLOC.LOCATION_ID = PLDF.LOCATION_ID(+)

       AND PLDF.LOCATION_DETAILS_ID = PLDFT.LOCATION_DETAILS_ID(+)

       AND PLDF.LOCATION_DETAILS_ID = PLDFT2.LOCATION_DETAILS_ID(+)

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PLDF.EFFECTIVE_START_DATE(+) AND PLDF.EFFECTIVE_END_DATE(+)

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PLDFT.EFFECTIVE_START_DATE(+) AND PLDFT.EFFECTIVE_END_DATE(+)

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PLDFT2.EFFECTIVE_START_DATE(+) AND PLDFT2.EFFECTIVE_END_DATE(+)

       

       AND PLDFT.LANGUAGE(+) = 'US'

       AND PLDFT2.LANGUAGE(+) = 'AR'

       --AND PADRS.ADDRESS_ID(+) = PLDF.MAIN_ADDRESS_ID

       

       

       AND PERS.PERSON_ID(+) = PER_ALL_PEOPLE_F_1.PERSON_ID

       AND PASTT.ASSIGNMENT_STATUS_TYPE_ID = "PER_ALL_ASSIGNMENTS_F".ASSIGNMENT_STATUS_TYPE_ID

       AND PASTT.LANGUAGE = 'US'

      --------------------------

       AND "PER_ALL_PEOPLE_F_1"."PERSON_ID" = PPR.PERSON_ID(+)

       

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PPR.START_DATE(+) AND PPR.END_DATE(+)

       

       AND PPR.RELATIONSHIP_TYPE_ID = PRT.RELATIONSHIP_TYPE_ID(+)

       AND PRT.BASE_REL_TYPE_NAME(+) = 'Standard'

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PCAF.EFFECTIVE_START_DATE(+) AND PCAF.EFFECTIVE_END_DATE(+)

       AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PCAF2.EFFECTIVE_START_DATE(+) AND PCAF2.EFFECTIVE_END_DATE(+)

       AND PCAF.PAYROLL_RELATIONSHIP_ID(+) = PPR.PAYROLL_RELATIONSHIP_ID

       AND PCA.SOURCE_SUB_TYPE(+) = 'COST'

       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 PCAF.SOURCE_TYPE(+) = 'ASG'

       AND PCAF2.SOURCE_ID(+) = "PER_ALL_ASSIGNMENTS_F".ORGANIZATION_ID

      ----------------------------- Bank Joins ---------------------------

     AND PAYREL.PAYROLL_RELATIONSHIP_ID = PPPMF.PAYROLL_RELATIONSHIP_ID(+)

      AND PPPMF.BANK_ACCOUNT_ID = PBA.BANK_ACCOUNT_ID(+)

       AND PPPMF.ORG_PAYMENT_METHOD_ID = POPF.ORG_PAYMENT_METHOD_ID(+)

      AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN TRUNC(PPPMF.EFFECTIVE_START_DATE(+)) AND TRUNC(PPPMF.EFFECTIVE_END_DATE(+))

      AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN TRUNC(POPF.EFFECTIVE_START_DATE(+)) AND TRUNC(POPF.EFFECTIVE_END_DATE(+))

     AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN TRUNC(PBA.START_DATE(+)) AND TRUNC(PBA.END_DATE(+))

    -- AND PPPMF.PRIORITY(+)=1

    ---------------------------------------Business Unit ------------------------------------------------------------------

AND HAOUF_BU.ORGANIZATION_ID = HOUCF_BU.ORGANIZATION_ID

AND HAOUF_BU.ORGANIZATION_ID = HAUFT_BU.ORGANIZATION_ID

AND HAOUF_BU.EFFECTIVE_START_DATE BETWEEN HOUCF_BU.EFFECTIVE_START_DATE AND HOUCF_BU.EFFECTIVE_END_DATE

AND HAUFT_BU.LANGUAGE = 'US'

AND HAUFT_BU.EFFECTIVE_START_DATE = HAOUF_BU.EFFECTIVE_START_DATE

AND HAUFT_BU.EFFECTIVE_END_DATE = HAOUF_BU.EFFECTIVE_END_DATE

AND HOUCF_BU.CLASSIFICATION_CODE = 'FUN_BUSINESS_UNIT'

AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN HAUFT_BU.EFFECTIVE_START_DATE AND HAUFT_BU.EFFECTIVE_END_DATE

AND HAUFT_BU.ORGANIZATION_ID="PER_ALL_ASSIGNMENTS_F".BUSINESS_UNIT_ID(+)

----------------------------------------------------------------------------------------------------------------------

AND PAYREL.ASSIGNMENT_ID = "PER_ALL_ASSIGNMENTS_F".ASSIGNMENT_ID

AND NVL(:P_AS_OF_DATE, TRUNC(SYSDATE)) BETWEEN PAYREL.START_DATE AND PAYREL.END_DATE

AND PPG.PEOPLE_GROUP_ID= "PER_ALL_ASSIGNMENTS_F".PEOPLE_GROUP_ID(+)

--  AND UPPER(TRIM(PNI.NATIONAL_IDENTIFIER_TYPE(+))) IN (UPPER(TRIM('IQAMA_NUMBER')),UPPER(TRIM('CIVIL_IDENTITY_NUMBER')),UPPER(TRIM('BORDER_NUMBER')))                     

--   AND PNI.NATIONAL_IDENTIFIER_ID = PER_ALL_PEOPLE_F_1.PRIMARY_NID_ID(+)

-- AND PNI.PERSON_ID= PER_ALL_PEOPLE_F_1.PERSON_ID

--------------------Paramater----------------------------------

AND NVL(HAUFT.NAME,0) = NVL (:P_DEPARTMENT,NVL(HAUFT.NAME,'0'))

AND  NVL(HAUFT_DIVISION.NAME ,'0')= NVL(:P_DIVISION,NVL(HAUFT_DIVISION.NAME ,'0'))

AND "PER_ALL_PEOPLE_F_1"."PERSON_NUMBER" =NVL(:P_PERSON_NUMBER,"PER_ALL_PEOPLE_F_1"."PERSON_NUMBER" )

AND (HAUFT_BU.NAME=:P_BUSINESS_UNIT OR :P_BUSINESS_UNIT IS NULL)

ORDER  BY TO_NUMBER("PER_ALL_PEOPLE_F_1"."PERSON_NUMBER")

Annual Bonus Report (To fetch the payroll run result value of an element)

 

SELECT 

--PAP.PAYROLL_NAME,

PAPF.PERSON_NUMBER,

PPNF.FULL_NAME AS "ENGLISH NAME",

PPNF1.FULL_NAME AS "ARABIC NAME",

PRRV.RESULT_VALUE

FROM 

PAY_ELEMENT_TYPES_VL ELE,

PAY_RUN_RESULTS PRR, 

PAY_RUN_RESULT_VALUES PRRV,

PAY_INPUT_VALUES_VL PIV,

PER_PERSON_NAMES_F PPNF,

PER_PERSON_NAMES_F PPNF1,

PER_PERSON_SECURED_LIST_V   PAPF,

PAY_PAYROLL_REL_ACTIONS PPRA,

PAY_PAY_RELATIONSHIPS_DN PREL,

 PAY_PAYROLL_ACTIONS PPA,

 PAY_ALL_PAYROLLS_F PAP,

 PAY_TIME_PERIODS PTP,

 PAY_ELE_CLASSIFICATIONS PEC 

WHERE 

PPA.PAYROLL_ACTION_ID = PPRA.PAYROLL_ACTION_ID

AND PPA.PAYROLL_ID = PAP.PAYROLL_ID

AND PRR.PAYROLL_REL_ACTION_ID = PPRA.PAYROLL_REL_ACTION_ID

AND PPRA.RUN_TYPE_ID IS NOT NULL

AND PRR.ELEMENT_TYPE_ID = ELE.ELEMENT_TYPE_ID

AND PRRV.RUN_RESULT_ID(+) = PRR.RUN_RESULT_ID

AND PIV.INPUT_VALUE_ID(+) = PRRV.INPUT_VALUE_ID

AND PREL.PAYROLL_RELATIONSHIP_ID = PPRA.PAYROLL_RELATIONSHIP_ID

AND PREL.PERSON_ID = PPNF.PERSON_ID

AND PAPF.PERSON_ID = PPNF.PERSON_ID

AND PREL.PERSON_ID = PPNF1.PERSON_ID

AND PAPF.PERSON_ID = PPNF1.PERSON_ID

AND PPNF.PERSON_ID = PPNF1.PERSON_ID

AND PPNF.NAME_TYPE= 'GLOBAL'

AND PPNF.CHAR_SET_CONTEXT = 'US'

AND PPNF1.CHAR_SET_CONTEXT = 'AR'

AND PPA.PAYROLL_ID = PTP.PAYROLL_ID

AND PPA.DATE_EARNED BETWEEN PTP.START_DATE AND PTP.END_DATE

--PTP.CUT_OFF_DATE

AND  PPA.DATE_EARNED  BETWEEN PAPF.EFFECTIVE_START_DATE AND PAPF.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED  BETWEEN PPNF.EFFECTIVE_START_DATE AND PPNF.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED  BETWEEN PPNF1.EFFECTIVE_START_DATE AND PPNF1.EFFECTIVE_END_DATE

AND PTP.STATUS='O'

AND  PPA.DATE_EARNED BETWEEN ELE.EFFECTIVE_START_DATE AND ELE.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PIV.EFFECTIVE_START_DATE AND PIV.EFFECTIVE_END_DATE

AND  PPA.DATE_EARNED BETWEEN PAP.EFFECTIVE_START_DATE AND PAP.EFFECTIVE_END_DATE

AND PIV.NAME= 'Pay Value'

AND PEC.CLASSIFICATION_ID=ELE.CLASSIFICATION_ID

AND  PIV.UOM = 'M'

AND  PPRA.ACTION_STATUS = 'C'

AND  PPA.ACTION_STATUS IN ('C', 'E')

AND PAP.PAYROLL_NAME IN (:PAYROLL)

AND PPA.EFFECTIVE_DATE IN (:Payroll_Process_Date)

AND PAPF.PERSON_NUMBER = NVL(:PERSON_NUMBER,PAPF.PERSON_NUMBER)

AND ELE.ELEMENT_NAME IN (:ELEMENT_NAME)

AND PPRA.RETRO_COMPONENT_ID IS NULL

--AND PAPF.PERSON_NUMBER = '16265'

--AND PPA.EFFECTIVE_DATE = :P_RUN_DATE

--AND PAPF.PERSON_NUMBER  = :P_PERSON_NUMBER

--AND PTP.PERIOD_NAME =:P_PERIOD_NAME

Active and Terminated Employee Details

 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