array

Usage of Selectbykey property -

&Rec2 = CreateRecord(Record.FDC_TLO_HIR_STG);
&Rec1 = &Rs1(1).GetRecord(Record.FDC_TLO_HIR_STG);

SQLExec("%SelectByKey(:1)", &Rec1, &Rec2);
                     rem &Rec1 = &Rec2;
                     &Rec2.CopyFieldsTo(&Rec1);
                     If &LoadSuccess = True Then
                        &Rec1.FLAG1.Value = "Y";
                        &Rec1.Update();
                     End-If;

Strip Special Character -
method StripSpecialChar
   /+ &String1 as String +/
   /+ Returns String +/
   Local number &SpclCharLen, &i;
   Local array of string &SpclChars;

   &SpclCharLen = 6;
   &SpclChars = CreateArrayRept(" ", &SpclCharLen);

   &SpclChars [1] = " ";
   &SpclChars [2] = "-";
   &SpclChars [3] = ".";
   &SpclChars [4] = "/";
   &SpclChars [5] = "(";
   &SpclChars [6] = ")";

   For &i = 1 To &SpclCharLen
      &String1 = Substitute(&String1, &SpclChars [&i], "");
   End-For;
   Return &String1;
end-method;
Array concept to split address and load into 4 fields -

Local array &AddrArray;
&AddrArray = %This.SplitAddress(&NewHireRec);
            &oCollAddresses.PROP_ADDRESS1 = LTrim(RTrim(&AddrArray [1], " "), " ");
            &oCollAddresses.PROP_ADDRESS2 = LTrim(RTrim(&AddrArray [2], " "), " ");
            &oCollAddresses.PROP_ADDRESS3 = LTrim(RTrim(&AddrArray [3], " "), " ");
            &oCollAddresses.PROP_ADDRESS4 = LTrim(RTrim(&AddrArray [4], " "), " ");

method SplitAddress
   /+ &NewHireRec as Record +/
   /+ Returns Array of Any +/
   Local array &AddrArray;
   Local number &i, &j;
   Local array &TempStringArray;
   Local string &Temp1;
   Local FDC_TLO_LIB2:FDC_TLO_NEWHIRE:FDC_TLO_NEWHIRE &TLO_NewHire = create FDC_TLO_LIB2:FDC_TLO_NEWHIRE:FDC_TLO_NEWHIRE();

   &AddrArray = CreateArray();
   For &i = 1 To 4
      &AddrArray.Push("");
   End-For;

   If Len(&NewHireRec.ADDRESS100.Value) <= 55 And
         Len(&NewHireRec.ADDRESS100_OTHER.Value) <= 55 Then
      &AddrArray [1] = &NewHireRec.ADDRESS100.Value;
      &AddrArray [2] = &NewHireRec.ADDRESS100_OTHER.Value;
      &AddrArray [3] = "";
      &AddrArray [4] = "";
      Return &AddrArray;
   End-If;



   If Len(&NewHireRec.ADDRESS100.Value) <= 55 And
         Len(&NewHireRec.ADDRESS100_OTHER.Value) > 55 Then
      &AddrArray [1] = &NewHireRec.ADDRESS100.Value;
      /* &NewHireRec.ADDRESS100_OTHER.Value is split and assigned to 2 & 3 */
      &TempStringArray = &TLO_NewHire.ParseString(&NewHireRec.ADDRESS100_OTHER.Value, " ");
   
      &AddrArray [2] = "";
      For &i = 1 To &TempStringArray.Len
         &Temp1 = &AddrArray [2] | " " | &TempStringArray [&i];
         If Len(&Temp1) <= 55 Then
            &AddrArray [2] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
   
      &AddrArray [3] = "";
      For &j = &i To &TempStringArray.Len
         &Temp1 = &AddrArray [3] | " " | &TempStringArray [&j];
         If Len(&Temp1) <= 55 Then
            &AddrArray [3] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
   
      &AddrArray [4] = "";
   
      /* 07/28/2016 Syed(F72MURX) FDC_2003 Begin - Split based on length */
      If &TempStringArray.Len = 1 Then
         &TempStringArray = %This.SplitString(&NewHireRec, Field.ADDRESS100_OTHER, 55);
         If &TempStringArray.Len > 1 Then
            For &i = 1 To &TempStringArray.Len
               If &i = 1 Then
                  &AddrArray [2] = &TempStringArray [1];
               End-If;
               If &i = 1 Then
                  &AddrArray [3] = &TempStringArray [2];
               End-If;
            End-For;
         End-If;
      End-If;
      /* 07/28/2016 Syed(F72MURX) FDC_2003 End */
   
      Return &AddrArray;
   End-If;


   If Len(&NewHireRec.ADDRESS100.Value) > 55 And
         Len(&NewHireRec.ADDRESS100_OTHER.Value) <= 55 Then
   
      /* &NewHireRec.ADDRESS100.Value is split and assigned to 1 & 2 */
      &TempStringArray = &TLO_NewHire.ParseString(&NewHireRec.ADDRESS100.Value, " ");
   
      &AddrArray [1] = "";
      For &i = 1 To &TempStringArray.Len
         &Temp1 = &AddrArray [1] | " " | &TempStringArray [&i];
         If Len(&Temp1) <= 55 Then
            &AddrArray [1] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
   
      &AddrArray [2] = "";
      For &j = &i To &TempStringArray.Len
         &Temp1 = &AddrArray [2] | " " | &TempStringArray [&j];
         If Len(&Temp1) <= 55 Then
            &AddrArray [2] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
      &AddrArray [3] = &NewHireRec.ADDRESS100_OTHER.Value;
      &AddrArray [4] = "";
   
      /* 07/28/2016 Syed(F72MURX) FDC_2003 Begin - Split based on length */
      If &TempStringArray.Len = 1 Then
         &TempStringArray = %This.SplitString(&NewHireRec, Field.ADDRESS100, 55);
         If &TempStringArray.Len > 1 Then
            For &i = 1 To &TempStringArray.Len
               If &i = 1 Then
                  &AddrArray [1] = &TempStringArray [1];
               End-If;
               If &i = 1 Then
                  &AddrArray [2] = &TempStringArray [2];
               End-If;
            End-For;
         End-If;
      End-If;
      /* 07/28/2016 Syed(F72MURX) FDC_2003 End */
   
      Return &AddrArray;
   End-If;


   If Len(&NewHireRec.ADDRESS100.Value) > 55 And
         Len(&NewHireRec.ADDRESS100_OTHER.Value) > 55 Then
      /* &NewHireRec.ADDRESS100.Value is split and assigned to 1 & 2 */
      &TempStringArray = &TLO_NewHire.ParseString(&NewHireRec.ADDRESS100.Value, " ");
      &AddrArray [1] = "";
      For &i = 1 To &TempStringArray.Len
         &Temp1 = &AddrArray [1] | " " | &TempStringArray [&i];
         If Len(&Temp1) <= 55 Then
            &AddrArray [1] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
      &AddrArray [2] = "";
      For &j = &i To &TempStringArray.Len
         &Temp1 = &AddrArray [2] | " " | &TempStringArray [&j];
         If Len(&Temp1) <= 55 Then
            &AddrArray [2] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
   
      /* 07/28/2016 Syed(F72MURX) FDC_2003 Begin - Split based on length */
      If &TempStringArray.Len = 1 Then
         &TempStringArray = %This.SplitString(&NewHireRec, Field.ADDRESS100, 55);
         If &TempStringArray.Len > 1 Then
            For &i = 1 To &TempStringArray.Len
               If &i = 1 Then
                  &AddrArray [1] = &TempStringArray [1];
               End-If;
               If &i = 1 Then
                  &AddrArray [2] = &TempStringArray [2];
               End-If;
            End-For;
         End-If;
      End-If;
      /* 07/28/2016 Syed(F72MURX) FDC_2003 End */
   
      /* &NewHireRec.ADDRESS100_OTHER.Value is split and assigned to 3 & 4 */
      &TempStringArray = &TLO_NewHire.ParseString(&NewHireRec.ADDRESS100_OTHER.Value, " ");
      &AddrArray [3] = "";
      For &i = 1 To &TempStringArray.Len
         &Temp1 = &AddrArray [3] | " " | &TempStringArray [&i];
         If Len(&Temp1) <= 55 Then
            &AddrArray [3] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
      &AddrArray [4] = "";
      For &j = &i To &TempStringArray.Len
         &Temp1 = &AddrArray [4] | " " | &TempStringArray [&j];
         If Len(&Temp1) <= 55 Then
            &AddrArray [4] = &Temp1;
         Else
            Break;
         End-If;
      End-For;
   
      /* 07/28/2016 Syed(F72MURX) FDC_2003 Begin - Split based on length */
      If &TempStringArray.Len = 1 Then
         &TempStringArray = %This.SplitString(&NewHireRec, Field.ADDRESS100_OTHER, 55);
         If &TempStringArray.Len > 1 Then
            For &i = 1 To &TempStringArray.Len
               If &i = 1 Then
                  &AddrArray [3] = &TempStringArray [1];
               End-If;
               If &i = 1 Then
                  &AddrArray [4] = &TempStringArray [2];
               End-If;
            End-For;
         End-If;
      End-If;
      /* 07/28/2016 Syed(F72MURX) FDC_2003 End */
   
      Return &AddrArray;
   End-If;

end-method;


