Multiple reports in sqr with one real time scenario

Requirement :   print two reports

• The first report will list all employees who currently participate in any health benefit plan along with their plan type and coverage codes. In addition, the report will indicate which employees had their dependent’s coverage terminated.

•  The second report will list only those employees and their dependents who had their benefits coverage terminated within the reporting period.

!**********************************************
.SQR
!A multiple report program
!**********************************************
!Set report column widths
#Define col_emplid 11 !Employee ID
#Define col_empl_name 15 !Employee Name
#Define col_plan_type 5 !Plan Type
#Define col_cov_cd 8 !Coverage Code
#Define col_cov_drop_flag 5 !Dependent Coverage Termination
#Define col_effdt 12 !Effective Date
#Define col_dep_id 6 !Dependent Beneficiary ID
#Define col_dep_name 15 !Dependent Name
#Define col_sep 1 !Column Separator
!***********************
Begin-Setup
!***********************
Declare-Layout EE_Data
Left-Margin=1
End-Declare
Declare-Layout Term_Dependents
Orientation=Landscape
Left-Margin=0.3
End-Declare
Declare-Report EE_Data
Layout=EE_Data
End-Declare
Declare-Report Term_Dependents
Layout=Term_Dependents
End-Declare
End-Setup
!**********************************
Begin-Program
!**********************************
Do Process_Main
End-Program
!**********************************
Begin-Heading 4 For-Reports=(EE_Data)
!**********************************
Print 'List of Employees by Plan Type' (1) Center
Print 'Page' (,+9)
Print #page-count (,+1) edit 999
Print 'EMPLID' (+1,1,{col_emplid})
Print 'Name ' (0,+{col_sep},{col_empl_name})
Print 'Plan ' (0,+{col_sep},{col_plan_type})
Print 'Effdt' (0,+{col_sep},{col_Effdt})
Print 'Coverg' (0,+{col_sep},{col_cov_cd})
Print 'Dep. ' (0,+{col_sep},{col_cov_drop_flag})
Print ' ' (+1,1,{col_emplid})
Print ' ' (0,+{col_sep},{col_empl_name})
Print 'Type ' (0,+{col_sep},{col_plan_type})
Print ' ' (0,+{col_sep},{col_Effdt})
Print 'Code ' (0,+{col_sep},{col_cov_cd})
Print 'Term ' (0,+{col_sep},{col_cov_drop_flag})
Print '-' (+1,1,60) Fill
End-Heading
!********************************************
Begin-Heading 4 For-Reports=(Term_Dependents)
!********************************************
Print 'List of Terminated Dependents' (1) Center
Print 'Page' (,+15)
Print #page-count (,+1) edit 999
Print 'EMPLID' (+2,1,{col_emplid})
Print 'Name ' (0,+{col_sep},{col_empl_name})
Print 'Plan Type' (0,+{col_sep},{col_plan_type})
Print 'Effdt' (0,+{col_sep},{col_Effdt})
Print 'Dep ID ' (0,+{col_sep},{col_dep_id})
Print 'Dep Name' (0,+{col_sep},{col_dep_name})
Print '-' (+1,1,65) Fill
End-Heading
!***************************************
Begin-Procedure Process_Main
!***************************************
!Select and print all Employees
!who are currently covered by any Health Benefits
!and who have Family, or Empl+1 Coverage
Begin-Select
A.Emplid
A.Plan_Type
A.Effdt
A.Covrg_Cd
B.Name
Do Check_Dep_Termination
If $TERM ='N'
Let $TERM = ' '
End-If
Do Print_EE_Data
From Health_Benefit A, Personal_Data B
Where A.Effdt =(Select Max(Effdt) From Health_Benefit
Where Emplid = A.Emplid
And Plan_Type = A.Plan_Type
And Effdt <= Sysdate)
And A.Coverage_Elect Not In ('T','W')
And A.Emplid = B.Emplid
Order By A.Emplid, A.Plan_Type
End-Select
End-Procedure
!***************************************************
Begin-Procedure Check_Dep_Termination
!***************************************************
! For each row retrieved in Step 1, compare between the previous
! maximum effective dated record and the current effective dated
! HEALTH_DEPENDNT row to determine a dependent that is no longer
! covered
! If dropped dependents are found, print the record to a
! separate report
Move 'N' to $TERM
Begin-Select
C.Emplid
C.Plan_Type
C.Effdt
D.Dependent_Benef
Move 'Y' to $TERM
Do Print-Terminated-Dependents
Show 'Dep Term found=' &D.Dependent_Benef ' for Employee '
&A.Emplid
From Health_Benefit C,
Health_Dependnt D
Where C.Emplid = &A.Emplid
And C.Plan_Type = &A.Plan_Type
And C.Emplid = D.Emplid
And C.Plan_Type = D.Plan_Type
And C.Effdt = D.Effdt
And C.Effdt = (Select MAX(E.Effdt)
From Health_Benefit E
Where C.Emplid = E.Emplid
And C.Plan_Type = E.Plan_Type
And E.Effdt < &A.Effdt)
And D.Dependent_Benef Not In
(Select F.Dependent_Benef
From Health_Dependnt F
Where C.Emplid = F.Emplid
And C.Plan_Type = F.Plan_Type
And F.Effdt = &A.EFFDT)
End-Select
End-Procedure
!***********************************
Begin-Procedure Print_EE_Data
!***********************************
Use-Report EE_Data
Let $EmplID=&A.Emplid
Let $Date_Str= Datetostr(&A.Effdt,'mm/dd/yyyy')
Print &A.Emplid (+1,1,{col_emplid}) On-Break Print=Change/Top-Page
Print &B.Name (0,+{col_sep},{col_empl_name}) On-Break
Print=Change/Top-Page
Print &A.Plan_Type(0,+{col_sep},{col_plan_type})
Print $Date_Str (0,+{col_sep},{col_effdt})
Print &A.Covrg_Cd(0,+{col_sep},{col_cov_cd})
Print $Term (0,+{col_sep},{col_cov_drop_flag})
Add 1 to #ee_rcds
End-Procedure
!******************************************
Begin-Procedure Print-Terminated-Dependents
!******************************************
Use-Report Term_Dependents
Let $EmplID=&A.Emplid
Do Get_Dependent_Name
Let $Date_Str= Datetostr(&A.Effdt,'mm/dd/yyyy')
Print &A.Emplid (+1,1,{col_emplid})
On-Break Print=Change/Top-Page
Print &B.Name (0,+{col_sep},{col_empl_name})
On-Break Print=Change/Top-Page
Print &A.Plan_Type(0,+{col_sep},{col_plan_type})
Print $Date_Str (0,+{col_sep},{col_effdt})
Print &D.Dependent_Benef(0,+{col_sep},{col_dep_id})
Print $DEP_NAME (0,+{col_sep},{col_dep_name})
Add 1 to #Dep_Rcds
End-Procedure
!************************************************
Begin-Procedure Get_Dependent_Name
!************************************************
Let $Dep_Name=' '
Begin-Select
Name
Move &Name to $Dep_Name
From Dependent_Benef
Where EMPLID=&A.Emplid
And Dependent_Benef=&D.Dependent_Benef
End-Select
End-Procedure
!************************** End of Report ***************************

SETID BUSSINESSUNIT IN SEARCH PAGE

create record any name insert fields from BU_DEPT_VW ,set u r record as search record will registering component.

Delevered SQR's

 Befits module :
BEN001:  health plan participation 
Navigation :benefits --> reports --->participation ---> health plan participation

BEN003: quarterly benefit contribution register
Navigation  :benefits --> reports --->contribution and deduction-->benefit contribution register

BEN 002 : life insurance participants 
Navigation :benefits --> reports --->participation ---> life insurance participation

 

BEN 733 :BASE BENEFITS AUDIT REPORT
Navigation :benefits --> reports --->Audits-->Base Benefits consistency audit
the report contain following details 

Employees Less Than 16 Years Old
People With Unusual Dependents Signed Up For Coverage
Employee and Spouse (or Other Dependent) Both Electing Health Benefits
Employees With Over-Age Dependent Coverage
Employees (Married) who do not have Spouses on file
Employees (Non-Married) who have Spouses on file
Employees whose Spouse/ExSpouse has wrong Marital Status
Employees whose Spouse has wrong gender