Межбанковский клиринг
Страница 11
PROMPT Creating table Results .
CREATE TABLE RESULTS
(
BankID NUMBER(8)
CONSTRAINT resultbankid_fk REFERENCES Banks ON DELETE CASCADE,
DebitPos INT,
CreditPos INT
);
-------------------------------------------------------
-- Интерфейс модуля "АРМ оператора клиринговой палаты"
-------------------------------------------------------
PROMPT Creating package ServerUtils .
CREATE OR REPLACE PACKAGE ServerUtils
AS
SessionState INT DEFAULT GlobalConst.cSessionUnActive;
PROCEDURE StartClearingSession;
PROCEDURE StopClearingSession;
FUNCTION GetActiveBanksCountInSession RETURN INT;
-- Процедура регистрации банка-участника клиринговой системы
FUNCTION RegisterBank(aBankName IN VARCHAR2,
aUserName IN VARCHAR2,
aPassword IN VARCHAR2) RETURN INT;
PROCEDURE UnRegisterBank(aBankName IN VARCHAR2);
-- Процедура выхода банка из клиринговой системы
PROCEDURE UnRegisterBank(aBankID IN INT);
FUNCTION CheckMessage(aMessageID IN INT) RETURN INT;
PROCEDURE ConfirmMessage(aMessageID IN INT);
END ServerUtils;
/
SHOW ERROR;
-----------------------------------------------
-- Процедуры и функции сервера КП
-----------------------------------------------
PROMPT Creating package body ServerUtils .
CREATE OR REPLACE PACKAGE BODY ServerUtils
AS
-- Процедура инициализации клирингового сенса в клиринговой палате
-- Выполняется каждый день в определенное время
PROCEDURE StartClearingSession
IS
BEGIN
DELETE FROM Messages;
DELETE FROM BankState;
DELETE FROM Results;
DELETE FROM TmpRes;
FOR x IN
(
SELECT BankID
FROM Banks
)
LOOP
INSERT INTO BankState (BankID, BankStatus)
VALUES (x.BankID, GlobalConst.cBankNotWork);
END LOOP;
SessionState := GlobalConst.cSessionActive;
END StartClearingSession;
-- Процедура завершения клирингового сеанса
-- Выполняется каждый день
PROCEDURE StopClearingSession
IS
aDebitSum INT;
aCreditSum INT;
BEGIN
SessionState := GlobalConst.cSessionUnActive;
UPDATE BankState
SET BankStatus = GlobalConst.cBankStopWork
WHERE BankStatus = GlobalConst.cBankWork;
FOR x IN
(
SELECT BankID
FROM BankState
WHERE BankStatus = GlobalConst.cBankStopWork
)
LOOP
BEGIN
SELECT Sum(Amount) INTO aDebitSum
FROM Messages
WHERE MsgStatus = GlobalConst.cMsgAccepted AND DebitBankID = x.BankID;
EXCEPTION
WHEN No_Data_Found THEN
aDebitSum := 0;
END;
BEGIN
SELECT Sum(Amount) INTO aCreditSum
FROM Messages
WHERE MsgStatus = GlobalConst.cMsgAccepted AND CreditBankID = x.BankID;
EXCEPTION
WHEN No_Data_Found THEN
aCreditSum := 0;
END;
INSERT INTO Results(BankID, DebitPos, CreditPos)
VALUES(x.BankID, aDebitSum, aCreditSum);
END LOOP;
INSERT INTO Statistics(ItemNo, WorkDay, BankID, DebitPos, CreditPos)
SELECT Stat_Seq.NextVal, SYSDATE, BankID, DebitPos, CreditPos
FROM Results;
DELETE FROM Messages;
DELETE FROM BankState;
DELETE FROM Results;
DELETE FROM TmpRes;
END StopClearingSession;
-- Возвращает количество активных участников текущего сеанса
FUNCTION GetActiveBanksCountInSession RETURN INT
IS
Res INT;
BEGIN
BEGIN
SELECT Count(*) INTO Res FROM BankState
WHERE BankStatus = GlobalConst.cBankWork;
EXCEPTION
WHEN No_Data_Found THEN
Res :=0;
END;
RETURN Res;
END GetActiveBanksCountInSession;
-- Регистрирует новый банк в клиринговой системе
FUNCTION RegisterBank(aBankName IN VARCHAR2,
aUserName IN VARCHAR2,
aPassword IN VARCHAR2) RETURN INT
IS
Res INT;
Cur INT;
Col INT;
BEGIN
BEGIN
SELECT BankID INTO Res FROM Banks
WHERE BankName = aBankName;
EXCEPTION
WHEN No_Data_Found THEN
SELECT BankID_Seq.NextVal INTO Res FROM Dual;
INSERT INTO Banks(BankID, BankName, UserName)
VALUES (Res, aBankName, aUserName);
cur:=dbms_sql.open_cursor;
dbms_sql.parse(cur,
'CREATE USER '||aUserName||' IDENTIFIED BY '||aPassword,
dbms_sql.v7);
Col := dbms_sql.execute(Cur);
dbms_sql.parse(cur,
'GRANT CREATE SESSION to '||aUserName,
dbms_sql.v7);
Col := dbms_sql.execute(Cur);
dbms_sql.close_cursor(cur);
END;
RETURN Res;
END RegisterBank;
-- Удаляет банк из числа участников по его номеру
PROCEDURE UnRegisterBank(aBankID IN INT)
IS
BEGIN
DELETE FROM Banks
WHERE BankID = aBankID;
END UnRegisterBank;
-- Удаляет банк из числа участников по его имени
PROCEDURE UnRegisterBank(aBankName IN VARCHAR2)
IS
BEGIN
DELETE FROM Banks
WHERE BankName = aBankName;
END UnRegisterBank;
-- Проверяет допустимость поступившего платежного документа
FUNCTION CheckMessage(aMessageID IN INT) RETURN INT
IS
aMsgStatus INT;
aBankStatus INT;
aSourceBank INT;
aDebitBank INT;
aCreditBank INT;
BEGIN
SELECT SourceBankID INTO aSourceBank FROM Messages
WHERE MessageID = aMessageID;
SELECT DebitBankID INTO aDebitBank FROM Messages
WHERE MessageID = aMessageID;
SELECT CreditBankID INTO aCreditBank FROM Messages
WHERE MessageID = aMessageID;
BEGIN
SELECT BankStatus INTO aBankStatus FROM BankState
WHERE BankID = aSourceBank;
EXCEPTION
WHEN No_Data_Found THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgSourceError
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgSourceError;
END;
IF aBankStatus = GlobalConst.cBankNotWork THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgSourceError
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgSourceError;
END IF;
BEGIN
SELECT BankStatus INTO aBankStatus FROM BankState
WHERE BankID = aDebitBank;
EXCEPTION
WHEN No_Data_Found THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgDebitError
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgDebitError;
END;
IF aBankStatus = GlobalConst.cBankNotWork THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgDebitError
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgDebitError;
END IF;
BEGIN
SELECT BankStatus INTO aBankStatus FROM BankState
WHERE BankID = aCreditBank;
EXCEPTION
WHEN No_Data_Found THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgCreditError
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgCreditError;
END;
IF aBankStatus = GlobalConst.cBankNotWork THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgCreditError
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgCreditError;
END IF;
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgProcessed
WHERE MessageID = aMessageID;
RETURN GlobalConst.cMsgProcessed;
END CheckMessage;
-- Подтверждает платежный документ, вызывается при наличии необходимых
-- документов
PROCEDURE ConfirmMessage(aMessageID IN INT)
IS
aMsgStatus INT;
aTmpID INT;
aAmount INT;
aDebitBank INT;
aCreditBank INT;
BEGIN
SELECT MsgStatus, DebitBankID, CreditBankID
INTO aMsgStatus, aDebitBank, aCreditBank
FROM Messages
WHERE MessageID = aMessageID;
IF aMessageID = GlobalConst.cMsgProcessed THEN
UPDATE Messages
SET MsgStatus = GlobalConst.cMsgAccepted
WHERE MessageID = aMessageID;
SELECT Amount INTO aAmount FROM Messages
WHERE MessageID = aMessageID;
BEGIN
SELECT BankID INTO aTmpID FROM TmpRes
WHERE BankID = aDebitBank;
EXCEPTION
WHEN No_Data_Found THEN
INSERT INTO TmpRes (BankID, DebitPos, CreditPos)
VALUES (aDebitBank, 0, 0);
END;
UPDATE TmpRes
SET DebitPos = DebitPos + aAmount