0

I am working on a package to process incoming payments from external entities and match them against existing "bills". Each payment has a reference to to bill_id, so matching is easy. Bills are then flagged as paid. Problems is payment may bounce and the contra-payment will be received in the same or, more likely, in a subsequent file. Logic is that a contra-payment must be reversing an already paid bill, while a regular payment must be for a "non paid" bill. How would you implement and enforce such a validation in SSIS, considering that the contra-payment might be in the same file as the payment it is reversing or in a successive file. How would you structure your data-flow? Also critical is the need to report on bounced payments, to assess a kind of "default rate". I'd like to hear the opinion of the experts. Many thanks

gif
  • 9
  • 1
  • 1
    To restate -> I have a bill for 100. It is in a non-paid state. I submit payment for 100 and it is marked as paid. The payment does not clear and a subsequent file indicates that I have a -100 payment, the contra-payment. I think I'm clear so far on the problem definition. What's the actual question? Also, what happens when you get my next 100 that should go through? We'll need some data structures and sample data if you'd like to get a reasonable answer versus vague hand waving – billinkc Jan 21 '15 at 21:45
  • 2
    Get the spread gun. It will make things much easier. – Zane Jan 21 '15 at 21:50
  • @gif what do you have so far? also, do you have an entity for "bounced payments"? – J.S. Orris Jan 21 '15 at 22:55
  • The problem/question is how to best make sure that a contra is for an already paid bill while a standard payment is for an unpaid bill, bearing in mind that the two could be in the same dataset or not. Once the next 100 come, they should go along if the bill has been correctly reset to unpaid. The entity for the bounced payment is the same that sent the original transaction. The process would need to run daily and the volume of records can be anything from 100 to 10000. Also monitoring and reporting on the bounced payment is crucial. What do you think is the best option? – gif Jan 21 '15 at 23:25
  • I have basically a blank canvas; the list of bills (about 10 million records) and daily incoming payments (positive and contra) all referring to the bill_id – gif Jan 21 '15 at 23:34

0 Answers0