Интерфейс модуля "АРМ оператора клиринговой палаты"

-- Интерфейс модуля "АРМ оператора клиринговой палаты"
-------------------------------------------------------

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
WHERE BankID = aDebitBank;

BEGIN

SELECT BankID INTO aTmpID FROM TmpRes
WHERE BankID = aCreditBank;

EXCEPTION

WHEN No_Data_Found THEN

INSERT INTO TmpRes (BankID, DebitPos, CreditPos)
VALUES (aCreditBank, 0, 0);

END;

UPDATE TmpRes
SET CreditPos = CreditPos - aAmount
WHERE BankID = aCreditBank;

END IF;

END ConfirmMessage;

END ServerUtils;

/
SHOW ERROR;

---------------------------------------------------------
-- Таблица и последовательность для ведения статистики
---------------------------------------------------------

PROMPT Create table Statistics...

PROMPT Creating sequence Stat_Seq...

CREATE SEQUENCE Stat_Seq
INCREMENT BY 1
START WITH 1
MAXVALUE 99999999
MINVALUE 1
NOCACHE
CYCLE;

CREATE TABLE Statistics
(
ItemNo NUMBER(8) -- Номер элемента выборки
CONSTRAINT itemno_pk PRIMARY KEY,
WorkDay DATE, -- Дата и время платежа
BankID NUMBER(8) -- Номер банка
DebitPos INT, -- Дебетовая позиция
CreditPos INT -- Кредитовая позиция
);

-----------------------------------------------
-- Заполнение таблицы классификаторов
-----------------------------------------------

PROMPT Inserting data into table Status ...

BEGIN

DELETE FROM Status WHERE StatusClass = 1;
INSERT INTO Status VALUES (1, GlobalConst.cStatusClass,
'Статус банка в текущем сеансе');
INSERT INTO Status VALUES (1, GlobalConst.cBankWork,
'Банк участвует в текущем сеансе');
INSERT INTO Status VALUES (1, GlobalConst.cBankStopWork,
'Банк закончил текущий сенс');
INSERT INTO Status VALUES (1, GlobalConst.cBankNotWork,
'Банк не участвует в текущем сенсе');
COMMIT;


DELETE FROM Status WHERE StatusClass = 2;
INSERT INTO Status VALUES (2, GlobalConst.cStatusClass,
'Статус сообщения о платеже');
INSERT INTO Status VALUES (2, GlobalConst.cMsgNotProcessed,
'Сообщение не обработано');
INSERT INTO Status VALUES (2, GlobalConst.cMsgSourceError,
'Банк-источник не работает');
INSERT INTO Status VALUES (2, GlobalConst.cMsgDebitError,
'Банк по дебету счета не работает');
INSERT INTO Status VALUES (2, GlobalConst.cMsgCreditError,
'Банк по кредиту счета не работает');
INSERT INTO Status VALUES (2, GlobalConst.cMsgProcessed,
'Сообщение обработано и требует документального подтверждения');
INSERT INTO Status VALUES (2, GlobalConst.cMsgAccepted,
'Сообщение обработано и принято в систему взаимозачета');

COMMIT;

END;
/

-----------------------------------------------
-- Таблица классификаторов
-----------------------------------------------

PROMPT Create table Status ...

CREATE TABLE Status
(
StatusClass NUMBER(3),
StatusNo NUMBER(3),
StatusText VARCHAR2(70) NOT NULL,
CONSTRAINT status_pk PRIMARY KEY (StatusClass, StatusNo)
);

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

PROMPT Creating table TmpRes...

CREATE TABLE TmpRes
(
BankID NUMBER(8)
CONSTRAINT tmpresbankid_fk REFERENCES Banks ON DELETE CASCADE,
DebitPos INT,
CreditPos INT
);

---------------------------------------------------
-- Удаление объектов системы
---------------------------------------------------

PROMPT Deleting databases...

SET ECHO ON;

DROP SEQUENCE Stat_Seq;
DROP TABLE Statistics;
DROP TABLE BankState;
DROP TABLE Status;
DROP SEQUENCE Msg_Seq;
DROP TABLE Messages;
DROP TABLE Results;
DROP TABLE TmpRes;
DROP SEQUENCE BankID_Seq;
DROP TABLE Banks;


PROMPT Deleting packages...

DROP PACKAGE BODY ServerUtils;
DROP PACKAGE ServerUtils;

DROP PACKAGE BODY ClientUtils;
DROP PACKAGE ClientUtils;

DROP PACKAGE BODY CommonUtils;
DROP PACKAGE CommonUtils;

SET ECHO OFF;
----------------------------------------------------------------

• 4. Роль клиринговой палаты на фондовой бирже
• Существуют различные системы зачета
• Многосторонний зачет
• Прямая компенсация
• ФУНКЦИИ ФИНАНСОВОЙ ЧАСТИ ПРОЦЕССА КЛИРИНГА

Содержание раздела

---