Home » SQL & PL/SQL » SQL & PL/SQL » Reconciliation of Transactions (oracle 9i)
Reconciliation of Transactions [message #336672] Mon, 28 July 2008 11:25 Go to next message
manke
Messages: 1
Registered: July 2008
Junior Member
Hi

I have to match transactions between two tables. Structure of both the tables is as under:

TXN_Date date
Terminal_ID varcahr2(16)
TXN_No varchar2(12)
TXN_amount number(8,2)

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:
15000
-6000
Three transactions of the same combination in table B appear with following amount:
15000
-15000
9000
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

Requirement is:

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?.
Re: Reconciliation of Transactions [message #336673 is a reply to message #336672] Mon, 28 July 2008 11:28 Go to previous message
BlackSwan
Messages: 25046
Registered: January 2009
Location: SoCal
Senior Member
http://www.orafaq.com/forum/t/88153/0/
Please read & follow posting guidelines as stated in URL above
Previous Topic: Index
Next Topic: Converting month/year to month number? (merged)
Goto Forum:
  


Current Time: Wed Dec 07 11:00:27 CST 2016

Total time taken to generate the page: 0.17274 seconds