GPFR_EST_MEM_VW

(SQL View)
 Index Back

Estab-Member Prompt View

Prompt View for Contact Definition

SELECT DISTINCT C.ESTABID ,A.MEMBERSHIP_NBR_FRA FROM PS_CNTRCT_FUND_FRA A , PS_CPNY_CNTRCT_FRA B , PS_ESTAB_TBL C WHERE A.MEMBERSHIP_NBR_FRA=B.MEMBERSHIP_NBR_FRA AND B.COMPANY=C.COMPANY AND A.EFFDT=( SELECT MAX(AA.EFFDT) FROM PS_CNTRCT_FUND_FRA AA ,PS_CPNY_CNTRCT_FRA BB WHERE AA.MEMBERSHIP_NBR_FRA=A.MEMBERSHIP_NBR_FRA AND B.COMPANY=BB.COMPANY AND BB.EFFDT=A.EFFDT AND AA.EFFDT<=%CurrentDateIn AND AA.EFF_STATUS='A' ) UNION SELECT DISTINCT C.ESTABID ,A.MEMBERSHIP_NBR_FRA FROM PS_CNTRCT_FUND_FRA A , PS_ESTB_CNTRCT_FRA D , PS_ESTAB_TBL C WHERE A.MEMBERSHIP_NBR_FRA=D.MEMBERSHIP_NBR_FRA AND D.ESTABID=C.ESTABID AND A.EFFDT=( SELECT MAX(AA.EFFDT) FROM PS_CNTRCT_FUND_FRA AA ,PS_ESTB_CNTRCT_FRA DD WHERE AA.MEMBERSHIP_NBR_FRA=A.MEMBERSHIP_NBR_FRA AND D.ESTABID=DD.ESTABID AND D.EFFDT=A.EFFDT AND AA.EFFDT<=%CurrentDateIn AND AA.EFF_STATUS='A') UNION SELECT DISTINCT C.ESTABID ,A.MEMBERSHIP_NBR_FRA FROM PS_CNTRCT_FUND_FRA A , PS_EMPL_CNTRCT_FRA D , PS_JOB C WHERE A.MEMBERSHIP_NBR_FRA=D.MEMBERSHIP_NBR_FRA AND D.EMPLID=C.EMPLID AND A.EFFDT=( SELECT MAX(AA.EFFDT) FROM PS_CNTRCT_FUND_FRA AA ,PS_EMPL_CNTRCT_FRA DD WHERE AA.MEMBERSHIP_NBR_FRA=A.MEMBERSHIP_NBR_FRA AND DD.EMPLID=D.EMPLID AND DD.EFFDT>=AA.EFFDT AND AA.EFFDT<=%CurrentDateIn AND AA.EFF_STATUS='A')

# PeopleSoft Field Name PeopleSoft Field Type Database Column Type Description
1 ESTABID Character(12) VARCHAR2(12) NOT NULL Establishment ID
2 MEMBERSHIP_NBR_FRA Character(20) VARCHAR2(20) NOT NULL Membership Number - France