1 2 3 16 Previous Next

SAP Business One Application

235 Posts

Thanks scn  Given Strength to Write a Blog  DATABASE ARE NOT APPEARED ON CLIENT LOGIN List

MY 1st and fore Most Master is scn  because here only I Strengthen,. Thanks scn

Come to Solution is ….

I Noticed that so many  are asked Related this Issue .for  that I’m Going to Give Clear Solution with pictures.   Based on this thread  http://scn.sap.com/thread/3668654 (we Discussed more there)

Cause is System Variables ..

1step:

 

Open SAP Business One In stallion path (most of the Installed in C:\ Program Files\SAP\SAP Business ONE...or they may own choice)

B1-install-path.jpg

 

 

2)Step:

Under the Conf folder take backup and delete this folder (b1-config-path )..(text file) then Login SAP B1 Client (Enter Manually as follow )

 

 

 

DB Login.jpg

 

After Log in Data Base if we checked the under Conf folder it will create like some binary files ( Don't Afraid Nothing will happen  )

Conf-path.jpg

 

3) Step

Log in into according to your Company name, once successful Log ON then go to  file and Exit  ...

then Check it it will Work fine ...

 

 

 

 

Hi all,

 

In our days the sales guys are more and more aggressive therefore I had a challenge to create an additional discount at the line level in the sales documents. For example, we have some items with 30% discount and for certain customers the sales reps wanted to give an additional 6% but they wanted the customer to have on their Invoices 30+6% and not 34.20% the real discount value.

I was searching and I didn't find anything like this and that's the reason of this post.

 

This was my approach:

- I created a table (Discounts) and added 2 fields (Discount and Mask)

- In the Marketing Documents - Title I added a field (Discount) that receives the value of the discount in the BP Master data

- In the Marketing Documents - Rows I added a field (Mask) that will have the value that we want to print in the documents.

- In the Business Partners Master Data I added a field (Discount) that will have the additional discount of the customer.

Discounts Fields.jpg


In the Business Partner Master Data the user can insert the additional discount if the customer has a discount.

Customer Screen.jpg

In the discounts table, we have a relation between the discount and the discount mask that we want to be printed in the documents.

Discounts values.jpg

In the Sales Order form I used 3 formatted search's to achieve the correct result.Sales Order FMS.jpg

1. FMS to get the customer discount from the Business Partner Master Data.

The FMS runs after typing the customer code.

Select U_Discount From OCRD Where OCRD.CardCode = $[$-4.0.0]

 

2. FMS that calculates the correct discount. The FMS runs and if the customer doesn't have a special price, gets the discount from the Discount Groups - Manufactures. If the discounts are by Item Group or by Properties the code has to be changed.

The FMS is triggered after the item code.

 

If IsNull((SELECT T0.[Discount] FROM OSPP T0 WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0]), 99) = 99

If (SELECT T1.[Discount] FROM OEDG T0  INNER JOIN EDG1 T1 ON T0.AbsEntry = T1.AbsEntry inner join OITM T2 on T2.FirmCode = T1.ObjKey WHERE T0.[ObjCode] = $[$4.0.0]  and  T1.[ObjType] = '43' and t2.itemcode = $[$38.1.0]) > 0
Select  (SELECT T1.[Discount] FROM OEDG T0  INNER JOIN EDG1 T1 ON T0.AbsEntry = T1.AbsEntry inner join OITM T2 on T2.FirmCode = T1.ObjKey WHERE T0.[ObjCode] = $[$4.0.0]  and  T1.[ObjType] = '43' and t2.itemcode = $[$38.1.0])
+ IsNull(((100 - (SELECT T1.[Discount] FROM OEDG T0  INNER JOIN EDG1 T1 ON T0.AbsEntry = T1.AbsEntry inner join OITM T2 on T2.FirmCode = T1.ObjKey WHERE T0.[ObjCode] = $[$4.0.0] and  T1.[ObjType] = '43' and t2.itemcode = $[$38.1.0])) * $[ORDR.U_Discount.0])/100, 0) from OCRD T1  WHERE T1.[CardCode]  = $[$4.0.0]

If IsNull((SELECT T0.[Discount] FROM OSPP T0 INNER JOIN SPP1 T1 ON T1.ItemCode = T0.itemcode and T1.Cardcode=T0.Cardcode  WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0] and $[$10.0.Date] > T1.FromDate and $[$10.0.Date] < T1.ToDate), 99) = 99
If IsNull((SELECT T0.[Discount] FROM OSPP T0 WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0]), 0) <> 0
SELECT T0.[Discount] FROM OSPP T0 WHERE T0.[CardCode] = $[$4.0.0] and  T0.[ItemCode]= $[$38.1.0]

 

3. FMS that gets the discount mask.

The FMS is triggered after the discount%.

If ($[$38.15.Number]) > 0

