Re: Help with Benefits Processing

[Brian Nice <bnice@CCCI.ORG>]

We did something similar where we created an audit table on the health_dependnt and dependent_benef records so we could track adds/changes/deletes to those tables.  Each time we ran the program to decide what to send to our benefits administrator, we could get the datetime stamp of the last time it was run and compare that to the audit datetime stamp to get the changes.  Maybe something similar would work in your case.

hth
Brian

>>> Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG> 12/10 6:39 PM >>>
     We are using PeopleSoft 6.0, with Oracle.

     I am creating a data file to be sent to Connecticut General.

     I need to report dependents which have terminated coverage as well as
     dependents which have added coverage.

     I am currently using the ps_health_dependnt and ps_dependent_benef
     tables but an unable to determine dependents who have terminated
     coverage.

     Here is the select:


     begin-SELECT

     DB.Emplid
     HD.Plan_Type
     DB.Dependent_Benef
     DB.SSN
     DB.Name
     DB.Birthdate
     DB.Sex
     DB.Relationship
     HD.Emplid
     HD.Dependent_Benef
     HD.Effdt
       do 1400-Write-Dependent
     FROM  PS_Health_Dependnt HD,
           PS_Dependent_Benef DB

     WHERE HD.Emplid = &J.Emplid
           AND HD.EffDt = (SELECT Max(EffDt)
                        FROM PS_Health_Dependnt A
                        WHERE A.Emplid = HD.Emplid
                        AND A.EMPL_RCD# = HD.EMPL_RCD#
                        AND A.COBRA_EVENT_ID = HD.COBRA_EVENT_ID
                        AND A.PLAN_TYPE = HD.PLAN_TYPE
                        AND A.BENEFIT# = HD.BENEFIT#)
           AND HD.Empl_Rcd# = 0
           AND HD.Benefit#  = 0
           AND HD.Plan_type = &HB.plan_type
           AND HD.Dependent_Benef = DB.Dependent_Benef
           AND HD.Emplid = DB.Emplid

     ORDER BY HD.effdt desc



     end-SELECT


     The &j.emplid is the emplid from the job table, &hb.Plan_type
     is the plan from ps_health_benefit



     Any ideas?

     Bill