method SplitString
   /+ &StringRec as Record, +/
   /+ &FieldName as String, +/
   /+ &StringLen as Number +/
   /+ Returns Array of Any +/
   Local array &StringArray1;
   Local string &TempString, &TempString2;
   Local number &i, &index1, &index2;

   &StringArray1 = CreateArray();
   &TempString = &StringRec.GetField((@("Field." | &FieldName))).Value;
   If Len(&TempString) <= &StringLen Then
      &StringArray1.Push(&TempString);
   Else
      &i = 1;
      While (&i > 0)
         If Len(&TempString) <= &StringLen Then
            &StringArray1.Push(&TempString);
            &i = - 1;
         Else
            &TempString2 = Substring(&TempString, 1, &StringLen);
            &StringArray1.Push(&TempString2);
            &TempString = Substring(&TempString, Len(&TempString2) + 1, Len(&TempString) - Len(&TempString2));
            &i = 1;
         End-If;
      End-While;
   End-If;
   Return &StringArray1;
end-method;

method ParseString
   /+ &String1 as String, +/
   /+ &ParseBy as String +/
   /+ Returns Array of Any +/
   Local array &String2;
   Local string &TempString, &MoreStringsFound;
   Local number &i, &index1, &index2, &Len;
   &String2 = CreateArray();
   /* Get the count of separator in string */
   &index1 = 1;
   &index2 = Find(&ParseBy, &String1, &index1);
   While &index2 <> 0
      &MoreStringsFound = "Y";
      /* Find if another separator is found */
      &TempString = Substring(&String1, &index1, &index2 - &index1);
      If All(&TempString) Then
         &String2.Push(&TempString);
      End-If;
      &index1 = &index2 + 1;
      &index2 = Find(&ParseBy, &String1, &index1);
   End-While;
   /* When only one string is found */
   If &MoreStringsFound <> "Y" Then
      &String2.Push(&String1);
   Else
      &TempString = Substring(&String1, &index1, Len(&String1));
      If All(&TempString) Then
         &String2.Push(&TempString);
      End-If;
   End-If;
   Return &String2;
end-method;

ci

FDC_ABS_EVNT app engine -

/***********************************************************************************
Modification Log
Programmer        Date        Project Code   Description
----------------  ----------  ------------   -----------   11/04/2012  FDC_526        Program to load the Absence Events CI
                                             through Peoplecode, by reading a input file
                                             from a FTP path.
*************************************************************************************/
Function Format_date(&dt_temp As string) Returns date
   Local date &ret_dt;
   &dt_temp1 = &dt_temp;
   &dt_check_month = Substring(&dt_temp1, 1, Find("/", &dt_temp1) - 1);
   &dt_check_month = Rept("0", 2 - Len(&dt_check_month)) | &dt_check_month;
   &dt_temp1 = Substring(&dt_temp1, Find("/", &dt_temp1) + 1, Len(&dt_temp1));
   &dt_check_date = Substring(&dt_temp1, 1, Find("/", &dt_temp1) - 1);
   &dt_check_date = Rept("0", 2 - Len(&dt_check_date)) | &dt_check_date;
   &dt_temp1 = Substring(&dt_temp1, Find("/", &dt_temp1) + 1, Len(&dt_temp1));
   &dt_check_year = &dt_temp1;
   &dt_check_year = Rept("0", 4 - Len(&dt_check_year)) | &dt_check_year;
   &dt_check = &dt_check_year | "-" | &dt_check_month | "-" | &dt_check_date;

   If All(&dt_temp) Then
      If IsDate(&dt_check) Then
         SQLExec("select %dateout(to_date(:1,'mm/dd/yyyy')) from dual", &dt_temp, &ret_dt);
      Else
         &PROCESS_FLAG = "E";
         &COMMENTS_VALUE = "Invalid Date found for Employee ";
         &ERROR_MESSAGE = &ERROR_MESSAGE | "Invalid Date found for Employee " | &dt_temp;
     
      End-If;
   Else
      SQLExec("Select null from dual", &ret_dt);
   End-If;
   Return &ret_dt;
End-Function;

Function EditRecord(&REC As Record) Returns boolean;
   Local integer &E;
   REM   &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_TranslateTable + %Edit_PromptTable + %Edit_OneZero);
   &REC.ExecuteEdits(%Edit_Required + %Edit_DateRange + %Edit_YesNo + %Edit_OneZero);
   If &REC.IsEditError Then
      &LOGFILE.WriteLine("*******************************************************************************************");
      &LOGFILE.Writeline(&ERROR_DETAILS | &enter | &ERROR_MESSAGE);
      &Error_Log.writeline(&Emp_String);
      For &E = 1 To &REC.FieldCount
         &MYFIELD = &REC.GetField(&E);
         If &MYFIELD.EditError Then
            &MSGNUM = &MYFIELD.MessageNumber;
            &MSGSET = &MYFIELD.MessageSetNumber;
            &LOGFILE.WriteLine("****Record:" | &REC.Name | ", Field:" | &MYFIELD.Name);
            &LOGFILE.WriteLine("****" | MsgGet(&MSGSET, &MSGNUM, ""));
         End-If;
      End-For;
      Return False;
   Else
      Return True;
   End-If;
End-Function;

Function ImportSegment(&RS2 As Rowset, &RSParent As Rowset)
   Local Rowset &RS1, &RSP;
   Local string &RecordName;
   Local Record &REC2, &RECP;
   Local SQL &SQL1;
   Local integer &I, &L;
   &enter = Char(13);
   &SQL1 = CreateSQL("%Insert(:1)");
   &RecordName = "RECORD." | &RS2.DBRecordName;
   &REC2 = CreateRecord(@(&RecordName));
   &REC2.PROCESS_INSTANCE.Value = FDC_ABSENCE_AET.PROCESS_INSTANCE.Value; /*added process instance */
   &REC2.PROCESS_FLAG.Value = "N";
   &REC2.COMMENTS.Value = " ";
   &RECP = &RSParent(1).GetRecord(@(&RecordName));
   For &I = 1 To &RS2.ActiveRowCount
      &PROCESS_FLAG = "N";
      &COMMENTS_VALUE = " ";
      &bgn_dt = &RS2(&I).GetRecord(1).BGN_DT.Value;
      &end_dt = &RS2(&I).GetRecord(1).END_DT.Value;
      &orig_begin_dt = &RS2(&I).GetRecord(1).ORIG_BEGIN_DT.Value;
      &evt_config_dt = &RS2(&I).GetRecord(1).EVT_CONFIG1_DT.Value;
   
      &RS2(&I).GetRecord(1).BGN_DT.Value = Format_date(&RS2(&I).GetRecord(1).BGN_DT.Value);
      &RS2(&I).GetRecord(1).END_DT.Value = Format_date(&RS2(&I).GetRecord(1).END_DT.Value);
      &RS2(&I).GetRecord(1).ORIG_BEGIN_DT.Value = Format_date(&RS2(&I).GetRecord(1).ORIG_BEGIN_DT.Value);
      &RS2(&I).GetRecord(1).EVT_CONFIG1_DT.Value = Format_date(&RS2(&I).GetRecord(1).EVT_CONFIG1_DT.Value);
      &RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
      &ERROR_DETAILS = "Employee Id:" | &REC2.EMPLID.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Employee Record:" | &REC2.EMPL_RCD.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Absence Take:" | &REC2.PIN_NM.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Absence Reason:" | &REC2.ABSENCE_REASON.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Manager Approved:" | &REC2.MANAGER_APPR_IND.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ",  Begin Date:" | &bgn_dt;
      &ERROR_DETAILS = &ERROR_DETAILS | ", End Date:" | &end_dt;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Original Begin Date:" | &orig_begin_dt;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Partial Days :" | &REC2.PARTIAL_DAYS.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Start Day Hours :" | &REC2.BEGIN_DAY_HRS.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", End Day Hours :" | &REC2.END_DAY_HRS.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", All Day Hours:" | &REC2.ALL_DAY_HRS.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Start Day is Half Day:" | &REC2.BEGIN_DAY_HALF_IND.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", End Day is Half Day::" | &REC2.END_DAY_HALF_IND.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", All Days Are Half Days:" | &REC2.ALL_DAYS_IND.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Date 1 :" | &evt_config_dt;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Character 1 :" | &REC2.EVT_CONFIG1.Value;
      &ERROR_DETAILS = &ERROR_DETAILS | ", Decimal 1 :" | &REC2.EVT_CONFIG1_DEC.Value;
   
      &Emp_String = &REC2.EMPLID.Value | "," | &REC2.EMPL_RCD.Value | "," | &REC2.PIN_NM.Value | "," | &REC2.ABSENCE_REASON.Value | "," | &REC2.MANAGER_APPR_IND.Value | "," | &bgn_dt | "," | &end_dt | "," | &orig_begin_dt | "," | &REC2.PARTIAL_DAYS.Value | "," | &REC2.BEGIN_DAY_HRS.Value | "," | &REC2.END_DAY_HRS.Value | "," | &REC2.ALL_DAY_HRS.Value | "," | &REC2.BEGIN_DAY_HALF_IND.Value | "," | &REC2.END_DAY_HALF_IND.Value | "," | &REC2.ALL_DAYS_IND.Value | "," | &evt_config_dt | "," | &REC2.EVT_CONFIG1.Value | "," | &REC2.EVT_CONFIG1_DEC.Value;
      If (EditRecord(&REC2)) Then
         If &PROCESS_FLAG = "E" Then
            &REC2.PROCESS_FLAG.Value = &PROCESS_FLAG;
            &REC2.COMMENTS.Value = &COMMENTS_VALUE;
            &LOGFILE.WriteLine("*******************************************************************************************");
            &LOGFILE.Writeline(&ERROR_DETAILS | &enter | &ERROR_MESSAGE);
            &LOGFILE.WriteLine("*******************************************************************************************");
            &Error_Log.writeline(&Emp_String);
         End-If;
         &SQL1.Execute(&REC2);
      Else
         &LOGFILE.WriteLine("****Correct error in this record and delete all error messages");
         &LOGFILE.WriteLine("*******************************************************************************************");
      End-If;
   End-For;
End-Function;

