Applied Concepts HR/Payroll » Search Results

Triggering a workflow only if specific fields are changed. Also use of User Defined SQL Functions.

Denton Harryman — Sat, 24 Jul 2010 12:45:55 GMT

Request: An emPath user recently asked the following:

What I would like to do is to only implement the workflow for a specific field. So for example, lets say we setup a workflow on the Bio Details page. This page has a very large collection of fields such as SSN, hire date, names but, we only want to trigger a workflow when the name fields are modified. Therefore, we would need to reference some object that contains which fields have been modified. I assume in order to do that, you would need a rule. If so, could you tell me how I would access what fields changed in a rule?

Solution: I provided an example workflow which used if/else logic within the captureUserInput stateactivity. This logic conditionally triggered a WF based on the change ofspecific fields on the EmBioDetails page.

The workflow test to see if name fields (i.e. title, last name, first name, middle initial, name suffix, nickname, etc) have changed. If so an approval workflow is triggered.

Following is a screen print showing the if/else logic in the workflow designer.

Note: Following is the expression used in the declarative rulecondition. Note that it uses the GetSqlBooleanValue method. IMHO this is the mostpowerful exposed method available to workflow users.

this.GetSqlBooleanValue("captureUserInputActivity1", "select dbo.exampleHaveNameFieldsChanged ('#ORIGINATOR_ACTIVE(employee, EM_EMPLOYEE_ID)#', '#DATA(txtEmNameTitle)#', '#DATA(txtEmLastName)#', '#DATA(txtEmFirstName)#', '#DATA(txtEmMiddleInitial)#', '#DATA(txtEmNameSuffix)#', '#DATA(txtEmNickname)#', '#DATA(txtEmPreferredGivenName)#', '#DATA(txtEmLegalName)#', '#DATA(txtEmFormerName)#', '#DATA(txtEmNameAtBirth)#') from swd1", False)

Please register to download workflow files and the example SQL User Defined Function. The registered user article is, Registered users - Triggering a workflow only if specific fields are changed.

how to reduce the size of the transaction log when using SQL Server

Denton Harryman — Wed, 30 Jun 2010 08:13:50 GMT

Request: An emPath user recently asked about how to reduce the size of the transaction log when using SQL Server. Specifically they ask:

Now that we are running emPath, on SQL Server 2005, I noticed the log file grows quite fast.

Would you suggest putting a limit on the file size? I realized with SQL Server 2005 the auto-shrink setting if off by default. Should this be set to on?

Suggestion: I recommend periodic transaction log backup and truncation. Following is some detail:

From review of the literature on the subject I think the proper thing to do is to periodically backup the transaction log file and truncate it during the backup. Naturally the backups should be saved. Following are a couple of helpful links on the subject:

How to: Back Up a Transaction Log (SQL Server Management Studio) .. be sure and keep the "truncate transaction log" radio box checked.
Transaction Log Truncation

Note that it is possible to create a batch job, using Transact-SQL, which can be scheduled to run regularly. If you are interested I would be pleased to contract to deliver such a job.

I recommend that you try, log file backup and truncation, in a test environment. Also, I recommend you test recovery from backups as well. Following is an article on the subject: Understanding How Restore and Recovery of Backups Work in SQL Server.

As for your question about the auto-shrink option. Best practice documentation indicates this should be set to off.

As for your question about limiting the file size. I recommend against limiting the file sizes. Instead I recommend periodic transaction log backup and truncation as described above.

I hope this information is helpful. Let me know if I can be of assistance

Update emPath login_string during newhire process - ESS Active Directory

Denton Harryman — Thu, 13 Aug 2009 18:22:28 GMT

Request: An emPath user recently asked for suggestions on how to update, during the new hire process, the emPath security table such that Active Directory authentication can be used. The emPath users request was:

"Our company is changing the authentication of ESS to use Active Directory. I used a SQL script to update the login_string field in the Susr table to the employees’ Active Directory user name for existing employees; however I am having an issue populating this field for new hires."

" I don’t think I can use the Network_account since it is not in the EMF table. I was thinking of using em_employee_id as the ESSLoginString. Then run a job every evening that will execute a SQL script updating the login_string field to AD user name, but I am no sure this is the best approach. Any ideas are greatly appreciated."

Suggestion: I suggested the emPath wizard and PDX (page description XML) features be used to address this issue. Following are wizard screens which show the newhire process with the addition of a step allowing the entry of domain and network id into the login_string field on the SUSR table. Effectively allowing Active Directory authentication.

Note that emPath provides user detail in their help document on how to use active directory authenticaton for employee self service (ESS). This example focuses on how to add the network id to the emPath security table, during the new hire process.

