Skip to main content

Integration1

Equifax:


SELECT
'000HEADER' AS RECORD_TYPE,
'' AS FILLER1,
'02.00' AS VERSION,
'17059' AS SOURCE_ID,
'EMPLOYER' AS SOURCE_TYPE,
'' AS FILLER2,
'' AS SOURCE_SERVICE,
'' AS EMP_ID

FROM
DUAL
-------------------
SELECT '202EMPLPIM' AS RECORD_TYPE,
'17059' AS COMP_CD,
LPAD(REPLACE(PNI.NATIONAL_IDENTIFIER_NUMBER,'-',''),9,'0')  AS NATIONAL_IDENTIFIER_NUMBER,
PER.PERSON_NUMBER,
PP.ATTRIBUTE6 USERNAME,
'' AS ACC_NUM,
'' AS PAYROLL,
NAME.FIRST_NAME,
NAME.MIDDLE_NAMES,
NAME.LAST_NAME,
'' AS SUFFIX,
'' AS TITLE,
LPAD(SUBSTR(PNI.NATIONAL_IDENTIFIER_NUMBER,-4),4,'0') AS EMP_ID,
'Y' AS DIRECT_ACCESS,
--LOC.INTERNAL_LOCATION_CODE AS DIVISION,
'' AS DIVISION,
--JOB.NAME,
        ASS.ASS_ATTRIBUTE4 AS NAME,
CASE WHEN ASS.ACTION_CODE IN ('REHIRE','REHIRE_WKR','HIRE_ADD_WORK_RELATION') THEN TO_CHAR(POS.DATE_START,'YYYYMMDD') ELSE '' END AS RECENT_HIRE_DATE,
CASE WHEN ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE' THEN 'A' ELSE 'T' END AS EMP_STATUS_CD,
TO_CHAR(POS.ORIGINAL_DATE_OF_HIRE,'YYYYMMDD') AS HIRE_DATE,
CASE
WHEN ASS.EMPLOYMENT_CATEGORY IN ('FR','FT') THEN 'F'
WHEN ASS.EMPLOYMENT_CATEGORY IN ('PR','PT') THEN 'P' ELSE ''
END AS EMPLOYEE_CATEGORY,
TO_CHAR(POS.ACTUAL_TERMINATION_DATE ,'YYYYMMDD') AS TERMINATION_DATE,
'' AS TERM_TWN,
'' AS TERM_UWX,
'' AS LAST_WORKING_DATE,
'' AS WORK_STATE,
'' AS WORK_LOC,
'' AS FEDERAL_ID,
'' AS STATE_UNEMP,
'' AS ADJUSTD_HR_DT

FROM PER_NATIONAL_IDENTIFIERS PNI,
PER_ALL_PEOPLE_F PER,
PER_PERSONS PP,
PER_PERSON_NAMES_F NAME,
PER_ALL_ASSIGNMENTS_F ASS,
PER_JOBS_F_VL JOB,
HR_LOCATIONS_ALL_F_VL  LOC,
PER_PERIODS_OF_SERVICE POS

WHERE PER.PERSON_ID=ASS.PERSON_ID
AND PNI.PERSON_ID(+)=PER.PERSON_ID
AND NAME.PERSON_ID(+)=PER.PERSON_ID
AND PP.PERSON_ID(+)=PER.PERSON_ID
AND JOB.JOB_ID(+)=ASS.JOB_ID
AND POS.PERSON_ID(+)=ASS.PERSON_ID
AND POS.PERIOD_OF_SERVICE_ID(+)=ASS.PERIOD_OF_SERVICE_ID
AND LOC.LOCATION_ID(+)=ASS.LOCATION_ID
AND NAME.NAME_TYPE='GLOBAL'
AND ASS.ASSIGNMENT_TYPE IN ('E')--,'C')
AND LOC.COUNTRY='US'
AND ASS.LEGISLATION_CODE IN ('US')
AND PNI.NATIONAL_IDENTIFIER_TYPE='SSN'
AND PNI.NATIONAL_IDENTIFIER_NUMBER IS NOT NULL AND PP.ATTRIBUTE6 IS NOT NULL AND LOC.INTERNAL_LOCATION_CODE IS NOT NULL



AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)
AND TRUNC(sysdate) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE 
AND TRUNC(sysdate) between ASS.EFFECTIVE_START_DATE and ASS.EFFECTIVE_END_DATE
AND TRUNC(sysdate) between NAME.EFFECTIVE_START_DATE(+) and NAME.EFFECTIVE_END_DATE(+)
AND TRUNC(sysdate) between JOB.EFFECTIVE_START_DATE(+) and JOB.EFFECTIVE_END_DATE(+)
AND TRUNC(sysdate) between LOC.EFFECTIVE_START_DATE(+) and LOC.EFFECTIVE_END_DATE(+)

SELECT '202EMPLPIM' AS RECORD_TYPE,
'17059' AS COMP_CD,
LPAD(REPLACE(PNI.NATIONAL_IDENTIFIER_NUMBER,'-',''),9,'0')  AS NATIONAL_IDENTIFIER_NUMBER,
PER.PERSON_NUMBER,
PP.ATTRIBUTE6 USERNAME,
'' AS ACC_NUM,
'' AS PAYROLL,
NAME.FIRST_NAME,
NAME.MIDDLE_NAMES,
NAME.LAST_NAME,
'' AS SUFFIX,
'' AS TITLE,
LPAD(SUBSTR(PNI.NATIONAL_IDENTIFIER_NUMBER,-4),4,'0') AS EMP_ID,
'Y' AS DIRECT_ACCESS,
--LOC.INTERNAL_LOCATION_CODE AS DIVISION,
'' AS DIVISION,
        ASS.ASS_ATTRIBUTE4 AS NAME,
CASE WHEN ASS.ACTION_CODE IN ('REHIRE','REHIRE_WKR','HIRE_ADD_WORK_RELATION') THEN TO_CHAR(POS.DATE_START,'YYYYMMDD') ELSE '' END AS RECENT_HIRE_DATE,
CASE WHEN ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE' THEN 'A' ELSE 'T' END AS EMP_STATUS_CD,
TO_CHAR(POS.ORIGINAL_DATE_OF_HIRE,'YYYYMMDD') AS HIRE_DATE,
CASE
WHEN ASS.EMPLOYMENT_CATEGORY IN ('FR','FT') THEN 'F'
WHEN ASS.EMPLOYMENT_CATEGORY IN ('PR','PT') THEN 'P' ELSE ''
END AS EMPLOYEE_CATEGORY,
TO_CHAR(POS.ACTUAL_TERMINATION_DATE ,'YYYYMMDD') AS TERMINATION_DATE,
'' AS TERM_TWN,
'' AS TERM_UWX,
'' AS LAST_WORKING_DATE,
'' AS WORK_STATE,
'' AS WORK_LOC,
'' AS FEDERAL_ID,
'' AS STATE_UNEMP,
'' AS ADJUSTD_HR_DT

