STUDENT_ACAD_LEVELS_VIEW:

USE [coll18_production]
GO

/****** Object:  View [dbo].[STUDENT_ACAD_LEVELS_VIEW]    Script Date: 06/29/2012 11:59:01 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE VIEW
  [dbo].[STUDENT_ACAD_LEVELS_VIEW]
AS SELECT
  LEFT(STUDENT_ACAD_LEVELS_ID,CHARINDEX('*',STUDENT_ACAD_LEVELS_ID+'*')-1) as STA_STUDENT,
  LEFT(SUBSTRING(STUDENT_ACAD_LEVELS_ID,(CHARINDEX('*',STUDENT_ACAD_LEVELS_ID+'*')+1),999),CHARINDEX('*',SUBSTRING(STUDENT_ACAD_LEVELS_ID,(CHARINDEX('*',STUDENT_ACAD_LEVELS_ID+'*')+1),999)+'*')-1) as STA_ACAD_LEVEL,
  *
FROM
  dbo.STUDENT_ACAD_LEVELS WITH (NOLOCK)

GO

STUDENT_ACAD_PROGRAMS_VIEW:

USE [coll18_production]
GO

/****** Object:  View [dbo].[STUDENT_ACAD_PROGRAMS_VIEW]    Script Date: 06/29/2012 11:59:51 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE view [dbo].[STUDENT_ACAD_PROGRAMS_VIEW] 
as
select left(stp.STUDENT_PROGRAMS_ID,7) 'STUDENT_ID',
substring(stp.STUDENT_PROGRAMS_ID, 9, LEN(stp.STUDENT_PROGRAMS_ID)) 'STP_ACADEMIC_PROGRAM',
isnull(stp.STPR_TITLE,programs.ACPG_TITLE) 'STP_PROGRAM_TITLE',
stp.STPR_CATALOG 'STP_CATALOG',
stp.STPR_DEPT 'STP_DEPT',
stp.STPR_DIVISION 'STP_DIVISION',
stp.STPR_SCHOOL 'STP_SCHOOL',
stp.STPR_ANT_CMPL_DATE 'STP_ANT_CMPL_DATE',
progstatuscodes.VAL_EXTERNAL_REPRESENTATION 'STP_CURRENT_STATUS',
stp_statuses.PROG_STATUS_DATE 'STP_CURRENT_STATUS_DATE',
stp_dates.STPR_START_DATE 'STP_START_DATE',
stp_dates.STPR_END_DATE 'STP_END_DATE',
case when STPR_END_DATE is null then 'Y' else NULL end 'STP_PROGRAM_ACTIVE',
class.CLS_DESC 'STUDENT_CLASS_LEVEL',
prog_ls1.ACPG_MAJORS 'STP_MAJOR1',
prog_ls2.ACPG_MAJORS 'STP_MAJOR2',
stp_addnl_majors1.STPR_ADDNL_MAJORS 'STP_ADDNL_MAJOR1',
stp_addnl_majors2.STPR_ADDNL_MAJORS 'STP_ADDNL_MAJOR2',
stp_addnl_majors3.STPR_ADDNL_MAJORS 'STP_ADDNL_MAJOR3',
prog_ls1.ACPG_MINORS 'STP_MINOR1',
prog_ls2.ACPG_MINORS 'STP_MINOR2',
stp_addnl_minors1.STPR_MINORS 'STP_ADDNL_MINOR1',
stp_addnl_minors2.STPR_MINORS 'STP_ADDNL_MINOR2',
stp_addnl_minors3.STPR_MINORS 'STP_ADDNL_MINOR3',
prog_ls1.ACPG_SPECIALIZATIONS 'STP_SPECIALIZATION1',
prog_ls2.ACPG_SPECIALIZATIONS 'STP_SPECIALIZATION2',
prog_ls3.ACPG_SPECIALIZATIONS 'STP_SPECIALIZATION3',
stp_addnl_specs1.STPR_SPECIALIZATIONS 'STP_ADDNL_SPECIALIZATION1',
stp_addnl_specs2.STPR_SPECIALIZATIONS 'STP_ADDNL_SPECIALIZATION2',
stp_addnl_specs3.STPR_SPECIALIZATIONS 'STP_ADDNL_SPECIALIZATION3',
prog_ls1.ACPG_CCDS 'STP_CCDS1',
prog_ls2.ACPG_CCDS 'STP_CCDS2',
prog_ls3.ACPG_CCDS 'STP_CCDS3',
stp_addnl_ccds1.STPR_CCDS 'STP_ADDNL_CCD1',
stp_addnl_ccds2.STPR_CCDS 'STP_ADDNL_CCD2',
stp_addnl_ccds3.STPR_CCDS 'STP_ADDNL_CCD3',
case when programs.ACPG_DEGREE is not null then 'Y' else 'N' end as STP_DEGREE_GRANTING_FLAG,
programs.ACPG_DEGREE 'STP_DEGREE',
programs.ACPG_ALLOW_GRADUATION_FLAG 'STP_ALLOW_GRADUATION_FLAG',
programs.ACPG_ACAD_LEVEL 'STP_ACAD_LEVEL',
stp.STPR_LOCATION 'STP_LOCATION',
stp.STPR_EVAL_DATE 'STP_EVAL_DATE',
stp.STPR_EVAL_STATUS 'STP_EVAL_STATUS',
stp.STPR_EVAL_COMBINED_CRED 'STP_EVAL_COMBINED_CREDITS',
stp.STPR_EVAL_COMBINED_GPA 'STP_EVAL_COMBINED_GPA',
stp_major_eval1.STPR_EVAL_MAJORS 'STP_EVAL_MAJOR1',
stp_major_eval1.STPR_EVAL_MAJOR_GPA 'STP_EVAL_MAJOR1_GPA',
stp_major_eval1.STPR_EVAL_MAJOR_CRED_TOT 'STP_EVAL_MAJOR1_CRED_TOTALS',
stp_major_eval2.STPR_EVAL_MAJORS 'STP_EVAL_MAJOR2',
stp_major_eval2.STPR_EVAL_MAJOR_GPA 'STP_EVAL_MAJOR2_GPA',
stp_major_eval2.STPR_EVAL_MAJOR_CRED_TOT 'STP_EVAL_MAJOR2_CRED_TOTALS',
stp_major_eval3.STPR_EVAL_MAJORS 'STP_EVAL_MAJOR3',
stp_major_eval3.STPR_EVAL_MAJOR_GPA 'STP_EVAL_MAJOR3_GPA',
stp_major_eval3.STPR_EVAL_MAJOR_CRED_TOT 'STP_EVAL_MAJOR3_CRED_TOTALS',
person.LAST_NAME 'STUDENT_LAST_NAME',
person.FIRST_NAME 'STUDENT_FIRST_NAME',
person.MIDDLE_NAME 'STUDENT_MIDDLE_NAME',
person.PREFERRED_NAME 'STUDENT_PREFERRED_NAME',
person.NICKNAME 'STUDENT_NICKNAME',
person.GENDER 'STUDENT_GENDER',
person.BIRTH_DATE 'STUDENT_BIRTH_DATE',
dbo.VW_S_CALC_AGE(person.BIRTH_DATE, GETDATE()) as 'STUDENT_AGE',
person.CITIZENSHIP 'STUDENT_CITIZENSHIP',
/* Begin REG2010STIPE Changes 05/07/10 Parrish Fessler
Removed the output of the old ETHNIC field to be replaced with new columns for update races and ethnicies
ethnics.ETH_DESC 'STUDENT_ETHNIC', */
CASE WHEN IS_FPER_ALIEN = 'Y' OR IS_ETHNIC_ALIEN = 'Y' OR IS_RACES_ALIEN = 'Y' THEN 'Non-Resident Alien' 
	WHEN IS_HISPANIC = 'Y' THEN 'Hispanic/Latino' 
	WHEN RACE_COUNT >= '2' THEN 'Two or More Races'
	WHEN RACE_MAX = '1' THEN 'American Indian' 
	WHEN RACE_MAX = '2' THEN 'Asian'
	WHEN RACE_MAX = '3' THEN 'Black or African American'
	WHEN RACE_MAX = '4' THEN 'Hawaiian/Pacific Islander'
	WHEN RACE_MAX = '5' THEN 'White' 
	ELSE 'Unknown'
