/**Begin E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\SM_table.sql **/
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDER') 
 CREATE TABLE T_SM_XSALORDER (
FID BIGINT DEFAULT 0 NOT NULL,
FBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBILLTYPEID BIGINT DEFAULT 0 NOT NULL,
FBIZTYPEID BIGINT DEFAULT 0 NOT NULL,
FBILLSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FORGID BIGINT DEFAULT 0 NOT NULL,
FDEPTID BIGINT DEFAULT 0 NOT NULL,
FOPERATORGROUPID BIGINT DEFAULT 0 NOT NULL,
FOPERATORID BIGINT DEFAULT 0 NOT NULL,
FBIZDATE DATETIME,
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FCREATETIME DATETIME,
FMODIFIERID BIGINT DEFAULT 0 NOT NULL,
FMODIFYTIME DATETIME,
FAUDITORID BIGINT DEFAULT 0 NOT NULL,
FAUDITDATE DATETIME,
FCLOSESTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCLOSERID BIGINT DEFAULT 0 NOT NULL,
FCLOSEDATE DATETIME,
FCANCELSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FCANCELERID BIGINT DEFAULT 0 NOT NULL,
FCANCELDATE DATETIME,
FVERSION VARCHAR(20) DEFAULT ' ' NOT NULL,
FCHANGERID BIGINT DEFAULT 0 NOT NULL,
FCHANGEDATE DATETIME,
FCHANGESTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FBIZTIME DATETIME,
FSRCBILLID BIGINT DEFAULT 0 NOT NULL,
FSRCBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSRCBIZTIME DATETIME,
FSRCBILLSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FREASON NVARCHAR(512) DEFAULT ' ',
FACTIVESTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FACTIVERID BIGINT DEFAULT 0 NOT NULL,
FACTIVEDATE DATETIME
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERDELIVENTRY') 
 CREATE TABLE T_SM_XSALORDERDELIVENTRY (
FDETAILID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ BIGINT DEFAULT 0 NOT NULL,
FPLANDATE DATETIME,
FTRANSPORTLEADTIME DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPLANDELIVERYDATE DATETIME,
FPLANUNITID BIGINT DEFAULT 0 NOT NULL,
FPLANQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FACTDELIVERYDATE DATETIME,
FACTQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMARK NVARCHAR(255) DEFAULT ' ' NOT NULL,
FDELENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL,
FSRCBILLDELIVENTRYID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERTERMENTRY') 
 CREATE TABLE T_SM_XSALORDERTERMENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FCLAUSEID BIGINT DEFAULT 0 NOT NULL,
FCLAUSEDESC VARCHAR(255) DEFAULT ' ' NOT NULL,
FCLAUSEENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL,
FSRCBILLCLAUSEENTRYID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERENTRY_R' AND KSQL_COL_NAME IN ('FPURJOINQTY')) 
 ALTER TABLE T_SM_SALORDERENTRY_R ADD (FPURJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERENTRY_R' AND KSQL_COL_NAME IN ('FPURQTY')) 
 ALTER TABLE T_SM_SALORDERENTRY_R ADD (FPURQTY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERENTRY_R' AND KSQL_COL_NAME IN ('FBASEPURQTY')) 
 ALTER TABLE T_SM_SALORDERENTRY_R ADD (FBASEPURQTY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERENTRY_R' AND KSQL_COL_NAME IN ('FASSOCIATEDBASEQTY')) 
 ALTER TABLE T_SM_SALORDERENTRY_R ADD (FASSOCIATEDBASEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERENTRY_R' AND KSQL_COL_NAME IN ('FASSOCIATEDQTY')) 
 ALTER TABLE T_SM_SALORDERENTRY_R ADD (FASSOCIATEDQTY DECIMAL(23,10) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERENTRY') 
 CREATE TABLE T_SM_XSALORDERENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ BIGINT DEFAULT 0 NOT NULL,
FSALESORGID BIGINT DEFAULT 0 NOT NULL,
FENTRYSTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FMATERIALNAME NVARCHAR(100) DEFAULT ' ' NOT NULL,
FMATERIALMASTERID BIGINT DEFAULT 0 NOT NULL,
FMATERIALID BIGINT DEFAULT 0 NOT NULL,
FAUXPTYID BIGINT DEFAULT 0 NOT NULL,
FRETURNTYPE VARCHAR(5) DEFAULT ' ' NOT NULL,
FUNITID BIGINT DEFAULT 0 NOT NULL,
FQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPRICEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPRICEUNITID BIGINT DEFAULT 0 NOT NULL,
FISPRESENT CHAR(1) DEFAULT '0' NOT NULL,
FCOREBILLNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FCOREBILLROWNO INT DEFAULT 0 NOT NULL,
FSTOCKORGID BIGINT DEFAULT 0 NOT NULL,
FPROJECTID BIGINT DEFAULT 0 NOT NULL,
FLOTID BIGINT DEFAULT 0 NOT NULL,
FBASEUNITID BIGINT DEFAULT 0 NOT NULL,
FDELIVERYDATE DATETIME,
FBASEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMARK NVARCHAR(255) DEFAULT ' ' NOT NULL,
FBIZUNITRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FUNITRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPRICEUNITRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FQTYBIZUNIT DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBIZUNITID BIGINT DEFAULT 0 NOT NULL,
FISSUEDQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FROWCLOSESTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FROWTERMINATESTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL,
FSRCBILLENTRYID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERENTRY_R') 
 CREATE TABLE T_SM_XSALORDERENTRY_R (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FBASEDELIJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASEBACKQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASETRANSJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASEINVOICEJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASEARJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASEPURJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASEDELIQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBACKQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FINVBASEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FINVQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMAININVBASEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMAININVQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMAINBASEBACKQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMAINBACKQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FREMAINJOINPRICEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FJOINPRICEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FDELIJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FDELIQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPURJOINQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPURQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FBASEPURQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FASSOCIATEDBASEQTY DECIMAL(23,10) DEFAULT 0 NOT NULL,
FASSOCIATEDQTY DECIMAL(23,10) DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERLOG') 
 CREATE TABLE T_SM_XSALORDERLOG (
FID BIGINT DEFAULT 0 NOT NULL,
FSRCBILLID BIGINT DEFAULT 0 NOT NULL,
FSRCBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FSRCBILLVERSION VARCHAR(30) DEFAULT ' ' NOT NULL,
FXBILLID BIGINT DEFAULT 0 NOT NULL,
FXBILLNO NVARCHAR(80) DEFAULT ' ' NOT NULL,
FBIZTIME DATETIME,
FXREASON NVARCHAR(512) DEFAULT ' ',
FCREATORID BIGINT DEFAULT 0 NOT NULL,
FSRCBILLJSON_TAG NCLOB,
FXBILLJSON_TAG NCLOB,
FXMDJSON_TAG NCLOB,
FSRCBILLJSON NVARCHAR(512),
FXBILLJSON NVARCHAR(512),
FXMDJSON NVARCHAR(512)
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERTRACEENTRY' AND KSQL_COL_NAME IN ('FTRACEENTRYCHANGETYPE')) 
 ALTER TABLE T_SM_SALORDERTRACEENTRY ADD (FTRACEENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERTERMENTRY' AND KSQL_COL_NAME IN ('FCLAUSEENTRYCHANGETYPE')) 
 ALTER TABLE T_SM_SALORDERTERMENTRY ADD (FCLAUSEENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERDELIVENTRY' AND KSQL_COL_NAME IN ('FDELENTRYCHANGETYPE')) 
 ALTER TABLE T_SM_SALORDERDELIVENTRY ADD (FDELENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERRPENTRY' AND KSQL_COL_NAME IN ('FRECENTRYCHANGETYPE')) 
 ALTER TABLE T_SM_SALORDERRPENTRY ADD (FRECENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERRPENTRY' AND KSQL_COL_NAME IN ('FREMAINAMOUNT')) 
 ALTER TABLE T_SM_SALORDERRPENTRY ADD (FREMAINAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL );
IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDER_C') 
 CREATE TABLE T_SM_XSALORDER_C (
FID BIGINT DEFAULT 0 NOT NULL,
FCUSTOMERID BIGINT DEFAULT 0 NOT NULL,
FDELIVERYWAYID BIGINT DEFAULT 0 NOT NULL,
FRECCUSTOMERID BIGINT DEFAULT 0 NOT NULL,
FSETTLECUSTOMERID BIGINT DEFAULT 0 NOT NULL,
FPAYINGCUSTOMERID BIGINT DEFAULT 0 NOT NULL,
FLOCATION VARCHAR(255) DEFAULT ' ' NOT NULL,
FRECEIVEADDRESS VARCHAR(255) DEFAULT ' ' NOT NULL,
FRECLINKMANID BIGINT DEFAULT 0 NOT NULL,
FLINKMANID BIGINT DEFAULT 0 NOT NULL,
FADDRESS VARCHAR(255) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDER_F') 
 CREATE TABLE T_SM_XSALORDER_F (
FID BIGINT DEFAULT 0 NOT NULL,
FCURRENCYID BIGINT DEFAULT 0 NOT NULL,
FSETTLECURRENCYID BIGINT DEFAULT 0 NOT NULL,
FSETTLETYPEID BIGINT DEFAULT 0 NOT NULL,
FSETTLEORGID BIGINT DEFAULT 0 NOT NULL,
FEXRATETABLEID BIGINT DEFAULT 0 NOT NULL,
FEXCHANGERATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FRECCONDITIONID BIGINT DEFAULT 0 NOT NULL,
FTOTALTAXAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FTOTALAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FTOTALALLAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FISTAX CHAR(1) DEFAULT '0' NOT NULL,
FCURTOTALAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FCURTOTALALLAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FMARGINLEVEL DECIMAL(23,10) DEFAULT 0 NOT NULL,
FMARGIN DECIMAL(19,6) DEFAULT 0 NOT NULL,
FASSOCIATEMARGIN DECIMAL(19,6) DEFAULT 0 NOT NULL,
FASSREFUNDMARGIN DECIMAL(19,6) DEFAULT 0 NOT NULL,
FISPAYRATE CHAR(1) DEFAULT '1' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERENTRY_F') 
 CREATE TABLE T_SM_XSALORDERENTRY_F (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FPRICE DECIMAL(19,6) DEFAULT 0 NOT NULL,
FTAXRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FPRICEANDTAX DECIMAL(19,6) DEFAULT 0 NOT NULL,
FCURTAXAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FTAXAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FDISCOUNTTYPE VARCHAR(5) DEFAULT ' ' NOT NULL,
FDISCOUNTRATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FDISCOUNTAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FCURAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FAMOUNTANDTAX DECIMAL(19,6) DEFAULT 0 NOT NULL,
FCURAMOUNTANDTAX DECIMAL(19,6) DEFAULT 0 NOT NULL,
FENTRYSETTLEORGID BIGINT DEFAULT 0 NOT NULL,
FSETTLEDEPTID BIGINT DEFAULT 0 NOT NULL,
FSETTLEAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FTAXRATEID BIGINT DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERRPENTRY') 
 CREATE TABLE T_SM_XSALORDERRPENTRY (
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FDUEDATE DATETIME,
FRECADVANCERATE DECIMAL(23,10) DEFAULT 0 NOT NULL,
FRECADVANCEAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FNEEDRECADVANCE CHAR(1) DEFAULT '0' NOT NULL,
FRELBILLNO VARCHAR(50) DEFAULT ' ' NOT NULL,
FRECAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL,
FCONTROLSEND VARCHAR(5) DEFAULT ' ' NOT NULL,
FITEMNAMEID BIGINT DEFAULT 0 NOT NULL,
FRECENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL,
FSRCBILLRECENTRYID BIGINT DEFAULT 0 NOT NULL,
FREMAINAMOUNT DECIMAL(19,6) DEFAULT 0 NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDERTRACEENTRY') 
 CREATE TABLE T_SM_XSALORDERTRACEENTRY (
FID BIGINT DEFAULT 0 NOT NULL,
FENTRYID BIGINT DEFAULT 0 NOT NULL,
FSEQ INT DEFAULT 0 NOT NULL,
FLOGCOMID BIGINT DEFAULT 0 NOT NULL,
FCARRYBILLNO VARCHAR(80) DEFAULT ' ' NOT NULL,
FPHONENUMBER VARCHAR(80) DEFAULT ' ' NOT NULL,
FDELITIME DATETIME,
FTRACESTATUS VARCHAR(5) DEFAULT ' ' NOT NULL,
FSRCBILLTRACEENTRYID BIGINT DEFAULT 0 NOT NULL,
FTRACEENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL
);


IF NOT EXISTS (SELECT 1 FROM KSQL_USERCOLUMNS WHERE KSQL_COL_TABNAME = 'T_SM_SALORDERENTRY' AND KSQL_COL_NAME IN ('FENTRYCHANGETYPE')) 
 ALTER TABLE T_SM_SALORDERENTRY ADD (FENTRYCHANGETYPE VARCHAR(5) DEFAULT ' ' NOT NULL );
 --- drop column --------- 请认真检查该脚本 ------------------- 
 EXEC p_AlterColumn 'T_SM_SALORDERENTRY', 'FASSOCIATEDQTY', 'NULL', 'NULL', '1001', 'NULL';

IF NOT EXISTS (SELECT 1 FROM KSQL_USERTABLES WHERE KSQL_TABNAME = 'T_SM_XSALORDER_L') 
 CREATE TABLE T_SM_XSALORDER_L (
FPKID VARCHAR(36) DEFAULT ' ' NOT NULL,
FID BIGINT DEFAULT 0 NOT NULL,
FLOCALEID VARCHAR(10) DEFAULT ' ' NOT NULL,
FCOMMENT NVARCHAR(512) DEFAULT ' '
);



/**End E:\NextERP_PDM\PDM_UPGRADE\PDMS\branch_lastest\pdm\SM_table.sql **/
