ESI Contributions in Global payroll for India

Common wisdom
Employee State Insurance (ESI) is a type of insurance scheme that addresses Indian employees whose gross wage is INR 15000 or lesser. Employee contributes 1.75% and Employer contributs 4.75 of the monthly gross wage.

Common questions

Question 1: When an employee receives an increment, can the Employer stop ESI deductions and contributions from that month onwards?

The answer is “No”. In the scheme of ESI, the whole financial year is split into two blocks – April to September and October to March.

When an employee has contributed to ESI atleast once in a block, the employee must continue to contribute to ESI for the rest of the block. Lets say, someone receives an increment, from 1-June onwards, that bumps their ESI gross from 14000 to 17000. Since the employee has contributed to ESI in April and May, he/she must continue to contribute to ESI until September.

Question 2: In that case, what is the wage based on which ESI should be calculated?

There is a common misconception that when ESI gross for an employee exceeds 15000 (the limit set by govt and revised from time to time), ESI deductions should be based on the limit. No, ESI should be deducted on the actual ESI gross regardless of the limit.

Going back to the example above, the employee’s ESI deduction in June will be 1.75% of 17000 and not 1.75% of 15000.

How is this handled in PeopleSoft’s Global Payroll for India?

ESI deduction is based on Generation control ESI GC ENROLL, which is based on ESI FM ENROLL GC. I have re-produced the formula and left a few comments also.

Lines 5 through 36 handle block 1, while lines 37 and later handle block 2.

Look out for AC[ESI_FYTDA] on line 27. Anytime that accumulator is greater than zero, the generation control turns true.

Similarly, look out for the historical rule HR[ESI HR 2ND SEG]. The historical rule computes total of ESI deductions made in block 2 (October to March). Anytime it turns out non-zero, the generation control is set to true.


IF AC[ESI GROSS WAGES] > 0 THEN

IF ( AR[ESI AR ENROLLMENT] = 1 AND FM[AGE60 FM] = 1 AND VR[ESI VR EFF STATUS] = 'A' ) THEN /* If enrolled in ESI and Aged < 60 */

IF FM[ESI FM 1ST SEG] = 1 THEN /* In block 1 - Period  b/w 1st April and 30th Sep*/

IF FM[ESI FM 1ST MTH SEG] = 1 THEN

IF AC[ESI GROSS WAGES] < VR[ESI VR GROSS CHECK] THEN /* ESI Gross Check = 15000*/

1 >> FM[ESI FM ENROLL GC]

ENDIF

ELSE

IF SY[LAST HIRE DT] >= SY[PERIOD BEGIN DATE] THEN /*New hire and first payroll*/

IF ( AC[ESI GROSS WAGES] < VR[ESI VR GROSS CHECK] ) THEN

1 >> FM[ESI FM ENROLL GC]

ENDIF

ELSE

IF ( AC[ESI GROSS WAGES] < VR[ESI VR GROSS CHECK] OR AC[ESI_FYTDA] > 0 ) THEN

1 >> FM[ESI FM ENROLL GC]

ENDIF

ENDIF

ENDIF

ELSE /* In block 2 - Period Begins after 30th Sep*/

IF FM[ESI FM 1ST MTH SEG] = 1 THEN

IF AC[ESI GROSS WAGES] < VR[ESI VR GROSS CHECK] THEN

1 >> FM[ESI FM ENROLL GC]

ENDIF

ELSE

IF SY[LAST HIRE DT] >= SY[PERIOD BEGIN DATE] THEN /* New hire and first payroll*/

IF ( AC[ESI GROSS WAGES] < VR[ESI VR GROSS CHECK] ) THEN

1 >> FM[ESI FM ENROLL GC]

ENDIF

ELSE

HR[ESI HR 2ND SEG] >> VR[GP TEMP001 NUM] /* Historical rule to identify ESI deductions in Block 2*/

IF ( AC[ESI GROSS WAGES] < VR[ESI VR GROSS CHECK] OR VR[ESI TOT2ND SEG VAL] > 0 ) THEN

1 >> FM[ESI FM ENROLL GC]

ENDIF

ENDIF

ENDIF

ENDIF

ENDIF

ELSE

0 >> FM[ESI FM ENROLL GC]

ENDIF

Advertisements

Sending emails from Openbravo workflows

Sometime ago, Openbravo made that big leap by integrating their wonderful ERP with a workflow engine. The Workflow Engine of choice was Activiti. You can download the module from here. Refer to the wiki page for more background.

The integration module available on forge is based on Activiti 5.8.

Its a world of goodness – Openbravo and Activiti together. Once you and your customers start experiencing the results, there is no going back. Workflows will climb their way up from a distantly relevant “Nice to Have” to an enriching “That’s easy, lets have that” position in your scheme of ERP implementation.

While discussing Openbravo workflows for an organization, a customer asked “Can I have an email sent at this point in the flow?”. Activiti makes it easy to send out emails through email tasks. There are a number of articles on the internet, so I am not delving into email tasks now.

