Mlmsalesreport table schema » History » Version 2
chin-yeh, 02/24/2012 03:26 PM
| 1 | 1 | chin-yeh | h1. mlmsalesreport table schema |
|---|---|---|---|
| 2 | |||
| 3 | 2 | chin-yeh | For *MySQL* only. |
| 4 | |||
| 5 | 1 | chin-yeh | <pre> |
| 6 | <code class="SQL"> |
||
| 7 | |||
| 8 | |||
| 9 | DROP TABLE T_REALMLM_SALES_LIST |
||
| 10 | ; |
||
| 11 | |||
| 12 | |||
| 13 | |||
| 14 | |||
| 15 | DROP TABLE T_REALMLM_SALES |
||
| 16 | ; |
||
| 17 | |||
| 18 | |||
| 19 | |||
| 20 | |||
| 21 | DROP TABLE T_REALMLM |
||
| 22 | ; |
||
| 23 | |||
| 24 | |||
| 25 | |||
| 26 | CREATE TABLE T_REALMLM |
||
| 27 | ( |
||
| 28 | SEQ INTEGER NULL, |
||
| 29 | COMPANY_CODE VARCHAR(4) NULL, |
||
| 30 | JOBCODE VARCHAR(2) NULL, |
||
| 31 | STATE VARCHAR(10) NULL, |
||
| 32 | ERRCODE VARCHAR(10) NULL, |
||
| 33 | ERRORMSG VARCHAR(500) NULL, |
||
| 34 | GUARANTE_NUM VARCHAR(17) NULL, |
||
| 35 | REGIDAY DATE NULL, |
||
| 36 | SEANDAY DATE NULL, |
||
| 37 | RECVDAY DATE NULL |
||
| 38 | ) |
||
| 39 | ; |
||
| 40 | |||
| 41 | |||
| 42 | |||
| 43 | ALTER TABLE T_REALMLM |
||
| 44 | ADD PRIMARY KEY (SEQ) |
||
| 45 | ; |
||
| 46 | |||
| 47 | |||
| 48 | |||
| 49 | CREATE TABLE T_REALMLM_SALES |
||
| 50 | ( |
||
| 51 | SEQ INTEGER NOT NULL, |
||
| 52 | SALEORDER_NUM VARCHAR(30) NULL, |
||
| 53 | ORDER_TYPE VARCHAR(10) NULL, |
||
| 54 | MEMBER_KIND VARCHAR(1) NULL, |
||
| 55 | MEMBER_NAME VARCHAR(30) NULL, |
||
| 56 | MEMBER_NUM VARCHAR(14) NULL, |
||
| 57 | RESIDENT_NUM VARCHAR(13) NULL, |
||
| 58 | TEL_NO VARCHAR(20) NULL, |
||
| 59 | MOBILE_NO VARCHAR(20) NULL, |
||
| 60 | POST_NO1 VARCHAR(6) NULL, |
||
| 61 | POST_ADDR1 VARCHAR(120) NULL, |
||
| 62 | POST_ADDR2 VARCHAR(120) NULL, |
||
| 63 | SALES_CARD NUMERIC(12) NULL, |
||
| 64 | SALES_CASH NUMERIC(12) NULL, |
||
| 65 | SALES_SENDAMT NUMERIC(12) NULL, |
||
| 66 | SALES_DT VARCHAR(8) NULL, |
||
| 67 | OLD_ORDER_NUM VARCHAR(30) NULL, |
||
| 68 | OLD_ORDER_SEQ VARCHAR(30) NULL, |
||
| 69 | DELIVERY_QUANTITY NUMERIC(4) NULL, |
||
| 70 | DELIVERY_SEQ VARCHAR(30) NULL, |
||
| 71 | DELIVERY_DT VARCHAR(8) NULL |
||
| 72 | ) |
||
| 73 | ; |
||
| 74 | |||
| 75 | |||
| 76 | |||
| 77 | ALTER TABLE T_REALMLM_SALES |
||
| 78 | ADD PRIMARY KEY (SEQ) |
||
| 79 | ; |
||
| 80 | |||
| 81 | |||
| 82 | |||
| 83 | CREATE TABLE T_REALMLM_SALES_LIST |
||
| 84 | ( |
||
| 85 | SEQ INTEGER NOT NULL, |
||
| 86 | ORDER_SEQ VARCHAR(30) NULL, |
||
| 87 | GOODS_NAME VARCHAR(80) NULL, |
||
| 88 | GOODS_COST NUMERIC(8) NULL, |
||
| 89 | QUANTITY NUMERIC(4) NULL, |
||
| 90 | OLD_ORDER_NUM VARCHAR(30) NULL, |
||
| 91 | OLD_ORDER_SEQ VARCHAR(30) NULL |
||
| 92 | ) |
||
| 93 | ; |
||
| 94 | |||
| 95 | |||
| 96 | |||
| 97 | ALTER TABLE T_REALMLM_SALES_LIST |
||
| 98 | ADD PRIMARY KEY (SEQ,ORDER_SEQ) |
||
| 99 | ; |
||
| 100 | |||
| 101 | |||
| 102 | |||
| 103 | ALTER TABLE T_REALMLM_SALES |
||
| 104 | ADD FOREIGN KEY R_35 (SEQ) REFERENCES T_REALMLM(SEQ) |
||
| 105 | ; |
||
| 106 | |||
| 107 | |||
| 108 | |||
| 109 | ALTER TABLE T_REALMLM_SALES_LIST |
||
| 110 | ADD FOREIGN KEY R_36 (SEQ) REFERENCES T_REALMLM_SALES(SEQ) |
||
| 111 | ; |
||
| 112 | |||
| 113 | </code> |
||
| 114 | </pre> |