|Reconciliation of Transactions [message #336672]
||Mon, 28 July 2008 11:25
Registered: July 2008
I have to match transactions between two tables. Structure of both the tables is as under:
Table A is base table and transactions of this table are to be matched with those of table B
Although combination of TXN_Date, Terminal_ID and TXN_No represents unique transaction, another combination may appear with different amount (which may be the same amount with -ve sign, different amount with -ve sign or zero amount) which is reversal/partial reversal of original transaction.
Same transaction appears in table B.
Generally transactions of table A match with those of table B,
Exceptions are as under
1) The transaction of table A may appear in table B multiple times. It may be once, twice or thrice.
2) The transaction of table A may not appear in table B.
3) Two transactions of a particular combination ( as mentioned above) in table A with following amount:
Three transactions of the same combination in table B appear with following amount:
4) Transaction of Table A of a specific TXN_date may appear in table B on following TXN_date(s)
TXN_date-1, TXN_date or TXN_date+1
1) Match transactions of table A of a particular TXN_Date in table B on one to one basis and stamp transactions in both the tables as matched.
2) Fetch un-matched transactions of table A of a TXN_date and un-matched transactions of table B of TXN_date -1.
There are 1.5 to 2 million transactions/day.
How to proceed ? Can anybody help me?.