Ben Admin Snapshot Error

I ran into this error with the Ben Admin Snapshot process. It was the second time I ran into it, so I thought that I had better make sure that I got this in my notes so I would remember it.

Here’s the error…

Costs Not Found For Pgm/Plan/Optn: <benefit program>/<plan type>/<option code>.
 
Application Program Failed
 In Pgm Section  : MOVE-TO-PDEFN                                                                                                                                               

Application Program Failed
 In Pgm Section  : TABLE-ACCESS(PSPBATBL)                                                                                                                                      
 
 
Application Program Failed
 In Pgm Section  : MAIN(PSPBASCH)                                                                                                                                              

Does the Cost Exist?

First, you need to check to see if the cost mentioned exists. Find the option id from the Option table:

SELECT * FROM PS_BEN_DEFN_OPTN A
WHERE A.BENEFIT_PROGRAM = '<benefit program>'
AND A.PLAN_TYPE = '<plan type>'
AND A.OPTION_CD = '<option code>';

Then, see if that exists in the Cost table:

SELECT * FROM PS_BEN_DEFN_COST A
WHERE A.BENEFIT_PROGRAM = '<benefit program>'
AND A.PLAN_TYPE = '<plan type>'
AND A.OPTION_ID = <option id from above>;

Orphans?

If the cost exists, it may be because you have extra orphaned rows. Try this SQL to see if you get any rows

SELECT * FROM PS_BEN_DEFN_COST A
WHERE A.EFFDT = (SELECT MAX(EFFDT)
                 FROM PS_BEN_DEFN_PGM 
                 WHERE BENEFIT_PROGRAM = A.BENEFIT_PROGRAM)
AND NOT EXISTS (SELECT 'X' FROM PS_BEN_DEFN_OPTN B
                WHERE A.BENEFIT_PROGRAM = B.BENEFIT_PROGRAM
                AND B.EFFDT = (SELECT MAX(EFFDT)
                               FROM PS_BEN_DEFN_PGM 
                               WHERE BENEFIT_PROGRAM = A.BENEFIT_PROGRAM)
                AND A.PLAN_TYPE = B.PLAN_TYPE
                AND A.OPTION_ID = B.OPTION_ID);

If you do have rows, you’ll need to delete them. (Take the normal precautions: backup first, etc)

DELETE FROM PS_BEN_DEFN_COST A
WHERE A.EFFDT = (SELECT MAX(EFFDT)
                 FROM PS_BEN_DEFN_PGM 
                 WHERE BENEFIT_PROGRAM = A.BENEFIT_PROGRAM)
AND NOT EXISTS (SELECT 'X' FROM PS_BEN_DEFN_OPTN B
                WHERE A.BENEFIT_PROGRAM = B.BENEFIT_PROGRAM
                AND B.EFFDT = (SELECT MAX(EFFDT)
                               FROM PS_BEN_DEFN_PGM 
                               WHERE BENEFIT_PROGRAM = A.BENEFIT_PROGRAM)
                AND A.PLAN_TYPE = B.PLAN_TYPE
                AND A.OPTION_ID = B.OPTION_ID);

Details

Here’s what’s happening. The program loads two arrays. You can find the option array like this:

SELECT STMT_TEXT
FROM PS_SQLSTMT_TBL
WHERE PGM_NAME = 'PSPBATBL'
AND STMT_TYPE  = 'S'
AND STMT_NAME  = 'PLAN';

This is the data for the W-OPTN array. You’ll probably get something like this:

SELECT A.EFFDT , 
  A.PLAN_TYPE , 
  A.DISPLAY_PLN_SEQ , 
  A.MIN_ANNUAL_CONTRIB , 
  A.MAX_ANNUAL_CONTRIB , 
  A.WAIVE_COVERAGE , 
  A.RESTRICT_ENTRY_MM , 
  A.EVENT_RULES_ID , 
  A.DEP_RULE_ID , 
  A.BENEFIT_PROGRAM 
FROM PS_BEN_DEFN_PLAN A 
WHERE A.BENEFIT_PROGRAM='<benefit program>' 
AND A.PLAN_TYPE       <>'00' 
AND A.EFFDT           <= <as of date>
AND A.EFFDT            = 
  (SELECT MAX(J.EFFDT) 
  FROM PS_BEN_DEFN_PLAN J 
  WHERE J.BENEFIT_PROGRAM=A.BENEFIT_PROGRAM 
  AND J.PLAN_TYPE        =A.PLAN_TYPE 
  AND J.EFFDT           <= <as of date>
  ) 
ORDER BY A.BENEFIT_PROGRAM ASC , 
  A.EFFDT DESC , 
  A.PLAN_TYPE ASC;

Then, the array for the costs comes from this:

SELECT STMT_TEXT
FROM PS_SQLSTMT_TBL
WHERE PGM_NAME = 'PSPBATBL'
AND STMT_TYPE  = 'S'
AND STMT_NAME  = 'COST';

This is the data for the W-COST array. You should see something like this:

SELECT PLAN_TYPE ,
  OPTION_ID ,
  COST_ID ,
  COST_TYPE ,
  ERNCD ,
  BENEFIT_PROGRAM ,
  EFFDT
FROM PS_BEN_DEFN_COST
WHERE BENEFIT_PROGRAM='<benefit program>'
AND EFFDT            = <as of date>
ORDER BY BENEFIT_PROGRAM ASC ,
  EFFDT DESC ,
  PLAN_TYPE ASC ,
  OPTION_ID ASC ,
  COST_ID ASC;

So, here is what happens. The program walks through the two arrays matching the rows up. If the next row in the costs array doesn’t match the next row in the options array, it assumes the cost doesn’t exist for that option. That works fine until you get some extra rows in there. When it finds extra rows, it thinks it’s a mismatch.

                       IF  WCOST-IDX  =  COST-START OF W-WK
                               AND NOT PLAN-TYPE-NO-COST-REC
                                       OF W-PLAN(WPLAN-IDX)
                               AND NOT OPTION-TYPE-PROGRAM
                                       OF W-OPTN(WOPTN-IDX)
                               AND NOT OPTION-TYPE-WAIVE
                                       OF W-OPTN(WOPTN-IDX)

                           DISPLAY 'Costs Not Found For Pgm/Plan/Optn: '
                                   BENEFIT-PROGRAM OF BATBL '/'
                                   PLAN-TYPE OF W-PLAN(WPLAN-IDX) '/'
                                   OPTION-CD OF W-OPTN(WOPTN-IDX) '.'
                           MOVE 'MOVE-TO-PDEFN'
                                   TO  ERR-SECTION OF SQLRT
                           SET RTNCD-USER-ERROR OF SQLRT  TO  TRUE
                           PERFORM ZZ000-SQL-ERROR
                       END-IF