How to do query of fixed asset in SAP Business One 9.2 PL10?

[former_member183402]

Hi all,

I tried to do query of fixed asset but I'm not getting real result, below is my query

select distinct OITM.ItemCode,OITM.ItemName,case when ITM8.APC=0 then ACQ1.LineTotal else ITM8.APC end as APC,OLCT.Location,OITM.AssetClass,OITM.CapDate,ITM8.OrDpAcc,
case when ITM8.OrDpAcc=0 or OITM.CapDate>='2018-07-01'  then (select sum(ODPV.OrdDprAct) from ODPV where ODPV.ItemCode=OITM.ItemCode group by ODPV.ItemCode) else ITM8.OrDpAcc end as 'Accumulated Depreciation',
(case when ITM8.APC=0 then ACQ1.LineTotal else ITM8.APC end) - (case when ITM8.OrDpAcc=0 or OITM.CapDate>='2018-07-01'  then (select sum(ODPV.OrdDprAct) from ODPV where ODPV.ItemCode=OITM.ItemCode group by ODPV.ItemCode) else ITM8.OrDpAcc end) as 'Net Book Value'
,ITM7.UsefulLife,ITM7.PeriodCat,ITM8.PeriodCat from ITM7 inner join ITM8 on ITM7.ItemCode=ITM8.ItemCode inner join ACQ1 on  ITM8.ItemCode=ACQ1.ItemCode inner join OITM on ACQ1.ItemCode=OITM.ItemCode left join OLCT on OLCT.Code=OITM.Location
where  ITM8.PeriodCat='2018_2019' and oitm.itemtype='F'

Please anyone can help me to correct this error

[MD1]

According to your need to update "TransType"

[MD1]

SELECT OITM."ItemCode", OITM."ItemName", OITM."FrgnName", OITM."CapDate", nc.APC AS "Acquisition Cost", Dep."dep" +
"Spdep" AS "Accum Dep", "SALvl" AS "Salvage", dm."rtr" AS "RetirementValue", nc.APC - (Dep."dep" + "Spdep") -
dm."rtr" AS "Profit/Loss"
FROM OITM
INNER JOIN (SELECT "ItemCode", SUM("OrDpAcc") AS "ordpacc"
FROM ITM8
GROUP BY "ItemCode") AS APC ON APC."itemcode" = OITM."ItemCode"
INNER JOIN (SELECT "ItemCode", SUM("OrdDprPost") AS "dep", SUM("SpDprPost" + "OrdDprPst1") AS "Spdep"
FROM ODPV
GROUP BY "ItemCode") AS DEP ON DEP."itemcode" = OITM."ItemCode"
INNER JOIN (SELECT "ItemCode", SUM(T0.APC) AS "APC"
FROM "FIX1" T0
WHERE t0."TransType" = '110'
GROUP BY "ItemCode") AS nc ON nc."itemcode" = OITM."ItemCode"
INNER JOIN (SELECT "ItemCode", SUM(a0."SalvageVal") AS "SALvl", SUM("TransAmnt") AS "a"
FROM "FIX1" a0
INNER JOIN "OFIX" a1 ON a1."AbsEntry" = a0."AbsEntry"
GROUP BY "ItemCode") AS d ON d."itemcode" = OITM."ItemCode"
LEFT OUTER JOIN (SELECT "ItemCode", SUM(T0."TransAmnt") AS "Rtr"
FROM "FIX1" T0
INNER JOIN "OFIX" T1 ON T1."AbsEntry" = T0."AbsEntry" AND t0."TransType" = '210'
GROUP BY "ItemCode") AS dm ON dm."itemcode" = OITM."ItemCode"
WHERE OITM."ItemType" = 'F';

[former_member183402]

Hi Mohd,

Your query is returning only 4 lines, I have more than 400 assets.

Is there any problem in above query?

[asalah2019]

Are you have query to get details of assets like details of asset history sheet in sap b1 group by assets classes only