CREATE OR REPLACE FUNCTION FUN_ACCOUNT_CHANGE( HeadObject IN EBSHEADOBJECT,
InAccountNo IN PUB_ACCOUNT_INFO.ACCOUNTNO%TYPE,
InAccountType IN PUB_ACCOUNT_INFO.ACCOUNTTYPE%TYPE,
InOpMoney IN NUMBER,
InOpProfit IN NUMBER,
InFlag IN VARCHAR) RETURN VARCHAR2
IS
DbAccountFlag PUB_ACCOUNT_INFO.ACCOUNTFLAG%TYPE;
DbState PUB_ACCOUNT_INFO.STATE%TYPE;
DbCredit PUB_ACCOUNT_INFO.CREDIT%TYPE;
DbCreditHLD PUB_ACCOUNT_INFO.CREDITHLD%TYPE;
DbProfit PUB_ACCOUNT_INFO.PROFIT%TYPE;
DbMacChk PUB_ACCOUNT_INFO.MACCHK%TYPE;
DbThresHold PUB_ACCOUNT_INFO.THRESHOLD%TYPE;
DbLeastCredit PUB_ACCOUNT_INFO.LEASTCREDIT%TYPE;
DbCredit0 PUB_ACCOUNT_INFO.CREDIT%TYPE;
DbProfit0 PUB_ACCOUNT_INFO.PROFIT%TYPE;
vCredit PUB_ACCOUNT_INFO.CREDIT%TYPE;
vProfit PUB_ACCOUNT_INFO.PROFIT%TYPE;
DbConnectPhone PUB_ACCOUNT_INFO.CONNECTPHONE%TYPE;
vMacChk VARCHAR2(128);
---212001 212002 214001 214003 214005 会导致lasttime时间的更新,该项的更新原则是仅记录由代理商主动发起的交易,佣金处理不做时间点更新.
BEGIN
--根据ACCOUNTTYPE ACCOUNTNO 获取账号信息
BEGIN
SELECT ACCOUNTFLAG,STATE,CREDIT,PROFIT,CREDITHLD,MACCHK,THRESHOLD,LEASTCREDIT ,CONNECTPHONE
INTO DbAccountFlag,DbState,DbCredit,DbProfit,DbCreditHld,DbMacChk,DbThresHold,DbLeastCredit,DbConnectPhone
FROM PUB_ACCOUNT_INFO
WHERE ACCOUNTNO=INACCOUNTNO AND ACCOUNTTYPE=INACCOUNTTYPE;
EXCEPTION
--账户不存在
WHEN NO_DATA_FOUND THEN
RETURN '31000002';
WHEN OTHERS THEN
RETURN '90000001';
END;
IF DbState != 0 THEN
--账务状态不正常,修改错误代码
RETURN '31000003';
END IF;
--校验MAC
SELECT RAWTOHEX(EBS_MD5(LPAD(DbCredit,16,'0')||LPAD(DbProfit,16,'0'))) INTO vMacChk FROM DUAL;
IF vMacChk != DbMacChk THEN
--账务非法变动,修改错误代码
RETURN '31000004';
END IF;
--如果使用本金账户，则重新获取账户信息
IF (INACCOUNTTYPE !=0) AND (DbAccountFlag !=0 )THEN
BEGIN
SELECT STATE,CREDIT,PROFIT,DbCreditHld,MACCHK,THRESHOLD,LEASTCREDIT ,CONNECTPHONE
INTO DbState,DbCredit,DbProfit0,DbCreditHld,DbMacChk,DbThresHold,DbLeastCredit,DbConnectPhone
FROM PUB_ACCOUNT_INFO
WHERE ACCOUNTNO=INACCOUNTNO AND ACCOUNTTYPE=0;
EXCEPTION
--账户不存在
WHEN NO_DATA_FOUND THEN
RETURN '31000002';
WHEN OTHERS THEN
RETURN '90000001';
END ;
IF DbState != 0 THEN
--账务状态不正常,修改错误代码
RETURN '31000003';
END IF;
--校验MAC
SELECT RAWTOHEX(EBS_MD5(LPAD(DbCredit,16,'0')||LPAD(DbProfit0,16,'0'))) INTO vMacChk FROM DUAL;
IF vMacChk != DbMacChk THEN
--账务非法变动,修改错误代码
RETURN '31000004';
END IF;
END IF;
--交易类别:
CASE InFlag
WHEN '-' THEN
--账户金额减少,佣金增加(适用于充值业务,佣金金额可以为负)
IF DbCredit < INOPMONEY THEN
--账务金额不足
RETURN '31000001';
END IF;
IF DbCredit -INOPMONEY <DbLeastCredit THEN
--最少金额控制
RETURN '31000001';
END IF;
IF DbCreDit - INOPMONEY <DbThresHold THEN
--告警阀值
INSERT INTO BASE_SENDSMS_LOG(AGTPHONE,CORPID,OPTIME,STATE,OWNER,MESSAGE)
VALUES( DbConnectPhone,SUBSTR(InAccountNo,3,4),sysdate,0,HeadObject.TradeType||HeadObject.TradeCode||HeadObject.StreamId,'保证金余额不足，剩余'||DbCreDit/100||'请及时存款');
END IF;
--更新账户,更新佣金(如果使用本金账户，则两个账户都需要更新,佣金记录在明细账户中)
IF (INACCOUNTTYPE !=0) AND (DbAccountFlag !=0 )THEN
--挂机账户
INSERT INTO PUB_ACCOUNT_LOG (STREAMID,ACCOUNTNO,ACCOUNTTYPE,OPTIME,OLDCREDIT,OLDPROFIT,OPMONEY,NEWCREDIT,NEWPROFIT,TRADETYPE,TRADECODE,CHNLTYPE,DEPTNO,OPERNO)
SELECT HeadObject.StreamId,InAccountNo,0,sysdate,CREDIT,PROFIT,InOpMoney,CREDIT-InOpMoney,PROFIT,HeadObject.TradeType,HeadObject.TradeCode,HeadObject.ChnlType,HeadObject.DeptNo,HeadObject.OperNo
FROM PUB_ACCOUNT_INFO
WHERE ACCOUNTNO=InAccountNo AND ACCOUNTTYPE=0;
UPDATE PUB_ACCOUNT_INFO SET CREDIT=CREDIT-INOPMONEY ,MACCHK=RAWTOHEX(EBS_MD5(LPAD(CREDIT-INOPMONEY,16,'0')||LPAD(PROFIT,16,'0'))) WHERE ACCOUNTNO=INACCOUNTNO AND ACCOUNTTYPE=0;