DATABASE PROGRAMMING ASSIGNMENT
TYPEDESCRIPTION TERMINALPREFIX TYPESTATUS MINTRANAMT MAXTRANAMT CREATEDBY
VARCHAR2(50) CHAR(24) VARCHAR2(1) NUMBER NUMBER VARCHAR2(10) DA TE
FK2 FK1
TERMINALTYPE SAMID TERMINALSTATUS MERCHANTID TYPENAME
TEXT(3) TEXT(10) TEXT(1) DOUBLE TEXT(3)
MERCHANTFIRSTNAME MERCHANTLASTNAME MERCHANTSTREETNR MERCHANTSTREETNAME MERCHANTSUBURB MERCHANTSTATE MERCHANTPOSTCODE MERCHANTTELEPHONENR MERCHANTFAXNR MERCHANTBANKBSB MERCHANTBANKACCNR MERCHANTACCOUNTTITLE MERCHANTSTATUS
TEXT(35) TEXT(50) TEXT(15) TEXT(35) TEXT(35) TEXT(3) TEXT(4) TEXT(15) TEXT(15) TEXT(6) TEXT(10) TEXT(32) TEXT(1)
PK ORGNR
NUMBER
FSS_TRANSACTION
ORGNAME
VARCHAR2(50)
PK
TRANSACTIONNR
NUMBER
FSS_ORGANISA TION
ORGSTREETNR ORGSTREET ORGSUBURB ORGST A TE ORGPOSTCODE ORGPHONENR ORGENQUIRYNR ORGBSBNR ORGBANKACCOUNT ORGACCOUNTTITLE COMMENTS
VARCHAR2(25) VARCHAR2(50) VARCHAR2(35) VARCHAR2(3) VARCHAR2(6) VARCHAR2(18) VARCHAR2(12) VARCHAR2(6) VARCHAR2(10) VARCHAR2(26) VARCHAR2(255)
DOWNLOADDATE TERMINALID
CARDID TRANSACTIONDATE CARDOLDVALUE TRANSACTIONAMOUNT CARDNEWVALUE TRANSACTIONSTATUS ERRORCODE
DATE VARCHAR2(10) VARCHAR2(17) DATE NUMBER NUMBER NUMBER VARCHAR2(1) VARCHAR2(25)
FSS_DAILY_TRANSACTION
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.
FSS_REFERENCE
PK REFERENCEID VARCHAR2(5)
FSS_SMARTCARD
REFERENCENAME VARCHAR2(25) REFERENCEVALUE VARCHAR2(50)
PK CARDID
VARCHAR2(17)
RUNSTART DATE
RUNEND DATE RUNOUTCOME VARCHAR2(15)
REFCOMMENT
VARCHAR2(255)
DATEISSUED ISSUEVALUE CARDSTATUS CURRENTVALUE
DATE NUMBER VARCHAR2(1) NUMBER
The FSS_DAILY_SETTLEMENT will store the results of your settlement run. You will need to design the definition of the table yourself
REMARKS
VARCHAR2(255)
FSS_HOLIDAY
HOLIDAY_DATE DATE
You will create the FSS_RUN_TABLE Values for the RUNOUTCOME attribute are SUCCESS or FAIL
PK
TYPENAME
VARCHAR2(3)
PK TERMINALID
TEXT(10)
PK MERCHANTID
DOUBLE
SMARTCARD FINANCIAL SETTLEMENT SYSTEM
FSS_TERMINAL_TYPE
FSS_TERMINAL
FSS_MERCHANT
CREA TEDDA TE
New Table Suggestion only
Existing Table
Staging Table Data is temporary
FK1 FK2
FSS_DAILY_SETTLEMENT
FSS_RUN_T ABLE
PK RUNID NUMBER