Effective date logic occupies a major part in the PeopleCode. Writing effective date logic in PeopleCode section requires the developers to join all the key values in the sub query to bring the exact output. It leads the developer to write more detailed SQL statement in the PeopleCode section.
PeopleCode provides an alternate way with the Meta-Sql function
PeopleCode provides an alternate way with the Meta-Sql function ‘%EffdtCheck’. This function collects the three parameter values.
- The record which needs to verify for maximum effective dated value
- Alias entry of record from the main SQL part
- Date to which the maximum effective date to be validated.
SQLExec(“SELECT ‘Y’ FROM %TABLE(:1) PY WHERE PY.COMPANY = :2 AND PY.PAYGROUP = :3 AND PY.COUNTRY = :4 AND %EFFDTCHECK(:1 PY1, PY, %CURRENTDATEIN) AND PY.EFF_STATUS = ‘A'”, Record.PAYGROUP_TBL, &COMPANY, &PAYGROUP, &Country, &UNCD_FLG);
The above SQL when reaches the database server will be restructured as
FROM PS_PAYGROUP_TBL PY
WHERE PY.COMPANY = ‘<&COMPANY>’
AND PY.PAYGROUP = ‘<&PAYGROUP>’
AND PY.COUNTRY = ‘<&Country>’
AND PY.EFFDT = (SELECT MAX(PY1.EFFDT)
FROM PS_PAYGROUP_TBL PY1
WHERE PY1.COMPANY = PY.COMPANY
AND PY1.PAYGROUP = PY.PAYGROUP
AND PY1.COUNTRY = PY.COUNTRY
AND PY1.EFFDT <= SYSDATE)
AND PY.EFF_STATUS = ‘A’
The above statement will in turn build the Maximum effective dated logic for Paygroup table to collect the latest row.
- Reduces the developer level of coding to the minimal number of lines
- When Key structure of the record is altered, the sub query to find the maximum effective date need not be rewritten.
The %EffdtCheck will not check for Maximum effective sequence (EFFSEQ) or Effective status (EFF_STATUS) validation.