FROM PER_NATIONAL_IDENTIFIERS PNI,
PER_ALL_PEOPLE_F PER,
PER_PERSONS PP,
PER_PERSON_NAMES_F NAME,
PER_ALL_ASSIGNMENTS_F ASS,
PER_JOBS_F_VL JOB,
HR_LOCATIONS_ALL_F_VL  LOC,
PER_PERIODS_OF_SERVICE POS

WHERE PER.PERSON_ID=ASS.PERSON_ID
AND PNI.PERSON_ID(+)=PER.PERSON_ID
AND NAME.PERSON_ID(+)=PER.PERSON_ID
AND PP.PERSON_ID(+)=PER.PERSON_ID
AND JOB.JOB_ID(+)=ASS.JOB_ID
AND POS.PERSON_ID(+)=ASS.PERSON_ID
AND POS.PERIOD_OF_SERVICE_ID(+)=ASS.PERIOD_OF_SERVICE_ID
AND LOC.LOCATION_ID(+)=ASS.LOCATION_ID
AND NAME.NAME_TYPE='GLOBAL'
AND ASS.ASSIGNMENT_TYPE IN ('E')--,'C')
AND LOC.COUNTRY='US'
AND ASS.LEGISLATION_CODE IN ('US')
AND PNI.NATIONAL_IDENTIFIER_TYPE='SSN'
AND PNI.NATIONAL_IDENTIFIER_NUMBER IS NOT NULL AND PP.ATTRIBUTE6 IS NOT NULL AND LOC.INTERNAL_LOCATION_CODE IS NOT NULL

AND ((ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE') OR (GREATEST(POS.ACTUAL_TERMINATION_DATE,POS.LAST_UPDATE_DATE) BETWEEN SYSDATE-7 AND SYSDATE))
AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)
AND TRUNC(sysdate) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE 
AND TRUNC(sysdate) between ASS.EFFECTIVE_START_DATE and ASS.EFFECTIVE_END_DATE
AND TRUNC(sysdate) between NAME.EFFECTIVE_START_DATE(+) and NAME.EFFECTIVE_END_DATE(+)
AND TRUNC(sysdate) between JOB.EFFECTIVE_START_DATE(+) and JOB.EFFECTIVE_END_DATE(+)
AND TRUNC(sysdate) between LOC.EFFECTIVE_START_DATE(+) and LOC.EFFECTIVE_END_DATE(+)



SELECT '350EMPLI9LOC' AS RECORD_TYPE,
'17059' AS CMP_CD,
LPAD(REPLACE(PNI.NATIONAL_IDENTIFIER_NUMBER,'-',''),9,'0') AS SSN,
'' AS BLK,
LOC.INTERNAL_LOCATION_CODE AS LOCATION_CODE,
CASE WHEN ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE' THEN 'A' ELSE 'R' END AS ACTION_CODE


FROM PER_NATIONAL_IDENTIFIERS PNI,
PER_ALL_PEOPLE_F PER,
PER_ALL_ASSIGNMENTS_F ASS,
HR_LOCATIONS_ALL_F_VL  LOC


WHERE PER.PERSON_ID=ASS.PERSON_ID
AND PNI.PERSON_ID(+)=PER.PERSON_ID
AND LOC.LOCATION_ID(+)=ASS.LOCATION_ID
AND LOC.COUNTRY='US'
AND ASS.LEGISLATION_CODE IN ('US')
AND ASS.ASSIGNMENT_TYPE IN ('E')--,'C')
AND PNI.NATIONAL_IDENTIFIER_TYPE='SSN'
AND PNI.NATIONAL_IDENTIFIER_NUMBER IS NOT NULL AND LOC.INTERNAL_LOCATION_CODE IS NOT NULL



AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)
AND TRUNC(sysdate) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE 
AND TRUNC(sysdate) between ASS.EFFECTIVE_START_DATE and ASS.EFFECTIVE_END_DATE
AND TRUNC(sysdate) between LOC.EFFECTIVE_START_DATE(+) and LOC.EFFECTIVE_END_DATE(+)


SELECT '350EMPLI9LOC' AS RECORD_TYPE,
'17059' AS CMP_CD,
LPAD(REPLACE(PNI.NATIONAL_IDENTIFIER_NUMBER,'-',''),9,'0') AS SSN,
'' AS BLK,
LOC.INTERNAL_LOCATION_CODE AS LOCATION_CODE,
CASE WHEN ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE' THEN 'A' ELSE 'R' END AS ACTION_CODE


FROM PER_NATIONAL_IDENTIFIERS PNI,
PER_ALL_PEOPLE_F PER,
PER_ALL_ASSIGNMENTS_F ASS,
HR_LOCATIONS_ALL_F_VL  LOC,
PER_PERIODS_OF_SERVICE POS


WHERE PER.PERSON_ID=ASS.PERSON_ID
AND PNI.PERSON_ID(+)=PER.PERSON_ID
AND LOC.LOCATION_ID(+)=ASS.LOCATION_ID
AND POS.PERSON_ID(+)=ASS.PERSON_ID
AND POS.PERIOD_OF_SERVICE_ID(+)=ASS.PERIOD_OF_SERVICE_ID
AND LOC.COUNTRY='US'
AND ASS.LEGISLATION_CODE IN ('US')
AND ASS.ASSIGNMENT_TYPE IN ('E')--,'C')
AND PNI.NATIONAL_IDENTIFIER_TYPE='SSN'
AND PNI.NATIONAL_IDENTIFIER_NUMBER IS NOT NULL AND LOC.INTERNAL_LOCATION_CODE IS NOT NULL

