Enterprise Resource Planning Blogs by Members
Gain new perspectives and knowledge about enterprise resource planning in blog posts from community members. Share your own comments and ERP insights today!
cancel
Showing results for 
Search instead for 
Did you mean: 
former_member197621
Active Contributor

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

2 Comments
Labels in this area