HEGII.ss.mes.wcf/DK.Client/Client.Controls/BarcodeBatchSet/sql/0001-TP_SET_BarCodeBatchLayout.sql

CREATE TABLE TP_SET_BarCodeBatchLayout      
(     
      
      
      
  LayoutID  integer    NOT NULL,    
  LayoutName  nvarchar2(50)    NOT NULL,    
  LayoutData  blob    NOT NULL,   
  Width  integer    NOT NULL,   
  Height  integer    NOT NULL,    
  Remarks  nvarchar2(500)    ,  
  displayNo  integer NOT NULL, 
  AccountID  integer    NOT NULL,   
  ValueFlag  char(1)  DEFAULT '1'  NOT NULL,    
  CreateTime  date  DEFAULT sysdate  NOT NULL,    
  CreateUserID  integer    NOT NULL,    
  UpdateTime  date  DEFAULT sysdate  NOT NULL,    
  UpdateUserID  integer    NOT NULL,    
  OPTimeStamp  timestamp(6)  DEFAULT systimestamp  NOT NULL  
      
)     ;
COMMENT ON TABLE TP_SET_BarCodeBatchLayout  IS '注浆条码打印模板';




COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.LayoutID  IS '条码打印格式ID';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.LayoutName  IS '条码打印格式名称';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.LayoutData  IS '条码打印格式数据';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.Width  IS '纸张宽度 (单位：mm，A4的宽度是210)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.Height  IS '纸张高度 (单位：mm，A4的高度是297)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.Remarks  IS '备注';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.AccountID  IS '帐套ID (所属帐套ID)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.ValueFlag  IS '有效标识 (1：正常  0：停用)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.CreateTime  IS '创建时间 (更新时，不更新该数据)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.CreateUserID  IS '创建数据操作员ID (更新时，不更新该数据)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.UpdateTime  IS '更新时间 (新建时，取新建时间；更新时，取最新时间更新)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.UpdateUserID  IS '更新数据操作员ID (新建时，取创建者ID；更新时，取更新者ID)';
COMMENT ON COLUMN TP_SET_BarCodeBatchLayout.OPTimeStamp  IS '操作时间戳 (防止多用户同时对单一记录操作的时间戳)';


alter table TP_SET_BarCodeBatchLayout
  add constraint PK_SET_BarCodeBatchLayout primary key (LayoutID)
  using index 
  tablespace TP_BF_DKMES
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    next 1M
    minextents 1
    maxextents unlimited
  );
/
 
 -- Create sequence 
create sequence SEQ_SET_BarCodeBatchLayout_ID
minvalue 1
maxvalue 9999999999999999999999999999
start with 1
increment by 1
nocache;
/
 
  CREATE OR REPLACE TRIGGER TRG_SET_BarCodeBatchLayout_B_I BEFORE INSERT ON TP_SET_BarCodeBatchLayout REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
declare
begin
  if :NEW.LayoutID is null then
    :NEW.LayoutID := SEQ_SET_BarCodeBatchLayout_ID.Nextval;
  end if;
end TRG_SET_BarCodeBatchLayout_B_I;
/
CREATE OR REPLACE TRIGGER TRG_SET_BarCodeBatchLayout_B_U BEFORE UPDATE ON TP_SET_BarCodeBatchLayout REFERENCING OLD AS "OLD" NEW AS "NEW" FOR EACH ROW
declare
begin
  :NEW.UpdateTime     := sysdate;
  :NEW.OPTimeStamp    := systimestamp;
end TRG_SET_BarCodeBatchLayout_B_U;
/
      
-- Create table
create table TP_SET_BarCodeITEM
(
  itemcode   varchar2(100) not null,
  itemsample varchar2(100),
  itemtype   varchar2(100),
  col_code   varchar2(100),
  displayno  int,
  ValueFlag  char(1)  DEFAULT '1'  NOT NULL, 
  itemtags   varchar2(1000)
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table TP_SET_BarCodeITEM
  add constraint PK_SET_BarCodeItem primary key (ITEMCODE);
/

insert into TP_SET_BARCODEITEM (ITEMCODE, ITEMSAMPLE, ITEMTYPE, COL_CODE, DISPLAYNO, ITEMTAGS)
values ('{#产品条码#}', '12345678901', 'TXT', 'BarCode', 1, null);

insert into TP_SET_BARCODEITEM (ITEMCODE, ITEMSAMPLE, ITEMTYPE, COL_CODE, DISPLAYNO, ITEMTAGS)
values ('{#注浆日期#}', '2024-05-05', 'DATE', 'GroutingDate', 2, null);

insert into TP_SET_BARCODEITEM (ITEMCODE, ITEMSAMPLE, ITEMTYPE, COL_CODE, DISPLAYNO, ITEMTAGS)
values ('{#注浆批次#}', '1', 'TXT', 'GroutingBatchNo', 3, null);

insert into TP_SET_BARCODEITEM (ITEMCODE, ITEMSAMPLE, ITEMTYPE, COL_CODE, DISPLAYNO, ITEMTAGS)
values ('{#成型线编码#}', 'C01C01', 'TXT', 'GroutingLineCode', 4, null);

insert into TP_SET_BARCODEITEM (ITEMCODE, ITEMSAMPLE, ITEMTYPE, COL_CODE, DISPLAYNO, ITEMTAGS)
values ('{#模具编号#}', 'C01C01-001', 'TXT', 'GroutingMouldCode', 5, null);

insert into TP_SET_BARCODEITEM (ITEMCODE, ITEMSAMPLE, ITEMTYPE, COL_CODE, DISPLAYNO, ITEMTAGS)
values ('{#产品编码#}', 'H0127L', 'TXT', 'GoodsCode', 6, null);

/
-- Create table
create table TP_SET_BarCodeBatchItem
(
  layoutid   int,
  itemcode   varchar2(100),
  itemsample varchar2(100)
)
;
-- Create/Recreate primary, unique and foreign key constraints 
alter table TP_SET_BarCodeBatchItem
  add constraint PK_SET_BarCodeBatchItem primary key (LAYOUTID, ITEMCODE);
/