Note that Field Level Security can also be used to edit the values entered on the above step.

Note that this approach requires some setup, configuration, and a small custom. Contact Denton Harryman at (888) 731-3026 x-1 or DentonHarryman@att.net if you are interested in obtaining this custom.

Importing timecard files larger than 4MB - emPath HR/Payroll

Denton Harryman — Tue, 11 Aug 2009 13:31:57 GMT

Issue: A client asked for assistance in resolving a problem where they were unable to import large timecard files.

Resolution: Research found that ISS imposes a 4MB limit on upload files. This same research identified a workaround which allows users to configure the upload size limit.

A Microsoft, MSDB, article discusses the <httpRuntime> Element which supports an override of the 4MB limit on upload files.

In this instance I changed the web.config file with the following custom configuration which changes the limit to 12MB.

  
  <httpRuntime maxRequestLength="12000" />
  

Note that the above custom configuration was placed in the <system.web> section of the web.config document which can be found at the following path: C:\Nsl64\emPath\iis

Following is additional information on this subject:

(1) In this instance the error occur when importing timecards. However it could have happened on any import within emPath.

(2) The error message shown on the screen is not very helpful:

(3) However taking the TimecardImport program through debug produces a much more meaningful error as shown below:

An exception of type 'System.Web.HttpException' occurred in System.Web.dll but was not handled in user code
Additional information: Maximum request length exceeded.
(4) Also, there is additional information in the C:\Nsl64\emPath\iis\Logs
ow_hr64.log
[ERROR] | nsl.Web.HrHttpApplication.Application_BeginRequest:line 135 | Exception has been thrown by the target of an invocation.
[ERROR] | nsl.Web.HrHttpApplication.Application_BeginRequest:line 136 | couldn't instantiate class nsl.empath.pagehandler.pay.TimecardImport,nsl

Allowing access to emPath Employee Self Service (ESS) outside the firewall

Denton Harryman — Fri, 31 Jul 2009 14:26:24 GMT

Request: A client asked for suggestions for allowing access to emPath Employee Self Service (ESS) outside the firewall.

Response: Following are diagrams and suggestions previously shared at user conferences on this subject. Additional suggestions and links have been added for consideration.

(1) use VPN to access from outside the firewall

Note that several clientless VPN alternatives are available for consideration.

(2) Place ESS webtier outside the firewall with tunnel through to the database

The ESS webtier servers, for ESS, should:

Be dedicated
have no source loaded
have no shares defined
have no ODBC connections
have administrator functions limited
be locked down (part of emPath web installation)
use NTFS security
database should be referemced by an alias

(2) Consider requiring client certificates. Read more about SSL and client certificates at Verisign.com

Field Level Security - creating a filter using multiple fields.

Denton Harryman — Thu, 30 Jul 2009 13:57:40 GMT

Request: A client recently asked for assistance with the creation of a filter, in the emPath feature called Field Level Security. Specifically the request was to only display Timecard Entry when batch number is "12DRIV", check sequence is 1, tax code is 1, and update code is "R".

The client's request was based on a planned release of the Timecard Entry screen to remote timekeepers. The security requirement was that these remote timekeepers should only be able to view timecards as described above. There were additional edit requirements which the client was able to create without assistance.

Resolution: The filter created to satisfy this request is shown below.