AND ((ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE') OR (GREATEST(POS.ACTUAL_TERMINATION_DATE,POS.LAST_UPDATE_DATE) BETWEEN SYSDATE-7 AND SYSDATE))
AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(sysdate) between EFFECTIVE_START_DATE and EFFECTIVE_END_DATE)
AND TRUNC(sysdate) between PER.EFFECTIVE_START_DATE and PER.EFFECTIVE_END_DATE 
AND TRUNC(sysdate) between ASS.EFFECTIVE_START_DATE and ASS.EFFECTIVE_END_DATE
AND TRUNC(sysdate) between LOC.EFFECTIVE_START_DATE(+) and LOC.EFFECTIVE_END_DATE(+)


SELECT DISTINCT
'999FOOTER' AS RECORD_TYPE,
'' AS BLK,
'' AS BLK1,
COUNT(A.LOCATION_ID) AS LOC_COUNT

FROM PER_ALL_ASSIGNMENTS_F A,HR_LOCATIONS_ALL B

WHERE A.LOCATION_ID=B.LOCATION_ID(+)
AND A.LEGISLATION_CODE = 'US'
AND B.COUNTRY='US'
AND A.ASSIGNMENT_STATUS_TYPE='ACTIVE'
and A.ASSIGNMENT_TYPE IN ('E')
AND GREATEST(A.LAST_UPDATE_DATE,A.EFFECTIVE_START_DATE) BETWEEN SYSDATE - 7 AND  SYSDATE

AND TRUNC(sysdate) between B.EFFECTIVE_START_DATE(+) and B.EFFECTIVE_END_DATE(+)
AND TRUNC(sysdate) between A.EFFECTIVE_START_DATE and A.EFFECTIVE_END_DATE


03_Emergency_DM

SELECT T.*,
CASE 
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5) IS NOT NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(NVL(T3.NUM3,T4.NUM4),T5.NUM5)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN ''
ELSE '' END AS PHONE1,
T3.NUM3 AS WORK_,
T4.NUM4 AS WORK_2,
T5.NUM5 AS WORK_3,
T2.NUM2 AS HOME,
T1.NUM1 AS CELL,
T6.NUM6 AS CELL_H,
CASE 
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL 
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
ELSE '' END AS SMSPHONE,
(SELECT '"'||((P.PHONE_NUMBER) || 'X' ||(P.EXTENSION))||'"'  FROM PER_PHONES_V P WHERE P.PERSON_ID=T.PERSON_ID AND P.PHONE_TYPE IN NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5) AND TRUNC(SYSDATE) BETWEEN P.DATE_FROM AND P.DATE_TO AND P.PHONE_NUMBER IS NOT NULL AND P.EXTENSION IS NOT NULL) AS PHONE3
FROM (SELECT ASS.PERSON_ID,
'"'||'   '||'"' AS AGENCY,
'"'||PER.PERSON_NUMBER||'"' AS REF_CODE,
'"'||NAME.FIRST_NAME||'"' AS FIRST_NAME,
'"'||NAME.LAST_NAME||'"' AS LAST_NAME,
-- '"'||PH.PHONE_NUMBER||'"' AS PHONE2,
(SELECT '"'||PH.PHONE_NUMBER||'"'  FROM PER_PHONES_V PH WHERE PH.PHONE_TYPE ='H1' AND PH.PERSON_ID = PER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PH.DATE_FROM AND PH.DATE_TO AND PH.PHONE_NUMBER IS NOT NULL) AS PHONE2,
'' AS TERMINATED,
'"'||LOC.LOCATION_NAME||'"' AS GROUP_,
CASE WHEN (SELECT LOCATION_NAME
   FROM PER_ALL_ASSIGNMENTS_M PAA,
HR_LOCATIONS HL
  WHERE PAA.LOCATION_ID = HL.LOCATION_ID
AND PAA.PERSON_ID = ASS.PERSON_ID
AND PAA.ASSIGNMENT_TYPE IN ('E')
AND SUBSTR(PAA.EFFECTIVE_END_DATE,1,10) = SUBSTR(ASS.EFFECTIVE_START_DATE-1,1,10)
AND PAA.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND PAA.EFFECTIVE_LATEST_CHANGE = 'Y'
AND TRUNC(SYSDATE) BETWEEN HL.EFFECTIVE_START_DATE AND HL.EFFECTIVE_END_DATE) = LOC.LOCATION_NAME THEN ''
  ELSE (SELECT '"'||LOCATION_CODE||'"'
   FROM PER_ALL_ASSIGNMENTS_M PAA,
HR_LOCATIONS HL
  WHERE PAA.LOCATION_ID = HL.LOCATION_ID
AND PAA.PERSON_ID = ASS.PERSON_ID
AND PAA.ASSIGNMENT_TYPE IN ('E')
AND SUBSTR(PAA.EFFECTIVE_END_DATE,1,10) = SUBSTR(ASS.EFFECTIVE_START_DATE-1,1,10)
AND PAA.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND PAA.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAA.LAST_UPDATE_DATE BETWEEN TRUNC(SYSDATE-7) AND TRUNC(SYSDATE)
AND TRUNC(SYSDATE) BETWEEN HL.EFFECTIVE_START_DATE AND HL.EFFECTIVE_END_DATE)
END AS DEL_GROUP

FROM PER_ALL_PEOPLE_F PER,
PER_PERSON_NAMES_F NAME,
HR_LOCATIONS_ALL  LOC,
-- PER_PHONES_V PH,
PER_ALL_ASSIGNMENTS_F ASS

WHERE 1=1
-- AND PH.PHONE_TYPE(+) ='H1' 
-- AND PH.PERSON_ID = PER.PERSON_ID
AND PER.PERSON_ID=ASS.PERSON_ID
AND NAME.PERSON_ID=PER.PERSON_ID
AND LOC.LOCATION_ID(+)=ASS.LOCATION_ID
AND NAME.NAME_TYPE='GLOBAL'
AND ASSIGNMENT_TYPE IN ('E')
AND ASS.LEGISLATION_CODE IN ('US','CA')
AND ASS.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND ASS.EFFECTIVE_LATEST_CHANGE = 'Y'
-- AND TRUNC(SYSDATE) BETWEEN PH.DATE_FROM AND PH.DATE_TO
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE AND NAME.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN LOC.EFFECTIVE_START_DATE(+) AND LOC.EFFECTIVE_END_DATE(+)
ORDER BY PER.PERSON_NUMBER) T

