EAS 脚本处理

---常用的数据表

 费报:T_BC_BizAccountBill; 4A44F49F
差报: T_BC_TravelAccountBill; C57003BC
支付: CT_LQG_PayApplyBill; A054305A
借款: T_BC_DailyLoanBill; 8110AAB2
列账单: CT_LQG_DisplayBill; E54C0ABC
内部往来支付: CT_LQG_IntraPayBill; D001701E
合同结算单:T_EC_ContractSettleBill
凭证生成报告表头: CT_VOU_VoucherReport;
报告分录: CT_VOU_VoucherReportEntry;
业务系统设置表: CT_LQG_BusinessSysSet;
凭证分录: T_GL_VoucherEntry
工作流程表: t_wfr_assign;
共享任务池表:
付款单：T_CAS_PaymentBill

用户权限表：

T_PM_UserOrgPerm

geninsert select * from t_PM_MAINMENUITEM where fid = 'b4pt7+m2RjWdlFbB3iVOof033J8='

 

校验表是否存在

If not exists (select * from KSQL_USERTABLES where KSQL_TABNAME ='CT_ZSJ_ComChangeParent')

 

效验字段是否存在

If not exists (select * from KSQL_USERCOLUMNS where KSQL_COL_NAME ='FRowId' and KSQL_COL_TABNAME='T_ZSJ_ComOperateDSHEntry') 
alter table T_ZSJ_ComOperateDSHEntry add FRowId varchar(44);

--同表字段替换

调试脚本最终执行
  protected KDPreparedStatement(PreparedStatement stmt, KDConnection cn, String ksql, String dialect_sql)

共享平台的组织对应表

T_sscorg

 

--- 任务推送平台处理视图

select fcontent 主题,fstatename 状态,cast( FCRTEATETIME as date) 接收时间,cast(Fcompletetme as date) 审批时间
,FPERSONNAME 业务员,FPRORITYNAME 优先级 from T_OperatedJOB_VIEW 
where FCRTEATETIME < to_date('2013-12-01','yyyy-MM-dd')
and Fcompletetme >= to_date('2013-12-01','yyyy-MM-dd')

--查询701凭证重复的情况
select v.* from t_gl_voucher v right join ( 
select cfothersyncsourcebillid From t_gl_voucher Group by cfothersyncsourcebillid having Count(cfothersyncsourcebillid) > 1 ) T on v.cfothersyncsourcebillid = T.cfothersyncsourcebillid order by v.cfothersyncsourcebillid 

---添加临时表的文件

select * from dba_temp_files where tablespace_name='TEMP1'
file_name,tablespace_name,bytes/1024/1024,STATUS
select * from dba_free_space where tablespace_name='TEMP1'
ALTER tablespace temp1 add  tempfile '/oradata/oradata2/tempfile4.dbf' size 2g autoextend on next 30m

---统计

select count(count(fcreatorid))  from T_BC_DailyLoanBill where fcreatetime >= {ts'2016-12-25 00:00:00'} and  fcreatetime<= {ts'2016-12-25 23:59:59'}
group by fcreatorid order by fcreatorid  desc

-- 费用报销单查询 上线项目及上线金额
select count(bill.fcompanyid),sum(bill.Famount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
T_BC_BizAccountBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.fcompanyid
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
group by bill.fcompanyid,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber


-- 差旅费单查询 上线项目及上线金额
select count(bill.fcompanyid),sum(bill.Famount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
T_BC_TravelAccountBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.fcompanyid
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
group by bill.fcompanyid,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber


-- 借款单单查询 上线项目及上线金额
select count(bill.fcompanyid),sum(bill.Famount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
T_BC_DailyLoanBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.fcompanyid
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
group by bill.fcompanyid,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber


-- 支付单查询 上线项目及上线金额
select count(bill.CFCOMPANYID),sum(bill.CFamount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
CT_LQG_PayApplyBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.CFCOMPANYID
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
group by bill.CFCOMPANYID,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber


-- 列账单查询 上线项目及上线金额
select count(bill.CFAPPLIERCOMPANYID),sum(bill.CFamount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
CT_LQG_DisplayBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.CFAPPLIERCOMPANYID
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
group by bill.CFAPPLIERCOMPANYID,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber

-- 内部往来支付查询 上线项目及上线金额
select count(bill.CFAPPLIERCOMPANYID),sum(bill.CFamount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
CT_LQG_IntraPayBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.CFAPPLIERCOMPANYID
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
group by bill.CFAPPLIERCOMPANYID,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber


-- 内部往来支付查询 上线项目及上线金额
select count(bill.CFAPPLIERCOMPANYID),sum(bill.CFamount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
CT_LQG_IntraApplyBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.CFAPPLIERCOMPANYID
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
--where bill.FbizDate >={ts'2017-09-01 00:00:00'} and  bill.FbizDate <={ts'2017-09-30 00:00:00'}
group by bill.CFAPPLIERCOMPANYID,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber




-- 内部往来申请查询 上线项目及上线金额
select count(bill.CFAPPLIERCOMPANYID),sum(bill.CFamount) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2 from 
CT_LQG_IntraApplyBill bill 
left join T_ORG_BaseUnit base
on base.fid = bill.CFAPPLIERCOMPANYID
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
--where bill.FbizDate >={ts'2017-09-01 00:00:00'} and  bill.FbizDate <={ts'2017-09-30 00:00:00'}
group by bill.CFAPPLIERCOMPANYID,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber

-- 出纳付款单当天付款的金额及笔数查询

select count(bill.fcompanyid),sum(bill.Factpaylocamt) as amount,base1.fname_l2, base.Fnumber ,base.Fname_l2  from  T_CAS_PaymentBill bill
left join T_ORG_BaseUnit base
on base.fid = bill.fcompanyid
left join T_ORG_BaseUnit base1
on base1.fid = base.fparentID
where fsourcebillid is null 
and fpayDate >= {ts'2017-09-22 00:00:00'} and fpayDate <= {ts'2017-09-22 23:59:59'}
group by bill.fcompanyid,base1.fname_l2, base.Fnumber ,base.Fname_l2 
order by  base.Fnumber