Visio-fss er diagram.vsd
FSS_MERCHANT
PK MERCHANTID DOUBLE
MERCHANTFIRSTNAME TEXT(35)
MERCHANTLASTNAME TEXT(50)
MERCHANTSTREETNR TEXT(15)
MERCHANTSTREETNAME TEXT(35)
MERCHANTSUBURB TEXT(35)
MERCHANTSTATE TEXT(3)
MERCHANTPOSTCODE TEXT(4)
MERCHANTTELEPHONENR TEXT(15)
MERCHANTFAXNR TEXT(15)
MERCHANTBANKBSB TEXT(6)
MERCHANTBANKACCNR TEXT(10)
MERCHANTACCOUNTTITLE TEXT(32)
MERCHANTSTATUS TEXT(1)
FSS_HOLIDAY
HOLIDAY_DATE DATE
FSS_ORGANISATION
PK ORGNR NUMBER
ORGNAME VARCHAR2(50)
ORGSTREETNR VARCHAR2(25)
ORGSTREET VARCHAR2(50)
ORGSUBURB VARCHAR2(35)
ORGSTATE VARCHAR2(3)
ORGPOSTCODE VARCHAR2(6)
ORGPHONENR VARCHAR2(18)
ORGENQUIRYNR VARCHAR2(12)
ORGBSBNR VARCHAR2(6)
ORGBANKACCOUNT VARCHAR2(10)
ORGACCOUNTTITLE VARCHAR2(26)
COMMENTS VARCHAR2(255)
FSS_REFERENCE
PK REFERENCEID VARCHAR2(5)
REFERENCENAME VARCHAR2(25)
REFERENCEVALUE VARCHAR2(50)
REFCOMMENT VARCHAR2(255)
FSS_SMARTCARD
PK CARDID VARCHAR2(17)
DATEISSUED DATE
ISSUEVALUE NUMBER
CARDSTATUS VARCHAR2(1)
CURRENTVALUE NUMBER
FSS_TERMINAL
PK TERMINALID TEXT(10)
TERMINALTYPE TEXT(3)
SAMID TEXT(10)
TERMINALSTATUS TEXT(1)
FK2 MERCHANTID DOUBLE
FK1 TYPENAME TEXT(3)
FSS_TERMINAL_TYPE
PK TYPENAME VARCHAR2(3)
TYPEDESCRIPTION VARCHAR2(50)
TERMINALPREFIX CHAR(24)
TYPESTATUS VARCHAR2(1)
MINTRANAMT NUMBER
MAXTRANAMT NUMBER
CREATEDBY VARCHAR2(10)
CREATEDDATE DATE
FSS_TRANSACTION
PK TRANSACTIONNR NUMBER
DOWNLOADDATE DATE
FK1 TERMINALID VARCHAR2(10)
FK2 CARDID VARCHAR2(17)
TRANSACTIONDATE DATE
CARDOLDVALUE NUMBER
TRANSACTIONAMOUNT NUMBER
CARDNEWVALUE NUMBER
TRANSACTIONSTATUS VARCHAR2(1)
ERRORCODE VARCHAR2(25)
SMARTCARD FINANCIAL SETTLEMENT SYSTEM
DATABASE PROGRAMMING
ASSIGNMENT
FSS_DAILY_SETTLEMENT
FSS_RUN_TABLE
PK RUNID NUMBER
RUNSTART DATE
RUNEND DATE
RUNOUTCOME VARCHAR2(15)
REMARKS VARCHAR2(255)
FSS_DAILY_TRANSACTION
Existing Table
Staging Table
Data is temporary
New Table
Suggestion only
The FSS_DAILY_TRANSACTION table
You will copy any NEW data from my table into yours
then settle the transactions that need to be settled.
The structure of the table will be the same as mine but
you may want to add any columns that you feel
appropriate to your solution. If you add new columns
then please follow the existing naming convention.
The FSS_DAILY_SETTLEMENT will store
the results of your settlement run. You will
need to design the definition of the table
yourself
You will create the FSS_RUN_TABLE
Values for the RUNOUTCOME attribute
are SUCCESS or FAIL