Loyola: nonstandard export

FTP folder name
Edit section

The folder into which the CSV/zip export files are placed is LoyolaMD, not Loyola:

 \\422biz001\FTP\LoyolaMD\ 

Export table
Edit section

Loyola_Warehouse has an additional export table named Xport_Loyola.  This table is very similar to the standard export table Xport, which also exists in this database.  The step "Custom for Loyola: Move export data to custom table" transforms data from the Xport table into the Xport_Loyola table which is then used for the creation of the export files. 

 

  • All ModifiedOn dates should be formatted as mm/dd/yyyy hh:mm(24) dates
  • The SSN field should contain only numeric characters 
  • The HispanicYesNo field translates 1 to Yes and anything else to No
  • Rating is hardcoded to the value 2 

 

TRUNCATE TABLE Xport_Loyola
GO

INSERT INTO Xport_Loyola (
	OrgName,
	Department,
	C422OpportunityID,	
	C422PersonID,
	Salutation,	
	FirstName,
	MiddleName,	
	LastName,
	NameSuffix,	
	NickName,
	PriorLastName,	
	HomePhone,
	MobilePhone,	
	IMAddress,
	EMail,	
	EMail2,
	EMail3,	
	GeoMarket,
	Fax,	
	BusinessPhone,
	PersonRole,	
	Gender,
	BirthDay,	
	Citizenship,
	VisaType,	
	SSN,
	MaritalStatus,	
	PreferredContactMethod,
	EmailAllow,	
	BulkEmailAllow,
	PhoneAllow,	
	FaxAllow,
	MailAllow,	
	ReligiousPreference,
	HispanicYesNo,	
	PersonModifiedOn,
	Race_01_Value,	
	Race_02_Value,
	Race_03_Value,	
	Race_04_Value,
	Race_05_Value,	
	Race_06_Value,
	Race_07_Value,	
	Race_08_Value,
	Address_01_Name,	
	Address_01_Active,
	Address_01_PermAddress,	
	Address_01_Street1,
	Address_01_Street2,	
	Address_01_Street3,
	Address_01_City,	
	Address_01_StateProvince,
	Address_01_PostalCode,	
	Address_01_County,
	Address_01_CountryRegion,	
	Address_02_Name,
	Address_02_Active,	
	Address_02_PermAddress,
	Address_02_Street1,	
	Address_02_Street2,
	Address_02_Street3,
	Address_02_City,
	Address_02_StateProvince,
	Address_02_PostalCode,
	Address_02_County,
	Address_02_CountryRegion,
	ID_01_Type,
	ID_01_Value,
	ID_02_Type,
	ID_02_Value,
	ID_03_Type,
	ID_03_Value,
	ID_04_Type,
	ID_04_Value,
	ID_05_Type,
	ID_05_Value,
	OpportunityStateCode,
	OpportunityType,
	EntryTerm,
	OpportunityStatus,
	ApplicationType,
	Classification,
	EnrollmentStatus,
	DecisionPlan,
	Housing,
	StateOfResidency,
	ConditionOfAdmission,
	AcademicMajor,
	SecondAcademicArea,
	PreProfessional,
	Legacy,
	Campus,
	FinancialAidInterest,
	Rating,
	FirstActivity,
	FirstActivityDate,
	StaffAssigned,
	OpportunityModifiedOn,
	Award_01_Type,
	Award_01_Amount,
	Award_02_Type,
	Award_02_Amount,
	Award_03_Type,
	Award_03_Amount,
	Award_04_Type,
	Award_04_Amount,
	Award_05_Type,
	Award_05_Amount,
	Award_06_Type,
	Award_06_Amount,
	Award_07_Type,
	Award_07_Amount,
	Award_08_Type,
	Award_08_Amount,
	FinancialAidModifiedOn,
	Activity_01_Value,
	Activity_01_Date,
	Activity_02_Value,
	Activity_02_Date,
	Activity_03_Value,
	Activity_03_Date,
	Activity_04_Value,
	Activity_04_Date,
	Activity_05_Value,
	Activity_05_Date,
	Activity_06_Value,
	Activity_06_Date,
	Activity_07_Value,
	Activity_07_Date,
	Activity_08_Value,
	Activity_08_Date,
	Interest_01_Value,
	Interest_02_Value,
	Interest_03_Value,
	Interest_04_Value,
	Interest_05_Value,
	Interest_06_Value,
	Interest_07_Value,
	Interest_08_Value,
	Organization_01_Code,
	Organization_01_Active,
	Organization_01_Name,
	Organization_01_GPA,
	Organization_01_GPASelfReported,
	Organization_01_ClassSize,
	Organization_01_ClassRank,
	Organization_01_ClassRankSelfReported,
	Organization_01_RankPercentile,
	Organization_01_CreditsCompleted,
	Organization_01_GraduatedCompleted,
	Organization_01_DiplomaDegreeRcvd,
	Organization_01_TranscriptRcvdDate,
	Organization_01_LastAttendedDate,
	Organization_01_FinalTranscript,
	Organization_01_ModifiedOn,
	Score_01_TestName,
	Score_01_TestDate,
	Score_01_TestSource,
	Score_01_TestValue,
	Score_01_Subscore_01_Name,
	Score_01_Subscore_01_Value,
	Score_01_Subscore_02_Name,
	Score_01_Subscore_02_Value,
	Score_01_Subscore_03_Name,
	Score_01_Subscore_03_Value,
	Score_01_Subscore_04_Name,
	Score_01_Subscore_04_Value,
	Score_01_Subscore_05_Name,
	Score_01_Subscore_05_Value,
	Score_02_TestName,
	Score_02_TestDate,
	Score_02_TestSource,
	Score_02_TestValue,
	Score_02_Subscore_01_Name,
	Score_02_Subscore_01_Value,
	Score_02_Subscore_02_Name,
	Score_02_Subscore_02_Value,
	Score_02_Subscore_03_Name,
	Score_02_Subscore_03_Value,
	Score_02_Subscore_04_Name,
	Score_02_Subscore_04_Value,
	Score_02_Subscore_05_Name,
	Score_02_Subscore_05_Value,
	Score_03_TestName,
	Score_03_TestDate,
	Score_03_TestSource,
	Score_03_TestValue,
	Score_03_Subscore_01_Name,
	Score_03_Subscore_01_Value,
	Score_03_Subscore_02_Name,
	Score_03_Subscore_02_Value,
	Score_03_Subscore_03_Name,
	Score_03_Subscore_03_Value,
	Score_03_Subscore_04_Name,
	Score_03_Subscore_04_Value,
	Score_03_Subscore_05_Name,
	Score_03_Subscore_05_Value,
	Score_04_TestName,
	Score_04_TestDate,
	Score_04_TestSource,
	Score_04_TestValue,
	Score_04_Subscore_01_Name,
	Score_04_Subscore_01_Value,
	Score_04_Subscore_02_Name,
	Score_04_Subscore_02_Value,
	Score_04_Subscore_03_Name,
	Score_04_Subscore_03_Value,
	Score_04_Subscore_04_Name,
	Score_04_Subscore_04_Value,
	Score_04_Subscore_05_Name,
	Score_04_Subscore_05_Value,
	Relationship_01_Role1,
	Relationship_01_Role2,
	Relationship_01_C422PersonID,
	Relationship_01_Salutation,
	Relationship_01_FirstName,
	Relationship_01_MiddleName,
	Relationship_01_LastName,
	Relationship_01_NameSuffix,
	Relationship_01_NickName,
	Relationship_01_HomePhone,
	Relationship_01_MobilePhone,
	Relationship_01_Email,
	Relationship_01_BusinessPhone,
	Relationship_01_PersonRole,
	Relationship_01_Street1,
	Relationship_01_Street2,
	Relationship_01_City,
	Relationship_01_StateProvince,
	Relationship_01_PostalCode,
	Relationship_01_CountryRegion,
	Relationship_02_Role1,
	Relationship_02_Role2,
	Relationship_02_C422PersonID,
	Relationship_02_Salutation,
	Relationship_02_FirstName,
	Relationship_02_MiddleName,
	Relationship_02_LastName,
	Relationship_02_NameSuffix,
	Relationship_02_NickName,
	Relationship_02_HomePhone,
	Relationship_02_MobilePhone,
	Relationship_02_Email,
	Relationship_02_BusinessPhone,
	Relationship_02_PersonRole,
	Relationship_02_Street1,
	Relationship_02_Street2,
	Relationship_02_City,
	Relationship_02_StateProvince,
	Relationship_02_PostalCode,
	Relationship_02_CountryRegion 
) 
SELECT 
	OrgName,
	Department,
	C422OpportunityID,
	C422PersonID,
	Salutation,
	FirstName,
	MiddleName,
	LastName,
	NameSuffix,
	NickName,
	PriorLastName,
	HomePhone,
	MobilePhone,
	IMAddress,
	EMail,
	EMail2,
	EMail3,
	GeoMarket,
	Fax,
	BusinessPhone,
	PersonRole,
	Gender,
	Birthday,
	Citizenship,
	VisaType,
	SUBSTRING(SSN,1,3) + SUBSTRING(SSN,5,2) + SUBSTRING(SSN,8,4),
	MaritalStatus,
	PreferredContactMethod,
	EmailAllow,
	BulkEmailAllow,
	PhoneAllow,
	FaxAllow,
	MailAllow,
	ReligiousPreference,
	HispanicYesNo  = 
	CASE
		WHEN Hispanic = 1 THEN 'Yes'
		ELSE 'No'
	END,
	CONVERT(nvarchar(10),CAST(PersonModifiedOn AS datetime),101) + ' ' + SUBSTRING(CONVERT(nvarchar(5),CAST(PersonModifiedOn AS datetime),114),1,5),
	Race_01_Value,
	Race_02_Value,
	Race_03_Value,
	Race_04_Value,
	Race_05_Value,
	Race_06_Value,
	Race_07_Value,
	Race_08_Value,
	Address_01_Name,
	Address_01_Active,
	Address_01_PermAddress,
	Address_01_Street1,
	Address_01_Street2,
	Address_01_Street3,
	Address_01_City,
	Address_01_StateProvince,
	Address_01_PostalCode,
	Address_01_County,
	Address_01_CountryRegion,
	Address_02_Name,
	Address_02_Active,
	Address_02_PermAddress,
	Address_02_Street1,
	Address_02_Street2,
	Address_02_Street3,
	Address_02_City,
	Address_02_StateProvince,
	Address_02_PostalCode,
	Address_02_County,
	Address_02_CountryRegion,
	ID_01_Type,
	ID_01_Value,
	ID_02_Type,
	ID_02_Value,
	ID_03_Type,
	ID_03_Value,
	ID_04_Type,
	ID_04_Value,
	ID_05_Type,
	ID_05_Value,
	OpportunityStateCode,
	OpportunityType,
	EntryTerm,
	OpportunityStatus,
	ApplicationType,
	Classification,
	EnrollmentStatus,
	DecisionPlan,
	Housing,
	StateOfResidency,
	ConditionOfAdmission,
	AcademicMajor,
	SecondAcademicArea,
	PreProfessional,
	Legacy,
	Campus,
	FinancialAidInterest,
	2,
	FirstActivity,
	FirstActivityDate,
	StaffAssigned,
	CONVERT(nvarchar(10),CAST(OpportunityModifiedOn AS datetime),101) + ' ' + SUBSTRING(CONVERT(nvarchar(5),CAST(OpportunityModifiedOn AS datetime),114),1,5),
	Award_01_Type,
	Award_01_Amount,
	Award_02_Type,
	Award_02_Amount,
	Award_03_Type,
	Award_03_Amount,
	Award_04_Type,
	Award_04_Amount,
	Award_05_Type,
	Award_05_Amount,
	Award_06_Type,
	Award_06_Amount,
	Award_07_Type,
	Award_07_Amount,
	Award_08_Type,
	Award_08_Amount,
	CONVERT(nvarchar(10),CAST(FinancialAidModifiedOn AS datetime),101) + ' ' + SUBSTRING(CONVERT(nvarchar(5),CAST(FinancialAidModifiedOn AS datetime),114),1,5),
	Activity_01_Value,
	Activity_01_Date,
	Activity_02_Value,
	Activity_02_Date,
	Activity_03_Value,
	Activity_03_Date,
	Activity_04_Value,
	Activity_04_Date,
	Activity_05_Value,
	Activity_05_Date,
	Activity_06_Value,
	Activity_06_Date,
	Activity_07_Value,
	Activity_07_Date,
	Activity_08_Value,
	Activity_08_Date,
	Interest_01_Value,
	Interest_02_Value,
	Interest_03_Value,
	Interest_04_Value,
	Interest_05_Value,
	Interest_06_Value,
	Interest_07_Value,
	Interest_08_Value,
	Organization_01_Code,
	Organization_01_Active,
	Organization_01_Name,
	Organization_01_GPA,
	Organization_01_GPASelfReported,
	Organization_01_ClassSize,
	Organization_01_ClassRank,
	Organization_01_ClassRankSelfReported,
	Organization_01_RankPercentile,
	Organization_01_CreditsCompleted,
	Organization_01_GraduatedCompleted,
	Organization_01_DiplomaDegreeRcvd,
	Organization_01_TranscriptRcvdDate,
	Organization_01_LastAttendedDate,
	Organization_01_FinalTranscript,
	CONVERT(nvarchar(10),CAST(Organization_01_ModifiedOn AS datetime),101) + ' ' + SUBSTRING(CONVERT(nvarchar(5),CAST(Organization_01_ModifiedOn AS datetime),114),1,5),
	Score_01_TestName,
	Score_01_TestDate,
	Score_01_TestSource,
	Score_01_TestValue,
	Score_01_Subscore_01_Name,
	Score_01_Subscore_01_Value,
	Score_01_Subscore_02_Name,
	Score_01_Subscore_02_Value,
	Score_01_Subscore_03_Name,
	Score_01_Subscore_03_Value,
	Score_01_Subscore_04_Name,
	Score_01_Subscore_04_Value,
	Score_01_Subscore_05_Name,
	Score_01_Subscore_05_Value,
	Score_02_TestName,
	Score_02_TestDate,
	Score_02_TestSource,
	Score_02_TestValue,
	Score_02_Subscore_01_Name,
	Score_02_Subscore_01_Value,
	Score_02_Subscore_02_Name,
	Score_02_Subscore_02_Value,
	Score_02_Subscore_03_Name,
	Score_02_Subscore_03_Value,
	Score_02_Subscore_04_Name,
	Score_02_Subscore_04_Value,
	Score_02_Subscore_05_Name,
	Score_02_Subscore_05_Value,
	Score_03_TestName,
	Score_03_TestDate,
	Score_03_TestSource,
	Score_03_TestValue,
	Score_03_Subscore_01_Name,
	Score_03_Subscore_01_Value,
	Score_03_Subscore_02_Name,
	Score_03_Subscore_02_Value,
	Score_03_Subscore_03_Name,
	Score_03_Subscore_03_Value,
	Score_03_Subscore_04_Name,
	Score_03_Subscore_04_Value,
	Score_03_Subscore_05_Name,
	Score_03_Subscore_05_Value,
	Score_04_TestName,
	Score_04_TestDate,
	Score_04_TestSource,
	Score_04_TestValue,
	Score_04_Subscore_01_Name,
	Score_04_Subscore_01_Value,
	Score_04_Subscore_02_Name,
	Score_04_Subscore_02_Value,
	Score_04_Subscore_03_Name,
	Score_04_Subscore_03_Value,
	Score_04_Subscore_04_Name,
	Score_04_Subscore_04_Value,
	Score_04_Subscore_05_Name,
	Score_04_Subscore_05_Value,
	Relationship_01_Role1,
	Relationship_01_Role2,
	Relationship_01_C422PersonID,
	Relationship_01_Salutation,
	Relationship_01_FirstName,
	Relationship_01_MiddleName,
	Relationship_01_LastName,
	Relationship_01_NameSuffix,
	Relationship_01_NickName,
	Relationship_01_HomePhone,
	Relationship_01_MobilePhone,
	Relationship_01_Email,
	Relationship_01_BusinessPhone,
	Relationship_01_PersonRole,
	Relationship_01_Street1,
	Relationship_01_Street2,
	Relationship_01_City,
	Relationship_01_StateProvince,
	Relationship_01_PostalCode,
	Relationship_01_CountryRegion,
	Relationship_02_Role1,
	Relationship_02_Role2,
	Relationship_02_C422PersonID,
	Relationship_02_Salutation,
	Relationship_02_FirstName,
	Relationship_02_MiddleName,
	Relationship_02_LastName,
	Relationship_02_NameSuffix,
	Relationship_02_NickName,
	Relationship_02_HomePhone,
	Relationship_02_MobilePhone,
	Relationship_02_Email,
	Relationship_02_BusinessPhone,
	Relationship_02_PersonRole,
	Relationship_02_Street1,
	Relationship_02_Street2,
	Relationship_02_City,
	Relationship_02_StateProvince,
	Relationship_02_PostalCode,
	Relationship_02_CountryRegion 
FROM Xport

GO

Comments