Coverage code and Coverage group code in PeopleSoft Benefits

Coverage code and Coverage group code in PeopleSoft Benefits

Coverage code

coverage code that defines who can be covered by a benefit, use the Health Coverage Codes (COVERAGE_CODES) component.

You define and manage coverage codes for your benefit programs using the Coverage Code table. You will attach these coverage codes to your benefit plan options on the Benefit Program table.

PeopleSoft Manage Base Benefits delivers nine basic coverage codes for which you define a minimum and maximum allowable for each eligible covered person type. These delivered coverage codes are described in this table:

You should create a new Coverage Code or modify your existing Coverage codes as appropriate to include the Covered Person type of Adult Child.

COBRA Coverage Set

A 2-character code that is used to group coverage codes together for COBRA eligibility purposes. When an individual experiences a COBRA event, the system must adjust the range of coverage codes available to each qualified beneficiary. By restricting itself to a specific set of coverage codes, COBRA processing can more accurately determine which benefit options to present to a participant who is eligible for COBRA coverage. If COBRA coverage sets are not established, then COBRA processing may encounter multiple equivalent coverage codes, and eligibility determination may fail.

Understanding Coverage Group Codes

Life insurance and accidental death and dismemberment (AD/D) plans use coverage group codes to establish the maximum lifetime coverage allowed.

For example, if you define a coverage group code with a 500,000 USD maximum, then enter that coverage group code for a supplemental life plan and a group life plan, and associate both benefit plans with a benefit program. If you were to enroll an employee in both of those plans, the system would ensure that the employee's total coverage by both plans does not exceed 500,000 USD.

If the total coverage for a participant exceeds the coverage maximum, the system reduces the total coverage to meet the coverage maximum. When the system processes the deduction, it accumulates the coverage amounts and begins reducing coverage when the coverage group maximum is reached. The system processes plans in order of deduction priority, and if more than one plan has the same priority, it processes in increasing plan type order.

In this case ABC is the new coverage code with coverage Maximum of 10000000USD.Enter the maximum total life and AD/D coverage.

This amount applies to all the life and AD/D benefit plans that you associate with the coverage group code and that you associate with one benefit program. You connect a life or AD/D plan with a coverage group code using the Life and AD/D Plan table.

PeopleSoft Base Benefit vs Benefit Administration

Base Benefit

·         Base Benefits is where you store the Core Tables and Employee Benefit 

Records

·         Base Benefits will allow you to store benefit plans, create benefit programs to group the plans and the employee elections by plan. You can set up the plan rates here as well as coverage codes. Base Benefits works well if you are handling benefit enrollments manually or if you use an outside TPA for your enrollment and just want to store the employee elections in the system. If you set up the rates with the plans it does work with PeopleSoft Payroll in calculating the deduction amounts per pay. 

·         Base Benefits automatically passes deduction information to PS Payroll and 

the maintenance of employee enrollments is manual while BenAdmin determines 

Benefit Program and Benefit Plan eligibility based on the rules that are 

setup and it ensures data integrity. Employee Self-Service Enrollment is 

also available when using BenAdmin. It is integrated with COBRA processing 

as well as Benefits Billing. 

Benefit Administration

·         Benefits Administration uses what you set up in base benefits, but also creates events which can be triggered to allow employees to enroll or change their elections (think new hire, annual enrollment, family status events, etc.). It also allows you to create eligibility rules to determine what plans employees are eligible to enroll in. You cannot use Ben Admin if you do not have base benefits set up or it won't have any plans and programs to tie to events. When events are finalized in Ben Admin, it will load the benefit elections into the base benefits table. See how it all relates? If you handle benefits enrollments in house, Ben Admin might be the way to go for you as it will also allow you to use self-service for the employees to make their elections. 

·         BenAdmin automates the administration of Benefits using 

Eligibility Rules and Event Rules.

·         Once BenAdmin events are finalized, it writes the employee elections to 

Base Benefits.

An error has occurred that has stopped this transaction from continuing:PeopleSoft Error

This is an error while we try to access a page in Peoplesoft PIA.

Possible Reasons & Solutions
------------------------------------

1.  This message appears due to 0 rows in PS_INSTALLATION Table

Solution:This message appears in Enterprise Portal due to 0 rows in PS_INSTALLATION Table. To fix this issue: Insert a row in PS_INSTALLATION table and  restart the appservers

2.  There might be some Error in  events PreBuild, Activate, PostBuild or any FieldDefault Peoplecode

Solution:Correct the code and check the page again

3.  Record underlying in the page is not build properly.

Solution:Build the record and try the page again.

PeopleSoft Interview Questions

1.      Difference between Field Edit and Field Default?

Field Change peoplecode is for recalculating field values based on changes 

