0FI_GL_1 and 0FI_GL_4

[Former Member]

Hi Everyone,

We are currently loading with 0FI_GL_1. We now realize we need the cumulated balance at a plant level for specific GL Accounts. 0FI_GL_1 does not have plant, but 0FI_FL_4 does. Problem is GL_1 has cumulated balance and GL_4 does not. I noticed that GL_1 reads from table GLT0 while GL_4 reads from BSEG. The documentation states GL_1 is for transaction while GL_4 is direct line items.

Comparing extracted data from GL_1 and GL_4...none of the data matches.

Any ideas on the above?

Thanks, Mark.

[Former Member · Accepted solution]

it doesnt match because in GLT0 you do have a cumulated balance and B_SEG has period end balance. If you add up the numbers from the opening balance to the current period end balance numbers would match.

[Former Member · Accepted solution]

Hi,

Can you elaborate on specifics?

Are you saying to use GL_1 to load opening balance by 2006000 (for example), push this to the Cube and then load the Cube with GL_4 and then add opening balance to period end...how would you do this?

Thanks.

[Former Member · Accepted solution]

Hi,

How about enhancing the DS 0FI_GL_1 for plant.But this is possible if and only if PLANT field is avialable in GLT0 table.

And I think, we can get Cumulative Balance from the datatarget which gets the data by 0FI_GL_4. For ex: If we need CB of a plant on 31.DEC.2006, then we will take 2 Restriction KF and one formula.

like below:

1) Take 0DEBIT into columns restrict with POSTINGDATE<= 31.12.2006

2) Take 0CREDIT into columns restrict with POSTINGDATE<= 31.12.2006

3) Create formula like column1-column2.

With rgds,

Anil Kumar Sharma .P

Message was edited by:

Anil Kumar Sharma

[Former Member · Accepted solution]

yeah thats correct - get the opening balance from GLT0 and going forward get the period end balances from GL4. And on the reporting stand point you can always cumulate the balance just the way Anil has told you ... restrict it with # to posting period.. that would give u the cumulative balance.

[Former Member]

Hi Anil and Voodi,

A follow up:

First, when I load FIGL_C01 by period 2006000 it brings 0 records?

Second, having FIGL_C01 loaded (which has cumulative balance) and having FIGL_O02 loaded (line item), my debit's and credit's match perfect in both targets, but if I perform debit - credit in my ODS it doesn't match the balance in my Cube. debit - credit in my ods matches my debit/credit amount in my ods.

How is the cumulative balance calculated in the cube FIGL_C01?

[Former Member]

First, when I load FIGL_C01 by period 2006000 it brings 0 records?

See if the previous year has been closed or not. Do you see any records for the same in rsa3? Better ask the finance guy if the numbers look good or check urself in the GLT0?

Second, having FIGL_C01 loaded (which has cumulative balance) and having FIGL_O02 loaded (line item), my debit's and credit's match perfect in both targets, but if I perform debit - credit in my ODS it doesn't match the balance in my Cube. debit - credit in my ods matches my debit/credit amount in my ods.

Yeah they wouldn’t match as the cube contains the cumulated balances and the ODS the period end balances. If you want to match the balances, create a query and restrict it from # to period and check the balances with the ODS and cube (GLT0), they would match for that particular year.

How is the cumulative balance calculated in the cube FIGL_C01?

The cumulative balance is not calculated in the cube rather the balance is passed down from the GLT0 table. And for the line items you can cumulate it on the front end.

Message was edited by:

voodi

[Former Member]

Hi Voodi,

To summarize one last time...

I ran RSA3 (in addition to my BW loads for GL_1) with period 2006000 and 2007000 and both are 0 records. I assume I have to get the period opened from the finance people to get an opening balance? Please confirm and anything special as in open period and do they have to create any transactions, otherwise it will still be 0 records?

I create a copy of cube FIGL_C01 and add, for example, local debit, credit and debit/credit currency fields (key figures). Load opening balance from GL_1 and then start loading GL_4 daily. In my query now I create a formula that adds opening balance (0BALANCE) + debit/credit local currency to each period I want to see a balance for each period?

