CREATE or REPLACE view "ECISDRDM"."BENE_VW" (
receipt_number,
service_center,
form_number,
a_number,
ssn,
last_name,
first_name,
middle_name,
date_of_birth,
sex,
country_of_birth,
country_of_citizenship,
country_of_residence,
street,
city,
state,
zip,
province,
postal_code,
country,
last_arrival,
prty_typ_id,
mig_filename)
AS SELECT DISTINCT (
ap.receipt_number,
scc.svc_ctr_crc_cd,
f.frm_nbr_std_cd,
bene.a_nbr,
bene.ssn,
bene.last_nm,
bene.frst_nm,
bene.mid_nm,
bene.dob_id,
bene.gndr,
bcf.bene_cntry_of_brth_id,
ctry.cntry_st_5_dgt_src_cd,
bcf.bene_cntry_of_rsdc_id,
bene.addr_1,
bene.cty,
bene.st_prvn,
bene.pstl_cd,
bene.st_prvn,
bene.pstl_cd,
bene.cntry,
bene.last_arrival,
bene.prty_typ_id,
bene.mig_filename)
FROM "ECISDRDM"."APPLICATION_CDIM" ap
INNER JOIN "ECISDRDM"."BNFT_CURR_FACT" bcf ON (ap.application_id = bcf.application_id)
INNER JOIN "ECISDRDM"."PRTY_CDIM" bene ON (bcf.bene_id = bene.prty_id)
INNER JOIN "ECISDRDM"."CNTRY_ST_CDIM" ctry ON (bcf.bene_cntry_of_brth_id = ctry.cntry_st_id)
INNER JOIN "ECISDRDM"."SVC_CTR_CDIM" scc ON (scc.svc_ctr_id = bcf.svc_ctr_id)
INNER JOIN "ECISDRDM"."FRM_CDIM" f ON (f.frm_id = bcf.frm_id)
WHERE bene.prty_typ_id = 1;