LEFT OUTER JOIN (SELECT
PER1.PERSON_ID AS P1,
PW.PHONE_NUMBER AS NUM1,
PW.PHONE_TYPE AS TYP1

FROM
PER_ALL_PEOPLE_F PER1,
PER_ALL_ASSIGNMENTS_F ASS1,
PER_PHONES_V PW

WHERE
PW.PHONE_TYPE ='WM'
AND PER1.PERSON_ID=ASS1.PERSON_ID
AND PW.PERSON_ID = PER1.PERSON_ID
AND ASS1.ASSIGNMENT_TYPE IN ('E')
AND ASS1.LEGISLATION_CODE IN ('US','CA')
AND ASS1.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER1.EFFECTIVE_START_DATE AND PER1.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS1.EFFECTIVE_START_DATE AND ASS1.EFFECTIVE_END_DATE
-- AND TRUNC(SYSDATE) BETWEEN PW.DATE_FROM AND PW.DATE_TO
AND PW.DATE_FROM = (SELECT MAX(DATE_FROM) FROM PER_PHONES_V WHERE PERSON_ID = PW.PERSON_ID AND PHONE_TYPE = PW.PHONE_TYPE)
) T1
ON T.PERSON_ID=T1.P1
LEFT OUTER JOIN (SELECT
PER6.PERSON_ID AS P6,
PWC.PHONE_NUMBER AS NUM6,
PWC.PHONE_TYPE AS TYP6

FROM
PER_ALL_PEOPLE_F PER6,
PER_ALL_ASSIGNMENTS_F ASS6,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='HM'
AND PER6.PERSON_ID=ASS6.PERSON_ID
AND PWC.PERSON_ID = PER6.PERSON_ID
AND ASS6.ASSIGNMENT_TYPE IN ('E')
AND ASS6.LEGISLATION_CODE IN ('US','CA')
AND ASS6.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER6.EFFECTIVE_START_DATE AND PER6.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS6.EFFECTIVE_START_DATE AND ASS6.EFFECTIVE_END_DATE
-- AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
AND PWC.DATE_FROM = (SELECT MAX(DATE_FROM) FROM PER_PHONES_V WHERE PERSON_ID = PWC.PERSON_ID AND PHONE_TYPE = PWC.PHONE_TYPE)
) T6
ON T.PERSON_ID=T6.P6

LEFT OUTER JOIN (SELECT
PER2.PERSON_ID AS P2,
PH.PHONE_NUMBER AS NUM2,
PH.PHONE_TYPE AS TYP2

FROM
PER_ALL_PEOPLE_F PER2,
PER_ALL_ASSIGNMENTS_F ASS2,
PER_PHONES_V PH

WHERE
PH.PHONE_TYPE ='H1'
AND PER2.PERSON_ID=ASS2.PERSON_ID
AND PH.PERSON_ID = PER2.PERSON_ID
AND ASS2.ASSIGNMENT_TYPE IN ('E')
AND ASS2.LEGISLATION_CODE IN ('US','CA')
AND ASS2.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER2.EFFECTIVE_START_DATE AND PER2.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS2.EFFECTIVE_START_DATE AND ASS2.EFFECTIVE_END_DATE
-- AND TRUNC(SYSDATE) BETWEEN PH.DATE_FROM AND PH.DATE_TO
AND PH.DATE_FROM = (SELECT MAX(DATE_FROM) FROM PER_PHONES_V WHERE PERSON_ID = PH.PERSON_ID AND PHONE_TYPE = PH.PHONE_TYPE)

) T2
ON T.PERSON_ID=T2.P2

LEFT OUTER JOIN (SELECT
PER3.PERSON_ID AS P3,
PWC.PHONE_NUMBER AS NUM3,
PWC.PHONE_TYPE AS TYP3

FROM
PER_ALL_PEOPLE_F PER3,
PER_ALL_ASSIGNMENTS_F ASS3,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='W1'
AND PER3.PERSON_ID=ASS3.PERSON_ID
AND PWC.PERSON_ID = PER3.PERSON_ID
AND ASS3.ASSIGNMENT_TYPE IN ('E')
AND ASS3.LEGISLATION_CODE IN ('US','CA')
AND ASS3.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER3.EFFECTIVE_START_DATE AND PER3.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS3.EFFECTIVE_START_DATE AND ASS3.EFFECTIVE_END_DATE
-- AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
AND PWC.DATE_FROM = (SELECT MAX(DATE_FROM) FROM PER_PHONES_V WHERE PERSON_ID = PWC.PERSON_ID AND PHONE_TYPE = PWC.PHONE_TYPE)

) T3
ON T.PERSON_ID=T3.P3
LEFT OUTER JOIN (SELECT
PER4.PERSON_ID AS P4,
PWC.PHONE_NUMBER AS NUM4,
PWC.PHONE_TYPE AS TYP4

FROM
PER_ALL_PEOPLE_F PER4,
PER_ALL_ASSIGNMENTS_F ASS4,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='W2'
AND PER4.PERSON_ID=ASS4.PERSON_ID
AND PWC.PERSON_ID = PER4.PERSON_ID
AND ASS4.ASSIGNMENT_TYPE IN ('E')
AND ASS4.LEGISLATION_CODE IN ('US','CA')
AND ASS4.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER4.EFFECTIVE_START_DATE AND PER4.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS4.EFFECTIVE_START_DATE AND ASS4.EFFECTIVE_END_DATE
-- AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
AND PWC.DATE_FROM = (SELECT MAX(DATE_FROM) FROM PER_PHONES_V WHERE PERSON_ID = PWC.PERSON_ID AND PHONE_TYPE = PWC.PHONE_TYPE)

) T4
ON T.PERSON_ID=T4.P4
LEFT OUTER JOIN (SELECT
PER5.PERSON_ID AS P5,
PWC.PHONE_NUMBER AS NUM5,
PWC.PHONE_TYPE AS TYP5

FROM
PER_ALL_PEOPLE_F PER5,
PER_ALL_ASSIGNMENTS_F ASS5,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='W3'
AND PER5.PERSON_ID=ASS5.PERSON_ID
AND PWC.PERSON_ID = PER5.PERSON_ID
AND ASS5.ASSIGNMENT_TYPE IN ('E')
AND ASS5.LEGISLATION_CODE IN ('US','CA')
AND ASS5.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER5.EFFECTIVE_START_DATE AND PER5.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS5.EFFECTIVE_START_DATE AND ASS5.EFFECTIVE_END_DATE
-- AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
AND PWC.DATE_FROM = (SELECT MAX(DATE_FROM) FROM PER_PHONES_V WHERE PERSON_ID = PWC.PERSON_ID AND PHONE_TYPE = PWC.PHONE_TYPE)

) T5
ON T.PERSON_ID=T5.P5
WHERE (T1.NUM1 IS NOT NULL OR T2.NUM2 IS NOT NULL OR T3.NUM3 IS NOT NULL OR T4.NUM4 IS NOT NULL OR T5.NUM5 IS NOT NULL OR T6.NUM6 IS NOT NULL)

-----

