v5升级

最新推荐文章于 2024-10-11 17:04:33 发布

cok280628

最新推荐文章于 2024-10-11 17:04:33 发布

阅读量168

点赞数

文章标签：数据库

-- Create table

create table T_TRD_PLY_TAX_BASE_PART(

c_crt_cde VARCHAR2(30) default USER,

t_crt_tm DATE default SYSDATE,

c_opr_typ VARCHAR2(1),

c_qry_cde VARCHAR2(50) not null,

query_sequence_no VARCHAR2(50) not null,

c_confirm_cde VARCHAR2(50),

tax_payer_id VARCHAR2(50),

tax_pay_type VARCHAR2(3),

tax_abate_amount NUMBER(14,2),

tax_abate_reason VARCHAR2(3),

tax_voucher VARCHAR2(50),

tax_comcode VARCHAR2(20),

tax_comname VARCHAR2(256),

tax_calc_flag VARCHAR2(3),

tax_this_pay NUMBER(14,2),

tax_after_pay NUMBER(14,2),

tax_delay_pay NUMBER(14,2),

tax_sum_amount NUMBER(14,2)

) tablespace ABSYS_DATA_18;

-- Add comments to the columns

comment on column T_TRD_PLY_TAX_BASE_PART.tax_payer_id is '纳税人识别号';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_pay_type is '纳税类型';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_abate_amount is '减税金额';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_abate_reason is '减免税原因';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_voucher is '完税凭证号/减免税证明号';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_comcode is '开具税务机关代码';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_comname is '开具税务机关名称';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_calc_flag is '是否平台计算税费金额';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_this_pay is '本年应缴税额';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_after_pay is '往年补缴税额';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_delay_pay is '滞纳金';

comment on column T_TRD_PLY_TAX_BASE_PART.tax_sum_amount is '合计';

-- Create table

create table T_TRD_TMP_PLY_TAX_BASE_PART(

c_crt_cde VARCHAR2(30) default USER,

t_crt_tm DATE default SYSDATE,

c_qry_cde VARCHAR2(50) not null,

query_sequence_no VARCHAR2(50) not null,

car_mark VARCHAR2(15),

vehicle_type VARCHAR2(2),

vehicle_category VARCHAR2(2) not null,

rack_no VARCHAR2(50),

engine_no VARCHAR2(50),

seat_count NUMBER(4),

curl_weight NUMBER(8),

tax_area_code VARCHAR2(12),

tax_item_code VARCHAR2(4),

tax_payer_id VARCHAR2(18),

tax_payer_name VARCHAR2(256),

tax_payer_code VARCHAR2(22),

tax_pay_date DATE,

tax_pay_type VARCHAR2(3),

tax_abate_amount NUMBER(14,2),

tax_abate_reason VARCHAR2(4),

tax_voucher VARCHAR2(50),

tax_comcode VARCHAR2(20),

tax_comname VARCHAR2(256),

tax_calc_flag VARCHAR2(3),

tax_this_pay NUMBER(14,2),

tax_after_pay NUMBER(14,2),

tax_delay_pay NUMBER(14,2)

) tablespace ABSYS_DATA_19;

-- Add comments to the columns

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.c_qry_cde is '投保查询码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.query_sequence_no is '投保查询码,为返回组装SQL使用';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.car_mark is '车牌号';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.vehicle_type is '号牌种类代码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.vehicle_category is '车辆种类代码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.rack_no is '车架号/VIN码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.engine_no is '发动机号';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.seat_count is '核定载客数量非客车送0 ';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.curl_weight is '整备质量(kg)';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_area_code is '税目地区';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_item_code is '税目代码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_payer_id is '纳税人识别号';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_payer_name is '纳税人名称或单位名称';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_payer_code is '纳税人身份证号码/组织机构代码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_pay_date is '纳税日期';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_pay_type is '纳税类型';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_abate_amount is '减税金额';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_abate_reason is '减免税原因';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_voucher is '完税凭证号/减免税证明号';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_comcode is '开具税务机关代码';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_comname is '开具税务机关名称';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_calc_flag is '是否平台计算税费金额';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_this_pay is '本年应缴税额';

comment on column T_TRD_TMP_PLY_TAX_BASE_PART.tax_after_pay is '往年补缴税额';

