MICROS 3700 4.9 Custom Report 3

[themicrosman]

Would anyone out there happen to have a MICROS 3700 4.9 custom report that is already written that would potentially include any or all of the information listed below. I would just like to have a manager be able to run this once in awhile so he can do a user security audit. So he can quickly see who has access to what.

Employee Name
Employee Class
Back Office Employee Class

I also really only want to pull the employees that are active, not any one who is disabled or terminated.

If anyone has a useful custom report that may fit the bill, I would be greatly appreciative.

 

[Mauriciose]

Hi microsman...do you need a SQL query to database?...What type of report do you want?

Mauro

 

[hosehead78]

I have this for 9700 system, but not the 3700 system.

 

[themicrosman]

Thanks everyone for taking the time to respond. I was looking for something along the lines of a crystal report?

 

[hosehead78]

You would still need to know where to look in the DB for this information. you should be able to use the DB query tool.

 

[themicrosman]

hosehead78 It should be the same place in the database for every 3700 system. no? So I was thinking maybe someone had already generated a similiar report and had one handy.

 

[hosehead78]

I can post the query I have for the 9700 system, it might help. I don't have access to a 3700 system to look around, otherwise I would take a look.

Query below is for a Micros 9700 System. It will return Micros Employee Number, Last & First Names and their Employee Class (Position). Use it as a possible reference.

select 
    a.posref as #
    ,a.LastName as LastName
    ,a.FirstName as FirstName
    ,CAST(b.employeeclassPOSRef AS nvarchar(20))+'-'+ b.name as EmployeeClass

from LOCATION_ACTIVITY_DB.dbo.employee a (nolock), LOCATION_ACTIVITY_DB.dbo.employee_class b (nolock)

where a.employeeclassid = b.employeeclassid
order by a.posref--, a.lastname

 

[themicrosman]

Thanks Hosehead

 

[pmegan]

The table structure for 3700's is different than 9700. This will give you what you're looking for:

select 
    EMP.obj_num [emp_num]
    ,EMP.last_name
    ,EMP.first_name
    ,CLS."name" [emp_class_name]
    ,BO."name" [bo_class_name]
from 
    micros.emp_def [EMP]
    join micros.emp_class_def [CLS]
        on EMP.emp_class_seq = CLS.emp_class_seq
    left outer join micros.emp_bo_class_def [BO]
        on EMP.emp_bo_class_seq = BO.emp_bo_class_seq
order by
    EMP.last_name
    ,EMP.first_name

Change "left outer join" to "join" to see only employees with a back office class assignement.

 

[themicrosman]

Very Nice Pmegan. Not a crystal report, but Ill take it. Thank you very much.

 

[pmegan]

No problem. I don't have a report for this, and whipping up a query is much faster than making a report. Plus, the query has all the stuff you'll need to make a report if you need to.

 

[hosehead78]

I knew pmgegan would come through.

 

[themicrosman]

Pmegan is great. On that note, would you happen to have the SQL query if I wanted to pull up a Menu Item Classes and the Sales itemizer assigned to it? Or even better if I could query Menu Item, Menu Item Class and sales itemizer in one shot. But lets be honest ill take anything.

 

[pmegan]

Give this a try:

Select
M.obj_num [item_num]
,M.name_1
,C.obj_num [class_num]
,C."name" [class_name]
,C.sls_itmzr_seq [sales_itemizer]
From
Micros.mi_def [M]
Join micros.mi_type_class_def [C]
On M.mi_type_seq = C.mi_type_seq

This isn't tested, I'm typing this on my phone from the train home, but I'm pretty sure I got all the fields correct.

 

[themicrosman]

Heck yes it works!!! Damn on your phone? On the train? Man you are good!

 

[pmegan]

Haha, I've been working on the 3700 database since Res 2.0 was the latest and greatest. Some thing are just embedded in my brain at this point.