rem *****************************************************************;
rem * PeopleCode to Import Data                                     *;
rem *****************************************************************;
Local File &FILE1;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;
Local string &file_name, &err_file_name, &run_cntl_id;
Local number &prcs_instance;
Component string &string1;

&date_time = %Datetime;
&file_path = GetURL(URL.FDC_GP_AM);
&filename = &file_path | FDC_ABSENCE_AET.FILE_NAME.Value;

&file_no_ext = Substring(&filename, 1, (Find(".", &filename) - 1));
&err_file = &file_no_ext | "_LOG.LOG";
&ERROR_FILE1 = &file_no_ext | "_ERR.CSV";
&FILE1 = GetFile(&filename, "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile(&err_file, "W", %FilePath_Absolute);
&Error_Log = GetFile(&ERROR_FILE1, "w", "a", %FilePath_Absolute);

If &FILE1.ReadLine(&string1) Then
   &Error_Log.writeline(&string1);
End-If;

&LOGFILE.WriteLine("*******************************************************************************************");
&LOGFILE.WriteLine("Absence Event Load Process.");
&LOGFILE.WriteLine("Process Instance - " | FDC_ABSENCE_AET.PROCESS_INSTANCE.Value);
&LOGFILE.WriteLine("Process Execution Time - " | &date_time);
&LOGFILE.WriteLine("Input File Name - " | FDC_ABSENCE_AET.FILE_NAME.Value);
&LOGFILE.WriteLine("*******************************************************************************************");
&LOGFILE.WriteLine("");

&FILE1.SetFileLayout(FileLayout.FDC_ABS_FL);
&LOGFILE.SetFileLayout(FileLayout.FDC_ABS_FL);
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.FDC_ABEVT_TAO);
&SQL1 = CreateSQL("%Insert(:1)");
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
   ImportSegment(&RS1, &RS);
   &RS1 = &FILE1.ReadRowset();
End-While;
SQLExec("Commit");
&FILE1.Close();
&LOGFILE.Close();


Step 2 - CI

/************************************************************************************
Modification Log
Programmer        Date        Project Code   Description
----------------  ----------  ------------   -----------
   11/04/2012  FDC_526        To Load the Absence Events for Employee
************************************************************************************/

Local File &fileLog;
Local ApiObject &oSession, &oFdcGpAbsenceEvent;
Local ApiObject &oGpAbsEventCollection, &oGpAbsEvent;
Local ApiObject &oGpAbsevtFcsVCollection, &oGpAbsevtFcsV;
Local ApiObject &oGpxpyeMsgVwCollection, &oGpxpyeMsgVw;
Local SQL &sql1;
Local string &Sql_data;
Local string &emplid, &pin_nm, &absence_reason, &evt_config1;
Local number &empl_rcd, &pin_num, &begin_day_hrs, &end_day_hrs, &all_day_hrs, &evt_config1_dec;
Local date &bgn_dt, &end_dt, &orig_begin_dt, &evt_config1_dt;
Local string &manager_appr_ind, &partial_days, &begin_day_half_ind, &end_day_half_ind, &all_days_ind;
Local Record &record1;
Local array of Record &array1;
Component string &string1;


Function errorHandler()
   Local ApiObject &oPSMessageCollection, &oPSMessage;
   Local number &i;
   Local string &sErrMsgSetNum, &sErrMsgNum, &sErrMsgText, &sErrType;

   &oPSMessageCollection = &oSession.PSMessages;
   For &i = 1 To &oPSMessageCollection.Count
      &oPSMessage = &oPSMessageCollection.Item(&i);
      &sErrMsgSetNum = &oPSMessage.MessageSetNumber;
      &sErrMsgNum = &oPSMessage.MessageNumber;
      &sErrMsgText = &oPSMessage.Text;
      &fileLog.WriteLine(&sErrType | " (" | &sErrMsgSetNum | "," | &sErrMsgNum | ") - " | &sErrMsgText);
   End-For;
   &ERROR_MSG = &sErrMsgText; /*added*/
   rem ***** Delete the Messages from the collection *****;
   &oPSMessageCollection.DeleteAll();
End-Function;

