Company And Employee Count Based On Plan Type And Benefit Plan

Requirement:

Make a page containing two drop downs, one for Plan Type, and another containing Benefit Plans associated to selected Plan Type. On selecting this Plan Type and Benefit Plan, number of Employees of respective Companies should be shown on the page.

Approach:

We need to fill the grid based on selected Plan Type and Benefit Plan. So based on selected value we need to populate the data into grid.

We selected the required table based on Plan Type and used query for getting the data. These data is populated into the grid.

Main People code Functions used and its use in this requirement:

1. Insertrow : for inserting row into scroll or grid.

2. Deleterow: for deleting row from scroll or grid.

Sample Code used for this requirement:

Local number &counter = 1;
Local number &flag = 1;
&PlanType = PM_BEN_ASGN_VW.PLAN_TYPE.Value;
&BenefitPlan = PM_BEN_ASGN_VW.BENEFIT_PLAN.Value;

/*************************************************************/

&plan = Substring(PM_BEN_ASGN_VW.PLAN_TYPE, 0, 1);
Evaluate &plan
When "1"
&sPlanTableName = "PS_HEALTH_BENEFIT";
Break;
When "2"
 &sPlanTableName = "PS_LIFE_ADD_BEN";
Break;
When "3"
&sPlanTableName = "PS_DISABILITY_BEN";
Break;
When "4"
&sPlanTableName = "PS_SAVINGS_PLAN";
Break;
When "5"
&sPlanTableName = "PS_LEAVE_PLAN";
Break;
When "6"
&sPlanTableName = "PS_FSA_BENEFIT";
Break;
When "7"
&sPlanTableName = "PS_RTRMNT_PLAN";
Break;
When "8"
&sPlanTableName = "PS_PENSION_PLAN";
Break;
When "9"
&sPlanTableName = "PS_VACATION_BEN";
Break;
When "A"
&sPlanTableName = "PS_SIMPLE_BENEFIT";
Break;
End-Evaluate;

Sql1 = CreateSQL("SELECT PLAN_TYPE,BENEFIT_PLAN,COMPANY,COUNT(COMPANY) COUNTEMP FROM (SELECT J.COMPANY, J.EMPLID , A.PLAN_TYPE , A.BENEFIT_PLAN FROM "
&sPlanTableName
" A, PS_JOB J WHERE J.EMPLID = A.EMPLID AND A.PLAN_TYPE = '"
&PlanType
"' AND A.BENEFIT_PLAN = '"
&BenefitPlan
"' AND J.EFFDT =(SELECT MAX(EFFDT) FROM PS_JOB JE WHERE JE.EMPLID = J.EMPLID AND JE.EFFDT <= GETDATE()) AND J.EFFSEQ = (SELECT MAX(EFFSEQ) FROM PS_JOB JS WHERE JS.EMPLID = J.EMPLID AND JS.EFFDT = J.EFFDT) GROUP BY J.COMPANY, J.EMPLID,A.PLAN_TYPE , A.BENEFIT_PLAN) B GROUP BY PLAN_TYPE,BENEFIT_PLAN,COMPANY ");

&rwset2 = GetLevel0()(1).GetRowset(Scroll.PM_BEN_CNT_SUMM);
&row_count1 = &rwset2.ActiveRowCount;
While &Sql1.Fetch(&PLAN_TYPE1, &BENEFIT_PLAN1, &COMPANY1, &COUNTEMP)
&rwset2.GetRow(&row_count1).PM_BEN_CNT_SUMM.PLAN_TYPE.Value = &PLAN_TYPE1;
&rwset2.GetRow(&row_count1).PM_BEN_CNT_SUMM.BENEFIT_PLAN.Value = &BENEFIT_PLAN1;
&rwset2.GetRow(&row_count1).PM_BEN_CNT_SUMM.COMPANY.Value = &COMPANY1;
&rwset2.GetRow(&row_count1).PM_BEN_CNT_SUMM.COUNT_1.Value = &COUNTEMP;

&rwset2.InsertRow(&row_count1);

&row_count1 = &row_count1 + 1;
End-While;

&rwset2.DeleteRow(&row_count1);
/*************************************************************/