-- createdbandtables-sqlite.sql -- sqlite script to create the database and tables (for future data import) -- n6lhv@arrl.net (Wayne Smith) -- created: 9-August-2004 -- updated: 10-August-2004 -- this script is based on pa_ddef18.txt on the FCC ULS Database page -- A few (non material) changes must be made to pa_ddef17.txt -- for use as a DDL MySql script. Those changes are as follows: -- remove the "go" words between each CREATE TABLE command -- (I believe that is for T-SQL environments, such as MS SQL Server) -- add a semi-colon (";") after closing CREATE TABLE command -- fix places where the the comma (",") is misplaced in the -- CREATE TABLE command -- change the keyword "smallinteger" to "smallint" -- change the keyword "tinyinteger" to "tinyint" -- change the keyword "money" to (for example) "numeric(20,4)" -- The default physical file format, MyISAM, is fine -- Manage the database --DROP DATABASE IF EXISTS uls; --CREATE DATABASE uls; --USE uls; -- Create all of the tables -- A2 - Application (Additional Data) CREATE TABLE ULS.PUBACC_A2 ( Record_Type char(2), unique_system_identifier numeric(9,0), ULS_File_Number char(14), EBF_Number varchar(30), spectrum_manager_leasing char(1), defacto_transfer_leasing char(1), new_spectrum_leasing char(1), spectrum_subleasing char(1), xfer_control_lessee char(1), revision_spectrum_lease char(1), assignment_spectrum_lease char(1), registered_link_action char(1) ); -- AC - Aircraft CREATE TABLE ULS.PUBACC_AC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), aircraft_count integer, type_of_carrier char(1), portable_indicator char(1), fleet_indicator char(1), n_number char(10) ); -- AD - Application/Detail CREATE TABLE ULS.PUBACC_AD ( Record_Type char(2), unique_system_identifier numeric(9,0), ULS_File_Number char(14), EBF_Number varchar(30), Application_Purpose char(2), Application_Status char(1), Application_Fee_Exempt char(1), Regulatory_Fee_Exempt char(1), Source char(1), requested_expiration_date_mmdd char(4), Receipt_Date char(10), Notification_Code char(1), Notification_date char(10), Expanding_Area_or_Contour char(1), Change_Type char(1), Original_Application_Purpose char(2), Requesting_A_Waiver char(1), How_Many_Waivers_Requested integer, Any_Attachments char(1), Number_of_Requested_SIDs integer, fee_control_num char(16), date_entered char(10), reason varchar(255), frequency_coordination_indicat char(1), emergency_sta char(1), overall_change_type char(1), slow_growth_ind char(1), previous_waiver char(1) ); -- AM - Amateur CREATE TABLE ULS.PUBACC_AM ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_num char(14), ebf_number varchar(30), callsign char(10), operator_class char(1), group_code char(1), region_code tinyint, trustee_callsign char(10), trustee_indicator char(1), physician_certification char(1), ve_signature char(1), systematic_callsign_change char(1), vanity_callsign_change char(1), vanity_relationship char(12), previous_callsign char(10), previous_operator_class char(1), trustee_name varchar(50) ); -- AN - Antenna CREATE TABLE ULS.PUBACC_AN ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), antenna_action_performed char(1), antenna_number integer, location_number integer, receive_zone_code char(6), antenna_type_code char(1), height_to_tip numeric(5,1), height_to_center_raat numeric(5,1), antenna_make varchar(25), antenna_model varchar(25), tilt numeric(3,1), polarization_code char(5), beamwidth numeric(4,1), gain numeric(4,1), azimuth numeric(4,1), height_above_avg_terrain numeric(5,1), diversity_height numeric(5,1), diversity_gain numeric(4,1), diversity_beam numeric(4,1), reflector_height numeric(5,1), reflector_width numeric(4,1), reflector_separation numeric(5,1), repeater_seq_num integer, back_to_back_tx_dish_gain numeric(4,1), back_to_back_rx_dish_gain numeric(4,1), location_name varchar(20), passive_repeater_id integer, alternative_cgsa_method char(1), path_number integer, line_loss numeric(3,1) ); -- AS - Associated Callsign CREATE TABLE ULS.PUBACC_AS ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), callsign char(10), assoc_callsign char(10) ); -- AT - Attachments CREATE TABLE ULS.PUBACC_AT ( Record_Type char(2), unique_system_identifier numeric(9,0), ULS_File_Number char(14), EBF_Number varchar(30), Attachment_Code char(1), Attachment_Description varchar(60), Attachment_Date char(10), attachment_file_name varchar(60), attachment_action_performed char(1) ); -- BC - Broadcast Callsign CREATE TABLE ULS.PUBACC_BC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), callsign char(10), broadcast_callsign char(10), broadcast_city char(20), broadcast_state char(2), parent_facility_id integer, parent_class_code char(2), nonparent_type_code char(1) ); -- BF - Buildout Frequency CREATE TABLE ULS.PUBACC_BF ( record_type char(2), unique_system_identifier numeric(9,0), call_sign char(10), location_number integer, antenna_number integer, frequency_assigned numeric(16,8), buildout_code integer, buildout_deadline char(10), buildout_date char(10) ); -- BL - Buildout Location CREATE TABLE ULS.PUBACC_BL ( record_type char(2), unique_system_identifier numeric(9,0), call_sign char(10), location integer, buildout_code integer, buildout_deadline char(10), buildout_date char(10) ); -- BO - Buildout CREATE TABLE ULS.PUBACC_BO ( record_type char(2), unique_system_identifier numeric(9,0), call_sign char(10), buildout_code integer, buildout_deadline char(10), buildout_date char(10) ); -- CF - Callsign/Filenumber CREATE TABLE ULS.PUBACC_CF ( Record_Type char(2), unique_system_identifier numeric(9,0), ULS_File_Number char(14), EBF_Number varchar(30), Item_Type_Indicator char(1), Item_Type char(14), Constructed char(1), Location_Number integer, Path_Number integer, Frequency_Assigned numeric(16,8), Frequency_Upper_Band numeric(16,8), number_of_mobiles integer, action_performed char(1) ); -- CG - Coast and Ground CREATE TABLE ULS.PUBACC_CG ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), station_available char(1), public_correspondence char(1), station_identifier char(12), aeronautical_enroute_call_sign char(10), faa_office_notified varchar(255), date_faa_notified char(10), seeking_authorization char(1), regularly_engaged char(1), engaged char(1), public_mooring char(1), servicing char(1), fixed_station char(1), maritime_support char(1), aeronautical_fixed char(1), unicom char(1), search_and_rescue char(1), flight_test_uhf char(1), flight_test_manufacturer char(1), flight_test_parent_corporation char(1), flight_test_educational char(1), flight_school_certitication char(1), lighter_than_air char(1), ballooning char(1), located_at_airport char(1), radiodetermination_not_faa char(1), radiodetermination_equipment char(1), radiodetermination_public char(1), radiodetermination_elts char(1), civil_air_patrol char(1), aeronautical_enroute char(1), mobile_routine char(1), mobile_owner_operator char(1), mobile_agreement char(1), coast_ground_identifier char(12), selective_call_sign_identifier char(4), station_class char(4) ); -- CO - Comments CREATE TABLE ULS.PUBACC_CO ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_num char(14), callsign char(10), date char(10), description varchar(255) ); -- CP - Control Point CREATE TABLE ULS.PUBACC_CP ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), control_point_action_performed char(1), control_point_number integer, control_address varchar(80), control_city char(20), state_code char(2), control_phone char(10), control_county varchar(60) ); -- CS - COSER CREATE TABLE ULS.PUBACC_CS ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), call_sign char(10), location_number integer, antenna_number integer, frequency_assigned numeric(16,8), coser_result char(5) ); -- EM - Emission CREATE TABLE ULS.PUBACC_EM ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), location_number integer, antenna_number integer, frequency_assigned numeric(16,8), emission_action_performed char(1), emission_code char(10), digital_mod_rate numeric(8,1), digital_mod_type char(7), frequency_number integer ); -- EN - Entity CREATE TABLE ULS.PUBACC_EN ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), entity_type char(2), licensee_id char(9), entity_name varchar(200), first_name varchar(20), mi char(1), last_name varchar(20), suffix char(3), phone char(10), fax char(10), email varchar(50), street_address varchar(60), city varchar(20), state char(2), zip_code char(9), po_box varchar(20), attention_line varchar(35), sgin char(3), frn char(10) ); -- F2 - Additional Frequency Information CREATE TABLE ULS.PUBACC_F2 ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), location_number integer, antenna_number integer, frequency_number integer, frequency_assigned numeric(16,8), frequency_upper_band numeric(16,8), offset char(3), frequency_channel_block char(4), equipment_class char(2), minimum_power_output numeric(15,3) ); -- FA - FRC Administration CREATE TABLE ULS.PUBACC_FA ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), operator_class char(2), ship_radar_endorsement char(1), six_month_endorsement char(1), date_of_birth char(10), certification_not_restricted char(1), cert_restricted_permit char(1), restricted_permit_limited_use char(1), cole_manager_code char(5), dm_call_sign char(10), proof_of_passing char(1) ); -- FC - Frequency Coordination CREATE TABLE ULS.PUBACC_FC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), coordination_number char(25), coordinator_name varchar(40), coordinator_phone char(10), freq_coordination_date char(10), action_performed char(1) ); -- FF - Frequency Free Form (Special Condition) CREATE TABLE ULS.PUBACC_FF ( record_type char(2), unique_system_identifier numeric(9,0), callsign char(10), location_number integer, antenna_number integer, frequency numeric(16,8), frequency_number integer, freq_freeform_cond_type char(1), unique_freq_freeform_id numeric(9,0), sequence_number integer, freq_freeform_condition varchar(255) ); -- FR - Frequency CREATE TABLE ULS.PUBACC_FR ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), frequency_action_performed char(1), location_number integer, antenna_number integer, class_station_code char(4), op_altitude_code char(2), frequency_assigned numeric(16,8), frequency_upper_band numeric(16,8), frequency_carrier numeric(16,8), time_begin_operations integer, time_end_operations integer, power_output numeric(15,3), power_erp numeric(15,3), tolerance numeric(6,5), frequency_ind char(1), status char(1), eirp numeric(7,1), transmitter_make varchar(25), transmitter_model varchar(25), auto_transmitter_power_control char(1), cnt_mobile_units integer, cnt_mob_pagers integer, freq_seq_id integer ); -- FS - Frequency (Special Condition) CREATE TABLE ULS.PUBACC_FS ( record_type char(2), unique_system_identifier numeric(9,0), call_sign char(10), location_number integer, antenna_number integer, frequency numeric(16,8), frequency_number integer, special_condition_type char(1), special_condition_code integer ); -- FT - Frequency Type CREATE TABLE ULS.PUBACC_FT ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), freq_type_action_performed char(1), location_number integer, antenna_number integer, frequency_assigned numeric(16,8), frequency_type_number integer, frequency_type_code char(2) ); -- HD - Application/Header CREATE TABLE ULS.PUBACC_HD ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), license_status char(1), radio_service_code char(2), grant_date char(10), expired_date char(10), cancellation_date char(10), eligibility_rule_num char(10), applicant_type_code char(1), alien char(1), alien_government char(1), alien_corporation char(1), alien_officer char(1), alien_control char(1), revoked char(1), convicted char(1), adjudged char(1), involved char(1), common_carrier char(1), non_common_carrier char(1), private_comm char(1), fixed char(1), mobile char(1), radiolocation char(1), satellite char(1), developmental_or_sta char(1), interconnected_service char(1), certifier_first_name varchar(20), certifier_mi char(1), certifier_last_name varchar(20), certifier_suffix char(3), certifier_title char(40), gender char(1), african_american char(1), native_american char(1), hawaiian char(1), asian char(1), white char(1), ethnicity char(1), effective_date char(10), last_action_date char(10), auction_id integer, reg_stat_broad_serv char(1), band_manager char(1), type_serv_broad_serv char(1) ); -- HS - History CREATE TABLE ULS.PUBACC_HS ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), callsign char(10), date char(10), code char(6) ); -- IA - International Address CREATE TABLE ULS.PUBACC_IA ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), international_address_1 varchar(60), international_address_2 varchar(60), international_city varchar(30), country varchar(20), international_zip_code varchar(20), international_phone char(20), international_fax char(20) ); -- IR - IRAC CREATE TABLE ULS.PUBACC_IR ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), call_sign char(10), location_number integer, antenna_number integer, frequency_assigned numeric(16,8), irac_result char(2) ); -- L2 - License (Additional Data) CREATE TABLE ULS.PUBACC_L2 ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), location_action_performed char(1), location_number integer, registration_required char(1), protection_date datetime ); -- LA - License Attachment CREATE TABLE ULS.PUBACC_LA ( record_type char(2), unique_system_identifier numeric(9,0), callsign char(10), attachment_code char(1), attachment_desc varchar(60), attachment_date char(10), attachment_filename varchar(60), action_performed char(1) ); -- LF - Location Free Form (Special Condition) CREATE TABLE ULS.PUBACC_LF ( record_type char(2), unique_system_identifier numeric(9,0), callsign char(10), location_number integer, loc_freeform_cond_type char(1), unique_loc_freeform_id numeric(9,0), sequence_number integer, loc_freeform_condition varchar(255) ); -- LM - License Mobile Administration CREATE TABLE ULS.PUBACC_LM ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), callsign char(10), ext_implement_appr char(1), lm_eligibility_activity varchar(255) ); -- LO - Location CREATE TABLE ULS.PUBACC_LO ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), location_action_performed char(1), location_type_code char(1), location_class_code char(1), location_number integer, site_status char(1), corresponding_fixed_location integer, location_address varchar(80), location_city char(20), location_county varchar(60), location_state char(2), radius_of_operation numeric(5,1), area_of_operation_code char(1), clearance_indicator char(1), ground_elevation numeric(7,1), lat_degrees integer, lat_minutes integer, lat_seconds numeric(3,1), lat_direction char(1), long_degrees integer, long_minutes integer, long_seconds numeric(3,1), long_direction char(1), max_lat_degrees integer, max_lat_minutes integer, max_lat_seconds numeric(3,1), max_lat_direction char(1), max_long_degrees integer, max_long_minutes integer, max_long_seconds numeric(3,1), max_long_direction char(1), nepa char(1), quiet_zone_notification_date char(10), tower_registration_number char(10), height_of_support_structure numeric(7,1), overall_height_of_structure numeric(7,1), structure_type char(6), airport_id char(4), location_name char(20), units_hand_held integer, units_mobile integer, units_temp_fixed integer, units_aircraft integer, units_itinerant integer ); -- LS - Location (Special Condition) CREATE TABLE ULS.PUBACC_LS ( record_type char(2), unique_system_identifier numeric(9,0), call_sign char(10), location_number integer, special_condition_type char(1), special_condition_code integer ); -- MC - Market Coordinate CREATE TABLE ULS.PUBACC_MC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), undefined_partitioned_area integer, partition_sequence_number integer, partition_lat_degrees integer, partition_lat_minutes integer, partition_lat_seconds numeric(3,1), partition_lat_direction char(1), partition_long_degrees integer, partition_long_minutes integer, partition_long_seconds numeric(3,1), partition_long_direction char(1) ); -- MF - Market Frequency CREATE TABLE ULS.PUBACC_MF ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), defined_partition_area char(6), lower_frequency numeric(16,8), upper_frequency numeric(16,8) ); -- MI - MDS/ITFS Administration CREATE TABLE ULS.PUBACC_MI ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), facility_type_code char(4), statement_of_intention char(1), license_type_code char(1) ); -- MK - Market CREATE TABLE ULS.PUBACC_MK ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), market_code char(6), channel_block char(4), submarket_code integer, market_name char(30), coverage_partitioning char(1), coverage_dissagregation char(1), cellular_phase_id smallint, population numeric(9,0), tribal_credit_indicator char(1), tribal_credit_calculation numeric(20,4), additional_credit_requested numeric(20,4), tribal_credit_awarded numeric(20,4), additiona_credit_awarded numeric(20,4) ); -- MP - Market Partition CREATE TABLE ULS.PUBACC_MP ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), market_partition_code char(6), defined_partition_area varchar(60), defined_area_population numeric(9,0), include_exclude_ind char(1), undefined_partitioned_area integer ); -- MW - Microwave CREATE TABLE ULS.PUBACC_MW ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), pack_indicator char(1), pack_registration_num integer, pack_name varchar(50), type_of_operation varchar(45), smsa_code char(6), station_class char(4), cum_effect_is_major char(1) ); -- OP - Area of Operation Text CREATE TABLE ULS.PUBACC_OP ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), callsign char(10), location_number integer, area_text_sequence_num integer, area_of_operation varchar(255) ); -- PA - Microwave Path CREATE TABLE ULS.PUBACC_PA ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), callsign char(10), path_action_performed char(1), path_number integer, transmit_location_number integer, transmit_antenna_number integer, receiver_location_number integer, receiver_antenna_number integer, mas_dems_subtype char(2), path_type_desc char(20), passive_receiver_indicator char(1), country_code char(3), interference_to_gso char(1), receiver_callsign varchar(10) ); -- PC - Points of Communication CREATE TABLE ULS.PUBACC_PC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), action_performed char(1), location_number integer, antenna_number integer, frequency numeric(16,8), subscriber_call_sign char(10), city varchar(20), state char(2), lat_degrees integer, lat_minutes integer, lat_seconds numeric(3,1), lat_direction char(1), long_degrees integer, long_minutes integer, long_seconds numeric(3,1), long_direction char(1), point_of_com_frequency numeric(16,8) ); -- RA - Radial CREATE TABLE ULS.PUBACC_RA ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), radial_action_performed char(1), location_number integer, antenna_number integer, frequency numeric(16,8), radial_direction numeric(3,0), radial_haat numeric(5,1), radial_erp numeric(7,3), dist_to_sab numeric(4,1), dist_to_cgsa numeric(4,1) ); -- RC - Receiver CREATE TABLE ULS.PUBACC_RC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), radial_action_performed char(1), location_number integer, antenna_number integer, receiver_make varchar(25), receiver_model varchar(25), receiver_stability numeric(6,5), receiver_noise_figure numeric(5,2) ); -- RE - Reason CREATE TABLE ULS.PUBACC_RE ( Record_Type char(2), unique_system_identifier numeric(9,0), ULS_File_Number char(14), EBF_Number varchar(30), Reason varchar(255) ); -- RZ - Receive Zone CREATE TABLE ULS.PUBACC_RZ ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), antenna_action_performed char(1), location_number integer, antenna_number integer, receive_zone_number integer, receive_zone char(6) ); -- SC - Special Condition CREATE TABLE ULS.PUBACC_SC ( record_type char(2), unique_system_identifier numeric(9,0), call_sign char(10), special_condition_type char(1), special_condition_code integer ); -- SE - Ship Exemption CREATE TABLE ULS.PUBACC_SE ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), ship_call_sign char(10), port_registry varchar(35), owner char(1), operater char(1), charter char(1), agent char(1), radiotelephone_exempt_req char(1), gmdss_exemp_req char(1), radio_dir_exempt_req char(1), prev_exempt_file_number varchar(10), foreign_port char(1), vessel_size_exempt char(1), equipment_exempt char(1), ltd_routes_exempt char(1), cond_voyages_exempt char(1), other_exempt char(1), other_exempt_desc varchar(50), ship_type char(1), number_of_crew integer, number_passengers integer, number_others integer, count_vhf integer, count_vhf_dsc char(1), count_epirb integer, count_survival integer, count_earth_station integer, count_auto_alarm integer, count_single_side_band integer, single_side_band_type_mf char(1), single_side_band_type_hf char(1), single_side_band_type_dsc char(1), count_of_navtex integer, count_of_9_ghz_radar integer, count_of_500_khz_distress integer, count_of_reserve_power integer, count_of_other integer, description_of_other varchar(50) ); -- SF - License Free Form (Special Condition) CREATE TABLE ULS.PUBACC_SF ( record_type char(2), unique_system_identifier numeric(9,0), callsign char(10), lic_freeform_cond_type char(1), unique_lic_freeform_id numeric(9,0), sequence_number integer, lic_freeform_condition varchar(255) ); -- SG - Microwave Segments CREATE TABLE ULS.PUBACC_SG ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), segment_action_performed char(1), path_number integer, transmit_location integer, transmit_antenna integer, receiver_location integer, receiver_antenna integer, segment_number integer, segment_length numeric(12,6) ); -- SH - Ship CREATE TABLE ULS.PUBACC_SH ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), callsign char(10), type_of_authorization char(1), count_in_fleet integer, general_class char(3), special_class char(3), ship_name varchar(35), ship_number char(12), international_voyages char(1), foreign_communications char(1), radiotelegraph char(1), mmsi_request char(1), gross_tonnage integer, ship_length integer, working_freq_s1 char(3), working_freq_s2 char(3), self_id_number char(5), comsat_id_number char(7), station_number numeric(9,0), required_cat_a char(1), required_cat_b char(1), required_cat_c char(1), required_cat_d char(1), required_cat_e char(1) ); -- SI - SIDS CREATE TABLE ULS.PUBACC_SI ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), sid char(4), action_performed char(1) ); -- SV - Ship Voyage CREATE TABLE ULS.PUBACC_SV ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), voyage_number integer, voyage_description varchar(255) ); -- TA - Transfer/Assign CREATE TABLE ULS.PUBACC_TA ( Record_Type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), pro_forma char(1), full_assignment char(1), method_of_accomplishment char(1), method_other_description varchar(30), voluntary_involuntary char(1), assignor_certifier_first_name varchar(20), assignor_certifier_mi varchar(1), assignor_certifier_last_name char(20), assignor_certifier_suffix varchar(3), assignor_certifier_title varchar(40), gross_revenue_year_1 numeric(20,4), gross_revenue_year_2 numeric(20,4), gross_revenue_year_3 numeric(20,4), total_assets numeric(20,4), same_small_category char(1), applying_for_installments char(1), notification_of_forebearance char(1), wireless_need_approval char(1), non_wireless_need_approval char(1), male_or_female char(1), african_american char(1), native_american char(1), native_pacific_islander char(1), asian char(1), white char(1), ethnicity char(1), consent_date char(10), consummation_date char(10), consummation_deadline char(10), eligibility_category varchar(30) ); -- TL - Tribal Land CREATE TABLE ULS.PUBACC_TL ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), market_code char(6), channel_block char(4), action_performed char(1), tribal_land_name varchar(80), tribal_certification char(1), tribal_land_type varchar(10), square_kilometers numeric(8,0) ); -- UA - Cellular Unserved Area CREATE TABLE ULS.PUBACC_UA ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), call_sign char(10), action_performed char(1), cellular_phase integer, market_code char(6), submarket_code integer, channel_block char(4) ); -- VC - Vanity Callsign CREATE TABLE ULS.PUBACC_VC ( record_type char(2), unique_system_identifier numeric(9,0), uls_file_number char(14), ebf_number varchar(30), request_sequence integer, callsign_requested char(10) );