Thursday, December 4, 2014

Debugging PeopleSoft Absence Management Forecast

Forecasting is one of the most useful PeopleSoft Absence Management functionalities. It allows users to know which is going to be the resulting balance when entering an absence. The alternative is to wait until the Global Payroll calendar group is calculated, which naturally is far from being an online calculation.

Although this is a handy functionality, the calculation process does not always return the expected results. For some specific needs, the system element FCST ASOF DT, FCST BGN DT and FCST END DT may be needed. These elements are null for normal Global Payroll runs, so the formulas may behave differently in these runs than in the actual forecast execution. If you ever hit a calculation issue in the forecast process that cannot be solved by looking at the element definitions, you may be stuck.

When this type of issues are found in a normal Global Payroll execution, one handy functionality is to enable the Debug information and then review the Element Resolution Chain page. This page shows the step by step calculation of each element and it is particularly helpful in identifying how an element is calculated.

Unfortunately, this information is not available in the standard forecast functionality. Luckily, it can be enabled using a tiny customisation.

In PeopleSoft HCM 9.1, the forecast functionality is executed from two different places:

DERIVED_GP.FCST_PB.FieldFormula - Abs_ForecastSetup function
FUNCLIB_GP_ABS.FCST_PB.FieldFormula - Abs_ForecastExec function

In both PeopleCode events, you will find a sentence like this one:

SQLExec("INSERT INTO PS_GP_RUNCTL(OPRID, RUN_CNTL_ID, CAL_RUN_ID, TXN_ID, STRM_NUM, GROUP_LIST_ID, RUN_IDNT_IND, RUN_UNFREEZE_IND, RUN_CALC_IND, RUN_RECALC_ALL_IND, RUN_FREEZE_IND, SUSP_ACTIVE_IND, STOP_BULK_IND, RUN_FINAL_IND, RUN_CANCEL_IND, RUN_SUSPEND_IND, RUN_TRACE_OPTN, RUN_PHASE_OPTN, RUN_PHASE_STEP, IDNT_PGM_OPTN, NEXT_PGM, NEXT_STEP, NEXT_NUM, CANCEL_PGM_OPTN, NEXT_EMPLID, UPDATE_STATS_IND, LANGUAGE_CD, EXIT_POINT, SEQ_NUM5, UE_CHKPT_CH1, UE_CHKPT_CH2, UE_CHKPT_CH3, UE_CHKPT_DT1, UE_CHKPT_DT2, UE_CHKPT_DT3, UE_CHKPT_NUM1, UE_CHKPT_NUM2, UE_CHKPT_NUM3,PRC_NUM,OFF_CYCLE) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,%datein(:33),%datein(:34),%datein(:35),:36,:37,:38,:39,:40)", &OprID, &RunCntl_ID, &CalcRunId, &TxnID, 0, &SpaceFiller, "Y", "N", "Y", "N", "N", "N", &ApprByInd, "N", "N", "N", "N", &RunPhaseOptN, &RunPhaseStep, &SpaceFiller, &SpaceFiller, 0, 0, &SpaceFiller, &SpaceFiller, "N", "ENG", &SpaceFiller, 0, &SpaceFiller, &SpaceFiller, &SpaceFiller, "", "", "", 0, 0, 0, 0, "N");

You will notice that the RUN_TRACE_OPTN field is set to "N". If you use "A" instead as the trace option value, you will obtain the Element Resolution Chain:

SQLExec("INSERT INTO PS_GP_RUNCTL(OPRID, RUN_CNTL_ID, CAL_RUN_ID, TXN_ID, STRM_NUM, GROUP_LIST_ID, RUN_IDNT_IND, RUN_UNFREEZE_IND, RUN_CALC_IND, RUN_RECALC_ALL_IND, RUN_FREEZE_IND, SUSP_ACTIVE_IND, STOP_BULK_IND, RUN_FINAL_IND, RUN_CANCEL_IND, RUN_SUSPEND_IND, RUN_TRACE_OPTN, RUN_PHASE_OPTN, RUN_PHASE_STEP, IDNT_PGM_OPTN, NEXT_PGM, NEXT_STEP, NEXT_NUM, CANCEL_PGM_OPTN, NEXT_EMPLID, UPDATE_STATS_IND, LANGUAGE_CD, EXIT_POINT, SEQ_NUM5, UE_CHKPT_CH1, UE_CHKPT_CH2, UE_CHKPT_CH3, UE_CHKPT_DT1, UE_CHKPT_DT2, UE_CHKPT_DT3, UE_CHKPT_NUM1, UE_CHKPT_NUM2, UE_CHKPT_NUM3,PRC_NUM,OFF_CYCLE) values (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18,:19,:20,:21,:22,:23,:24,:25,:26,:27,:28,:29,:30,:31,:32,%datein(:33),%datein(:34),%datein(:35),:36,:37,:38,:39,:40)", &OprID, &RunCntl_ID, &CalcRunId, &TxnID, 0, &SpaceFiller, "Y", "N", "Y", "N", "N", "N", &ApprByInd, "N", "N", "N", "A", &RunPhaseOptN, &RunPhaseStep, &SpaceFiller, &SpaceFiller, 0, 0, &SpaceFiller, &SpaceFiller, "N", "ENG", &SpaceFiller, 0, &SpaceFiller, &SpaceFiller, &SpaceFiller, "", "", "", 0, 0, 0, 0, "N");