try

   &record1 = CreateRecord(Record.FDC_ABEVT_TAO);
   &array1 = CreateArrayRept(&record1, 0);

   &file_path = GetURL(URL.FDC_GP_AM);
   &FILE_NO_EXT = &file_path | Substring(FDC_ABSENCE_AET.FILE_NAME, 1, (Find(".", FDC_ABSENCE_AET.FILE_NAME) - 1));
   &ERROR_FILE = &FILE_NO_EXT | "_ERR.CSV";
   &LOG_FILE = &FILE_NO_EXT | "_LOG.LOG";
   &Error_Log = GetFile(&ERROR_FILE, "a", "a", %FilePath_Absolute);
   &fileLog = GetFile(&LOG_FILE, "a", "a", %FilePath_Absolute);


   rem ***** Get current PeopleSoft Session *****;
   &oSession = %Session;

   rem ***** Set the PeopleSoft Session Error Message Mode *****;
   rem ***** 0 - None *****;
   rem ***** 1 - PSMessage Collection only (default) *****;
   rem ***** 2 - Message Box only *****;
   rem ***** 3 - Both collection and message box *****;
   &oSession.PSMessagesMode = 1;

   rem ***** Get the Component Interface *****;
   &oFdcGpAbsenceEvent = &oSession.GetCompIntfc(CompIntfc.FDC_GP_ABSENCE_EVENT);
   If &oFdcGpAbsenceEvent = Null Then
      errorHandler();
      throw CreateException(0, 0, "GetCompIntfc failed");
   End-If;

   rem ***** Set the Component Interface Mode *****;
   &oFdcGpAbsenceEvent.InteractiveMode = False;
   &oFdcGpAbsenceEvent.GetHistoryItems = True;
   &oFdcGpAbsenceEvent.EditHistoryItems = True;

   &sql = CreateSQL("select process_instance, emplid, empl_rcd, pin_nm, absence_reason, manager_appr_ind, bgn_dt, end_dt, orig_begin_dt, partial_days, begin_day_hrs, end_day_hrs, all_day_hrs, begin_day_half_ind, end_day_half_ind, all_days_ind, evt_config1_dt, evt_config1, evt_config1_dec,process_flag,comments from %table(FDC_ABEVT_TAO) where process_instance = :1 and process_flag='N' order by emplid", FDC_ABSENCE_AET.PROCESS_INSTANCE.Value, &record1);
   While &sql.fetch(&record1)
      &array1.Push(&record1);
      &record1 = CreateRecord(Record.FDC_ABEVT_TAO);
   End-While;
   &count = 0;
   &enter = Char(13);

   For &count = 1 To &array1.Len
      &record1 = &array1.Shift();
      &emplid = &record1.EMPLID.Value;
      &empl_rcd = &record1.EMPL_RCD.Value;
      &pin_nm = &record1.PIN_NM.Value;
      &absence_reason = &record1.ABSENCE_REASON.Value;
      &manager_appr_ind = &record1.MANAGER_APPR_IND.Value;
      &bgn_dt = &record1.BGN_DT.Value;
      &end_dt = &record1.END_DT.Value;
      &orig_begin_dt = &record1.ORIG_BEGIN_DT.Value;
      &partial_days = &record1.PARTIAL_DAYS.Value;
      &begin_day_hrs = &record1.BEGIN_DAY_HRS.Value;
      &end_day_hrs = &record1.END_DAY_HRS.Value;
      &all_day_hrs = &record1.ALL_DAY_HRS.Value;
      &begin_day_half_ind = &record1.BEGIN_DAY_HALF_IND.Value;
      &end_day_half_ind = &record1.END_DAY_HALF_IND.Value;
      &all_days_ind = &record1.ALL_DAYS_IND.Value;
      &evt_config1_dt = &record1.EVT_CONFIG1_DT.Value;
      &evt_config1 = &record1.EVT_CONFIG1.Value;
      &evt_config1_dec = &record1.EVT_CONFIG1_DEC.Value;
      &ERROR_MSG = " ";
   
      If None(&bgn_dt) Then
         &from_dt_condition = " and BGN_DT is null";
      Else
         &from_dt_condition = " and TO_CHAR(BGN_DT,'YYYY-MM-DD') = '" | &bgn_dt | "'";
      End-If;
      If None(&end_dt) Then
         &thru_dt_condition = " and END_DT is null";
      Else
         &thru_dt_condition = " and TO_CHAR(END_DT,'YYYY-MM-DD') = '" | &end_dt | "'";
      End-If;
      try
         rem ***** Set Component Interface Get/Create Keys *****;
         &oFdcGpAbsenceEvent.EMPLID = &emplid;
         &oFdcGpAbsenceEvent.EMPL_RCD = &empl_rcd;
     
         rem ***** Execute Get *****;
         If Not &oFdcGpAbsenceEvent.Get() Then
            rem ***** No rows exist for the specified keys.*****;
            errorHandler();
            throw CreateException(0, 0, "Get failed");
         End-If;
         /*To Fetch the Pin Num*/
         SQLExec("select pin_num from ps_gp_pin where pin_nm = :1", &pin_nm, &pin_num);
     
         SQLExec("select to_char(%DATEIN(:1),'mm/dd/yyyy'),to_char(%DATEIN(:2),'mm/dd/yyyy'),to_char(%DATEIN(:3),'mm/dd/yyyy'),to_char(%DATEIN(:4),'mm/dd/yyyy') from dual", &bgn_dt, &end_dt, &orig_begin_dt, &evt_config1_dt, &bgn_dt_orig, &end_dt_orig, &orig_begin_dt_orig, &evt_config1_dt_orig);
         &error_message = "Employee Id:" | &emplid;
         &error_message = &error_message | ", Absence Take:" | &pin_nm;
         &error_message = &error_message | ", Begin Date:" | &bgn_dt_orig;
         &error_message = &error_message | ", End Date:" | &end_dt_orig;
     
         rem ***** Begin: Get/Set Component Interface Properties *****;
     
         rem ***** Set/Get GP_ABS_EVENT Collection Field Properties -- Parent: PS_ROOT Collection *****;
         &oGpAbsEventCollection = &oFdcGpAbsenceEvent.GP_ABS_EVENT;
         &oGpAbsEvent = &oGpAbsEventCollection.insertItem(1);
         &oGpAbsEvent.BGN_DT = &bgn_dt;
         &oGpAbsEvent.PIN_TAKE_NUM = &pin_num;
         &oGpAbsEvent.END_DT = &end_dt;
         &oGpAbsEvent.ABSENCE_REASON = &absence_reason;
         &oGpAbsEvent.EVT_CONFIG1 = &evt_config1;
         &oGpAbsEvent.EVT_CONFIG1_DT = &evt_config1_dt;
         &oGpAbsEvent.EVT_CONFIG1_DEC = &evt_config1_dec;
         &oGpAbsEvent.MANAGER_APPR_IND = &manager_appr_ind;
         &oGpAbsEvent.PARTIAL_DAYS = &partial_days;
         &oGpAbsEvent.ALL_DAYS_IND_0 = &all_days_ind;
         &oGpAbsEvent.ALL_DAYS_IND = &all_days_ind;
         &oGpAbsEvent.ALL_DAY_HRS = &all_day_hrs;
         &oGpAbsEvent.BEGIN_DAY_HALF_IND = &begin_day_half_ind;
         &oGpAbsEvent.BEGIN_DAY_HALF_IND_0 = &begin_day_half_ind;
         &oGpAbsEvent.BEGIN_DAY_HRS = &begin_day_hrs;
         &oGpAbsEvent.BEGIN_DAY_HRS_0 = &begin_day_hrs;
     
         /*Added for All Day Hours*/
         If &all_day_hrs > 0 Then
            &oGpAbsEvent.BEGIN_DAY_HRS = &all_day_hrs;
            &oGpAbsEvent.BEGIN_DAY_HRS_0 = &all_day_hrs;
            &oGpAbsEvent.ALL_DAYS_IND_0 = "Y";
            &oGpAbsEvent.ALL_DAYS_IND = "Y";
         End-If;
         /*Added for All Days Are Half Days */
         If &all_days_ind = "Y" Then
            &oGpAbsEvent.BEGIN_DAY_HALF_IND_0 = "Y";
         End-If;
     
     
         &oGpAbsEvent.END_DAY_HALF_IND = &end_day_half_ind;
         &oGpAbsEvent.END_DAY_HALF_IND_0 = &end_day_half_ind;
     
         &oGpAbsEvent.END_DAY_HRS = &end_day_hrs;
         &oGpAbsEvent.END_DAY_HRS_0 = &end_day_hrs;
     
     
         rem ***** End: Get/Set Component Interface Properties *****;
     
         rem ***** Execute Save *****;
         If Not &oFdcGpAbsenceEvent.Save() Then;
            &fileLog.WriteLine("*******************************************************************************************");
            &error_message = &error_message | " - Save Failed";
            &fileLog.WriteLine(&error_message);
            errorHandler();
            &ERROR_MSG = &ERROR_MSG | "Save Failed for Employee - " | &emplid | &enter | "Pin Name -" | &pin_nm | &enter | "Pin Number" | &pin_num;
            SQLExec("UPDATE %table(FDC_ABEVT_TAO) SET PROCESS_FLAG = 'E',COMMENTS = :5 WHERE PROCESS_INSTANCE = :1 AND PROCESS_FLAG = 'N' AND EMPLID=:2 AND EMPL_RCD=:3 AND PIN_NM = :4 " | &from_dt_condition | &thru_dt_condition, FDC_ABSENCE_AET.PROCESS_INSTANCE, &emplid, &empl_rcd, &pin_nm, &ERROR_MSG);
            &Emp_String = &emplid | "," | &empl_rcd | "," | &pin_nm | "," | &absence_reason | "," | &manager_appr_ind | "," | &bgn_dt_orig | "," | &end_dt_orig | "," | &orig_begin_dt_orig | "," | &partial_days | "," | &begin_day_hrs | "," | &end_day_hrs | "," | &all_day_hrs | "," | &begin_day_half_ind | "," | &end_day_half_ind | "," | &all_days_ind | "," | &evt_config1_dt_orig | "," | &evt_config1 | "," | &evt_config1_dec;
            &Error_Log.WriteLine(&Emp_String);
            throw CreateException(0, 0, "Save failed");
         Else
            &ERROR_MSG = "Saved Successfully for Employee - " | &emplid | &enter | "Pin Name -" | &pin_nm | &enter | "Pin Number-" | &pin_num;
            SQLExec("UPDATE %table(FDC_ABEVT_TAO) SET PROCESS_FLAG = 'Y',COMMENTS = :5 WHERE PROCESS_INSTANCE = :1 AND PROCESS_FLAG = 'N' AND EMPLID=:2 AND EMPL_RCD=:3 AND PIN_NM = :4 " | &from_dt_condition | &thru_dt_condition, FDC_ABSENCE_AET.PROCESS_INSTANCE, &emplid, &empl_rcd, &pin_nm, &ERROR_MSG);
            &error_message = &error_message | " - Saved Successfully";
            &fileLog.WriteLine(&error_message);
         End-If;
     
         rem ***** Execute Cancel *****;
         If Not &oFdcGpAbsenceEvent.Cancel() Then;
            errorHandler();
            throw CreateException(0, 0, "Cancel failed");
         End-If;
     
      catch Exception &ex1
         &fileLog.WriteLine(&ex1.ToString());
         &fileLog.WriteLine("*******************************************************************************************");
         If Not &oFdcGpAbsenceEvent.Cancel() Then;
            errorHandler();
         End-If;
     
      end-try;
      SQLExec("commit");
   
   End-For;
   SQLExec("SELECT COUNT(*) FROM  %TABLE(FDC_ABEVT_TAO) WHERE PROCESS_INSTANCE = :1", FDC_ABSENCE_AET.PROCESS_INSTANCE, &total_count);
   SQLExec("SELECT COUNT(*) FROM  %TABLE(FDC_ABEVT_TAO) WHERE PROCESS_INSTANCE = :1 AND PROCESS_FLAG = 'E'", FDC_ABSENCE_AET.PROCESS_INSTANCE, &fail_count);
   SQLExec("SELECT COUNT(*) FROM  %TABLE(FDC_ABEVT_TAO) WHERE PROCESS_INSTANCE = :1 AND PROCESS_FLAG = 'Y'", FDC_ABSENCE_AET.PROCESS_INSTANCE, &success_count);
   SQLExec("SELECT COUNT(*) FROM  %TABLE(FDC_ABEVT_TAO) WHERE PROCESS_INSTANCE = :1 AND PROCESS_FLAG = 'N'", FDC_ABSENCE_AET.PROCESS_INSTANCE, &not_process_count);
   &fileLog.WriteLine(" ");
   &fileLog.WriteLine("Total number of rows in File               : " | &total_count);
   &fileLog.WriteLine("Number of rows loaded successfully         : " | &success_count);
   &fileLog.WriteLine("Number of rows not loaded                  : " | &fail_count);
   &fileLog.WriteLine("Number of rows not Processed               : " | &not_process_count);

catch Exception &ex

   &fileLog.WriteLine(&ex.ToString());
end-try;

&fileLog.Close();


----------------------------------------------------------------------------------------------------------

Date validation -




import FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON;

Declare Function AgeInYears PeopleCode FUNCLIB_HR.AGE FieldFormula;

Function CalcAge() Returns number;
 
   SQLExec("SELECT BIRTHDATE FROM PS_PERSONAL_DATA WHERE EMPLID =:1", FD_SVR_USA_DTL.EMPLID.Value, &birth_date);
   &Current_date = %Date;
 
   If All(&birth_date) Then
      &EMP_AGE = AgeInYears(&birth_date, &Current_date);
   End-If;
 
   Return &EMP_AGE;
 
 
End-Function;

Function CalcRule70(&ServYr) Returns number;
   <* &EMP_AGE = CalcAge();
 
   &rule_of_70_calc = &EMP_AGE + &ServYr;
 
   If &rule_of_70_calc >= "70" Then
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELIG.Value = "Y";
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELIG.DisplayOnly = True;
   Else
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELIG.Value = "N";
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELIG.DisplayOnly = True;
   End-If;
 
   If FD_SVR_USA_DTL.FD_SVR_RULE70_ELIG.Value = "Y" Then
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELEC.Value = "Y";
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELEC.DisplayOnly = True;
   Else
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELEC.Value = "N";
      FD_SVR_USA_DTL.FD_SVR_RULE70_ELEC.DisplayOnly = True;
   End-If;*>
 
End-Function;

Function CalcSrvcdate Returns number;
 
   Local FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON &SvrCommon = create FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON();
   &REC_JOB = &SvrCommon.GetJobRecord(FD_SVR_USA_DTL.EMPLID.Value, "EMP");
   If All(&REC_JOB.termination_dt.value) Then
      &term_dt = &REC_JOB.termination_dt.value;
   Else
      &term_dt = %Date;
   End-If;
 
   rem &serv_date = &SvrCommon.GetServdate(FD_SVR_USA_DTL.EMPLID.Value, 0, "EMP");
   &serv_date = &SvrCommon.GetServdate(FD_SVR_USA_DTL.EMPLID.Value, 0);
   &ServYr = "";
   If (All(&serv_date) And
         All(&term_dt)) Then
      &ServYr = AgeInYears(&serv_date, &term_dt);
   End-If;
   rem &rule70 = CalcRule70(&ServYr);
   Return &ServYr;
 
