aibeike/sql/CreateTable.sql

CREATE TABLE CT_DOR_Dormitory (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
	CFRoomNumber NVARCHAR ( 100 ),
	CFRoomClassification VARCHAR ( 100 ),
	CFRoomTypeID VARCHAR ( 44 ),
	CFDormitoryTypeID VARCHAR ( 44 ),
	CFOccupantsNumber INT,
	CFOccupancyCapacity INT,
	CFIsFull INT,
	CFRemarks NVARCHAR ( 255 ),
	CFState INT DEFAULT 1,
	CFIsSealUp INT DEFAULT 0,
CONSTRAINT CPK_DOR_Dormitos62 PRIMARY KEY ( FID ));
CREATE TABLE CT_OCC_Occupants (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFPersonID VARCHAR ( 44 ),
	CFPersonPositionID VARCHAR ( 44 ),
	CFPersonDormitoryT VARCHAR ( 44 ),
	CFCheckInDate DateTime,
	CFCheckOutDate DateTime,
	CFDormitoryID VARCHAR ( 44 ),
	CFRemarks NVARCHAR ( 255 ),
	CFState  INT DEFAULT 1,
CONSTRAINT CPK_OCC_Occupan9mg PRIMARY KEY ( FID ));
CREATE TABLE T_OCC_OccupantsTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OCC_OccpnTRb0h PRIMARY KEY ( FID ));
CREATE TABLE CT_OTH_DormitoryType (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFState  INT DEFAULT 1,
CONSTRAINT CPK_OTH_DrmtrTI9n3 PRIMARY KEY ( FID ));
CREATE TABLE CT_OTH_PersonDormitoryType (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFState INT DEFAULT 1,
CONSTRAINT CPK_OTH_PersonDk1d PRIMARY KEY ( FID ));
CREATE TABLE CT_OTH_RoomType (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FTreeID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	CFState INT DEFAULT 1,
CONSTRAINT CPK_OTH_RoomTypv7q PRIMARY KEY ( FID ));
CREATE TABLE T_OTH_DormitoryTypeTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OTH_DrmtTTRt14 PRIMARY KEY ( FID ));
CREATE TABLE T_OTH_PERSONDORMITORYTTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
	FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OTH_PERSONDrbd PRIMARY KEY ( FID ));
CREATE TABLE T_OTH_RoomTypeTREE (
	FName_l1 NVARCHAR ( 255 ),
	FName_l2 NVARCHAR ( 255 ),
	FName_l3 NVARCHAR ( 255 ),
	FNumber NVARCHAR ( 80 ),
	FDescription_l1 NVARCHAR ( 255 ),
	FDescription_l2 NVARCHAR ( 255 ),
	FDescription_l3 NVARCHAR ( 255 ),
	FSimpleName NVARCHAR ( 80 ),
	FIsLeaf INT,
	FLevel INT,
	FLongNumber NVARCHAR ( 80 ),
	FID VARCHAR ( 44 ) DEFAULT '' NOT NULL,
	FCreatorID VARCHAR ( 44 ),
	FCreateTime DateTime,
	FLastUpdateUserID VARCHAR ( 44 ),
	FLastUpdateTime DateTime,
	FParentID VARCHAR ( 44 ),
	FControlUnitID VARCHAR ( 44 ) DEFAULT '11111111-1111-1111-1111-111111111111CCE7AED4',
	FDisplayName_l1 NVARCHAR ( 80 ),
	FDisplayName_l2 NVARCHAR ( 80 ),
FDisplayName_l3 NVARCHAR ( 80 ),
CONSTRAINT CPK_OTH_RmTTREEgiq PRIMARY KEY ( FID ));

-- 入住申请单
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckInApplication')
Create Table CT_APP_CheckInApplication ( FFivouchered INT DEFAULT 0,CFManagementNotes VARCHAR(2000),FAdminOrgUnitId VARCHAR(44),FHROrgUnitID VARCHAR(44),FBillState INT,FApproveType INT,FIsMultiEntry INT,FNumber NVARCHAR(80),FBizDate DateTime,FHandlerID VARCHAR(44),FDescription VARCHAR(255),FHasEffected INT,FAuditorID VARCHAR(44),FSourceBillID VARCHAR(80),FSourceFunction VARCHAR(255),FCreatorID VARCHAR(44),FCreateTime DateTime,FLastUpdateUserID VARCHAR(44),FLastUpdateTime DateTime,FControlUnitID VARCHAR(44),FID VARCHAR(44) DEFAULT '' NOT NULL ,CFErrorMsg NCLOB(1000),CONSTRAINT PK_CheckInAppliadf PRIMARY KEY (FID));

--入住申请单分录
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckInAE')
Create Table CT_APP_CheckInAE ( FParentID VARCHAR(44),CFPersonID VARCHAR(44),CFAdminOrgID VARCHAR(44),CFPositionID VARCHAR(44),CFCheckInRoomID VARCHAR(44),CFNotes VARCHAR(2000),CFCheckInDate DateTime,CFCheckoutDate DateTime,CFOccupantsID VARCHAR(44),FSeq INT,FID VARCHAR(44) DEFAULT '' NOT NULL ,CONSTRAINT PK_CheckInAppliES PRIMARY KEY (FID));

-- 退宿申请单
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckoutApplication')
Create Table CT_APP_CheckoutApplication ( FFivouchered INT DEFAULT 0,FAdminOrgUnitId VARCHAR(44),FHROrgUnitID VARCHAR(44),FBillState INT,FApproveType INT,FIsMultiEntry INT,FNumber NVARCHAR(80),FBizDate DateTime,FHandlerID VARCHAR(44),FDescription VARCHAR(255),FHasEffected INT,FAuditorID VARCHAR(44),FSourceBillID VARCHAR(80),FSourceFunction VARCHAR(255),FCreatorID VARCHAR(44),FCreateTime DateTime,FLastUpdateUserID VARCHAR(44),FLastUpdateTime DateTime,FControlUnitID VARCHAR(44),FID VARCHAR(44) DEFAULT '' NOT NULL ,CFManagementNotes NVARCHAR(2000),CFErrorMsg NVARCHAR(1000),CONSTRAINT PK_CheckoutApplkje PRIMARY KEY (FID));

-- 退宿申请单分录
If not exists (select 1 from KSQL_USERTABLES where KSQL_TABNAME='CT_APP_CheckoutAE')
Create Table CT_APP_CheckoutAE ( FParentID VARCHAR(44),CFPersonID VARCHAR(44),CFAdminOrgID VARCHAR(44),CFPositionID VARCHAR(44),CFCurrentRoomID VARCHAR(44),CFCheckInDate DateTime,CFCheckoutDate DateTime,CFNotes VARCHAR(2000),CFOccupantsID VARCHAR(44),FSeq INT,FID VARCHAR(44) DEFAULT '' NOT NULL ,CFOldCheckoutDate DateTime,CONSTRAINT PK_CheckoutApplESF PRIMARY KEY (FID));