自己学习BCDL 5 ——银行企业账 视图

--企业端
drop view view_auditcorpbook;
create view view_auditcorpbook as
select a.corpunfulid as id ,a.auditdetailid,a.submitdate,a.voucherdate,
a.debitamount,a.creditamount,a.voucherno,a.voucherdetailno,a.digest,
a.oppcrop,a.oppcropno,a.auditno,a.isaudit,a.requtime,
a.resptime,a.oppaccountno,a.isbatch,a.companycode,a.opp_bank_account_num,
a.opp_bank_account_name,a.accountant,-1 as subjectid,a.remark
from cmcs3_audit_corp_unfulfilment a
union
select b.corpbookid as id ,b.auditdetailid,b.submitdate,b.voucherdate,
b.debitamount,b.creditamount,b.voucherno,b.voucherdetailno,b.digest,
b.oppcrop,b.oppcropno,b.auditno,b.isaudit,b.requtime,
b.resptime,b.oppaccountno,b.isbatch,b.companycode,b.opp_bank_account_num,
b.opp_bank_account_name,b.accountant,b.subjectid as subjectid,b.remark
from cmcs3_audit_corp_book b;

 

--银行端
drop view view_auditbankbook;
create view view_auditbankbook as
select a.bankunfulid as id ,a.auditdetailid,a.tradetime,a.debitamount,
a.creditamount,a.digest,a.oppaccountno,a.oppaccountname,a.oppbracth,
a.auditno,a.isaudit,-1 as accountid,a.pre_auditstate as pre_auditstate ,a.remark
from cmcs3_audit_bank_unfulfilment a
union
select b.bankbookid as id ,b.auditdetailid,b.tradetime,b.debitamount,
b.creditamount,b.digest,b.oppaccountno,b.oppaccountname,b.oppbracth,
b.auditno,b.isaudit,b.accountid as accountid,0 as pre_auditstate ,b.remark
from cmcs3_audit_bank_book b;
insert into cmbbcd.CMCS3_IO_FILE_FORMAT (FILEFORMATID, FILEID, FIELDCODE, FIELDNAME, FIELDTYPE, ISFIELDNULL, FIELDFORMAT, SHEETFIELD)
values (187, 101, 'bankNum', '行号', 1, 1, null, 'M');