Commitments / Encumbrances

After building this twice now, both while specs were being written (sigh) I suggest the following approach.

Multiple App Engines.

Main: Contains main outer loop(s)
Grouping Code: Contains calls to Element AEs
Element: Contains Section for each Element calculation.
Functions: Sections for populating component arrays and the like
Population: AE that deals only with splitting, account code mapping and population of temp tables.

... something lik ethat. Would make things more legible, pluggable.

The need for an environment that has accurate pay run data to compare against cannot be stressed enough, preferably dev. The lag between code changes and migrations and runs in downstream envs that have comparable data can be days, very slow and confusing.

Application Engine Performance

1. Reduce writing to log or trace files. I've seen this add 100%+ to processing times. Really.

2. Reduce calls to DB. If possible use Commponent arrays instead of calling the db 1,000,000 times. Searching throug hsmall arrays i far quicker, usually because of the network, not the DB.

Progress Bar for Processes

1. Create an HTML area on a page where you are going to query the progress of a Process.

2. You need to setup a "progress" table for your process. You will need to update the values from within the process while it is running.

e.g. fields EMPLOYEES_TOTAL | EMPLOYEES_PROCESSED etc.

You will probably have to run a precursory SQL to count how many rows are expected to be processed up front, and then update the progress as each row or set of rows is processed.

3. Here is some sample code to update the HTML area with details of how the process is progressing. CC_DERIVED_UOA.DESCRLONG is the HTML Area on the page.

Function UpdateProgressBar(&title, &complete, &total) Returns string
   
   Local string &str = "";
   &str = &str | "   <tr><td style=""font-size:small"">" | &title | ":&nbsp;</td>";
   
   /* Calc pct complete. */
   If &total > 0 Then
      &pct = (&complete / &total) * 10;
   Else
      &pct = 10;
   End-If;
   
   /* Update bars. */
   For &i = 1 To 10
      If &pct >= &i Then
         If &total = 0 Then
            &str = &str | "      <td BGCOLOR=""#CCCCCC"">&nbsp;</td>"; /* grey */
         Else
            &str = &str | "      <td BGCOLOR=""#AAFFAA"">&nbsp;</td>"; /* green */
         End-If;
      Else
         &str = &str | "      <td BGCOLOR=""#FFAAD4"">&nbsp;</td>"; /* red */
      End-If;
   End-For;
   
   &str = &str | "      <td style=""font-size:x-small"">&nbsp;&nbsp;" | &complete | " / " | &total | "</td>";
   &str = &str | "   </tr>";
   
   Return &str;
   
End-Function;



Local Rowset &rsCC_PROGRESS_UOA = CreateRowset(Record.CC_PROGRESS_UOA);
&rsCC_PROGRESS_UOA.Fill(); /* only ever one row */
Local Record &recCC_PROGRESS_UOA = &rsCC_PROGRESS_UOA(1).GetRecord(Record.CC_PROGRESS_UOA);

&strl_progbar = &strl_progbar | "<table>";
&strl_progbar = &strl_progbar | UpdateProgressBar("Employees", &recCC_PROGRESS_UOA.CC_FULLTM_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_FULLTM_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Casuals", &recCC_PROGRESS_UOA.CC_CASUAL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_CASUAL_TOTAL.Value);
&strl_progbar = &strl_progbar | UpdateProgressBar("Scholarships", &recCC_PROGRESS_UOA.CC_SCHOL_COMPLETE.Value, &recCC_PROGRESS_UOA.CC_SCHOL_TOTAL.Value);
&strl_progbar = &strl_progbar | "</table><br>";

CC_DERIVED_UOA.DESCRLONG.Value = &strl_progbar;

Filter Retro Payments


SELECT
 (select pin_nm from ps_gp_pin where pin_num = a.pin_num) AS PIN_NM
, A.EMPLID
, A.EMPL_RCD
, A.PIN_NUM
, Sum(A.CALC_RSLT_VAL)
, A.RATE_RSLT_VAL
, Sum(A.UNIT_RSLT_VAL)
, Sum(A.PCT_RSLT_VAL)

FROM 
  PS_GP_RSLT_ERN_DED A
, PS_GP_PYE_PRC_STAT C

WHERE A.PIN_NUM = 13265 --#PinNum
AND A.EMPLID = '1000269' --$Emplid
AND A.EMPL_RCD = 0 --#Empl_Rcd
AND A.CAL_RUN_ID = '2010F12' --$Calendar_ID

AND C.EMPLID = A.EMPLID
AND C.CAL_RUN_ID = A.CAL_RUN_ID
AND C.EMPL_RCD = A.EMPL_RCD
AND C.GP_PAYGROUP = A.GP_PAYGROUP
AND C.CAL_ID = A.CAL_ID
AND C.ORIG_CAL_RUN_ID = A.ORIG_CAL_RUN_ID
AND C.PRD_TYPE = 'C'