floydborga
Programmer
Hi All,
I'm working here in Poland on an Archiving project where Account
Receivable Data older than a certain date is going to be Archived.
I have to come up with a Detailed Design of how this is going to be done.
The first is a Selection process into Temp tables and the second is copy
into the Archiving DB and a deletion to the production DB.
There is going to be a Unix script calling Stored Procedures which do the
Selection process. My problem is that for AR there are three tables that
are interdependent. For every PAYMENTS there could be more than one row
PAYMENT_DETAILS and for every row in PAYMENT_DETAILS there is only one row
in BILLING_SUMMARY. All this just means that one payment could be applied
to one or more bill.
Anyway, for table integrity these related rows have to be selected for
Archiving. Suppose that I have a payment that was split up between to two
bills and this payment paid both bills in full. So I have one row in
PAYMENTS two in PAYMENT_DETAILS and two in BILLING_SUMMARY to be selected.
My Idea is to first do a Join between the tree tables into a KEY_TEMP table
and than using the key from this table do a JION to select the actual rows
from each respective table.
The problem is that for the example above, the KEY_TEMP would have two rows
for the payment with the same key. No??
Does anyone have any suggestions on the best way to do this??
TIA