made to other fields 

Case1: suppose u have 2 fields one is rate and other is quantity and there is another field which shows the total cost so whenever a value is changed in any of the above 2 field (rate and quantity) the total cost will also change so on field change event of these 2 fields u can write code to calculate the total cost 

Field edit event is used for validating field values if the field doesn’t pass the validation an error message is displayed and the page is displayed with the field marked in red 

Case 2: the quantity should not exceed 1000 if it does the system should display an error 

so u can write a peoplecode for that which will check the quantity if it is greater than 1000 error message will be displayed

2.    Difference b/w Save Prechange and Save Postchange

In theory, SavePreChange is triggered just before the component buffer data is updated to database, so this is the last event in which you can manipulate component buffer data inorder to get updated in database.

The SavePreChange event is initiated after SaveEdit completes without errors.

·         SavePreChange PeopleCode provides one final opportunity to manipulate data before the system updates the database; for instance, you could use SavePreChange PeopleCode to set sequential high-level keys

·         If SavePreChange runs successfully, a Workflow event is generated, and then the Component Processor issues appropriate Insert, Update, or Delete SQL statements.

·         An Error statement in SaveEdit PeopleCode displays a message and redisplays the component without saving data. A Warning statement enables the user to click OK and save the data, or to click Cancel and return to the component without saving.

·         Used to change display characteristics and validations

·         SavePreChange PeopleCode can be associated with record fields, components, and component records.

After the Component Processor updates the database, it initiates the SavePostChange event. You can use SavePostChange PeopleCode to update tables not in your component using the SQLExec built-in function.

·         Performs after SavePreChange and Workflow have completed successfully. SavePostChange is not executed if workflow fails or component is not saved due to any error.

·         Do not use Error and Warnings in this event

·         The system issues a SQL Commit statement after SavePostChange PeopleCode completes successfully.

·         Other than updating tables that are not in your component, it is also used to send an email, say for approval.

·         SavePostChange PeopleCode can be associated with record fields, components, and component records.

 Case1

Let’s take Personal Data component in HR as an example, when you add a new person Person ID would be NEW, so before the data is updated to database we want the Person ID to be generated and assigned to EMPLID field. So this is done in SavePreChange. 
Now once the data is updated to database we want this data to be sent to other PeopleSoft applications like CRM, ELM etc., for this we need to trigger the Asynchronous service operation. Since this need to be triggered only after the component data is updated to database (after all validations) we would use SavePostChange to trigger it.

Case2

The difference is more into functional than technical. You can write sql to 
update other tables in both SAVE PRE change & POST change it will work. But 
if you write to update other table in pre change depends on status of 
current component, you may get wrong data.
Because the component can fail at any time after pre change. 
For example you want to update location of employee to other table each 
time a transfer happened, for this if you write sql in save pre change, the 
new location will be updated in other table first, then an 
error happened in post change so system will roll back the changes 
in original component 
and will have the old location only But your table saving location already 
got updated & will have new location. But if you write in save post change 
it will update only when original component saved successfully.

Execution Order of Peoplesoft Events

SEARCHINIT, SEARCHSAVE, ROWSELECT, PREBUILD, FIELDDEFAULT, FIELD FORMULA, ROWINIT, POSTBUILD, PAGEACTIVATE, FIELDEDIT, FIELDCHANGE, POP_UP, ROWINSERT, ROWDELETE, SAVEEDIT, SAVEPRECHANGE, WORKFLOW, SAVEPOSTCHANGE

Create a hyperlink to call a secondary page from the Main page

1. select Insert, then selectPush Button/Hyperlink.
2. Click where you want the upper, left-hand corner of the control to begin.
3. A push button of small image size appears.
4. Double-click the push button to access the Push Button/Hyperlink Properties dialog box.
5. On the Type tab, select whether the control appears as a push button, hyperlink, or action widgit.
6. Select a destination type from the Destination drop-down list box.
7. See Specifying Type Properties for Push Buttons or Links.
8. Select the record and field names with which you want to associate the push button or link.
9. Select any of the remaining Type options based on your destination type.
10. On the Use tab, select the purpose of the control.
11. See Specifying Use Properties for Push Buttons or Links.
12. Save the page.

Hyperlink in the Main Page

Use a Domodal function to call the secondary page from main page.

Hyperlink Properties

Secondary Page Properties

In the hyperlink properties we mention the field name,In the field change event of the same field write this code

DoModal(Page.Secondary page name, "Sample Secondary Page", - 1, - 1, 1, Record.table name, CurrentRowNumber());

In  secondary page properties we have the OK and Cancel button,to Activate the functionality of  OK button insert a secondary page button in the main page and assign the secondary page name

Step1:Insert secondary page