End-Function;


Function getPayabletime() Returns number;
 
   <* Local FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON &SvrCommon = create FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON();
   &REC_JOB = &SvrCommon.GetJobRecord(FD_SVR_USA_DTL.EMPLID.Value, "EMP");
   &term_dt = &REC_JOB.termination_dt.value;
   &serv_date = &SvrCommon.GetServdate(FD_SVR_USA_DTL.EMPLID.Value, 0, "EMP");
   &ServYr = "";
   If (All(&serv_date) And
         All(&term_dt)) Then
      &ServYr = AgeInYears(&serv_date, &term_dt);
   End-If;*>
   &sevyr = CalcSrvcdate();
 
 
   rem Evaluate &ServYr;
   Evaluate &sevyr
   When <= "1"
      &Sev_weeks = "2";
      Break;
      <*When >= "2"
      &Sev_weeks = 1 * (&ServYr);
      Break;*>
   When <= "25"
      &Sev_weeks = 1 * (&ServYr);
      Break;
   When >= "26"
      &Sev_weeks = "26";
      Break;
   End-Evaluate;
 
   FD_SVR_USA_DTL.FD_SVR_PY_TIME.Value = &Sev_weeks;
   FD_SVR_USA_DTL.FD_SVR_PY_TIME.DisplayOnly = True;
   FD_SVR_USA_DTL.FD_SVR_PAY_DUR.Value = "WEEKS";
   FD_SVR_USA_DTL.FD_SVR_PAY_DUR.DisplayOnly = True;
   &days = FD_SVR_USA_DTL.FD_SVR_PY_TIME.Value * 7;
 
 
   Return &ServYr;
 
 
End-Function;



Function CalcRates(&req_rate) Returns number;
 
   Local FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON &SvrCommon = create FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON();
   &rate = 0;
   &daily_rt = 0;
   &monthly_rt = 0;
   &ANNUAL_RT = 0;
   &HOURLY_RT = 0;
   &STD_HOURS = 0;
 
   &REC_JOB = &SvrCommon.GetJobRecord(FD_SVR_USA_DTL.EMPLID.Value, "EMP");
   &daily_rt = &REC_JOB.DAILY_RT.value;
   &monthly_rt = &REC_JOB.MONTHLY_RT.value;
   &ANNUAL_RT = &REC_JOB.ANNUAL_RT.value;
   &HOURLY_RT = &REC_JOB.HOURLY_RT.value;
   &STD_HOURS = &REC_JOB.STD_HOURS.Value;
   If &req_rate = "SevAmount" Then
      If &HOURLY_RT <> 0 Then
         &rate = &HOURLY_RT;
      Else
       
         rem &rate = Round((&daily_rt / 8), 2);
         &rate = (&daily_rt / 8);
      End-If;
   End-If;
 
   If &req_rate = "Nonworkingamt" Then
      If &daily_rt <> 0 Then
         &rate = &daily_rt;
      Else
         rem &rate = Round((&HOURLY_RT * &STD_HOURS / 5), 2);
         &rate = (&HOURLY_RT * &STD_HOURS / 5);
      End-If;
   End-If;
   Return &rate;
 
End-Function;

Function CalcSevAmt(&pay_time As number, &pay_st_date As date) Returns number;
   FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value = 0;
   FD_SVR_USA_DTL.FD_SVR_CONTG_SEVR.Value = "0";
   FD_SVR_USA_DTL.FD_SVR_CONTG_STDT.Value = "";
   FD_SVR_USA_DTL.FD_SVR_CONTG_ENDDT.Value = "";
   FD_SVR_USA_DTL.FD_SVR_BASDISM_AMT.Value = "0";
   FD_SVR_USA_DTL.FD_SVR_TOTAL_PAID.Value = "0";
   FD_SVR_USA_DTL.FD_SVR_SVC_FRNG.Value = "0";
 
   Local FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON &SvrCommon = create FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON();
   Local date &cont_start_day, &cont_num_day, &cont_end_day;
   &days = FD_SVR_USA_DTL.FD_SVR_PY_TIME.Value * 7;
 
   &REC_JOB = &SvrCommon.GetJobRecord(FD_SVR_USA_DTL.EMPLID.Value, "EMP");
   If All(FD_SVR_USA_DTL.FD_SVR_PY_TIME.Value) And
         All(FD_SVR_USA_DTL.FD_SVR_PY_STDT.Value) Then
     
      &sev_calc_days = FD_SVR_USA_DTL.FD_SVR_PY_TIME * 7;
      &sev_calc_weeks = FD_SVR_USA_DTL.FD_SVR_PY_TIME.Value;
      &rate = CalcRates("SevAmount");
      rem &hrly_rate = &REC_JOB.HOURLY_RT.value;
     
      &STD_HOURS = &REC_JOB.STD_HOURS.value;
      &Weekly_rate = &rate * &STD_HOURS;
     
     
     
      If &sev_calc_weeks <= "12" Then
         &cont_weeks = "0";
         &Sev_weeks = &sev_calc_weeks;
       
         FD_SVR_USA_DTL.FD_SVR_BASDISM_AMT.Value = &Weekly_rate * &sev_calc_weeks;
         FD_SVR_USA_DTL.FD_SVR_CONTG_SEVR.Value = "0";
         FD_SVR_USA_DTL.FD_SVR_CONTG_STDT.Value = "";
         FD_SVR_USA_DTL.FD_SVR_CONTG_ENDDT.Value = "";
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value = FD_SVR_USA_DTL.FD_SVR_BASDISM_AMT.Value + FD_SVR_USA_DTL.FD_SVR_CONTG_SEVR.Value;
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value = Round(FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value, 2);
         FD_SVR_USA_DTL.FD_SVR_SVC_FRNG.Value = Round((FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value * 0.15), 2); /*Added logic to calculate the svc fringe based on dismissal $*/
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.DisplayOnly = True;
      Else
         &cont_weeks = &sev_calc_weeks - 12;
         &sev_calc_weeks = &sev_calc_weeks - &cont_weeks;
         &cont_days = &cont_weeks * 7;
         &sev_days = &sev_calc_weeks * 7;
       
         &cont_num_day = AddToDate(FD_SVR_USA_DTL.FD_SVR_PY_STDT.Value, 0, 0, &sev_days);
         &cont_num_day = AddToDate(&cont_num_day, 0, 0, - 1); /*logic changed to include the start date also while calcuating the date difference */
         &cont_start_day = AddToDate(&cont_num_day, 0, 0, 1);
         &cont_end_day = AddToDate(&cont_start_day, 0, 0, &cont_days);
         &cont_end_day = AddToDate(&cont_end_day, 0, 0, - 1); /*logic changed to include the contingency start date also while calcuating the date difference */
       
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value = "0";
         FD_SVR_USA_DTL.FD_SVR_BASDISM_AMT.Value = &Weekly_rate * &sev_calc_weeks;
         FD_SVR_USA_DTL.FD_SVR_CONTG_SEVR.Value = &Weekly_rate * &cont_weeks;
         FD_SVR_USA_DTL.FD_SVR_CONTG_STDT.Value = &cont_start_day;
         FD_SVR_USA_DTL.FD_SVR_CONTG_ENDDT.Value = &cont_end_day;
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value = FD_SVR_USA_DTL.FD_SVR_BASDISM_AMT.Value + FD_SVR_USA_DTL.FD_SVR_CONTG_SEVR.Value;
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value = Round(FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value, 2);
         FD_SVR_USA_DTL.FD_SVR_SVC_FRNG.Value = Round((FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value * 0.15), 2); /*Added logic to calculate the svc fringe based on dismissal $*/
         FD_SVR_USA_DTL.FD_SVR_DISM_AMT.DisplayOnly = True;
       
      End-If;
   End-If;
 
   If All(FD_SVR_USA_DTL.FD_SVR_PY_STDT.Value) And
         All(FD_SVR_USA_DTL.FD_SVR_PY_TIME.Value) Then
      FD_SVR_USA_DTL.FD_SVR_PY_ENDDT.Value = AddToDate(FD_SVR_USA_DTL.FD_SVR_PY_STDT.Value, 0, 0, &days);
      FD_SVR_USA_DTL.FD_SVR_PY_ENDDT.Value = AddToDate(FD_SVR_USA_DTL.FD_SVR_PY_ENDDT.Value, 0, 0, - 1); /*logic changed to include the start date also while calcuating the date difference */
      FD_SVR_USA_DTL.FD_SVR_PY_ENDDT.DisplayOnly = True;
   End-If;
 
   If All(FD_SVR_USA_DTL.FD_SVR_PY_ENDDT.Value) Then
      If All(FD_SVR_USA_DTL.FD_SVR_CONTG_ENDDT.Value) Then
         FD_SVR_USA_DTL.TERMINATION_DT.Value = AddToDate(FD_SVR_USA_DTL.FD_SVR_CONTG_ENDDT.Value, 0, 0, 1);
         FD_SVR_USA_DTL.TERMINATION_DT.DisplayOnly = True;
      Else
         FD_SVR_USA_DTL.TERMINATION_DT.Value = AddToDate(FD_SVR_USA_DTL.FD_SVR_PY_ENDDT.Value, 0, 0, 1);
         FD_SVR_USA_DTL.TERMINATION_DT.DisplayOnly = True;
      End-If;
   End-If;
   /*Added additional pay $ to the total paid calculation to the below code - UAT enhancement*/
   FD_SVR_USA_DTL.FD_SVR_TOTAL_PAID.Value = FD_SVR_USA_DTL.FD_SVR_RET_TOTAL.Value + FD_SVR_USA_DTL.FD_SVR_WNTC_AMT.Value + FD_SVR_USA_DTL.FD_SVR_NWNTC_AMT.Value + FD_SVR_USA_DTL.FD_SVR_NWNTC_FRNG.Value + FD_SVR_USA_DTL.FD_SVR_SVC_FRNG.Value + FD_SVR_USA_DTL.FD_SVR_DISM_AMT.Value + FD_SVR_USA_DTL.FD_SVR_OUTPLCMNT.Value + FD_SVR_USA_DTL.FD_SVR_VAC_AMT.Value + FD_SVR_USA_DTL.FD_SVR_ADDPAY.Value;
 
 
