Tables-reference

From VPOIDS Documentation

Contents

activity

  • Database of events reported in the activity feed.
Column Type Length Description
id int
content text 65535
created_at datetime

afa_leg

  • Additional information required for linking legs.
Column Type Length Description
id int
pilot_name varchar 50
day_phone varchar 16
night_phone varchar 16
fax_phone varchar 16
aircraft_id int
n_number varchar 8
aircraft_color varchar 20
etd varchar 15
eta varchar 15
afa_org_id int
pilot_mobile_phone varchar 16
mission_leg_id int

afa_org

  • Information about other VPO organizations.
Column Type Length Description
id int
name varchar 60
org_phone varchar 16
home_page_url varchar 80
org_fax varchar 16
ref_contact_name varchar 25
ref_contact_email varchar 80
vpo_soap_server_url varchar 125
vpo_request_post_email varchar 125
vpo_user_id varchar 25
vpo_user_password varchar 25
vpo_org_id varchar 5
afids_requester_user_name varchar 25
afids_requester_password varchar 75
afids_server_url varchar 125
afids_request_post_email varchar 125
phone_number1 varchar 16
phone_number2 varchar 16

affiliation

  • Reference table of affiliations of people who come in contac
Column Type Length Description
id int
description varchar 25

agency

  • Requesting agencies which the organization serves.
Column Type Length Description
id int
name varchar 80
address1 varchar 40
address2 varchar 40
city varchar 30
county varchar 30
state varchar 2
country varchar 30
zipcode varchar 10
phone varchar 16
comment varchar 40
fax_phone varchar 16
fax_comment varchar 40
email varchar 80

aircraft

  • Aircraft categories used by the member pilots.
Column Type Length Description
id int
make varchar 50
model varchar 25
name varchar 50
tail varchar 20
faa varchar 10
engines tinyint
def_seats tinyint
speed int
pressurized tinyint
cost int
range int
ac_load int

airline

  • Airline partners who provide transportation.
Column Type Length Description
id int
name varchar 255

airport

  • Database of airports.
Column Type Length Description
id int
ident varchar 4
name varchar 30
city varchar 30
state varchar 2
latitude float
longitude float
runway_length int
wing_id int
gmt_offset int
dst_offset int
zipcode varchar 5
closed tinyint
faa_site_number varchar 18
atct tinyint
is_private tinyint
non_commercial_landing_fee tinyint
elevation int

application

  • Members' applications, both initial new member application a
Column Type Length Description
id int
ccard_number varchar 20
ccard_code varchar 5
ccard_expire date
member_id int
renewal tinyint
date datetime
vocation_class_id int
company varchar 40
license_type varchar 10
ifr tinyint
multi_engine tinyint
se_instructor varchar 5
me_instructor varchar 5
other_ratings varchar 50
total_hours int
fbo varchar 80
mission_orientation tinyint
mission_coordination tinyint
pilot_recruitment tinyint
fund_raising tinyint
celebrity_contacts tinyint
hospital_outreach tinyint
member_meetings tinyint
media_relations tinyint
telephone_work tinyint
computers tinyint
clerical tinyint
publicity tinyint
writing tinyint
speakers_bureau tinyint
executive_board tinyint
wing_team tinyint
graphic_arts tinyint
other_volunteer varchar 50
t_shirt_size char 5
dues_amount_paid float
method_of_payment varchar 20
check_number int
donation_amount_paid float
title varchar 12
first_name varchar 40
last_name varchar 40
address1 varchar 40
address2 varchar 40
city varchar 30
state varchar 2
zipcode varchar 10
day_phone varchar 16
day_comment varchar 40
eve_phone varchar 16
eve_comment varchar 40
mobile_phone varchar 16
mobile_comment varchar 40
pager_phone varchar 16
pager_comment varchar 40
fax_phone1 varchar 16
fax_comment1 varchar 40
fax_phone2 varchar 16
fax_comment2 varchar 40
other_phone varchar 16
other_comment varchar 40
email varchar 80
spouse_first_name varchar 40
spouse_last_name varchar 40
applicant_pilot tinyint
languages_spoken varchar 125
home_base varchar 20
secondary_home_bases varchar 30
aircraft_primary_id int
aircraft_primary_own tinyint
aircraft_primary_ice tinyint
aircraft_primary_seats int
aircraft_primary_n_number varchar 10
aircraft_secondary_id int
aircraft_secondary_own tinyint
aircraft_secondary_ice tinyint
aircraft_secondary_seats int
aircraft_secondary_n_number varchar 10
pilot_certificate varchar 40
medical_class int
ifr_hours int
multi_hours int
other_hours int
date_of_birth date
height int
weight int
availability_weekdays tinyint
availability_weeknights tinyint
availability_weekends tinyint
availability_notice tinyint
availability_last_minute tinyint
availability_copilot tinyint
affirmation_agreed tinyint
insurance_agreed tinyint
volunteer_interest varchar 255
company_position varchar 40
company_match_funds tinyint
company_business_category_id int
referral_source int
premium_choice int
premium_size int
ccard_approval_number varchar 20
ccard_error_code varchar 10
ccard_avs_result varchar 25
processed_date datetime
event_planning tinyint
web_internet tinyint
foundation_contacts tinyint
aviation_contacts tinyint
printing tinyint
member_aopa tinyint
member_kiwanis tinyint
member_rotary tinyint
member_lions tinyint
novapointe_id int
premium_ship_date datetime
premium_ship_method varchar 10
premium_ship_cost int
premium_ship_tracking_number varchar 35
spouse_pilot tinyint
applicant_copilot tinyint
pager_email varchar 80
member_99s tinyint
member_wia tinyint
mission_email_optin tinyint
hseats_interest tinyint
master_application_id int
master_member_id int
referral_source_other varchar 40
secondary_email varchar 80
payment_transaction_id int
aircraft_third_ice tinyint
aircraft_third_id int
aircraft_third_n_number varchar 10
aircraft_third_own tinyint
aircraft_third_seats int
drivers_license_number varchar 25
drivers_license_state varchar 2
emergency_contact_name varchar 40
emergency_contact_phone varchar 40
country varchar 30
middle_name varchar 40
suffix varchar 25
nickname varchar 40
veteran tinyint
gender varchar 7

