Skip to Content

Useful Queries for Reports in SAP Business One

Dear SAP B1 USERS.

Please find the list of queries created through query generator  

  1) Customer and Vendor Tax IDS

To get a list of PAN/TIN/Service Tax and other tax ids of Customer/Vendor.Choose by dropdown

Useful for  users handling Finance module.

SELECT T0.[CardName], T1.[CardCode],T1.[TaxId0] as ‘PAN’,  T1.[TaxId7]as’Service Tax’, T1.[TaxId11] as ‘TIN’,T1.[TaxId5] as ‘CST’, T1.[TaxId10] as
‘LBT’ FROM OCRD T0  INNER JOIN CRD7 T1 ON T0.CardCode = T1.CardCode WHERE T0.[CardType] =[%0]

2) Document_Numbering

To get the series number of various documents defined in the Document Numbering -setup.

Very important for FMS query and useful for Crystal Reports Formula purpose

 

Useful for  users Creating Document series usually a super user

SELECT *FROM NNM1 WHERE REMARK LIKE ‘SalesQuotation%’

3)Service_call_report_detailed

To get the details of service calls made for particular customer. All the resolution and the issues handled. Useful for  users handling Service module

SELECT T0.[callID], T0.[subject],
T0.[custmrName], T0.[contctCode], T0.[internalSN], T0.[resolution],
T0.[resolDate], T0.[resolTime], T0.[itemCode], T0.[itemName], T0.[technician],
T0.[resolOnDat], T0.[resolOnTim], T0.[respByDate], T0.[respByTime],
T0.[respOnDate], T0.[respOnTime], T0.[respAssign], T0.[AssignDate],
T0.[AssignTime], T0.[StartDate], T0.[StartTime], T0.[EndDate], T0.[EndTime],
T0.[Duration] FROM OSCL T0 WHERE T0.[custmrName] =[%0]

Hope its helpful!!.Will keep adding

Regards

Darshan Desai

Be the first to leave a comment
You must be Logged on to comment or reply to a post.