-- Grant/Revoke object privileges

grant select on T_TRD_TMP_PLY_TAX_BASE_PART to ABLOOK;

ALTER TABLE T_TRD_EDR_VEHICLEMODEL ADD REFCODE1 VARCHAR2(15);

ALTER TABLE T_TRD_EDR_VEHICLEMODEL ADD REFCODE2 VARCHAR2(15);

ALTER TABLE T_TRD_EDR_VEHICLE_PRICE_LIST ADD IS_PRICED VARCHAR2(1);

ALTER TABLE T_TRD_EDR_VEHICLE_PRICE_LIST ADD RISK_FLAG VARCHAR2(1);

ALTER TABLE T_TRD_PLY_PECC_LIST ADD PECCANCY_ADJUST_VALUE NUMBER(4,2);

ALTER TABLE T_TRD_PLY_VEHICLEMODEL ADD REFCODE1 VARCHAR2(15);

ALTER TABLE T_TRD_PLY_VEHICLEMODEL ADD REFCODE2 VARCHAR2(15);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD AGENCY_NAME VARCHAR2(100);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD BUSINESS_CHANNEL VARCHAR2(2);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD AGENCY_CODE VARCHAR2(50);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD LASTPOLICYNO VARCHAR2(50);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD COMPUTER_IP VARCHAR2(50);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD USBKEY VARCHAR2(50);

ALTER TABLE T_TRD_TMP_PLY_BASE_PART ADD POS_NO VARCHAR2(50);

ALTER TABLE T_TRD_TMP_PLY_COVERAGE_LIST ADD OWNER VARCHAR2(256);

alter table T_COMM_CODE1 modify C_DPT_CODE VARCHAR2(200);

drop table t_netfee_percent;

create table t_netfee_percent

(

C_DPT_CDE VARCHAR2(10) not null,

N_DRINK_PERCENT NUMBER(3,2) not null,

N_DRUNK_PERCENT NUMBER(3,2) not null

) tablespace ABSYS_DATA_20;

CREATE TABLE t_car_area(C_DPT_CDE VARCHAR2(11) , C_CAR_NAME VARCHAR2(10) NOT NULL, C_PROPERTY VARCHAR2(2)) tablespace ABSYS_DATA_19;

create table T_BASE_CHAN_BUSI

(

T_CRT_DATE DATE,

C_CRT_CDE VARCHAR2(30),

T_UPD_DATE DATE,

C_UPD_CDE VARCHAR2(30),

C_DPT_CDE VARCHAR2(30),

C_DPT_CNM VARCHAR2(100),

C_AGNT_CDE VARCHAR2(30),

C_DIVI_CDE VARCHAR2(30),

C_GRP_CNM VARCHAR2(100),

C_DVLP_CDE VARCHAR2(30),

C_DVLP_NME VARCHAR2(100),

C_DVLP_ID VARCHAR2(40),

C_CHAN_CDE VARCHAR2(30),

C_CHAN_NME VARCHAR2(100),

C_CHAN_ID VARCHAR2(40),

C_BRAND VARCHAR2(50),

C_4S_BUSI VARCHAR2(50),

C_4S_TEL VARCHAR2(30),

C_MOBILE VARCHAR2(20),

T_STARTDATE DATE,

T_ENDDATE DATE,

C_AUTH_MRK VARCHAR2(10),

C_AGENT1 VARCHAR2(50) ,

C_AGENT2 VARCHAR2(50) ,

C_AGENT3 VARCHAR2(50) ,

C_LICENCE1 VARCHAR2(30) ,

C_LICENCE2 VARCHAR2(30) ,

C_LICENCE3 VARCHAR2(30) ,

C_ERRCNM VARCHAR2(100) ,

C_COMPNY VARCHAR2(100)

) tablespace ABSYS_DATA_18;

----保单和维护人的关联信息表

