5,889
社区成员
发帖
与我相关
我的任务
分享
String financeId = "";
String firstFinanceId = "";
List<TbCsmCustomerFinanceBO> list = ds.selectByHqlQuery("select bo from TbCsmCustomerFinanceBO bo where bo.customerNum = ? order by bo.financeStatementDeadline",new Object[] {customerNum});
int j = 0;
for (int i = 0; i < list.size(); i++) {
TbCsmCustomerFinanceBO tbCsmCustomerFinanceBO = (TbCsmCustomerFinanceBO) list.get(i);
if (i == 0) {//资产总额取上月底财务报表中“资产合计”科目数据
firstFinanceId = tbCsmCustomerFinanceBO.getCustomerFinanceId();
sql = "select sum(BEFORE_PROJECT_VALUE) project_value from TB_CSM_FINANCE_STATEMENT_DATA"
+ " where CUSTOMER_FINANCE_ID = '"
+ firstFinanceId
+ "' and PROJECT_CD in ('PNO001000045','PMO001000046','PNN001000033','PMN001000039')";
if ("1".equals(tbCsmCustomerFinanceBO.getRegulatedInd())) {
sql = sql.replaceAll("BEFORE_PROJECT_VALUE","AFTER_PROJECT_VALUE");
}
List list1 = (List) ds.selectBySqlQuery(sql, null);
if (list1 != null && list1.size() > 0) {
Map map = (Map) list1.get(0);
if( map.get("project_value")!=null){
assetTotalAmt = ((BigDecimal) map.get("project_value")).doubleValue();
}
}
}
//销售总额取近三年年度财务报表中“主营业务收入”算术平均值,如果应用新会计准则(2006)的财务报表,则取“营业收入”算术平均值。
if ("1".equals(tbCsmCustomerFinanceBO.getFinanceStatementTypeCd())) {//年报
if (j < 3) {
financeId = tbCsmCustomerFinanceBO.getCustomerFinanceId();
sql = "select sum(BEFORE_PROJECT_VALUE) project_value from TB_CSM_FINANCE_STATEMENT_DATA"
+ " where CUSTOMER_FINANCE_ID = '"
+ financeId
+ "' and PROJECT_CD in ('PNO002000001','PMO002000001','PNN002000001','PMN002000002')";
if ("1".equals(tbCsmCustomerFinanceBO.getRegulatedInd())) {
sql = sql.replaceAll("BEFORE_PROJECT_VALUE","AFTER_PROJECT_VALUE");
}
List list1 = (List) ds.selectBySqlQuery(sql, null);
if (list1 != null && list1.size() > 0) {
Map map = (Map) list1.get(0);
if( map.get("project_value")!=null){
venditionAmt = venditionAmt + ((BigDecimal) map.get("project_value")).doubleValue();
}
}
j = j + 1;
}
}
}
if (j != 0) {
venditionAmt = venditionAmt / j;
}
-- db2 connect to inte user db2admin using db2admin
--编译过程 db2 -td@ -vf PR_income_check.db2
--运行过程
--db2 call PR_income_check()
--删除过程
drop procedure PR_income_check()@
--创建PR_income_check过程
create procedure PR_income_check()
language sql
begin
--定义变量
declare V_RPTNO_DATE VARCHAR(10);
declare V_RPTNO_YEAR CHARACTER(2);
declare V_DATE DATE;
declare VAR_CUSTNAME VARCHAR(128);
--循环计数器
declare loopcount INT;
declare loopcontrol INT;
--校验日志相关变量
declare V_TBL_NAME CHARACTER(50);
declare V_PK_VALUE VARCHAR(255);
declare V_ERR_TYPE CHARACTER(4);
declare V_DESCRIPTION VARCHAR(255);
declare STR_TBL_CUSTOMERINFO CHARACTER(50) default 'TBL_CUSTOMERINFO';
declare STR_TBL_BRANCHINFO CHARACTER(50) default 'TBL_BRANCHINFO';
declare STR_TBL_BANKINFO CHARACTER(50) default 'TBL_BANKINFO';
declare STR_TBL_PAYMETHODCODE CHARACTER(50) default 'TBL_PAYMETHODCODE';
declare STR_TBL_CCYCODE CHARACTER(50) default 'TBL_CCYCODE';
declare STR_TBL_COUNTRY CHARACTER(50) default 'TBL_COUNTRY';
declare STR_TBL_TXCODE CHARACTER(50) default 'TBL_TXCODE';
declare V_PK VARCHAR(255);
--定义数据select错误的相关异常处理的参数
declare tblname char(50) default 'TBL_INCOME_TMP';
declare errLog VARCHAR(400);
declare sqlcode integer ;
declare sqlstate char(5) ;
declare rowcount int;
DECLARE not_found CONDITION FOR SQLSTATE '02000';
DECLARE CONTINUE HANDLER FOR not_found
set rowcount = 0;
DECLARE CONTINUE HANDLER FOR SQLSTATE '22007'
set rowcount = 6;
-- DECLARE CONTINUE HANDLER FOR SQLSTATE '23505'
-- set rowcount = 2;
declare exit handler for sqlexception
call logNote(tblname, errLog, sqlcode, sqlstate);
-------
call logNote(tblname, 'PR_income_check开始',0, '0');
-------
set errLog = '游标取数,TBL_INCOME_TMP';
set V_TBL_NAME = 'TBL_INCOME_TMP';
set loopcount = 0;
set loopcontrol = 1;
for checkLoop as check_cursor cursor with hold for
--游标取数TBL_INCOME_TMP,放入变量
select
RPTNO V_RPTNO,
DS_ID V_DS_ID,
-- YEAR V_YEAR,
-- PAYDATE V_PAYDATE,
BRANCHCODESELF V_BRANCHCODESELF,
BRANCHCODE V_BRANCHCODE,
BANKCODESELF V_BANKCODESELF,
BANKCODE V_BANKCODE,
CUSTTYPE V_CUSTTYPE,
-- IDCODE V_IDCODE,
CUSTCODE V_CUSTCODE,
CUSTNAME V_CUSTNAME,
-- OPPNAME V_OPPNAME,
TXCCY V_TXCCY,
TXCCYSELF V_TXCCYSELF,
TXAMT V_TXAMT,
TXAMTUSD V_TXAMTUSD,
EXRATE V_EXRATE,
LCYAMT V_LCYAMT,
LCYAMTUSD V_LCYAMTUSD,
-- LCYACC V_LCYACC,
FCYAMT V_FCYAMT,
FCYAMTUSD V_FCYAMTUSD,
-- FCYACC V_FCYACC,
OTHAMT V_OTHAMT,
OTHAMTUSD V_OTHAMTUSD,
-- OTHACC V_OTHACC,
METHODSELF V_METHODSELF,
METHOD V_METHOD,
-- BANKBUSICODE V_BANKBUSICODE,
INCHARGECCY V_INCHARGECCY,
INCHARGECCYSELF V_INCHARGECCYSELF,
INCHARGEAMT V_INCHARGEAMT,
-- INCHARGEAMTUSD V_INCHARGEAMTUSD,
OUTCHARGECCY V_OUTCHARGECCY,
OUTCHARGECCYSELF V_OUTCHARGECCYSELF,
OUTCHARGEAMT V_OUTCHARGEAMT,
-- OUTCHARGEAMTUSD V_OUTCHARGEAMTUSD,
-- BANKUSERTEL V_BANKUSERTEL,
COUNTRYCODE V_COUNTRYCODE,
COUNTRYCODESELF V_COUNTRYCODESELF,
PAYTYPE V_PAYTYPE,
TXCODE1 V_TXCODE1,
TXCODE1SELF V_TXCODE1SELF,
TXAMT1 V_TXAMT1,
TXAMT1USD V_TXAMT1USD,
-- TXREM1 V_TXREM1,
TXCODE2 V_TXCODE2,
TXCODE2SELF V_TXCODE2SELF,
TXAMT2 V_TXAMT2,
TXAMT2USD V_TXAMT2USD,
-- TXREM2 V_TXREM2,
-- ISWRITEOFF V_ISWRITEOFF,
-- BILLNO V_BILLNO,
-- RPTUSER V_RPTUSER,
-- RPTTEL V_RPTTEL,
-- RPTDATE V_RPTDATE,
-- UNDERLIMIT V_UNDERLIMIT,
-- PAYATTR V_PAYATTR,
-- PAYATTRSELF V_PAYATTRSELF,
-- ISPRINTED V_ISPRINTED,
-- OSAMT V_OSAMT,
-- OSAMTUSD V_OSAMTUSD,
-- WRITEOFFAMT V_WRITEOFFAMT,
-- WRITEOFFAMTUSD V_WRITEOFFAMTUSD,
-- WRITEOFFUSER V_WRITEOFFUSER,
-- WRITEOFFTEL V_WRITEOFFTEL,
-- WRITEOFFDATE V_WRITEOFFDATE,
RPTSTS V_RPTSTS,
RECSTS V_RECSTS,
-- FATCHNO V_FATCHNO,
CHECK_STATE V_CHECK_STATE
from TBL_INCOME_TMP
for update
do
set rowcount = 1;
set V_PK_VALUE = V_RPTNO;
update TBL_INCOME_TMP set CHECK_STATE = '1' where current of check_cursor;
--数据源校验
--Ds_Id为1、2、3或4
-- if V_DS_ID = 1 or V_DS_ID = 2 or V_DS_ID = 3 or V_DS_ID = 4 then
-- else
-- end if;
--对金额字段进行非空处理
--收款金额TxAmt
--结汇金额LcyAmt
--现汇金额FcyAmt
--其他金额OthAmt
update TBL_INCOME_TMP set TxAmt = coalesce(V_TXAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set LcyAmt = coalesce(V_LCYAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set FcyAmt = coalesce(V_FCYAMT, 0) where current of check_cursor;
update TBL_INCOME_TMP set OthAmt = coalesce(V_OTHAMT, 0) where current of check_cursor;
--对限额标记字段进行赋值
if V_TXAMT <= 2000 then
update TBL_INCOME_TMP set UNDERLIMIT = 'Y' where current of check_cursor;
else
update TBL_INCOME_TMP set UNDERLIMIT = 'N' where current of check_cursor;
end if;
--申报号里的时间相关校验
set V_RPTNO_YEAR = substr(V_RPTNO, 13, 2);
set rowcount = 1;
if V_RPTNO_YEAR > '60' then
set V_RPTNO_DATE = '19' || V_RPTNO_YEAR || '-' || substr(V_RPTNO, 15 ,2) || '-' || substr(V_RPTNO, 17 ,2);
else
set V_RPTNO_DATE = '20' || V_RPTNO_YEAR || '-' || substr(V_RPTNO, 15 ,2) || '-' || substr(V_RPTNO, 17 ,2);
end if;
set V_DATE = cast(V_RPTNO_DATE as date);
if rowcount = 6 then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0101';
set V_DESCRIPTION = '涉外收入申报单:申报单号中日期非法:'||V_RPTNO_DATE;
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
end if;
--——————————————————————————————————
--非删除记录才校验
if not (V_RECSTS = 'D') then
--——————————————————————————————————
--单位代码的校验
--组织机构代码CustCode在单位基本情况表TBL_CUSTOMERINFO中是否存在
--if not ((V_DS_ID = 1 or V_DS_ID = 2) and V_RPTSTS = '0') then
set rowcount = 1;
select
CUSTNAME
into
VAR_CUSTNAME
from TBL_CUSTOMERINFO
where CUSTCODE = V_CUSTCODE;
if rowcount = 0 and V_CUSTTYPE = 'C' and V_RPTSTS = '1' then
update TBL_INCOME_TMP set CHECK_STATE = '0' where current of check_cursor;
set V_ERR_TYPE = '0102';
set V_DESCRIPTION = '涉外收入申报单:单位代码不存在';
call check_log(V_TBL_NAME, V_PK_VALUE, V_ERR_TYPE, V_DESCRIPTION);
set V_PK = V_CUSTCODE;
set V_DESCRIPTION = '单位基本情况表:单位代码不存在';
call check_log(STR_TBL_CUSTOMERINFO, V_PK, V_ERR_TYPE, V_DESCRIPTION);
else
if (V_DS_ID = 1 or V_DS_ID = 2) and (V_CUSTNAME is null or ltrim(V_CUSTNAME) = '') then
update TBL_INCOME_TMP set CUSTNAME = VAR_CUSTNAME where current of check_cursor;
end if;
end if;
--end if;
--——————————————————————————————————
--非删除记录才校验
end if;
--——————————————————————————————————
set loopcount = loopcount + 1;
if loopcount >= 8000 * loopcontrol then
set loopcontrol = loopcontrol + 1;
call logNote(tblname, 'PR_income_check处理了'||char(loopcount)||'条数据',0, '0');
commit;
end if;
end for;
-------
call logNote(tblname, 'PR_income_check结束',0, '0');
commit;
end@