Help with Benefits Processing

[Bill Bowers <bowersb@PSPH.PROVIDENCE.ORG>]

     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