| Converting Setup Data | KEVIN RESCHENBERG
04-25-2005 |
When you initially installed your PeopleSoft system, you (or a
consultant) carefully analyzed your company's organizational structure
and operating procedures. You then filled in all of the basic
setup tables (for HRMS, these would be things such as departments,
locations, companies, etc.). You moved everything to production
and things went fine—for a while. Then the users decided that
the original setup wasn't exactly correct. They wanted to make
a few slight changes—for example, completely replacing the scheme
for coding departments, locations, and companies. Or maybe there
was a reorganization and the original setup no longer fits. How
can you handle this?
I've run into this situation a few times. It seems surprisingly
common. One customer brought me in to completely reorganize
the business unit, department, and account tables (for Financials) and
the business unit, department, SetID, company, location and job code
tables (for HRMS).
How is this done? There are several possible methods. One
method—considered in the case above but dropped—would be to
start with a fresh copy of the SYS database and convert all existing
data (possibly without the history) to it. This is similar to a
re-implementation of the system.
Another method is to mark each existing code (department ID, for
example) "inactive", insert new rows into the tables for the new
set of codes, and add a new row for each employee as of a
particular cutoff date. This method has the advantage of showing
the history of the two structures—before and after the change.
It is important to add a row for every
employee, including the terminated ones. If this is not done,
a rehire would copy old, inactive codes to the new row. If the
user did not enter a new code, the old one would be saved.
(How can this escape the prompt table edits? Remember that these are
done only on a change to a field. If the value is left alone
it will be accepted—even if it is invalid.)
This method has the disadvantage of leaving the tables full of
old codes. It complicates reporting across the cutoff
boundary. It also requires the addition of new rows for each
employee—and don't forget JOB_JR, COMPENSATION, etc.
A third method is what I call an "in-place conversion". Under this
method, you simply change every old value to its new equivalent,
wherever it may occur. This is done directly within the database
using SQL. For example, if old department ABC is now
department 123 under the new coding scheme, we find every field
in every table that indicates department ABC and change that value
to 123. All history and all current rows would now show 123. The
main department table is also converted. Rows remain active.
No new rows are inserted into any tables.
This is the method we used. The actual conversion in production
took just a few hours and there were no issues.
If you are contemplating a conversion using any of these methods,
be sure to check with all of the stakeholders first. Are there
legal or operational reasons to keep history of the old codes?
Will processes break if there are two coding schemes before and
after the cutoff date? What are you trying to accomplish? If
you are reacting to a company reorganization, you might be more
likely to choose the second method (inactivating the old codes).
On the other hand, if you are trying to fix a poor design, you
might want to consider the in-place conversion.
If you do opt for the in-place conversion, you are faced with
the problem of finding all of the department ID fields (for
example). Yes, you can find the DEPTID fields easily. But what
about fields that contain department IDs but are not called
DEPTID? Most of these fields might contain the letters "DEPT".
But maybe you really call them "cost centers" and there is a
custom field called X_COST_CTR. How can you be sure that you
have found all of the relevant fields? Even if you are not
doing a conversion, but simply need to find all fields containing
a particular kind of data, how can you do it? I'll continue with
that topic next week.
|
