Example: Validate E-mail Id. We can use other validation (like PAN, SSN validations etc.) by changing the regular expression.
/***************************************************/
Function emailvlidation()
Local JavaObject &email_Regex = CreateJavaObject("java.lang.String", "^([0-9a-zA-Z]+[- ._+&])*[0-9a-zA-Z]+@([-0-9a-zA-Z]+[.])+[a-zA-Z]{2,6}$");
Local JavaObject &Actual_String = CreateJavaObject("java.lang.String", EM_EMAIL.EMAIL_ADDR);
If &Actual_String.matches(&email_Regex) Then
/*Nothing*/
EM_EMAIL.EMAIL_ADDR.Style = "PSEDITBOX";
Else
EM_EMAIL.EMAIL_ADDR.Style = "PSERROR";
Error ("Please provide correct Email address");
End-If;
End-Function;
/***************************************************/
Wednesday, August 4, 2010
Read A Flat File Using File Layout And Insert Into The Specific Component Using CI ( Bulk Insert )
Read A Flat File Using File Layout And Insert Into The Specific Component Using CI:
Approach:
Get the sample flat file and create a File Layout.
Create the CI for the component.
Create an AE and drag the File Layout and CI for dynamic code generation in the peoplecode action
Sample Code:
/*******************************************************************************/
Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;
/* *****************************************************************/
Function StartSession
try
&oSession = %Session;
&oSession.PSMessagesMode = 1;
catch Exception &ex
&LOGFILE.WriteLine(&ex.ToString());
end-try;
End-Function;
/*******************************************************************/
Function ImportData(&RS1 As Rowset)
try
&oEmPDpndtBlInsCi = &oSession.GetCompIntfc(CompIntfc.EM_P_DPNDT_BL_INS_CI);
If &oEmPDpndtBlInsCi = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;
&oEmPDpndtBlInsCi.InteractiveMode = False;
&oEmPDpndtBlInsCi.GetHistoryItems = True;
&oEmPDpndtBlInsCi.EditHistoryItems = False;
&recPerInfo = CreateRecord(Record.EM_PER_INFO);
&recPerInfo1 = CreateRecord(Record.EM_PER_INFO1);
&recBnkInf = CreateRecord(Record.EM_BANK_INFO);
&recdpdntInf = CreateRecord(Record.EM_DPNDNT_INFO);
&LOGFILE.WriteRowset(&RS);
&RS1(1).GetRecord(1).CopyFieldsTo(&recPerInfo);
&oEmPDpndtBlInsCi.EMPLID = &recPerInfo.EMPLID.Value;
rem ***** Execute Get *****;
If Not &oEmPDpndtBlInsCi.Get() Then
rem ***** No rows exist for the specified keys.*****;
errorHandler();
throw CreateException(0, 0, "Get failed");
End-If;
&oEmPerInfo1Collection = &oEmPDpndtBlInsCi.EM_PER_INFO1;
&RS2 = &RS1(1).GetRowset(Scroll.EM_PER_INFO1);
For &i = 1 To &RS2.ActiveRowCount
If &i > 1 Then
&oEmPerInfo1 = &oEmPerInfo1Collection.EM_PER_INFO1.InsertItem(1);
Else
&oEmPerInfo1 = &oEmPerInfo1Collection.Item(1);
End-If;
&RS2.GetRow(&i).GetRecord(Record.EM_PER_INFO1).CopyFieldsTo(&recPerInfo1);
&oEmPerInfo1.EFFDT = &recPerInfo1.EFFDT.Value;
&oEmPerInfo1.SEX = &recPerInfo1.SEX.Value;
&oEmPerInfo1.MAR_STATUS = &recPerInfo1.MAR_STATUS.Value;
End-For;
&RS3 = &RS1(1).GetRowset(Scroll.EM_BANK_INFO);
&oEmBankInfoCollection = &oEmPDpndtBlInsCi.EM_BANK_INFO;
For &j = 1 To &RS3.ActiveRowCount
If &j > 1 Then
&oEmBankInfo = &oEmBankInfoCollection.EM_BANK_INFO.InsertItem(1);
Else
&oEmBankInfo = &oEmBankInfoCollection.Item(&j);
End-If;
&RS3.GetRow(&j).GetRecord(Record.EM_BANK_INFO).CopyFieldsTo(&recBnkInf);
&oEmBankInfo.BANK_NM = &recBnkInf.BANK_NM.Value;
&oEmBankInfo.BANK_ACCOUNT_NUM = &recBnkInf.BANK_ACCOUNT_NUM.Value;
End-For;
rem ***** End: Get/Set Component Interface Properties *****;
rem ***** Execute Save *****;
If Not &oEmPDpndtBlInsCi.Save() Then;
rem errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;
catch Exception &ex
&fileLog.WriteLine(&ex.ToString());
end-try;
End-Function;
rem *****************************************************************;
rem * PeopleCode to Import Data *;
rem *****************************************************************;
&FILE1 = GetFile("C:\Documents and Settings\pmundhr\Desktop\dpndBlkIns.csv", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("C:\Documents and Settings\pmundhr\Desktop\dpndBlkIns.csv.err", "W", %FilePath_Absolute);
StartSession();
&FILE1.SetFileLayout(FileLayout.EM_P_DPNDT_IB);
&LOGFILE.SetFileLayout(FileLayout.EM_P_DPNDT_IB);
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.EM_PER_INFO, CreateRowset(Record.EM_PER_INFO1), CreateRowset(Record.EM_BANK_INFO), CreateRowset(Record.EM_DPNDNT_INFO));
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
ImportData(&RS1);
&RS1 = &FILE1.ReadRowset();
End-While;
&FILE1.Close();
&LOGFILE.Close();
/******************************************************************************/
Sample FileLayout Image:
Approach:
Get the sample flat file and create a File Layout.
Create the CI for the component.
Create an AE and drag the File Layout and CI for dynamic code generation in the peoplecode action
Sample Code:
/*******************************************************************************/
Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;
/* *****************************************************************/
Function StartSession
try
&oSession = %Session;
&oSession.PSMessagesMode = 1;
catch Exception &ex
&LOGFILE.WriteLine(&ex.ToString());
end-try;
End-Function;
/*******************************************************************/
Function ImportData(&RS1 As Rowset)
try
&oEmPDpndtBlInsCi = &oSession.GetCompIntfc(CompIntfc.EM_P_DPNDT_BL_INS_CI);
If &oEmPDpndtBlInsCi = Null Then
errorHandler();
throw CreateException(0, 0, "GetCompIntfc failed");
End-If;
&oEmPDpndtBlInsCi.InteractiveMode = False;
&oEmPDpndtBlInsCi.GetHistoryItems = True;
&oEmPDpndtBlInsCi.EditHistoryItems = False;
&recPerInfo = CreateRecord(Record.EM_PER_INFO);
&recPerInfo1 = CreateRecord(Record.EM_PER_INFO1);
&recBnkInf = CreateRecord(Record.EM_BANK_INFO);
&recdpdntInf = CreateRecord(Record.EM_DPNDNT_INFO);
&LOGFILE.WriteRowset(&RS);
&RS1(1).GetRecord(1).CopyFieldsTo(&recPerInfo);
&oEmPDpndtBlInsCi.EMPLID = &recPerInfo.EMPLID.Value;
rem ***** Execute Get *****;
If Not &oEmPDpndtBlInsCi.Get() Then
rem ***** No rows exist for the specified keys.*****;
errorHandler();
throw CreateException(0, 0, "Get failed");
End-If;
&oEmPerInfo1Collection = &oEmPDpndtBlInsCi.EM_PER_INFO1;
&RS2 = &RS1(1).GetRowset(Scroll.EM_PER_INFO1);
For &i = 1 To &RS2.ActiveRowCount
If &i > 1 Then
&oEmPerInfo1 = &oEmPerInfo1Collection.EM_PER_INFO1.InsertItem(1);
Else
&oEmPerInfo1 = &oEmPerInfo1Collection.Item(1);
End-If;
&RS2.GetRow(&i).GetRecord(Record.EM_PER_INFO1).CopyFieldsTo(&recPerInfo1);
&oEmPerInfo1.EFFDT = &recPerInfo1.EFFDT.Value;
&oEmPerInfo1.SEX = &recPerInfo1.SEX.Value;
&oEmPerInfo1.MAR_STATUS = &recPerInfo1.MAR_STATUS.Value;
End-For;
&RS3 = &RS1(1).GetRowset(Scroll.EM_BANK_INFO);
&oEmBankInfoCollection = &oEmPDpndtBlInsCi.EM_BANK_INFO;
For &j = 1 To &RS3.ActiveRowCount
If &j > 1 Then
&oEmBankInfo = &oEmBankInfoCollection.EM_BANK_INFO.InsertItem(1);
Else
&oEmBankInfo = &oEmBankInfoCollection.Item(&j);
End-If;
&RS3.GetRow(&j).GetRecord(Record.EM_BANK_INFO).CopyFieldsTo(&recBnkInf);
&oEmBankInfo.BANK_NM = &recBnkInf.BANK_NM.Value;
&oEmBankInfo.BANK_ACCOUNT_NUM = &recBnkInf.BANK_ACCOUNT_NUM.Value;
End-For;
rem ***** End: Get/Set Component Interface Properties *****;
rem ***** Execute Save *****;
If Not &oEmPDpndtBlInsCi.Save() Then;
rem errorHandler();
throw CreateException(0, 0, "Save failed");
End-If;
catch Exception &ex
&fileLog.WriteLine(&ex.ToString());
end-try;
End-Function;
rem *****************************************************************;
rem * PeopleCode to Import Data *;
rem *****************************************************************;
&FILE1 = GetFile("C:\Documents and Settings\pmundhr\Desktop\dpndBlkIns.csv", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("C:\Documents and Settings\pmundhr\Desktop\dpndBlkIns.csv.err", "W", %FilePath_Absolute);
StartSession();
&FILE1.SetFileLayout(FileLayout.EM_P_DPNDT_IB);
&LOGFILE.SetFileLayout(FileLayout.EM_P_DPNDT_IB);
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.EM_PER_INFO, CreateRowset(Record.EM_PER_INFO1), CreateRowset(Record.EM_BANK_INFO), CreateRowset(Record.EM_DPNDNT_INFO));
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
ImportData(&RS1);
&RS1 = &FILE1.ReadRowset();
End-While;
&FILE1.Close();
&LOGFILE.Close();
/******************************************************************************/
Sample FileLayout Image:
Tuesday, August 3, 2010
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);
/*************************************************************/
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);
/*************************************************************/
Subscribe to:
Posts (Atom)