Can you clarify "create a query and restrict it from # to period"

Can you clarify "check balances with ODS and Cube" Are you saying using a multiprovider/infoset?

Thanks so much for taking the time to explain. I will award full points.

Thanks, Mark.

[Former Member]

I ran RSA3 (in addition to my BW loads for GL_1) with period 2006000 and 2007000 and both are 0 records. I assume I have to get the period opened from the finance people to get an opening balance? Please confirm and anything special as in open period and do they have to create any transactions, otherwise it will still be 0 records?

As I said earlier, once the year is closed there would be a closing balance (2005)/opening balance (2006) posted to the GLT0. as you said you are getting 0 records, ask the functional guy where are you supposed to get the opening balance (the amount of history the business would like to have – 2003, 4 or 5) from GL or Special Ledger? If it’s SL, you would need to create a DS. I can let u know the steps to create if that would be the case.

I create a copy of cube FIGL_C01 and add, for example, local debit, credit and debit/credit currency fields (key figures). Load opening balance from GL_1 and then start loading GL_4 daily. In my query now I create a formula that adds opening balance (0BALANCE) + debit/credit local currency to each period I want to see a balance for each period?

Yeah what you did is correct. First load the opening balance for any particular year, then start loading the line item data – posting for period end balances. You need not create a formula, you need to do a selection in structure, restrict period as range from # to posting period(variable), restrict the fiscal year and debit/credit kf. That would give you the cumulated balance of that period for that year. Hope I’m clear. If not, just blindly create a query on top of ur custom cube that you built with the selections and gl account in the rows and start comparing the numbers with R/3.

Can you clarify "create a query and restrict it from # to period"

See above answer

Can you clarify "check balances with ODS and Cube" Are you saying using a multiprovider/infoset?

Don’t worry abt that .. the above said steps would be sufficient to tally the numbers.

Please ask your functional guy to reconcile the numbers between the BI and R/3

[Former Member]

I'm with you on everything you said with the exception of the selection.

When you say...# to posting period(variable) I think I'm just misunderstanding you. By misunderstand I mean # means not assigned to me...

Do you mean create a selection on the below...

1. Restrict with a posting period variable (from - to select option for example to see balances for several posting periods at a time or just single value to see just balance for 1 specific period)

2. Restrict by fiscal year (or could have the user put in a year by variable and see balance by several years or just one...depending on setup of variable or restriction itself)

3. Restrict by debit/credit key figure

If this is correct...how does 0BALANCE come into play here? I have this updated in the cube for opening balance, in addition to debit/credit...but how does the restriction above know what the opening balance is?

I think I'm just missing something very stupid and obvious...

Again, thanks so much for the explanations.

Best Regards, Mark.

[Former Member]

there would be no value for # (unassigned) as such in ur GL cube, the unassigned value (its always the opening balance for ur GL) is derived from 0balance that you load as the opening balance(as that would be a one time gig, loading the opening balance and we dont load opening balance for every year. we just load the opening balance from the point where the users want the history from). the system calculates what the opening balance is during the query run time and cumulates it with current balances.

[Former Member]

Sorry, I don't understand your last explanation and how debit/credit reads 0balance.

I checked with our Finance guys here and they say that the only open period is 2007. Loading GL_1 with any period (2002/000.....2006/000 always brings 0 records).

Is this something you have accomplished before. We checked OB52 for what period was currently open. Only 2007 was for 'S' G/L Accounts.

I hate to keep posting this to everyone...sorry

[Former Member]

Does anyone know how to get an opening balance for 0FI_GL_4 ODS so I can push summarized data to a Cube?

Comparing GL_1 to GL4 doesn't match because GL_1, appears to have a starting balance?

Thanks.

[Former Member]

Hi,

Check the cube 0PCA_C01 is helpful to you here.

http://help.sap.com/saphelp_nw2004s/helpdata/en/ff/686a373638445ce10000009b38f842/frameset.htm

With rgds,

Anil Kumar Sharma .P

[Former Member]

Hi Anil,

This was the first thing I thought of (we are in fact loading this), but the Finance user stated that data doesn't look correct.

Any other thoughts?

Thanks, Mark.