建表语句
“`sql
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_RUN_TABLE
(
RUNID NUMBER
, RUNSTART DATE
, RUNEND DATE
, RUNOUTCOME VARCHAR2(15 BYTE)
, REMARKS VARCHAR2(255)
);
CREATE TABLE FSS_DAILY_SETTLEMENT
(
TRANSACTION_DATE DATE,
MERCHANTID NUMBER,
AMOUNT NUMBER
);
CREATE TABLE FSS_MAX_TRANSACTIONNR
(
TRANSACTIONNR NUMBER
);
insert into FSS_MAX_TRANSACTIONNR values(0);
“`
函数语句
“`sql
CREATE OR REPLACE DIRECTORY MYDIR AS ‘/exports/orcloz’;
CREATE or REPLACE PACKAGE Pkg_FSS_Settlement AS
PROCEDURE write_deskbank_file (mydate IN date);
PROCEDURE write_report_file (mydate IN date, reportDate IN DATE);
PROCEDURE settle (mydate IN date);
PROCEDURE DailySettlement;
PROCEDURE DailyBankingSummary(d varchar2 DEFAULT null);
PROCEDURE myoutputLine(f IN UTL_FILE.FILE_TYPE, s varchar2);
END Pkg_FSS_Settlement;
/
CREATE OR REPLACE PACKAGE BODY Pkg_FSS_Settlement AS
PROCEDURE myoutputLine(f IN UTL_FILE.FILE_TYPE, s varchar2)
AS
begin
UTL_FILE.PUT_LINE(f, s);
dbms_output.put_line(s);
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;
PROCEDURE DailySettlement
AS
curDate DATE;
BEGIN
curDate := to_date(TO_CHAR(SYSDATE, ‘MM-DD-YYYY’), ‘MM-DD-YYYY’);
settle(curDate);
END;
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);
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 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’;
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;
PROCEDURE write_report_file
(mydate IN date, reportDate IN DATE)
AS
— reportDate DATE;
s VARCHAR2(200);
total NUMBER;
out_File UTL_FILE.FILE_TYPE;
fn VARCHAR2(200);
begin
— reportDate := to_date(’02-APR-18′,’DD-MON-RR’);
fn := ‘99189274_DSREP_’ || TO_CHAR(mydate, ‘DDMMYYYY’) || ‘.rpt’;
out_File := UTL_FILE.FOPEN(‘MYDIR’, fn , ‘W’);
myoutputLine(out_file,’ SMARTCARD SETTLEMENT SYSTEM’);
myoutputLine(out_file,’ DAILY DESKBANK SUMMARY’);
myoutputLine(out_file,’Date ‘ || TO_CHAR(reportDate, ‘DD-Mon-YYYY’) || ‘ Page x’);
myoutputLine(out_file,”);
myoutputLine(out_file,’Merchant ID Merchant Name Account Number Debit Credit’);
myoutputLine(out_file,’———- —————————————— —————– ———- ———‘);
FOR tran IN (SELECT m.MERCHANTID, m.MERCHANTLASTNAME as MerchantName, SUBSTR(m.MERCHANTBANKBSB, 0, 3) || ‘-‘ || SUBSTR(m.MERCHANTBANKBSB, 4) || m.MERCHANTBANKACCNR as AccountNum, s.amount FROM FSS_DAILY_SETTLEMENT s, FSS_MERCHANT m
where TRANSACTION_DATE = mydate and s.MERCHANTID = m.MERCHANTID)
LOOP
myoutputLine(out_file, TO_CHAR(tran.MERCHANTID) || ‘ ‘ || RPAD(tran.MerchantName , 43, ‘ ‘) || tran.AccountNum || ‘ ‘ || LPAD(tran.amount / 100, 11, ‘ ‘) );
END LOOP;
select sum(amount) into total from FSS_DAILY_SETTLEMENT where TRANSACTION_DATE = mydate;
FOR tran in (select ORGACCOUNTTITLE as MerchantName, SUBSTR(ORGBSBNR, 0, 3) || ‘-‘ || SUBSTR(ORGBSBNR, 4) || ORGBANKACCOUNT as AccountNum from FSS_ORGANISATION)
LOOP
myoutputLine(out_file, ‘ ‘ || RPAD(tran.MerchantName , 43, ‘ ‘) || tran.AccountNum || ‘ ‘ || LPAD(total / 100, 11, ‘ ‘) );
END LOOP;
myoutputLine(out_file,’ ————- ————-‘);
myoutputLine(out_file,’BALANCE TOTAL ‘ || LPAD(total / 100, 12, ‘ ‘) || LPAD(total / 100, 12, ‘ ‘));
myoutputLine(out_file,”);
myoutputLine(out_file,”);
myoutputLine(out_file,’Deskbank file Name : ‘ || ‘99189274_DS_’ || TO_CHAR(mydate, ‘DDMMYYYY’) || ‘.dat’ );
myoutputLine(out_file,”);
myoutputLine(out_file,’Dispatch Date : ‘ || TO_CHAR(mydate, ‘DD Mon YYYY’));
myoutputLine(out_file,’ ****** End of Report ****** ‘);
UTL_FILE.FCLOSE(out_file);
dbms_output.put_line(‘file ‘ || fn || ‘ generated’);
end;
END Pkg_FSS_Settlement;
/
“`