SQL Query to get Fixed Asset details from SAP Business one Database

Following are the SQL Query to get FA details from SAP Business one database, I hope this below query will help you to get back the details from SAP Business one  Fixed Asset Module.

select

oitm.itemcode[FA Code],
oitm.itemname[FA Name],
oitm.frgnname[Foreign Name],
OBPL.BplName[Branch],
OACS.Code[Asset Class Code],
OACS.Name[Asset Class Name],
OITB.ItmsGrpNam[Item Group],
convert(varchar(10),oitm.capdate,103)[Capatilization Date],
ITM7.PeriodCat[Fiscal Year],
ITM7.DprArea[Depreciation Area],
convert(varchar(10),ITM7.DprStart,103)[Depreciation Start Date],
convert(varchar(10),ITM7.DprEnd,103) [Deprecitaion End Date],
ITM7.UsefulLife,
ITM7.RemainLife,
ITM7.DprType[Depreciation Type],

APC.APC as 'Acquisition and Production Cost',

--Dep.dep as 'Accum Dep',

--APC.ordpacc as 'Accum Dep2',

apc.apc-(dep.dep+apc.ordpacc)[Net Book Value],

dep.dep+apc.ordpacc as 'Ordinary Depreciation'


from oitm

inner join (select itemcode, sum(APC) as APC, sum(ordpacc) as ordpacc from itm8 group by Itemcode)APC on APC.itemcode=oitm.itemcode

inner join (select itemcode, sum(orddprplan) as dep from odpv group by itemcode)DEP on DEP.itemcode=oitm.itemcode

inner join OACS on OITM.AssetClass=OACS.Code

Inner Join OBPL on OACS.BPLId=OBPL.BPLId
inner join OITB on OITM.ItmsGrpCod=OITB.ItmsGrpCod
Inner Join ITM7 on OITM.ItemCode=ITM7.ItemCode

where oitm.itemtype='F'