SQL Query for Project Accounting Security Setup

(0) Share

Report

Posted on by Community Member

Product: MSGP 2010 SP3 version 11.00.2197, Dexterity version 11.00.0359.00 running on SQL Server 2008 R2

I am a Programmer/Analyst that supports MSGP for my company of 600+ employees. I'm looking for some help in developing an SQL Query that will show the PA User Class Security Setup for each user. On the front-end this is access via Microsoft Dynamics GP >> Setup >> Project >> User [User Project Accounting Settings]. There are (9) sub-forms with User Settings information that needs to be captured as well: Project, Timesheet, Equipment Log, Miscellaneous Log, Employee Expense, Purchase Order, Purchasing Invoice, Inventory and Billing.

A majority of the information is retained in Tables PA01501 and PA40401, but I need help translating the 0, 1, 2 codes--I understand that it indicates yes/no, checked/unchecked or a drop-down selection, but not what number is associated with which option. There is what I call the "manual method" which is going screen-by-screen and mapping it myself, but I'd rather not recreate the wheel if I don't have to.

We have identified a gap in our MSGP Security Matrix around Project Accounting User Classes and I was tasked with filling it in. Information on the Dynamics GP Project Accounting Tables is very limited and I would appreciate whatever assistance or resources anyone can offer. I would rank my SQL Query writing at a solid Intermediate skill-level.

Regards,

Joanne Herzog

*This post is locked for comments

All responses (4)

Answers (1)

Community Member on at

Like (0)

Report

RE: SQL Query for Project Accounting Security Setup

Mahmoud,

Thanks so much for the enhanced SQL script. This will make the comparative piece of my job much easier--better than exporting to Excel and running duplicate comparisons!

Regards,

Joanne
Community Member on at

Like (0)

Report

RE: SQL Query for Project Accounting Security Setup

Leslie,

Yes, I have looked at those spreadsheets and have used many of the SQL queries for the GP Security piece. The gap we have relates specifically to the Project Accounting User Class security. I haven't found anything existing yet, but I'm still looking. Perhaps I will post this question on her blog...
Verified answer

Mahmoud Saadi 32,738 on at

Like (1)

Report

RE: SQL Query for Project Accounting Security Setup

When it comes to comparing users security, it might be a bit of headache. Just a bit of detail; further to the essentials information mentioned by Ms. Leslie, which might be important in your task

GP Essentials - User Security Role Enhancement–Intersect
L Vail 65,271 on at

Like (0)

Report

RE: SQL Query for Project Accounting Security Setup

Have you already looked at the various security spreadsheets available? There's on that shows what your users have security to now, and there's another that shows you what the tasks, roles, etc that you have set up have security to.

Find them all at Victoria Yudins blog. Look around while you are there, she has some incredible information posted!

Kind regards,

Leslie