Thursday, September 29, 2011

Oracle EBS: Linking GL Lines to AP Invoice Distributions in R12

This blog post gives some guidance as to how to link a GL Journal line in Oracle R12 back to its associated AP Invoice Distribution.

NOTE: That this applies ONLY to R12 data - you will not be able to link old (pre-migration) data using this method you will need to have one linking rule for old records and one for new. Just another reason to do a reimplementation!

As always the easiest way to look at this is with a picture;

The image is click-able for a larger view.

The linking goes as follows;

GL_JE_Lines > XLA_AE_Lines > XLA_Distribution_Links > AP_Invoice_Distributions_All

In our Oracle implementation this didn't end up with a 1:1 relationship (which we had in R11) mainly due to the changes in the way AP invoices are stored (R11: Invoice + Distributions, R12: Invoice + Invoice Lines + Distributions).

Looking at a specific example from our R12 test system for journal “4673291 Purchase Invoices GBP”. This journal contains 5 GL records;

Looking at records in XLA_AE_Lines we also have 5 records;

But when we move on to the next table (XLA_DISTRIBTUION_LINKS);

We now have 6 lines (line 2 having been split into two).

We solved this problem but automatically populating a field in the GL with direct 1:1 linking information (hopefully I can add this as another Knoll).


Kassem Nasser said...

I am interested to know how did you solve the link to be 1:1


Andy Pellew said...

We created a number flexfields on the GL Line (GL.GL_JE_LINES) into which we stored the linking information (Invoice_ID, Invoice_Distribution_ID, PO_Header_ID, Transaction_Id, etc) for where the line was linked to outside the GL.

For invoices we followed the rule that we would record as much information as we could. For example if a line in the GL directly linked to an invoice distribution we would record that, if the line linked to multiple invocie distributions (i.e. tax) then we'd just record the invoice_id and leave the distributions blank.

It's not perfect but it works for us.

Of course there is a process running every couple of hours to keep this up to date.

ja_n said...

That's a helpful post, thanks.

Joel Orta said...

Thanks, Thanks, Thanks!