VIEW: APPS.BEN_CWB_EQUITY_EDGE_ACT_INFO_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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