They are an organization that have their email hosted on a google domain. For example, if the emails were sent out using gmail, the activiti.cfg.xml would look like this:

<property name=”mailServerPort” value=”587″ />
<property name=”mailServerHost” value=”tls://smtp.gmail.com:587″ />
<property name=”mailServerDefaultFrom” value=”sender@gmail.com” />
<property name=”mailServerUsername” value=”sender” />
<property name=”mailServerPassword” value=”*******” />
<property name=”mailServerUseTLS” value=”true” />

They are an organization that have their email hosted on a google domain.

When the workflow was fired, Activiti produced error messages and it was evident that it didn’t like the TLS part of config. It turns out that Activiti 5.8 doesn’t support useTLS parameter.

TLS is addressed in this patch. We applied the patch on top of Activiti 5.8 thats shipped with the integration module and that did the trick for us, and now emails go out from Openbravo.

Optimus HCM – Leave approval workflow

Optmius HCM, the Human Capital Management solution from Fugo, is built on the Openbravo ERP framework. Leave approval process is workflow enabled using Openbravo-Activiti integration module.

Watch the screencast from here

PS_APP_HOME introduced in PeopleTools 8.52

Traditionally, a PS_HOME contained both PeopleTools and Application related files. This meant that you had to maintain four different PS_HOME folders for each of you DEV, TST, QA and PROD environments. For customers that use more than one PeopleSoft application (and there are many many), the number of PS_HOMEs multiply and get un-manageable at some point in time.

PeopleTools 8.52 introduces PS_APP_HOME which co-exists with a PS_HOME, ofcourse along with the PS_CFG_HOME. That sounds like a lot of HOMEs 😉 What does each of these contain? Simply put, PS_HOME – contains all that your PeopleTools CDs offer, while PS_APP_HOME contains all that your application and language media packs offer. PS_CFG_HOME contains all your application server and process scheduler domain configuration fules.

I personally think that the separation offered by PS_HOME and PS_APP_HOME reduces a lot of administration overhead. And a lot of space too. The separation also tremendously streamlines the application of peopletools patches and promises a shorter time-to-production.

Just that you need to ensure you set PS_APP_HOME to the appropriate environment (DEV/QA etc) before invoking PSADMIN. You would also need to enlist both PS_HOME and PS_APP_HOME in your process scheduler for SQR files, because DDDAUDIT would come from PS_HOME and PER010 from you HR PS_APP_HOME.

Finally, PS_APP_HOME is an optional feature. You could still continue to use PS_HOMEs the traditional way

PeopleSoft Data Archive Manager

PeopleSoft’s Data Archive Manager offers an excellent framework for building archival scenarios. Due to its nativity, Data Archive Manager intuitively understands relationship between tables while creating scripts to archive data. There is no compromise on data integrity and no data is lost while archiving. By design, Data Archive Manager offers seamless archive and restore options. Personally, I like the fact that at every stage in the process, Data Archive Manager audits the data moved back and forth to a row-count level of accuracy.

And oh, did I mention that Data Archive Manager is bundled along with PeopleSoft, so you don’t have to buy expensive licenses to third-party tools for managing your PeopleSoft data.

Working with DDL Model in PeopleSoft

PeopleSoft clients that use Oracle as their database, by popular practice, create a database user called SYSADM and nominate SYSADM as the owner of all PeopleSoft objects.

As shown in the picture, when a end-user accesses the PIA, Application server connects to the database using SYSADM is. Likewise, regardless of how a developer is connected to App Designer, either in 3-tier or 2-tier mode, the database connection is always established using SYSADM id.

Most clients do not share the SYSADM credentials to anyone except administrators and they do rightly so as a best practice. Instead, developers are given their own database user ids.

Build Process

In such environments, for a developer to be able to define a record definition in App Designer and access the table in SQL client, these three steps have to be followed:

  1. Define the record in Application Designer (Developer)
  2. Build the record (Administrator)
  3. Grant to developer the access to insert/update/delete/select from the table (Administrator)

Granting access is important, without which, the developer cannot log on to database through a SQL client, (SQL Plus, SQL Developer etc.), and access the table they just created. On describing the table, they would receive a “Table or View does not exist” error.

Administrators follow two methods to grant access:

Method 1. Hand-write the SQL statement to grant access and execute the statement along with the build script.
This method, however, requires a lot of manual effort. Imagine the plight of an administrator when a 100 tables are built.

Method 2. Some others, schedule a script to automatically grant access to all new tables. This method is very efficient, but not necessarily real-time. If the script is scheduled to run every 30 minutes, a developer might have to wait 29 minutes before gaining access to the table.

There’s another method which is both real-time and automatic. The solution lies in the power of DDL Model statements. DDL Model statements are defined at PeopleTools > Utilities > Administration > DDL Model Defaults.

Application designer, when asked to build the script for a table, refers to the DDL Model statement defined for that particular database type – Oracle for the purpose of this blog.