Create table T_PLY_CHAN_BUSI (

T_CRT_DATE DATE, --**创建时间

C_CRT_CDE VARCHAR2(30), --**创建人员

T_UPD_DATE DATE, --**修改时间

C_UPD_CDE VARCHAR2(30), --**修改人员

C_DPT_CDE VARCHAR2(30), --**出单口编号

C_CHAN_CDE VARCHAR2(30), --**员工编号

C_CHAN_NME VARCHAR2(100), --**人员姓名

C_CHAN_ID VARCHAR2(40), --**员工身份证

C_DIVI_CDE VARCHAR2(30), --**所属事业部

C_GRP_CNM VARCHAR2(100), --**所属集团

C_BRAND VARCHAR2(50), --**经营品牌

C_PLY_NO VARCHAR2(30), ---保单号

C_INSRNT_CNM VARCHAR2(100) ---被保险人姓名

) tablespace ABSYS_DATA_20;

create table T_PLY_USB_KEY

(

C_DOC_NO VARCHAR2(50) not null,

C_USB_HEY VARCHAR2(50),

C_COM_IP VARCHAR2(20),

C_PROD_NO VARCHAR2(10),

C_BSNS_TYP VARCHAR2(10),

C_DESC VARCHAR2(100),

primary key (C_DOC_NO)

) tablespace ABSYS_DATA_19;

comment on column T_PLY_USB_KEY.C_DOC_NO

is '单证号';

comment on column T_PLY_USB_KEY.C_USB_HEY

is 'UsbKey值';

comment on column T_PLY_USB_KEY.C_COM_IP

is '中介报备IP';

comment on column T_PLY_USB_KEY.C_PROD_NO

is '产品号';

comment on column T_PLY_USB_KEY.C_BSNS_TYP

is '业务来源';

comment on column T_PLY_USB_KEY.C_DESC

is '描述';

ALTER TABLE T_EDR_TGT ADD

(

C_CARD_USETP VARCHAR2(1),

NO_DAMAGE_YEARS NUMBER(2),

N_TGT_FLD26 NUMBER(20,6),

N_TGT_FLD27 NUMBER(20,6),

N_TGT_FLD28 NUMBER(20,6),

N_TGT_FLD29 NUMBER(20,6),

N_TGT_FLD30 NUMBER(20,6),

N_TGT_FLD31 NUMBER(20,6),

N_TGT_FLD32 NUMBER(20,6));

ALTER TABLE T_EDR_VHL ADD

(

C_TRANS_TYP VARCHAR2(20),

NO_DAMAGE_YEARS NUMBER(2));

ALTER TABLE T_PLY_TGT ADD

(

C_CARD_USETP VARCHAR2(1),

NO_DAMAGE_YEARS NUMBER(2),

N_TGT_FLD26 NUMBER(20,6),

N_TGT_FLD27 NUMBER(20,6),

N_TGT_FLD28 NUMBER(20,6),

N_TGT_FLD29 NUMBER(20,6),

N_TGT_FLD30 NUMBER(20,6),

N_TGT_FLD31 NUMBER(20,6),

N_TGT_FLD32 NUMBER(20,6));

ALTER TABLE T_PLY_VHL ADD

(

C_TRANS_TYP VARCHAR2(20),

NO_DAMAGE_YEARS NUMBER(2));

ALTER TABLE t_fin_plyedr_coldue ADD (C_BANK_CODE VARCHAR2(50) );

------------------------------------------------

--V5升级处理

ALTER TABLE t_fin_plyedr_coldue ADD (c_rp_flag char(1) );

CREATE OR REPLACE VIEW V_FIN_SENDPACKET (c_prod_no, c_ply_no, c_dpt_cde, c_edr_no, c_packet, c_cav_no)

--根据查询码判断是否上传平台

select c.c_prod_no,

c.c_ply_no,

c.c_dpt_cde,

nvl(c.c_edr_no, chr(0)),

(CASE

WHEN length(c.c_edr_no) > 10 THEN

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_edr_no = RTRIM(c.c_edr_no))

ELSE

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_ply_no = RTRIM(c.c_ply_no))

END),

b.c_cav_no

from t_fin_plyedr_coldue c, t_fin_cavdoc b

where b.c_rcpt_no = c.c_rcpt_no

and c.n_get_prm > 0

and b.c_prod_no like '03%'

and nvl(c.c_opt_no, '123') <> 'EXCEPT'

and length((CASE WHEN length(c.c_edr_no) > 10 THEN

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_edr_no = RTRIM(c.c_edr_no)) ELSE

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_ply_no = RTRIM(c.c_ply_no)) END)) > 100;

