[SOLVED] Table Usage and Creation

Table Usage and Creation
Table FSS_DAILY_TRANSACTION is used to copy the new transactions from FS_TRANSACTIONS tableinto this table and then process it.
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
);

Table FSS_MAX_TRANSACTIONNR is used to record the latest transaction already processed so that we know which transactions are new transactions.
CREATE TABLE FSS_MAX_TRANSACTIONNR
(
TRANSACTIONNR NUMBER
);

Table FSS_DAILY_SETTLEMENT is used to record the credit of the merchants in a settlement.

CREATE TABLE FSS_DAILY_SETTLEMENT
(
TRANSACTION_DATE DATE,
MERCHANTID NUMBER,
AMOUNT NUMBER
);

Table FSS_RUN_TABLE is used to record the result of each settlement so that we only run settlement once in each day.
CREATE TABLE FSS_RUN_TABLE
(
RUNID NUMBER
, RUNSTART DATE
, RUNEND DATE
, RUNOUTCOME VARCHAR2(15 BYTE)
, REMARKS VARCHAR2(255)
);

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.
PROCEDURE write_deskbank_file
(mydate IN date)

AS

s VARCHAR2(200);
ind INT;
total NUMBER;

out_FileUTL_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 = mydateand 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;
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.

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 whereDOWNLOADDATE < 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;

DailySettlement invokes settle to make settlement in today.
PROCEDURE DailySettlement
AS
curDate DATE;
BEGIN
curDate := to_date(TO_CHAR(SYSDATE, MM-DD-YYYY), MM-DD-YYYY);
settle(curDate);

END;

DailyBankingSummary just invokes the write_report_file,
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;