SELECT T0.[U_Mask] FROM [dbo].[@DISCOUNTS]  T0 WHERE T0.U_Discount =($[$38.15.Number])

 

This is how it looks like after typing a customer and some items:

Sales Order data.jpg

 

Then I made a change on the layouts to print the Disc.Mask if it's filled otherwise it prints the discount.

 

I hope it helps someone and I'm looking forward for your comments or questions.

 

Best regards,

Augusto Silva

In this blog I would like to share my thoughts on how to become successfull with SAP Business One in the Cloud. It takes some steps to get up and running which mostly starts with a cloud infrastructure but on the other hand, don't under estimate the changing sales cycle that comes automatically when selling cloud applications like SAP Business One.

 

In my role as SAP Business One Cloud Specialist I get in contact with a lot of partners with questions regarding Cloud set-up and infrastructure based on HANA, this but also questions about a changing business model when selling SAP Business One in the Cloud drives me to inform you in serveral topics about what is relevant and which questions will come on your path when taking the next step: 'Being successfull with SAP Business One HANA in the Cloud'.

I will address serveral topics which are relevant when talking about cloud, not only the infrastuctural set-up but also sales cycles that will be affected.

 

What drives you to go for cloud?

Personally I was triggered a couple of years ago when Rinse Tamsma (former Vice-President Global Sales Business One) kicked off the B1 Summit in Kopenhagen. He showed a graph about the SME market and demand for ERP solutions, there was a differentation between On-Premise and Cloud. The percentage of requests in the market for Cloud was more than 52%. I was working for a SAP Business One partner at that moment and responsible for sales, my conclusion was that I had contact with less than 50% of the market because we were not offering SAP Business One in the cloud. I could sell more but did not have the 'cloud solution' and price model. That more or less frustrated me and brought me at the point to think about cloud and what it could do for our business.

 

I will explain in this blog the steps to take and questions which are relevant when going for cloud, based on my experience but also based on the request from SAP Business One Partners. Doing this by mainly by explaining the steps and sharing information which is already available on SCN.

 

This blog will be updated from time to time as I will try to add new information every week.

The following topics will be covered in this blog.

 

  • A cloud business model
  • Cloud infrastructure
    • HANA Sizing
    • High Availability on HANA
    • Cloud Control Center
  • Sales and marketing in a new perspective

 

Finally SAP Partners get the best opportunitie to become successful in cloud as they have the contract with the end customer. Look at this article which explains that SAP Business One Partners a in a good position to sell Cloud!

 

 

About me:

 

I work at SAP and working with SAP Business One for the last 8 years in a variety of roles. Currently I look after SAP Business One Cloud and spend much of my time working with partners to help them take advantage of the new and exciting opportunities that have emerged since it's release. You can follow me on Twitter @CB_Rijssen if you want to hear more!

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 'DSTAR%'

and T6.[SeriesName] NOT LIKE '_ST%'

and T3.[ItmsGrpNam] NOT LIKE 'Service'

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 'DSTAR%'

and T6.[SeriesName] NOT LIKE '_ST%'

and T3.[ItmsGrpNam] NOT LIKE 'Service'

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]


Bharathiraja J

Dynamic Pivot Query

Posted by Bharathiraja J Aug 25, 2015

Dear All,

 

I'm Bharathiraja, having 2 years of experience in SAP B1 functional.

 

Here i am posting some queries using dynamic pivot.

 

1.Total Sales Query with respective to customer and Item group.

 

DECLARE @listCol VARCHAR(8000)

DECLARE @Query VARCHAR(8000)

DECLARE @OINV VARCHAR(8000)

DECLARE @ORIN VARCHAR(8000)

DECLARE @From varchar(15)

DECLARE @To varchar(15)

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

 

 