End-Function;

Function CalcPayableAmt(&start_dt As date, &end_dt As date) Returns number;
   Local FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON &SvrCommon = create FD_SVR_LIB:FD_SVR_COMMON_LIB:FD_SVR_COMMON();
   &day_count = 0;
 
   &REC_JOB = &SvrCommon.GetJobRecord(FD_SVR_USA_DTL.EMPLID.Value, "EMP");
 
 
 
   SQLExec("SELECT COUNT (*)FROM (SELECT ROWNUM rnum FROM ps_fd_svr_usa_dtl WHERE ROWNUM <= TO_DATE (%datein(:2)) - TO_DATE (%datein(:1)) + 1) WHERE TO_CHAR (TO_DATE (%datein(:1)) + rnum - 1, 'DY') NOT IN ('SAT', 'SUN')", &start_dt, &end_dt, &day_count);
   rem  MessageBox(0, "", 0, 0, "days : " | &day_count);
   Return &day_count;
 
 
 
 
 
End-Function;



App Engine -


-------------------------------------------------------------------------------------------------------------------------

pc

MessageBox(0, "", 18160, 43, "Message not found");
&RsMain1 = GetLevel0()(1).GetRowset(@("Scroll." | &DTL_Rec));
&SearchResultRS = CreateRowset(@("Record." | &SRCH_Rec));
&RSGridRslt = GetLevel0()(1).GetRowset(Scroll.FD_SVR_SRCH1);
&RSGridRslt.Flush();
&RSGridRslt.InsertRow(0);
Local Rowset &RSGrid;
&RSGrid = GetLevel0()(1).GetRowset(@("Scroll." | &DTL_Rec));
Local Row &COPYROWTO;
&RSGrid.InsertRow(0);
&COPYROWTO = &RSGrid.GetRow(1);
&COPYROWTO.GetRecord(@("Record." | &DTL_Rec)).EMPLID.Value = &RSGridRslt(&i).GetRecord(Record.FD_SVR_SRCH1).EMPLID.Value;
MessageBox(0, "", 21000, 1010, "Message not found", String(&EmplCounter));
 If All(GetRecord(@("Record." | &HDR_Rec)).FD_SVR_POA_ID.Value) Then
      If True = &SvrCommon.IsPartOfEvent(&ParamValue, GetRecord(@("Record." | &HDR_Rec)).FD_SVR_EVENT_NAME.Value, GetRecord(@("Record." | &HDR_Rec)).FD_SVR_POA_ID.Value) Then
         Error MsgGet(21000, 1017, "Message not found", GetRecord(@("Record." | &HDR_Rec)).FD_SVR_POA_ID.Value);
      End-If;
   End-If;
 &URL_Link = GenerateComponentContentURL(%Portal, %Node, MenuName.ZD_SVR, "GBL", @("Component." | %Component), @("Page." | &Event_Page), "C", &TempRec11);

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8" />
<style>
table {
    font-family: arial, sans-serif;
    border-collapse: collapse;
    width: 40%;
 font-size: 90%;
 border-style: double;
}
td, th {
    border: 2px solid #dddddd;
    text-align: left;
    padding: 8px;
 font-size: 90%;
border-style: double
}
tr:nth-child {
    background-color: #dddddd;
}
</style>
</head>
<body>
The Below Scheduled Process did not run. Please check last run date in process monitor
</br>
</br>
<table>
%1
 </table>
</br>
</br>
</br>
</br>
</body>
"***Please do not respond to this mail***"
</html>
import PT_MCF_MAIL:*;
Local PT_MCF_MAIL:MCFOutboundEmail &eMail = create PT_MCF_MAIL:MCFOutboundEmail();
Local Rowset &Email_Tmpl = CreateRowset(Record.WL_TEMPLATE_GEN);
&TemplateName = "Job Monitioring Notification";
&Email_Tmpl.Fill("WHERE WL_TEMPLATE_ID = :1", &TemplateName);
&subj_text = &Email_Tmpl(1).WL_TEMPLATE_GEN.WL_SUBJECT.Value;
Local Rowset &Rs = CreateRowset(Record.ZD_PRCS_JOB_VW);
Local Rowset &Rs1 = CreateRowset(Record.ZD_MAIL_OPR_VW);
&Rs.Fill("ORDER BY PRCSNAME");
/*Body for multiple parts*/
Local string &plain_text = "***This mail is to test sending welcome letters using BI Publisher***";
Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();
&text.Text = &plain_text;
&text.ContentType = "Content-type: text/html; charset=UTF-8";
/*Build HTML Table format */
&td = "<td>";
&td1 = "</td>";
&tr = "<tr>";
&tr1 = "</tr>";
&th = "<th>";
&th1 = "</th>";
&count = &Rs.ActiveRowCount;
rem MessageBox(0, "", 0, 0, "Rowcount : " | &count);
If &count > "0" Then
   For &i = 1 To &Rs.ActiveRowCount
      &output_email = "";
      &header = &tr | &th | "S.no" | &th1 | &th | "Runcontrol" | &th1 | &th | "Process" | &th1 | &th | "Frequency" | &th1 | &th | "Status" | &th1 | &tr1;
      SQLExec(SQL.ZD_RECURNAME_SQL, &Rs(&i).ZD_PRCS_JOB_VW.ZD_RECURNAME.Value, &freq);
      rem SQLExec(SQL.ZD_RUNSTATUS_SQL, &Rs(&i).ZD_PRCS_JOB_VW.RUNSTATUS.Value, &runstatus);
      &runstatus = "Scheduled Job has not run";
      &process = &tr | &td | "1" | ".)" | &td1 | &td | &Rs(&i).ZD_PRCS_JOB_VW.RUNCNTLID.Value | &td1 | &td | &Rs(&i).ZD_PRCS_JOB_VW.PRCSNAME.Value | &td1 | &td | &freq | &td1 | &td | &runstatus | &td1 | &tr1;
      &output_process1 = &header | &process;
      &str_Etext = &Email_Tmpl(1).WL_TEMPLATE_GEN.WL_MSG_TXT.Value;
      &str_Etext = Substitute(&str_Etext, "%1", &output_process1);
      &text.Text = &str_Etext;
      /*Send Mail to Group that is assigned in Job Monitoring Setup Page*/
      Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart();
      &mp.AddBodyPart(&text);
      &Rs1.Fill("WHERE ROLENAME =:1", &Rs(&i).ZD_PRCS_JOB_VW.ROLENAME.Value);
      For &j = 1 To &Rs1.ActiveRowCount
         &email1 = &Rs1(&j).ZD_MAIL_OPR_VW.EMAILID.Value;
         &email1 = &email1 | ";";
         &output_email = &output_email | &email1;
      End-For;
      SQLExec("SELECT EMAILID FROM PSOPRDEFN WHERE OPRID =:1", &Rs(&i).ZD_PRCS_JOB_VW.OPRID.Value, &emailid);
      &output_email = &output_email | ";" | &emailid;
      MessageBox(0, "", 0, 0, "mail list : " | &output_email);
     
      &ToList = &output_email;
      &Subject = &subj_text;
      &eMail.Recipients = &ToList; /*comma separeted list of email addresses*/
      &eMail.From = &FromList; /*from email address*/
      &eMail.ReplyTo = &ReplyToList; /*in case the reply is to be sent to a different email address*/
      &eMail.Subject = &Subject;
      &eMail.MultiPart = &mp;
      Local integer &resp = &eMail.Send();
     
   End-For;
End-If;

 SELECT DECODE(SUM(CALC_RSLT_VAL)
 , 0
 , SUM(UNIT_RSLT_VAL) +SUM(UNIT_ADJ_VAL)
 ,SUM(CALC_RSLT_VAL) + SUM(CALC_ADJ_VAL) )
