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'

As Of Date in Oracle SQL - Back to the Future

This works:

select *
from
   (select * from ps_job where emplid = '123')
AS OF TIMESTAMP SYSDATE - 1;

Binary Search

Example of:

Function BinarySearch(&arr, &srchVal, &low As integer, &high As integer) Returns integer
  
   If (&high < &low) Then
      Return - 1; /* not found */
   End-If;
  
   &mid = &low + ((&high - &low) / 2);
   If &arr [&mid] > &srchVal Then
      Return BinarySearch(&arr, &srchVal, &low, &mid - 1);
   Else
      If &arr [&mid] < &srchVal Then
         Return BinarySearch(&arr, &srchVal, &mid + 1, &high);
      Else
         Return &mid; /* found */
      End-If;
   End-If;
  
End-Function;

I don't often post random snippets of SQL, but I thought this was a good one. Rare enough to be able to forget it easily, and perhaps will be required again.

This returns schedules based on the schedule id(s) that is/are current for a given employee during a given payment period. If there is no schedule id default to 'UOA01'.

The first part was tricky as sch_assign is an effective dated table, and has no required end date, but each row does end when the next effective date comes into effect. I solved that using a subselect to derive the end_dt. Easy enough.

The next part, defaulting in 'UOA01' was a little trickier. A union to always bring in UOA01, but then a not exists subselect to only bring in where part A of the union returned nothing.

Perhaps easy when you see the solution first, this one took me quite a few attempts, and half the afternoon. Saved for prosperity and that time in the future when I ask myself "I remember doing similar...".

Apart from doing some tricky things with dates this SQL always returns a value. There is a default value in the SQL.

select
  %DateOut(dur)
, b.sched_hrs
from
    (select
      a.schedule_id
    , a.effdt as bgn_dt
    ,
        (select min(x.effdt) - 1
        from ps_sch_assign x
        where x.emplid = a.emplid
        and x.empl_rcd = a.empl_rcd
        and x.effdt < a.effdt) as end_dt
    from %Table(sch_assign) a
    where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
    and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
    and a.schedule_id <> ' '
    and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt)
    union
    select 
      'UOA01'
    , to_date('01/01/2000', 'dd/mm/yyyy') as bgn_dt
    , to_date('31/12/9999', 'dd/mm/yyyy') as end_dt
    from dual
    where not exists
        (select 1
        from %Table(sch_assign) a
        where a.emplid = %Bind(cc_a20_uoa_aet.emplid)
        and a.empl_rcd = %Bind(cc_a20_uoa_aet.empl_rcd)
        and a.schedule_id <> ' '
        and a.effdt < %Bind(cc_a20_uoa_aet.prd_end_dt))) a
, %Table(sch_clnd_vw) b
where b.schedule_id = a.schedule_id
and b.dur between %Bind(cc_a20_uoa_aet.prd_bgn_dt) and %Bind(cc_a20_uoa_aet.prd_end_dt)
and (b.dur <= a.end_dt or a.end_dt is null)
and b.sched_hrs > 0
order by dur