I am very new to Oracle and SQL and I have the following scenario which I need some assistance in solving.
Tables
AR_Invoice
AR_inv_no GL_Code AR_Amount AR_Date
1234 11500 500.00 01/01/2015
2345 11500 300.00 01/02/2015
3456 11501 250.00 03/01/2015
etc
AP_Invoice
AP_Inv_No GL_Code AP_Amount AP_Date
4567 12500 350.00 02/05/2015
5678 12500 275.00 03/04/2015
6789 12501 150.00 01/03/2015
etc
Tran_Doc_No
Inv_No GL_Code Reg_No Type
1234 11500 ABC 122 M
1234 11500 ABC 122 F
1234 11500 ABC 122 R
2345 11500 ABC 124 F
3456 11501 ABC 121 M
3456 11501 ABC 121 F
3456 11501 ABC 121 R
4567 12500 ABC 122 M
5678 12500 ABC 124 M
5678 12500 ABC 124 R
6789 12501 ABC 127 M
Most of the records in the Tran_Doc_No have multiple lines, there is much more info which does differ line by line but the only info I need from this table is the Reg_No
The only GL_Codes that I need in the query are 11500 and 12500
I have looked to I need to achieve the following
Reg_No AR_Inv_No AR_Amount AR_Date AP_Inv_No AP_Amount AP_Date
ABC 122 1234 500.00 01/01/2015 4567 350.00 02/05/2015
ABC 124 2345 300.00 01/02/2015 5678 275.00 03/04/2015
I do hope that I have explained this accurate enough, if not please ask.
Kind regards,
Keith
Open in new window