File Layout Peoplecode

Declare Function AX_set_directory PeopleCode AX_FUNCLIB_SS.FILE_DIRECTORY FieldFormula;

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
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, &SQL2, &SQL3;
Local integer &I, &L, &Max_Dep_count;
&Depend_Update_Flag = "N";
&Depend_Benef = 0;
&Max_PS_Dependent_id = 0;
&PS_Depend_Benef = 0;
&Dependent_id = 0;
AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = " ";
&SQL1 = CreateSQL("%Insert(:1)");
&RecordName = "RECORD." | &RS2.DBRecordName;
&REC2 = CreateRecord(@(&RecordName));
&RECP = &RSParent(1).GetRecord(@(&RecordName));
For &I = 1 To &RS2.ActiveRowCount
&RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
MessageBox(0, " ", 0, 0, &REC2.EMPLID.Value);

&REC2.DEP_BENEF_TYPE.Value = "D";
&REC2.INTERFACE_STATUS.Value = "NEW"; /* Set the status field to new for all rows */

&Effdt = &RS2.GetRow(&I).GetRecord(1).GetField(Field.DATE_FROM_ALIAS).Value;
If RTrim(&Effdt) <> "" Then
&REC2.EFFDT.Value = DateValue(&Effdt);
Else
&REC2.INTERFACE_STATUS.Value = "ERR";
&REC2.EFFDT.Value = "2099-01-01";
&REC2.DESCRLONG.Value = MsgGet(26003, 11, "Effdt does not exist", "Effdt");
End-If;

&Dob = &RS2.GetRow(&I).GetRecord(1).GetField(Field.BIRTHDATE_C).Value;
If RTrim(&Dob) <> "" Then
&REC2.DATE_OF_BIRTH.Value = DateValue(&Dob);
End-If;

&Exp_dt = &RS2.GetRow(&I).GetRecord(1).GetField(Field.DATE_FROM_ALIAS).Value;
If RTrim(&Exp_dt) <> "" Then
&REC2.EXP_DT.Value = DateValue(&Exp_dt);
End-If;

&Add_dttmt = &RS2.GetRow(&I).GetRecord(1).GetField(Field.FLDDATEFMT).Value;
If RTrim(&Add_dttmt) <> "" Then
&REC2.ADD_DTTM.Value = DateTimeValue(&Add_dttmt);
End-If;

&Startdatetime = &RS2.GetRow(&I).GetRecord(1).GetField(Field.DATE_CHAR).Value;
If RTrim(&Startdatetime) <> "" Then
&REC2.STARTDATETIME.Value = DateTimeValue(&Startdatetime);
End-If;


If (EditRecord(&REC2)) Then
/* check for the existence of the emplid in job record */
SQLExec("SELECT 'X' FROM PS_JOB WHERE EMPLID = :1", &REC2.EMPLID.Value, &Emplid_Exists);

/* If the emplid is not present in job then set the status field to error and provide the error reason */
If &Emplid_Exists <> "X" Then
&REC2.INTERFACE_STATUS.Value = "ERR";
&REC2.DESCRLONG.Value = &REC2.DESCRLONG.Value | " " | MsgGet(26003, 11, "Emplid does not exist", "Emplid");
End-If;
&Name = "";
&Benef_Dep_id = 0;
&SQL2 = CreateSQL("SELECT NAME,DEPENDENT_BENEF FROM PS_AX_DEPBEN_XLAT WHERE EMPLID = :1 ", &REC2.EMPLID.Value, &Name, &Benef_Dep_id);

While &SQL2.Fetch(&Name, &Benef_Dep_id)
&FirstName_Match = DBPatternMatch(&REC2.FIRST_NAME.Value, "%" | &Name | "%", False);
&LastName_Match = DBPatternMatch(&REC2.LAST_NAME.Value, "%" | &Name | "%", False);