END AS IPEDS_RACE_ETHNIC_DESC,
PERSON_ETHNIC_1, 
PERSON_ETHNIC_DESC_1, 
PERSON_ETHNIC_2, 
PERSON_ETHNIC_DESC_2, 
PERSON_RACE_1, 
PERSON_RACE_DESC_1, 
PERSON_RACE_2, 
PERSON_RACE_DESC_2, 
PERSON_RACE_3, 
PERSON_RACE_DESC_3, 
PERSON_RACE_4, 
PERSON_RACE_DESC_4, 
PERSON_RACE_5, 
PERSON_RACE_DESC_5,
/* End REG2010STIPE Changes 05/07/10 Parrish Fessler */
person.RESIDENCE_STATE 'STUDENT_RESIDENCE_STATE',
privacycodes.VAL_EXTERNAL_REPRESENTATION 'STUDENT_PRIVACY_FLAG',
directorycodes.VAL_EXTERNAL_REPRESENTATION 'STUDENT_DIRECTORY_FLAG',
email.PERSON_EMAIL_TYPES 'STUDENT_EMAIL',
email.PERSON_EMAIL_ADDRESSES 'STUDENT_EMAIL_TYPE',
acad_levels.ACLV_TRANSCRIPT_GROUPING 'TRGR_GPA_TRANSCRIPT_GROUPING'
       /*************************************************/
       /*************** GPA SECTION *********************/
       /*************************************************/
       /*-----------------------------------------------------
       CUSTOMIZATION INSTRUCTIONS FOR GPA OPTIONAL COLUMNS
       STEP 1
       To activate any of the following student GPA columns, remove the comment marks
          " / * "   and   " * / "   from the beginning and end of the line containing
       the gpa you would like to activate in the view.
       Let us say you are activating the "TRGR_CUM_GPA" column.
       Before activating it, you would have:
            / * , cum_gpa.STUDENT_OVERALL_CUM_GPA 'TRGR_CUM_GPA' * /
       After activating it, you would have (notice the comment marks are gone
       from the beginning and end of the line):
            , cum_gpa.STUDENT_OVERALL_CUM_GPA 'TRGR_CUM_GPA'
       NOTE: make sure you do not delete the comma at the beginning of the line.
       STEP 2
       Remove the comment marks surrounding the corresponding GPA table from the
       SOURCE TABLES" section below. For example, if activating the GPA
       "TRGR_CUM_GPA", you would also remove the comments around the
       "cum_gpa" table in the "SOURCE TABLES" section.
       Before activating it, here's what the table definition would look like in
       the "SOURCE TABLES" section:
            / * left outer join dbo.STUDENT_CUM_GPA_VIEW cum_gpa WITH (NOLOCK) on stc.STC_PERSON_ID = cum_gpa.STUDENT_ID and cum_gpa.TRANSCRIPT_GROUPINGS_ID = levels.ACLV_TRANSCRIPT_GROUPING * /
       And after activating it (notice the comment marks are gone from the beginning and
       end of the line):
            left outer join dbo.STUDENT_CUM_GPA_VIEW cum_gpa WITH (NOLOCK) on stc.STC_PERSON_ID = cum_gpa.STUDENT_ID and cum_gpa.TRANSCRIPT_GROUPINGS_ID = levels.ACLV_TRANSCRIPT_GROUPING
       -----------------------------------------------------------------*/
       /**********************************/
       /* START GPA CUSTOMIZATION STEP 1 */
       /**********************************/
       /* , cum_gpa.STUDENT_OVERALL_CUM_GPA 'TRGR_CUM_GPA' */
       /* , cum_gpa_by_type_I.STUDENT_OVERALL_CUM_GPA 'TRGR_GPA_INSTITUTIONAL' */
       /* , cum_gpa_by_type_C.STUDENT_OVERALL_CUM_GPA 'TRGR_GPA_CONT_ED' */
       /* , cum_gpa_by_type_T.STUDENT_OVERALL_CUM_GPA 'TRGR_GPA_TRANSFER' */
       /* , cum_gpa_by_type_E.STUDENT_OVERALL_CUM_GPA 'TRGR_GPA_EXCHANGE' */
       /* , cum_gpa_by_type_O.STUDENT_OVERALL_CUM_GPA 'TRGR_GPA_OTHER' */
       /* , cum_gpa_by_type_N.STUDENT_OVERALL_CUM_GPA 'TRGR_GPA_NONE' */
        /**********************************/
       /*  END GPA CUSTOMIZATION STEP 1  */
       /**********************************/
