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")
No comments:
Post a Comment