Interface

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

     We are developing an interface file for Connecticut General Life
     Insurance company 'CIGNA'.

     I am encountering problems getting all employees reported.  We need to
     notify CIGNA when people are added and deleted from benefits.
     Terminated employees are not being reported, even after i commented
     out the requirement for hb.coverage_elect = 'E'.

     At this time I am extracting data from the benefit tables, creating
     (truncating and loading) separate tables for employees and benefits.
     Following the loading of the tables, I dump out the data creating a
     written report and extract file.

     Below is the select used for the extract, if anyone has been here
     before or has any ideas please let me know.  Thanks!

     Bill


     *****************************************************************''

     begin-SELECT !loops=50
     !Personal Data
     PD.EMPLID
       !show '&PD.Emplid: ' &Pd.Emplid
     PD.Name            !&PD.Name (+1,1)
     PD.SSN
       !show '&PD.SSN: ' &PD.SSN
     PD.Address1
     PD.Address2
     PD.City
     PD.State
     PD.ZIP
     PD.Sex
     PD.Birthdate
     !to_char(&PD.Birthdate, 'YYYYMMDD')    &Birthdate

     !Employment
     E.Hire_Dt
     !to_char(E.Hire_Dt, 'YYYYMMDD')       &Hire_dt
     E.Termination_dt
     !to_char(E.Termination_Dt, 'YYYYMMDD')    &Termination_dt

     J.Company       !&J.Company
     J.Empl_Status   !&J.Empl_status (,34)
     !Job
     J.Emplid   !&J.Emplid (,25)
     J.Empl_Rcd#
     J.Action_Dt        !&J.Action_Dt (,38)
     j.Action
     j.Action_Reason

     !Health_benefit
     HB.Plan_Type       !&HB.Plan_Type (,40)
       !show '&hb.plan_type: ' &hb.Plan_type
     HB.Benefit_Plan    !&HB.Benefit_Plan (,46)
     HB.Covrg_Cd        !&HB.Covrg_CD (,56)
        let $Covrg_Cd = &HB.Covrg_cd
     HB.Benefit#
     HB.Effdt
     !to_char(HB.Effdt, 'YYYYMMDD')    &Effdt
     !HB.Coverage_Elect &HB.Coverage_Elect (,58)
     HB.Coverage_Elect_Dt
     HB.Coverage_Begin_Dt
     !to_char(HB.Coverage_Begin_Dt, 'YYYYMMDD')    &covrg_begin_dt (,60)

       let $New_Coverage_Begin_dt = &hb.Coverage_Begin_dt

       let $Termed_Emp_Ind = 'N'

       if (&J.Empl_Status ='T'
             or &J.Empl_Status = 'R'
             or &J.Empl_Status = 'D')
             let $Termed_Emp_Ind = 'Y'
             do 300-Recalc-Date
       end-if

     HB.Coverage_Elect

     BP.Provider        &BP.Provider

        do 200-Get-FSA   !Get Flexable Spending Account Info
        do 1200-Write-Emp !Loads Employee Table

        If $covrg_cd <> '1'
           and $covrg_cd <> 'W'
          do 500-get-dep-benefits
        end-if

     FROM  PS_Personal_Data  PD,
           PS_Employment     E,
           PS_Job            J,
           PS_Health_Benefit HB,
           PS_Benef_Plan_Tbl BP

     WHERE J.Effdt = (SELECT MAX(Effdt) FROM PS_Job
                       WHERE Emplid = J.Emplid
                         AND Empl_Rcd# = J.Empl_Rcd#
                         AND Effdt <= $AsOfDate)
       AND J.Effseq = (Select MAX(Effseq) FROM PS_Job
                        WHERE Emplid = J.Emplid
                          AND Empl_Rcd# = J.Empl_Rcd#
                          AND Effdt = J.Effdt)
       AND J.Empl_Rcd# = 0
       AND J.Company in [$Company_Reqstd]
       AND E.Emplid = J.Emplid
       AND E.Empl_Rcd# = 0
       AND PD.EmplID = J.EmplID
       AND HB.EmplID = J.EmplID
       AND HB.Empl_Rcd# = J.Empl_Rcd#
       AND HB.Benefit# = 0
       AND HB.Effdt = (SELECT Max(Effdt)
                        FROM PS_Health_Benefit
                       WHERE Emplid = HB.Emplid
                         AND Empl_Rcd# = HB.Empl_Rcd#
                         AND Plan_Type = HB.Plan_Type
                         AND Benefit# = HB.Benefit#
                    AND Coverage_Begin_Dt <= $AsOfDate)
                    AND HB.Coverage_Elect ='E'
       AND (HB.Coverage_End_Dt IS NULL  OR
            HB.Coverage_End_Dt > $AsOfDate)
       AND BP.Plan_Type = HB.Plan_Type
       AND BP.Benefit_Plan = HB.Benefit_Plan
       AND BP.EffDt = (SELECT Max(E.EffDt)
                        FROM PS_Benef_Plan_Tbl E
                       WHERE E.Plan_Type = BP.Plan_Type
                         AND E.Benefit_Plan = BP.Benefit_Plan
                         AND E.EffDt <= $AsOfDate)


        AND BP.Provider = 'CIGMED'

     ORDER BY PD.Emplid, PD.Name, HB.Plan_Type


     !********************************************************
     begin-procedure 500-Get-Dep-Benefits
     !********************************************************
      show '500-get-dep-benefits'

     begin-SELECT

     DB.Emplid                    ! (+1,6)
      ! show 'Emplid: ' &db.emplid
     HD.Plan_Type                  !  (,15)
      ! show 'Plan type: ' &hd.plan_type
     DB.Dependent_Benef       !(,20)
     DB.SSN                   !(,45)
     DB.Name                  !(,25)
     DB.Birthdate             !(,60)
     !to_char(DB.Birthdate, 'YYYYMMDD')        !&Dep_Birthdate
     DB.Sex                   !(,70)
     DB.Relationship          !(,80)

     HD.Emplid

     HD.Dependent_Benef
     HD.Effdt                 !(,90)
     !to_char(HD.Effdt, 'YYYYMMDD')        &Dep_Covrg_Dt (,60)

       !show 'Effdt: ' &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 EffDt <= $AsOfDate)
           AND HD.Empl_Rcd# = 0
           AND HD.Benefit#  = 0
           AND HD.Plan_type = &HB.plan_type
           AND HD.EFfdt <= $AsOfDate
           AND HD.Dependent_Benef = DB.Dependent_Benef
           AND HD.Emplid = DB.Emplid

     ORDER BY HD.effdt desc



     end-SELECT