SELECT T.*,
CASE 
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5) IS NOT NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(NVL(T3.NUM3,T4.NUM4),T5.NUM5)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN ''
ELSE '' END AS PHONE1,
T3.NUM3 AS WORK_,
T4.NUM4 AS WORK_2,
T5.NUM5 AS WORK_3,
T2.NUM2 AS HOME,
T1.NUM1 AS CELL,
T6.NUM6 AS CELL_H,
CASE 
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(NVL(T3.NUM3,T4.NUM4),T5.NUM5)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NOT NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||NVL(T1.NUM1,T6.NUM6)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(NVL(T3.NUM3,T4.NUM4),T5.NUM5)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NOT NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN '"'||T2.NUM2||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NOT NULL
THEN '"'||NVL(NVL(T3.NUM3,T4.NUM4),T5.NUM5)||'"'
WHEN NVL(T1.TYP1,T6.TYP6) IS NULL AND T2.TYP2 IS NULL AND NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5)  IS NULL
THEN ''
ELSE '' END AS SMSPHONE,
(SELECT '"'||((P.PHONE_NUMBER) || 'X' ||(P.EXTENSION))||'"'  FROM PER_PHONES_V P WHERE P.PERSON_ID=T.PERSON_ID AND P.PHONE_TYPE IN NVL(NVL(T3.TYP3,T4.TYP4),T5.TYP5) AND TRUNC(SYSDATE) BETWEEN P.DATE_FROM AND P.DATE_TO AND P.PHONE_NUMBER IS NOT NULL AND P.EXTENSION IS NOT NULL) AS PHONE3
FROM (SELECT ASS.PERSON_ID,
'"'||'   '||'"' AS AGENCY,
'"'||PER.PERSON_NUMBER||'"' AS REF_CODE,
'"'||NAME.FIRST_NAME||'"' AS FIRST_NAME,
'"'||NAME.LAST_NAME||'"' AS LAST_NAME,
(SELECT '"'||PH.PHONE_NUMBER||'"'  FROM PER_PHONES_V PH WHERE PH.PHONE_TYPE ='H1' AND PH.PERSON_ID = PER.PERSON_ID AND TRUNC(SYSDATE) BETWEEN PH.DATE_FROM AND PH.DATE_TO AND PH.PHONE_NUMBER IS NOT NULL) AS PHONE2,
'' AS TERMINATED,
'"'||LOC.LOCATION_NAME||'"' AS GROUP_,
CASE WHEN (SELECT LOCATION_NAME
   FROM PER_ALL_ASSIGNMENTS_M PAA,
HR_LOCATIONS HL
  WHERE PAA.LOCATION_ID = HL.LOCATION_ID
AND PAA.PERSON_ID = ASS.PERSON_ID
AND PAA.ASSIGNMENT_TYPE IN ('E')
AND SUBSTR(PAA.EFFECTIVE_END_DATE,1,10) = SUBSTR(ASS.EFFECTIVE_START_DATE-1,1,10)
AND PAA.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND PAA.EFFECTIVE_LATEST_CHANGE = 'Y'
AND TRUNC(SYSDATE) BETWEEN HL.EFFECTIVE_START_DATE AND HL.EFFECTIVE_END_DATE) = LOC.LOCATION_NAME THEN ''
  ELSE (SELECT '"'||LOCATION_CODE||'"'
   FROM PER_ALL_ASSIGNMENTS_M PAA,
HR_LOCATIONS HL
  WHERE PAA.LOCATION_ID = HL.LOCATION_ID
AND PAA.PERSON_ID = ASS.PERSON_ID
AND PAA.ASSIGNMENT_TYPE IN ('E')
AND SUBSTR(PAA.EFFECTIVE_END_DATE,1,10) = SUBSTR(ASS.EFFECTIVE_START_DATE-1,1,10)
AND PAA.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND PAA.EFFECTIVE_LATEST_CHANGE = 'Y'
AND PAA.LAST_UPDATE_DATE BETWEEN TRUNC(SYSDATE-7) AND TRUNC(SYSDATE)
AND TRUNC(SYSDATE) BETWEEN HL.EFFECTIVE_START_DATE AND HL.EFFECTIVE_END_DATE)
END AS DEL_GROUP

FROM PER_ALL_PEOPLE_F PER,
PER_PERSON_NAMES_F NAME,
HR_LOCATIONS_ALL  LOC,
PER_ALL_ASSIGNMENTS_F ASS

WHERE 1=1
AND PER.PERSON_ID=ASS.PERSON_ID
AND NAME.PERSON_ID=PER.PERSON_ID
AND LOC.LOCATION_ID(+)=ASS.LOCATION_ID
AND NAME.NAME_TYPE='GLOBAL'
AND ASSIGNMENT_TYPE IN ('E')
AND ASS.LEGISLATION_CODE IN ('US','CA')
AND ASS.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND ASS.EFFECTIVE_LATEST_CHANGE = 'Y'
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE AND ASS.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE AND NAME.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN LOC.EFFECTIVE_START_DATE(+) AND LOC.EFFECTIVE_END_DATE(+)
ORDER BY PER.PERSON_NUMBER) T

LEFT OUTER JOIN (SELECT
PER1.PERSON_ID AS P1,
PW.PHONE_NUMBER AS NUM1,
PW.PHONE_TYPE AS TYP1

FROM
PER_ALL_PEOPLE_F PER1,
PER_ALL_ASSIGNMENTS_F ASS1,
PER_PHONES_V PW

WHERE
PW.PHONE_TYPE ='WM'
AND PER1.PERSON_ID=ASS1.PERSON_ID
AND PW.PERSON_ID = PER1.PERSON_ID
AND ASS1.ASSIGNMENT_TYPE IN ('E')
AND ASS1.LEGISLATION_CODE IN ('US','CA')
AND ASS1.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER1.EFFECTIVE_START_DATE AND PER1.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS1.EFFECTIVE_START_DATE AND ASS1.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PW.DATE_FROM AND PW.DATE_TO
) T1
ON T.PERSON_ID=T1.P1
LEFT OUTER JOIN (SELECT
PER6.PERSON_ID AS P6,
PWC.PHONE_NUMBER AS NUM6,
PWC.PHONE_TYPE AS TYP6

FROM
PER_ALL_PEOPLE_F PER6,
PER_ALL_ASSIGNMENTS_F ASS6,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='HM'
AND PER6.PERSON_ID=ASS6.PERSON_ID
AND PWC.PERSON_ID = PER6.PERSON_ID
AND ASS6.ASSIGNMENT_TYPE IN ('E')
AND ASS6.LEGISLATION_CODE IN ('US','CA')
AND ASS6.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER6.EFFECTIVE_START_DATE AND PER6.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS6.EFFECTIVE_START_DATE AND ASS6.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
) T6
ON T.PERSON_ID=T6.P6

