MF_CardTransaction

Fact

Individual transactions recorded to card access system's ledgers.

The business key here is the transaction ID and the name of the system from which the data is taken. The transaction ID is calculated as part of the ETL process and converted to a 36-character varchar field. In CS Gold it is calculated, using the LEDGERS_CARL view, using the transaction table number, the transaction ID generation, the transaction ID, and the transaction ID sequence. It makes sense to collapse all these values down into a single key field.

This table is loaded (as far as CS Gold is concerned, in 2012) from a view, LEDGERS_CARL, which is based on a delivered view, LEDGERS_VR, but with the addition throughout of another field, TRANSIDSEQ.

The full definition of this view, as of April 2012, in the Oracle back-end to CS Gold, is appended below. Note how it collapses information from multiple ledgers into one transaction table:

CREATE VIEW DIEBOLD.LEDGERS_CARL
(
TABLEID,
TRANDATE,
LOCATION,
PATRONID,
TRANSIDSEQ_GENERATION,
TRANSID,
TRANSIDSEQ,
ACCOUNTTYPE,
APPRVALUEOFTRAN,
COMPLETIONSTATUS,
REASONMSG,
TRANSTYPE,
CURRENTPRIMARYKEY
)
AS
(SELECT 1 tableid,
trandate,
LOCATION,
patronid,
transidseq_generation,
transid,
transidseq,
accounttype,
0 ApprValueOfTran,
completionstatus,
reasonmsg,
trantype transtype,
currentprimarykey
FROM activitytranledger_vr
WHERE patronid IS NOT NULL
AND trantype IN
(10000,
10200,
10300,
10301,
12000,
12100,
20000,
20203,
20303,
0,
990000,
940002)
UNION ALL
SELECT 2 tableid,
trandate,
LOCATION,
patronid,
transidseq_generation,
transid,
transidseq,
0 accounttype,
ApprValueOfTran,
DECODE (transtatus, 'C', '1', 'P', '1', 0) completionstatus,
(SELECT DISTINCT description
FROM uilistvalues
WHERE FIELD = 'TransType'
AND numcopyofvalue = m.transtype
AND groupid = -1)
reasonmsg,
transtype transtype,
currentprimarykey
FROM mealplantranledg_TRAN_vr m
WHERE (operatorname NOT LIKE 'PATIMP-%' OR operatorname IS NULL)
AND transtype IN (9090, 9898, 179898, 209090, 209898, 219898)
UNION ALL
SELECT 3 tableid,
trandate,
LOCATION,
patronid,
transidseq_generation,
transid,
transidseq,
accounttype,
ApprValueOfTran,
DECODE (transtatus, 'C', '1', 'P', '1', 0) completionstatus,
(SELECT DISTINCT description
FROM uilistvalues
WHERE FIELD = 'TransType'
AND numcopyofvalue = g.transtype
AND groupid = -1)
reasonmsg,
transtype transtype,
currentprimarykey
FROM generalledger_TRAN_vr g
WHERE accounttype = 2
AND (operatorname NOT LIKE 'PATIMP-%' OR operatorname IS NULL)
AND transtype IN
(9090,
9999,
179999,
189999,
209090,
209999,
219090,
219999,
939999,
949999)
UNION ALL
SELECT 4 tableid,
trandate,
LOCATION,
patronid,
transidseq_generation,
transid,
transidseq,
0 accounttype,
0 ApprValueOfTran,
completionstatus,
reasonmsg,
transtype transtype,
currentprimarykey
FROM time_attend_tranledger_TRAN_vr
WHERE transtype IN (100, 200, 201, 202, 300)
UNION ALL
SELECT 5 tableid,
trandate,
LOCATION,
patronid,
transidseq_generation,
transid,
transidseq,
accounttype,
0 ApprValueOfTran,
'0' completionstatus,
reasonmsg,
transactiontype transtype,
currentprimarykey
FROM transactionfailledger_TRAN_vr
WHERE transactiontype IN
(9090,
9898,
179898,
209090,
209898,
219898,
9090,
9999,
179999,
189999,
209090,
209999,
219090,
219999,
939999,
949999));


CREATE PUBLIC SYNONYM LEDGERS_CARL FOR DIEBOLD.LEDGERS_CARL;

GRANT SELECT ON DIEBOLD.LEDGERS_CARL TO CARLETON_DATAWAREHOUSE;