---------------------
SELECT
(CASE WHEN A.OBJECTTYPE = 0 Then 'Record'
WHEN A.OBJECTTYPE = 1 Then 'Index'
WHEN A.OBJECTTYPE = 2 Then 'Field'
WHEN A.OBJECTTYPE = 3 Then 'Field Format'
WHEN A.OBJECTTYPE = 4 Then 'Translate Value'
WHEN A.OBJECTTYPE = 5 Then 'Pages'
WHEN A.OBJECTTYPE = 6 Then 'Menus'
WHEN A.OBJECTTYPE = 7 Then 'Components'
WHEN A.OBJECTTYPE = 8 Then 'Record PeopleCode'
WHEN A.OBJECTTYPE = 9 Then 'Menu PeopleCode'
WHEN A.OBJECTTYPE = 10 Then 'Query'
WHEN A.OBJECTTYPE = 11 Then 'Tree Structures'
WHEN A.OBJECTTYPE = 12 Then 'Trees'
WHEN A.OBJECTTYPE = 13 Then 'Access group'
WHEN A.OBJECTTYPE = 14 Then 'Color'
WHEN A.OBJECTTYPE = 15 Then 'Style'
WHEN A.OBJECTTYPE = 16 Then 'N/A'
WHEN A.OBJECTTYPE = 17 Then 'Business process'
WHEN A.OBJECTTYPE = 18 Then 'Activity'
WHEN A.OBJECTTYPE = 19 Then 'Role'
WHEN A.OBJECTTYPE = 20 Then 'Process Definition'
WHEN A.OBJECTTYPE = 21 Then 'Server Definition'
WHEN A.OBJECTTYPE = 22 Then 'Process Type Definition'
WHEN A.OBJECTTYPE = 23 Then 'Job Definitions'
WHEN A.OBJECTTYPE = 24 Then 'Recurrence Definition'
WHEN A.OBJECTTYPE = 25 Then 'Message Catalog'
WHEN A.OBJECTTYPE = 26 Then 'Dimension'
WHEN A.OBJECTTYPE = 27 Then 'Cube Definitions'
WHEN A.OBJECTTYPE = 28 Then 'Cube Instance Definitions'
WHEN A.OBJECTTYPE = 29 Then 'Business Interlink'
WHEN A.OBJECTTYPE = 30 Then 'SQL'
WHEN A.OBJECTTYPE = 31 Then 'File Layout Definition'
WHEN A.OBJECTTYPE = 32 Then 'Component Interfaces'
WHEN A.OBJECTTYPE = 33 Then 'AE program'
WHEN A.OBJECTTYPE = 34 Then 'AE section'
WHEN A.OBJECTTYPE = 35 Then 'Message Node'
WHEN A.OBJECTTYPE = 36 Then 'Message Channel'
WHEN A.OBJECTTYPE = 37 Then 'Message'
WHEN A.OBJECTTYPE = 38 Then 'Approval rule set'
WHEN A.OBJECTTYPE = 39 Then 'Message PeopleCode'
WHEN A.OBJECTTYPE = 40 Then 'Subscription PeopleCode'
WHEN A.OBJECTTYPE = 41 Then 'N/A'
WHEN A.OBJECTTYPE = 42 Then 'Component Interface PeopleCode'
WHEN A.OBJECTTYPE = 43 Then 'AE PeopleCode'
WHEN A.OBJECTTYPE = 44 Then 'Page PeopleCode'
WHEN A.OBJECTTYPE = 45 Then 'Page Field PeopleCode'
WHEN A.OBJECTTYPE = 46 Then 'Component PeopleCode'
WHEN A.OBJECTTYPE = 47 Then 'Component Record PeopleCode'
WHEN A.OBJECTTYPE = 48 Then 'Component Rec Fld PeopleCode'
WHEN A.OBJECTTYPE = 49 Then 'Image'
WHEN A.OBJECTTYPE = 50 Then 'Style sheet'
WHEN A.OBJECTTYPE = 51 Then 'HTML'
WHEN A.OBJECTTYPE = 52 Then 'Not used'
WHEN A.OBJECTTYPE = 53 Then 'Permission List'
WHEN A.OBJECTTYPE = 54 Then 'Portal Registry Definitions'
WHEN A.OBJECTTYPE = 55 Then 'Portal Registry Structures'
WHEN A.OBJECTTYPE = 56 Then 'URL Definitions'
WHEN A.OBJECTTYPE = 57 Then 'Application Packages'
WHEN A.OBJECTTYPE = 58 Then 'Application Package Peoplecode'
WHEN A.OBJECTTYPE = 59 Then 'Portal Registry User Homepage'
WHEN A.OBJECTTYPE = 60 Then 'Problem Type'
WHEN A.OBJECTTYPE = 61 Then 'Archive Templates'
WHEN A.OBJECTTYPE = 62 Then 'XSLT'
WHEN A.OBJECTTYPE = 63 Then 'Portal Registry User Favorite'
WHEN A.OBJECTTYPE = 64 Then 'Mobile Page'
WHEN A.OBJECTTYPE = 65 Then 'Relationships'
WHEN A.OBJECTTYPE = 66 Then 'Component Interface Property Peoplecode'
WHEN A.OBJECTTYPE = 67 Then 'Optimization Models'
WHEN A.OBJECTTYPE = 68 Then 'File References'
WHEN A.OBJECTTYPE = 69 Then 'File Type Codes'
WHEN A.OBJECTTYPE = 70 Then 'Archive Object Definitions'
WHEN A.OBJECTTYPE = 71 Then 'Archive Templates (Type 2)'
WHEN A.OBJECTTYPE = 72 Then 'Diagnostic Plug In'
WHEN A.OBJECTTYPE = 73 Then 'Analytic Model'
ELSE TO_CHAR(A.OBJECTTYPE) END) AS OBJECTTYPE,
 (CASE WHEN A.OBJECTID4 <> 0 THEN
    (A.OBJECTVALUE1 || ' -> ' || A.OBJECTVALUE2 || ' -> ' || A.OBJECTVALUE3 || ' -> ' || A.OBJECTVALUE4)
 ELSE (CASE WHEN A.OBJECTID3 <> 0 THEN
    (A.OBJECTVALUE1 || ' -> ' || A.OBJECTVALUE2 || ' -> ' || A.OBJECTVALUE3)
 ELSE (CASE WHEN A.OBJECTID2 <> 0 THEN
    (A.OBJECTVALUE1|| ' -> ' || A.OBJECTVALUE2)
 ELSE A.OBJECTVALUE1 END) END )END) AS OBJECTVALUE
FROM PSPROJECTITEM A WHERE A.PROJECTNAME = '2356';

 AddToDate(FD_SVR_USA_DTL.FD_SVR_NTFY_DT, 0, 0, 1);
MessageBox(0, "Warning", 21000, 1023, "Message not found", &date2, &date1);
SQLExec("SELECT COUNT (*)FROM (SELECT ROWNUM rnum FROM ps_fd_svr_usa_dtl WHERE ROWNUM <= TO_DATE (%datein(:2)) - TO_DATE (%datein(:1)) + 1) WHERE TO_CHAR (TO_DATE (%datein(:1)) + rnum - 1, 'DY') NOT IN ('SAT', 'SUN')", &start_dt, &end_dt, &day_count);
&Rec1.FD_SVR_HRNTFY_ID.SetCursorPos(%Page);
         &Rec1.FD_SVR_HRNTFY_ID.Style = "PSERROR";
         MessageBox(0, "Error", 21000, 1038, "Message not found");

---2nd row
SELECT * FROM (
SELECT RANK() OVER (PARTITION BY EMPLID, EMPL_RCD ORDER BY EFFDT DESC, EFFSEQ DESC) RANK1,
A.* FROM PS_JOB A WHERE EMPLID = '266314')
WHERE RANK1 = 2;
---Fetch first row of Termination in Job
SELECT *
  FROM PS_JOB A
  WHERE 
       A.EFFDT =
        (SELECT MAX(A_ED.EFFDT) FROM PS_JOB A_ED
        WHERE A.EMPLID = A_ED.EMPLID
          AND A.EMPL_RCD = A_ED.EMPL_RCD 
          AND A_ED.EFFDT <= (SELECT MIN(A_ED.EFFDT)
  FROM PS_JOB A_ED
  WHERE  A_ED.ACTION ='TER'   AND A.EMPLID =A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD ))
  AND (SELECT COUNT(*) FROM PS_JOB A_ED WHERE  A_ED.ACTION ='TER'  
 AND A.EMPLID =A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD AND A_ED.EFFDT >= (SELECT MIN(A_ED.EFFDT)
 FROM PS_JOB A_ED
 WHERE  A_ED.ACTION ='TER' AND A.EMPLID =A_ED.EMPLID AND A.EMPL_RCD = A_ED.EMPL_RCD))> 1
    AND A.EFFSEQ =
        (SELECT MAX(A_ES.EFFSEQ) FROM PS_JOB A_ES
        WHERE A.EMPLID = A_ES.EMPLID
          AND A.EMPL_RCD = A_ES.EMPL_RCD
          AND A.EFFDT = A_ES.EFFDT) ;

PC2

&Pswd = Decrypt("", ZIN_CEO_RPT_AET.PASSWORD.Value);
SQLExec("SELECT A.PRCSINSTANCE FROM PSPRCSRQST A WHERE A.PRCSNAME = 'HR' AND A.RUNSTATUS = 9 AND A.JOBINSTANCE = (SELECT JOBINSTANCE FROM PSPRCSRQST WHERE PRCSNAME = 'ZINEORPT' AND RUNSTATUS = 7 AND OPRID = :1 AND RUNCNTLID = :2)", %OperatorId, ZIN_CEO_RPT_AET.RUN_CNTL_ID.Value, &Prcs_instance);
&outfile1 = GetURL(URL.ZIN_PAYPREFIX) | "rep_" | &Prcs_instance | ".csv";
&zipfile = GetURL(URL.ZIN_PAYPREFIX) | "rep_" | &Prcs_instance | ".zip";
&zip_cmd = "zip -P " | &Pswd | " -j -m  " | "'" | &zipfile | "'" | " " | "'" | &outfile1 | "'";
&StatusCode = Exec(&zip_cmd, %Exec_Synchronous + %FilePath_Absolute);
/*Copy file onto process monitor */
SQLExec("SELECT PRCSOUTPUTDIR FROM PSPRCSPARMS WHERE  PRCSINSTANCE = :1", ZIN_CEO_RPT_AET.PROCESSINSTANCE.Value, &Prcs_dir);
&prcsfile = &Prcs_dir | "/" | "Report_" | &Prcs_instance | ".zip";
&cp_cmd = "cp " | &zipfile | " " | &prcsfile;
Warning (&cp_cmd);
&Cp_Status = Exec(&cp_cmd, %Exec_Synchronous + %FilePath_Absolute);
&ArchiveSQL = CreateSQL("SELECT P.PRCSINSTANCE FROM PSPRCSRQST P WHERE P.PRCSNAME = 'HR' AND P.RUNDTTM <= SYSDATE - 90");
While &ArchiveSQL.FETCH(&Prcs_id);
Warning ("Archiving Process Instance is : " | &Prcs_id);
&rmfile = GetURL(URL.ZIN_PAYPREFIX) | "CEO_Report_" | &Prcs_id | ".zip";
&rm_cmd = "rm " | &rmfile;
Warning ("Remove command is : " | &rm_cmd);
&Arch_Sts = Exec(&rm_cmd, %Exec_Synchronous + %FilePath_Absolute);
If &Arch_Sts = 0 Then
      Warning ("Archiving  Successful");
   Else
      Warning &Arch_Sts;
      Warning ("Archiving   is not successful");
   End-If;
  