LEFT OUTER JOIN (SELECT
PER2.PERSON_ID AS P2,
PH.PHONE_NUMBER AS NUM2,
PH.PHONE_TYPE AS TYP2

FROM
PER_ALL_PEOPLE_F PER2,
PER_ALL_ASSIGNMENTS_F ASS2,
PER_PHONES_V PH

WHERE
PH.PHONE_TYPE ='H1'
AND PER2.PERSON_ID=ASS2.PERSON_ID
AND PH.PERSON_ID = PER2.PERSON_ID
AND ASS2.ASSIGNMENT_TYPE IN ('E')
AND ASS2.LEGISLATION_CODE IN ('US','CA')
AND ASS2.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER2.EFFECTIVE_START_DATE AND PER2.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS2.EFFECTIVE_START_DATE AND ASS2.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PH.DATE_FROM AND PH.DATE_TO
) T2
ON T.PERSON_ID=T2.P2

LEFT OUTER JOIN (SELECT
PER3.PERSON_ID AS P3,
PWC.PHONE_NUMBER AS NUM3,
PWC.PHONE_TYPE AS TYP3

FROM
PER_ALL_PEOPLE_F PER3,
PER_ALL_ASSIGNMENTS_F ASS3,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='W1'
AND PER3.PERSON_ID=ASS3.PERSON_ID
AND PWC.PERSON_ID = PER3.PERSON_ID
AND ASS3.ASSIGNMENT_TYPE IN ('E')
AND ASS3.LEGISLATION_CODE IN ('US','CA')
AND ASS3.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER3.EFFECTIVE_START_DATE AND PER3.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS3.EFFECTIVE_START_DATE AND ASS3.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
) T3
ON T.PERSON_ID=T3.P3
LEFT OUTER JOIN (SELECT
PER4.PERSON_ID AS P4,
PWC.PHONE_NUMBER AS NUM4,
PWC.PHONE_TYPE AS TYP4

FROM
PER_ALL_PEOPLE_F PER4,
PER_ALL_ASSIGNMENTS_F ASS4,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='W2'
AND PER4.PERSON_ID=ASS4.PERSON_ID
AND PWC.PERSON_ID = PER4.PERSON_ID
AND ASS4.ASSIGNMENT_TYPE IN ('E')
AND ASS4.LEGISLATION_CODE IN ('US','CA')
AND ASS4.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER4.EFFECTIVE_START_DATE AND PER4.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS4.EFFECTIVE_START_DATE AND ASS4.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
) T4
ON T.PERSON_ID=T4.P4
LEFT OUTER JOIN (SELECT
PER5.PERSON_ID AS P5,
PWC.PHONE_NUMBER AS NUM5,
PWC.PHONE_TYPE AS TYP5

FROM
PER_ALL_PEOPLE_F PER5,
PER_ALL_ASSIGNMENTS_F ASS5,
PER_PHONES_V PWC

WHERE
PWC.PHONE_TYPE ='W3'
AND PER5.PERSON_ID=ASS5.PERSON_ID
AND PWC.PERSON_ID = PER5.PERSON_ID
AND ASS5.ASSIGNMENT_TYPE IN ('E')
AND ASS5.LEGISLATION_CODE IN ('US','CA')
AND ASS5.ASSIGNMENT_STATUS_TYPE IN ('ACTIVE')
AND TRUNC(SYSDATE) BETWEEN PER5.EFFECTIVE_START_DATE AND PER5.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS5.EFFECTIVE_START_DATE AND ASS5.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN PWC.DATE_FROM AND PWC.DATE_TO
) T5
ON T.PERSON_ID=T5.P5

WHERE (T1.NUM1 IS NULL AND T2.NUM2 IS NULL AND T3.NUM3 IS NULL AND T4.NUM4 IS NULL AND T5.NUM5 IS NULL AND T6.NUM6 IS NULL)

***********************************************************************************************************************************************

Interface ConcurExpense Feed_DM

SELECT
ASS.ASSIGNMENT_TYPE,
ASS.ASS_ATTRIBUTE9,
PER.PERSON_NUMBER AS EMPLOYEE_ID,
NAME.FIRST_NAME AS FIRST_NAME,
NAME.MIDDLE_NAMES AS MIDDLE_NAME,
NAME.LAST_NAME AS LAST_NAME,
(select distinct EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES WHERE PERSON_ID=PER.PERSON_ID AND EMAIL_TYPE='W1') as EMAIL_ADDR,
case when PER.PERSON_NUMBER in ('502928') THEN '998270'
else
(SELECT 
CASE WHEN PER.PERSON_NUMBER <> '998270' AND PERSON_NUMBER = '502928' THEN '998270'
     when PER.PERSON_NUMBER in ('998270') THEN '502928' ELSE PERSON_NUMBER END 
FROM PER_ALL_PEOPLE_F NA WHERE MAN.MANAGER_ID=NA.PERSON_ID AND TRUNC(SYSDATE) BETWEEN NA.EFFECTIVE_START_DATE AND NA.EFFECTIVE_END_DATE) end AS SUPERVISOR_ID,
(select distinct TO_CHAR(min(a.date_start),'YYYY-MM-DD') from per_periods_of_service a, per_all_assignments_m b where a.person_id=b.person_id and b.PERIOD_OF_SERVICE_ID=a.PERIOD_OF_SERVICE_ID and b.assignment_type='E' and b.person_id=ass.person_id) AS HIRE_DATE,
TO_CHAR(PPS.DATE_START,'YYYY-MM-DD') AS LATEST_HIRE_DATE,
case when PPS.ACTUAL_TERMINATION_DATE is null then 'NULL' 
when PPS.ACTUAL_TERMINATION_DATE is not null then TO_CHAR(PPS.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD') else '' end AS TERMINATION_DATE,

CASE WHEN PER.PERSON_NUMBER IN ('900377','991515','991630') THEN 'GBP' ELSE CUR.LOCAL_CURRENCY END AS CURRENCY,
--CUR.LOCAL_CURRENCY AS CURRENCY,
-- CASE WHEN FT.TERRITORY_SHORT_NAME='United States' THEN 'USA'
-- ELSE '' END AS COUNTRY,
FT.iso_TERRITORY_code AS COUNTRY,
LOC.COUNTRY as country_code,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,1,5) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS ACCOUNT,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,7,3) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS DEPTID_CF,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,19,4) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS OPERATING_UNIT,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,11,3) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS CHARTFIELD1,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,15,3) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS CHARTFIELD2,

ASS.WORK_AT_HOME

FROM
PER_ALL_PEOPLE_F PER,
PER_PERSON_NAMES_F NAME,
PER_ALL_ASSIGNMENTS_F ASS,
PER_ASSIGNMENT_SUPERVISORS_F MAN,
FND_TERRITORIES_VL FT,
HR_LOCATIONS_ALL LOC,
PER_PERIODS_OF_SERVICE PPS,
PER_JOBS JOB,

CMP_SALARY_V CUR,
PER_DEPARTMENTS DEPT

