Skip to Content
Author's profile photo Prasanna s

How to count the entries user wise. **Helpful for go live**

Hi every one,

Run the below query to get the count of entries user wise,

Benefit:

Testing phase we will ask the user to test the entries but no one take interest to test the scenario. Finally we(consultant) will get issue while go live.

Avoid this situation, do auto schedule this below query to top management on daily basis.  This will help you to go live easily.

Note:

Create one User defined table,  Object type -> no-object . Table Name  -> SFL_OBJ , Description-> List of Objects

In the table enter the Object type in “Code” field and Document name in “Name” field. More clarification refer the attached image.

Select (select upper(ou1.U_NAME) from OUSR ou1 where ou1.userid = TBL1.usersign) [UserName],TBL1.Counts,
(Select j1.name from [dbo].[@SFL_OBJ]  j1 where j1.code = TBL1.ObjType) [Document]
,TBL1.PIndicator
from (
–Purchase
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from OPQT p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
union all
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from OPOR p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
union all
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from OPDN p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
union all
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from ORPD p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
union all
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from ODPO p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
union all
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from OPCH p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
union all
select p1.UserSign,count(p1.DocNum) [Counts],p1.ObjType, p1.PIndicator from ORPC p1
group by p1.UserSign,p1.ObjType,p1.PIndicator
— Sales–
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from OQUT s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from ORDR s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from ODLN s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from ORDN s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from ODPI s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from OINV s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from ORIN s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from OWTR s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from OSCL s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.Owner,count(s1.ContractID) [Counts],’190′, ‘FY’ from OCTR s1
group by s1.Owner
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from OVPM s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.DocNum) [Counts],s1.ObjType, s1.PIndicator from ORCT s1
group by s1.UserSign,s1.ObjType,s1.PIndicator
union all
select s1.UserSign,count(s1.OpprId) [Counts],’970′, ‘FY’ from OOPR s1
group by s1.UserSign

) TBL1
where TBL1.UserSign <> ‘1’
order by TBL1.usersign,TBL1.ObjType

If you using 9 version simply you can use this query,

select (select name from  dbo.[@SFL_OBJ] where Code   = m1.TransType) ‘Document Name’,max(m1.JrnlMemo) [Doc Inf],

(select U_NAME from ousr where ousr.USERID  = m1.UserSign) [User Name], m1.CreateDate [Date of Creation],Count(m1.DocEntry) [Count]

from dbo.OILM M1

where m1.CreateDate = ‘[%0]’

group by m1.TransType,m1.UserSign,m1.CreateDate


                            (OR)

select max(m1.JrnlMemo) [Doc Inf],Count(m1.DocEntry) [Count],

(select U_NAME from ousr where ousr.USERID  = m1.UserSign) [User Name], m1.CreateDate [Date of Creation]

from dbo.OILM M1

where m1.CreateDate = ‘[%0]’

group by m1.TransType,m1.UserSign,m1.CreateDate

                         (OR)

select (select name from  dbo.[@SFL_OBJ] where Code   = m1.TransType) ‘Document Name’,max(m1.JrnlMemo) [Doc Inf],

(select U_NAME from ousr where ousr.USERID  = m1.UserSign) [User Name], Count(m1.DocEntry) [Count]

from dbo.OILM M1

group by m1.TransType,m1.UserSign

                         (OR)

select max(m1.JrnlMemo) [Doc Inf],

(select U_NAME from ousr where ousr.USERID  = m1.UserSign) [User Name], Count(m1.DocEntry) [Count]

from dbo.OILM M1

group by m1.TransType,m1.UserSign

See also:

How to Implement SAP Business One projects successfully

How to count the entries user wise. **Helpful for go live**

Database design in SAP Business One

How to know the current user LOGIN status in 9-Version

Assigned Tags

      2 Comments
      You must be Logged on to comment or reply to a post.
      Author's profile photo Former Member
      Former Member

      Thanks prasanna

      Author's profile photo Mitche Ann Gutierrez
      Mitche Ann Gutierrez

      Thanks for sharing Prasanna..Big help to someone who's just learning SAP B1.