If &FirstName_Match And
&LastName_Match And
All(&Name) Then
SQLExec("UPDATE PS_AX_STG_DEPEND SET FIRST_NAME = :1,LAST_NAME = :2,DEP_BENEF_TYPE=:3,AX_DEPENDENT_ID = :4 WHERE EMPLID = :5 AND NAME = :6 AND DEP_BENEF_TYPE = :7 AND DEPENDENT_BENEF = :8", &REC2.FIRST_NAME.Value, &REC2.LAST_NAME.Value, "O", &REC2.AX_DEPENDENT_ID.Value, &REC2.EMPLID.Value, &Name, "B", &Benef_Dep_id);
SQLExec("UPDATE PS_AX_DEPBEN_XLAT SET FIRST_NAME = :1,LAST_NAME = :2, AX_DEPENDENT_ID = :3 WHERE EMPLID = :4 AND DEPENDENT_BENEF = :5 AND NAME = :6 ", &REC2.FIRST_NAME.Value, &REC2.LAST_NAME.Value, &REC2.AX_DEPENDENT_ID.Value, &REC2.EMPLID.Value, &Benef_Dep_id, &Name);
&Depend_Update_Flag = "Y";
Break;
Else
&Depend_Update_Flag = "N";
End-If;
End-While;

&SQL3 = CreateSQL("SELECT DEPENDENT_BENEF FROM PS_AX_DEPBEN_XLAT A WHERE A.EMPLID = :1 AND UPPER(A.LAST_NAME) = :2 AND UPPER(A.FIRST_NAME) = :3 AND A.AX_DEPENDENT_ID = :4", &REC2.EMPLID.Value, Upper(&REC2.LAST_NAME.Value), Upper(&REC2.FIRST_NAME.Value), &REC2.AX_DEPENDENT_ID.Value);

&A = &SQL3.Fetch(&Depend_Benef);
REM SQLExec("SELECT DEPENDENT_BENEF FROM PS_AX_DEPBEN_XLAT A WHERE A.EMPLID = :1 AND A.AX_DEPENDENT_ID = :2 AND A.ASOFDATE = (SELECT MAX(A1.ASOFDATE) FROM PS_AX_DEPBEN_XLAT A1 WHERE A1.EMPLID = A.EMPLID AND A1.AX_DEPENDENT_ID = A.AX_DEPENDENT_ID AND A1.ASOFDATE <= %Datein(:3))", &REC2.EMPLID.Value, &REC2.AX_DEPENDENT_ID.Value, &REC2.EFFDT.Value, &Depend_Benef); REM SQLExec("SELECT DEPENDENT_BENEF FROM PS_AX_DEPBEN_XLAT A WHERE A.EMPLID = :1 AND UPPER(A.LAST_NAME) = :2 AND UPPER(A.FIRST_NAME) = :3 AND A.AX_DEPENDENT_ID = :4", &REC2.EMPLID.Value, Upper(&REC2.LAST_NAME.Value), Upper(&REC2.FIRST_NAME.Value), &REC2.AX_DEPENDENT_ID.Value, &Depend_Benef); If None(&Depend_Benef) Then REM If &A Then; REM SQLExec("SELECT A.DEPENDENT_BENEF from PS_DEPENDENT_BENEF A WHERE A.EMPLID = :1 AND (A.DEPENDENT_BENEF = :2 OR (UPPER(A.LAST_NAME) = :4 AND UPPER(A.FIRST_NAME) = :5 AND A.BIRTHDATE = %DATEIN(:6)))", &REC2.EMPLID.Value, &REC2.DEPENDENT_BENEF.Value, &REC2.NATIONAL_ID.Value, Upper(&REC2.LAST_NAME.Value), Upper(&REC2.FIRST_NAME.Value), &REC2.DATE_OF_BIRTH.Value, &PS_Depend_Benef); SQLExec("SELECT A.DEPENDENT_BENEF from PS_DEPENDENT_BENEF A WHERE A.EMPLID = :1 AND (A.DEPENDENT_BENEF = :2 OR (UPPER(A.LAST_NAME) = :4 AND UPPER(A.FIRST_NAME) = :5))", &REC2.EMPLID.Value, &REC2.DEPENDENT_BENEF.Value, &REC2.NATIONAL_ID.Value, Upper(&REC2.LAST_NAME.Value), Upper(&REC2.FIRST_NAME.Value), &PS_Depend_Benef); If None(&PS_Depend_Benef) Then SQLExec("SELECT MAX(DEPENDENT_BENEF) FROM PS_DEPENDENT_BENEF A WHERE A.EMPLID = :1", &REC2.EMPLID.Value, &Max_PS_Dependent_id); SQLExec("SELECT MAX(DEPENDENT_BENEF) FROM PS_AX_DEPBEN_XLAT A WHERE A.EMPLID = :1", &REC2.EMPLID.Value, &Stg_Dependent_id); If RTrim(&Max_PS_Dependent_id) = "" And RTrim(&Stg_Dependent_id) = "" Then AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = "01"; Else If Value(&Max_PS_Dependent_id) > Value(&Stg_Dependent_id) Then
AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = &Max_PS_Dependent_id + 1;
Else
AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = &Stg_Dependent_id + 1;
End-If;
End-If;
Else
AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = &PS_Depend_Benef;
End-If;

