177 lines
15 KiB
SQL
Executable File
177 lines
15 KiB
SQL
Executable File
/* 脚本导出时间:2026/08/18 14:46:23 */
|
|
/* 脚本导出人员:系统管理员 */
|
|
/* 脚本 说明:T_PUR_POORDERENTRY 采购订单明细*/
|
|
-- DROP TABLE T_PUR_POORDERENTRY
|
|
IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'T_PUR_POORDERENTRY') AND type in (N'U'))
|
|
BEGIN
|
|
CREATE TABLE T_PUR_POORDERENTRY(
|
|
FROWTYPE NVARCHAR(160) NOT NULL DEFAULT '' , -- 产品类型
|
|
FPRODUCTTYPE VARCHAR(50) NOT NULL DEFAULT '' , -- 委外产品类型
|
|
FMATERIALID VARCHAR(50) NOT NULL DEFAULT '' , -- 物料
|
|
FBARCODE VARCHAR(500) NOT NULL DEFAULT '' , -- 零售条形码
|
|
FQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 数量
|
|
FRECEIVEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 累计收料数量
|
|
FBASEUNITQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 基本单位数量
|
|
FPRICE DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 单价
|
|
FTAXRATE DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 税率%
|
|
FAUXPROPID VARCHAR(50) NOT NULL DEFAULT '' , -- 辅助属性
|
|
FREQUIREORGID VARCHAR(50) NOT NULL DEFAULT '' , -- 需求组织
|
|
FRECEIVEORGID VARCHAR(50) NOT NULL DEFAULT '' , -- 收料组织
|
|
FSETTLEORGID VARCHAR(50) NOT NULL DEFAULT '' , -- 结算组织
|
|
FSRCBIZUNITID DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 携带的主业务单位
|
|
FSTOCKBASEDEN DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 库存基本分母
|
|
FPURBASENUM VARCHAR(50) NOT NULL DEFAULT '' , -- 采购基本分子
|
|
FTAXPRICE DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 含税单价
|
|
FDISCOUNTRATE DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 折扣率%
|
|
FDISCOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 折扣额
|
|
FBASEUNITID VARCHAR(50) NOT NULL DEFAULT '' , -- 基本单位
|
|
FUNITID VARCHAR(50) NOT NULL DEFAULT '' , -- 常用单位
|
|
FAMOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 金额
|
|
FAMOUNT_LC DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 金额(本位币)
|
|
FSRCBILLNO VARCHAR(50) NOT NULL DEFAULT '' , -- 源单单据编号
|
|
FSRCFORMID VARCHAR(50) NOT NULL DEFAULT '' , -- 源单类型
|
|
FSUPMATID VARCHAR(50) NOT NULL DEFAULT '' , -- 供应商物料编码
|
|
FSUPMATNAME VARCHAR(50) NOT NULL DEFAULT '' , -- 供应商物料名称
|
|
FSUPPLIERLOT VARCHAR(50) NOT NULL DEFAULT '' , -- 供应商批号
|
|
FTAXAMOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 税额
|
|
FTAXAMOUNT_LC DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 税额(本位币)
|
|
FALLAMOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 价税合计
|
|
FALLAMOUNT_LC DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 价税合计(本位币)
|
|
FDELIVERYDATE DATE NULL , -- 交货日期
|
|
FMRPCLOSESTATUS BIT NOT NULL DEFAULT 0 , -- 业务关闭
|
|
FLOT VARCHAR(50) NOT NULL DEFAULT '' , -- 批次
|
|
FNOTE VARCHAR(255) NOT NULL DEFAULT '' , -- 备注
|
|
FMRBQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 累计退料数量
|
|
FINSTOCKQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 累计入库数量
|
|
FRECEIVEBASEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 累计收料基本数量
|
|
FMRBBASEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 累计退料基本数量
|
|
FINSTOCKBASEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 , -- 累计入库基本数量
|
|
F_RESEARCHPROJECT VARCHAR(50) NOT NULL DEFAULT '' , -- 研发项目
|
|
FINTERID VARCHAR(50) NOT NULL DEFAULT '', -- 表内码
|
|
FENTRYID INT NOT NULL DEFAULT 0 , -- 行号
|
|
FDETAILID 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_POORDERENTRY] PRIMARY KEY NONCLUSTERED
|
|
)
|
|
END
|
|
ELSE
|
|
BEGIN
|
|
if(not exists(select name from SysColumns where name ='FROWTYPE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FROWTYPE NVARCHAR(160) NOT NULL DEFAULT '' -- 产品类型
|
|
if(not exists(select name from SysColumns where name ='FPRODUCTTYPE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FPRODUCTTYPE VARCHAR(50) NOT NULL DEFAULT '' -- 委外产品类型
|
|
if(not exists(select name from SysColumns where name ='FMATERIALID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FMATERIALID VARCHAR(50) NOT NULL DEFAULT '' -- 物料
|
|
if(not exists(select name from SysColumns where name ='FBARCODE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FBARCODE VARCHAR(500) NOT NULL DEFAULT '' -- 零售条形码
|
|
if(not exists(select name from SysColumns where name ='FQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 数量
|
|
if(not exists(select name from SysColumns where name ='FRECEIVEQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FRECEIVEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 累计收料数量
|
|
if(not exists(select name from SysColumns where name ='FBASEUNITQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FBASEUNITQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 基本单位数量
|
|
if(not exists(select name from SysColumns where name ='FPRICE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FPRICE DECIMAL(24,6) NOT NULL DEFAULT 0 -- 单价
|
|
if(not exists(select name from SysColumns where name ='FTAXRATE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FTAXRATE DECIMAL(24,6) NOT NULL DEFAULT 0 -- 税率%
|
|
if(not exists(select name from SysColumns where name ='FAUXPROPID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FAUXPROPID VARCHAR(50) NOT NULL DEFAULT '' -- 辅助属性
|
|
if(not exists(select name from SysColumns where name ='FREQUIREORGID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FREQUIREORGID VARCHAR(50) NOT NULL DEFAULT '' -- 需求组织
|
|
if(not exists(select name from SysColumns where name ='FRECEIVEORGID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FRECEIVEORGID VARCHAR(50) NOT NULL DEFAULT '' -- 收料组织
|
|
if(not exists(select name from SysColumns where name ='FSETTLEORGID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSETTLEORGID VARCHAR(50) NOT NULL DEFAULT '' -- 结算组织
|
|
if(not exists(select name from SysColumns where name ='FSRCBIZUNITID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSRCBIZUNITID DECIMAL(24,6) NOT NULL DEFAULT 0 -- 携带的主业务单位
|
|
if(not exists(select name from SysColumns where name ='FSTOCKBASEDEN' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSTOCKBASEDEN DECIMAL(24,6) NOT NULL DEFAULT 0 -- 库存基本分母
|
|
if(not exists(select name from SysColumns where name ='FPURBASENUM' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FPURBASENUM VARCHAR(50) NOT NULL DEFAULT '' -- 采购基本分子
|
|
if(not exists(select name from SysColumns where name ='FTAXPRICE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FTAXPRICE DECIMAL(24,6) NOT NULL DEFAULT 0 -- 含税单价
|
|
if(not exists(select name from SysColumns where name ='FDISCOUNTRATE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FDISCOUNTRATE DECIMAL(24,6) NOT NULL DEFAULT 0 -- 折扣率%
|
|
if(not exists(select name from SysColumns where name ='FDISCOUNT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FDISCOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 -- 折扣额
|
|
if(not exists(select name from SysColumns where name ='FBASEUNITID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FBASEUNITID VARCHAR(50) NOT NULL DEFAULT '' -- 基本单位
|
|
if(not exists(select name from SysColumns where name ='FUNITID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FUNITID VARCHAR(50) NOT NULL DEFAULT '' -- 常用单位
|
|
if(not exists(select name from SysColumns where name ='FAMOUNT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FAMOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 -- 金额
|
|
if(not exists(select name from SysColumns where name ='FAMOUNT_LC' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FAMOUNT_LC DECIMAL(24,6) NOT NULL DEFAULT 0 -- 金额(本位币)
|
|
if(not exists(select name from SysColumns where name ='FSRCBILLNO' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSRCBILLNO VARCHAR(50) NOT NULL DEFAULT '' -- 源单单据编号
|
|
if(not exists(select name from SysColumns where name ='FSRCFORMID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSRCFORMID VARCHAR(50) NOT NULL DEFAULT '' -- 源单类型
|
|
if(not exists(select name from SysColumns where name ='FSUPMATID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSUPMATID VARCHAR(50) NOT NULL DEFAULT '' -- 供应商物料编码
|
|
if(not exists(select name from SysColumns where name ='FSUPMATNAME' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSUPMATNAME VARCHAR(50) NOT NULL DEFAULT '' -- 供应商物料名称
|
|
if(not exists(select name from SysColumns where name ='FSUPPLIERLOT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FSUPPLIERLOT VARCHAR(50) NOT NULL DEFAULT '' -- 供应商批号
|
|
if(not exists(select name from SysColumns where name ='FTAXAMOUNT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FTAXAMOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 -- 税额
|
|
if(not exists(select name from SysColumns where name ='FTAXAMOUNT_LC' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FTAXAMOUNT_LC DECIMAL(24,6) NOT NULL DEFAULT 0 -- 税额(本位币)
|
|
if(not exists(select name from SysColumns where name ='FALLAMOUNT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FALLAMOUNT DECIMAL(24,6) NOT NULL DEFAULT 0 -- 价税合计
|
|
if(not exists(select name from SysColumns where name ='FALLAMOUNT_LC' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FALLAMOUNT_LC DECIMAL(24,6) NOT NULL DEFAULT 0 -- 价税合计(本位币)
|
|
if(not exists(select name from SysColumns where name ='FDELIVERYDATE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FDELIVERYDATE DATE NULL -- 交货日期
|
|
if(not exists(select name from SysColumns where name ='FMRPCLOSESTATUS' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FMRPCLOSESTATUS BIT NOT NULL DEFAULT 0 -- 业务关闭
|
|
if(not exists(select name from SysColumns where name ='FLOT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FLOT VARCHAR(50) NOT NULL DEFAULT '' -- 批次
|
|
if(not exists(select name from SysColumns where name ='FNOTE' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FNOTE VARCHAR(255) NOT NULL DEFAULT '' -- 备注
|
|
if(not exists(select name from SysColumns where name ='FMRBQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FMRBQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 累计退料数量
|
|
if(not exists(select name from SysColumns where name ='FINSTOCKQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FINSTOCKQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 累计入库数量
|
|
if(not exists(select name from SysColumns where name ='FRECEIVEBASEQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FRECEIVEBASEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 累计收料基本数量
|
|
if(not exists(select name from SysColumns where name ='FMRBBASEQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FMRBBASEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 累计退料基本数量
|
|
if(not exists(select name from SysColumns where name ='FINSTOCKBASEQTY' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FINSTOCKBASEQTY DECIMAL(24,6) NOT NULL DEFAULT 0 -- 累计入库基本数量
|
|
if(not exists(select name from SysColumns where name ='F_RESEARCHPROJECT' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add F_RESEARCHPROJECT VARCHAR(50) NOT NULL DEFAULT '' -- 研发项目
|
|
if(not exists(select name from SysColumns where name ='FCOMPANYID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FCOMPANYID VARCHAR(50) NOT NULL DEFAULT '' -- 组织代码
|
|
if(not exists(select name from SysColumns where name ='FINTERID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FINTERID VARCHAR(50) NOT NULL DEFAULT '' -- 表内码
|
|
if(not exists(select name from SysColumns where name ='FDETAILID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FDETAILID VARCHAR(50) NOT NULL DEFAULT '' -- 表体内码
|
|
if(not exists(select name from SysColumns where name ='FENTRYID' and id= OBJECT_ID('T_PUR_POORDERENTRY')))
|
|
Alter Table T_PUR_POORDERENTRY add FENTRYID INT NOT NULL DEFAULT 0 -- 行号
|
|
|
|
END
|
|
GO
|
|
|
|
--
|
|
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID(N'T_PUR_POORDERENTRY') AND name = N'T_PUR_MRBENTRY_1' )
|
|
Drop Index T_PUR_POORDERENTRY.T_PUR_MRBENTRY_1
|
|
GO
|
|
Create unique index T_PUR_MRBENTRY_1 on T_PUR_POORDERENTRY(FDETAILID)
|
|
GO
|
|
--
|
|
IF EXISTS (SELECT * FROM sysindexes WHERE id = OBJECT_ID(N'T_PUR_POORDERENTRY') AND name = N'T_PUR_MRBENTRY_2' )
|
|
Drop Index T_PUR_POORDERENTRY.T_PUR_MRBENTRY_2
|
|
GO
|
|
Create index T_PUR_MRBENTRY_2 on T_PUR_POORDERENTRY(FINTERID)
|
|
GO
|
|
|
|
|
|
GO
|
|
|