By performing this change, you will notice that GP_AUDIT_TBL table starts to be populated with the Element Resolution Chain information. However, it may still not be visible from the page itself, because some tables are only populated temporarily in the forecast execution. In order to enable the access for the forecast runs, you will need to customise the GP_AUDIT_SEG_VW search record by adding the lines in italics to the SQL definition:

SELECT DISTINCT A.CAL_RUN_ID 
 , A.EMPLID 
 , A.EMPL_RCD 
 , A.GP_PAYGROUP 
 , A.CAL_ID 
 , A.ORIG_CAL_RUN_ID 
 , B.RSLT_SEG_NUM 
 , A.FICT_CAL_ID 
 , A.FICT_CAL_RUN_ID 
 , A.FICT_RSLT_SEG_NUM 
 , B.RSLT_VER_NUM 
 , B.RSLT_REV_NUM 
 , B.SEG_BGN_DT 
 , B.SEG_END_DT 
  FROM PS_GP_AUDIT_TBL A 
  , PS_GP_PYE_SEG_STAT B 
 WHERE A.CAL_RUN_ID = B.CAL_RUN_ID 
   AND A.EMPLID = B.EMPLID 
   AND A.EMPL_RCD = B.EMPL_RCD 
   AND A.GP_PAYGROUP = B.GP_PAYGROUP 
   AND A.CAL_ID = B.CAL_ID 
  UNION ALL 
 SELECT DISTINCT A.CAL_RUN_ID 
 , A.EMPLID 
 , A.EMPL_RCD 
 , A.GP_PAYGROUP 
 , A.CAL_ID 
 , A.ORIG_CAL_RUN_ID 
 , A.RSLT_SEG_NUM 
 , A.FICT_CAL_ID 
 , A.FICT_CAL_RUN_ID 
 , A.FICT_RSLT_SEG_NUM 
 , 1 
 , 1 
 , NULL 
 , NULL 
  FROM PS_GP_AUDIT_TBL A 
 WHERE NOT EXISTS ( 
 SELECT 'X' 
  FROM PS_GP_PYE_SEG_STAT B 
 WHERE A.CAL_RUN_ID = B.CAL_RUN_ID 
   AND A.EMPLID = B.EMPLID 
   AND A.EMPL_RCD = B.EMPL_RCD 
   AND A.GP_PAYGROUP = B.GP_PAYGROUP 
   AND A.CAL_ID = B.CAL_ID)

I hope you find this useful. Should you have any question or doubt, I will be happy to assist.

Note: Keep in mind that it is not a good idea to leave the Debug information enabled for Production environments, at least permanently. The time needed to run a forecast calculation with this type of information is significantly higher than without it. So, if you do not want to hit performance issues, my recommendation is to store in a table a flag indicating if the Element Resolution Chain for forecast should be enabled or not.


17 comments:

DHIRAJ KUMAR said...

Hi Javier,

I am getting a error while forecasting saying " CObol Program GPPOLRUN aborted (2,1): " ,i checked the Remote Call utility from people tools it works fine and also compiled the cobol but it does not seems to work ,we are upgrading people tools from 8.52 to 8.53 that when the issue occured .any suggestion for resolution to such issue s.

Javier Delgado said...

Hi Dhiraj

Well, that's a different type of debugging you need. The one I described in the article is useful to debug the calculation itself, but not program abends.

