Sage 100

I need to create a SQL view to display the employee dept, employee number, name, address fields, and social security number.  I am not getting any data.  below is my query:



------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

I would ring up @Dan Burleson for assistance.  He is the man with a plan!  And it is reasonably priced.​

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------

Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

I have a script solution that maintains those PII fields up to date in a work table with the EmployeeKey field for linking to PR_Employee. Regarding violating security: Just leaving the P/R Employee Listing report in preview makes PII fields available unencrypted in link-able tables to user's with P/R permissions. For Standard/Advanced versions, putting the Session ID in the ODBC control panel provides access to the PII fields in the previewed report. It's easier in Premium. Email me if you're interested to automate the access and I will send you the installation instructions. Several 90 Minds members use it.

---------------------------------
Dan Burleson
Software Consultant
Connex Software
---------------------------------



Original Message:
Sent: 01-25-2023
From: Jeff Schwenk
Subject: RE: How Can I Display PII Info ?

I would ring up @Dan Burleson for assistance.  He is the man with a plan!  And it is reasonably priced.​

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------


Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Thanks @Dan Burleson  I sent you an email.
​

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Original Message:
Sent: 01-25-2023 20:51
From: Dan Burleson
Subject: How Can I Display PII Info ?

I have a script solution that maintains those PII fields up to date in a work table with the EmployeeKey field for linking to PR_Employee. Email me if you're interested and I will send you the glossy. Several 90 Minds members use it.

---------------------------------
Dan Burleson
Software Consultant
Connex Software
---------------------------------




Original Message:
Sent: 01-25-2023
From: Jeff Schwenk
Subject: RE: How Can I Display PII Info ?

I would ring up @Dan Burleson for assistance.  He is the man with a plan!  And it is reasonably priced.​

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------


Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

I second, third and so on for @Dan Burleson.  I have a client using his solution at a very reasonable price.​

------------------------------
[Michele] [Herzog] [CPA,CITP, CGMA]
[Overland Park] [KS]
[816-520-1365]
------------------------------

Original Message:
Sent: 01-25-2023 21:49
From: Doug Higgs
Subject: How Can I Display PII Info ?

Thanks @Dan Burleson  I sent you an email.
​

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------


Original Message:
Sent: 01-25-2023 20:51
From: Dan Burleson
Subject: How Can I Display PII Info ?

I have a script solution that maintains those PII fields up to date in a work table with the EmployeeKey field for linking to PR_Employee. Email me if you're interested and I will send you the glossy. Several 90 Minds members use it.

---------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 01-25-2023
From: Jeff Schwenk
Subject: RE: How Can I Display PII Info ?

I would ring up @Dan Burleson for assistance.  He is the man with a plan!  And it is reasonably priced.​

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444


Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

Well worth the small investment.  Why reinvent the wheel when you have the Michelin man in the group.

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444
------------------------------

Original Message:
Sent: 01-26-2023 00:50
From: Michele Herzog
Subject: How Can I Display PII Info ?

I second, third and so on for @Dan Burleson.  I have a client using his solution at a very reasonable price.​

------------------------------
[Michele] [Herzog] [CPA,CITP, CGMA]
[Overland Park] [KS]
[816-520-1365]
------------------------------


Original Message:
Sent: 01-25-2023 21:49
From: Doug Higgs
Subject: How Can I Display PII Info ?

Thanks @Dan Burleson  I sent you an email.
​

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 01-25-2023 20:51
From: Dan Burleson
Subject: How Can I Display PII Info ?

I have a script solution that maintains those PII fields up to date in a work table with the EmployeeKey field for linking to PR_Employee. Email me if you're interested and I will send you the glossy. Several 90 Minds members use it.

---------------------------------
Dan Burleson
Software Consultant
Connex Software


Original Message:
Sent: 01-25-2023
From: Jeff Schwenk
Subject: RE: How Can I Display PII Info ?

I would ring up @Dan Burleson for assistance.  He is the man with a plan!  And it is reasonably priced.​

------------------------------
Jeff Schwenk
Bottomline Software, Inc.
(540) 221-4444


Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

Not ODBC only.  In theory you could use BOI to push the data into a separate database table (unencrypted) but that would probably break some data security laws.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------

Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

Yes.  I was thinking maybe a script that would copy the PII fields I need to UDFs in PR_Employee.  Then we just read PR_Employee.UDF_xxxxx.

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles
------------------------------

Original Message:
Sent: 01-25-2023 19:43
From: Kevin Moyes
Subject: How Can I Display PII Info ?

Not ODBC only.  In theory you could use BOI to push the data into a separate database table (unencrypted) but that would probably break some data security laws.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------


Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.

This is exactly what we did for another customer (BOI script that runs daily to populate a SQL table outside of Sage), and then we just locked down access to that table to the appropriate users and had the file system underneath encrypted. In theory, it meets the compliance requirements, but it's certainly not 'in the spirit' of what the compliance was trying to solve....

------------------------------
Steve Iwanowski, NextStep Technology Advisors, aka DSD Lancaster PA ¯\_(ツ)_/¯
------------------------------

Original Message:
Sent: 01-25-2023 19:43
From: Kevin Moyes
Subject: How Can I Display PII Info ?

Not ODBC only.  In theory you could use BOI to push the data into a separate database table (unencrypted) but that would probably break some data security laws.

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.
------------------------------


Original Message:
Sent: 01-25-2023 19:38
From: Doug Higgs
Subject: How Can I Display PII Info ?

That's what I thought, and that sucks.  The PR_EmployeePIIwrk gets populated using the BOI objects when executed from a Sage 100 menu.  I have a customer that is trying to pass data to a third party program via an ODBC call to the SQL server database.  Are there any other options?

------------------------------
Doug Higgs
Midwest Commerce Solutions, Inc
(312) 315-0960
Chauffeur, Chef, and Personal Assistant to Sprinkles


Original Message:
Sent: 01-25-2023 19:33
From: Kevin Moyes
Subject: How Can I Display PII Info ?

AFAIK: you can't.  The data is encrypted, and only accessible through BOI or a PII work table (which is only populated runtime, in a tempdb copy of the table, while a custom report is running from the Sage menu).

------------------------------
Kevin Moyes
Technical Systems Analyst
Munjal White Consulting Co.