I have 2 invoice transaction files (header and detail). Once the transaction has been marked as posted, you can not edit it. But you can perform a "distribution change" on the original invoice. To change the account code it goes against for example. Each row can have one dist change against it. But a dist change can have another dist change performed against it.

The way the data model works is that each row in the detail table has 2 id columns, detail_id and ref_id and a flag column, dist_change.

An invoice with no dist change against it will look like:
detail_id: 100
ref_id: null
dist_change: Y

An invoice with a dist change, and the dist change row:
invoice: detail_id: 101
ref_id: null
dist_change: N

dist change row: detail_id: 102
ref_id: 101
dist_change: Y

So the ref_id of the dist change refers to the detail_id of the original invoice line. If a dist change was performed against this dist change, it's ref_id would have 102, and the dist_change flag of the first dist_change would get set to N, meaning no more dist change allowed for this row.

For 1099 tax purposes, there is one more step. First only rows with a dist change of "Y" are selected. Next, the transaction table is related into the checks table by detail_id to only get transactions that have a row in the checks table (they are paid).

Is it possible to create a view that can traverse the tree. So when it encounters a dist change row, it can find the detail_id of the invoice it applies to, using that id to join to the checks table?

Someone gave me a Stored Proc I was able to use for this, but this is for a Java program that expects a view. So I will have to change the program to use the new Stored Proc if I can't use a view.

Thanks.