design
Table Usage and Creation
Table FSS_DAILY_TRANSACTION is used to copy the new transactions from FS_TRANSACTIONS
table into this table and then process it.
Table FSS_MAX_TRANSACTIONNR is used to record the latest transaction already processed so
that we know which transactions are new transactions.
Table FSS_DAILY_SETTLEMENT is used to record the credit of the merchants in a settlement.
CREATE TABLE FSS_DAILY_TRANSACTION
(
TRANSACTIONNR NUMBER
, DOWNLOADDATE DATE
, TERMINALID VARCHAR2(10 BYTE)
, CARDID VARCHAR2(17 BYTE)
, TRANSACTIONDATE DATE
, CARDOLDVALUE NUMBER
, TRANSACTIONAMOUNT NUMBER
, CARDNEWVALUE NUMBER
, TRANSACTIONSTATUS VARCHAR2(1 BYTE)
, ERRORCODE VARCHAR2(25 BYTE)
, CONSTRAINT FSS_DAILY_PRIME PRIMARY KEY
(
TRANSACTIONNR
)
ENABLE
);
CREATE TABLE FSS_MAX_TRANSACTIONNR
(
TRANSACTIONNR NUMBER
);
Table FSS_RUN_TABLE is used to record the result of each settlement so that we only run
settlement once in each day.
Procedure definition
write_deskbank_file is used to generate the deskbank file. It reads the table
FSS_DAILY_SETTLEMENT to get the information needed to generate the desbank file.
CREATE TABLE FSS_DAILY_SETTLEMENT
(
TRANSACTION_DATE DATE,
MERCHANTID NUMBER,
AMOUNT NUMBER
);
CREATE TABLE FSS_RUN_TABLE
(
RUNID NUMBER
, RUNSTART DATE
, RUNEND DATE
, RUNOUTCOME VARCHAR2(15 BYTE)
, REMARKS VARCHAR2(255)
);
PROCEDURE write_deskbank_file
(mydate IN date)
AS
s VARCHAR2(200);
ind INT;
total NUMBER;
out_File UTL_FILE.FILE_TYPE;
fn VARCHAR2(200);
begin
fn := ‘99189274_DS_’ || TO_CHAR(mydate, ‘DDMMYYYY’) || ‘.dat’;
write_report_file is used to generate the banking summary report file. It also reads the
table FSS_DAILY_SETTLEMENT to get the information needed.
out_File := UTL_FILE.FOPEN(‘MYDIR’, fn , ‘W’);
s := ‘0 01WBC S/CARD BUS PAYMENTS
038759INVOICES ‘ || TO_CHAR(mydate, ‘DDMMRR’);
myoutputLine(out_file,s);
ind := 1;
FOR tran IN (SELECT ‘1’ || SUBSTR(m.MERCHANTBANKBSB, 0, 3) || ‘-‘ ||
SUBSTR(m.MERCHANTBANKBSB, 4) || m.MERCHANTBANKACCNR || ‘ ‘ || ’50’ ||
LPAD(TO_CHAR(s.amount), 10, ‘0’) || RPAD(m.MERCHANTACCOUNTTITLE, 32, ‘ ‘)
|| ‘ F ‘ as record FROM FSS_DAILY_SETTLEMENT s, FSS_MERCHANT m
where TRANSACTION_DATE = mydate and s.MERCHANTID = m.MERCHANTID)
LOOP
myoutputLine(out_file, tran.record || TO_CHAR(mydate, ‘YYYYMMDD’)
|| LPAD( 430 + ind, 7, ‘0’) || ‘032-797 001006SMARTCARD TRANS 00000000’
);
ind := ind + 1;
END LOOP;
select sum(amount) into total from FSS_DAILY_SETTLEMENT where
TRANSACTION_DATE = mydate;
FOR tran in (select ‘1’ || SUBSTR(ORGBSBNR, 0, 3) || ‘-‘ ||
SUBSTR(ORGBSBNR, 4) || ORGBANKACCOUNT || ‘ ‘ || ’13’ || LPAD( total , 10,
‘0’) || RPAD(ORGACCOUNTTITLE, 32, ‘ ‘) || ‘ N ‘ as record from
FSS_ORGANISATION)
LOOP
myoutputLine(out_file, tran.record || TO_CHAR(mydate, ‘YYYYMMDD’)
|| LPAD( 430 + ind, 7, ‘0’) || ‘032-797 001006SMARTCARD TRANS 00000000’
);
END LOOP;
myoutputLine(out_file,’7999-999 ‘ || LPAD(0, 10, ‘0’) ||
LPAD(total, 10, ‘0’) || LPAD(total, 10, ‘0’) || ‘ ‘
|| LPAD(ind, 6, ‘0’) || LPAD(‘ ‘, 40, ‘ ‘));
UTL_FILE.FCLOSE(out_file);
dbms_output.put_line(‘file ‘ || fn || ‘ generated’);
end;
settle makes the settlement for a given date. It first copies the new transactions from
FSS_TRANSACTIONS to FSS_DAILY_TRANSACTION. It then computes the credit of each merchant
and inserts these information into talbe FSS_DAILY_SETTLEMENT. Finally, it uses
write_deskbank_file and write_report_file to generate the deskbank file and report
file. It also checks and update the FSS_RUN_TABLE to ensure settlement only run once.
PROCEDURE settle
(mydate IN date)
AS
maxId NUMBER;
alreadRun INT;
begin
select count(*) into alreadRun from FSS_RUN_TABLE where RUNSTART =
mydate and RUNOUTCOME = ‘SUCCESS’;
IF alreadRun > 0 THEN
dbms_output.put_line(‘already run, abort’);
COMMON.LOG(‘already run, abort’);
ELSE
insert into FSS_RUN_TABLE(RUNSTART, RUNOUTCOME) values(mydate,
‘SUCCESS’);
— mydate := to_date(’02-APR-18′,’DD-MON-RR’);
select max(TRANSACTIONNR) into maxId from FSS_MAX_TRANSACTIONNR;
Insert into FSS_DAILY_TRANSACTION select * from FSS_TRANSACTIONS
where DOWNLOADDATE < mydate + 1 and TRANSACTIONNR > maxId;
insert into FSS_DAILY_SETTLEMENT(TRANSACTION_DATE, MERCHANTID,
AMOUNT)
select mydate, temp.MERCHANTID, temp.amount from
(select t.MERCHANTID, sum(TRANSACTIONAMOUNT) as amount from
FSS_DAILY_TRANSACTION d, FSS_TERMINAL t
where d.TERMINALID = t.TERMINALID group by t.MERCHANTID)
temp,
(select TO_NUMBER(REFERENCEVALUE, ‘9G999D99’) as
Daily_Minimum from FSS_REFERENCE where REFERENCENAME = ‘Daily Minimum
Settlement’) dm
where temp.amount >= dm.Daily_Minimum * 100;
write_deskbank_file(mydate);
write_report_file(mydate, mydate);
DailySettlement invokes settle to make settlement in today.
DailyBankingSummary just invokes the write_report_file ,
insert into FSS_MAX_TRANSACTIONNR select max(TRANSACTIONNR) from
FSS_DAILY_TRANSACTION;
delete from FSS_DAILY_TRANSACTION where TERMINALID in (select
t.TERMINALID from FSS_TERMINAL t, FSS_DAILY_SETTLEMENT d where
TRANSACTION_DATE = mydate and t.MERCHANTID = d.MERCHANTID );
END IF;
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(‘run error’);
COMMON.LOG(‘run error’);
update FSS_RUN_TABLE SET RUNOUTCOME = ‘FAIL’ where RUNSTART = mydate;
end;
PROCEDURE DailySettlement
AS
curDate DATE;
BEGIN
curDate := to_date(TO_CHAR(SYSDATE, ‘MM-DD-YYYY’), ‘MM-DD-YYYY’);
settle(curDate);
END;
PROCEDURE DailyBankingSummary(d varchar2)
AS
settleD varchar2(100);
curD varchar2(100);
begin
curD := TO_CHAR(SYSDATE, ‘DD-MON-YYYY’);
settleD := d;
If d IS NULL THEN
settleD := curD;
end If;
write_report_file(settleD, curD);
end;
Table Usage and Creation
Procedure definition