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]

Useful Queries for Reports in SAP Business One

Assigned Tags