View: BEN_CWB_EQUITY_EDGE_ACT_INFO_V

Product:	BEN - Advanced Benefits
Description:	Stock Account information.
Implementation/DBA Data:	APPS.BEN_CWB_EQUITY_EDGE_ACT_INFO_V

View Text

SELECT PER2.LAST_NAME LAST_NAME
, PER2.FIRST_NAME FIRST_NAME
, PER2.MIDDLE_NAMES MIDDLE_NAME
, PER2.EMPLOYEE_NUMBER EMPLOYEE_NUMBER
, DECODE(COUNTRY
, 'US'
, PER2.NATIONAL_IDENTIFIER
, NULL) SSN
, ADR.ADDRESS_LINE1 ADDRESS_LINE1
, ADR.ADDRESS_LINE2 ADDRESS_LINE2
, ADR.ADDRESS_LINE3 ADDRESS_LINE3
, DECODE(ADR.COUNTRY
, 'US'
, ADR.TOWN_OR_CITY
, (ADR.TOWN_OR_CITY || DECODE(ADR.REGION_1
, NULL
, NULL
, '
, ') || ADR.REGION_1 || DECODE(ADR.REGION_2
, NULL
, NULL
, '
, ') || ADR.REGION_2 || DECODE(ADR.REGION_3
, NULL
, NULL
, '
, ') || ADR.REGION_3 || DECODE(ADR.POSTAL_CODE
, NULL
, NULL
, '
, ') || ADR.POSTAL_CODE) ) US_CITY_OR_INTNL_ADDR
, DECODE(ADR.COUNTRY
, 'US'
, ADR.REGION_2
, NULL) US_STATE
, ADR.COUNTRY EMP_ADDR_COUNTRY
, DECODE(ADR.COUNTRY
, 'US'
, ADR.POSTAL_CODE
, NULL) US_ZIP_CODE
, SUBSTR(JOB.NAME
, 1
, 30) TITLE_1
, SUBSTR(JOB.NAME
, 31
, 30) TITLE_2
, BG.LEGISLATION_CODE BUS_GRP_COUNTRY
, ORG.NAME ORG_NAME
, PER2.BUSINESS_GROUP_ID USR_DFND_3
, PER2.PERSON_ID PERSON_ID
, PPF.FULL_NAME WS_MGR_FULL_NAME
, NULL USR_DFND_6
, 'Y' EMPLOYEE_FLAG
, PER2.START_DATE HIRE_DATE
, PER2.EMAIL_ADDRESS EMAIL
, NULL DIRECTOR_FLAG
, NULL OFFICER_FLAG
, NULL TEN_PCT_OWNER_FLAG
, NULL OTH_INSIDER_FLAG
, POPL.ENRT_PERD_ID
, PPF.PERSON_ID BIND_PERSON_ID
FROM BEN_PIL_ELCTBL_CHC_POPL POPL
, PER_ALL_PEOPLE_F PPF
, BEN_PER_IN_LER PIL
, BEN_ELIG_PER_ELCTBL_CHC CHC2
, BEN_ENRT_RT ENR
, BEN_PRTT_RT_VAL PRT
, PER_ALL_PEOPLE_F PER2
, PER_ALL_ASSIGNMENTS_F ASG
, PER_JOBS JOB
, HR_ORGANIZATION_UNITS ORG
, PER_BUSINESS_GROUPS_PERF BG
, PER_ADDRESSES ADR
WHERE CHC2.WS_MGR_ID = PPF.PERSON_ID
AND SYSDATE BETWEEN PPF.EFFECTIVE_START_DATE
AND PPF.EFFECTIVE_END_DATE
AND CHC2.ELIG_PER_ELCTBL_CHC_ID = ENR.ELIG_PER_ELCTBL_CHC_ID
AND ENR.ACTY_TYP_CD = 'CWBWS'
AND ENR.PRTT_RT_VAL_ID = PRT.PRTT_RT_VAL_ID (+)
AND PRT.RT_VAL > 0
AND CHC2.PER_IN_LER_ID = PIL.PER_IN_LER_ID
AND CHC2.ASSIGNMENT_ID = ASG.ASSIGNMENT_ID
AND CHC2.ELIG_FLAG = 'Y'
AND CHC2.ELCTBL_FLAG = 'Y'
AND SYSDATE BETWEEN ASG.EFFECTIVE_START_DATE
AND ASG.EFFECTIVE_END_DATE
AND ASG.JOB_ID = JOB.JOB_ID (+)
AND ASG.ORGANIZATION_ID = ORG.ORGANIZATION_ID (+) /*
AND ADR.PERSON_ID = PER2.PERSON_ID
AND ADR.PRIMARY_FLAG = 'Y'
AND SYSDATE BETWEEN ADR.DATE_FROM
AND NVL(SYSDATE
, ADR.DATE_TO) */
AND ADR.PERSON_ID(+) = PER2.PERSON_ID
AND ADR.PRIMARY_FLAG(+) = 'Y'
AND SYSDATE BETWEEN ADR.DATE_FROM(+)
AND NVL(ADR.DATE_TO(+)
, SYSDATE)
AND PIL.PER_IN_LER_STAT_CD IN ('STRTD'
, 'PROCD')
AND PIL.PERSON_ID = PER2.PERSON_ID
AND SYSDATE BETWEEN PER2.EFFECTIVE_START_DATE
AND PER2.EFFECTIVE_END_DATE
AND PIL.PER_IN_LER_ID = POPL.PER_IN_LER_ID
AND BG.BUSINESS_GROUP_ID = PER2.BUSINESS_GROUP_ID

Columns