In any case, the error seems to point to a compilation error. Have you compiled all the Cobol programs after the PeopleTools upgrade? Even though some application Cobols are not modified by the PeopleTools upgrade, they may need to be recompiled, as they may include PeopleTools copy books.

You also may want to check the .out files generated in the Application Server LOGS directory. Normally these files should provide valuable information on why the process has aborted. If the files do not appear, you may need to change the following setting in your Application Server configuration:

RCCBL Redirect=1

I hope this helps.

Matt said...

Thanks Javier, that was a very useful tip.

Anonymous said...

Hi Javier

While forecasting the absence, I am getting the following error -
(2,-1) FUNCLIB_GP_ABS.FCST_PB.FieldFormula Name:Abs_ForecastExec PCPC:5087 Statement:54
Called from:GP_ABS_EESS_REQ.GBL.DERIVED_ABS_SS.FCST_PB.Fi eldChange Statement:23 (0,0)

I am getting this error while forecasting the absence through - ESS, MSS and Admin page.

Any idea how to resolve this.

Any pointers would be useful

Thanks

Asha

Javier Delgado said...

Hi Asha

I would first take a look at a PeopleCode trace to check exactly in which statement you're getting the error. I've never seen that error, and it looks quite generic. Have you pasted it completely in your comment?

Please note that the Absence Forecast works using COBOL Remote Call. Having this in mind, you need to have the COBOL compiler installed in your Application Server box. I've seen in some customers that have Application Server separated from the Process Scheduler that COBOL is only installed in the latter, while for Absence Management it needs to be installed in both.

I hope this helps.
Thanks

Anonymous said...

Thanks for your swift reply.

Yes Javier, this is the error that I am getting.
My technical team has taken the Pcode trace and the error is appearing in the
statement -

RemoteCall("PSRCCBL", "PSCOBOLPROG", "GPPOLRUN", "NET_RETURN_CD", &NET_RETURN_CD, "NET_TXN_ID", &NET_TXN_ID, "NET_TXN_NUM", &NET_TXN_NUM, "NET_MSG_ID", &NET_MSG_ID, "NET_MSG_PRM_CNT", &NET_MSG_PRM_CNT, "NET_MSG_PRM1", &NET_MSG_PRM1, "NET_MSG_PRM2", &NET_MSG_PRM2, "NET_MSG_PRM3", &NET_MSG_PRM3);


The process GPPOLRUN is erroring out and this is the error message we are getting -
ErrorReturn-> -1 - (2,-1) DERIVED_GP.FCST_PB.FieldFormula Name:Abs_ForecastExec PCPC:4823 Statement:74Called from:DERIVED_GP.FCST_PB.FieldChange Statement:5




Little background -

We finished with the development and migrated the elements to test environment. That's where we are getting the error while forecasting. The test instance is refreshed from Production.

Also, we have got the recompiled the Cobol processes. Inspite of that the error appears.

Javier Delgado said...

Hi Asha

This is a quite generic error. Have you tried obtaining a SQL trace of the COBOL Remote Call process? Another thing that could help is to set the following psappsrv.cfg setting:

;-------------------------------------------------------------------------
; RemoteCall child process output redirection and Tracing
;
; If this parameter is non-zero,
; the child process output is saved to \_.out (with remote call tracing information),
; and any error output is saved to _.err
; also rmtcall_in., rmtcall_out., rmtcall_cblmsg. files will be saved
;
; If this parameter is zero (default),
; the child process output and error files are created but with no remote call tracing information,
; also rmtcall_in., rmtcall_out., rmtcall_cblmsg. files will not be saved
;
RCCBL Redirect=1

This setting should help to display the COBOL log, which probably displays a clearer error.
I hope this helps.
Thanks!

Javier Delgado said...

Hi all

I got the following comment from Aravind but I deleted it by mistake :P. I'm quoting it here and providing and answer. Sorry Aravind!

"Hi Xavier,
I would to understand the underlying functionality on how the forecasting works in the background when called from self service absence event page , self service forecast balance page and admin absence event forecast and review absence balance forecast balance .i have worked quite extensively in GP from the payroll front , but not much from absence part, hence would like to clearly understand the basics on how the system will propagate ( payroll runs) when forecasting is tried from the above mentioned pages.

kindly suggest any useful backend tables we track for forecasting results.

Cheers
Aravind V"

---

Hi Aravind

