henryyan
5/22/2012 - 6:17 AM
gistfile1.sql
diff --git a/joying-common-core/src/main/resources/sqls/erp/create.sql b/joying-common-core/src/main/resources/sqls/erp/create.sql
index 7c53e16..ec48ad4 100644
--- a/joying-common-core/src/main/resources/sqls/erp/create.sql
+++ b/joying-common-core/src/main/resources/sqls/erp/create.sql
@@ -54,6 +54,14 @@ create sequence SEQ_ERP_INSURE_PROJECT
increment by 1
start with 50;
+create sequence SEQ_INSURE_PROJECT_SHADOW
+increment by 1
+start with 50;
+
+create sequence SEQ_ERP_CAR_SHADOW
+increment by 1
+start with 1000;
+
/*==============================================================*/
/* Table: ERP_INSURE_PROJECT */
/*==============================================================*/
@@ -79,6 +87,7 @@ create table ERP_INSURE_PROJECT
CREATE_DATE DATE,
INSURE_PEOPLE_MUN NUMBER(9),
QUOTE_ID NUMBER(20),
+ REMARK VARCHAR2(4000),
constraint PK_ERP_INSURE_PROJECT primary key (ID)
);
@@ -142,6 +151,97 @@ comment on column ERP_INSURE_PROJECT.INSURE_PEOPLE_MUN is
comment on column ERP_INSURE_PROJECT.QUOTE_ID is
'报价ID';
+comment on column ERP_INSURE_PROJECT.REMARK is
+'备注';
+
+/*==============================================================*/
+/* Table: ERP_INSURE_PROJECT_SHADOW */
+/*==============================================================*/
+create table ERP_INSURE_PROJECT_SHADOW
+(
+ ID NUMBER(20) not null,
+ INSURE_COMPANY_ID VARCHAR2(30),
+ INSURE_LIABILITY_ID NUMBER(20),
+ INSURE_LIABILITY_TYPE VARCHAR2(9),
+ INSURE_ID NUMBER(20),
+ APPECT_INSURE_ID NUMBER(20),
+ ITEM_ID NUMBER(20),
+ INSURE_PROJECT_NUMBER VARCHAR2(30),
+ INSURE_PROJECT_NAME VARCHAR2(30),
+ INSURE_SCOPE VARCHAR2(1000),
+ INSURE_SUM NUMBER(20,2),
+ PRUMIEM NUMBER(20,2),
+ RATE NUMBER(20,2),
+ INSURE_LIABILITY VARCHAR2(100),
+ BROKERAGE_SCALE NUMBER(20,2),
+ SALESMAN_BROKERAGE NUMBER(20,2),
+ SALESMAN_ID NUMBER(20),
+ CREATE_DATE DATE,
+ INSURE_PEOPLE_MUN NUMBER(9),
+ REMARK VARCHAR2(4000),
+ constraint PK_INSURE_PROJECT_SHADOW primary key (ID)
+);
+
+comment on table ERP_INSURE_PROJECT_SHADOW is
+'投保项目影子表';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_COMPANY_ID is
+'保险公司ID';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_LIABILITY_ID is
+'保险责任ID';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_LIABILITY_TYPE is
+'保险险种类型';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_ID is
+'投保ID';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.APPECT_INSURE_ID is
+'承保ID';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.ITEM_ID is
+'保险项目ID';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_PROJECT_NUMBER is
+'保险项目编码';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_PROJECT_NAME is
+'保险项目名称';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_SCOPE is
+'保障名称';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_SUM is
+'保额';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.PRUMIEM is
+'保费';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.RATE is
+'费率';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_LIABILITY is
+'保险责任';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.BROKERAGE_SCALE is
+'经纪费比例';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.SALESMAN_BROKERAGE is
+'业务员佣金';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.SALESMAN_ID is
+'业务员工号';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.CREATE_DATE is
+'创建日期';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.INSURE_PEOPLE_MUN is
+'投保人数';
+
+comment on column ERP_INSURE_PROJECT_SHADOW.REMARK is
+'备注';
+
/*==============================================================*/
/* Table: ERP_ACCEPT_INSURANCE */
@@ -347,6 +447,7 @@ create table ERP_INSUR_ENDORSEMENT
REFUND_MONEY NUMBER(20,2),
END_PAYMENT VARCHAR2(30),
END_PAYMENT_ORDER VARCHAR2(30),
+ END_PAYMENT_PERIOD VARCHAR2(30),
constraint PK_ERP_INSUR_ENDORSEMENT primary key (ID)
);
@@ -401,6 +502,9 @@ comment on column ERP_INSUR_ENDORSEMENT.END_PAYMENT is
comment on column ERP_INSUR_ENDORSEMENT.END_PAYMENT_ORDER is
'支付顺序';
+comment on column ERP_INSUR_ENDORSEMENT.END_PAYMENT_PERIOD is
+'支付顺序';
+
/*==============================================================*/
/* Table: ERP_CUSTOM_CAR */
/*==============================================================*/
@@ -469,7 +573,7 @@ create table ERP_CAR_SHADOW
ID NUMBER(20) not null,
ACCEPT_INSURANCE_ID NUMBER(20),
POLICY_ID NUMBER(20),
- CUSTOM_TYPE VARCHAR2(30)，
+ CUSTOM_TYPE VARCHAR2(30),
CUSTOM_ID NUMBER(20),
CUSTOM_NUMBER VARCHAR2(30),
CUSTOM_NAME VARCHAR2(50),
@@ -1102,7 +1206,7 @@ create table ERP_INSUREMPLOYEE_SHADOW
BANK VARCHAR2(30) not null,
BANK_CARD_CODE VARCHAR2(20) not null,
COMPANY_NAME VARCHAR2(40) not null,
- BRANCH_ID NUMBER(30) not null,
+ BRANCH_ID NUMBER(30),
PERSONAL_ID NUMBER(30),
COVER_ID NUMBER(20),
POLICY_ID NUMBER(20),
@@ -1132,6 +1236,7 @@ create table ERP_INSUREMPLOYEE_SHADOW
OTHER_PAY_RATE NUMBER(10,3),
DOC_STATE CHAR(1),
DOC_ADDRESS VARCHAR2(50),
+ REMARK VARCHAR2(400),
constraint PK_ERP_INSUREMPLOYEE_SHADOW primary key (ID)
);
@@ -1258,6 +1363,9 @@ comment on column ERP_INSUREMPLOYEE_SHADOW.DOC_STATE is
comment on column ERP_INSUREMPLOYEE_SHADOW.DOC_ADDRESS is
'医保属地';
+comment on column ERP_INSUREMPLOYEE_SHADOW.REMARK is
+'备注';
+
/*==============================================================*/
/* Table: ERP_EMPLOYEE_INSURETYPE_SHADOW */
@@ -1462,10 +1570,11 @@ comment on column ERP_INSURPOLICY_SHADOW.INSURANCE_TYPE_CATEGORY is
create table ERP_INSUR_DUTY_SHADOW
(
ID NUMBER(20) not null,
- DUTY_NAME VARCHAR2(30) not null,
+ DUTY_NAME VARCHAR2(30),
POLICY_ID NUMBER(20),
INSURE_ID NUMBER(20),
ITEM_ID NUMBER(20),
+ ITEM_NAME VARCHAR2(30),
DUTY_DESCRIPTION VARCHAR2(100),
B_INSURANCE_MONEY NUMBER(10,3),
B_PRENUIM NUMBER(10,3),
@@ -1500,6 +1609,9 @@ comment on column ERP_INSUR_DUTY_SHADOW.INSURE_ID is
comment on column ERP_INSUR_DUTY_SHADOW.ITEM_ID is
'项目ID';
+comment on column ERP_INSUR_DUTY_SHADOW.ITEM_NAME is
+'项目名称';
+
comment on column ERP_INSUR_DUTY_SHADOW.DUTY_DESCRIPTION is
'责任描述';
@@ -1644,9 +1756,9 @@ create table ERP_INSURANCE_POLICY
POLICY_INCEPTION_DATE TIMESTAMP not null,
POLICY_OVER_DATE TIMESTAMP not null,
UNDERWRITE_TOTAL NUMBER(10) not null,
- GNPI NUMBER(10,3),
- TOTAL_BROKERAGE NUMBER(10,3),
- SUM_TOTAL NUMBER(10,3),
+ GNPI NUMBER(20,3),
+ TOTAL_BROKERAGE NUMBER(20,3),
+ SUM_TOTAL NUMBER(20,3),
POLICY_STATE CHAR(1),
BUSINESS_NUMBER VARCHAR2(20),
PUBLIC_SUM NUMBER(10,3),
diff --git a/joying-common-core/src/main/resources/sqls/erp/drop.sql b/joying-common-core/src/main/resources/sqls/erp/drop.sql
index 19ed462..b273993 100644
--- a/joying-common-core/src/main/resources/sqls/erp/drop.sql
+++ b/joying-common-core/src/main/resources/sqls/erp/drop.sql
@@ -38,7 +38,9 @@ drop table ERP_INSURE cascade constraints;
drop table ERP_ACCEPT_INSURANCE cascade constraints;
drop table ERP_REPPEAR_ENDORSEMENT cascade constraints;
drop table ERP_INSURE_PROJECT cascade constraints;
+drop table ERP_INSURE_PROJECT_SHADOW cascade constraints;
+drop sequence SEQ_INSURE_PROJECT_SHADOW;
drop sequence SEQ_ERP_INSURE_PROJECT;
drop sequence SEQ_ERP_ACCEPT_I;
drop sequence SEQ_INSURE;
@@ -50,6 +52,7 @@ drop sequence SEQ_INSUR_POLICY_SHADOW;
drop sequence SEQ_EMPLOYEE_SHADOW;
drop sequence SEQ_ERP_EMPLOYEE_DUTY;
drop sequence SEQ_CUSTOM_CAR;
+drop sequence SEQ_ERP_CAR_SHADOW;
drop sequence SEQ_RECORD;
drop sequence SEQ_REPPEAR_ENDORSEMENT;
drop sequence SEQ_INSUR_ADDITION;
diff --git a/joying-common-core/src/main/resources/sqls/market/create.sql b/joying-common-core/src/main/resources/sqls/market/create.sql
index ad23cb9..271f04a 100644
--- a/joying-common-core/src/main/resources/sqls/market/create.sql
+++ b/joying-common-core/src/main/resources/sqls/market/create.sql
@@ -266,10 +266,10 @@ comment on column MK_CUSTOMER_ENTERPRISE.BUSINESS_NUMBER is
create table MK_CUSTOMER_PERSONAL
(
ID NUMBER(20) not null,
- CUSTOMER_CODE VARCHAR2(99 CHAR) not null deferrable,
+ CUSTOMER_CODE VARCHAR2(99 CHAR) not null,
EMPLOYEE_CODE VARCHAR2(99 CHAR),
OLD_CODE VARCHAR2(99 CHAR),
- NAME VARCHAR2(30 CHAR) not null deferrable,
+ NAME VARCHAR2(30 CHAR) not null,
OLD_NAME VARCHAR2(30 CHAR),
BIRTHDAY DATE,
GENDER CHAR(1),
diff --git a/joying-common-core/src/main/resources/sqls/oa/create.sql b/joying-common-core/src/main/resources/sqls/oa/create.sql
index cefe55a..547d3d0 100644
--- a/joying-common-core/src/main/resources/sqls/oa/create.sql
+++ b/joying-common-core/src/main/resources/sqls/oa/create.sql
@@ -4,7 +4,6 @@ create sequence SEQ_CACHET_APPLY;
create sequence SEQ_CAR_INFO;
-
create sequence SEQ_OA_ANNOUNCEMENT;
create sequence SEQ_OA_CACHET;
@@ -71,7 +70,7 @@ comment on column OA_ANNOUNCEMENT.RELEASE_SCOPE is
'发布范围';
comment on column OA_ANNOUNCEMENT.EFFECT_DATE is
-'发布时间';
+'生效时间';
comment on column OA_ANNOUNCEMENT.CLOSE_DATE is
'终止时间';
@@ -201,7 +200,8 @@ create table OA_CAR_APPLY
DESTINATION VARCHAR2(64),
KILOMETER NUMBER(5,1),
REMARK VARCHAR2(1000),
- END_TIME TIMESTAMP,
+ START_TIME TIMESTAMP not null,
+ END_TIME TIMESTAMP not null,
constraint PK_OA_CAR_APPLY primary key (ID)
);
@@ -238,6 +238,9 @@ comment on column OA_CAR_APPLY.KILOMETER is
comment on column OA_CAR_APPLY.REMARK is
'备注';
+comment on column OA_CAR_APPLY.START_TIME is
+'开始时间';
+
comment on column OA_CAR_APPLY.END_TIME is
'结束时间';
@@ -537,7 +540,6 @@ create table OA_OFFICAL_APPLY_PERSONAL
APPLY_STATE VARCHAR2(20),
REMARK varchar2(1000),
ORG_ID NUMBER(20) not null,
- PROCESS_INSTANCE_ID VARCHAR2(64),
constraint PK_OA_OFFICAL_APPLY_PERSONAL primary key (ID)
);
@@ -571,9 +573,6 @@ comment on column OA_OFFICAL_APPLY_PERSONAL.REMARK is
comment on column OA_OFFICAL_APPLY_PERSONAL.ORG_ID is
'部门ID';
-comment on column OA_OFFICAL_APPLY_PERSONAL.PROCESS_INSTANCE_ID is
-'流程ID';
-
/*==============================================================*/
/* Table: OA_OFFICE_SUPPLIES */
/*==============================================================*/
@@ -617,7 +616,6 @@ comment on column OA_OFFICE_SUPPLIES.REFERENCE_UNIT is
comment on column OA_OFFICE_SUPPLIES.LAST_BUY_DATE is
'最后购入日期';
-
/*==============================================================*/
/* Table: OA_OUT */
/*==============================================================*/
@@ -740,15 +738,14 @@ comment on column OA_OVERTIME.OVERTIME_SUBJECT is
comment on column OA_OVERTIME.CHECK_STATE is
'审核状态';
-
/*==============================================================*/
/* Table: OA_ROLL_MACHINE_DATA */
/*==============================================================*/
create table OA_ROLL_MACHINE_DATA
(
ID NUMBER(30) not null,
- USER_ID NUMBER(20),
- USER_NAME VARCHAR(20),
+ USER_ID NUMBER(20) not null,
+ USER_NAME VARCHAR(20) not null,
GET_ON_TIME TIMESTAMP,
GET_OFF_TIME TIMESTAMP,
SYN_TIME TIMESTAMP,
@@ -758,21 +755,37 @@ create table OA_ROLL_MACHINE_DATA
comment on table OA_ROLL_MACHINE_DATA is
'考勤机数据';
+comment on column OA_ROLL_MACHINE_DATA.ID is
+'ID';
+
+comment on column OA_ROLL_MACHINE_DATA.USER_ID is
+'工号';
+
+comment on column OA_ROLL_MACHINE_DATA.USER_NAME is
+'姓名';
+
+comment on column OA_ROLL_MACHINE_DATA.GET_ON_TIME is
+'上班时间';
+
+comment on column OA_ROLL_MACHINE_DATA.GET_OFF_TIME is
+'下班时间';
+
+comment on column OA_ROLL_MACHINE_DATA.SYN_TIME is
+'同步时间';
+
/*==============================================================*/
/* Table: OA_SUPPLIES_PURCHASE */
/*==============================================================*/
create table OA_SUPPLIES_PURCHASE
(
ID NUMBER(20) not null,
- PROCESS_INSTANCE_ID VARCHAR2(64),
SUPPLIES_ID NUMBER(20) not null,
REFERENCE_UNIT NUMBER(6,2),
- APPLY_AMOUNT NUMBER(6,2),
AMOUNT NUMBER(6,2),
UNIT_PRICE NUMBER(6,2),
PURCHASE_COUNT NUMBER(20),
+ PURCHASE_DATE DATE,
TOTAL NUMBER(10,2),
- PURCHASE_DATE TIMESTAMP,
RECEIPT_NUM VARCHAR2(40),
constraint PK_OA_SUPPLIES_PURCHASE primary key (ID)
);
@@ -783,18 +796,12 @@ comment on table OA_SUPPLIES_PURCHASE is
comment on column OA_SUPPLIES_PURCHASE.ID is
'主键';
-comment on column OA_SUPPLIES_PURCHASE.PROCESS_INSTANCE_ID is
-'流程ID';
-
comment on column OA_SUPPLIES_PURCHASE.SUPPLIES_ID is
'办公用品ID';
comment on column OA_SUPPLIES_PURCHASE.REFERENCE_UNIT is
'参考单价';
-comment on column OA_SUPPLIES_PURCHASE.APPLY_AMOUNT is
-'申请数量';
-
comment on column OA_SUPPLIES_PURCHASE.AMOUNT is
'当前库存';
@@ -804,12 +811,12 @@ comment on column OA_SUPPLIES_PURCHASE.UNIT_PRICE is
comment on column OA_SUPPLIES_PURCHASE.PURCHASE_COUNT is
'购入数量';
-comment on column OA_SUPPLIES_PURCHASE.TOTAL is
-'总额';
-
comment on column OA_SUPPLIES_PURCHASE.PURCHASE_DATE is
'购入日期';
+comment on column OA_SUPPLIES_PURCHASE.TOTAL is
+'总额';
+
comment on column OA_SUPPLIES_PURCHASE.RECEIPT_NUM is
'发票编号';