Skip to Content
avatar image
Former Member

Budget

Hi

I'm busy developing a Qlickview financial model for one of our clients. In the profit and loss report I have to show Actual vs Budget. The budget is loaded in SAP Business One and I need to create a view in SQL from the data in the budget tables OBGT and BGT1 which links to the financial periods in SAP Business One and the Journals (JDT1).

The problem is that there is no clear link between the budget tables and the OFPR table containing the financial periods. In BGT1  you have a row number (Line_ID) from 0 to 11 with 0 being the 1st month of the financial year (in this case March) and 11 being the last month of the Financial Year (Feb). Also in OBGT you have Start of Fiscal year (FinancYear) which will be 2012/03/01 for all the rows.

How does SAP Business One link the data from these tables? For eg. Turnover - Actual vs Budget when you run the Profit and Loss Statement Budget Report for June 2102, I would imagine the link will be between JDT1 and OFPR on FinncPriod = AbsEntry for Actual but how is the budget linked to a specific period?

Please help.

Regards

Danie

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

1 Answer

  • Best Answer
    avatar image
    Former Member
    Jun 14, 2012 at 12:49 PM

    Hi Danie,

    You need find the relationship between OBGT AdsID and OFPR AbsEntry.

    Thanks,

    Gordon

    Add comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      Hi Gordon,

      I was having the same issue as Danie but when I tried your solution it did not work for me.  Instead of having to take 24 from OFPR.AbsEntry I had to take 6.

      Whilst this did work, when I tried the same query on another database, I had to change the value from 6 to something else. After a bit more research I was able to find a link between OFPR & OBGT, it was through the OACP table. The FROM section of my query is below:

      FROM OBGT T0 INNER JOIN

      OACP T1 ON T0.FinancYear = T1.FinancYear INNER JOIN

      OFPR T2 ON T1.PeriodCat = T2.Category

      Regards,

      Hans