--and b.c_cav_no='20110118110000010001'

-- Create table

drop table t_FIN_RPE_DOC;

create table t_FIN_RPE_DOC

(

C_CAV_NO VARCHAR2(50) not null,

C_ITEM_NO VARCHAR2(4) not null,

C_RCPT_NO VARCHAR2(50),

C_PLY_NO VARCHAR2(50),

C_EDR_NO VARCHAR2(50),

C_APP_PLY_NO VARCHAR2(50) not null,

N_TMS NUMBER(4) default 0 not null,

C_CUR_CDE VARCHAR2(30) default '01' not null,

N_GET_PRM NUMBER(20,2) default 0 not null,

N_OTHER_AMT NUMBER(20,2) default 0 not null,

N_YH_AMT NUMBER(20,2) default 0 not null,

C_PROD_NO VARCHAR2(4) not null,

C_DPT_CDE VARCHAR2(30) not null,

C_CONFIRM_SEQUENCE_NO VARCHAR2(30),

C_CONFIRM_SEQUENCE_FLAG CHAR(1),

C_APP_TYP CHAR(1),

C_IS_VALID CHAR(1) default '1',

T_UPD_TM DATE,

C_TABLE_FLAG VARCHAR2(2),

C_ACCNT_FLAG CHAR(2) default '00',

C_MEMO VARCHAR2(2000),

C_APP_NME VARCHAR2(255),

C_DOC_STATUS VARCHAR2(1) default '0'

) tablespace ABSYS_DATA_18;

-- Create/Recreate primary, unique and foreign key constraints

alter table t_FIN_RPE_DOC

add constraint PK_t_FIN_RPE_DOC primary key (C_CAV_NO, C_ITEM_NO)

using index tablespace ABSYS_INDEX_08;

-- Create/Recreate indexes

create index I_FIN_RPE_DOC_APP_NO on t_FIN_RPE_DOC (C_APP_PLY_NO)

tablespace ABSYS_INDEX_10;

create index I_FIN_RPE_DOC_PLY_NO on t_FIN_RPE_DOC (C_PLY_NO)

tablespace ABSYS_INDEX_08;

create index I_FIN_RPE_DOC_RCPT_NO on t_FIN_RPE_DOC (C_RCPT_NO)

tablespace ABSYS_INDEX_10;

-- Grant/Revoke object privileges

grant insert, update, delete on t_FIN_RPE_DOC to VHL_OPER;

-- Create table

drop table t_FIN_RPE_DUE;

create table t_FIN_RPE_DUE

(

C_CAV_NO VARCHAR2(50) not null,

C_REQUEST_TYPE VARCHAR2(30) default '0',

C_RETURN_MEMO VARCHAR2(255),

C_RETURN_FLAG VARCHAR2(2) default '1',

C_DPTACC_CDE VARCHAR2(30) not null,

N_SUM_AMT NUMBER(20,2) default 0 not null,

C_CUR_CDE VARCHAR2(30) default '01',

C_OPER_CDE VARCHAR2(30) not null,

C_OPER_CNM VARCHAR2(255) not null,

C_EMP_CDE VARCHAR2(30),

C_DOC_FLAG VARCHAR2(30),

C_BAL_TYPE VARCHAR2(30) not null,

C_ICAV_NO VARCHAR2(50),

C_BANK_NO VARCHAR2(30),

C_COMPANY_ACCOUNTS VARCHAR2(50),

C_BFN_NO VARCHAR2(30),

C_POS_NO VARCHAR2(30),

C_SHOP_ID VARCHAR2(30),

T_ROLL_TM DATE,

T_CAV_TM DATE not null,

C_CUSTOMER_ACCOUNTS VARCHAR2(50),

C_CUSTBANK_NO VARCHAR2(30),

C_REF_CDE VARCHAR2(30),

C_CHECK_CDE VARCHAR2(30),

T_CHECK_TM DATE,

C_UPD_CDE VARCHAR2(30),

T_UPD_TM DATE,

C_BANK_CDE VARCHAR2(30) not null,

C_BANK_ACCOUNTS VARCHAR2(255),

C_PROVINCES VARCHAR2(255),

C_CITY VARCHAR2(255),

C_CHECK_NAME VARCHAR2(255),

C_IS_VALID CHAR(1) default '1',

C_ACCNT_FLAG VARCHAR2(2) default '00',

C_COMPARE_TYPE CHAR(1),

C_HEAD_FLAG CHAR(1) default '0',

C_COMPARE_MEMO VARCHAR2(255),

T_QUERY_TM DATE,

C_HEAD_CAV_NO VARCHAR2(50),

C_CUSTBANK_NAME VARCHAR2(255),

C_DPT_CDE VARCHAR2(50),

T_CRT_TM DATE default SYSDATE

) tablespace ABSYS_DATA_18;

