Hi,

These are few transactions and corresponding adjustments made in a bank.
[code/]

Acct id    Type            Location Code    Date    Previous Acct id
153    TRANSACTION    4    01-SEP-09 12.00.00 AM    
155    Adjustment        6    04-SEP-09 04.14.06 PM    153
157    TRANSACTION    4    04-SEP-09 04.31.00 PM    
162    Adjustment        6    04-SEP-09 04.50.29 PM    157
169    TRANSACTION    4    30-AUG-09 12.00.00 AM    
171    Adjustment        6    30-AUG-09 12.00.00 AM    155
172    TRANSACTION    4    30-AUG-09 12.00.00 AM    


What does this mean ?

Acct id    Type    Location Code    Date    Previous Acct id
                         
153     TRANSACTION      4  01-SEP-09 12.00.00 AM   
155    Adjustment           6  04-SEP-09 04.14.06 PM    153
171    Adjustment           6  30-AUG-09 12.00.00 AM    155



The transaction with Acc code 153 has been adjusted twice, the first adjustments acc code is 155 and its previous adj code (153) is equal to acc code of the original transaction.(153).

So the most recent adjustment is

171    Adjustment    6    30-AUG-09 12.00.00 AM    155


I want to develop two reports with following out puts

For each transaction, I want to see

1)Original Transaction + All adjustments associated with it

2)Original Transaction+ Most recent adjustment, they are linked with previous acct id.



Report 1)

I am going to write an sql command in add command line which is like this


select * from tableA whe
start with tableA.previous acct id  is null
connect by prior tableA.acct_id = tableA.previous_acct_id)

Acct id Type    Location Code    Date    Previous Acct id    

Grouped by date of transaction

sep - 01 - 2008

                        
153     TRANSACTION     4  01-SEP-09 12.00.00 AM   
155    Adjustment          6  04-SEP-09 04.14.06 PM    153
171    Adjustment          6  30-AUG-09 12.00.00 AM    155

sep - 04 - 2008

157    TRANSACTION    4    04-SEP-09 04.31.00 PM   
162    Adjustment        6    04-SEP-09 04.50.29 PM    157


Problem 1)In this case if user has to be given the ability to key in a date par, so if a parameter field is created for Select_date, {?Select_date} and selection criteria has a formula like {command.date} ={?Select_Date}

It retrieves all transactions or adjustments for that date and doesnt pick any other transactions or corresponding adjustments , may be because transaction and adjustments use same database field called Date .


How can I give user an opportunity to select a date, which in the backened will fetch all transactions for  that date and use my sql command to check on any adjustments related to it?




Report 2) I couldn't figure out a way to supress all the adjustments in the loop and display only the original transaction and most recent adjustments, any advise?


The ideal output is


                       
153     TRANSACTION      4     01-SEP-09 12.00.00 AM   
171      Adjustment         6     30-AUG-09 12.00.00 AM    155

[code/]
 

Thanks
NAmmu