Step2:Assign the secondary page name,which is triggered using the hyperlink

OK Button Activation

Update using case in Application engine SQL

Please find the sample code to update a table using case :

UPDATE TABLE1 MAIN
  SET MAIN.CHAR_TO = (CASE WHEN MAIN.REG_TEMP = 'R'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE1' WHEN MAIN.REG_TEMP = 'R'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE2' WHEN MAIN.REG_TEMP = 'S'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE3' WHEN MAIN.REG_TEMP = 'S'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE4' WHEN MAIN.REG_TEMP = 'W'
   AND MAIN.POSTAL2 IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE5' WHEN MAIN.REG_TEMP = 'W'
   AND MAIN.POSTAL2 NOT IN (
 SELECT POSTAL
  FROM TABLE2) THEN 'VALUE6' ELSE ' ' END )
 WHERE MAIN.PROCESS_INSTANCE = %ProcessInstance

Where value1 to value6 varies based on the case statement.Here the CHAR_TO field is assigned different value based on the case statements.

Concatenation of strings in application Engine using SQL

Please find the sample code to Concatenate using Application Engine SQL

UPDATE Table1 MAIN
  SET MAIN.PASSWD = (
 SELECT 'TXT'|| SUBSTR(REPLACE(NID.NATIONAL_ID %Comma '-' %Comma'') %Comma -4 ) || EXTRACT(YEAR
  FROM SYSDATE)
  FROM Table2  NID
 WHERE NID.EMPLID = MAIN.EMPLID
   AND NID.NATIONAL_ID_TYPE = 'PR')
 WHERE EXISTS(
 SELECT 'X'
  FROM Table2  NID1
 WHERE NID1.EMPLID = MAIN.EMPLID
   AND NID1.NATIONAL_ID_TYPE = 'PR')
   AND MAIN.PROCESS_INSTANCE = %ProcessInstance

IF NID.NATIONAL_ID=1234567
AND YEAR=2015

Then Output will be -->TXT12345672015

Application Engine Trace-Peoplecode & SQL

Steps to  trace an application engine process using the Process Definitions

1. Navigate to PeopleTools > Process Scheduler > Processes
2. Enter your Application Engine name
3. Navigate to override options tab.
4. Enter the parameter list as listed below:-Trace 7 -TOOLSTRACEPC 4044

This will trace SQLs inside of your application engine as well as peoplecode. Save the process definition and go ahead and run your program. To examine the trace files, just navigate to the process monitor, click details on your process, then View Log/Trace.

First and last day of previous Month using Oracle SQL

To get the First and last day of previous month use following code snippet::

a) SYSDATE
select trunc(trunc(SYSDATE,'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

 b)For any Input Date::
select trunc(trunc(TO_DATE('01-JAN-15','DD-MON-YYYY'),'MM')-1,'MM') "First Day of Last Month",trunc(sysdate,'MM')-1 "Last Day of Last Month" from dual;

Where '01-JAN-2015' is the input Date

PeopleSoft DB Link

A database link (dblink) links databases. It allows you to query against other databases using the syntax TABLENAME@DBLINK.
In a scenario where you are trying to access the finance data from HCM environment,at that particular point you can create a DB link to do the same.

CREATE public database link DBlink Name CONNECT TO Username IDENTIFIED BY password using 'databasename';

PS:In order to execute this query use a data mover.

Process Scheduler::Location of the Log and SQL Trace Files

In case if we are unable to view the log file from Peoplesoft process monitor there is another method to view the out file and the log file.

Navigate to the following location::

Operating System	Log Directory
Windows	PS_CFG_HOME\appserv\prcs\<Database Name>\LOGS
UNIX	PS_CFG_HOME/appserv/prcs/<Database Name>/LOGS
OS390 UNIX System Services	PeopleSoft Process Scheduler Server: $PS_HOME/appserv/prcs/<Database Name>/<Log Directory>/_PSPRCSRVLOG Distribution Agent Server: $PS_HOME/appserv/prcs/<Database Name>/<Log Directory>/_PSDSTSRVLOG

In the UNIX Box,Navigate to the location(HOME\appserv\prcs\<HCMDEV>\LOGS) ,let the Database Name be HCMDEV. At that particular location you will find many folders named after dates.Go to the recent date folder ,then you will come across folders named after the Report ID (which you can find in process monitor view trace page).Check for the apt report id and get your log file and  Out file.

Oracle SQL:: Convert a field from NOT NULL to NULL

In order to convert a field from NOT NULL to NULL use below stated SQL :

ALTER TABLE TABLENAME MODIFY FIELD NAME NULL;

In order to convert a field from  NULL to NOT NULL use below stated SQL :

ALTER TABLE TABLENAME MODIFY FIELD NAME NOT NULL;