Банк Рефератов - Межбанковский клиринг - скачать рефераты, бесплатно рефераты

Главная: Рефераты

Рефераты. Межбанковский клиринг

FUNCTION GetCurrentDebitSaldo(aBankID IN INT) RETURN INT

Res INT;

BEGIN

SELECT DebitPos INTO Res FROM TmpRes

WHERE BankID = aBankID;

EXCEPTION

WHEN No_Data_Found THEN

Res := 0;

END;

RETURN Res;

END GetCurrentDebitSaldo;

-- Возвращает текущее кредитовое сальдо банка-участника

FUNCTION GetCurrentCreditSaldo(aBankID IN INT) RETURN INT

Res INT;

BEGIN

SELECT CreditPos INTO Res FROM TmpRes

WHERE BankID = aBankID;

EXCEPTION

WHEN No_Data_Found THEN

Res := 0;

END;

RETURN Res;

END GetCurrentCreditSaldo;

END CommonUtils;

SHOW ERROR;

--------------------------------------------------------

-- Модуль содержит глобальные константы

--------------------------------------------------------

PROMPT Creating package GlobalConst

CREATE OR REPLACE PACKAGE GlobalConst

cStatusClass CONSTANT INT := -1;

cBankWork CONSTANT INT := 1;

cBankStopWork CONSTANT INT := 2;

cBankNotWork CONSTANT INT := 3;

cMsgNotProcessed CONSTANT INT := 1;

cMsgAccepted CONSTANT INT := 2;

cMsgSourceError CONSTANT INT := 3;

cMsgDebitError CONSTANT INT := 4;

cMsgCreditError CONSTANT INT := 5;

cMsgProcessed CONSTANT INT := 6;

cSessionUnActive CONSTANT INT := 1;

cSessionActive CONSTANT INT := 2;

END GlobalConst;

SHOW ERROR;

------------------------------------------------------

-- Модуль инициализации системы в СУБД Oracle 7.3.3

------------------------------------------------------

CONNECT INTERNAL

SPOOL createdb.log

SET ECHO ON

CREATE TABLESPACE "CLEARING" DATAFILE 'C:\ORAWIN95\DATABASE\clearing.dbf'

SIZE 5M REUSE DEFAULT STORAGE (INITIAL 10K NEXT 10K MINEXTENTS 1

MAXEXTENTS 120 PCTINCREASE 0);

CREATE USER "ALEX" IDENTIFIED BY "ALEX"

DEFAULT TABLESPACE "CLEARING";

GRANT "DBA" TO "ALEX" WITH ADMIN OPTION;

GRANT UNLIMITED TABLESPACE to "ALEX" WITH ADMIN OPTION;

GRANT SELECT ANY TABLE to "ALEX" WITH ADMIN OPTION;

GRANT GRANT ANY ROLE to "ALEX" WITH ADMIN OPTION;

GRANT CREATE ANY SYNONYM to "ALEX" WITH ADMIN OPTION;

GRANT DROP ANY SYNONYM to "ALEX" WITH ADMIN OPTION;

GRANT EXECUTE ANY PROCEDURE to "ALEX";

GRANT CREATE USER to "ALEX";

GRANT ALTER USER to "ALEX";

GRANT DROP USER to "ALEX";

GRANT GRANT ANY PRIVILEGE to "ALEX";

SPOOL OFF

EXIT

--------------------------------------------------

-- Начальное создание объектов системы

--------------------------------------------------

PROMPT Creating databases and sequences...

@@consts.pkg

@@banks

@@messages

@@status

@@statinit

@@bnkstate

@@results

@@tmpres

@@statist

PROMPT Creating packages...

@@srvrutil.pkg

@@srvrutil.bdy

@@clntutil.pkg

@@clntutil.bdy

@@commutil.pkg

@@commutil.bdy

-------------------------------------------------------------

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

-------------------------------------------------------------

PROMPT Creating sequence Msg_Seq...

CREATE SEQUENCE Msg_Seq

INCREMENT BY 1

START WITH 1

MAXVALUE 99999999

MINVALUE 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

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

-- Процедура инициализации клирингового сенса в клиринговой палате

-- Выполняется каждый день в определенное время

PROCEDURE StartClearingSession

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

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

Res INT;

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

Res INT;

Cur INT;

Col INT;

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)

BEGIN

DELETE FROM Banks

WHERE BankID = aBankID;

END UnRegisterBank;

-- Удаляет банк из числа участников по его имени

PROCEDURE UnRegisterBank(aBankName IN VARCHAR2)

BEGIN

DELETE FROM Banks

WHERE BankName = aBankName;

END UnRegisterBank;

-- Проверяет допустимость поступившего платежного документа

FUNCTION CheckMessage(aMessageID IN INT) RETURN INT

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)

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;

----------------------------------------------------------------

-----------------------

[pic]

Страницы: 1, 2, 3, 4, 5, 6, 7, 8, 9