WHERE
PER.PERSON_ID=NAME.PERSON_ID(+)
AND PER.PERSON_ID=ASS.PERSON_ID(+)
AND ASS.ASSIGNMENT_ID=MAN.ASSIGNMENT_ID(+)
AND FT.TERRITORY_CODE(+)=LOC.COUNTRY
AND ASS.LOCATION_ID = LOC.LOCATION_ID(+)
AND ASS.PERIOD_OF_SERVICE_ID=PPS.PERIOD_OF_SERVICE_ID(+)
and ASS.PERSON_ID=pps.PERSON_ID(+)
AND ASS.JOB_ID=JOB.JOB_ID(+)

AND ASS.ORGANIZATION_ID=DEPT.ORGANIZATION_ID(+)

AND ASS.ASSIGNMENT_ID=CUR.ASSIGNMENT_ID(+)
AND NAME.NAME_TYPE = 'GLOBAL'
AND (ASS.assignment_type in ('E') or (ASS.assignment_type in ('C') and ASS.ASS_ATTRIBUTE9 = 'Y' and ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE'))
AND ASS.LEGISLATION_CODE NOT IN ('CN')

---and FT.Language(+)='US'
AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E','C') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)
AND MAN.MANAGER_TYPE(+) = 'LINE_MANAGER'
AND (CUR.DATE_FROM in (SELECT MAX(DATE_FROM) FROM CMP_SALARY_V WHERE ASSIGNMENT_ID=CUR.ASSIGNMENT_ID)  or CUR.DATE_FROM is null)
AND (JOB.JOB_CODE NOT IN ('UR0901','UR0101','UR0102','US0201') or JOB.JOB_CODE IS NULL)
AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE(+) AND NAME.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE AND PER.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE(+) AND ASS.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN MAN.EFFECTIVE_START_DATE(+) AND MAN.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN DEPT.EFFECTIVE_START_DATE(+) AND DEPT.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN LOC.EFFECTIVE_START_DATE(+) AND LOC.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN JOB.EFFECTIVE_START_DATE(+) AND JOB.EFFECTIVE_END_DATE(+)

-- AND TRUNC(SYSDATE) BETWEEN CUR.DATE_FROM(+) AND CUR.DATE_TO(+)


union

SELECT DISTINCT
ASS.ASSIGNMENT_TYPE,
ASS.ASS_ATTRIBUTE9,
PER.PERSON_NUMBER AS EMPLOYEE_ID,
NAME.FIRST_NAME AS FIRST_NAME,
NAME.MIDDLE_NAMES AS MIDDLE_NAME,
NAME.LAST_NAME AS LAST_NAME,
(select distinct EMAIL_ADDRESS FROM PER_EMAIL_ADDRESSES WHERE PERSON_ID=PER.PERSON_ID AND EMAIL_TYPE='W1') as EMAIL_ADDR,
case when PER.PERSON_NUMBER in ('502928') THEN '998270'
else
(SELECT 
CASE WHEN PER.PERSON_NUMBER <> '998270' AND PERSON_NUMBER = '502928' THEN '998270'
     when PER.PERSON_NUMBER in ('998270') THEN '502928' ELSE PERSON_NUMBER END 
FROM PER_ALL_PEOPLE_F NA WHERE MAN.MANAGER_ID=NA.PERSON_ID AND TRUNC(SYSDATE) BETWEEN NA.EFFECTIVE_START_DATE AND NA.EFFECTIVE_END_DATE) end AS SUPERVISOR_ID,


(select distinct TO_CHAR(min(a.date_start),'YYYY-MM-DD') from per_periods_of_service a, per_all_assignments_m b where a.person_id=b.person_id and b.PERIOD_OF_SERVICE_ID=a.PERIOD_OF_SERVICE_ID and b.assignment_type='E' and b.person_id=ass.person_id) AS HIRE_DATE,
TO_CHAR(PPS.DATE_START,'YYYY-MM-DD') AS LATEST_HIRE_DATE,
case when PPS.ACTUAL_TERMINATION_DATE is null then 'NULL' 
when PPS.ACTUAL_TERMINATION_DATE is not null then TO_CHAR(PPS.ACTUAL_TERMINATION_DATE,'YYYY-MM-DD') else '' end AS TERMINATION_DATE,

CASE WHEN PER.PERSON_NUMBER IN ('900377','991515','991630') THEN 'GBP' ELSE CUR.LOCAL_CURRENCY END AS CURRENCY,
--CUR.LOCAL_CURRENCY AS CURRENCY,
FT.iso_TERRITORY_code AS COUNTRY,
LOC.COUNTRY as country_code,

(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,1,5) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS ACCOUNT,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,7,3) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS DEPTID_CF,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,19,4) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS OPERATING_UNIT,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,11,3) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS CHARTFIELD1,
(SELECT DISTINCT SUBSTR(HROF.ORG_INFORMATION2,15,3) FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND TRUNC(SYSDATE) BETWEEN HROF.EFFECTIVE_START_DATE(+) AND HROF.EFFECTIVE_END_DATE(+) AND TRUNC(SYSDATE) BETWEEN HROU.EFFECTIVE_START_DATE(+) AND HROU.EFFECTIVE_END_DATE(+) AND ROWNUM=1) AS CHARTFIELD2,

ASS.WORK_AT_HOME

FROM
PER_ALL_PEOPLE_F PERNUM,
PER_ALL_ASSIGNMENTS_F ASS1,
PER_ASSIGNMENT_SUPERVISORS_F MAN1,
PER_ALL_PEOPLE_F PER,
PER_PERSON_NAMES_F NAME,
PER_ALL_ASSIGNMENTS_F ASS,
PER_JOBS JOB,
FND_TERRITORIES_VL FT,
HR_LOCATIONS_ALL LOC,
PER_ASSIGNMENT_SUPERVISORS_F MAN,

PER_DEPARTMENTS DEPT,
CMP_SALARY_V CUR,
PER_PERIODS_OF_SERVICE PPS



WHERE
PERNUM.PERSON_ID=ASS1.PERSON_ID(+)
AND ASS1.ASSIGNMENT_ID=MAN1.ASSIGNMENT_ID(+)
AND ASS1.PERSON_ID=MAN1.PERSON_ID(+)
AND MAN1.MANAGER_ID=PER.PERSON_ID(+)
AND PER.PERSON_ID=NAME.PERSON_ID(+)
AND PER.PERSON_ID=ASS.PERSON_ID(+)
AND ASS.JOB_ID=JOB.JOB_ID(+)
AND FT.TERRITORY_CODE(+)=LOC.COUNTRY
AND ASS.LOCATION_ID = LOC.LOCATION_ID(+)