The forecast process runs a special version of the Global Payroll process only available through Remote Call, which uses temporary tables that are cleaned after the process is finished. Thus, the best way to get a view on how the calculation went is to enable the Debug as I've indicated in my blog post. Still, you would probably like to look at other tables, like GP_PYE_SEG_STAT or GP_RSLT_PIN, but those tables are not used during the process. The ones used are GPXPYE_SEG_STAT, and so on, but they are deleted at the end of the process.

Thanks!

Aravind said...

Hi Xavier,
Thanks for the reply. Just a quick query . Is there any difference in the calendars to be processed by the forecast run ( absence run) when it is triggered from self service Leave request page and forecast balance page used by admin

in self service, the absence begin date and end date are the only parameters and in forecast balance page it is "As of date" given by admin.

My understanding is that in case of Self service page, it arrives at the calendars to be processed (and then apply the forecasting formula at the end of last calendar being processed) based on the absence end date given by employee(the end date will fall in the last calendar).

In case of Forecast balance page used by Admin, it arrives at the calendars to be processed based on the As of date give by admin and this date woull fall in the last calendar.

In both cases , the there would be a absence run and the forecasting formula is appplied at the end.

Kindly confirm if my understanding is correct.

Cheers
Aravind V

Javier Delgado said...

Hi Aravind

Yes, you're right about the similarities of both calculations. However, there are some differences between both calculations. The calculation run when the forecast functionality is used on the self service page used the Forecast mode. On the other hand, the admin balance check (and also the self service one) uses the Balance Inquiry mode.

I don't recall all the differences, but when I last looked at the COBOL program, the Balance Inquiry mode simulates a zero duration absence on the As Of Date of the balance calculation. This is done to force the absence calculation to go up to that date. However, this only caused (at least in PeopleSoft HCM 9.1) that absence balance queries could not be done on dates on which the employees already had a leave ongoing. I'm not sure if this changed in 9.2.

In any case, other than that, I don't recall any other major difference between both calculation modes.

I hope this helps!

Santhosh Poojari said...

Many Many Thanks Javier! for this excellent blog. Its very helpful and informative.

lee said...

Hi Javier,

I am calling a cobol from AE. The process goes to no success with the following error "COBOL Program SRPCCART aborted (2,-1) MC_CRS_CRD_E.CrsCred.GBL.default.1900-01-01.Fetch.OnExecute". this pops up randomly . When I run the program again for the same data it goes to a success. Any words of wisdom on this ?

Thanks
Lee

Javier Delgado said...

Hi Lee

Sorry for the delay. Normally, when I face those issues, I enable the COBOL SQL trace to check where it's failing. Sometimes you would need to go through a bit of COBOL source code, but most of the times it's possible to figure out what is happening just from the SQL trace.

Now, it didn't come clear to me if the second time you process the program, it actually process the information correctly or it just ends up in Success but without doing any processing.

There are a few guesses I could take in this situation:

* Make sure you have enough COBOL licenses. In some customers, they bought up to 5 concurrent licenses, so when a 6th simultaneous COBOL process was run, it would end in error.
* A second reason for random failure may be concurrency. Does it fails immediately when it does? If it takes a while, it's probably due to another process locking the tables, which may end up in a deadlock.

I hope this helps.
Thanks

Aditya said...

Hi Javier,
Greetings !
I am encountering the following error while performing forecasting leaves for next FY.
Error message-

T&L period ID %1 on calendar for Pay Group %2, Cal ID %3 not found.
%1--> P A2018A01

However, we never used the specified period id instead we used only A2018A01 in the calendars. Also, I tried run the calc [GPPDPRUN] for this calendar and it went to error with the same message.
Observations-
1.Verified template cal group for the calendars in which absence span falls.
2.Able to run payroll calendar but not the absence calendar
3. verified T&L calendars,Periods

Appreciate if you can help me in resolving the issue.

Thanks,
Aditya

Javier Delgado said...

Hi Aditya

It's difficult to tell. What I have experienced in the past is that if you have a retro trigger that goes back in the past to a point the calendar was used, it will pick it up again, but I'm not sure it's your case.

I guess the best way would be to take a look at the trace to understand why it's identifying the period.

Thanks,

Javier

mutyalarao said...

Hi Javier,

I am grateful to you for this article. It was of great help and was a turning point in troubleshooting a forecasting issue which haunted me for days! Finally with the help of your tip, I was able understand that the absences were being processed twice in the element resolution and took a deep dive into Cobols and pinned down the culprit code.

Thank you once again and keep writing wonderful articles!

Javier Delgado said...

Thanks for your comments Mutyala!! :-)