import export notes - university of california, santa...

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 of 17 Revised 10/16/09

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.txtb. 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 DatabaseNote: 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.


121

2

Copy from

Copy to

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.


The following dialogue boxes will appear confirming that you have selected the correct quarter and have thecorrect import file. Confirm each message and select Yes to continue.


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.


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


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.


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.


Change Files of type: to All Files (*.*)

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.


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..


9. Run the Import PnC Addresses script

a. Select the current address file and select Open

b. Then Specify Excel Data dialog box will appear. Select OK.


c. The First Row Option dialog box will appear. Select OK.

d. The Import Field Mapping dialog box will appear. Select Import.


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.datii. Important: be sure to include the extension .dat in the actual file name

iii. USHIP_charges_2080_20080204.dativ. USHIP_reverse_2080_20080204.datv. USHIP_cancel_2080_20080204.dat

vi. GSHIP_waivers_2080_20080204.datvii. CruzCare_charges_2080_20080204.dat

viii. CruzCare _reverse_2080_20080204.datix. CruzCare _cancel_2080_20080204.dat

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.txtii. Important: be sure to include the extension .txt in the actual file name

iii. USHIP_fall_roster_20080204.txtiv. 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.


import export notes - university of california, santa...

Documents