-- Add comments to the table

comment on table t_FIN_RPE_DUE

is '用来记录外围预投保系统收款信息的详细信息。

记录持卡人信息；交易信息；撤销信息；

-- Add comments to the columns

comment on column t_FIN_RPE_DUE.C_REQUEST_TYPE

is '01 订单缴费

02 订单撤销(暂不做)

03 通知保单确认

04 通知订单撤销(暂不做)

comment on column t_FIN_RPE_DUE.C_RETURN_FLAG

is '0 成功

1失败

comment on column t_FIN_RPE_DUE.C_DPTACC_CDE

is '根据出单口配置获取';

comment on column t_FIN_RPE_DUE.N_SUM_AMT

is '保费+车船税+滞纳金+印花税';

comment on column t_FIN_RPE_DUE.C_OPER_CNM

is '客户名称';

comment on column t_FIN_RPE_DUE.C_EMP_CDE

is '我公司员工';

comment on column t_FIN_RPE_DUE.C_DOC_FLAG

is '销售渠道';

comment on column t_FIN_RPE_DUE.C_BAL_TYPE

is '002010网银

002011 POS

comment on column t_FIN_RPE_DUE.C_ICAV_NO

is '银行认可的订单号';

comment on column t_FIN_RPE_DUE.C_BANK_NO

is '由于合作银行少，可以先写死

905 中国银联股份有限公司

308 招商银行

comment on column t_FIN_RPE_DUE.C_COMPANY_ACCOUNTS

is '根据配置获取';

comment on column t_FIN_RPE_DUE.C_BFN_NO

is 'Pos机需要记录';

comment on column t_FIN_RPE_DUE.C_POS_NO

is 'Pos机需要记录';

comment on column t_FIN_RPE_DUE.C_SHOP_ID

is 'Pos机需要记录';

comment on column t_FIN_RPE_DUE.T_ROLL_TM

is '实际撤销日期

YYYY-MM-DD HH24:MI:SS

comment on column t_FIN_RPE_DUE.T_CAV_TM

is '实际收款日期

YYYY-MM-DD HH24:MI:SS

comment on column t_FIN_RPE_DUE.C_CUSTBANK_NO

is '由于合作银行少，可以先写死

905 中国银联股份有限公司

308 招商银行

-- Create/Recreate primary, unique and foreign key constraints

alter table t_FIN_RPE_DUE

add constraint t_FIN_RPE_DUE_PK primary key (C_CAV_NO)

using index tablespace ABSYS_INDEX_10;

-- Create/Recreate indexes

create index IDX_FINRPEDUE_DPT on t_FIN_RPE_DUE (C_DPT_CDE)

tablespace ABSYS_INDEX_10;

create index IDX_FINRPEDUE_DPTACC on t_FIN_RPE_DUE (C_DPTACC_CDE)

tablespace ABSYS_INDEX_08;

create index I_FIN_RPE_DUE_CAV on t_FIN_RPE_DUE (T_CAV_TM)

tablespace ABSYS_INDEX_10;

create index I_FIN_RPE_DUE_CHECK on t_FIN_RPE_DUE (T_CHECK_TM)

tablespace ABSYS_INDEX_08;

create index I_FIN_RPE_DUE_CRT on t_FIN_RPE_DUE (T_CRT_TM)

tablespace ABSYS_INDEX_10 ;

create index I_FIN_RPE_DUE_HEAD_CAV on t_FIN_RPE_DUE (C_HEAD_CAV_NO)

tablespace ABSYS_INDEX_08 ;

create index I_FIN_RPE_DUE_ICAV on t_FIN_RPE_DUE (C_ICAV_NO)

tablespace ABSYS_INDEX_10;

