Track all DR lines and CR line in GL for an invice

[jairam.apps]

Hi all,

Please help me this issue.


>> For local financial reporting, it is necessary to know corresponding account(s) of each journl line.
>> Is there any reference field on journal line to record related lines (Dr and Cr lines) from one subledger record?
>> E.g. (AP) Invoice post multiple journal lines to GL, will the AP invoice number/document number kept in GL tables by then we can track all lines for an invoice/payment?

Steps to reproduce:
******************
>> Create some invoices and account in AP and transfer to GL. Journal includes mutliple debit and mutliple credit lines. There is no data to link related Dr/Cr lines togather for particular invoice/payment.

>> My doubt is...Is there any option to track all the journal lines (DR & CR)in GL for an invoice or a payment.

>> I knwo about the drill down.... But here I want to separate the journals, based on the each accounting event. like for each invoie and each payment.


Thanks and Regards,

Jayaram.

[jairam.apps]

Hi friends,

Can somebody please provide your inputs on the above.


Thanks in advance.
Jayaram.

[oteixeira]

Hello.

This is for EBS 11.5.0.2.
For an Invoice:

Code: Select all

select l.reference_5 InvNr, 
l.reference_10, 
cc.segment2 account, 
l.accounted_dr, 
l.accounted_cr
from gl_je_lines l, gl_code_combinations cc
where cc.code_combination_id = l.code_combination_id
and l.reference_2 = <your invoice_id>
and l.reference_5 = <your invoice_num>

Note that REFERENCE_2 field contains the INVOICE_ID. If you know this information the query will be very fast. If you only know the invoice number than the query will take a long time to run and it will need to be modified.

For a payment:

Code: Select all

select l.reference_3 ChkId, 
l.reference_10, 
cc.segment2 account, 
l.accounted_dr, 
l.accounted_cr
from gl_je_lines l, gl_code_combinations cc
where cc.code_combination_id = l.code_combination_id
and l.reference_3 = <your check_id>
and l.subledger_doc_sequence_value = <your doc_sequence_value>

Note that REFERENCE_3 contains the CHECK_ID and SUBLEDGER_DOC_SEQUENCE_VALUE is indexed. If you don?t know this values the query may take too long and return more values than you want.

Octavio