application_temp

  • Temporary container for applications received online. (see a
Column Type Length Description
id int
application_date datetime
renewal tinyint
title varchar 12
first_name varchar 40
last_name varchar 40
address1 varchar 40
address2 varchar 40
city varchar 30
state varchar 2
zipcode varchar 10
day_phone varchar 16
day_comment varchar 40
eve_phone varchar 16
eve_comment varchar 40
mobile_phone varchar 16
mobile_comment varchar 40
pager_phone varchar 16
pager_comment varchar 40
fax_phone1 varchar 16
fax_comment1 varchar 40
fax_phone2 varchar 16
fax_comment2 varchar 40
other_phone varchar 16
other_comment varchar 40
email varchar 80
spouse_first_name varchar 40
spouse_last_name varchar 40
applicant_pilot tinyint
spouse_pilot tinyint
applicant_copilot tinyint
languages_spoken varchar 125
home_base varchar 20
fbo_name varchar 80
aircraft_primary_id int
aircraft_primary_own tinyint
aircraft_primary_ice tinyint
aircraft_primary_seats int
aircraft_primary_n_number varchar 10
aircraft_secondary_id int
aircraft_secondary_own tinyint
aircraft_secondary_ice tinyint
aircraft_secondary_seats int
aircraft_secondary_n_number varchar 10
pilot_certificate varchar 40
ratings varchar 80
medical_class int
license_type varchar 10
total_hours int
ifr_hours int
multi_hours int
other_hours int
date_of_birth datetime
height int
weight int
availability_weekdays tinyint
availability_weeknights tinyint
availability_weekends tinyint
availability_notice tinyint
availability_last_minute tinyint
availability_copilot tinyint
affirmation_agreed tinyint
insurance_agreed tinyint
volunteer_interest varchar 255
company_name varchar 40
company_position varchar 40
company_match_funds tinyint
company_business_category_id int
referral_source int
premium_choice int
premium_size int
dues_amount_paid int
donation_amount_paid int
method_of_payment_id int
ccard_approval_number varchar 20
ccard_error_code varchar 10
ccard_avs_result varchar 25
processed_date datetime
member_id int
mission_orientation tinyint
mission_coordination tinyint
pilot_recruitment tinyint
fund_raising tinyint
celebrity_contacts tinyint
hospital_outreach tinyint
media_relations tinyint
telephone_work tinyint
computers tinyint
clerical tinyint
publicity tinyint
writing tinyint
speakers_bureau tinyint
wing_team tinyint
graphic_arts tinyint
event_planning tinyint
web_internet tinyint
foundation_contacts tinyint
aviation_contacts tinyint
printing tinyint
member_aopa tinyint
member_kiwanis tinyint
member_rotary tinyint
member_lions tinyint
person_id int
novapointe_id int
premium_ship_date datetime
premium_ship_method varchar 10
premium_ship_cost int
premium_ship_tracking_number varchar 35
referer_name varchar 40
referral_session_id int
aircraft_third_id int
aircraft_third_own tinyint
aircraft_third_ice tinyint
aircraft_third_seats int
aircraft_third_n_number varchar 10
ip_address varchar 20
pager_email varchar 80
member_99s tinyint
member_wia tinyint
mission_email_optin tinyint
hseats_interest tinyint
master_application_id int
master_member_id int
referral_source_other varchar 40
secondary_email varchar 80
payment_transaction_id int
middle_name varchar 40
suffix varchar 25
nickname varchar 40
veteran tinyint
gender varchar 7
emergency_contact_name varchar 40
emergency_contact_phone varchar 40
country varchar 30
drivers_license_state varchar 2
drivers_license_number varchar 25
is_complete tinyint
member_class_id int
wing_id int
ccard_exp_month int
ccard_exp_year int
check_number int
processed_by int
disposition int

audit_log

  • Log of user events which can be used for auditing.
Column Type Length Description
id int
date_time timestamp
person_id int
event varchar 15
event_reason varchar 1000

availability

  • Members' availability for accepting missions.
Column Type Length Description
id int
member_id int
first_date date
last_date date
not_available tinyint
no_weekday tinyint
no_night tinyint
last_minute tinyint
no_weekend tinyint
as_mission_mssistant tinyint
availability_comment varchar 50

board_committee

  • Committees to which board members may belong.
Column Type Length Description
id int
board_member_id int
committee_id int
chairman tinyint

board_member

  • Identifies members of the organization's board of directors.
Column Type Length Description
id int
person_id int
startyear int
endyear int

camp

  • Information about the camp when a mission is a camp mission.
Column Type Length Description
id int
agency_id int
airport_id int
fbo_id int
camp_name varchar 40
camp_phone varchar 16
session varchar 100
camp_phone_comment varchar 40
lodging_name varchar 40
lodging_phone varchar 16
lodging_phone_comment varchar 40
arrival_date datetime
arrival_comment varchar 150
departure_date datetime
departure_comment varchar 150
comment varchar 500
grouped_mission varchar 5
flight_information varchar 255

camp_passenger

  • M-M table connecting camps and passengers.
Column Type Length Description
camp_id int
passenger_id int
airport_id int
note varchar 255
link int
return_airport_id int
mission_id int

camp_pilot_passenger

  • M-M table connecting pilots with camp-passengers
Column Type Length Description
camp_id int
member_id int
passenger_id int
flight_date date

campaign

Column Type Length Description
id int
campaign_decs varchar 25
premium_sku int
premium_min int

committee

  • Committees to which board members may be assigned. See board
Column Type Length Description
id int
name varchar 40

companion

  • Database of companions who travel with the passengers.
Column Type Length Description
id int
passenger_id int
name varchar 50
relationship varchar 30
date_of_birth date
weight int
companion_phone varchar 16
companion_phone_comment varchar 40
person_id int

company

  • Tracks the companies which employ members.
Column Type Length Description
id int
name varchar 40
has_matching_program tinyint
has_payroll_withholding_program tinyint

contact

  • Information about prospects who come in contact with the org
Column Type Length Description
id int
person_id int
ref_source_id int
send_app_format varchar 10
comment varchar 100
letter_sent date
contact_type_id int
date_added date
date_updated date
company_name varchar 60

contact_request

  • A container for the contact request information from the onl
Column Type Length Description
id int
first_name varchar 40
last_name varchar 40
title varchar 12
address1 varchar 40
address2 varchar 40
city varchar 30
state varchar 2
zipcode varchar 10
country varchar 30
day_phone varchar 40
day_comment varchar 40
eve_phone varchar 40
eve_comment varchar 40
fax_phone varchar 40
fax_comment varchar 40
mobile_phone varchar 40
mobile_comment varchar 40
pager_phone varchar 40
pager_comment varchar 40
email varchar 80
ref_source_id int
send_app_format varchar 10
comment varchar 100
contact_type_id int
person_id int
processed tinyint
letter_to_send int
letter_sent_date datetime
request_date datetime
session_id varchar 120
ip_address varchar 20

contact_type

  • Reference table for the types of contacts (see contact table
Column Type Length Description
id int
contact_type_desc varchar 40

coordinator

  • Database of members who have the role of coordinator.
Column Type Length Description
id int
member_id int
lead_id int
coor_of_the_week tinyint
coor_week_date datetime
initial_contact varchar 40

county

  • Database of US counties
Column Type Length Description
fips_code int
name varchar 25

discussion

Column Type Length Description
id int
user_id int
leg_id int
comment text 65535
created_at datetime
is_split tinyint

donation

Column Type Length Description
id int
donor_id int
gift_date datetime
gift_amount decimal
deductible_amount decimal
gift_type int
check_number varchar 10
campain_id int
fund_id int
gift_note varchar 125
printed_note varchar 255
receipt_generated_date datetime
follow_up tinyint
premium_order_date timestamp
tribute_first_name varchar 30
tribute_last_name varchar 30
tribute_address1 varchar 30
tribute_address2 varchar 30
tribute_city varchar 30
tribute_state varchar 2
tribute_zipcode varchar 10
tribute_email varchar 80
tribute_category varchar 20
tribute_message varchar 255
tribute_deliver_by varchar 20
tribute_gift_id int
person_id int
processedDate datetime
novapointe_id varchar 30
ccard_approval_number varchar 30
novapointe_fulfillment_id varchar 30
novapointe_tracking_number varchar 40
novapointe_ship_date datetime
tribute_letter_sent_date datetime
send_tribute_card tinyint
tribute_image_id int
ecard_read_date datetime
ecardEmailSentDate datetime
ecard_email_sent_result int

donor

Column Type Length Description
id int
co_donor_id int
affiliation_id int
block_mailings tinyint
prospect_comment varchar 500
salutation varchar 125
company_id int
position varchar 40
donor_potential int
person_id int
date_added datetime
date_updated timestamp

dp_donor_mapping

  • Maps AFIDS persons to Donor Perfect using the ID assigned by
Column Type Length Description
id int
afids_person_id int
dp_donor_id int
dateAdded datetime

email_body

  • Text of emails sent using the mass email system
Column Type Length Description
id int
subject varchar 75
sender_name varchar 30
sender_email varchar 50
body text 65535
attach_file_path varchar 255
recipients text 65535
recipients_type varchar 50

email_letter

  • Text of emails sent using the mass email system
Column Type Length Description
id int
subject varchar 50
sender_name varchar 30
sender_email varchar 50
body text 65535
attach_file_path varchar 255
recipients text 65535

email_list

  • Database of email distribution lists offered by the organiza
Column Type Length Description
id int
description varchar 25
is_private tinyint
wing_id int

email_list_person

  • M-M mapping of persons to email distribution lists (see emai
Column Type Length Description
id int
list_id int
person_id int

email_list_role

Column Type Length Description
list_id int
role_id int

email_log

  • Log of emails sent using the mass email system. Used to trou
Column Type Length Description
id int
send_date datetime
sender_name varchar 40
sender_email varchar 80
recipient_count int
letter_id int
error_count int
batch_id int

email_queue

  • Contains the emails waiting (queued) to be sent.
Column Type Length Description
id int
person_id int
letter_id int
request_date datetime
send_date datetime
priority tinyint
send_status varchar 255

email_template

Column Type Length Description
id int
name varchar 255
subject varchar 255
sender_name varchar 255
sender_email varchar 255
body text 65535
person_id int

email_tracking

Column Type Length Description
id int
track_date datetime
send_date datetime
letter_id int
rec_count int
afids_user_id int

event

  • Database of events in the organization's calendar of events.
Column Type Length Description
id int
event_name varchar 60
event_date datetime
event_time varchar 30
location varchar 60
short_desc varchar 500
long_desc text 65535
contact_info varchar 100
email_sent_date datetime
online_reservation tinyint
adult_cost int
child_cost int
adult_door_cost int
child_door_cost int
signup_deadline datetime
onsite_signup_ok tinyint
max_persons int
collect_secure_info tinyint
addl_info_fields varchar 500
addl_info_fields_help varchar 500

event_reservation

  • Database of individual reservations for events (see event ta
Column Type Length Description
id int
event_id int
member_id int
reservation_date datetime
first_name varchar 60
last_name varchar 60
address varchar 40
city varchar 30
state varchar 2
zipcode varchar 10
phone varchar 16
email varchar 60
adult_guests int
child_guests int
guest_names longtext 4294967295
amt_paid int
method_of_payment varchar 10
payment_date datetime
auth_number varchar 10
status varchar 10
comments varchar 255
collect_secure_info tinyint
addl_info_fields varchar 500
novapointe_trans_id varchar 12

event_wings

Column Type Length Description
event_id int
wing_id int

faa_nfdc_facilities

  • Used for importing airport data from the FAA
Column Type Length Description
site_number varchar 18
ap_type varchar 25
location_id varchar 4
effective_date date
state char 2
county varchar 40
city varchar 40
facility_name varchar 125
ownership char 2
apUse char 2
arp_latitude_s decimal
arp_longitude_s decimal
arp_elevation int
activiation_date date
atct char 1
non_commercial_landing_fee char 1
manager_zipcode varchar 5
owner_address varchar 125
owner_csz varchar 50
owner_phone varchar 50
manager varchar 50
manager_address varchar 125
manager_csz varchar 50
manager_phone varchar 50

faa_nfdc_runways

  • Used for importing airport data from the FAA
Column Type Length Description
site_number varchar 18
runway_id varchar 20
runway_length int
runway_width int
runway_surface_type_condition varchar 12
runway_surface_treatment varchar 20

fbo

  • Database of FBOs (fixed base operators) operating at airport
Column Type Length Description
id int
airport_id int
name varchar 40
voice_phone varchar 16
fax_phone varchar 16
discount_amount tinyint
fuel_discount double
default_fbo int
address varchar 40

fund

  • Deprecated in AFIDS 2.0
Column Type Length Description
id int
fund_desc varchar 25

gift_type

Column Type Length Description
id int
gift_type_desc varchar 25

itinerary

  • A umbrella container for all the missions associated with a
Column Type Length Description
id int
date_requested datetime
mission_request_id int
mission_type_id int
apoint_time varchar 50
passenger_id int
requester_id int
facility varchar 50
lodging varchar 50
orgin_city varchar 50
orgin_state varchar 2
dest_city varchar 50
dest_state varchar 2
waiver_need tinyint
need_medical_release tinyint
comment varchar 255
agency_id int
camp_id int
leg_id int
point_time time
timetype varchar 2
cancel_itinerary tinyint
copied_itinerary varchar 15

member

  • Database of the organization's members.
Column Type Length Description
id int
person_id int
external_id int
wing_id int
member_class_id int
master_member_id int
join_date date
active tinyint
inactive_reason varchar 15
inactive_comment varchar 40
flight_status varchar 20
co_pilot tinyint
contact varchar 10
date_of_birth date
weight int
spouse_name varchar 50
languages varchar 50
coordinator_notes varchar 255
renewed_date date
renewal_date date
renewal_notice1 date
renewal_notice2 date
renewal_notice3 date
renewal_notice4 date
review_done date
ed_new_member_notify date
wn_new_memberN_ntify date
no_wing_contact_ack tinyint
hold_harmless_received tinyint
member_welcomed date
badge_made date
notebook_sent date
clothing_sent date
s_mod_member_notify varchar 255
w_mod_member_notify varchar 255
renew_mark tinyint
renewal_sent_date tinyint
s_late_renewal_notify tinyint
w_late_renewal_notify tinyint
s_inactive_notify tinyint
w_inactive_notify tinyint
w_not_oriented_notify_date1 date
w_not_oriented_notify_date2 date
emergency_contact_name varchar 40
emergency_contact_phone varchar 40
drivers_license_state varchar 2
drivers_license_number varchar 25
height int
secondary_wing_id int

member_class

  • Reference table of member classes identifying their renewal
Column Type Length Description
id int
name varchar 20
initial_fee int
renewal_fee int
sub_initial_fee int
sub_renewal_fee int
renewal_period tinyint
fly_missions tinyint
newsletters tinyint
sub_newsletters tinyint

member_wing_job

  • Used to assign members to volunteer positions within the org
Column Type Length Description
id int
member_id int
wing_job_id int

member_wing_stats

  • Table of member statistics accumulated on a monthly basis.
Column Type Length Description
id int
month int
year int
wing_id int
flight_status varchar 20
member_count int

mission

  • Database of information about a passenger travel event for o
Column Type Length Description
id int
external_id int
request_id int
itinerary_id int
mission_type_id int
mission_date datetime
date_requested datetime
passenger_id int
requester_id int
agency_id int
other_requester_id int
other_agency_id int
camp_id int
coordinator_id int
appt_date datetime
flight_time varchar 60
treatment varchar 30
comment varchar 100
appointment varchar 50
mission_specific_comments varchar 255
start int 1-home, 2 treatment
apoint_time varchar 50
mission_count int
b_weight int
b_type varchar 2
b_desc varchar 255
cancel_mission tinyint
copied_mission varchar 15

mission_companion

  • M-M table mapping missions to companions (see mission table
Column Type Length Description
mission_id int
companion_id int

mission_companion_count

  • VIEW
Column Type Length Description
companion_count bigint
companion_total_weight decimal

mission_leg

  • A leg of a mission with a single origin airport, destination
Column Type Length Description
id int
mission_id int
leg_number int
from_airport_id int air
to_airport_id int air
reverse_from int
pass_on_board tinyint
baggage_weight int
baggage_desc varchar 50
coordinator_id int
public_c_note varchar 255
private_c_note varchar 255
copilot_wanted tinyint
pilot_id int
aca_org varchar 25
copilot_id int
miss_assis_id int
backup_pilot_id int
backup_copilot_id int
backup_miss_assis_id int
cancelled varchar 25
cancel_comment varchar 60
waiver_received datetime
web_coordinated tinyint
mission_report_id int
pilot_aircraft_id int
fbo_id int
fbo_address_new varchar 100
fbo_dest_id int
share_afa_org_id int
transportation varchar 20
ground_origin varchar 10
ground_destination varchar 10
custom_origin_address varchar 75
custom_destination_address varchar 75
flight_time time commercial
airline_id int commercial
fund_id int commercial
confirm_code varchar 50 commercial
flight_cost int commercial
comm_origin varchar 50 commercial
comm_dest varchar 50 commercial
flight_number varchar 50 commercial
departure time commercial
arrival time commercial
prefix varchar 10 air or g
cancel_mission_leg int
copied_mission_leg varchar 15

mission_leg_change

  • Log of events that change a mission leg record. Used for tra
Column Type Length Description
id int
leg_id int
change_date datetime
cancelled varchar 25
pilot_id int
cancelled_to varchar 25
pilot_id_new int
change_by int
event_type_id int
event_desc varchar 175
person_id int

mission_photo

  • Photos taken and submitted by members who flew a mission.
Column Type Length Description
id int
first_name varchar 50
last_name varchar 50
submission_date datetime
mission_date datetime
passenger_name varchar 50
pilot_name varchar 50
origin varchar 25
destination varchar 25
caption varchar 255
comment varchar 255
photo_filename varchar 75
mission_id int
review_date datetime
review_by int
approved tinyint
filesize int
height int
width int
file_format int
photo_quality int
event_id int
leg_id int
category varchar 25
photo_use varchar 25

mission_report

  • After a mission is completed, the command pilot completes th
Column Type Length Description
id int
report_date datetime
mission_date datetime
copilot_name varchar 50
member_copilot tinyint
aircraft_id int
n_number varchar 8
makemodel varchar 60
hobbs_time decimal
passenger_names varchar 255
mission_comments text 65535
expense_report text 65535
approved tinyint
pickup_airport_ident varchar 25
dropoff_airport_ident varchar 25
routing varchar 40
commercial_ticket_cost int
airline_ref_number varchar 40
airline_owrt varchar 2
mileage int
photo1 varchar 255
photo2 varchar 255
photo3 varchar 255
photo4 varchar 255
photo5 varchar 255

mission_request

  • A request for travel completed by a passenger or requester.
Column Type Length Description
id int
requester_id int
requester_date datetime
request_by int
on_behalf varchar 20
appt_date datetime
mission_date datetime
return_date datetime
baggage_weight int
baggage_desc varchar 50
pass_title varchar 7
pass_first_name varchar 30
pass_last_name varchar 30
pass_address1 varchar 50
pass_address2 varchar 50
pass_city varchar 30
pass_county varchar 30
pass_state varchar 30
pass_country varchar 30
pass_zipcode varchar 10
pass_day_phone varchar 16
pass_day_comment varchar 30
pass_eve_phone varchar 16
pass_eve_comment varchar 30
pass_mobile_phone varchar 16
pass_mobile_comment varchar 30
pass_pager_phone varchar 16
pass_pager_comment varchar 30
pass_other_phone varchar 16
pass_other_comment varchar 30
pass_email varchar 30
pass_date_of_birth datetime
illness varchar 60
financial varchar 100
pass_weight int
releasing_physician varchar 40
release_phone varchar 16
release_phone_comment varchar 30
release_fax varchar 16
release_fax_comment varchar 30
release_email varchar 30
treating_physician varchar 30
treating_phone varchar 16
treating_phone_comment varchar 30
treating_fax varchar 16
treating_fax_comment varchar 30
req_title varchar 7
req_first_name varchar 30
req_last_name varchar 30
agency_name varchar 40
req_address1 varchar 30
req_address2 varchar 30
req_city varchar 30
req_county varchar 30
req_state varchar 2
req_country varchar 40
req_zipcode varchar 10
req_day_phone varchar 16
req_day_comment varchar 30
req_eve_phone varchar 16
req_eve_comment varchar 30
req_mobile_phone varchar 16
req_mobile_comment varchar 30
req_pager_phone varchar 16
req_pager_comment varchar 30
req_other_phone varchar 16
req_other_comment varchar 30
req_email varchar 30
req_secondary_email varchar 50
req_pager_email varchar 50
req_position varchar 80
req_discharge varchar 2
orgin_ident varchar 40
orgin_id int
orgin_city varchar 30
orgin_state varchar 2
orgin_zipcode varchar 10
dest_ident varchar 4
dest_id int
dest_city varchar 30
dest_state varchar 2
dest_zipcode varchar 10
com1_name varchar 50
com1_relationship varchar 30
com1_date_of_birth datetime
com1_weigth int
com1_phone varchar 16
com1_comment varchar 30
com2_name varchar 30
com2_relationship varchar 30
com2_date_of_birth datetime
com2_weigth int
com2_phone varchar 16
com2_comment varchar 30
com3_name varchar 30
com3_relationship varchar 30
com3_date_of_birth datetime
com3_weigth int
com3_phone varchar 16
com3_comment varchar 30
com4_name varchar 30
com4_relationship varchar 30
com4_date_of_birth datetime
com4_weigth int
com4_phone varchar 16
com4_comment varchar 30
com5_name varchar 30
com5_relationship varchar 30
com5_date_of_birth datetime
com5_weigth int
com5_phone varchar 16
com5_comment varchar 30
lodging_name varchar 30
lodging_phone varchar 16
lodging_phone_comment varchar 30
facility_name varchar 30
facility_phone varchar 16
facility_phone_comment varchar 30
req_language varchar 30
best_contact varchar 80
emergency_name varchar 30
emergency_phone1 varchar 16
emergency_phone1_comment varchar 30
emergency_phone2 varchar 16
emergency_phone2_comment varchar 30
comment varchar 80
processed_date datetime
session_id int
ip_address varchar 40
pass_fax_phone1 varchar 16
pass_fax_comment1 varchar 30
guar_first_name varchar 30
guar_last_name varchar 30
guar_address1 varchar 30
guar_address2 varchar 30
guar_city varchar 30
guar_state varchar 2
guar_zipcode varchar 10
guar_day_phone varchar 16
guar_day_comment varchar 30
guar_eve_phone varchar 16
guar_eve_comment varchar 30
guar_fax_phone varchar 16
guar_fax_comment varchar 30
guar_mobile_phone varchar 16
guar_mobile_comment varchar 30
guar_other_phone varchar 16
guar_other_comment varchar 30
guar_pager_phone varchar 16
guar_pager_comment varchar 30
guar_guar_email varchar 30
req_fax_phone1 varchar 16
req_fax_comment1 varchar 30
pass_english tinyint
pass_language varchar 50
treating_email varchar 30
pass_height int
pass_oxygen tinyint
pass_medical tinyint
referral_source_id int
follow_up_contact_name varchar 30
follow_up_contact_phone varchar 16
follow_up_email varchar 30
miss_req_orginator_afa_org_id int
afa_org_id int
afa_org_mission_id int
mission_request_type_id int
last_page_processed int
pass_gender varchar 10
pass_type int
pass_private_cons varchar 150
pass_public_cons varchar 150
appt_time time
flight_time time
waiver_required varchar 5
disposition int
processed_by int
refer_to_org int

mission_request_temp

  • Temporary container for the mission request with raw data pr
Column Type Length Description
id int
request_date datetime
request_by int
request_on_behalf int
appt_date datetime
return_date datetime
baggage_weight int
baggage_desc varchar 30
pass_title varchar 7
pass_first_name varchar 40
pass_last_name varchar 40
pass_address1 varchar 40
pass_address2 varchar 40
pass_city varchar 30
pass_county varchar 30
pass_state char 2
pass_country varchar 30
pass_zipcode varchar 10
pass_day_phone varchar 16
pass_day_comment varchar 40
pass_eve_phone varchar 16
pass_eve_comment varchar 40
pass_mobile_phone varchar 16
pass_mobile_comment varchar 40
pass_pager_phone varchar 16
pass_pager_comment varchar 40
pass_other_phone varchar 16
pass_other_comment varchar 40
pass_email varchar 80
pass_date_of_birth datetime
illness varchar 60
financial varchar 100
pass_weight smallint
releasing_physician varchar 40
release_phone varchar 16
release_phone_comment varchar 40
release_fax varchar 16
release_fax_comment varchar 40
release_email varchar 80
treating_physician varchar 40
treating_phone varchar 16
treating_phone_comment varchar 40
treating_fax varchar 16
treating_fax_comment varchar 40
req_title varchar 7
req_first_name varchar 40
req_last_name varchar 40
agency_name varchar 40
req_address1 varchar 30
req_address2 varchar 30
req_city varchar 30
req_county varchar 30
req_state char 2
req_country varchar 40
req_zipcode varchar 10
req_day_phone varchar 16
req_day_comment varchar 40
req_eve_phone varchar 16
req_eve_comment varchar 40
req_mobile_phone varchar 16
req_mobile_comment varchar 40
req_pager_phone varchar 16
req_pager_comment varchar 40
req_other_phone varchar 16
req_other_comment varchar 40
req_email varchar 80
origin_ident varchar 4
origin_id int
origin_city varchar 30
origin_state char 2
origin_zipcode varchar 10
dest_ident varchar 4
dest_id int
dest_city varchar 30
dest_state char 2
dest_zipcode varchar 10
com1_name varchar 50
com1_relationship varchar 30
com1_date_of_birth datetime
com1_weight smallint
com1_phone varchar 16
com1_comment varchar 40
com2_name varchar 50
com2_relationship varchar 30
com2_date_of_birth datetime
com2_weight smallint
com2_phone varchar 16
com2_comment varchar 40
com3_name varchar 50
com3_relationship varchar 30
com3_date_of_birth datetime
com3_weight smallint
com3_phone varchar 16
com3_comment varchar 40
com4_name varchar 50
com4_relationship varchar 30
com4_date_of_birth datetime
com4_weight smallint
com4_phone varchar 16
com4_comment varchar 40
lodging_name varchar 60
lodging_phone varchar 16
facility_name varchar 60
facility_phone varchar 16
facility_phone_comment varchar 40
req_language_spoken varchar 30
best_contact varchar 80
emergency_name varchar 40
emergency_phone1 varchar 16
emergency_phone2 varchar 16
emergency_phone1_comment varchar 40
emergency_phone2_comment varchar 40
comment varchar 125
processed_date datetime
session_id int
ip_address varchar 40
pass_fax_phone1 varchar 16
pass_fax_comment1 varchar 40
guar_first_name varchar 40
guar_last_name varchar 40
guar_address1 varchar 40
guar_address2 varchar 40
guar_city varchar 30
guar_state char 2
guar_zipcode varchar 10
guar_day_phone varchar 16
guar_day_comment varchar 40
guar_eve_phone varchar 16
guar_eve_comment varchar 40
guar_fax_phone1 varchar 16
guar_fax_comment1 varchar 40
guar_mobile_phone varchar 16
guar_mobile_comment varchar 40
guar_pager_phone varchar 16
guar_pager_comment varchar 40
guar_other_phone varchar 16
guar_other_comment varchar 40
guar_guar_email varchar 80
req_fax_phone1 varchar 16
req_fax_comment1 varchar 40
pass_english tinyint
pass_language varchar 20
appt_time varchar 20
requester_id int
passenger_id int
mission_id int
treating_email varchar 80
pass_height int
return_time varchar 20
pass_oxygen tinyint
pass_medical tinyint
referral_source_id int
follow_up_contact_name varchar 40
follow_up_contact_phone varchar 16
follow_up_email varchar 80
miss_req_originator_afa_org_id int
afa_org_id int
afa_org_mission_id int
mission_request_type int
last_page_processed int
guardian_middle_name varchar 40
guardian_nickname varchar 40
guardian_pager_email varchar 80
guardian_secondary_email varchar 80
guardian_suffix varchar 25
guardian_title varchar 7
guardian_veteran tinyint
pass_gender varchar 7
pass_middle_name varchar 40
pass_nickname varchar 40
pass_pager_email varchar 80
pass_secondary_email varchar 80
pass_suffix varchar 25
pass_veteran tinyint
req_middle_name varchar 40
req_nickname varchar 40
req_pager_email varchar 80
req_secondary_email varchar 80
req_suffix varchar 25
req_veteran tinyint
lodging_phone_comment varchar 40
req_gender varchar 7
guardian_gender varchar 7
agency_position varchar 40

mission_type

  • A reference table of the types of missions.
Column Type Length Description
id int
name varchar 40
stat_count tinyint
color varchar 25

mission_type_wing_stats

  • Table of permanent statistics of the organization's missions
Column Type Length Description
id int
month_no int
year_no int
wing_id int
mission_type_id int
leg_count int
total_hours int
aircraft_cost int
commercial_cost int

page_content

Column Type Length Description
id mediumint
pagename varchar 255
content text 65535
content_key varchar 50

passenger

  • Database of passengers, which are those persons who are rece
Column Type Length Description
id int
person_id int
passenger_type_id int
parent varchar 50
date_of_birth datetime
illness varchar 60
financial varchar 255
weight int
public_considerations varchar 255
private_considerations varchar 255
releasing_physician varchar 40
releasing_phone varchar 16
lodging_name varchar 40
lodging_phone varchar 16
lodging_phone_comment varchar 40
facility_name varchar 40
facility_city varchar 50
facility_state varchar 2
facility_phone varchar 16
facility_phone_comment varchar 40
requester_id int
medical_release_requested datetime
medical_release_received datetime
passenger_illness_category_id int
releasing_fax1 varchar 16
releasing_fax1_comment varchar 40
releasing_email varchar 80
treating_physician varchar 40
treating_phone varchar 16
treating_fax1 varchar 16
treating_fax1_comment varchar 80
treating_email varchar 80
language_spoken varchar 30
best_contact_method varchar 80
emergency_contact_name varchar 40
emergency_contact_primary_phone varchar 16
emergency_contact_secondary_phone varchar 16
emergency_contact_primary_comment varchar 80
emergency_contact_secondary_comment varchar 80
travel_history_notes varchar 255
need_medical_release tinyint
ground_transportation_comment varchar 255

passenger_dest

Column Type Length Description
id int
passenger_id int
lodging varchar 50
facility varchar 50
facility_city varchar 50
facility_state varchar 2
lod_phone varchar 16
lod_phone_comment varchar 40
fac_phone varchar 16
facility_phone_comment varchar 40

passenger_illness_category

  • A reference table of categories of illnesses, used for repor
Column Type Length Description
id int
category_description varchar 30
super_category_description varchar 30

passenger_type

  • A reference table of types of passengers.
Column Type Length Description
id int
name varchar 30

password_request

Column Type Length Description
id int
person_id int
email varchar 80
token varchar 32
created_at datetime

permission

Column Type Length Description
id int
code varchar 30
title varchar 30
description varchar 255

person

  • Contains basic contact information for all persons in the sy
Column Type Length Description
id int
title varchar 12
first_name varchar 40
last_name varchar 40
address1 varchar 40
address2 varchar 40
city varchar 30
county varchar 30
state varchar 2
country varchar 30
zipcode varchar 30
day_phone varchar 16
day_comment varchar 40
evening_phone varchar 16
evening_comment varchar 40
mobile_phone varchar 16
mobile_comment varchar 40
pager_phone varchar 16
pager_comment varchar 40
other_phone varchar 16
other_comment varchar 40
fax_phone1 varchar 16
fax_comment1 varchar 40
auto_fax tinyint
fax_phone2 varchar 16
fax_comment2 varchar 40
email varchar 80
email_text_only tinyint
email_blocked tinyint
username varchar 20
password varchar 50
comment varchar 255
wf_policy_agreed int
wf_policy_agreed_date date
pager_email varchar 80
block_mailings tinyint
newsletter tinyint
gender varchar 7
deceased tinyint
deceased_comment varchar 255
secondary_email varchar 80
deceased_date date
middle_name varchar 25
suffix varchar 25
nickname varchar 25
veteran tinyint
created_at datetime

person_role

  • M-M mapping of persons to roles in the system.
Column Type Length Description
person_id int
role_id int
priority varchar 60

personal_flight

  • Allows member pilots to enter flights they have planned on t
Column Type Length Description
id int
pilot_id int
name varchar 255
departure_date date
return_date date
origin_city varchar 255
origin_zipcode varchar 12
destination_city varchar 255
destination_zipcode varchar 12

personal_note

  • Stores the personal notes in the personal note tab.
Column Type Length Description
person_id int
note text 65535

pilot

  • Contains information about their pilot credentials and exper
Column Type Length Description
id int
member_id int
primary_airport_id int
secondary_home_bases varchar 30
total_hours int
license_type varchar 10
ifr tinyint
multi_engine tinyint
se_instructor varchar 5
me_instructor varchar 5
other_ratings varchar 50
insurance_received datetime
oriented_member_id int
oriented_date datetime
mop_active_status int
mop_oriented_member_id int
mop_oriented_date datetime
mop_regions_served varchar 125
mop_served_by varchar 500
mop_qualifications varchar 125
hseats varchar 30
transplant tinyint

pilot_aircraft

  • M-M mapping of pilots to aircraft to identify the pilot's ai
Column Type Length Description
id int
member_id int
aircraft_id int
n_number varchar 8
own tinyint
primary tinyint
seats tinyint
known_ice tinyint

pilot_date

Column Type Length Description
id int
member_id int
date datetime
available_seats int
pilot_request_id int

pilot_request

  • Tracks the requests pilots make for available missions. Requ
Column Type Length Description
id int
member_id int
group_camp_id int
home_base varchar 4
number_seats int
total_weight int
multiple_pick tinyint
leg_id int
date varchar 100 multible dates has
pilot_type varchar 17
comment varchar 200
accepted tinyint
processed tinyint
pilot_status tinyint 1 Reopen 2 Delete 3 Decline
on_hold tinyint
mission_assistant_wanted tinyint
miss_assis_id int
mission_assistant_name varchar 50
ifr_backup_wanted tinyint
created_at datetime
number_date_assign int
aircraft_id int
tail varchar 100

policy

  • Stores information about acceptable use policies.
Column Type Length Description
id int
description varchar 25
policy_text varchar 5000
effective_date datetime

ref_source

  • A reference table with referral sources for contacts coming
Column Type Length Description
id int
source_name varchar 60

requester

  • Database of persons who request missions.
Column Type Length Description
id int
person_id int
agency_id int
title varchar 40
how_find_af varchar 40

role

  • System roles, used to determine permissions. Deprecated in A
Column Type Length Description
id int
title varchar 30
description varchar 255

role_notification

Column Type Length Description
id int
mid varchar 255
role_id int
notification tinyint

role_permission

Column Type Length Description
role_id int
permission_id int

rp_application_search

  • VIEW
Column Type Length Description
applicationID int
externalID int
active tinyint
applicationDate datetime
personID int
firstName varchar 40
lastName varchar 40
address1 varchar 40
city varchar 30
state varchar 2
zipcode varchar 30
county varchar 30
areaCode varchar 16
dayPhone varchar 16
dayComment varchar 40
eveningPhone varchar 16
eveComment varchar 40
email varchar 80
spousePilot tinyint
wingID int
wingName varchar 30
renewalDate date
joinDate date
joinYear int
refSourceID int
refSourceName varchar 60
companyMatchFunds tinyint
companyName varchar 40
companyPosition varchar 40
vocationClassID int
vocationClassName varchar 30
memberAOPA tinyint
memberKiwanis tinyint
memberRotary tinyint
memberLions tinyint
member_99s tinyint
memberWIA tinyint
missionOrientation tinyint
missionCoordination tinyint
pilotRecruitment tinyint
fundRaising tinyint
celebrityContacts tinyint
graphicArts tinyint
hospitalOutreach tinyint
eventPlanning tinyint
mediaRelations tinyint
telephoneWork tinyint
computers tinyint
printing tinyint
writing tinyint
speakersBureau tinyint
wingTeam tinyint
webInternet tinyint
foundationContacts tinyint
aviationContacts tinyint
disasterResponseInterest tinyint

rp_audit_logs

  • VIEW
Column Type Length Description
DateTimeDisplay varchar 10
date_time timestamp
audit_identity varchar 82
event varchar 15
event_reason varchar 1000

rp_camp_count

  • VIEW
Column Type Length Description
campID int
campName varchar 40
agencyName varchar 80
airportState varchar 2
legCount bigint
cancelledCount bigint
approvedCount bigint
missionDate datetime

rp_camp_passengers

  • VIEW
Column Type Length Description
passName varchar 81
passLastName varchar 40
passFirstName varchar 40
weight int
passDayPhone varchar 16
passEvePhone varchar 16
passMobilePhone varchar 16
passPagerPhone varchar 16
passOtherPhone varchar 16
passFaxPhone varchar 16
passDayComment varchar 40
passEveComment varchar 40
passMobileComment varchar 40
passPagerComment varchar 40
passOtherComment varchar 40
passFaxComment varchar 40
reqName varchar 81
reqFirstName varchar 40
reqLastName varchar 40
reqDayPhone varchar 16
reqEvePhone varchar 16
reqMobilePhone varchar 16
reqPagerPhone varchar 16
reqOtherPhone varchar 16
reqFaxPhone varchar 16
reqDayComment varchar 40
reqEveComment varchar 40
reqMobileComment varchar 40
reqPagerComment varchar 40
reqOtherComment varchar 40
reqFaxComment varchar 40
campName varchar 40
campPhone varchar 16
pilotName varchar 81
pilotDayPhone varchar 16
pilotEvePhone varchar 16
pilotMobilePhone varchar 16
pilotOtherPhone varchar 16
pilotPagerPhone varchar 16
pilotFaxPhone varchar 16
homeBase varchar 4
pilotDayComment varchar 40
pilotEveComment varchar 40
pilotMobileComment varchar 40
pilotPagerComment varchar 40
pilotOtherComment varchar 40
pilotFaxComment varchar 40
pilotEmail varchar 80
ifr tinyint
toAirportName varchar 30
toAirportIdent varchar 30
fromAirportName varchar 30
fromAirportIdent varchar 30
missionID int
missionSelectDate datetime
missionDate datetime
medicalReleaseReceived datetime
waiverReceived datetime
private_c_note varchar 255
cancelled varchar 25
n_number varchar 8
make varchar 50
model varchar 25

rp_cancel_no_pilot

  • VIEW
Column Type Length Description
mission_date datetime
missionDateDisplay varchar 10
fromAirport varchar 4
toAirport varchar 4
passName varchar 81
reqName varchar 81
agencyName varchar 80
from_wing_id int
to_wing_id int

rp_cancelled_missions

  • VIEW
Column Type Length Description
year int
month int
cancelled varchar 25
mission_leg bigint
coordinated bigint

rp_children_passengers

  • VIEW
Column Type Length Description
mission_date datetime
displayDate varchar 10
first_name varchar 40
initial varchar 2
last_name varchar 40
passCity varchar 30
passState varchar 2
passCounty varchar 30
originIdent varchar 4
originName varchar 30
originCity varchar 30
originState varchar 2
destIdent varchar 4
destName varchar 30
destCity varchar 30
destState varchar 2
agencyName varchar 80
facility_name varchar 40
illness varchar 60
passAge bigint
wing_id int

rp_deceased_persons

  • VIEW
Column Type Length Description
deceased_date date
deceasedDate varchar 10
personName varchar 82
deceasedComment varchar 255

rp_disaster_response_status

  • VIEW
Column Type Length Description
lastName varchar 40
personName varchar 82
disasterResponse varchar 30
county varchar 30
wingID int
wingName varchar 30
flightStatus varchar 20

rp_flight_status_wing

  • VIEW
Column Type Length Description
wingName varchar 30
commandPilot bigint
nonPilot bigint
verifyOrientation bigint
groundAngel bigint
missionAssistant bigint
totalCount bigint

rp_member_application

  • VIEW
Column Type Length Description
applicationID int
applicationDate datetime
title varchar 12
firstName varchar 40
lastName varchar 40
middleName varchar 40
suffix varchar 25
nickname varchar 40
veteran tinyint
gender varchar 7
city varchar 30
state varchar 2
zipcode varchar 10
dayPhone varchar 16
pilotDayComment varchar 40
evePhone varchar 16
pilotEveComment varchar 40
faxPhone1 varchar 16
pilotFaxComment varchar 40
faxPhone2 varchar 16
pilotFaxComment2 varchar 40
mobilePhone varchar 16
pilotMobileComment varchar 40
pagerPhone varchar 16
pilotPagerComment varchar 40
otherPhone varchar 16
pilotOtherComment varchar 40
email varchar 80
pageEmail varchar 80
secondaryEmail varchar 80
spouseFirstName varchar 40
spouseLastName varchar 40
applicantPilot tinyint
spousePilot tinyint
applicantCopilot tinyint
languagesSpoken varchar 125
homeBase varchar 20
fboName varchar 80
secondaryHomeBases varchar 30
apMake varchar 50
apModel varchar 25
aircraftPrimaryOwn tinyint
aircraftPrimaryIce tinyint
aircraftPrimarySeats int
aircraftPrimaryNNumber varchar 10
asMake varchar 50
asModel varchar 25
aircraftSecondaryOwn tinyint
aircraftSecondaryIce tinyint
aircraftSecondarySeats int
aircraftSecondaryNNumber varchar 10
pilotCertificate varchar 40
ratings varchar 50
medicalClass int
licenseType varchar 10
totalHours int
ifrHours int
multiHours int
otherHours int
dateOfBirth date
height int
weight int
availabilityWeekdays tinyint
availabilityWeeknights tinyint
availabilityWeekends tinyint
availabilityNotice tinyint
availabilityLastMinute tinyint
availabilityCopilot tinyint
affirmationAgreed tinyint
insuranceAgreed tinyint
hseatsInterest tinyint
volunteerInterest varchar 255
companyPosition varchar 40
companyMatchFunds tinyint
companyBusinessCategoryID int
referralSource int
referralSourceOther varchar 40
premiumChoice int
premiumSize int
duesAmountPaid float
donationAmountPaid float
methodOfPaymentID varchar 20
ccardApprovalNumber varchar 20
processedDate datetime
missionOrientation tinyint
missionCoordination tinyint
pilotRecruitment tinyint
fundRaising tinyint
celebrityContacts tinyint
hospitalOutreach tinyint
mediaRelations tinyint
telephoneWork tinyint
computers tinyint
clerical tinyint
publicity tinyint
writing tinyint
speakersBureau tinyint
wingTeam tinyint
graphicArts tinyint
eventPlanning tinyint
webInternet tinyint
foundationContacts tinyint
aviationContacts tinyint
printing tinyint
memberAOPA tinyint
memberKiwanis tinyint
memberRotary tinyint
memberLions tinyint
member99s tinyint
memberWIA tinyint
EDNewMemberNotify date
WNewMemberNotify date
badgeMade date
notebookSent date
externalID int
novapointeID int
premiumShipDate datetime
premiumShipMethod varchar 10
premiumShipTrackingNumber varchar 35

rp_member_application_reconciliation

  • VIEW
Column Type Length Description
first_name varchar 40
last_name varchar 40
external_id int
renewal tinyint
dues_amount_paid float
method_of_payment varchar 20
check_number int
donation_amount_paid float
application_date datetime
applicationDateDisplay varchar 10
processedDate varchar 10
ccard_approval_number varchar 20
payment_transaction_id int
member_class_id int
memberClass varchar 20
master_member_id int
masterMemberExternalID int
masterMemberFirstName varchar 40
masterMemberLastName varchar 40
renewal_date date

rp_member_directory

  • VIEW
Column Type Length Description
external_id int
personID int
first_name varchar 40
last_name varchar 40
address1 varchar 40
address2 varchar 40
city varchar 30
state varchar 2
zipcode varchar 30
deceased tinyint
email_blocked tinyint
email varchar 80
secondary_email varchar 80
pager_email varchar 80
countyName varchar 30
dayAreaCode varchar 3
eveAreaCode varchar 3
mobileAreaCode varchar 3
dayPhone varchar 16
day_comment varchar 40
evePhone varchar 16
evening_comment varchar 40
mobilePhone varchar 16
mobile_comment varchar 40
pagerPhone varchar 16
pager_comment varchar 40
faxPhone1 varchar 16
fax_comment1 varchar 40
faxPhone2 varchar 16
fax_comment2 varchar 40
otherPhone varchar 16
other_comment varchar 40
license_type varchar 10
ifr varchar 3
multiEngine varchar 3
se_instructor varchar 5
hseats varchar 30
transplant tinyint
me_Instructor varchar 5
mop_regions_served varchar 125
insurance_received datetime
insuranceExpired int
memberID int
flight_status varchar 20
renewal_date date
date_of_birth date
coordinator_notes varchar 255
wingName varchar 30
id int
active tinyint
member_class_id int
homeBase varchar 4
wingJobID int
coordinatorID int
boardMemberID int
no_weekday tinyint
no_night tinyint
no_weekend tinyint
last_minute tinyint
first_date date
last_date date
availability_comment varchar 50
aircraftOwner int
fastestAircraft bigint
maxLoadAircraft bigint
missionCountThisYear bigint
missionCountTotal bigint
lastMissionFlown datetime
nextPendingMission datetime

rp_member_directory_for_members

  • VIEW
Column Type Length Description
external_id int
personID int
first_name varchar 40
last_name varchar 40
address1 varchar 40
address2 varchar 40
city varchar 30
state varchar 2
zipcode varchar 30
deceased tinyint
email_blocked tinyint
email varchar 80
secondary_email varchar 80
pager_email varchar 80
countyName varchar 30
dayAreaCode varchar 3
eveAreaCode varchar 3
mobileAreaCode varchar 3
dayPhone varchar 16
day_comment varchar 40
evePhone varchar 16
evening_comment varchar 40
mobilePhone varchar 16
mobile_comment varchar 40
pagerPhone varchar 16
pager_comment varchar 40
faxPhone1 varchar 16
fax_comment1 varchar 40
faxPhone2 varchar 16
fax_comment2 varchar 40
otherPhone varchar 16
other_comment varchar 40
license_type varchar 10
ifr varchar 3
multiEngine varchar 3
se_instructor varchar 5
hseats varchar 30
transplant tinyint
me_Instructor varchar 5
mop_regions_served varchar 125
insurance_received datetime
insuranceExpired int
memberID int
flight_status varchar 20
renewal_date date
date_of_birth date
coordinator_notes varchar 255
wingName varchar 30
id int
active tinyint
member_class_id int
homeBase varchar 4
wingJobID int
coordinatorID int
boardMemberID int
no_weekday tinyint
no_night tinyint
no_weekend tinyint
last_minute tinyint
first_date date
last_date date
availability_comment varchar 50

rp_member_photos

  • VIEW
Column Type Length Description
photoID int
submissionDate varchar 10
missionDate varchar 10
passengerName varchar 81
pilotName varchar 81
photo_quality int
photo_filename varchar 75
photoThumb varchar 235
wing_id int
passLastName varchar 40
pilotLastName varchar 40

rp_member_wing_stats_report

  • VIEW
Column Type Length Description
monthNo int
yearNo int
wingID int
wingName varchar 30
flightStatus varchar 20
memberCount int

rp_members_no_missions

  • VIEW
Column Type Length Description
firstName varchar 40
lastName varchar 40
county varchar 30
zipcode varchar 30
memberAC varchar 3
joinDate varchar 10
orientedDate varchar 10
joinDateSort date
wing_id int
email varchar 80

rp_mission_cost_tracking

  • VIEW
Column Type Length Description
mission_date datetime
missionDateDisplay varchar 10
external_id int
pilot_id int
pilotName varchar 81
id int
commercial_ticket_cost int
yearNo int
approved tinyint
passengerName varchar 81

rp_mission_itinerary

  • VIEW
Column Type Length Description
Now datetime
missionID int
externalID int
missionTypeID int
missionDate datetime
missionDateDisplay varchar 10
apptDate datetime
apptDateDisplay varchar 10
apptTime varchar 50
flightTime varchar 60
releasingPhysician varchar 40
releasingPhone varchar 16
releasingFax varchar 16
releasingFaxComment varchar 40
releasingEmail varchar 80
lodging varchar 40
lodgingPhone varchar 16
lodging_phone_comment varchar 40
facilityName varchar 40
facilityPhone varchar 16
facilityPhoneComment varchar 40
publicConsiderations varchar 255
privateConsiderations varchar 255
illness varchar 60
groundTransportationComment varchar 255
treatingPhysician varchar 40
treatingPhone varchar 16
treatingFax varchar 16
treatingFaxComment varchar 80
treatingEmail varchar 80
languageSpoken varchar 30
bestContactMethod varchar 80
emergencyContactName varchar 40
emergencyContactPrimaryPhone varchar 16
emergencyContactSecondaryPhone varchar 16
emergencyContactPrimaryComment varchar 80
emergencyContactSecondaryComment varchar 80
passFirstName varchar 40
passLastName varchar 40
passCity varchar 30
passState varchar 2
passZipcode varchar 30
passAge bigint
passWeight int
passDayPhone varchar 16
passDayComment varchar 40
passEvePhone varchar 16
passEveComment varchar 40
passPagerPhone varchar 16
passPagerComment varchar 40
passMobilePhone varchar 16
passMobileComment varchar 40
passOtherPhone varchar 16
passOtherComment varchar 40
passFaxPhone1 varchar 16
passFax1Comment varchar 40
passEmail varchar 80
passAddressOne varchar 40
passAddressTwo varchar 40
comment varchar 100
legNumber int
passOnBoard tinyint
baggageWeight int
baggageDesc varchar 255
publicCNote varchar 255
privateCNote varchar 255
copilotWanted tinyint
fromAirportIdent varchar 4
fromAirportName varchar 30
fromAirportCity varchar 30
fromAirportState varchar 2
fromAirportGMTOffset int
fromAirportDSTOffset int
toAirportIdent varchar 4
toAirportName varchar 30
toAirportCity varchar 30
toAirportState varchar 2
toAirportGMTOffset int
toAirportDSTOffset int
pilotFirstName varchar 40
pilotLastName varchar 40
pilotDayPhone varchar 16
pilotDayComment varchar 40
pilotEvePhone varchar 16
pilotEveComment varchar 40
pilotFaxPhone varchar 16
pilotFaxComment varchar 40
pilotMobilePhone varchar 16
pilotMobileComment varchar 40
pilotPagerPhone varchar 16
pilotPagerComment varchar 40
pilotOtherPhone varchar 16
pilotOtherComment varchar 40
pilotEmail varchar 80
coPilotFirstName varchar 40
coPilotLastName varchar 40
CoPilotDayPhone varchar 16
coPilotDayComment varchar 40
CoPilotEvePhone varchar 16
coPilotEveComment varchar 40
CoPilotMobilePhone varchar 16
coPilotMobileComment varchar 40
CoPilotPagerPhone varchar 16
coPilotPagerComment varchar 40
CoPilotOtherPhone varchar 16
coPilotOtherComment varchar 40
CoPilotFaxPhone varchar 16
coPilotFaxComment varchar 40
coPilotEmail varchar 80
buPilotFirstName varchar 40
buPilotLastName varchar 40
buPilotDayPhone varchar 16
buPilotDayComment varchar 40
buPilotEvePhone varchar 16
buPilotEveComment varchar 40
buPilotMobilePhone varchar 16
buPilotMobileComment varchar 40
buPilotPagerPhone varchar 16
buPilotPagerComment varchar 40
buPilotOtherPhone varchar 16
buPilotOtherComment varchar 40
buPilotFaxPhone varchar 16
buPilotFaxComment varchar 40
buPilotEmail varchar 80
coordFirstName varchar 40
coordLastName varchar 40
coordDayPhone varchar 16
coordDayComment varchar 40
coordEvePhone varchar 16
coordEveComment varchar 40
coordMobilePhone varchar 16
coordMobileComment varchar 40
coordPagerPhone varchar 16
coordPagerComment varchar 40
coordOtherPhone varchar 16
coordOtherComment varchar 40
coordFaxPhone varchar 16
coordFaxComment varchar 40
coordEmail varchar 80
requesterName varchar 81
reqFirstName varchar 40
reqLastName varchar 40
reqDayPhone varchar 16
reqEvePhone varchar 16
reqMobilePhone varchar 16
reqPagerPhone varchar 16
reqOtherPhone varchar 16
reqFaxPhone varchar 16
reqDayComment varchar 40
reqEveComment varchar 40
reqPagerComment varchar 40
reqMobileComment varchar 40
reqFaxComment varchar 40
reqOtherComment varchar 40
reqEmail varchar 80
companionName varchar 50
companionRelationship varchar 30
companionDOB date
companionAge bigint
companionWeight int
companionPhone varchar 16
companionPhoneComment varchar 40
pilotName varchar 50
afaDayPhone varchar 16
afaNightPhone varchar 16
afaFaxPhone varchar 16
afaPilotMobilePhone varchar 16
afaAircraft varchar 76
afaNNumber varchar 8
aircraftColor varchar 20
etd varchar 15
eta varchar 15
afaOrgName varchar 60
afaOrgPhone varchar 16
fboName varchar 40
fboPhone varchar 16
fboFax varchar 16
fboFuelDiscount double
fboAirportIdent varchar 4
aircraftSeats tinyint
aircraftNNumber varchar 8
aircraftKnownIce tinyint
aircraftMake varchar 50
aircraftModel varchar 25
campName varchar 40
campPhone varchar 16
campPhoneComment varchar 40
campLodgingName varchar 40
campLodgingPhone varchar 16
campLodgingPhoneComment varchar 40
campComment varchar 500
flightInformation varchar 255
commOriginIdent varchar 4
commOriginName varchar 30
commOriginCity varchar 30
commOriginState varchar 2
commDestIdent varchar 4
commDestName varchar 30
commDestCity varchar 30
commDestState varchar 2
transportation varchar 20
groundOrigin varchar 10
groundDestination varchar 10
customOriginAddress varchar 75
customDestAddress varchar 75
commFlightTime time commercial
airlineName varchar 255
commConfirmCode varchar 50 commercial
commFlightNumber varchar 50 commercial
commDeparture time commercial
commArrival time commercial

rp_mission_leg_companion_count

  • VIEW
Column Type Length Description
mission_id int
CompanionCount bigint
CompanionTotalWeight decimal

rp_mission_legs_origin_destination

  • VIEW
Column Type Length Description
missionNo varbinary 25
externalID int
missionDate datetime
legNumber int
missionDisplayDate varchar 10
origin varchar 41
destination varchar 41
wingName varchar 30
mission_date datetime
legID int
isAfaLeg int
isFlown int

rp_mission_report

  • VIEW
Column Type Length Description
id int
reportDate varchar 73
missionDate varchar 10
missionAssistant varchar 50
nnumber varchar 8
hobbsTime decimal
passengerNames varchar 255
missionComments text 65535
expenseReport text 65535
pickup varchar 25
dropoff varchar 25
routing varchar 40
commercialTicketCost int
mileage int
make varchar 50
model varchar 25
name varchar 50
externalID int
legNumber int
pilotFirstName varchar 40
pilotLastName varchar 40
pilotTitle varchar 12
pilotAddress varchar 40
pilotCity varchar 30
pilotState varchar 2
pilotZipcode varchar 30

rp_mission_type_wing_stats

  • VIEW
Column Type Length Description
monthNo int
yearNo int
missionTypeID int
wingID int
legCount int
totalHours int
aircraftCost int
commercialCost int

rp_missions_available_coordination

  • VIEW
Column Type Length Description
externalID varbinary 25
missionLegID int
memberWingID int
memberHomeBase char 0
missionDate varchar 10
mission_date datetime
flight_time varchar 60
pass_on_board tinyint
originID varchar 4
originName varchar 30
originCity varchar 30
originState varchar 2
originLat float
originLong float
originWingID int
destinationID varchar 4
destinationName varchar 30
destinationCity varchar 30
destinationState varchar 2
destinationLat float
destinationLong float
destinationWingID int
CompanionCount bigint
distance double
passCount bigint
totalMissionWeight decimal
firstName varchar 40
lastName varchar 40
email varchar 80
dayPhone varchar 16
day_comment varchar 40
evePhone varchar 16
evening_comment varchar 40
coordinatorID int
coordFirstName varchar 40
coordLastName varchar 40
coordEmail varchar 80
missionCount bigint
fastestAircraft bigint
bestRangeAircraft bigint
seatsAircraft int

rp_missions_by_member

  • VIEW
Column Type Length Description
external_id int
pilotName varchar 82
mission_date datetime
legCount bigint
hobbsTime decimal
commercialTicketCost decimal

rp_missions_origin_destination

  • VIEW
Column Type Length Description
externalID int
mission_date datetime
missionDateDisplay varchar 10
originID varchar 4
originCity varchar 30
destID varchar 4
destCity varchar 30
legCount bigint

rp_monthly_missions

  • VIEW
Column Type Length Description
missionReportID int
missionDate datetime
firstName varchar 40
lastName varchar 40
legCount bigint
hours decimal
legCost decimal
commercialTicketCost decimal

rp_new_member_badge

  • VIEW
Column Type Length Description
memberID int
externalID int
firstName varchar 40
lastName varchar 40
addressOne varchar 40
addressTwo varchar 40
city varchar 30
state varchar 2
zipcode varchar 30
joinDate varchar 10
flightStatus varchar 20
insuranceReceived varchar 10
badgeMade varchar 10
notebookSent varchar 10
joinDateSort date
wing_id int
email varchar 80
applicationID bigint

rp_new_member_status

  • VIEW
Column Type Length Description
firstName varchar 40
lastName varchar 40
joinDate varchar 10
flightStatus varchar 20
insuranceReceived varchar 10
badgeMade varchar 10
notebookSent varchar 10
joinDateSort date
wing_id int
email varchar 80

rp_outreach_report

  • VIEW
Column Type Length Description
externalID int
mission_date datetime
displayDate varchar 10
cancelled varchar 25
pilotName varchar 81
pilotLastName varchar 40
agencyName varchar 80
passName varchar 81
passLastName varchar 40
passAge int
illness varchar 60
facilityName varchar 40
lodgingName varchar 40
agencyCity varchar 30
agencyState varchar 2
passState varchar 2
id int
passengerID int

rp_weekend_summary

  • VIEW
Column Type Length Description
passName varchar 81
passLastName varchar 40
weight int
passDayPhone varchar 16
passDayPhoneSearch varchar 16
passEvePhone varchar 16
passMobilePhone varchar 16
passPagerPhone varchar 16
passOtherPhone varchar 16
passFaxPhone varchar 16
passDayComment varchar 40
passEveComment varchar 40
passMobileComment varchar 40
passPagerComment varchar 40
passOtherComment varchar 40
passFaxComment varchar 40
passAC varchar 3
passCity varchar 30
reqName varchar 81
reqLastName varchar 40
reqDayPhone varchar 16
reqEvePhone varchar 16
reqMobilePhone varchar 16
reqPagerPhone varchar 16
reqOtherPhone varchar 16
reqFaxPhone varchar 16
reqDayComment varchar 40
reqEveComment varchar 40
reqMobileComment varchar 40
reqPagerComment varchar 40
reqOtherComment varchar 40
reqFaxComment varchar 40
pilotName varchar 81
pilotLastName varchar 40
pilotDayPhone varchar 16
pilotEvePhone varchar 16
pilotMobilePhone varchar 16
pilotPagerPhone varchar 16
pilotOtherPhone varchar 16
pilotFaxPhone varchar 16
homeBase varchar 4
pilotDayComment varchar 40
pilotEveComment varchar 40
pilotMobileComment varchar 40
pilotPagerComment varchar 40
pilotOtherComment varchar 40
pilotFaxComment varchar 40
toAirportName varchar 30
toAirportIdent varchar 4
fromAirportName varchar 30
fromAirportIdent varchar 4
mission_date datetime
missionTypeID int
missionDisplayDate varchar 10
missionType varchar 40
missionID int
wing_id int
cancelled varchar 25
countyName varchar 30

team_note

  • Table of the broadcast notes in the team note pad.
Column Type Length Description
role_id int
note text 65535

user_filter

  • Stores the filter settings for the missions available list for the user's preferences
Column Type Length Description
id int
person_id int
date_range1 datetime
date_range2 datetime
day1 varchar 20
day2 varchar 20
day3 varchar 20
day4 varchar 20
day5 varchar 20
day6 varchar 20
day7 varchar 20
wing varchar 50
ident varchar 4
city varchar 50
zipcode varchar 10
state varchar 10
orgin tinyint
dest tinyint
is_pilot tinyint
is_ma tinyint
ifr_backup tinyint
filled tinyint
open tinyint
max_passenger int
max_weight int
max_distance int
max_effciency int
availability tinyint
alltype tinyint

user_filter_mission_types

Column Type Length Description
user_filter_id int
mission_type_id int

vocation_class

  • A reference table of job/business types for members.
Column Type Length Description
id int
name varchar 30

vpo_request

  • Used for mission requests submitted by other organizations.
Column Type Length Description
id int
hazardous_cargo_flag tinyint
request_date date
request_reason_desc varchar 125
agency_name varchar 80
req_first_name varchar 40
req_last_name varchar 40
agency_division varchar 40
req_address1 varchar 40
req_address2 varchar 40
req_city varchar 30
req_state varchar 2
req_country varchar 30
req_zipcode varchar 10
req_office_phone varchar 16
req_office_comment varchar 40
req_mobile_phone varchar 16
req_mobile_comment varchar 40
req_fax_phone varchar 16
req_fax_comment varchar 40
req_pager_phone varchar 16
req_pager_comment varchar 40
req_other_phone1 varchar 16
req_other_comment1 varchar 40
req_other_phone2 varchar 16
req_other_comment2 varchar 40
req_other_phone3 int
req_other_comment3 varchar 40
req_email varchar 80
req_alt_email varchar 80
contact_notes varchar 125
origin_city varchar 30
origin_state varchar 2
dest_city varchar 30
dest_state varchar 2
preferred_date date
alternative_date date
transport_type varchar 25
request_posted_date date
request_posted_to_afa_org_id int
reques_processed_date date
request_disposition varchar 25
caller_name varchar 40
caller_phone varchar 16
request_post_results varchar 500
requester_vpo_user_id varchar 25
source varchar 10
soap_post_from_afa_org_id varchar 5
soap_post_request_id int
request_processed_by int

vpo_request_animal

  • Subtable of a vpo_request. 1-M mapping.
Column Type Length Description
id int
request_id int
animal_name varchar 40
animal_type varchar 40
weight int
harness varchar 20
kennel_height int
kennel_width int
kennel_length int
handler_traveling tinyint

vpo_request_cargo

  • Subtable of a vpo_request. 1-M mapping.
Column Type Length Description
id int
request_id int
hazardous tinyint
cargo_type varchar 40
weight int
height int
length int
width int
special_handling varchar 125
oc_first_name varchar 40
oc_last_name varchar 40
oc_day_phone varchar 16
oc_day_comment varchar 40
oc_mobile_phone varchar 16
oc_mobile_comment varchar 40
oc_other_phone1 varchar 16
oc_other_comment1 varchar 40
oc_other_phone2 varchar 16
oc_other_comment2 varchar 40
oc_email varchar 80
oc_alt_email varchar 80
dc_first_name varchar 40
dc_last_name varchar 40
dc_day_phone varchar 16
dc_day_comment varchar 40
dc_mobile_hone int
dc_mobile_comment varchar 40
dc_other_phone1 varchar 16
dc_other_comment1 varchar 40
dc_other_phone2 varchar 16
dc_other_comment2 varchar 40
dc_email varchar 80
dc_alt_email varchar 80
item_description varchar 255

vpo_request_passenger

  • Subtable of a vpo_request. 1-M mapping.
Column Type Length Description
id int
request_id int
fema_no varchar 25
first_name varchar 40
last_lame varchar 40
address1 varchar 40
address2 varchar 40
city varchar 30
state varchar 2
country varchar 30
zipcode varchar 10
day_phone varchar 16
day_comment varchar 40
eve_phone tinyint
eve_comment varchar 40
mobile_phone varchar 16
mobile_comment varchar 40
fax_phone varchar 16
fax_comment varchar 40
pager_phone varchar 16
pager_comment varchar 40
other_phone1