-- Grant/Revoke object privileges

grant insert, update, delete on t_FIN_RPE_DUE to VHL_OPER;

ALTER TABLE t_fin_rpe_due

ADD (C_Receive_no varchar2(30) );

--根据订单连平台

CREATE OR REPLACE VIEW V_FIN_SENDPACKET_ORDER

(c_prod_no, c_ply_no, c_dpt_cde, c_edr_no, c_packet, c_cav_no,c_rcpt_no)

select c.c_prod_no,

c.c_ply_no,

c.c_dpt_cde,

nvl(c.c_edr_no, chr(0)),

(CASE

WHEN length(c.c_edr_no) > 10 THEN

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_edr_no = RTRIM(c.c_edr_no))

ELSE

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_ply_no = RTRIM(c.c_ply_no))

END),

b.c_cav_no,c.c_rcpt_no

from t_fin_plyedr_coldue c, t_fin_rpe_doc b

where b.c_rcpt_no = c.c_rcpt_no

and c.n_get_prm > 0

and b.c_prod_no like '03%'

and nvl(c.c_opt_no, '123') <> 'EXCEPT'

and length((CASE WHEN length(c.c_edr_no) > 10 THEN

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_edr_no = RTRIM(c.c_edr_no)) ELSE

(SELECT NVL(c_packet, '')

FROM t_trd_packet

WHERE c_ply_no = RTRIM(c.c_ply_no)) END)) > 100;

--添加自动做账相关字段 2010-07-05

ALTER TABLE t_fin_plyedr_coldue add c_bal_type varchar2(30);

ALTER TABLE t_fin_saveamtdue add c_bal_type varchar2(30);

ALTER TABLE t_fin_credit_due add c_bal_type varchar2(30);

ALTER TABLE t_fin_clmdue add c_bal_type varchar2(30);

ALTER TABLE t_fin_paydue add c_bal_type varchar2(30);

ALTER TABLE t_fin_plyedr_coldue add c_cav_no varchar2(30);

ALTER TABLE t_fin_saveamtdue add c_cav_no varchar2(30);

ALTER TABLE t_fin_credit_due add c_cav_no varchar2(30);

ALTER TABLE t_fin_clmdue add c_cav_no varchar2(30);

ALTER TABLE t_fin_paydue add c_cav_no varchar2(30);

ALTER TABLE t_fin_plyedr_coldue add c_bank_cde varchar2(30);

ALTER TABLE t_fin_saveamtdue add c_bank_cde varchar2(30);

ALTER TABLE t_fin_credit_due add c_bank_cde varchar2(30);

ALTER TABLE t_fin_clmdue add c_bank_cde varchar2(30);

ALTER TABLE t_fin_paydue add c_bank_cde varchar2(30);

ALTER TABLE t_fin_plyedr_coldue add c_customer_accounts varchar2(30);

ALTER TABLE t_fin_saveamtdue add c_customer_accounts varchar2(30);

ALTER TABLE t_fin_credit_due add c_customer_accounts varchar2(30);

ALTER TABLE t_fin_clmdue add c_customer_accounts varchar2(30);

ALTER TABLE t_fin_paydue add c_customer_accounts varchar2(30);

ALTER TABLE t_fin_plyedr_coldue add c_dptacc_cde varchar2(30);

ALTER TABLE t_fin_saveamtdue add c_dptacc_cde varchar2(30);

ALTER TABLE t_fin_credit_due add c_dptacc_cde varchar2(30);

ALTER TABLE t_fin_clmdue add c_dptacc_cde varchar2(30);

ALTER TABLE t_fin_paydue add c_dptacc_cde varchar2(30);

ALTER TABLE t_fin_cavobject add c_end_no varchar2(30);

ALTER TABLE t_fin_premoney add C_MEMO varchar2(255);

ALTER TABLE t_fin_premoney add C_DOC_TYP varchar2(30);

ALTER TABLE t_fin_premoney add c_end_no varchar2(30);

ALTER TABLE t_fin_premoney add C_BATCH_NO varchar2(30);

CREATE OR REPLACE VIEW V_FIN_DUE