AND ASS.ASSIGNMENT_ID=MAN.ASSIGNMENT_ID(+)


AND ASS.PERIOD_OF_SERVICE_ID=PPS.PERIOD_OF_SERVICE_ID(+)
and ASS.PERSON_ID=pps.PERSON_ID(+)
AND ASS.ORGANIZATION_ID=DEPT.ORGANIZATION_ID(+)
AND ASS.ASSIGNMENT_ID=CUR.ASSIGNMENT_ID(+)

AND NAME.NAME_TYPE = 'GLOBAL'

AND (ASS.assignment_type in ('E') or (ASS.assignment_type in ('C') and ASS.ASS_ATTRIBUTE9 = 'Y' and ASS.ASSIGNMENT_STATUS_TYPE='ACTIVE'))



----and FT.Language(+)='US'
AND ASS.ASSIGNMENT_SEQUENCE = (SELECT DISTINCT MAX(ASSIGNMENT_SEQUENCE) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID=ASS.PERSON_ID AND ASSIGNMENT_TYPE IN ('E','C') AND TRUNC(SYSDATE) BETWEEN EFFECTIVE_START_DATE AND EFFECTIVE_END_DATE)

AND ASS.PERSON_ID IN (SELECT DISTINCT A.MANAGER_ID 

FROM PER_ASSIGNMENT_SUPERVISORS_F A, PER_ALL_ASSIGNMENTS_F B 

WHERE A.PERSON_ID = B.PERSON_ID 

AND A.ASSIGNMENT_ID = B.ASSIGNMENT_ID 

AND TRUNC(SYSDATE) BETWEEN A.EFFECTIVE_START_DATE AND A.EFFECTIVE_END_DATE

AND TRUNC(SYSDATE) BETWEEN B.EFFECTIVE_START_DATE AND B.EFFECTIVE_END_DATE AND B.ASSIGNMENT_TYPE = 'E' )

AND MAN1.MANAGER_TYPE(+) = 'LINE_MANAGER'
AND MAN.MANAGER_TYPE(+) = 'LINE_MANAGER'

and ASS.LEGISLATION_CODE  IN ('CN')
and ASS1.LEGISLATION_CODE NOT IN ('CN')
AND (CUR.DATE_FROM in (SELECT MAX(DATE_FROM) FROM CMP_SALARY_V WHERE ASSIGNMENT_ID=CUR.ASSIGNMENT_ID)  or CUR.DATE_FROM is null)
AND (JOB.JOB_CODE NOT IN ('UR0901','UR0101','UR0102','US0201') or JOB.JOB_CODE IS NULL)
AND TRUNC(SYSDATE) BETWEEN PERNUM.EFFECTIVE_START_DATE AND PERNUM.EFFECTIVE_END_DATE
AND TRUNC(SYSDATE) BETWEEN ASS1.EFFECTIVE_START_DATE(+) AND ASS1.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN MAN1.EFFECTIVE_START_DATE(+) AND MAN1.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN NAME.EFFECTIVE_START_DATE(+) AND NAME.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN DEPT.EFFECTIVE_START_DATE(+) AND DEPT.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN PER.EFFECTIVE_START_DATE(+) AND PER.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN ASS.EFFECTIVE_START_DATE(+) AND ASS.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN LOC.EFFECTIVE_START_DATE(+) AND LOC.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN MAN.EFFECTIVE_START_DATE(+) AND MAN.EFFECTIVE_END_DATE(+)
AND TRUNC(SYSDATE) BETWEEN JOB.EFFECTIVE_START_DATE(+) AND JOB.EFFECTIVE_END_DATE(+)
-- AND TRUNC(SYSDATE) BETWEEN CUR.DATE_FROM(+) AND CUR.DATE_TO(+)

*************************************************************************************************

Popular posts from this blog

face1

--NewHireRehire_AAP_DM SELECT DISTINCT PER.PERSON_NUMBER, ASSIGNMENT_SEQUENCE, NAME.DISPLAY_NAME AS NAME, case when ASS.REASON_CODE in ('REHIRE') then ' ' else TO_CHAR((SELECT MIN(effective_START_date) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID = ASS.PERSON_ID and ASSIGNMENT_TYPE in ('E')                                           AND ACTION_CODE in ('HIRE','ADD_AN_EMPLOYEE','HIRE_ADD_WORK_RELATION') AND EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE  ),'MM/DD/YYYY') end as HIRE_DATE,  case when ASS.REASON_CODE in ('REHIRE') then           to_char((SELECT MAX(effective_START_date) FROM PER_ALL_ASSIGNMENTS_M WHERE PERSON_ID = ASS.PERSON_ID               AND (reason_CODE IN ('REHIRE','REHIRE_WKR') or (ACTION_CODE in ('REHIRE')))  AND EFFECTIVE_START_DATE = ASS.EFFECTIVE_START_DATE),'MM/DD...
Post a Comment
Read more

face3

--SALES AND PROMOTION SELECT DISTINCT PER.PERSON_NUMBER AS ID, TO_CHAR(NEW_ASS.CREATION_DATE,'MM/DD/YYYY') AS ACTION_DATE, TO_CHAR(NEW_ASS.Effective_start_date,'MM/DD/YYYY') AS EFFECTIVE_DATE, NEW_ASS.ACTION_CODE AS ACTION, NEW_ASS.REASON_CODE AS ACTION_REASON, OLD_LEG.NAME AS LEGAL_ENTITY, OLD_ASS.WORK_AT_HOME AS OLD_REMOTE_INDICATOR, (SELECT LOC.LOCATION_NAME FROM HR_LOCATIONS_ALL_F_VL  LOC WHERE  LOC.LOCATION_ID =OLD_ASS.LOCATION_ID AND TRUNC(sysdate) between LOC.EFFECTIVE_START_DATE and LOC.EFFECTIVE_END_DATE ) AS OLD_PHYSICAL_LOC, --OLD_LOC.LOCATION_NAME AS OLD_PHYSICAL_LOC, (SELECT DISTINCT HROF.ORG_INFORMATION1 FROM HR_ORGANIZATION_INFORMATION_F HROF, HR_ALL_ORGANIZATION_UNITS_X HROU WHERE HROF.ORGANIZATION_ID = HROU.ORGANIZATION_ID AND HROF.ORGANIZATION_ID=OLD_ASS.ORGANIZATION_ID AND (HROF.ORG_INFORMATION_CONTEXT = 'Division' OR HROF.ORG_INFORMATION_CONTEXT IS NULL) AND OLD_ASS.EFFECTIVE_START_DATE BETWEEN HROF.EFFECTIVE_START_DATE(+) AND H...
Post a Comment
Read more