/* SOURCE TABLES: */
from dbo.STUDENT_PROGRAMS stp WITH (NOLOCK)
left outer join dbo.STUDENT_ACAD_PROG_STATUS_VIEW stp_statuses WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_statuses.STUDENT_PROGRAMS_ID
left outer join dbo.STPR_DATES stp_dates WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_dates.STUDENT_PROGRAMS_ID and stp_dates.POS = 1
left outer join dbo.STPR_MAJOR_LIST stp_addnl_majors1 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_majors1.STUDENT_PROGRAMS_ID and stp_addnl_majors1.POS = 1
left outer join dbo.STPR_MAJOR_LIST stp_addnl_majors2 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_majors2.STUDENT_PROGRAMS_ID and stp_addnl_majors2.POS = 2
left outer join dbo.STPR_MAJOR_LIST stp_addnl_majors3 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_majors3.STUDENT_PROGRAMS_ID and stp_addnl_majors3.POS = 3
left outer join dbo.STPR_CCD_LIST stp_addnl_ccds1 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_ccds1.STUDENT_PROGRAMS_ID and stp_addnl_ccds1.POS = 1
left outer join dbo.STPR_CCD_LIST stp_addnl_ccds2 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_ccds2.STUDENT_PROGRAMS_ID and stp_addnl_ccds2.POS = 2
left outer join dbo.STPR_CCD_LIST stp_addnl_ccds3 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_ccds3.STUDENT_PROGRAMS_ID and stp_addnl_ccds3.POS = 3
left outer join dbo.STPR_MINOR_LIST stp_addnl_minors1 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_minors1.STUDENT_PROGRAMS_ID and stp_addnl_minors1.POS = 1
left outer join dbo.STPR_MINOR_LIST stp_addnl_minors2 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_minors2.STUDENT_PROGRAMS_ID and stp_addnl_minors2.POS = 2
left outer join dbo.STPR_MINOR_LIST stp_addnl_minors3 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_minors3.STUDENT_PROGRAMS_ID and stp_addnl_minors3.POS = 3
left outer join dbo.STPR_SPECIALTIES stp_addnl_specs1 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_specs1.STUDENT_PROGRAMS_ID and stp_addnl_specs1.POS = 1
left outer join dbo.STPR_SPECIALTIES stp_addnl_specs2 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_specs2.STUDENT_PROGRAMS_ID and stp_addnl_specs2.POS = 2
left outer join dbo.STPR_SPECIALTIES stp_addnl_specs3 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_addnl_specs3.STUDENT_PROGRAMS_ID and stp_addnl_specs3.POS = 3
left outer join dbo.STPR_MAJOR_EVAL stp_major_eval1 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_major_eval1.STUDENT_PROGRAMS_ID and stp_major_eval1.POS = 1
left outer join dbo.STPR_MAJOR_EVAL stp_major_eval2 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_major_eval2.STUDENT_PROGRAMS_ID and stp_major_eval2.POS = 2
left outer join dbo.STPR_MAJOR_EVAL stp_major_eval3 WITH (NOLOCK) on stp.STUDENT_PROGRAMS_ID = stp_major_eval3.STUDENT_PROGRAMS_ID and stp_major_eval3.POS = 3
left outer join dbo.ACAD_PROGRAMS_LS prog_ls1 WITH (NOLOCK) on substring(stp.STUDENT_PROGRAMS_ID, 9, LEN(stp.STUDENT_PROGRAMS_ID)) = prog_ls1.ACAD_PROGRAMS_ID and prog_ls1.POS = 1
left outer join dbo.ACAD_PROGRAMS_LS prog_ls2 WITH (NOLOCK) on substring(stp.STUDENT_PROGRAMS_ID, 9, LEN(stp.STUDENT_PROGRAMS_ID)) = prog_ls2.ACAD_PROGRAMS_ID and prog_ls2.POS = 2
left outer join dbo.ACAD_PROGRAMS_LS prog_ls3 WITH (NOLOCK) on substring(stp.STUDENT_PROGRAMS_ID, 9, LEN(stp.STUDENT_PROGRAMS_ID)) = prog_ls3.ACAD_PROGRAMS_ID and prog_ls3.POS = 3
left outer join dbo.ACAD_PROGRAMS programs WITH (NOLOCK) on substring(stp.STUDENT_PROGRAMS_ID, 9, LEN(stp.STUDENT_PROGRAMS_ID)) = programs.ACAD_PROGRAMS_ID
left outer join dbo.STUDENT_ACAD_LEVELS levels WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID,7) + '*' + programs.ACPG_ACAD_LEVEL = levels.STUDENT_ACAD_LEVELS_ID
left outer join dbo.ACAD_LEVELS acad_levels WITH (NOLOCK) on programs.ACPG_ACAD_LEVEL = acad_levels.ACAD_LEVELS_ID
left outer join dbo.CLASSES class WITH (NOLOCK) on class.CLASSES_ID = levels.STA_CLASS
left outer join dbo.PERSON person WITH (NOLOCK) on  left(stp.STUDENT_PROGRAMS_ID, 7) = person.ID
/* Begin REG2010STIPE Changes 05/07/10 Parrish Fessler
Adding a new join on a dynamic table to derive the new IPEDS race and ethnicity information. */
left outer join (SELECT pr_ls.ID, 
                        COUNT(pr_ls.ID) LS_REC_COUNT, 
                        MAX(CASE alien_vals.VAL_ACTION_CODE_1 WHEN 'NRA' THEN 'Y' ELSE 'N' END) AS IS_FPER_ALIEN,
                        MAX(CASE ethnics_vals.VAL_ACTION_CODE_1 WHEN 'NRA' THEN 'Y' ELSE 'N' END) AS IS_ETHNIC_ALIEN,
                        MAX(CASE races_vals.VAL_ACTION_CODE_1 WHEN 'NRA' THEN 'Y' ELSE 'N' END) AS IS_RACES_ALIEN,
                        MAX(CASE ethnics_vals.VAL_ACTION_CODE_1 WHEN 'H' THEN 'Y' ELSE 'N' END) AS IS_HISPANIC,
                        COUNT(DISTINCT races_vals.VAL_ACTION_CODE_1) AS RACE_COUNT,
                        MAX(races_vals.VAL_ACTION_CODE_1) AS RACE_MAX, 
                        MAX(CASE pr_ls.POS WHEN 1 THEN pr_ls.PER_ETHNICS ELSE NULL END) AS PERSON_ETHNIC_1, 
                        MAX(CASE pr_ls.POS WHEN 1 THEN ethnics_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_ETHNIC_DESC_1, 
                        MAX(CASE pr_ls.POS WHEN 2 THEN pr_ls.PER_ETHNICS ELSE NULL END) AS PERSON_ETHNIC_2, 
                        MAX(CASE pr_ls.POS WHEN 2 THEN ethnics_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_ETHNIC_DESC_2, 
                        MAX(CASE pr_ls.POS WHEN 1 THEN pr_ls.PER_RACES ELSE NULL END) AS PERSON_RACE_1,
                        MAX(CASE pr_ls.POS WHEN 1 THEN races_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_RACE_DESC_1,
                        MAX(CASE pr_ls.POS WHEN 2 THEN pr_ls.PER_RACES ELSE NULL END) AS PERSON_RACE_2,
                        MAX(CASE pr_ls.POS WHEN 2 THEN races_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_RACE_DESC_2,
                        MAX(CASE pr_ls.POS WHEN 3 THEN pr_ls.PER_RACES ELSE NULL END) AS PERSON_RACE_3,
                        MAX(CASE pr_ls.POS WHEN 3 THEN races_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_RACE_DESC_3,
                        MAX(CASE pr_ls.POS WHEN 4 THEN pr_ls.PER_RACES ELSE NULL END) AS PERSON_RACE_4,
                        MAX(CASE pr_ls.POS WHEN 4 THEN races_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_RACE_DESC_4,
                        MAX(CASE pr_ls.POS WHEN 5 THEN pr_ls.PER_RACES ELSE NULL END) AS PERSON_RACE_5,
                        MAX(CASE pr_ls.POS WHEN 5 THEN races_vals.VAL_EXTERNAL_REPRESENTATION ELSE NULL END) AS PERSON_RACE_DESC_5
                   FROM dbo.PERSON_LS AS pr_ls WITH (NOLOCK)
                   LEFT OUTER JOIN dbo.FOREIGN_PERSON AS fpr WITH (NOLOCK) on pr_ls.ID = fpr.FOREIGN_PERSON_ID
                   LEFT OUTER JOIN dbo.VALS AS ethnics_vals WITH (NOLOCK) ON pr_ls.PER_ETHNICS = ethnics_vals.VAL_INTERNAL_CODE AND ethnics_vals.VALCODE_ID = 'PERSON.ETHNICS'
                   LEFT OUTER JOIN dbo.VALS AS races_vals   WITH (NOLOCK) ON pr_ls.PER_RACES   = races_vals.VAL_INTERNAL_CODE AND races_vals.VALCODE_ID = 'PERSON.RACES'
                   LEFT OUTER JOIN dbo.VALS as alien_vals WITH (NOLOCK) ON fpr.FPER_ALIEN_STATUS = alien_vals.VAL_INTERNAL_CODE and alien_vals.VALCODE_ID = 'ALIEN.STATUSES'
                   GROUP BY pr_ls.ID) AS PERSON_MAIN_ETHNIC_RACE_INFO 
   on left(stp.STUDENT_PROGRAMS_ID, 7) = PERSON_MAIN_ETHNIC_RACE_INFO.ID
/* End REG2010STIPE Changes 05/07/10 Parrish Fessler */
left outer join dbo.ETHNICS ethnics WITH (NOLOCK) on person.ETHNIC = ethnics.ETHNICS_ID
left outer join dbo.VALS privacycodes WITH (NOLOCK) on privacycodes.VALCODE_ID = 'PRIVACY.CODES' and privacycodes.VAL_MINIMUM_INPUT_STRING = person.PRIVACY_FLAG
left outer join dbo.VALS directorycodes WITH (NOLOCK) on directorycodes.VALCODE_ID = 'DIRECTORY.CODES' and directorycodes.VAL_MINIMUM_INPUT_STRING = person.DIRECTORY_FLAG
left outer join dbo.VALS progstatuscodes WITH (NOLOCK) on progstatuscodes.VALCODE_ID = 'STUDENT.PROGRAM.STATUSES' and progstatuscodes.VAL_MINIMUM_INPUT_STRING = stp_statuses.PROG_STATUS
left outer join dbo.PEOPLE_EMAIL email WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID,7) = email.ID and email.PERSON_PREFERRED_EMAIL = 'Y'
/**********************************/
/* START GPA CUSTOMIZATION STEP 2 */
/**********************************/
/* left outer join dbo.STUDENT_CUM_GPA_VIEW cum_gpa WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa.STUDENT_ID and cum_gpa.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/* left outer join dbo.STUDENT_CUM_GPA_BY_TYPE_VIEW cum_gpa_by_type_I WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa_by_type_I.STUDENT_ID and cum_gpa_by_type_I.CREDIT_TYPE = 'I' and cum_gpa_by_type_I.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/* left outer join dbo.STUDENT_CUM_GPA_BY_TYPE_VIEW cum_gpa_by_type_C WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa_by_type_C.STUDENT_ID and cum_gpa_by_type_C.CREDIT_TYPE = 'C' and cum_gpa_by_type_C.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/* left outer join dbo.STUDENT_CUM_GPA_BY_TYPE_VIEW cum_gpa_by_type_T WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa_by_type_T.STUDENT_ID and cum_gpa_by_type_T.CREDIT_TYPE = 'T' and cum_gpa_by_type_T.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/* left outer join dbo.STUDENT_CUM_GPA_BY_TYPE_VIEW cum_gpa_by_type_E WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa_by_type_E.STUDENT_ID and cum_gpa_by_type_E.CREDIT_TYPE = 'E' and cum_gpa_by_type_E.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/* left outer join dbo.STUDENT_CUM_GPA_BY_TYPE_VIEW cum_gpa_by_type_O WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa_by_type_O.STUDENT_ID and cum_gpa_by_type_O.CREDIT_TYPE = 'O' and cum_gpa_by_type_O.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/* left outer join dbo.STUDENT_CUM_GPA_BY_TYPE_VIEW cum_gpa_by_type_N WITH (NOLOCK) on left(stp.STUDENT_PROGRAMS_ID, 7) = cum_gpa_by_type_N.STUDENT_ID and cum_gpa_by_type_N.CREDIT_TYPE = 'N' and cum_gpa_by_type_N.TRANSCRIPT_GROUPINGS_ID = acad_levels.ACLV_TRANSCRIPT_GROUPING */
/**********************************/
/*  END GPA CUSTOMIZATION STEP 2  */
/**********************************/;

GO


ACAD_CREDENTIALS:

USE [coll18_production]
GO

/****** Object:  Table [dbo].[ACAD_CREDENTIALS]    Script Date: 06/29/2012 12:00:19 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[ACAD_CREDENTIALS](
	[ACAD_CREDENTIALS_ID] [varchar](10) NOT NULL,
	[ACAD_PERSON_ID] [varchar](10) NULL,
	[ACAD_INSTITUTIONS_ID] [varchar](10) NULL,
	[ACAD_START_DATE] [datetime] NULL,
	[ACAD_END_DATE] [datetime] NULL,
	[ACAD_DEGREE] [varchar](5) NULL,
	[ACAD_DEGREE_DATE] [datetime] NULL,
	[ACAD_DIVISION] [varchar](5) NULL,
	[ACAD_LOCATION] [varchar](5) NULL,
	[ACAD_ACAD_LEVEL] [varchar](5) NULL,
	[ACAD_NO_YEARS] [decimal](2, 0) NULL,
	[ACAD_COMMENCEMENT_DATE] [datetime] NULL,
	[ACAD_DIPLOMA_NAME] [varchar](40) NULL,
	[ACAD_GPA] [decimal](6, 3) NULL,
	[ACAD_GPA1] [decimal](6, 3) NULL,
	[ACAD_GPA2] [decimal](6, 3) NULL,
	[ACAD_RANK_PERCENT] [decimal](5, 2) NULL,
	[ACAD_RANK_NUMERATOR] [decimal](7, 0) NULL,
	[ACAD_RANK_DENOMINATOR] [decimal](7, 0) NULL,
	[ACAD_THESIS] [text] NULL,
	[ACAD_COMMENTS] [text] NULL,
	[ACAD_CREDENTIALS_ADDOPR] [varchar](20) NULL,
	[ACAD_CREDENTIALS_ADDDATE] [datetime] NULL,
	[ACAD_CREDENTIALS_CHGOPR] [varchar](20) NULL,
	[ACAD_CREDENTIALS_CHGDATE] [datetime] NULL,
	[ACAD_TRANSCRIPT_CITY] [varchar](30) NULL,
	[ACAD_TRANSCRIPT_STATE] [varchar](2) NULL,
	[ACAD_TRANSCRIPT_ZIP] [varchar](10) NULL,
	[ACAD_TRANSCRIPT_COUNTRY] [varchar](8) NULL,
	[ACAD_COMMENCEMENT_SITE] [varchar](10) NULL,
	[ACAD_USER1] [varchar](10) NULL,
	[ACAD_USER2] [varchar](10) NULL,
	[ACAD_USER3] [varchar](10) NULL,
	[ACAD_USER4] [varchar](10) NULL,
	[ACAD_USER5] [varchar](10) NULL,
	[ACAD_USER6] [varchar](10) NULL,
	[ACAD_USER7] [varchar](10) NULL,
	[ACAD_USER8] [varchar](10) NULL,
	[ACAD_USER9] [varchar](10) NULL,
	[ACAD_USER10] [varchar](10) NULL,
	[ACAD_ACAD_PROGRAM] [varchar](20) NULL,
	[ACAD_CERT_TYPE] [varchar](10) NULL,
	[ACAD_CERT_STATUS] [varchar](10) NULL,
	[ACAD_CERT_EMPLOYABLE_BY_CODE] [varchar](10) NULL,
	[ACAD_CERT_STAFF_REQUEST_FLAG] [varchar](1) NULL,
	[ACAD_TERM] [varchar](7) NULL,
	[ACAD_CERT_PART_TIME_IND] [varchar](10) NULL,
	[ACAD_CERT_PART_TIME_OVR] [varchar](1) NULL,
	[ACAD_RENEWAL_IND] [varchar](1) NULL,
 CONSTRAINT [PK_ACAD_CREDENTIALS] PRIMARY KEY CLUSTERED 
(
	[ACAD_CREDENTIALS_ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [IDX]
) ON [IDX] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_ACAD_LEVEL_OTHER_ACAD_LEVELS] FOREIGN KEY([ACAD_ACAD_LEVEL])
REFERENCES [dbo].[OTHER_ACAD_LEVELS] ([OTHER_ACAD_LEVELS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_ACAD_LEVEL_OTHER_ACAD_LEVELS]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DEGREE_OTHER_DEGREES] FOREIGN KEY([ACAD_DEGREE])
REFERENCES [dbo].[OTHER_DEGREES] ([OTHER_DEGREES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DEGREE_OTHER_DEGREES]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DIVISION_OTHER_DIVISIONS] FOREIGN KEY([ACAD_DIVISION])
REFERENCES [dbo].[OTHER_DIVISIONS] ([OTHER_DIVISIONS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_DIVISION_OTHER_DIVISIONS]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_INSTITUTIONS_ID_INSTITUTIONS] FOREIGN KEY([ACAD_INSTITUTIONS_ID])
REFERENCES [dbo].[INSTITUTIONS] ([INSTITUTIONS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_INSTITUTIONS_ID_INSTITUTIONS]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_LOCATION_OTHER_LOCATIONS] FOREIGN KEY([ACAD_LOCATION])
REFERENCES [dbo].[OTHER_LOCATIONS] ([OTHER_LOCATIONS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_LOCATION_OTHER_LOCATIONS]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_PERSON_ID_PERSON] FOREIGN KEY([ACAD_PERSON_ID])
REFERENCES [dbo].[PERSON] ([ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_PERSON_ID_PERSON]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_COUNTRY_COUNTRIES] FOREIGN KEY([ACAD_TRANSCRIPT_COUNTRY])
REFERENCES [dbo].[COUNTRIES] ([COUNTRIES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_COUNTRY_COUNTRIES]
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS]  WITH NOCHECK ADD  CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_STATE_STATES] FOREIGN KEY([ACAD_TRANSCRIPT_STATE])
REFERENCES [dbo].[STATES] ([STATES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[ACAD_CREDENTIALS] NOCHECK CONSTRAINT [FK_ACAD_CREDENTIALS_ACAD_TRANSCRIPT_STATE_STATES]
GO

PERSON:

USE [coll18_production]
GO

/****** Object:  Table [dbo].[PERSON]    Script Date: 06/29/2012 12:00:44 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_PADDING ON
GO

CREATE TABLE [dbo].[PERSON](
	[ID] [varchar](10) NOT NULL,
	[LAST_NAME] [varchar](57) NULL,
	[SOURCE] [varchar](10) NULL,
	[FIRST_NAME] [varchar](30) NULL,
	[MIDDLE_NAME] [varchar](30) NULL,
	[PREFIX] [varchar](25) NULL,
	[PREFERRED_ADDRESS] [varchar](10) NULL,
	[JOINT_PERSON] [varchar](10) NULL,
	[SSN] [varchar](12) NULL,
	[PERSON_STATUS] [varchar](10) NULL,
	[GENDER] [varchar](1) NULL,
	[MARITAL_STATUS] [varchar](10) NULL,
	[BIRTH_DATE] [datetime] NULL,
	[PREFERRED_NAME] [varchar](300) NULL,
	[PERSON_ADD_OPERATOR] [varchar](20) NULL,
	[SPOUSE] [varchar](10) NULL,
	[NICKNAME] [varchar](15) NULL,
	[ETHNIC] [varchar](5) NULL,
	[PERSON_CHANGE_OPERATOR] [varchar](20) NULL,
	[PERSON_CHANGE_DATE] [datetime] NULL,
	[PERSON_ADD_DATE] [datetime] NULL,
	[SUFFIX] [varchar](25) NULL,
	[PERSON_NATIVE_LANGUAGE] [varchar](10) NULL,
	[ANONYMOUS] [varchar](1) NULL,
	[DECEASED_DATE] [datetime] NULL,
	[BIRTH_NAME_LAST] [varchar](57) NULL,
	[BIRTH_NAME_FIRST] [varchar](30) NULL,
	[BIRTH_NAME_MIDDLE] [varchar](30) NULL,
	[PREFERRED_RESIDENCE] [varchar](10) NULL,
	[PERSON_BEN_ID] [varchar](10) NULL,
	[INCOME_LEVEL] [varchar](20) NULL,
	[PREF_BUS_ADDRESS] [varchar](10) NULL,
	[PERSON_COUNTRY_ENTRY_DATE] [datetime] NULL,
	[PREMIUM_PREFERENCE] [varchar](10) NULL,
	[PERSON_CORP_INDICATOR] [varchar](1) NULL,
	[RESIDENCE_COUNTRY] [varchar](8) NULL,
	[BOX] [varchar](10) NULL,
	[PERSON_DONOR_TYPE] [varchar](10) NULL,
	[PERSON_GROUP_ID] [varchar](10) NULL,
	[PREFERRED_LISTING] [varchar](60) NULL,
	[OBITUARY] [varchar](10) NULL,
	[MEMORIAL_TYPE] [varchar](10) NULL,
	[PERSON_ORIGIN_DATE] [datetime] NULL,
	[PERSON_ORIGIN_CODE] [varchar](10) NULL,
	[PERSON_USER1] [varchar](10) NULL,
	[PERSON_USER2] [varchar](10) NULL,
	[PERSON_USER3] [varchar](10) NULL,
	[PERSON_USER4] [varchar](10) NULL,
	[PERSON_USER5] [varchar](10) NULL,
	[PERSON_USER_CHANGED_BY] [varchar](10) NULL,
	[PERSON_USER_CHANGED_DATE] [datetime] NULL,
	[PERSON_USER6] [varchar](10) NULL,
	[PERSON_USER7] [varchar](10) NULL,
	[PERSON_USER8] [varchar](10) NULL,
	[PERSON_USER9] [varchar](10) NULL,
	[PERSON_USER10] [varchar](10) NULL,
	[PERSON_MERGED_TO_ID] [varchar](10) NULL,
	[ANNUITY_ADDRESS] [varchar](10) NULL,
	[PERSON_BIRTH_PLACE] [varchar](30) NULL,
	[DENOMINATION] [varchar](2) NULL,
	[RFA_SEGMENT] [varchar](10) NULL,
	[PERSON_VIP] [varchar](4) NULL,
	[POLITICAL_PARTY] [varchar](4) NULL,
	[VISA_ISSUED_DATE] [datetime] NULL,
	[OCCUPATION] [varchar](6) NULL,
	[PERSON_OVERRIDE_SALUTATION] [varchar](30) NULL,
	[PERSON_FAMILY_SIZE] [varchar](10) NULL,
	[GUARDIANS] [varchar](10) NULL,
	[PERSON_OVRL_EMP_STAT] [varchar](10) NULL,
	[PARTICIPANT_TYPE] [varchar](10) NULL,
	[RESIDENCE_COUNTY] [varchar](7) NULL,
	[RESIDENCE_STATE] [varchar](2) NULL,
	[VISA_TYPE] [varchar](20) NULL,
	[VISA_EXP_DATE] [datetime] NULL,
	[ALIEN_ID] [varchar](12) NULL,
	[ALIEN_FLAG] [varchar](10) NULL,
	[SELECTIVE_SERVICE_FLAG] [varchar](3) NULL,
	[SELECTIVE_SERVICE_NUMBER] [varchar](12) NULL,
	[CITIZENSHIP] [varchar](8) NULL,
	[EMER_CONTACT_NAME] [varchar](30) NULL,
	[EMER_CONTACT_PHONE] [varchar](20) NULL,
	[DIRECTORY_FLAG] [varchar](10) NULL,
	[PRIVACY_FLAG] [varchar](10) NULL,
	[IMMIGRATION_STATUS] [varchar](10) NULL,
	[PREF_EMPLOYMENT] [varchar](10) NULL,
	[PERSON_PRIMARY_LANGUAGE] [varchar](20) NULL,
	[AARS] [varchar](10) NULL,
	[DRIVER_LICENSE_NO] [varchar](15) NULL,
	[DRIVER_LICENSE_STATE] [varchar](2) NULL,
	[PERSON_HOME_LANG_SCH_NO_YRS] [decimal](2, 0) NULL,
	[PERSON_HOME_LANG_SCH_COUNTRY] [varchar](8) NULL,
	[PERSON_ACHIEVEMENTS] [text] NULL,
	[PERSON_WEBSITE_ADDRESS] [varchar](50) NULL,
	[PERSON_CAMPUS_ORGS_ID] [varchar](5) NULL,
 CONSTRAINT [PK_PERSON] PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON, FILLFACTOR = 90) ON [IDX]
) ON [IDX] TEXTIMAGE_ON [PRIMARY]

GO

SET ANSI_PADDING ON
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_AARS_PERSON] FOREIGN KEY([AARS])
REFERENCES [dbo].[PERSON] ([ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_AARS_PERSON]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_DRIVER_LICENSE_STATE_STATES] FOREIGN KEY([DRIVER_LICENSE_STATE])
REFERENCES [dbo].[STATES] ([STATES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_DRIVER_LICENSE_STATE_STATES]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_ETHNIC_ETHNICS] FOREIGN KEY([ETHNIC])
REFERENCES [dbo].[ETHNICS] ([ETHNICS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_ETHNIC_ETHNICS]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_ID_CORP_FOUNDS] FOREIGN KEY([ID])
REFERENCES [dbo].[CORP_FOUNDS] ([CORP_FOUNDS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_ID_CORP_FOUNDS]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_ID_SHOPPING_CART] FOREIGN KEY([ID])
REFERENCES [dbo].[SHOPPING_CART] ([SHOP_PERSON_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_ID_SHOPPING_CART]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_OBITUARY_CLIPPING] FOREIGN KEY([OBITUARY])
REFERENCES [dbo].[CLIPPING] ([CLIPPING_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_OBITUARY_CLIPPING]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_PERSON_HOME_LANG_SCH_COUNTRY_COUNTRIES] FOREIGN KEY([PERSON_HOME_LANG_SCH_COUNTRY])
REFERENCES [dbo].[COUNTRIES] ([COUNTRIES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_PERSON_HOME_LANG_SCH_COUNTRY_COUNTRIES]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_PREFERRED_ADDRESS_ADDRESS] FOREIGN KEY([PREFERRED_ADDRESS])
REFERENCES [dbo].[ADDRESS] ([ADDRESS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_PREFERRED_ADDRESS_ADDRESS]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_PREFERRED_RESIDENCE_ADDRESS] FOREIGN KEY([PREFERRED_RESIDENCE])
REFERENCES [dbo].[ADDRESS] ([ADDRESS_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_PREFERRED_RESIDENCE_ADDRESS]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_RESIDENCE_COUNTRY_COUNTRIES] FOREIGN KEY([RESIDENCE_COUNTRY])
REFERENCES [dbo].[COUNTRIES] ([COUNTRIES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_COUNTRY_COUNTRIES]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_RESIDENCE_COUNTY_COUNTIES] FOREIGN KEY([RESIDENCE_COUNTY])
REFERENCES [dbo].[COUNTIES] ([COUNTIES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_COUNTY_COUNTIES]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_RESIDENCE_STATE_STATES] FOREIGN KEY([RESIDENCE_STATE])
REFERENCES [dbo].[STATES] ([STATES_ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_RESIDENCE_STATE_STATES]
GO

ALTER TABLE [dbo].[PERSON]  WITH NOCHECK ADD  CONSTRAINT [FK_PERSON_SPOUSE_PERSON] FOREIGN KEY([SPOUSE])
REFERENCES [dbo].[PERSON] ([ID])
NOT FOR REPLICATION 
GO

ALTER TABLE [dbo].[PERSON] NOCHECK CONSTRAINT [FK_PERSON_SPOUSE_PERSON]
GO