Invoice, POs and MRs from GL

[nhsa]

Hi...
I have a report that shows the actual YTD , Budget YTD , Encumbrance YTD.

I want to create another report that list the invoices data from GL since they are represent the Actual YTD figure in the first report.

I tried to list the data from GL_je_lines table , but these amounts are period amounts only, How can I list the invoices with YTD amount????

I have the same problem for MR and PO report, they should match the figures on the first report , but it's not becasue of the calculation problem.

Can any body help me on this , please it's urgent....

Thanks in advance

 

[mdwyer]

I don't understand. GL_JE_LINES is transaction amount, not period. If you want the total for a period, you select and summarize by Period_Name. If you want Year-to-Date, you select and summarize by the year portion of Period_Date.

 

[nhsa]

Hi and thanks fro your reply,I have one comments regarding calculating YTD field, now from gl_balances table I used the opining balances with the period amount to calculate YTD , now from gl_je_lines , I will use only period amount,How can I check if the values I calculate are correct? I mean I will have different figures between these 2 reports, the YTD actual from gl_balances table will be different than the actual YTD from gl_je_lines table(invoices), so how can I check if what I do is correct?

 

[mdwyer]

The Entered_Dr and Entered_Cr in GL_JE_LINES are transaction amounts that are also posted to GL_BALANCES for the same period. YTD totals from GL_JE_LINES for Period_Num 1 through 'n' should be the same as Begin_Balance_Dr/Begin_Balance_Cr plus Period_Net_Dr/Period_Net_Cr in GL_BALANCES for Period_Num 'n'.

A couple of things to consider:
GL_JE_LINES.Status = 'P' means Posted.
GL_JE_HEADERS.Actual_Flag and GL_BALANCES.Actual_Flag = 'A' means Actual.
(You can join GL_JE_LINES and GL_JE_HEADERS on JE_Header_ID.)