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?
The link should be LIKE
FROM OFPR T0
JOIN OBGT T1 ON T1.AbsID=T0.AbsEntry - 24