Else
AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = &Depend_Benef;
End-If;

If None(&Depend_Benef) Then
SQLExec("INSERT INTO PS_AX_DEPBEN_XLAT (EMPLID,DEPENDENT_BENEF,AX_DEPENDENT_ID,ASOFDATE,NATIONAL_ID,BIRTHDATE,LAST_NAME,FIRST_NAME,MIDDLE_NAME,NAME) VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10)", &REC2.EMPLID.Value, AX_BN_FIRST_AET.DEPENDENT_BENEF.Value, &REC2.AX_DEPENDENT_ID.Value, &REC2.EFFDT.Value, &REC2.NATIONAL_ID.Value, &REC2.DATE_OF_BIRTH.Value, &REC2.LAST_NAME.Value, &REC2.FIRST_NAME.Value, &REC2.MIDDLE_NAME.Value, &REC2.GetField(Field.NAME).Value);
End-If;
&REC2.DEPENDENT_BENEF.Value = AX_BN_FIRST_AET.DEPENDENT_BENEF.Value;
&Depend_Benef = 0;
&PS_Depend_Benef = 0;
&Dependent_id = 0;
AX_BN_FIRST_AET.DEPENDENT_BENEF.Value = " ";

&SQL1.Execute(&REC2);

&RS2(&I).GetRecord(1).CopyFieldsTo(&RECP);
For &L = 1 To &RS2.GetRow(&I).ChildCount
&RS1 = &RS2.GetRow(&I).GetRowset(&L);
If (&RS1 <> Null) Then
&RSP = &RSParent.GetRow(1).GetRowset(&L);
ImportSegment(&RS1, &RSP);
End-If;
End-For;
If &RSParent.ActiveRowCount > 0 Then
&RSParent.DeleteRow(1);
End-If;
Else
&LOGFILE.WriteRowset(&RS);
&LOGFILE.WriteLine("****Correct error in this record and delete all error messages");
&LOGFILE.WriteRecord(&REC2);
For &L = 1 To &RS2.GetRow(&I).ChildCount
&RS1 = &RS2.GetRow(&I).GetRowset(&L);
If (&RS1 <> Null) Then
&LOGFILE.WriteRowset(&RS1);
End-If;
End-For;
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;


AX_set_directory("BEN_FIRST_FILE_PATH", &Dir);
&LOGFILE = GetFile(&Dir | "\DEPENDENT_Err.txt", "W", %FilePath_Absolute); /*Error File*/
&LOGFILE.SetFileLayout(FileLayout.AX_BEN_DEPENDENT);
&SearchString = &Dir | "\AX_BEN_DEPENDENT*.*";
&FNAMES = FindFiles(&SearchString, %FilePath_Absolute);
While &FNAMES.Len > 0
&FileName = &FNAMES.Shift();
&FILE1 = GetFile(&FileName, "R", %FilePath_Absolute); /* Open each file from the folder*/
&FILE1.SetFileLayout(FileLayout.AX_BEN_DEPENDENT);

&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.AX_STG_DEPEND);
&RS1 = &FILE1.ReadRowset();
&RS1.DeleteRow(1); /*To delete the Header in the Input File*/
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
ImportSegment(&RS1, &RS);
&RS1 = &FILE1.ReadRowset();
End-While;
&MoveCommand = "cmd.exe /c move " | &FILE1.Name | " " | &Dir | "\Archive"; /*Archive the processed file */
&FILE1.Close();
&Return = Exec(&MoveCommand, %FilePath_Absolute);
End-While;
&LOGFILE.Close();