162 lines
13 KiB
SQL
Executable File
162 lines
13 KiB
SQL
Executable File
/* 脚本导出时间:2026/08/18 14:46:34 */
|
|
/* 脚本导出人员:系统管理员 */
|
|
/* 脚本 说明:T_PUR_RECEIVE 收料通知单*/
|
|
-- DROP TABLE T_PUR_RECEIVE
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'T_PUR_RECEIVE') AND type in (N'U'))
|
|
BEGIN
|
|
CREATE TABLE T_PUR_RECEIVE(
|
|
FBILLTYPEID VARCHAR(50) NOT NULL DEFAULT '' , -- 单据类型
|
|
FSUPPLYID VARCHAR(50) NOT NULL DEFAULT '' , -- 供应商
|
|
FPURCHASERID VARCHAR(50) NOT NULL DEFAULT '' , -- 采购员
|
|
FBUSINESSTYPE VARCHAR(50) NOT NULL DEFAULT '' , -- 业务类型
|
|
ISSYN BIT NOT NULL DEFAULT 0 , -- 同步单据
|
|
FERPNO VARCHAR(50) NOT NULL DEFAULT '' , -- ERP编号
|
|
FERPID VARCHAR(50) NOT NULL DEFAULT '' , -- ERP内码
|
|
FDATE DATE NULL , -- 收料日期
|
|
FPURDEPTID VARCHAR(50) NOT NULL DEFAULT '' , -- 采购部门
|
|
FRECEIVEDEPTID VARCHAR(50) NOT NULL DEFAULT '' , -- 收料部门
|
|
FRECEIVERID VARCHAR(50) NOT NULL DEFAULT '' , -- 收料员
|
|
FMANAGERID VARCHAR(50) NOT NULL DEFAULT '' , -- 主管
|
|
FDEMANDORGID VARCHAR(50) NOT NULL DEFAULT '' , -- 需求组织
|
|
FPURORGID VARCHAR(50) NOT NULL DEFAULT '' , -- 采购组织
|
|
FSENDBILLNO VARCHAR(50) NOT NULL DEFAULT '' , -- 送货单号
|
|
FPROVIDERID VARCHAR(50) NOT NULL DEFAULT '' , -- 供货方
|
|
FPROVIDERCONTACTID VARCHAR(50) NOT NULL DEFAULT '' , -- 供货方联系人
|
|
FPROVIDERADDRESS VARCHAR(50) NOT NULL DEFAULT '' , -- 供货方地址
|
|
FSETTLEID VARCHAR(50) NOT NULL DEFAULT '' , -- 结算方
|
|
FCHARGEID VARCHAR(50) NOT NULL DEFAULT '' , -- 收款方
|
|
FSETTLEORGID VARCHAR(50) NOT NULL DEFAULT '' , -- 结算组织
|
|
FSETTLECURRID VARCHAR(50) NOT NULL DEFAULT '' , -- 结算币别
|
|
FEXCHANGETYPEID VARCHAR(50) NOT NULL DEFAULT '' , -- 汇率类型
|
|
FEXCHANGERATE DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 汇率
|
|
FFETCHADDRESS VARCHAR(255) NOT NULL DEFAULT '' , -- 交货地点
|
|
FNOTE VARCHAR(50) NOT NULL DEFAULT '' , -- 备注
|
|
FCHECKERID VARCHAR(50) NOT NULL DEFAULT '' , -- 审核人内码
|
|
FCHECKDATE DATETIME NULL , -- 审核日期
|
|
FDISABLEID VARCHAR(50) NOT NULL DEFAULT '' , -- 禁用人
|
|
FDISABLEDATE DATETIME NULL , -- 禁用日期
|
|
FSRCBILLNO VARCHAR(50) NOT NULL DEFAULT '' , -- 源单单据编号
|
|
FSRCFORMID VARCHAR(50) NOT NULL DEFAULT '' , -- 源单类型
|
|
FSFWJSD BIT NOT NULL DEFAULT 0 , -- 随附文件送达
|
|
FSHDSD BIT NOT NULL DEFAULT 0 , -- 送货单送达
|
|
FOASTATUS VARCHAR(50) NOT NULL DEFAULT '' , -- 审批状态
|
|
FOARESULT VARCHAR(50) NOT NULL DEFAULT '' , -- 审批结果
|
|
F_PATC_COMBO_QTR VARCHAR(50) NOT NULL DEFAULT '' , -- 是否加急
|
|
FINTERID VARCHAR(50) NOT NULL DEFAULT '', -- 表内码
|
|
FBILLNO VARCHAR(50) NOT NULL DEFAULT '', -- 单据编号
|
|
FGROUPID VARCHAR(50) NOT NULL DEFAULT '', -- 分组内码
|
|
FSTATUS INT NOT NULL DEFAULT 0 , -- 状态
|
|
FDELETED BIT NOT NULL DEFAULT 0 , --禁用状态 0 = 正常 1 = 禁用
|
|
FCOMPANYID VARCHAR(50) NOT NULL DEFAULT '', -- 组织代码
|
|
C_YMD DATETIME NULL, -- 资料创建日期
|
|
C_USER VARCHAR(20) NOT NULL DEFAULT '', -- 资料创建帐号
|
|
M_YMD DATETIME NULL, -- 最后异动日期
|
|
M_USER VARCHAR(20) NOT NULL DEFAULT '', -- 最后异动帐号
|
|
UID VARCHAR(50) NOT NULL CONSTRAINT [PK_T_PUR_RECEIVE] PRIMARY KEY NONCLUSTERED
|
|
)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
if(not exists(select name from SysColumns where name ='FBILLTYPEID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FBILLTYPEID VARCHAR(50) NOT NULL DEFAULT '' -- 单据类型
|
|
if(not exists(select name from SysColumns where name ='FSUPPLYID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSUPPLYID VARCHAR(50) NOT NULL DEFAULT '' -- 供应商
|
|
if(not exists(select name from SysColumns where name ='FPURCHASERID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FPURCHASERID VARCHAR(50) NOT NULL DEFAULT '' -- 采购员
|
|
if(not exists(select name from SysColumns where name ='FBUSINESSTYPE' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FBUSINESSTYPE VARCHAR(50) NOT NULL DEFAULT '' -- 业务类型
|
|
if(not exists(select name from SysColumns where name ='ISSYN' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add ISSYN BIT NOT NULL DEFAULT 0 -- 同步单据
|
|
if(not exists(select name from SysColumns where name ='FERPNO' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FERPNO VARCHAR(50) NOT NULL DEFAULT '' -- ERP编号
|
|
if(not exists(select name from SysColumns where name ='FERPID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FERPID VARCHAR(50) NOT NULL DEFAULT '' -- ERP内码
|
|
if(not exists(select name from SysColumns where name ='FDATE' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FDATE DATE NULL -- 收料日期
|
|
if(not exists(select name from SysColumns where name ='FPURDEPTID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FPURDEPTID VARCHAR(50) NOT NULL DEFAULT '' -- 采购部门
|
|
if(not exists(select name from SysColumns where name ='FRECEIVEDEPTID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FRECEIVEDEPTID VARCHAR(50) NOT NULL DEFAULT '' -- 收料部门
|
|
if(not exists(select name from SysColumns where name ='FRECEIVERID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FRECEIVERID VARCHAR(50) NOT NULL DEFAULT '' -- 收料员
|
|
if(not exists(select name from SysColumns where name ='FMANAGERID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FMANAGERID VARCHAR(50) NOT NULL DEFAULT '' -- 主管
|
|
if(not exists(select name from SysColumns where name ='FDEMANDORGID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FDEMANDORGID VARCHAR(50) NOT NULL DEFAULT '' -- 需求组织
|
|
if(not exists(select name from SysColumns where name ='FPURORGID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FPURORGID VARCHAR(50) NOT NULL DEFAULT '' -- 采购组织
|
|
if(not exists(select name from SysColumns where name ='FSENDBILLNO' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSENDBILLNO VARCHAR(50) NOT NULL DEFAULT '' -- 送货单号
|
|
if(not exists(select name from SysColumns where name ='FPROVIDERID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FPROVIDERID VARCHAR(50) NOT NULL DEFAULT '' -- 供货方
|
|
if(not exists(select name from SysColumns where name ='FPROVIDERCONTACTID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FPROVIDERCONTACTID VARCHAR(50) NOT NULL DEFAULT '' -- 供货方联系人
|
|
if(not exists(select name from SysColumns where name ='FPROVIDERADDRESS' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FPROVIDERADDRESS VARCHAR(50) NOT NULL DEFAULT '' -- 供货方地址
|
|
if(not exists(select name from SysColumns where name ='FSETTLEID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSETTLEID VARCHAR(50) NOT NULL DEFAULT '' -- 结算方
|
|
if(not exists(select name from SysColumns where name ='FCHARGEID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FCHARGEID VARCHAR(50) NOT NULL DEFAULT '' -- 收款方
|
|
if(not exists(select name from SysColumns where name ='FSETTLEORGID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSETTLEORGID VARCHAR(50) NOT NULL DEFAULT '' -- 结算组织
|
|
if(not exists(select name from SysColumns where name ='FSETTLECURRID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSETTLECURRID VARCHAR(50) NOT NULL DEFAULT '' -- 结算币别
|
|
if(not exists(select name from SysColumns where name ='FEXCHANGETYPEID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FEXCHANGETYPEID VARCHAR(50) NOT NULL DEFAULT '' -- 汇率类型
|
|
if(not exists(select name from SysColumns where name ='FEXCHANGERATE' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FEXCHANGERATE DECIMAL(24,6) NOT NULL DEFAULT 0 -- 汇率
|
|
if(not exists(select name from SysColumns where name ='FFETCHADDRESS' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FFETCHADDRESS VARCHAR(255) NOT NULL DEFAULT '' -- 交货地点
|
|
if(not exists(select name from SysColumns where name ='FNOTE' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FNOTE VARCHAR(50) NOT NULL DEFAULT '' -- 备注
|
|
if(not exists(select name from SysColumns where name ='FCHECKERID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FCHECKERID VARCHAR(50) NOT NULL DEFAULT '' -- 审核人内码
|
|
if(not exists(select name from SysColumns where name ='FCHECKDATE' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FCHECKDATE DATETIME NULL -- 审核日期
|
|
if(not exists(select name from SysColumns where name ='FDISABLEID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FDISABLEID VARCHAR(50) NOT NULL DEFAULT '' -- 禁用人
|
|
if(not exists(select name from SysColumns where name ='FDISABLEDATE' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FDISABLEDATE DATETIME NULL -- 禁用日期
|
|
if(not exists(select name from SysColumns where name ='FSRCBILLNO' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSRCBILLNO VARCHAR(50) NOT NULL DEFAULT '' -- 源单单据编号
|
|
if(not exists(select name from SysColumns where name ='FSRCFORMID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSRCFORMID VARCHAR(50) NOT NULL DEFAULT '' -- 源单类型
|
|
if(not exists(select name from SysColumns where name ='FSFWJSD' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSFWJSD BIT NOT NULL DEFAULT 0 -- 随附文件送达
|
|
if(not exists(select name from SysColumns where name ='FSHDSD' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FSHDSD BIT NOT NULL DEFAULT 0 -- 送货单送达
|
|
if(not exists(select name from SysColumns where name ='FOASTATUS' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FOASTATUS VARCHAR(50) NOT NULL DEFAULT '' -- 审批状态
|
|
if(not exists(select name from SysColumns where name ='FOARESULT' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FOARESULT VARCHAR(50) NOT NULL DEFAULT '' -- 审批结果
|
|
if(not exists(select name from SysColumns where name ='F_PATC_COMBO_QTR' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add F_PATC_COMBO_QTR VARCHAR(50) NOT NULL DEFAULT '' -- 是否加急
|
|
if(not exists(select name from SysColumns where name ='FCOMPANYID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FCOMPANYID VARCHAR(50) NOT NULL DEFAULT '' -- 组织代码
|
|
if(not exists(select name from SysColumns where name ='FINTERID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FINTERID VARCHAR(50) NOT NULL DEFAULT '' -- 表内码
|
|
if(not exists(select name from SysColumns where name ='FBILLNO' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FBILLNO VARCHAR(50) NOT NULL DEFAULT '' -- 单据编号
|
|
if(not exists(select name from SysColumns where name ='FGROUPID' and id= OBJECT_ID('T_PUR_RECEIVE')))
|
|
Alter Table T_PUR_RECEIVE add FGROUPID VARCHAR(50) NOT NULL DEFAULT '' -- 分组内码
|
|
|
|
END
|
|
GO
|
|
|
|
--
|
|
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID(N'T_PUR_RECEIVE') AND name = N'T_PUR_RECEIVE_1' )
|
|
Drop Index T_PUR_RECEIVE.T_PUR_RECEIVE_1
|
|
GO
|
|
Create unique index T_PUR_RECEIVE_1 on T_PUR_RECEIVE(FINTERID)
|
|
GO
|
|
--
|
|
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID(N'T_PUR_RECEIVE') AND name = N'T_PUR_RECEIVE_2' )
|
|
Drop Index T_PUR_RECEIVE.T_PUR_RECEIVE_2
|
|
GO
|
|
Create unique Clustered index T_PUR_RECEIVE_2 on T_PUR_RECEIVE(FBILLNO)
|
|
GO
|
|
|
|
|
|
GO
|
|
|