SET @OINV =  isnull( STUFF((Select  ',' + convert(varchar(10),Docentry) from OINV

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SET @ORIN =  isnull(STUFF((Select  ',' + convert(varchar(10),Docentry) from ORIN

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SELECT  @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB

ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('') ), 1, 2, '') + ']'

 

SET @Query = 'Select *  from (Select T0.CardName,T4.ItmsGrpNam,sum(T1.LineTotal) as Sal from

OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@OINV+')

group by T0.CardName ,T4.ItmsGrpNam

 

union all

 

Select T0.CardName,T4.ItmsGrpNam,sum(-T1.LineTotal) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OFPR T2 on T2.AbsEntry = T0.FinncPriod

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T4.ItmsGrpNam

 

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

 

Your Result will be like this

Total Sale.png

 

2. Total Sales Qty  with respective to customer and Item group

 

DECLARE @listCol VARCHAR(8000)

DECLARE @Query VARCHAR(8000)

DECLARE @OINV VARCHAR(8000)

DECLARE @ORIN VARCHAR(8000)

DECLARE @From varchar(15)

DECLARE @To varchar(15)

Set @From =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%0]',112)

Set @To =/* SELECT convert(varchar(15),Max(T0.DocDate),112) FROM OINM T0 WHERE T0.DocDate > */ convert(varchar(15),'[%1]',112)

 

SET @OINV =  isnull( STUFF((Select  ',' + convert(varchar(10),Docentry) from OINV

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SET @ORIN =  isnull(STUFF((Select  ',' + convert(varchar(10),Docentry) from ORIN

  where CANCELED <>'N' FOR XML PATH('')),1,1,'') ,0)

SELECT  @listCol = STUFF(( SELECT DISTINCT '],[' + ltrim((ItmsGrpNam)) from OITB

ORDER BY '],[' + ltrim((ItmsgrpNam)) FOR XML PATH('') ), 1, 2, '') + ']'

 

SET @Query = 'Select *  from (Select T0.CardName,T4.ItmsGrpNam,sum(T1.Quantity) as Sal from

OINV T0 Inner join INV1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@OINV+')

group by T0.CardName ,T4.ItmsGrpNam

 

union all

 

Select T0.CardName,T4.ItmsGrpNam,sum(-T1.Quantity) as Sal from

ORIN T0 Inner join RIN1 T1 on T0.DocEntry = T1.DocEntry

Inner Join OITM T3 Inner Join OITB T4 On T4.ItmsGrpCod = T3.ItmsgrpCod on T1.ItemCode = T3.ItemCode

Where convert(varchar(15),T0.Docdate,112) >= convert(varchar(15),'+@From+',112)

and convert(varchar(15),T0.Docdate,112) <= convert(varchar(15),'+@To+',112)

and t0.docentry not in ('+@ORIN+')

group by T0.CardName ,T4.ItmsGrpNam

 

) src

PIVOT (sum(Sal) for ItmsgrpNam IN ('+@listCol+')) AS pvt'

EXECUTE (@Query)

 

 

For above query you will get result like attached image

Total Qty.pngI

 

3.Stock in all the warehouse

 

declare @column as varchar(max)

declare @Query as varchar(max)

 

set @column= STUFF((Select '],['+whsname from OWHS FOR XML PATH('')),1,2,'') +']'

 

set @Query='select * from (select w.ItemCode,m.Itemname ,s.WhsName ,sum(w.OnHand) Stock

from oitw w inner join oitm m on m.itemcode=w.itemcode inner join OWHS s on s.WhsCode=w.WhsCode group by w.ItemCode,m.itemname ,s.WhsName) fg

Pivot (Sum(Stock) for

Whsname in ('+@column+')) AS pvtb'

EXECUTE (@Query)

 

For the above query you will get the following image result

Whs Stock.png

SAP Business One 9.1 PL08, version for SAP HANA has been released!

 

Details of this release can be found at the overview note: 2175812  - Overview Note for SAP Business One 9.1 PL08, version for SAP HANA

 

 

Important Notes


eLearning

Access the SAP Business One  9.1 Clips on YouTube or the SAP Business One Academy to learn about the new release.

 

SAP Business One HANA pages in SDN

 

SAP Business One on SAP HANA solutions

 

SAP Business One HANA topics

SAP Business One, version for SAP HANA Extensibility topics

Hi Friends,

 

I am facing the issue as per enclosed error while trying to upload .bfp file as received from bank as bank statement to be uploaded in SAP Business One for automatic bank statement processing.

 

Kindly guide me how to resolve the error.

 

With Regards,

 

Rajesh Rana

SP Samy

TDS Report

Posted by SP Samy Jul 28, 2015

Hi All

 

I attached here TDS query report it will helpful for new comers in SAP B1. PFA

 

Regards,

 

SP Samy

Balaji Sampath

TAX Queries

Posted by Balaji Sampath Jul 22, 2015

Dear All

 

I posted here some Accounting Tax Queries it will helpful for new comers in SAP Business One.

 

A/P Invoice Tax Wise Breakup

 

declare @todate as datetime declare @enddate as datetime  SELECT @todate= /*min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/ '[%0]' SELECT @enddate= /*max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/'[%1]' select DISTINCT a.Docnum as "SAP Invoice No" ,a.docdate AS " SAP Invoice Date" ,a.comments, a.Numatcard AS "Vendor Bill No" ,a.U_Date AS " Vendor Bill Date" ,a.cardcode as "Party Code" ,a.cardname as "Name of the Supplier" ,(Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin No], (SELECT SUM(Quantity) FROM PCH1 where docentry =a.docentry)as "Quantity" , (select sum(LineTotal) from PCH1 where docentry = a.docentry)as "Basic Value" ,(Select sum(taxsum) FROM PCH4 where docentry = a.docentry and statype=-90) as "BED" ,(Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=1) as "VAT" , (Select SUM(taxsum) FROM PCH4 where docentry = a.docentry and statype=4) as "CST" ,  A.VatSum as "Total Tax" ,(select sum(linetotal) from PCH3 where docentry = a.docentry) as 'Freight' ,  A.DocTotal as "Net Value"  FROM OPCH A left OUTER JOIN PCH3 B ON A.DOCENTRY = B.DOCENTRY left OUTER JOIN PCH4 C ON A.DOCENTRY = C.DOCENTRY left outer join PCH3 h on A.DocEntry = h.DocEntry left outer join PCH1 d on a.docentry = d.docentry left outer join crd7 e on a.cardcode = e.cardcode, oitm g left outer join ochp f on f.absentry= g.chapterid where a.Docdate >=@todate and a.Docdate <=@enddate and g.itemcode=d.itemcode ORDER BY A.DOCNUM

 

A/R Invoice Tax Wise Breakup

 

declare @todate as datetime declare @enddate as datetime  SELECT @todate= /*min(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/ '[%0]' SELECT @enddate= /*max(T0.DocDate) FROM OINM T0 WHERE T0.DocDate=*/'[%1]'  select DISTINCT a.Docnum as "Invoice No" ,a.Numatcard AS "Customer Reference" ,a.comments, a.cardcode as "Cust.Code", a.cardname as "Customer Name" ,(Select max (T2.TaxId2) from CRD7 T2 where T2.CardCode = a.CardCode) [Tin#],  a.docdate AS "Invoice Date" ,(SELECT SUM(Quantity) FROM INV1 where docentry =a.docentry)as "Quantity" , (select sum(LineTotal) from INV1 where docentry = a.docentry)as "Basic Value" ,(Select sum(taxsum) FROM INV4 where docentry = a.docentry and statype=-90) as "BED" , (Select SUM(taxsum) FROM INV4 where docentry = a.docentry and statype=1) as "VAT" , (Select SUM(taxsum) FROM INV4 where docentry = a.docentry and statype=4) as "CST" ,  A.VatSum as "Total Tax" ,(select sum(linetotal) from inv3 where docentry = a.docentry) as 'Freight' ,  A.DocTotal as "Net Value"  FROM OINV A left OUTER JOIN INV3 B ON A.DOCENTRY = B.DOCENTRY left OUTER JOIN INV4 C ON A.DOCENTRY = C.DOCENTRY left outer join INV3 h on A.DocEntry = h.DocEntry left outer join INV1 d on a.docentry = d.docentry left outer join crd7 e on a.cardcode = e.cardcode, oitm g left outer join ochp f on f.absentry= g.chapterid where a.Docdate >=@todate and a.Docdate <=@enddate and g.itemcode=d.itemcode ORDER BY A.DOCNUM

 

TDS Report Section Wise

 

SELECT T0.[DocNum], T0.[CardCode], T0.[CardName], T0.[DocDate],T0.[BaseAmnt], T0.[WTSum] as TDSAmount, T0.[DocTotal] FROM [dbo].[OPCH]  T0 INNER JOIN PCH5 T1 ON T0.DocEntry = T1.AbsEntry WHERE T0.[DocDate] > =[%0] AND  T0.[DocDate] < =[%1] AND  T0.[WTSum] > = '1' AND  T1.[WTCode] =[%2]

 

Regards

 

Balaji Sampath

Hi All,

I am Unnikrishnan from India-Kerala State, having 5+ years of experience in SAP Business One.

 

Here I am posting some queries reports which may helpful for newcomers.

 

1. Detailed Sales Analysis


Declare @FDate DateTime, @TDate DateTime, @CardCode Varchar (20), @ItmGrpN Varchar (100),  @ItemCode Varchar (100)

 

 

Select @FDate = min(Q.Refdate) from OJDT Q where Q.Refdate >='[%0]'

Select @TDate = max(R.Refdate) from OJDT R where R.Refdate <='[%1]'

Select @CardCode = Min(S.CardCode) from OCRD S where S.CardCode like N'%[%3]%'

Select @ItmGrpN = Min(T.ItmsGrpNam) from OITB T where T.ItmsGrpNam like N'%[%4]%'

Select @ItemCode = Min(U.ItemCode) from OITM U where U.ItemCode like N'%[%5]%'

 

 

--Select @FDate, @TDate, @ItmGrpN, @ItemName

 

 

Select 'AR Invoice'[Type]

,case A.DocType When 'I' then 'Item' else 'Service' end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When 'I' then E.ItemName else B.Dscription end [ItemName/Description]

,B.Quantity[Quantity]

,B.Price

,B.Rate

, B.Currency[Price Currency]

,B.LineTotal- (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotalLC],B.GrssProfit

,B.TotalFrgn- (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,B.GTotalFC

,Case When B.SlpCode<>'-1' then F.SlpName

  When B.SlpCode='-1' and A.SlpCode<>'-1' then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else '' end [Sales Person]

from OINV A

  Left Outer Join INV1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

 

 

Where A.Canceled = 'N'

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N'%[%2]%'

  and C.CardCode  Like '%[%3]%'

  and (G.ItmsGrpNam Like N'%[%4]%' or G.ItmsGrpNam is null)

  and (B.ItemCode Like '%[%5]%' or B.ItemCode is null)

 

 

 

 

 

 

 

 

Union All

 

 

Select 'AR Credit Note'[Type]

,case A.DocType When 'I' then 'Item' else 'Service' end[DocType]

,A.DocCur

,A.DocDate

, A.DocNum

,A.CardCode, C.CardName,D.GroupName[BP group]

,B.ItemCode

,Case A.DocType When 'I' then E.ItemName else B.Dscription end [ItemName/Description]

, Case when B.NoInvtryMv ='Y' then 0 else -B.Quantity end [Quantity]

,B.Price

,B.Rate

, B.Currency [Price Currency]

,-B.LineTotal+ (B.LineTotal*isnull(A.DiscPrcnt,0)/100)[LineTotal],-B.GrssProfit

,-B.TotalFrgn+ (B.TotalFrgn*isnull(A.DiscPrcnt,0)/100)[LineTotalFC]

,-B.GTotalFC

,Case When B.SlpCode<>'-1' then F.SlpName

  When B.SlpCode='-1' and A.SlpCode<>'-1' then (Select M.SlpName from OSLP M where A.SlpCode = M.SlpCode) else '' end [Sales Person]

 

 

from ORIN A

  Left Outer Join RIN1 B on A.Docentry = B.DocEntry

  Left Outer Join OCRD C on A.CardCode = C.CardCode

  Left Outer Join OCRG D on C.GroupCode = D.GroupCode

  left outer join OITM E on B.ItemCode = E.ItemCode

  Left Outer Join OSLP F on B.SlpCode = F.SlpCode

  Left Outer Join OITB G on E.ItmsGrpCod = G.ItmsGrpCod

 

 

Where A.Canceled = 'N'

  and A.DocDate >=@FDate and A.DocDate  <=@TDate

  and D.GroupName Like N'%[%2]%'

  and C.CardCode  Like '%[%3]%'

  and (G.ItmsGrpNam Like N'%[%4]%' or G.ItmsGrpNam is null)

  and (B.ItemCode Like '%[%5]%' or B.ItemCode is null)

 

 

Order BY 3

 

 

2. Sales Order to AR Invoice Tracking

 

Select A.DocNum[SO No], A.DocEntry [SO DocEntry], A.DocDate [SO DocDate], H.ItmsGrpNam, G.FrgnName, G.ItemCode, G.ItemName

  , D.DocDate [DC Date], D.DocNum [DC No], F.DocDate [Invoice Date], F.DocNum [Invoice No]

From ORDR A

  Inner Join RDR1 B on A.docEntry = B.DocEntry

  left Outer Join DLN1 C on C.BaseType = 17 and C.BaseEntry = B.DocEntry and C.BaseLine = B.LineNum

  left Outer Join ODLN D on C.docEntry = D.DocEntry and D.Canceled = 'N'

  left Outer Join INV1 E on E.BaseType = 15 and E.BaseEntry = C.DocEntry and E.BaseLine = C.LineNum

  left Outer Join OINV F on E.docEntry = F.DocEntry  and F.Canceled = 'N'

  Inner Join OITM G on B.ItemCode = G.ItemCode

  Inner Join OITB H on G.ItmsGrpCod = H.ItmsGrpCod

 

 

Where A.DocDate >=[%0]

  and A.DocDate <=[%1]

 

 

 

3. Approval Process tracking for AP Down payment request

 

SELECT T5.U_NAME "RequestedBy"

,T6."U_NAME" "Originator"

, T0."DocNum", T0."DocStatus", T0."DocDate"

,T13.U_Name [Authorized By]

 

 

FROM ODPO T0 

Left Outer Join ODRF T4 ON T0.[draftKey] = T4.[DocEntry] and T4.ObjType = 204

Left Outer Join OUSR T5 on T4.UserSign = T5.USERID

LEFT oUTER jOIN OUSR T6 on T0.UserSign = T6.USERID

Left Outer Join OWDD T11 on T11.ObjType = 204and T11.DocEntry = T0.DocEntry

Left Outer join  WDD1 T12 on T12.WddCode= T11.WddCode and T12.Status='Y'

LEFT oUTER jOIN OUSR T13 on T12.UserID = T13.USERID

 

 

WHERE T0."CANCELED"='N'

Order By T0."DocDate", T0."DocNum"

 

 

 

 

4. Comparision of AP Invoice Price with PO Price

 

 

SELECT  T0.DocDate,T0.NumAtCard,T0.DocNum,O1.CardName,T1.ItemCode, I1.ItemName,T4.Price [POrate],T1.Price [AP Invoice Rate],(T4.Price-T1.Price) Diff

FROM OPCH T0

inner join PCH1 T1 ON T0.[DocEntry]=T1.[DocEntry]

Inner Join OCRD O1 on T0.CardCode = O1.CardCode

Inner Join OITM I1 on T1.ItemCode = I1.ItemCode

 

inner join  PDN1 T2 ON T1.BaseType = 20 and T1.[BaseEntry]=T2.[DocEntry] AND T1.BaseLine = T2.LineNum

inner JOIN OPDN T3 ON T3.[DocEntry]=T2.[DocEntry]

 

inner JOIN POR1 T4 ON T2.BaseType = 22 and T2.[BaseEntry]=T4.[DocEntry] AND T2.BaseLine = T4.LineNum

inner JOIN OPOR T5 ON T4.[DocEntry]=T5.[DocEntry]

 

 

Where T0.DocDate >=[%0] and T0.DocDate <=[%1]

 

 

 

5. Stock Ledger

 

Declare @FDate DateTime, @TDate DateTime, @Whs Varchar (10)

Select @FDate = Min(AA.DocDate) from OINM AA Where AA.DocDate>=[%0]

Select @TDate = Max(AB.DocDate) from OINM AB Where AB.DocDate<=[%1]

Select @Whs = Max(AC.Warehouse) from OINM AC Where AC.Warehouse Like '%[%2]%'

Set @FDate = '[%0]'

Set @TDate = '[%1]'

 

 

 

 

;WITH STKLOG AS

(

Select A.ItemCode[Item_Code], B.ItemName[Item_Name],A.Warehouse[Warehouse_Code]

  ,0[OB_Qty]

  ,0[OB_Value]

  ,Sum(A.InQty)[ReceiptQty]

  ,Case When A.TransValue>0 then Sum(A.TransValue) else 0 end [ReceiptValue]

  ,Sum(A.OutQty)[OutQty]

  ,Case When A.TransValue<=0 then Sum(A.TransValue) else 0 end [OutValue]

  ,0[Cls_Qty]

  ,0[Cls_Value]

from dbo.OINM A

  Inner Join OITM B on A.ItemCode = B.ItemCode

Where A.DocDate>=@FDate and A.DocDate<=@TDate and A.Warehouse Like '%[%2]%'

Group By A.ItemCode, B.ItemName,A.Warehouse, A.TransValue

 

 

Union All

 

 

SELECT T0.ItemCode[Item_Code], T1.ItemName[Item_Name], T0.Warehouse[Warehouse_Code]

  ,Case When T0.DocDate <@FDate then Sum(T0.InQty-T0.OutQty) else 0 end [OB_Qty]

  ,Case When T0.DocDate <@FDate then Sum(T0.TransValue) else 0 end [OB_Value]

  ,0[ReceiptQty]

  ,0[ReceiptValue]

  ,0[OutQty]

  ,0[OutValue]

  ,Case When T0.DocDate <=@TDate then Sum(T0.InQty-T0.OutQty) else 0 end [Cls_Qty]

  ,Case When T0.DocDate <=@TDate then Sum(T0.TransValue) else 0 end [Cls_Value]

 

 

FROM OINM T0 

  INNER JOIN OITM T1 ON T0.ItemCode = T1.ItemCode

WHERE T0.DocDate <=@TDate and T0.Warehouse Like '%[%2]%'

 

 

GROUP BY T0.[ItemCode], T1.ItemName, T0.Warehouse, T0.DocDate

)

 

 

 

 

----

Select @FDate[From Date], @TDate [To Date]

  , STKLOG.Item_Code

  , STKLOG.Item_Name, STKLOG.Warehouse_Code

  ,Sum(STKLOG.OB_Qty)[OBQTY]

  ,Sum(STKLOG.OB_Value)[OBValue]

  ,Sum(STKLoG.ReceiptQty)[RecQty]

  , Sum(STKLOG.ReceiptValue)[RecValue]

  ,Sum(STKLoG.OutQty)[OutQty]

  ,Sum(STKLOG.OutValue)[OutValue]

  ,Sum(STKLOG.Cls_Qty)[ClsQty], Sum(STKLOG.Cls_Value)[ClsValue]

 

 

From STKLOG

group By STKLOG.Item_Code, STKLOG.Item_Name, STKLOG.Warehouse_Code

 

------------------------

 

Please share your feedback

 

Thanks

Unnikrishnan

So...

 

A while ago Abhinav Bannerjee wrote an excellent article (http://scn.sap.com/community/business-one/blog/2012/08/09/performance-best-practices-for-sap-business-one-landscape .) about best practices for setting up and configuring the MS SQL Server landscape for use with B1. Unfortunately it was deleted or moved for some reason, so I am going to try and give you my version of the same. Please bear in mind that all of the following is mostly hearsay, and my own personal conclusions from experience.

 

Hardware

 

B1 is a database driven application.That means that whatever the application does, it is actually the database (usually MS SQL Server) that does it. As such when deciding on a hardware configuration for your B1 installation, consider what a database needs for optimal performance.

 

Here are some rules of thumb:

  • RAM, RAM, RAM. The database server (usually MS SQL Server) is basically optimized to use hardware as efficiently as possible. In practice that means that it will load data from the database into RAM memory, so it can be accessed much faster than when it would have to read it from a hard disk. What data is loaded is determined by the available amount of RAM and by how popular it is. For example customer information is used throughout the business process (sales order to customer X = delivery to customer X = invoice to customer X), whereas information about human resources is accessed maybe once a day. If the amount of RAM is smaller than the size of the (company) database, SQL Server will load only the most popular data into memory, and access everything else by reading it from the hard disk at the moment it is needed. This is also the premise of HANA. Just take a look at the hardware requirements and you will see it is not a miracle database system. It is simply a database that is allowed to load more data into RAM memory, and optimized to access data that way. MS SQL Server will actually do the same, it is merely less optimized to do this with SAP software like Business One.

    Tips:
    - Compared to the rest of the server hardware, RAM is cheap, and has by far the greatest impact on performance.
    - The more RAM available, the more data can be loaded for fast(er) access.
    - Determine the average size of your company database during the expected life time of the server hardware. MS SQL Server runs on Windows. Windows needs RAM too. Preferably you want to get an amount of RAM the size of your company database + the recommended amount of RAM for your Windows Server version (32 bit = 4 GB, with 64 bit it depends on the Windows Server version). For example with an average company database size of 10 GB and Windows Server 2012 (64 bits), get at least 10 GB + 8 GB = 18 GB RAM.
    - If you use a Remote Desktop landscape on this same server, add another 1 GB of RAM for each concurrent user.

  • Storage, a.k.a. hard disks. A slightly more complicated topic. B1 requires enough hard disk space to fit the company database at least twice. Once for the company database, and once more as free space. The free space is needed for system upgrades (maybe SAP is working on changing this, or has done so already, I don't know), and obviously the system needs free space to work, and the database space to grow (even if only temporarily). A very important principle to understand about databases is that we are talking about reading operations (for example running a sales analysis report) and writing operations (for example adding a new sales order in B1). Writing to a disk is always slower than reading from it, although with the newest SSD disks the difference is becoming more and more negligible. HANA's exceptional speed for example is really only reading speed. Adding a sales order will take just as long as MS SQL Server.

    About RAID:
    Old school says: RAID1 with a separate physical set of hard disks for the (company) database, as it allows for the fastest possible reading and writing speed. This is and remains sound advice. However, server hard disks can get very expensive at sizes larger than 146 GB, so if your company database is nearing that 100 GB size...
    Also in the development of current hard disk technology a lot of focus has been put on the hard disk controller (the hardware card that connects the hard disks to the rest of the server), and in newer servers these controllers have their own caching system. That means that when you create a sales order in B1, it will really be written into the hard disk controller's RAM memory, from which the controller will write it to the disk(s). As in point 1 above, writing and reading with this controller cache RAM is much faster than reading and writing with hard disks directly. As such with newer servers, it is fine to use other RAID configurations as well. At any rate, using a separate physical set of hard disks for the company database is a good idea.

    About NAS:
    Network Attached Storage is becoming more and more commonplace, even for smaller companies, because of the advantages of easier scalability, and maintenance.
    With databases the general rule of thumb is: every layer slows the system down. Network Attached storage is an extra layer.

    Tips:
    - The faster the physical hard disk, the better
    - Use RAID1 if possible and your budget allows for it, at least on older server hardware
    - Invest in a good hard disk controller, rather than in faster hard disks.
    - Once server quality SSD hard disks become affordable, consider them.
    - Use local hard disks for your company database and MS SQL Server installation. Use NAS only for backups, and that sort of thing.
    - If you insist on using NAS, or in separating your hard disks from the server in any way, try to use fiber optic network cabling.

  • The rest. After you have allocated the necessary amount of your budget to RAM, and then to storage, invest whatever is left in the CPU and network adapter.
    In general, if it fits in your budget, go for redundancy: two power sources, two CPU's multiple physical disks, etc. Server downtime can be very costly.

 

Software

 

As I mentioned before, it is really all about RAM. However do not forget your network.

 

Some rules of thumb:

  • MS SQL Server memory settings. Although the database does all the work, it itself runs on an operating system (usually Windows). The operating system needs memory (RAM) to operate. A common oversight is to not limit the Maximum Server Memory (in MB) of the MS SQL Server installation. As a result, SQL Server can monopolize all server RAM, during peak loads. This in turn smothers Windows, which then smothers SQL Server. In other words, you allow SQL Server to hang itself.
    So however much RAM you have in your server, limit the amount of memory MS SQL Server may use. Ideally, total RAM minus the recommended minimum amount of RAM for your Windows version, but at the very least some 3 - 4 GB. So for example, you have a Windows 2012 (64 bit) server with 16 GB RAM, and a company database of 10 GB. Then you should set Maximum Server Memory to 120,000 MB at the most.
    Of course this is not quite so black and white. You should carefully consider your scenario. How large is your company database, how much RAM do you have? Can you add RAM (is it expensive, does it fit)? How is your B1 installation mainly used? Are you creating transactions at a steady pace, or in peaks? Are you running a lot of demanding queries or reports (also) during business hours? Do you run any addons? Does any of these addons run a lot of demanding queries, or create a lot of transactions ? Based on the answers to these questions you can increase or decrease the maximum amount of RAM the SQL Server may use. Are you running other software on your server and/or database?

 

  • Database files. Your company database consists of two files (see the Files section in the database's properties): Rows Data, and Log. You can improve performance by saving these files on physically separate hard disks (also see the paragraph About RAID). However the increase of performance to cost of separate disks ratio is not enough to warrant purchasing multiple disks for this purpose alone. I recommend this only if you happen to already have separate disks

 

  • Remote Desktop Services. Using this is smart, though there are one or two pitfalls.
    What is good about it? With RDS you only have to install, maintain, update, and troubleshoot, a single Business One client.
    If your scenario requires users to access Business One from a remote location (from home, or a business partner's premises), it is much easier to access; just start a VPN connection and double click the Business One client shortcut.
    You will be running the client on server hardware. That should improve performance in itself, assuming that your server hardware is stronger than your workstation's hardware, as is usually the case.
    What are the pitfalls? Printing is not as easy, as the Business One client via RDS, will only see the printers installed on the server. That means that you need a printer with a network card, and printer settings are the same for all users.
    The amount of times you can access an RDS application per user is limited. Users must log off (close the application) correctly / normally for RDS to register that a session is no longer active. If your environment is prone to sudden power outages, or a workstation is shutting down unexpectedly a lot (for whatever reason), or you rely on WIFI, and one of your Access Points is acting up, or you have a sloppy user, who likes to shut down their workstation without closing the RDS application properly first, you have to purposely log on to the server to close sessions that are left in limbo this way. If not, users will not be able to start the application after a while because there are too many active sessions "open".

 

In Conclusion

 

If I have missed something, or if I got something horribly wrong, please let me know, and I will try to add it or correct it.

Hello Guys,

 

I have created SAP B1 on demand setup on AWS and i was using amazon AD service. I was trying to connect my application server to CCC server but it was not getting connected.

After a long research i found that it was because of AWS AD service, and i have created my own AD server and include my systems in that  AD.

After that i am able to connect SLD agent to SLD CCC server.

 

 

Regards,

Ashwin Katkar

Hola!

 

Actualmente tengo en ambiente productivo trabajando la facturación nativa de SAP y funciona correctamente y sin problemas. Tengo un ambiente independiente a productivo que tiene su propia instalación de SQL, SBO y ocupa el mismo spp que el server productivo y el respaldo es reciente al de la base productiva. Sin embargo, en este último cuando quiero crear un documento me manda el siguiente error: El formato no puede analizarse sintácticamente; contáctese con el proveedor de formato para obtener soporte. error happens in Executing the MappingInstance #1.

 

Anteriormente, había estado generando facturas sin problemas en el server de pruebas, pero ahora me encuentro con este error. No se ha realizado ninguna modificación recientemente por lo que no logro determinar lo que genera el error.

 

¿Alguna sugerencia para su tratamiento ?

 

Saludos.

SAP Business One 9.1 PL07, version for SAP HANA has been released and adopts HANA SPS08!

 

Details of this release can be found at the overview note: 2160655  - Overview Note for SAP Business One 9.1 PL07, version for SAP HANA

 

 

Important Notes


eLearning

Access the SAP Business One  9.1 Clips on YouTube or the SAP Business One Academy to learn about the new release.

 

SAP Business One HANA pages in SDN

 

SAP Business One on SAP HANA solutions

 

SAP Business One HANA topics

SAP Business One, version for SAP HANA Extensibility topics

Mike Fielding

Starting Out IN B1 Sales

Posted by Mike Fielding Jun 29, 2015

Well here we go. 1st blog post and 1st time out in the big bad world of SAP Business One. Loads to learn and loads to get done. Hope to see you out in the thick of it.

Actions

Filter Blog

By author:
By date:
By tag: