Insurance Database Import / Export Procedure Policy: Student Health Services utilizes a custom FileMaker Pro insurance database to facilitate the mana...
Insurance Database Import / Export Procedure Policy: Student Health Services utilizes a custom FileMaker Pro insurance database to facilitate the management of student eligibility, enrollment, and insurance. The database is used to manage eligibility and enrollment in the UC Student Health Insurance Plan (UC SHIP). The database incorporates demographic enrollment data extracts from the campus Academic Information System (AIS) along with data extracts from the Wells Fargo Insurance Services on-line waiver web site. The database handles student premium charges and credits which are transferred in the AIS system. The database also handles all the undergraduate and graduate insurance rosters that are submitted to the UC SHIP insurance administrator, Anthem Blue Cross. Finally, the database is used to create an import file of all eligible students and their insurance plans which is imported into the Student Health Services Electronic Medical Record (EMR) system. The import process creates new patient records in the EMR, updates existing patient records and sets each patient’s eligibility and insurance. Procedure: 1. Transfer the latest AIS demographic file(s) from the AIS server to the insurance database Extracts folder. a. Open the AIS FTP client on your desktop. b. Log into the AIS server by selecting AIS from the Profiles drop-down menu. c. Enter your password (same as email password). d. Navigate to the Extracts folder on the FileMaker server. e. Navigate to the SHS folder (/data/CC/outbox/SHS) on the remote server (set as default home). f. Select latest demographic files to transfer to the extracts folder.
AIS FTP.lnk
Insurance Database Import / Export Procedure
Page 1of 17
Revised 10/16/09
2
1
1
Copy from
2
Copy to
2. Rename the AIS files to clearly identify the quarter they correspond to. a. Three files are run each day from AIS (generally prior quarter, current quarter, next quarter). The files are named by the AIS system as follows: i. Healthcenter_YYYYMMDD.txt, Healthcenter2_ YYYYMMDD.txt, Healthcenter3_ YYYYMMDD.txt b. Open the files in Word Pad to view the term code within the file and rename the files accordingly (example terms only).: i. Example: Healthcenter2098_YYYYMMDD.txt (Fall quarter 2009), Healthcenter2100_YYYYMMDD.txt (Winter quarter 2010), Healthcenter2102_YYYYMMDD.txt (Spring quarter 2010). ii. This will help to ensure that you are importing the correct term’s data into the correct term in the database. 3. Import the AIS file(s) into FileMaker Insurance Database Note: Generally you will import at least two AIS files (i.e. different quarters) during the weekly import process. IMPORT THE PRIOR OR FUTURE AIS FILE FIRST. This is so that the current active quarter will be updated last which is necessary to export the latest demographic and insurance file to be imported into PnC. a. From the main login screen, select the “Admin” button located on the right side of the screen.
Insurance Database Import / Export Procedure
Page 2of 17
Revised 10/16/09
b. Select the quarter that corresponds to the AIS file you are importing (i.e. Fall quarter in example below). Then select the “Import from AIS” link.
Insurance Database Import / Export Procedure
Page 3of 17
Revised 10/16/09
The following dialogue boxes will appear confirming that you have selected the correct quarter and have the correct import file. Confirm each message and select Yes to continue.
Insurance Database Import / Export Procedure
Page 4of 17
Revised 10/16/09
c. The Open File dialogue box will appear. Navigate to the Extracts folder and select the correct demographic file to import and select Open.
d. The Import Field Mapping dialogue box will appear. Select Import.
e. The Import Options dialogue box will appear. Select OK. The database will begin the import process. This may take several minutes to complete.
4. Repeat steps 3a-3e for additional files.
Insurance Database Import / Export Procedure
Page 5of 17
Revised 10/16/09
5. Transfer the latest Waiver file(s) from the Wells Fargo secure server to the insurance database Extracts folder.. a. Open the Wells Fargo FTP client on your desktop. b. Log into the secure server by selecting Wells Fargo Secure Website certificate and select Connect. c. Navigate to the Extracts folder on the FileMaker server. d. Navigate to the Outbound folder on the remote server. e. Select latest waiver file to transfer to your desktop folder. f. Right click on the file and select Download and then select Now.
Wells Fargo FTP.lnk
Insurance Database Import / Export Procedure
Page 6of 17
Revised 10/16/09
6. Import the latest waiver file, if one is available. a. Select the quarter that corresponds to the waiver file you are importing (i.e. Fall quarter in example below). Then select the “Import Waivers” link.
The following dialogue boxes will appear confirming that you have selected the correct quarter and have the correct import file. Confirm each message and select OK to continue.
b. The Open File dialogue box will appear. Navigate to the Extracts folder and select the correct waiver file to import and select Open.
Insurance Database Import / Export Procedure
Page 7of 17
Revised 10/16/09
Change Files of type: to All Files (*.*)
c. The Import Field Mapping dialogue box will appear. Select Import.
d. The Import Options dialogue box will appear. Select OK. The database will begin the import process. This may take several minutes to complete.
7. Repeat steps 7a-7c for additional files.
Insurance Database Import / Export Procedure
Page 8of 17
Revised 10/16/09
8. Export PnC Addresses using the Ad Hoc Reports application. a. A custom report, PnC Addresses, has been created which exports the necessary fields for import into the Insurance Database. i. Launch OpenReport from the PnC Application Manager ii. Double click the PnC Addresses report located in the Public Reports folder
iii. Check that the term codes are correct and select Run Report
iv. The requested data will now populate the data area of the report. Next select Report from the file menu and then select Export List. Name the report and save to the PnC Addresses report folder.
Insurance Database Import / Export Procedure
Page 9of 17
Revised 10/16/09
v. Import the .csv file into Excel. The file is located at \\shs-pnc-fs.ad\PnC\Releases\SHS Share\Administration Share\IT Admin\PnC Data\PnC Addresses. Search for and remove the special characters ( ) – and empty spaces from the local phone data field. Save the file as an Excel worksheet (i.e. PnC_Addresses_20100111.xls) in the Extracts folder in the current year Insurance folder on the PnC fileserver..
10. Export Charge and Credit files and Transfer to AIS. a. From the main login screen, select the “Exports” button located on the right side of the screen.
b. Select the quarter that corresponds to the export files you are exporting (i.e. Fall quarter in example below). Then select the USHIP, GSHIP, and CruzCare export links on the right side of the screen.
c. Name the files according to the following convention and save them to the Charges folder in the current year Insurance folder on the FileMaker server: i. InsuranceName_FileType_Term_CurrentDate.dat ii. Important: be sure to include the extension .dat in the actual file name iii. iv. v. vi. vii. viii. ix.
d. Transfer the files to the Charges folder in the current year Insurance folder on the PnC fileserver. e. Transfer the files to the AIS server. i. Open the SSH Secure File Transfer Client on your desktop. ii. Log into the AIS server by selecting AIS from the Profiles drop-down menu. iii. Enter your password (same as email password). iv. Navigate to the SHS folder (/data/SHS/inbox) on the remote server. v. Select the charge/credit files to transfer to the server. vi. Drag the files from the charges folder to the AIS server.
11. Export Roster files and Transfer to Wells Fargo. a. From the main login screen, select the “Exports” button located on the right side of the screen.
b. Select the quarter that corresponds to the export files you are exporting (i.e. Fall quarter in example below). Then select the USHIP and GSHIP Roster export links on the left side of the screen.
c. Name the files according to the following convention and save them to the Rosters folder in the current year Insurance folder on the FileMaker server: i. InsuranceName_Term_Roster_CurrentDate.txt ii. Important: be sure to include the extension .txt in the actual file name iii. USHIP_fall_roster_20080204.txt iv. GSHIP_fall_roster_20080204.txt d. Transfer the roster files to the Wells Fargo secure server. i. Open the SecureTransport Client on your desktop. ii. Log into the secure server by selecting Wells Fargo Secure Website certificate and select Connect. iii. Select the roster files to transfer to the secure server. iv. Upload the files.
Summary Notes: 1. Import prior or future quarter AIS file first. 2. Import current quarter AIS file second. This is because the export Active AIS should always reflect current quarter information and export relies on the data stored in the Import AIS database. 3. Import current quarter waiver file. 4. Export local address from PnC using the Ad Hoc Reports application. A saved custom report, PnC_LocalAddresses.ord, has been created which export the necessary fields for import into the Insurance Database. Import the text file into Excel. Search and remove any values of null and remove the special characters ( ) – from the local phone data field. Save the file as an Excel worksheet. 5. Import the PnC Addresses into the Insurance Database. 6. Run the Update Addresses script. 7. Find any records where gender value is U and change to F as a default. This must be done in both the Insurance Database and the Import AIS database. 8. Create charge/credit files for the respective quarters, naming and saving to the designated transfer folder. 9. Create rosters for the respective quarters, naming and saving to the designated transfer folder. 10. Export the Active AIS file from the Insurance Database making sure that the current quarter is selected. Name the file and save to the designated folder. 11. Copy the Active AIS file to the PnC server in Releases/Imports-Exports folder. 12. Run the Registration Interface program selecting the latest file for import into PnC.
