--PRJ-00260325 用餐订单加字段
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_MEALBILL' AND KSQL_COL_NAME IN ('FREIMBURSENUM'))
ALTER TABLE T_ER_MEALBILL ADD (FREIMBURSENUM VARCHAR(80) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_ER_MEALBILL' AND KSQL_COL_NAME IN ('FREIMBURSESTATUS'))
ALTER TABLE T_ER_MEALBILL ADD (FREIMBURSESTATUS CHAR(1) DEFAULT ' ' NOT NULL );

--用餐结算单建表
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_MEALCHECKINGBILL')
  CREATE TABLE T_ER_MEALCHECKINGBILL (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLSTATUS VARCHAR(50) DEFAULT ' ' NOT NULL,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FAUDITORID BIGINT DEFAULT 0 NOT NULL,
FAUDITDATE DATETIME,
FMODIFYTIME DATETIME,
FCREATETIME DATETIME,
FORGID BIGINT DEFAULT 0 NOT NULL,
FSERVER VARCHAR(50) DEFAULT ' ' NOT NULL,
FORDERDATE DATETIME,
FORDERNUM VARCHAR(80) DEFAULT ' ' NOT NULL,
FBATCHNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FORDERTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FOPERATIONTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FISBUSINESS VARCHAR(50) DEFAULT ' ' NOT NULL,
FPRODUCTTYPE VARCHAR(50) DEFAULT ' ' NOT NULL,
FHAPPENDDATE DATETIME,
FOABILLNUM VARCHAR(80) DEFAULT ' ' NOT NULL,
FORDERSTATUS VARCHAR(50) DEFAULT ' ' NOT NULL,
FSOURCEBOOKEDID BIGINT DEFAULT 0 NOT NULL,
FBOOKEDNAME VARCHAR(50) DEFAULT ' ' NOT NULL,
FCOMPANYID BIGINT DEFAULT 0 NOT NULL,
FSETTLEMAIN BIGINT DEFAULT 0 NOT NULL,
FSETTLEDEPT BIGINT DEFAULT 0 NOT NULL,
FALLORDERBASEID BIGINT DEFAULT 0 NOT NULL,
FPERIOD NVARCHAR(50) DEFAULT ' ' NOT NULL,
FTOTALAMOUNT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FCURRENCY BIGINT DEFAULT 0 NOT NULL,
FMEALTIME DATETIME,
FMEALCITYNAME NVARCHAR(255) DEFAULT ' ' NOT NULL,
FDINNERSCENE VARCHAR(50) DEFAULT ' ' NOT NULL,
FSERVICEFEE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPERSONALFEE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSHOPNAME NVARCHAR(255) DEFAULT ' ' NOT NULL,
FSHOPADDRESS NVARCHAR(255) DEFAULT ' ' NOT NULL,
FISBALANCE CHAR(1) DEFAULT '0' NOT NULL,
FBALANCEREMARK NVARCHAR(500) DEFAULT ' ' NOT NULL,
FISDEDUCTIBLE CHAR(1) DEFAULT '0' NOT NULL,
FTOTALTAX DECIMAL(23,10) DEFAULT 0 NOT NULL,
FTOTALTAXRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FORDERAMOUNTTAX DECIMAL(23,10) DEFAULT 0 NOT NULL,
FORDERDEDUCTRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSERVICEAMOUNTTAX DECIMAL(23,10) DEFAULT 0 NOT NULL,
FSERVICEDEDUCTRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREIMBURSESTATUS CHAR(1) DEFAULT ' ' NOT NULL,
FORDERISINVOICEREP CHAR(1) DEFAULT '0' NOT NULL,
FPAYBILLNUM VARCHAR(80) DEFAULT ' ' NOT NULL,
FPAYBILLSTATUS CHAR(1) DEFAULT ' ' NOT NULL,
FPAYBILLID BIGINT DEFAULT 0 NOT NULL,
FPUSHCOUNT INT DEFAULT 0 NOT NULL,
FVOUCHERNUM VARCHAR(80) DEFAULT ' ' NOT NULL,
FISVOUCHER CHAR(1) DEFAULT '0' NOT NULL,
FREIMBURSENUM VARCHAR(80) DEFAULT ' ' NOT NULL,
FCHECKINGBILLNUM VARCHAR(80) DEFAULT ' ' NOT NULL
);

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_ER_MEALCHECKINGBILL_L')
  CREATE TABLE T_ER_MEALCHECKINGBILL_L (
FPKID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(50) DEFAULT ' ' NOT NULL,
FBALANCEREMARK NVARCHAR(1000) DEFAULT ' ' NOT NULL
);

EXEC P_ALTERPK 'PK_T_ER_MEALCHECKINGBILL', 'T_ER_MEALCHECKINGBILL', 'FID', '0';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_MEALCHECKINGBILL_BILLNO') CREATE INDEX IDX_ER_MEALCHECKINGBILL_BILLNO ON T_ER_MEALCHECKINGBILL ( FORDERNUM ,FSERVER );

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_MEALCHECKINGBILL_ORG') CREATE INDEX IDX_ER_MEALCHECKINGBILL_ORG ON T_ER_MEALCHECKINGBILL ( FCOMPANYID ,FSETTLEMAIN );



EXEC P_ALTERPK 'PK_T_ER_MEALCHECKINGBILL_L', 'T_ER_MEALCHECKINGBILL_L', 'FPKID', '1';

 IF NOT EXISTS (SELECT 1 FROM KSQL_INDEXES WHERE KSQL_INDNAME = 'IDX_ER_MEALCHECKINGBILL_L') CREATE INDEX IDX_ER_MEALCHECKINGBILL_L ON T_ER_MEALCHECKINGBILL_L ( FID ,FLOCALEID );