(c_due_type, c_ply_no, c_edr_no, c_clm_no, n_tms, c_prod_no, c_bsns_typ, c_slsgrp_cde,

c_sls_cde, c_feetyp_cde, c_clnt_mrk, c_bs_cur, n_bs_amt, t_due_tm,

c_rp_cur, n_rp_amt, t_rp_tm, t_paid_tm, n_paid_amt, c_cha_mrk, c_cha_cls, c_cha_cde,

c_dpt_cde, c_dptacc_no, c_pay_name, c_bank_account, c_app_nme,

c_lcn_no, t_insrnc_bgn_tm, t_insrnc_end_tm, c_tran_flag, c_bala_mrk,

c_rcpt_no, c_opt_no, c_expiration_type, c_lx_flag, n_other_amt, t_upd_tm,

c_upd_cde, t_crt_tm, c_memo, c_accnt_flag, c_batch_no,c_prn_no,c_customer_accounts,c_bank_cde,c_bal_type,c_cav_no)

SELECT '1' AS c_due_type, c_ply_no, c_edr_no, NULL AS c_clm_no,

n_tms, c_prod_no, c_bsns_typ, C_SALEGRP_CDE, c_sls_cde, c_feetyp_cde,

c_clnt_mrk, c_cur_cde, n_get_prm, t_bal_tm,

c_rp_cur, n_rp_amt, t_cav_tm, t_check_tm, n_got_prm,

c_cha_mrk, c_cha_cls, c_cha_cde,

c_dpt_cde, c_dptacc_no, c_pay_name,

c_bank_account, c_app_nme, c_lcn_no, t_insrnc_bgn_tm,

t_insrnc_end_tm, c_tran_flag, c_bala_mrk,

c_rcpt_no, c_opt_no, NULL, NULL, n_other_amt,

t_upd_tm, c_upd_cde, t_crt_tm, c_memo, c_accnt_flag,

c_batch_no,c_prn_no,c_customer_accounts,c_bank_cde,c_bal_type,c_cav_no

FROM T_FIN_PLYEDR_COLDUE

UNION ALL

SELECT '2' AS c_due_type, c_ply_no, NULL AS c_edr_no,

c_clm_no, 1, c_prod_no, c_bsns_typ, C_SALEGRP_CDE, c_sls_cde,

c_feetyp_cde, c_clnt_mrk, c_cur_typ, n_pay_amt,

t_bal_tm, c_rp_cur, n_rp_amt, t_cav_tm, t_check_tm, n_paid_amt,

c_cha_mrk, c_cha_cls, c_cha_cde,

c_dpt_cde, c_dptacc_no,

c_pay_name, c_bank_account, c_app_nme, c_lcn_no,

NULL, NULL, c_tran_flag, c_bala_mrk, c_rcpt_no,

c_opt_no, NULL, NULL, NULL, t_upd_tm, c_upd_cde,

t_crt_tm, c_memo, c_accnt_flag, c_batch_no,c_prn_no,c_customer_accounts,c_bank_cde,c_bal_type,c_cav_no

FROM T_FIN_CLMDUE

UNION ALL

SELECT '3' AS c_due_type, c_ply_no, c_edr_no, c_clm_no, n_tms,

c_prod_no, c_bsns_typ, C_SALEGRP_CDE, c_sls_cde, c_feetyp_cde,

c_clnt_mrk, c_cur_cde, n_pay_amt, t_bal_tm,

c_rp_cur, n_rp_amt, t_cav_tm, t_check_tm, n_paid_amt,

c_cha_mrk, c_cha_cls, c_cha_cde, c_dpt_cde, c_dptacc_no, c_pay_name,

c_bank_account, c_app_nme, c_lcn_no, t_insrnc_bgn_tm,

t_insrnc_end_tm, c_tran_flag, c_bala_mrk,

c_rcpt_no, c_opt_no, NULL, NULL, NULL, t_upd_tm,

c_upd_cde, t_crt_tm, c_memo, c_accnt_flag, c_batch_no,c_prn_no,c_customer_accounts,c_bank_cde,c_bal_type,c_cav_no

FROM T_FIN_PAYDUE

UNION ALL

SELECT '4' AS c_due_type, c_ply_no, c_edr_no, c_clm_no, n_tms,

c_prod_no, c_bsns_typ, C_SALEGRP_CDE, c_sls_cde, c_feetyp_cde,

