VIEW: APPS.OKX_EMPLOYEE_DETAILS_V

Source

View Text - Preformatted

SELECT EMP.PERSON_ID ID1, to_char(EMP.EFFECTIVE_START_DATE) || '#' || to_char(EFFECTIVE_END_DATE) ID2, EMP.FULL_NAME NAME, NULL DESCRIPTION, EMP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER, EMP.EFFECTIVE_START_DATE START_DATE_ACTIVE, EMP.EFFECTIVE_END_DATE END_DATE_ACTIVE, decode(sign(nvl(emp.effective_start_date,sysdate) -sysdate),0,'I', decode(sign(nvl(emp.effective_end_date,sysdate) -sysdate),-1,'I', decode(sign(nvl(emp.effective_start_date,sysdate)- sysdate),1,'I','A'))) STATUS, EMP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID, EMP.PERSON_TYPE_ID PERSON_TYPE_ID, EMP.LAST_NAME LAST_NAME, EMP.FIRST_NAME FIRST_NAME, EMP.START_DATE START_DATE, EMP.EMAIL_ADDRESS EMAIL_ADDRESS, EMP.INTERNAL_LOCATION INTERNAL_LOCATION, NULL SMALL_BUSINESS_FLAG, NULL WOMEN_OWNED_FLAG, NULL MINORITY_GROUP_LOOKUP, EMP.WORK_TELEPHONE WORK_TELEPHONE, EMP.NATIONALITY NATIONALITY, EMP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER, ADDR.ADDRESS_LINE1 ADDRESS_LINE1, ADDR.ADDRESS_LINE2 ADDRESS_LINE2, ADDR.ADDRESS_LINE3 ADDRESS_LINE3, ADDR.ADDRESS_TYPE ADDRESS_TYPE, ADDR.TOWN_OR_CITY TOWN_OR_CITY, ADDR.POSTAL_CODE POSTAL_CODE, ADDR.COUNTRY COUNTRY, ADDR.DATE_FROM DATE_FROM, ADDR.DATE_TO DATE_TO FROM PER_ALL_PEOPLE_F EMP,PER_ADDRESSES ADDR WHERE EMP.PERSON_ID = ADDR.PERSON_ID(+) AND (ADDR.DATE_TO >= SYSDATE OR ADDR.DATE_TO IS NULL) AND (EMP.EFFECTIVE_END_DATE >= SYSDATE OR EMP.EFFECTIVE_END_DATE IS NULL) AND (ADDR.PRIMARY_FLAG IS NULL OR ADDR.PRIMARY_FLAG = 'Y')

View Text - HTML Formatted

SELECT EMP.PERSON_ID ID1
, TO_CHAR(EMP.EFFECTIVE_START_DATE) || '#' || TO_CHAR(EFFECTIVE_END_DATE) ID2
, EMP.FULL_NAME NAME
, NULL DESCRIPTION
, EMP.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, EMP.EFFECTIVE_START_DATE START_DATE_ACTIVE
, EMP.EFFECTIVE_END_DATE END_DATE_ACTIVE
, DECODE(SIGN(NVL(EMP.EFFECTIVE_START_DATE
, SYSDATE) -SYSDATE)
, 0
, 'I'
, DECODE(SIGN(NVL(EMP.EFFECTIVE_END_DATE
, SYSDATE) -SYSDATE)
, -1
, 'I'
, DECODE(SIGN(NVL(EMP.EFFECTIVE_START_DATE
, SYSDATE)- SYSDATE)
, 1
, 'I'
, 'A'))) STATUS
, EMP.BUSINESS_GROUP_ID BUSINESS_GROUP_ID
, EMP.PERSON_TYPE_ID PERSON_TYPE_ID
, EMP.LAST_NAME LAST_NAME
, EMP.FIRST_NAME FIRST_NAME
, EMP.START_DATE START_DATE
, EMP.EMAIL_ADDRESS EMAIL_ADDRESS
, EMP.INTERNAL_LOCATION INTERNAL_LOCATION
, NULL SMALL_BUSINESS_FLAG
, NULL WOMEN_OWNED_FLAG
, NULL MINORITY_GROUP_LOOKUP
, EMP.WORK_TELEPHONE WORK_TELEPHONE
, EMP.NATIONALITY NATIONALITY
, EMP.NATIONAL_IDENTIFIER NATIONAL_IDENTIFIER
, ADDR.ADDRESS_LINE1 ADDRESS_LINE1
, ADDR.ADDRESS_LINE2 ADDRESS_LINE2
, ADDR.ADDRESS_LINE3 ADDRESS_LINE3
, ADDR.ADDRESS_TYPE ADDRESS_TYPE
, ADDR.TOWN_OR_CITY TOWN_OR_CITY
, ADDR.POSTAL_CODE POSTAL_CODE
, ADDR.COUNTRY COUNTRY
, ADDR.DATE_FROM DATE_FROM
, ADDR.DATE_TO DATE_TO
FROM PER_ALL_PEOPLE_F EMP
, PER_ADDRESSES ADDR
WHERE EMP.PERSON_ID = ADDR.PERSON_ID(+)
AND (ADDR.DATE_TO >= SYSDATE OR ADDR.DATE_TO IS NULL)
AND (EMP.EFFECTIVE_END_DATE >= SYSDATE OR EMP.EFFECTIVE_END_DATE IS NULL)
AND (ADDR.PRIMARY_FLAG IS NULL OR ADDR.PRIMARY_FLAG = 'Y')