tb_bom_tables.sql

工厂采购系统,DELPHI+SQL SERVER,三层技术

if object_id('dbo.GL_BOM_Catg') is not null
drop table dbo.GL_BoM_Catg
go

Create table  GL_BoM_Catg
(
 BoM_Catg_CD CHAR(1) constraint pk_glk_bom_catg primary key,
 BoM_Category varchar(50) not null
)
go

if object_id('dbo.GL_BoM_Hdr') is not null 
drop table dbo.GL_BoM_Hdr
go

Create Table GL_BoM_Hdr
(
  BoM_Number      int not null constraint pk_GL_BOM_HDR primary key,
  BoM_Catg_CD     CHAR(1) NOT NULL constraint fk_category references GL_BoM_Catg(BoM_Catg_CD),
  Alt_BoM_Number  int  NOT NULL,
  Int_Count       CHAR(8) NOT NULL,
  BoM_Version     VARCHAR(18) NULL,
  BoM_Revsion     VARCHAR(18) NULL,
  Valid_Dt        Datetime not null default getdate(), 
  Status          CHAR(1) NOT NULL DEFAULT 'A',
  ECN_Number      int null,
  Deleted_Flag    bit not null default 0,
  Prev_Hdr_Count  float null,
  BoM_UoM         CHAR(3) NULL,
  Base_Qty        float NULL,
  MTL_Group       CHAR(4) NULL,
  Language        CHAR(2) NULL,
  Alt_bom_txt     CHAR(40)     NULL,
  BoM_Status      CHAR(2) NULL,
  Plant           CHAR(4) NULL,
  Model_Number    VARCHAR(30)  NULL,
  Model_Nm        varchar(120) null,
  Creation_dt     Datetime not null default Getdate(),
  Created_By      Varchar(15) NOT NULL,
  Update_dt       Datetime not null default Getdate(),
  Updated_By      Varchar(15) not null ,
)
go


if object_id('dbo.GL_BOM_Item') is not null
drop table GL_BoM_Item
go

Create Table GL_BoM_Item
(
 BoM_Number    int not null constraint fk_bom_item references GL_BOM_Hdr(Bom_Number),
 BoM_Node      int not null,
 Status        CHAR(1) NULL,
 ECN           char(12)      NULL,
    del_ind       char(1)       NULL,
    prec_node     char(8)       NULL,
    prev_count    char(8)       NULL,
    crt_date      datetime      NULL,
    crt_person    char(12)      NULL,
    chg_date      datetime      NULL,
    chg_person    char(12)      NULL,
    bom_comp      char(18)      NULL,
    item_typ      char(4)       NULL,
    issue_plant   char(4)       NULL,
    itm_catg      char(1)       NULL,
    bom_itm_num   char(4)       NULL,
    sort_key      char(10)      NULL,
    uom           char(4)       NULL,
    qty           numeric(12,6) NULL,
    fix_qty       char(1)       NULL,
    bom_recursive char(1)       NULL,
    recursive     char(1)       NULL,
    alt_id        char(1)       NULL,
    phantom       char(1)       NULL,
    description   varchar(120)  NULL,
    descrip_2     varchar(120)  NULL,
    process_code  char(20)      NULL,
    eng_remark1   varchar(80)   NULL,
    eng_remark2   varchar(80)   NULL,
    eng_remark3   varchar(80)   NULL,
    eng_remark4   varchar(80)   NULL,
    eng_remark5   varchar(80)   NULL,
    inter_desc    char(120)     NULL,
    vend_part     char(32)      NULL,
    maker         char(32)      NULL,
    mpn_2         char(32)      NULL,
    cust_part     varchar(32)   NULL,
    consign_flat  char(1)       NULL,
    to_date       datetime      NULL,
    procure_type  char(2)       NULL,
    spec_procure  char(2)       NULL,
    tmp_field1    varchar(120)  NULL,
    tmp_field2    varchar(120)  NULL,
    tmp_field3    varchar(120)  NULL,
    tmp_field4    varchar(120)  NULL,
    tmp_field5    varchar(120)  NULL,
    tmp_field6    varchar(120)  NULL,
    tmp_field7    varchar(120)  NULL,
    tmp_field8    varchar(120)  NULL,
    tmp_field9    varchar(120)  NULL,
    tmp_field0    varchar(120)  NULL,
    CONSTRAINT ca_bom_itm_1216t_19708220831
    PRIMARY KEY CLUSTERED (bom_catg,bom_num,bom_node,int_count)
)
LOCK ALLPAGES