Hi All,
I am venkadeswara from India - chennai State, having 1+ years of experience in SAP Business One.
Here I am posting some queries reports which may helpful for newcomers.
Outgoing - accounts
SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[Address],T1.[AcctCode], T1.[AcctName], T1.[SumApplied], T0.[Comments], T0.[JrnlMemo], T0.[CashSum], T0.[CheckSum], T0.[TrsfrSum] FROM [dbo].[OVPM] T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum WHERE T0.[DocDate] between '[%0]' and '[%1]'
Outgoing - payments
SELECT T0.[DocNum], T0.[DocDate], T0.[DocDueDate], T0.[CardCode], T0.[CardName], T0.[Address],T1.[AcctCode], T1.[AcctName], T1.[SumApplied], T0.[Comments], T0.[JrnlMemo], T0.[CashSum], T0.[CheckSum], T0.[TrsfrSum] FROM [dbo].[OVPM] T0 INNER JOIN VPM4 T1 ON T0.DocEntry = T1.DocNum WHERE T0.[DocDate] between '[%0]' and '[%1]'
Sales Incentive without stocktransfer & service
SELECT T0.[DocDate], T5.GroupName, T6.SeriesName, T4.[CardName],T4.[Address],T4.[City],T0.[DocNum], T3.ItmsGrpNam, T2.ItemName, case when T1.Linetotal is not null then sum(T1.Linetotal )-(Select sum(T0.DiscSum)/count(*) from inv1 where Docentry=T0.Docentry) end as 'Base Amount',((T0.DocTotal-T0.[PaidToDate])/(select count(*) from inv1 where Docentry=T0.Docentry)) as 'Balance Due' FROM [dbo].[OINV] T0 INNER JOIN [dbo].[INV1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod inner join OCRD T4 on T4.CardCode=T0.Cardcode inner join OCRG T5 on T4.GroupCode = T5.GroupCode inner join NNM1 T6 on T6.Series=T0.Series
WHERE T6.[SeriesName] NOT LIKE 'ST%'
and T0.[DocDate] between [%0] and [%1] group by T5.GroupName,T4.[CardName], T0.[DocNum], T0.[DocDate], T0.DocTotal,T1.Vatsum,T0.[PaidToDate], T4.[Address],T4.[City], T3.ItmsGrpNam,T2.ItemName,T1.Linetotal,T0.DocEntry,T6.SeriesName
union all
SELECT T0.[DocDate], T5.GroupName,T6.SeriesName, T4.[CardName],T4.[Address],T4.[City], T0.[DocNum], T3.ItmsGrpNam, T2.ItemName, case when T1.Linetotal is not null then -sum(T1.Linetotal)-(Select sum(T0.DiscSum)/count(*) from rin1 where Docentry=T0.Docentry) end as 'Base Amount',-((T0.DocTotal-T0.[PaidToDate])/(select count(*) from rin1 where Docentry=T0.Docentry)) as 'Balance Due' FROM [dbo].[ORIN] T0 INNER JOIN [dbo].[RIN1] T1 ON T0.DocEntry = T1.DocEntry INNER JOIN OITM T2 ON T1.ItemCode = T2.ItemCode INNER JOIN OITB T3 ON T2.ItmsGrpCod = T3.ItmsGrpCod inner join OCRD T4 on T4.CardCode=T0.Cardcode inner join OCRG T5 on T4.GroupCode = T5.GroupCode inner join NNM1 T6 on T6.Series=T0.Series
WHERE T6.[SeriesName] NOT LIKE 'ST%'
and T0.[DocDate] between [%0] and [%1] group by T5.GroupName,T4.[CardName], T0.[DocNum], T0.[DocDate],T0.DocTotal,T1.Vatsum,T0.[PaidToDate], T3.ItmsGrpNam,T2.ItemName,T1.Linetotal,T0.DocEntry,T6.SeriesName, T4.[Address],T4.[City]
