Межбанковский клиринг

1 NOCACHE CYCLE;

PROMPT Creating table Messages...

CREATE TABLE Messages ( MessageID NUMBER(8) -- Номер сообщения CONSTRAINT message_pk PRIMARY KEY, SourceBankID NUMBER(8) -- Банк-источник сообщения CONSTRAINT sourcebank_fk REFERENCES Banks ON DELETE CASCADE, DebitBankID NUMBER(8) -- Банк-получатель CONSTRAINT debitbank_fk REFERENCES Banks ON DELETE CASCADE, CreditBankID NUMBER(8) -- Банк-плательщик CONSTRAINT creditbank_fk REFERENCES Banks ON DELETE CASCADE, Amount INT NOT NULL, -- Сумма MsgStatus INT NOT NULL -- Статус сообщения );

-------------------------------------------------------------- -- Таблица окончательных результатов клирингового сеанса --------------------------------------------------------------

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