c_clnt_mrk, c_cur_cde, n_pay_amt, t_bal_tm,

c_rp_cur, n_rp_amt, t_cav_tm, t_check_tm, n_paid_amt,

c_cha_mrk, c_cha_cls, c_cha_cde,c_dpt_cde, c_dptacc_no, c_pay_name,

c_bank_account, c_app_nme, c_lcn_no, t_insrnc_bgn_tm,

t_insrnc_end_tm, c_tran_flag, c_bala_mrk,

c_rcpt_no, c_opt_no, c_expiration_type, c_lx_flag,

n_other_amt, t_upd_tm, c_upd_cde, t_crt_tm,

c_memo, c_accnt_flag, c_batch_no,c_prn_no,c_customer_accounts,c_bank_cde,c_bal_type,c_cav_no

FROM T_FIN_SAVEAMTDUE

UNION ALL

SELECT '5' AS c_due_type, c_ply_no, c_edr_no, c_clm_no, n_tms,

c_prod_no, c_bsns_typ, C_SALEGRP_CDE, c_sls_cde, c_feetyp_cde,

c_clnt_mrk, c_cur_cde, n_pay_amt, t_bal_tm,

c_rp_cur, n_rp_amt, t_cav_tm, t_check_tm, n_paid_amt,

c_cha_mrk, c_cha_cls, c_cha_cde, c_dpt_cde, c_dptacc_no, c_payer_nme,

c_bank_account, c_app_nme, c_lcn_no, t_insrnc_bgn_tm,

t_insrnc_end_tm, c_tran_flag, c_bala_mrk,

c_rcpt_no, c_opt_no, c_expiration_type, c_lx_flag,

n_other_amt, t_upd_tm, c_upd_cde, t_crt_tm,

c_memo, c_accnt_flag, c_batch_no,c_prn_no,c_customer_accounts,c_bank_cde,c_bal_type,c_cav_no

FROM T_FIN_CREDIT_DUE

--2011.2.12

drop table t_fin_pointrepair;

create table t_fin_pointrepair

(

c_rcpt_no varchar2(30 ) not null,

c_lcn_no varchar2(50 ) not null,

C_repairplant_no varchar2(30 ) ,

C_repairplant_name varchar2(50 ) not null,

n_amt varchar2(255 ) not null,

constraint t_fin_pointrepair_pk primary key (c_rcpt_no,c_lcn_no)

) tablespace ABSYS_DATA_19;

在升级的过程中出现了问题，查询了metalink，发现是cdc造成的，也可以理解是bug，按这个操作了，就正常了

Getting ORA-31495 When Attempting A Dml On A Regular Table [ID 1167495.1]

		修改时间 04-AUG-2010 类型 PROBLEM 状态 MODERATED

In this Document

Applies to:

Oracle Server - Enterprise Edition - Version: 11.2.0.1 and later [Release: 11.2 and later ]
Information in this document applies to any platform.

Symptoms

All suddenly you start seeing following error when attempting to do any DML (Insert/Update/Delete) in a regular database user table:

ORA-31495: error in synchronous change table on "".""

Changes

The affected database previously was a source database for a CDC (Change Data Capture) setup.

Note: If the table is still part of a CDC setup, this might be a different issue, please take a look on this another Note 751452.1 "Ora-31495: Error In Synchronous Change Table".

Cause

Affected table is a former source CDC table, so it contains some additional objects like triggers that were wrongly removed for some reason.

You can double check if the affected table is a former CDC source table by using below query:

conn / as sysdba

select owner,object_name,object_type,trigflag from dba_objects d, sys.tab$ t where d.object_id = t.obj# and d.object_name='';

If the query returns '0x00000010' (16 dec) for the 'trigflag' column, it indicates the affected table was part of a CDC setup:

/* 0x00000010 = synchronous change table */ (16 dec)

Solution

As there is a mismatch metadata corruption for the affected table into the RDBMS dictionary underlying tables, the only solution here is to re-create the table.

You can cleanup this by recreating the affected table as follows, for example:

1. Create a dummy table from the original table:

sql> conn /
sql> create table dummy as select * from .;

2. Then drop original table:

sql> drop table .;

3. Then rename dummy table to the original one:

sql> rename dummy to ;

4. Create (if any) all indexes, constraints, etc for the table.