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