CREATE TABLE [TBNAME] ([TBCOLLIST]) TABLESPACE [TBSPCNAME] STORAGE (INITIAL **INIT** NEXT **NEXT** MAXEXTENTS **MAXEXT** PCTINCREASE **PCT**) PCTFREE **PCTFREE** PCTUSED **PCTUSED**;

[TBNAME] is replaced with the record being built.
[TBSPCNAME] is replaced with the tablespace assigned to the record etc.

To automatically, grant access whenever a table is created, I appended

GRANT SELECT, INSERT, UPDATE, DELETE ON [TBNAME] to PS_DEVELOPER;

PS_DEVELOPER is the database role assigned to all PeopleSoft developers.

With the grant statement included in your DDL, Application designer automatically grants developers with access to any new tables created.

Some known loopholes with this method are:

1. This method doesn’t work when altering tables. Grants will be provided to the temporary table PSYJOB, for example.

2. Grants cannot be provided to different roles. The role is always hard-coded in the DDL Model statement.

Its a nice way to extend the possibilities DDLMODEL though.

CurrentRowNumber function in Peoplecode

Our end user reported a peculiar error on one of the custom pages developed long ago. The structure of the page is shown below.

The “Override” hyperlink redirects to a secondary page where users may enter an override reason. This user was able to use the Override link on first row. But, when they use the Override link on second or third or further rows, they receive an error that states – “Invalid Row number”.

Its almost like Peoplecode yelling back – “I don’t know anything about the row you are talking about”. But, the row is right there in front of our eyes, without which the user cannot click the Override link.

The Override link is attached to APP_RSN_WRK.OVERRIDE record field. The Fieldchange peoplecode behind the rec field is:


&TRANS_ROW = GetLevel0()(1).GetRowset(Scroll.APP_TRANS).GetRow(CurrentRowNumber());

&REASON_ROWSET = &VLN_ROW.GetRowset(Scroll.APP_REASON);

For &I_LOOP = 1 To &REASON_ROWSET.ActiveRowCount

   & REASON_REC = & REASON_ROWSET(&I_LOOP).GetRecord(Record.VCHR_LOG_RULE);
   &WK2_REC = & REASON_ROWSET(&I_LOOP).GetRecord(Record. APP_RSN_WRK);
   &BUSINESS_UNIT = &REASON_REC.BUSINESS_UNIT.Value;
   &TICKER_ID = &REASON_REC.TICKER_ID.Value;
   &TICKER_LINE_NUM = &REASON_REC.TICKER_LINE_NUM.Value;
   &TICKER_CNTRL_ID = &REASON_REC.TICKER_CNTRL_ID.Value;
   &TICKER_RULE_ID = &REASON_REC.TICKER_RULE_ID.Value;

   If &WK2_REC.OVERRIDE_RULE.Value = "Y" Then
      SQLExec("SELECT REASONDESCR FROM PS_APP_RULE_OVRD WHERE BUSINESS_UNIT = :1 AND 
                    TICKER_ID = :2 AND TICKER_LINE_NUM = :3 AND TICKER_CNTRL_ID = :4 AND 
                    TICKER_RULE_ID = :5", &BUSINESS_UNIT, &VOUCHER_ID, & TICKER_LINE_NUM, 
                    &TICKER_CNTRL_ID, & TICKER_RULE_ID, &REASONDESCR);

      If All(&REASONDESCR) Then
         &WK2_REC.REASONDESCR.Value = &REASONDESCR;
      End-If;
   End-If;
End-For;

The error message occurs on the very first line of code. Why?

Lets dissect the code and analyze each part.

From the picture above, it becomes evident that the code errors out only when “GetRow(CurrentRowNumber())” returns an invalid row number.

Peoplebooks’ definition of CurrentRowNumber is:

This function can determine the current row number on the level where the function call resides, or on a higher scroll level. It won’t work on a scroll level below the one where the PeopleCode program resides.

CurrentRowNumber takes an optional number parameter. The parameter is a

A Number specifying the scroll level from which the function returns the current row number. If the level parameter is omitted, it defaults to the scroll level where the function call resides.

In our code, CurrentRowNumber doesn’t accept any function, therefore it will return the row number of the scroll from which the peoplecode event is executed.

Since the peoplecode is located in FieldChange event of “Override” link, which is located in Level 2 rowset, when the user clicks the link on third row, CurrentRowNumber() returns 3.

But is there a third row on the level 1 APP_TRANS rowset? NO!. Thats the problem. In other words, the CurrentRowNumber() and GetRow() functions are operating on two different scroll levels. Any moment, the level 2 scroll has more rows than the level 1 scroll, the error is bound to occur.

To rectify the same, the first line has to be replaced as


&TRANS_ROW = GetLevel0()(1).GetRowset(Scroll.APP_TRANS).GetRow(CurrentRowNumber(1));

Note the parameter value in CurrentRowNumber. Value of 1 forces the function to fetch the rownumber from Level 1 Rowset. That solved the error 🙂