-- =============================================
-- Author:		<赵文兵>
-- Create date: <2021年4月3日15:47:09>
-- Description:	<创建表USER_QC_DSPO_ORDER和USER_QC_DSPO_FILE>
-- =============================================



CREATE TABLE [MES].[USER_QC_DSPO_ORDER](
	DSPO_NO                         	NUMERIC(38)                     	NOT NULL,
	DSPO_ID                         	VARCHAR(64)                     	NOT NULL,
	DSPO_TYPE                       	VARCHAR(5)                      	NOT NULL,
	STATUS                          	VARCHAR(5)                      	NOT NULL,
	PR_NO                           	NUMERIC(38)                     	,
	PO_NO                           	NUMERIC(38)                     	,
	PROJECT_ID                      	VARCHAR(255)                    	,
	LOT_NO                          	NUMERIC(38)                     	,
	BATCH_ID                        	VARCHAR(64)                     	,
	DEF_NO                          	NUMERIC(38)                     	,
	INSP_ORDER_NO                   	NUMERIC(38)                     	,
	PROD_DEF                        	VARCHAR(64)                     	,
	PROD_NAME                       	VARCHAR(128)                    	,
	DEF_VERSION                     	VARCHAR(64)                     	,
	DEPT_ID                         	VARCHAR(64)                     	,
	BATCH_QTY                       	NUMERIC(18,3)                   	,
	UNQLY_QTY                       	NUMERIC(18,3)                   	,
	DSPO_CLASS                      	VARCHAR(64)                     	,
	DSPO_ZERO                       	VARCHAR(64)                     	,
	UNQLY_DESC                      	VARCHAR(1024)                   	,
	CREATE_TIME                     	DATETIME2                       	,
	CREATE_USER                     	VARCHAR(64)                     	,
	UPDATE_TIME                     	DATETIME2                       	,
	UPDATE_USER                     	VARCHAR(64)                     	,
	ANALY_DESC                      	VARCHAR(1024)                   	,
	ANALY_ROUTE                     	VARCHAR(64)                     	,
	ANALY_USER                      	VARCHAR(64)                     	,
	ANALY_TIME                      	DATETIME2                       	,
	REVW_RESULT                     	VARCHAR(128)                    	,
	REVW_LEVEL                      	VARCHAR(128)                    	,
	REVW_DESC                       	VARCHAR(1024)                   	,
	REVW_USER                       	VARCHAR(64)                     	,
	REVW_TIME                       	DATETIME2                       	,
	PROC_DESC                       	VARCHAR(1024)                   	,
	PROC_USER                       	VARCHAR(64)                     	,
	PROC_TIME                       	DATETIME2                       	,
	CUST_DESC                       	VARCHAR(1024)                   	,
	CUST_USER                       	VARCHAR(64)                     	,
	CUST_TIME                       	DATETIME2                       	,
	ROUTE_DESC                      	VARCHAR(1024)                   	,
	ROUTE_USER                      	VARCHAR(64)                     	,
	ROUTE_TIME                      	DATETIME2                       	,
	JUDGMENT                        	VARCHAR(255)                    	,
	RESULT_DESC                     	VARCHAR(1024)                   	,
	RESULT_USER                     	VARCHAR(64)                     	,
	RESULT_TIME                     	DATETIME2                       	,
	EXT_VALUE01                     	VARCHAR(255)                    	,
	EXT_VALUE02                     	VARCHAR(255)                    	,
	EXT_VALUE03                     	VARCHAR(255)                    	,
	EXT_VALUE04                     	VARCHAR(255)                    	,
	EXT_VALUE05                     	VARCHAR(255)                    	,
	EXT_QTY01                       	NUMERIC(18,3)                   	,
	EXT_QTY02                       	NUMERIC(18,3)                   	,
	EXT_QTY03                       	NUMERIC(18,3)                   	,
	REMARK                          	VARCHAR(512)                    	
);

ALTER TABLE [MES].[USER_QC_DSPO_ORDER] ADD CONSTRAINT USER_QC_DSPO_ORDER_PK PRIMARY KEY (DSPO_NO);
ALTER TABLE [MES].[USER_QC_DSPO_ORDER] ADD CONSTRAINT USER_QC_DSPO_ORDER_PR_NO_FK
	FOREIGN KEY (PR_NO)
	REFERENCES [MES].PP_PRODUCTION_REPORT(PR_NO);
ALTER TABLE [MES].[USER_QC_DSPO_ORDER] ADD CONSTRAINT USER_QC_DSPO_ORDER_PO_NO_FK
	FOREIGN KEY (PO_NO)
	REFERENCES [MES].PO_PRODUCTION_ORDER(PO_NO);
ALTER TABLE [MES].[USER_QC_DSPO_ORDER] ADD CONSTRAINT USER_QC_DSPO_ORDER_LOT_NO_FK
	FOREIGN KEY (LOT_NO)
	REFERENCES [MES].MM_LOT(LOT_NO);
ALTER TABLE [MES].[USER_QC_DSPO_ORDER] ADD CONSTRAINT USER_QC_DSPO_ORDER_INSP_ORDER_NO_FK
	FOREIGN KEY (INSP_ORDER_NO)
	REFERENCES [MES].[QC_INSP_ORDER](INSP_ORDER_NO);

CREATE TABLE [MES].[USER_QC_DSPO_FILE](
	FILE_NO                         	NUMERIC(38)                     	NOT NULL,
	DSPO_NO                         	NUMERIC(38)                     	NOT NULL,
	FILE_ID                         	VARCHAR(64)                     	NOT NULL,
	FILE_TYPE                       	VARCHAR(5)                      	,
	CREATE_TIME                     	DATETIME2                       	,
	PATH                            	VARCHAR(255)                    	,
	REMARK                          	VARCHAR(512)                    	
);

ALTER TABLE [MES].[USER_QC_DSPO_FILE] ADD CONSTRAINT USER_QC_DSPO_FILE_PK PRIMARY KEY (FILE_NO);


ALTER TABLE [MES].[USER_QC_DSPO_FILE] ADD CONSTRAINT USER_QC_DSPO_FILE_DSPO_NO_FK
	FOREIGN KEY (DSPO_NO)
	REFERENCES [MES].[USER_QC_DSPO_ORDER](DSPO_NO);