workspace/code/fms/sql/fms_business_otherdata.sql

/* ============================================================================
   其他数据（base_otherdata）：62 张字典表 + 查询视图

   来源：旧库 b_other_bmfl 登记的 62 张数据表（清单见 fms-api/tools/migration/otherdata-modules.txt）
   设计见《FMS业务表设计.md》「其他数据」节，要点：
     1. 主键 b_id 为雪花（bigint），旧编号（001/002 等流水号）不保留；
     2. 各表统一含 b_i18n / b_canuse / b_xh / b_bz 与审计四字段；
     3. 视图 v_<表名> 供 s_module.b_view_table 查询，写库走 s_module.b_save_table = 表名；
     4. b_from / b_grade 的重建与引用转换由迁移工具处理（不在本脚本）——
        它们已按编码主键建好，需随本次迁移重建为雪花，并转换 b_othercompany 的引用值。

   执行：cd fms-api
        java -Dstdout.encoding=UTF-8 -cp "tools/migration/mssql-jdbc-13.4.0.jre11.jar" \
             tools/migration/RunSqlFile.java ../sql/fms_business_otherdata.sql
   ============================================================================ */

/* ----------------------------------------------------------------------------
   ① 标准结构（52 张）：b_id / b_name / b_i18n / b_canuse / b_xh / b_bz / 审计
   ---------------------------------------------------------------------------- */

declare @std table (t sysname primary key);
insert into @std (t) values
    -- 1.常用
    ('b_goodssource'), ('b_dailyfeetype'), ('b_dailytype'), ('b_ywtype'), ('b_ysfs'),
    -- 2.系统
    ('b_cargo_damage_status'), ('b_bulkforkliftpersonname'), ('b_bulktype'), ('b_bulkpersonname'),
    ('b_bulkstevedorepersonname'), ('b_charging_method'), ('b_unloading_method'), ('b_sys_b_lessfull'),
    ('b_sys_edittype'), ('b_freightpayment'), ('b_jsfs'), ('b_ivtype'), ('b_sys_sex'), ('b_sys_copies'),
    ('b_cztype'), ('b_sys_level'), ('b_sys_operator'), ('b_blstate'), ('b_sys_state'), ('b_sys_feesf'),
    ('bc_priceList_type'), ('b_sys_yesno'), ('b_ystype'), ('b_billtype'), ('b_bgtype'), ('b_httype'),
    ('b_sys_hdtype'), ('b_progress'), ('b_zxstate'),
    -- 3.仓库
    ('b_depot'), ('b_depotouttype'), ('b_depotintype'),
    -- 5.物流
    ('car_daily_feetype'), ('car_jiayou_jsfs'), ('car_jiayou_type'), ('car_jiayou_youliao'),
    ('b_cdtype'), ('b_shfs'), ('b_thfs'), ('b_wangdian'), ('b_ywfrom'), ('b_ywtype_tc'),
    -- 6.铁运
    ('b_servicetype'), ('b_contype'), ('b_ywtype_ht'),
    -- 7.空运
    ('b_dcterm'), ('b_ywtype_ky');

declare @t sysname, @sql nvarchar(max);
declare std_cursor cursor local fast_forward for select t from @std order by t;
open std_cursor;
fetch next from std_cursor into @t;
while @@fetch_status = 0
begin
    set @sql = 'drop table if exists dbo.' + @t + ';';
    exec sp_executesql @sql;
    set @sql = 'create table dbo.' + @t + ' ('
        + 'b_id bigint not null primary key, b_name nvarchar(50) null, b_i18n varchar(150) null, '
        + 'b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null, '
        + 'b_created_by varchar(50) null, b_created_at datetime2 null, '
        + 'b_updated_by varchar(50) null, b_updated_at datetime2 null);';
    exec sp_executesql @sql;
    print 'created dbo.' + @t;
    fetch next from std_cursor into @t;
end
close std_cursor;
deallocate std_cursor;
GO

/* ----------------------------------------------------------------------------
   ② 差异结构（10 张）：标准字段之外还有业务字段，按《FMS业务表设计.md》逐张写出
   ---------------------------------------------------------------------------- */

drop table if exists dbo.b_feetype;
GO
create table dbo.b_feetype (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_name_e nvarchar(50) null, b_default tinyint not null default 0,
    b_amount decimal(18,2) null, b_tax decimal(18,4) null, b_money_id varchar(50) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_oceanline;
GO
create table dbo.b_oceanline (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_edicode varchar(50) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_port;
GO
create table dbo.b_port (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_name_e nvarchar(50) null, b_country nvarchar(50) null,
    b_oceanline varchar(50) null, b_edicode varchar(50) null, b_tz tinyint not null default 0,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_money;
GO
create table dbo.b_money (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_edicode varchar(50) null, b_hl decimal(18,4) null,
    b_symbol nvarchar(50) null, b_isconvert tinyint not null default 0,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_vessel;
GO
create table dbo.b_vessel (
    b_id bigint not null primary key, b_name nvarchar(100) null, b_edicode varchar(50) null, b_voy nvarchar(100) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_con_type;
GO
create table dbo.b_con_type (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_edicode varchar(50) null, b_type nvarchar(50) null,
    b_linkcon nvarchar(50) null, b_default tinyint not null default 0,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_city;
GO
create table dbo.b_city (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_name_e nvarchar(50) null,
    b_country_name_c nvarchar(50) null, b_country_name_e nvarchar(50) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_sys_pricelisttype;
GO
create table dbo.b_sys_pricelisttype (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_dw_id varchar(50) null, b_dw_name varchar(50) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_transway;
GO
create table dbo.b_transway (
    b_id bigint not null primary key, b_name nvarchar(100) null, b_edicode varchar(50) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

drop table if exists dbo.b_package;
GO
create table dbo.b_package (
    b_id bigint not null primary key, b_name nvarchar(50) null, b_edicode varchar(50) null,
    b_i18n varchar(150) null, b_canuse tinyint not null default 1, b_xh int not null default 0, b_bz nvarchar(200) null,
    b_created_by varchar(50) null, b_created_at datetime2 null, b_updated_by varchar(50) null, b_updated_at datetime2 null);
GO

/* ----------------------------------------------------------------------------
   ③ 说明：字典模块直接查表（s_module.b_view_table = 表名），不建 v_ 直通视图。
      早期版本曾按 v_<表名> 生成过一批直通视图，已随模块编码改为表名一并删除。
   ---------------------------------------------------------------------------- */