End-While;


!**************************************************************************
!* Procedure : Secure-file
!* Description: Secure-file
!**************************************************************************
begin-procedure Secure-file
let $zipfile = '{PAYPREFIX}' || 'CEO_Report_'|| $prcs_process_instance || '.zip'
Let $zip_cmd = 'zip -P ' || $Pswd   || ' -j -m  ' || '''' || $zipfile || '''' || ' ' || '''' || $outfile1 || ''''  
   
! show $zip_cmd

    call SYSTEM using $zip_cmd #move_status

 If #move_status<> 0
  show 'Securing CEO report is unsuccessful'                           
 else
  show 'Securing CEO report is successful'                           
 end-if
BEGIN-SELECT
R.PRCSOUTPUTDIR
 let $prcs_dir = &R.PRCSOUTPUTDIR
FROM PSPRCSPARMS R
WHERE R.PRCSINSTANCE = $prcs_process_instance
END-SELECT
let $prcsfile = $prcs_dir ||'/'|| 'CEO_Report_'|| $prcs_process_instance || '.zip'
let $cp_cmd = 'cp ' || $zipfile || ' ' || $prcsfile
! show $cp_cmd
call system using $cp_cmd #cp_status
   
 if #cp_status <> 0
  show 'File copy is unsuccessful'
 else
  show 'File copy is successful'
 end-if

end-procedure Secure-file
!**************************************************************************
!* Procedure : Archive-old-files
!* Description: Archive files older than 90 days
!**************************************************************************
begin-Procedure Archive-old-files
BEGIN-SELECT
P.PRCSINSTANCE
 let $prcs_inst = EDIT(&P.PRCSINSTANCE,'9999999')

 display 'Archive Processs instance is : '   noline
    display $prcs_inst

 let $rmfile = '{PAYPREFIX}' || 'CEO_Report_' || $prcs_inst ||'.zip'

 let $rm_cmd = 'rm ' || $rmfile
! show $rm_cmd
 call system using $rm_cmd #rm_status

 if #rm_status <> 0
  display 'Archive of file : ' noline
  display $rmfile  noline
  display ' is unsuccessful'
 else
  display 'Archive of file : ' noline
  display $rmfile  noline
  display ' is successful'
 end-if
FROM PSPRCSRQST P
WHERE P.PRCSNAME = 'HR173FC' AND P.RUNDTTM <= SYSDATE - 90
! Process archives files older than 90 days
END-SELECT
end-Procedure Archive-old-files

&sFilePrefix = &TLO_Common.GetPSImportInputFileNameFormat(ZIN_TLO_IMP_AET.ZIN_TLO_IMPORTS.Value);
&ErrLogFile = &TLO_Common.GetTCCArchivePath() | "\" | &sFilePrefix | "ErrLog_" | String(ZIN_TLO_IMP_AET.PROCESS_INSTANCE.Value) | "_" | DateTimeToLocalizedString(%Datetime, "yyyyMMdd_HHmmss") | ".csv";
&SummLogFile = &TLO_Common.GetTCCArchivePath() | "\" | &sFilePrefix | "SummLog_" | String(ZIN_TLO_IMP_AET.PROCESS_INSTANCE.Value) | "_" | DateTimeToLocalizedString(%Datetime, "yyyyMMdd_HHmmss") | ".csv";

!***************************************************************************
! Procedure: Text-Convert
! Function:  Convert ascii character
!***************************************************************************
begin-procedure Text-Convert($input, :$output)
Let #length = length($input)
Let $output = $input
Let #i = 1
While #i <= #length
 Let $val = substr($input,#i,1)
 Let #char = ascii($val)
 Let $char_val = ''
 do Get-Translate($val, $char_val)
 if $char_val <> ''
 Let $output = replace($output,$val,$char_val)
 end-if
 Add 1 to #i
end-while
end-procedure !Text-Convert

!***************************************************************************
! Procedure: Get-Translate
! Function:  Translate for non-english characters
!***************************************************************************
begin-procedure Get-Translate($val, :$char_val)
BEGIN-SELECT
CHARACTER_VALUE   &char_val
 Let $char_val = &char_val
! show '$char_val - ' $char_val
FROM PS_ZIN_SPLCHAR_MAP
WHERE (SYMBOLICID) = $val
end-select
end-procedure !Get-Translate
grep -i -e union *.sq* > Analysis.txt
lpad(to_char(#ESPP_Qtrly_Total_Participants),8,'0')
Let $ESPP_Qtrly_Not_usedtr= rpad($Blank,1874,' ')
--------------------------------------------------------------------
begin-procedure Format-Where-Clause
  move  'IN (''' to $WhereClause
  let #start_pos = 0
  let #count = 0
  while 1 = 1
    FIND $comma in $RptParm #start_pos #comma_loc
    if #comma_loc = -1
 if #count = 0
           let $SlctParm = rtrim(ltrim($RptParm,' '),' ')
           concat $SlctParm  with $WhereClause
           concat ''''           with $WhereClause
           let $WhereClause = $WhereClause || ')'
 else
           EXTRACT $SlctParm from $RptParm #start_pos #parm_len_last
           let $SlctParm = rtrim(ltrim($SlctParm,' '),' ')
           concat $SlctParm  with $WhereClause
           concat ''''           with $WhereClause
           let $WhereClause = $WhereClause || ')'
 end-if
 BREAK
    end-if
    let #parm_len = #comma_loc - #start_pos
    EXTRACT $SlctParm from $RptParm #start_pos #parm_len
    let $SlctParm = rtrim(ltrim($SlctParm,' '),' ')
    concat $SlctParm  with $WhereClause
    concat ''''           with $WhereClause
    concat ','''           with $WhereClause
    let #start_pos = #comma_loc + 1
    let #count = #count + 1
  
  end-while
end-procedure
----------------------------------------------------
%TruncateTable(%table(_TMP))
---------------------------------------------------
SELECT EMPLID, E."SUPERVISOR_ID"
  FROM (
 SELECT J1.EMPLID
 , J1.EMPL_RCD
 , J1.EMPL_STATUS
 , J1.POSITION_NBR
 , J1.REPORTS_TO
 , J2.EMPLID AS "SUPERVISOR_ID"
  FROM PS_JOB J1
  , PS_JOB J2
 WHERE J1.EFFDT = (
 SELECT MAX(EFFDT)
  FROM PS_JOB
 WHERE EMPLID = J1.EMPLID
   AND EMPL_RCD = J1.EMPL_RCD
   AND EFFDT <= SYSDATE)
   AND J1.EFFSEQ = (
 SELECT MAX(EFFSEQ)
  FROM PS_JOB
 WHERE EMPLID = J1.EMPLID
   AND EMPL_RCD = J1.EMPL_RCD
   AND EFFDT = J1.EFFDT)
   AND J1.REPORTS_TO = J2.POSITION_NBR
   AND J1.REPORTS_TO IS NOT NULL
   AND J1.REPORTS_TO <> ' '
   AND J2.EFFDT = (
 SELECT MAX(EFFDT)
  FROM PS_JOB
 WHERE EMPLID = J2.EMPLID
   AND EMPL_RCD = J2.EMPL_RCD
   AND EFFDT <= SYSDATE)
   AND J2.EFFSEQ = (
 SELECT MAX(EFFSEQ)
  FROM PS_JOB
 WHERE EMPLID = J2.EMPLID
   AND EMPL_RCD = J2.EMPL_RCD
   AND EFFDT = J2.EFFDT)
   and J2.HR_STATUS ='A') E START WITH E.EMPLID = (
 SELECT EMPLID
  FROM PS_ZIN_WHOS_R_CEO CEO
 WHERE CEO.EFFDT = (
 SELECT MAX(CEO1.EFFDT)
  FROM PS_ZIN_WHOS_R_CEO CEO1
 WHERE CEO1.EFFDT <= sysdate)) CONNECT BY NOCYCLE PRIOR E.EMPLID = E."SUPERVISOR_ID"
   AND LEVEL <= 13;

Zip 2

$ zip -P *secret* compressed.zip file.txt

Zip n mail

Local string &ZipFile = %FilePath | &zip_name; Local string &Zip_Cmd = "D:\BatchServer\Batch\PKzip25 " | &ZipFile | " -add " | &qryFile | "." | &ext; CommitWork(); &ExitCode = Exec(&Zip_Cmd, %Exec_Synchronous + %FilePath_Absolute); &email.AddAttachment(&ZipFile, %FilePath_Absolute, &zip_name, &attachDescr, "", "");

Zip password

**This method is used to create a password protected zip file.
* @param dirName of type String indicating the name of the directory to be zipped
* @param zipFileName of type String indicating the name of the zip file to be created
* @param password of type String indicating the password
*/
public static void zipDirWithPassword( String dirName , String zipFileName , String password)
{
if( zipFileName == null )
{
File tempFile = new File(dirName);
zipFileName = tempFile.getAbsoluteFile().getParent() +File.separator+tempFile.getName()+".zip";
}
zipDir(dirName, zipFileName);
String tempZipFileName = new File( dirName ).getAbsoluteFile() .getParent()+File.separator+"tempencrypted.zip";
try
{
AesZipFileEncrypter enc = new AesZipFileEncrypter (tempZipFileName);
enc.addEncrypted( new File(zipFileName), password);
new File(zipFileName).delete();
new File( tempZipFileName ).renameTo( new File (zipFileName));
}
catch (IOException e) 
{
e.printStackTrace();
}
}