select decode(#DATA(TCF_BATCH_NO)# || #DATA(TCF_CHECK_SEQ_NO)# || #DATA(TCF_TAX_CODE)# || #DATA(TCF_UPDATE_CODE)#, '12DRIV11R', 'YES', 'NO') as LISTABLE from dual

Note that the filter concatenated four fields and tested as one test. This approach forces AND logic. Timecards will only be displayed if all four fields match the requirements. Also, the filter uses a decode statement to insure the select statement would always return YES or NO.

Please register to see additional screen prints showing this filter in action. The registered user article is, Registered users - Field Level Security - creating a filter using multiple fields.

Restrict the printing option for emPath Employee Self Service Users

Denton Harryman — Wed, 22 Apr 2009 13:12:53 GMT

Request: A question from an emPath user stated the following:

"We are releasing some parts of self-service this week and what I'm discovering is that I'm not able to restrict emPath users from printing to specific locations without having an effect on their ability to print while in other browser applications."
"As many of you know printing while signed in to emPath as a nonemployee or nonmanager is controlled by the print configuration performed at the server level. I want that same control to self-service employees. A Windows network group policy will have too great of an effect on other browser apps. Has anyone addressed this issue within your organizations? "
"What we are attempting to do with this restrict is to help employees safeguard their information. At our hospital staff float to many parts of the building and current network configurations allow them to print any number of printers on our network. From time to time staff have been known to print items to a printer they didn't intent to use and with the ability to pull up their sensitive employee/payroll data, I'm concerned they will mistakenly print materials that others will see."

Response: A custom solution would to:

(1) Place the following style tag in the PayStubDetails xml file and in other ESS pages with confidential information.

This change will cause a blank page to be printed if the print function is used on this specific page.

Note that to insure your change takes effect you will need to delete the PayStubDetails file from the emPath cache and stop the w3wp process.

(2) Place a custom print function on the chosen pages.

*** Please register to view additional details on how customs are accomplished ... Note that after registering you have to call Denton at (888) 731-3026 before your registeration will be activated. The phone number will always reach a voice prompt. Please select option 1 and the service will track Denton down ... this activation process is necessary to insure those registering are emPath users.

Manually remove emPath 6.4 from the add/remove list

Denton Harryman — Sat, 18 Apr 2009 17:57:05 GMT

Issue: I recently had a client moving emPath 6.4 from one server to another. The installation on the new server was easy. However we faced a challenge removing emPath 6.4 from the old server. Specifically, after removal emPath 6.4 remained in the add/remove list under "settings > control panel > add or remove programs".

Response: The resolution was found in the following Microsoft knowledge database article.

How to manually remove programs from the Add or Remove Programs tool

Note that challenge also affected my custom efforts, in that sometimes I need to start with a fresh install of emPath 6.4

Using Page Description XML (PDX) to maintain custom data

Denton Harryman — Wed, 08 Apr 2009 17:34:27 GMT

Question: An emPath user recently asked

"Is anybody using the applicant tracking module of emPath? We want to know how Volunteer applicants are handled? How do you collect some of the information that Empath does not contain such as Availability, Emergency contact, Education, and Background check?"

Response: There are several valid responses to this question. This article focuses on the use of Page Description XML (PDX) to maintain custom data.

The emPath application is delivered with a feature called "Page Description XML (PDX)" which can be used to create custom pages to maintain custom or client specific data.

Following is an example page created using PDX. This example attempts to address the emPath users detail questions.

A detailed article is available, for registered members, which shows how to create a PDX page. Also, this information will be explained at the NOW Solutions User Conference.

In summary:

(1) Create a custom table using SQL statements. Set AP_APPLICANT_ID as the key. Add custom columns as needed.

(2) Create a SQL trigger to insert a record into the custom table when an applicant is added to the AP1 table.

(3) Create or modify an XML file called site_ComponentMetadata. This file will define the custom table such that emPath PDX pages can access and maintain the data.

(4) Create an emPath PDX page using XML statements to define the data to be presented and maintained.

(5) Create or modify an XML file called site_website to let emPath know about the new PDX page.

(6) Create or modify an XML file called site_nav to let emPath know where in the menu the new PDX page should appear.

(7) Update emPath security tables letting emPath know which user classes can used the new PDX page.

(8) Update emPath metadata tables to provide lable descriptions and help text for the new PDX page.

Please register to view details on how this custom was accomplished. The registered user article is, Registered users - Using Page Description XML (PDX) to maintain custom data.

Resolve an invalid object error by changing ownership of a SQLserver object (table, function, ect).

Denton Harryman — Sat, 04 Apr 2009 16:44:40 GMT

Issue: While helping a client upgrade to emPath 6.4 the log for the DatabaseDeploymentUtility included an error which was related to ownership of SQLserver objects. Note that this was not a bug in the emPath DatabaseDeploymentUtility. In fact the utility was identifying a problem which had occurred previously and gone undetected until the empath 6.4 upgrade. (See notes at the bottom of this article for examples of error messages received)

Resolution:
(1) using Query Analyzer identify the SQLserver objects which have an owner other than "dbo". The tables and functions should have ownership of dbo:

(2) If an object (table or function) has an ownership other than dbo you should change the ownership to dbo prior to running the emPath DatabaseDeploymentUtility. Following the SQL statement to change ownership in the above example:

sp_changeobjectowner 'xxx.AA2', 'dbo'

Note that errors in the log file of the DatabaseDeploymentUtility may look similar to the following:

Note that errors on emPath pages related to ownership issues look similar to the following:

An example general ledger interface

Denton Harryman — Mon, 23 Feb 2009 17:01:28 GMT

Request: An emPath user recently asked for example general ledger interfaces from emPath to an external GL.

Response: The client list server responses indicated several different examples. This article details an example in which emPath reporting submit is supported, an automated read of the selection criteria, a SQL selection, a SQL formatting of the data, and a SQL update of the emPath posted flag.

Following is a screen print of the emPath report selection page used in this custom GL interface.

Please register to view details on how this custom was accomplished. The registered user article is, Registered users - An example general ledger interface .

Display your logo on the emPath 6.4 portal page

Denton Harryman — Wed, 18 Feb 2009 22:44:14 GMT

Request: A client asked for assistance in porting a 6.3 custom to emPath 6.4 in which their logo appears on the emPath 6.4 portal page.

Solution: A minor custom allows the client to use a web portal page which appears as follows:

Please register to view details on how this custom was accomplished. The registered user article is, Registered users - Display your logo on the emPath 6.4 portal page.

Note that the steps to implement this custom are different in emPath version 6.4 than they were with emPath version 6.3

Setup for the Salary Range page in emPath 6.4

Denton Harryman — Thu, 12 Feb 2009 10:32:11 GMT

Request: An emPath user asked for information, from other users, about how they setup the Salary Range page within emPath. Specifically, the emPath user wanted to know how to use the Salary Range page to show a salaried employee annual salary.

Note that this article has been modified to demonstrate the setup for MX and M position control.

Example: Following is an example with I created in the demo database.

(1) This screen print shows the Salary Range page with annual salary displayed.

Note the wage structure. This is the tie which allows the min, mid, and max to be displayed.

(2) This screen print shows the employee position assignment and annual rate the employee earns. Note that this screen shows an example when the MX position option is set.

(3) This screen print shows the employee position assignment and annual rate the employee earns. Note that this screen shows an example when the M position option is set.

(4) This screen print shows the position assignment and the wage structure tie

(5) This screen print shows the possibility to override the wage structure min, mid, and max at the position level.

(6) The wage structure page in which the min, mid, and max are defined.

(7) Note that taking this approach may affect the pay formulas given an annual rate is entered on the position/salary page. Following is a screen print showing that change in the demo data base necessary to implement this setup

Resolve an installation error indicating vjredist is not installed

Denton Harryman — Wed, 11 Feb 2009 16:31:04 GMT

Issue: When installing emPath 6.4 it is possible to receive an error indicating vjredist is not installed. This can appear even though vjredist is installed.

Solution: It is possible the installed copy of vjredist is an old version. An easy fix is to install Microsoft Visual J# v2.0 delivered by with emPath.

A custom leave balance ESS page in emPath

Denton Harryman — Mon, 09 Feb 2009 17:08:12 GMT

Request: A client asked to have a custom version of the delivered Leave Balance ESS page. Specifically they want to suppress the display of three columns.

Solution: This was a simple change to the LeaveBalancesList XML file.

The original Leave Balance page looked like the following screen print:

After the custom the page looks like the following screen print.

The steps involved are:

(1) Locate C:\Nsl64\emPath\iis\html\LeaveBalancesList.xml and make a backup copy

(2) Modify C:\Nsl64\emPath\iis\html\LeaveBalancesList.xml as follows:

From:

     <tr>
         <th field-name="PMF_DESCRIPTION" id="hdrPmfDescription" width="20%">Type of Leave</th>
         <th field-name="EPF_OPENING_BAL_HOURS_ACCRUED" id="hdrEpfOpeningBalHoursAccrued"
               horizontal-align="right" width="20%">Opening Balance</th>
         <th field-name="EPF_YTD_HOURS_ACCRUED" id="hdrEpfYtdHoursAccrued" horizontal-align="right" width="20%">Hours Accrued</th>
         <th field-name="EPF_YTD_HOURS_PAID" id="hdrEpfYtdHoursPaid" horizontal-align="right" width="20%">Hours Used</th>
         <th field-name="EPF_HOURS_AVAILABLE" id="hdrEpfHoursAvailable" horizontal-align="right" width="20%">Hours Available</th>
     </tr>

To:

     <tr>
         <th field-name="PMF_DESCRIPTION" id="hdrPmfDescription" width="20%">Type of Leave</th>
         
         <th field-name="EPF_HOURS_AVAILABLE" id="hdrEpfHoursAvailable" horizontal-align="right" width="20%">Hours Available</th>
     </tr>

(3) Delete the following cache files if they exist

     C:\Nsl64\emPath\Cache\en_CA\ESS\LeaveBalancesList
     C:\Nsl64\emPath\Cache\en_CA\MSS\LeaveBalancesList
     C:\Nsl64\emPath\Cache\en_US\ESS\LeaveBalancesList
     C:\Nsl64\emPath\Cache\en_US\MSS\LeaveBalancesList
     C:\Nsl64\emPath\Cache\fr_CA\ESS\LeaveBalancesList
     C:\Nsl64\emPath\Cache\fr_CA\MSS\LeaveBalancesList

(4) End your W3WP process in the task manager.

Note that a more sophisticated approach would be to rename the XML files and to create custom a C# program. This approach would prevent the change from having to be re-applied after each service pack.

SQL performance tip - When joining char and varchar columns

Denton Harryman — Sun, 08 Feb 2009 15:22:25 GMT

Issue: A client was experencing slow response from a SQL script ran against the emPath employee benefit (BN3) table and a custom table.

Research: The slow response was directly related to the custom table using VARCHAR field types while the BN3 table used CHAR field types. The workaround on the field type issue was to trim each column when referenced in the where clause. That workaround, along with the fact that columns in the where clause were part of an index, caused very slow response.

Resolution: The resolution is to use RPAD instead of TRIM. This results in a significant performance improvement.

Original where clause:

            where trim(bn3.em_employee_id) = trim(es.em_employee_id)
                     and trim(bn3.bn_benefit) = trim(es.bn_benefit)
                     and trim(bn3.bn_coverage) = trim(es.bn_coverage)

Performance improving where clause:

             where bn3.em_employee_id = rpad(es.em_employee_id,9)
                      and bn3.bn_benefit = rpad(es.bn_benefit,8)
                      and bn3.bn_coverage = rpad(es.bn_coverage,4)

create a view which combines employee deduction (EDF) and DDS

Denton Harryman — Sun, 08 Feb 2009 15:17:30 GMT

Request: A client recently asked for assistance in balancing their 401(k) deductions. See article Q10023 for a summary explanation of the request and solution. The specific portion of that request, which this article details, relates to running SQL scripts to create two views which combine information from EDF and DDS. The goal is to be able to create a spreadsheet with combines specific information from EDF and DDS in a columniar format.

Solution: Use SQL to create two views which combine information from the EDF table and the parsed results from the DDS reports. See articles the related articles, attached to the document, for detail on how the DDS report was parsed. For the purposes of this article accept that the DDS results now exists in a temporary table.

(1) The goal is to allow the user to create a spreadsheet similar to the following. Note that the following is a simplified example. There acutal request involved 19 deduction codes and multiple spreadsheets.

(2) Create views for the EDF information and the DDS parsed table. The sql scripts are attached.

(3) Use Business Objects or Crystal to combine the two views created in step 2.

Note: A simplified SQL statement, used to create a spooled file which was pulled into Excel to create the above spreadsheet, is as follows:

select
edfv.EM_EMPLOYEE_ID,
DEFSAVR_PERCENT,
NDSAVR_PERCENT,
DEFSAVR_YTD,
NDSAVR_YTD,
DEFSAVR_DDS,
NDSAVR_DDS
from view_401k_edf_info edfv, view_401k_dds_info dds
where NDSAVR_YTD <> 0
and edfv.em_employee_id = dds.em_employee_id
/

Resolving RPC (remote procedure call) problems after an install.

Denton Harryman — Sun, 08 Feb 2009 15:09:15 GMT

Resolving RPC (remote procedure call) problems. After an install or a change in the server architecture it is possible to have a red X on the top of emPath screen indicating the RPC could not be started. One possible cause is that the "Supplemental UI Library for Visual J# .Net v1.1" (vjssupuilib) is not installed. The symptom, of this problem, is a gem_server dmp file in the Gembase log files. The gem_server dmp file will indicate there has been an EXCEPTION_ACCESS_VIOLATION.

Resolving pay/deduction overflow issues on the ETF table

Denton Harryman — Sun, 08 Feb 2009 15:00:30 GMT

Question: A client recently asked for assistance in resolving a problem where they were receiving pay/deduction overflow massages on the ETF table when they ran the CTXG report.

The challenge is that with emPath 6.3 there is a limitation of 42 pays and deductions on the employee tax (ETF / ETF2) tables. Making the challenge even harder is that there is a limit of 12 pay and deduction codes on the tax group (TGF) table. The code on the TGF can be wild carded which often causes multiple pays and deductions to be added to the ETF which are not needed.

The best solution to this issue is to upgrade to emPath 6.4 where these limitations have been relaxed. However, if you are using emPath 6.3 the following steps will help in resolving the problem

(1) Identify the employees and tax groups impacted. Following is a SQL statement which will identify those employees with a value of 42 in pay/deduction occurances column.

select em_employee_id, etf_tax_group

from etf

where etf_pay_ded_data_occurs_count = 42;

(2) Review the tax groups identified in step 1 vs the pays and deductions paid in the year as reported on the E-H-R report. When running the E-H-R report enter "T" in the sort by column such that the report will provide totals by tax group.

(3) If possible change the tax group pay/deduction codes to workaround the overflow issue. Following is a before and after screen print of just such a change. You can see code AWARD was removed then MEM***** was replaced by MEMPEN** and MEMR****

(4) Re-run CTXG and verify that the overflow massages no longer appear. The proceed to the running of UPTX.

In this case I received the following thank you email after quickly and successfully resolving the issue:

Note that it is not always this easy. In some cases one has to use SQL to update the ETF and ETF2 tables replacing pay and deduction codes which have zero amounts with codes for those which were not successfully added due to the overflow.

Null values can cause SQLstate 22002

Denton Harryman — Sun, 08 Feb 2009 14:51:05 GMT

It is possible to create customs which allow null values. When nulls are present they can cause a NetExpress (Microfocus COBOL) program to error out with a SQLstate 22002 error. The error message "indicator variable required but not supplied" is less than helpful. There is however a Microsoft knowledge base article which explains the error in more detail.

To workaround this issue in Oracle you can use the decode function in the select statement such that nulls are changed to spaces as shown below:

               SELECT
                   EM_EMPLOYEE_ID,
                   decode(RATE_TABLE_GRADE,NULL,'     ',
                          RATE_TABLE_GRADE) as rateGrade

. . .

A custom validation table and a drop down selection box.

Denton Harryman — Sun, 08 Feb 2009 14:34:31 GMT

Question: I need to create a validation table with a list of our training instructors. The validation table needs to be linked to the “Instructor” field in the set-up > Sessions > Details page. Any information you can share with me is greatly appreciated.

Solution: This can be accomplished using the emPath site_ComponentMetaData feature in combination with a minor XML change. The result is a dropdown box tied to a custom validation table. Following is an example using the (training > set-up > Sessions > Details) page.

Following are the steps involved in the custom shown above:

(1) Create or modify at site_ComponentMetaData xml file in which you edit the TrSessions component as follows.

from: <field name="TR_INSTRUCTOR" table="TR2">

to: <field name="TR_INSTRUCTOR" table="TR2" type="validated" table-code="ZINSTRUCTR" />

(2) Modify the TrSessionDetails xml file as follows:

from: <input type="text" class="edit" id="txtTrInstructor" name="txtTrInstructor" />
<span class="view" id="spnTrInstructor" />

to: <select type="text" class="edit" id="txtTrInstructor" name="txtTrInstructor"> </select>
<span datafld="TR_INSTRUCTOR" class="view" id="spnTrInstructor" name="spnTrInstructor" ></span>

(3) Using the emPath (setup -> validation tables) page add instructors. Following is an example:

(4) Delete the CTrSessionDetails file from your nsl63 cache.

(5) End the aspnet_wp.exe process (if you run Windows 2000) or the w3wp.exe process (if you run Windows 2003).

That is all there is to it.

Compile and test on the business tier in an emPath configuration

Denton Harryman — Sun, 08 Feb 2009 14:05:27 GMT

Question: A client, moving from HR Classic to emPath, recently asked for tips on several issues about compiling and testing on the business tier in an emPath configuration.

Response: Following are responses to the clients specific questions:

(1) Question: Do we compile on the Business Server or just move files over ( Source, executable, etc).

Response: What I have used and seen at several clients is the following approach. Each developer has a copy of NetExpress / Microfocus COBOL on their personal PC. The programs are developed and compiled on their personal PC. Only the executable (exe) is moved to the emPath business tier. The source is normally maintained in a common Visual SourceSafe (VSS).

The command procedures, sort specifications, SQL statements, etc are also normally maintained on VSS and copied to the emPath business tier.

(2) Question: For testing do we have to automatically add to the emPath Menu as an Item to the right or can the program be easily added as part of the list for Batch Reports Menu Item.

Response: Most batch custom development should be added as a batch reports menu item. Adding menu items outside of the batch reports area is normally done for online customs.

Note that while it is possible to create a menu item, outside of the batch reports area, which involks a command procedure it is important to note that this method of invoking a command procedure results in a job running on a client PC not the business tier.

The steps to add a batch reports menu item are simple and quick. They are as follows:

(2.1) In emPath select menu item setup > reports

(2.2) Hover over the edit icon then select "new report entry"

(2.3) Enter new report entry values. An example is shown below which was used to create the menu item to execute the example command procedure in article 50017.

(2.4) Hover over the edit item and select "elements". Note that these are the elements which the user will be prompted for. They will be added to the users PYT file. Logic in your custom command procedure can read these PYT elements and control the processing in your command procedure.

NOTE - Atleast one element is required even if your command procedure does not use it.

(2.5) Hover over the edit item and select "new element"

(2.6) Enter element values as shown in the attached screen print.

(3) Question: Can we test a cobol program from the dos prompt of the server.

Response: Yes you can, however it is not recommended. My recommended approach is to use the batch reporting features from emPath in combination with the custom command procedure recommendations shown in article 50018. This will result in a detailed log showing the results from your test.

However, because there are instances where you may need to run the custom program from the DOS prompt I will share two alternatives for testing from the dos prompt.

(3.1) Recommended Alternative - Create a command procedure and a batch reporting menu item. Then when you submit the job place a future time in the start time (see screne print below). This will result in a command procedure ready to execute placed in your hrlogdir folder. The job will be scheduled to run on the empath business tier using the windows scheduler. However, you can run the job, line by line, from the DOS prompt.

(3.1.1) Logon to the emPath business heir and locate the command procedure created. Note that emPath combines the command procedure you create with additional statements which are very important. Note the the location for this resulting command procedure is where ever you have defined your HRLOGDIR folder for the emPath environment you are testing with.

(3.1.2) Open a DOS prompt, edit the command procedure, then copy paste from the command procedure to the DOS prompt.

(3.2) Second alternative - Create a batch file which defines the environment you wish to test in. You can create this batch file by doing the following:

(3.2.1) Open the RES file you are using to define the test enviornment you are using.

(3.2.2) Create a batch file. Then copy paste the statements beginning with set_env from your RES file to your manual setup batch file.

(3.3.3) change the statements to set= instead of set_env= statements. A SQLServer example is shown below:

set=HR_ROOT=c:\Nsl63\emPath_bc
set=HRDLLS_DIR=c:\Nsl63\emPath_bc\Dll
set=HRRPC_B=c:\Nsl63\emPath_bc\Dll\hrrpc_b32.dll
set=DBIO_B_DLL=c:\Nsl63\emPath_bc\Dll\dbio_odbc.dll
set=DBIO_B=ODBC
set=HRSYSCONFIG=c:\bowpgm\demo63Sql\rpc\sysconfig_emPath63sql.bat
set=HRDATACONFIG=c:\Nsl63\emPath_bc\rpc\dataconfig.bat
set=RSILOGFILE=c:\Nsl63\emPath_bc\Logs\rsitrace.log
set=SP_RDB_ERROR_FILE=c:\Nsl63\emPath_bc\Logs\database_errors.log
set=ROSSPYHR=xxxxxxxx/xxxxxxxx/xxxxxxxx
set=datadrive=c
set=appdrive=c

(3.3.4) Open a DOS prompt

(3.3.5) run the manual setup batch file

(3.3.6) type the specific statements to test your program. For example:

(SET dd_RPT_PR852=%HRRPTDIR%\pr852.rpt)
(if exist �_RPT_PR852% del �_RPT_PR852%)
(pr852.exe)

(4) Question: Of course a command file exist for each of the programs.

Response: Normally a command procedure exist for each custom program or set of related programs.

*** Overall notes on the subject of compiling and testing on the business tier in an emPath configuration

Note that it is possible to move away from COBOL and develop most of your reports and/or interfaces using SQL scripts.

Note that if you have a significant number of customs my recommendation is to have a sperate test and a production server environments.

Note that if you have multiple developers I recommend the use of Microsoft VSS to control the source.

Note that you will be developing much more than just executables. You will also need to consider control of command procedures, SQL scripts, sort control files, FTP control files, email distribution batch files, windows Script Hosting (WSH) files such as VB scripts, and others. I recommend you consider the examples shown in article 10007.

Note that one of the examples, in article 10007, show how to use embedded SQL in a MicroFocus COBOL program. Following is a screen print showing part of that example:

Sorting as part of a command procedure in an emPath environment

Denton Harryman — Sun, 08 Feb 2009 11:58:45 GMT

Question: How do we do a sort in a custom emPath command file ( .CMD )

Response: Following is an excerpt from article 50018 which provides several command procedure examples.

Execute MicroFocus sort:
The following example shows the execute from a command procedure which uses MicroFocus sort.

# statement
01 @echo *** Step 080 Sort 401k and Loan payment
02 (set sortresult=%hrdatdir%\fsav_extract_401k.dat)
03 (if exist %sortresult% del %sortresult%)
04 (�tadrive%:)
05 (cd %hrdatdir%)
06 (mfsort take %cmn_srt%\fsav_srt_spec1.txt)

Statement 06 executes MFSORT and references the file fsav_srt_spec1.txt. This file contains the sort statements. They are shown below:

# statement
07 use fsav_extract_401k_from_cdh.dat org ls record f,90
08 use fsav_extract_loan_payment.dat org ls record f,90
09 use fsav_extract_place_record.dat org ls record f,90
10 give fsav_extract_401k.dat org ls record f,90
11 sort fields (6,2,ch,a,13,11,ch,a)

NetExpress help will explain how to use MFSORT. Documentation can be found as follows:

Open MicroFocus Cobol
Select help > help topics
click on the index tab
enter mfsort

You should find help for command lines, error messages, examples, field instructions, input and output files, key instructions, and record instructions.

A Visual Basic Script to send an email in a custom command procedure

Denton Harryman — Sun, 08 Feb 2009 11:38:58 GMT

Question: It is common to need an email sent as part of a custom command procedure. The solution presented here is the result of a need to replace MapiSend which Outlook security changes have made obsolete.

Solution: The Visual Basic Script attached can be used to send emails from a custom command procedure.

Following is an example of how the VB script is executed:

(call :sub070_email_reports) >> %joblog% 2>&1 || %x%

:sub070_email_reports
@echo ****************************************************************
@echo sub070_email_reports
@echo
time /t
(%AppDrive%:)
(call �iDis%\%cmdproc%_dis.bat)
(set fa=%emailAdmin%)
(set mr=%distribution%)
(set ms="%cmdProc% reports")
(set mm="See attached files")
(set mt="NA")
(set ma1="%LoadRptfile%")
(set ma2="�iTranslatorSummary%")
(set ma3="�iTranslatorErrors%")
(set ma4="�iUpdateEmPathReportfile%")
(set smtp=mail.yourdomain.com)
(cscript �iwsh%\aciSendMail.vbs �% %mr% %ms% %mm% %mt% %ma1% %ma2% %ma3% %ma4% %smtp% //B)
(goto :eof)

Note that the example shown uses several environment variables defined as follows:

fa = From Address for example denton@AppliedConceptsHrPayroll.com
mr = recipient(s) (multiples must be separated by ';' and
must not be ambiguous in default address book.)
ms = subject line
mm = message to be placed in the email body
mt = specifies text file for contents of the mail message
ma1 - ma4 = attahments 1 through 4
smtp = smtp server

Note that MM or MT must be set to NA for not applicable. Also, and of ma1-ma4 can be set to NA.

Note that other environment variables are client specific and could be anything of your choosing.

Note that the command procedure statements shown are a small portion of a larger command procedure. The approach shown places the email VB script in a subroutine. If you would like additional information on using subroutines and the logging of errors then I recommend you review knowledge article 50018- HOWTO: emPath Custom Command Procedures .

Create a temporary PYT file requesting a DDS (Deduction Detail Summary) report.

Denton Harryman — Fri, 06 Feb 2009 21:41:24 GMT

Request: A client recently asked for assistance in balancing their 401(k) deductions. See article 50001 for a summary explanation of the request and solution. The specific portion of that request, which this article details, relates to the clients need to run a DDS report for multiple deduction codes at the same time. The specific request was for 19 deduction codes. The client asked that they submit a single emPath report request which triggered the DDS report to run for 19 deduction codes.

Solution: Use emPath to prompt the user for a date range. Then create a temporary PYT file requesting that the DDS report be ran for multiple deduction codes. Additional articles will be referenced in this article detailing how the resulting DDS report is parsed and SQL is generated to create tables. Then those tables are joined with other tables in a view which can be used by Business Objects or Crystal to generate balancing spreadsheets.

The other benefit of this article is to demonstrate the power of using custom programs PYTparse and PYTinsert.

(1) The delivered PYT process is depicted in the following diagram:

(2) The power of custom program PYTparse is depicted in the following diagram:

(3) The possibilities of custom program PYTparse and PYTinsert are shown in the following diagram and description of how they were used in one specific request:

(3.1) An emPath job request was created prompting the user for a from and a to date. In this specific case the job was named "401k - build 401(k) DDS extract ".

(3.2) A user controlled configuration file was created defining what the custom PYTparse program should do with the 401k report request. The specifics of that configuration file are shown below:

                           name str value
     env variable          len pos len
---- -------------------- --- --- ---
401K FROM_DATE             009 023 008
401K TO_DATE               007 031 008

(3.3) The custom program PYTparse runs and creates a batch command file which sets environment variables as defined in the configuration file defined in step 3.2.

(3.4) The batch command file called such that the environment variables are set. These variables are then available to be used by COBOL programs or SQL scripts.

(3.5) The custom program PYTinsert runs and adds multiple records to a temporary PYT file. In this specific example there are 19 records added to the PYT requesting that the DDS report (program pr852) be ran for 19 different deduction codes using the date range entered in step 3.1 above.

Note that user controlled configuration capability will be added to the PYTinsert program in the near future.

(3.6) The standard delivered pr852 program is ran such that DDS reports are created as requested.

Additional articles will be referenced in this article detailing how the resulting DDS report is parsed and SQL is generated to create tables. Then those tables are joined with other tables in a view which can be used by Business Objects or Crystal to generate balancing spreadsheets.

All of this from a single user report request.