Thursday, April 12, 2012

How to use a file layout in Peoplesoft ?










First, create the Staging tables and then use create a File Layout, map the Staging table as a Segment to the File Layout and use Browse button in the file Layout to map the source file into the File Layout. That it saving will set you all done.

Following is the Typical File Layout Code.

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;
Local integer &I, &L;
&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);
&CovrgCd = &RS2.GetRow(&I).GetRecord(1).GetField(Field.KZL_COVERAGE_ELECT).Value;
Rem This is the place where we usually modify, add all the field level validations to the file layout before loading it to the staging table



If (EditRecord(&REC2)) Then
&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;

&FILE1 = GetFile("C:\Documents and Settings\jvelchamy\Desktop\Csv_files\Benefits\FILE2.CSV", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("C:\Documents and Settings\jvelchamy\Desktop\Csv_files\Benefits\FILE2.CSV.err", "W", %FilePath_Absolute);
&FILE1.SetFileLayout(FileLayout.KZL_BEN_ENROLL_FL);
&LOGFILE.SetFileLayout(FileLayout.KZL_BEN_ENROLL_FL);
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.KZL_HLT_BEN_STG);
&SQL1 = CreateSQL("%Insert(:1)");
&RS1 = &FILE1.ReadRowset();
While &RS1 <> Null;
ImportSegment(&RS1, &RS);
&RS1 = &FILE1.ReadRowset();
End-While;

&FILE1.Close();
&LOGFILE.Close();

No comments:

Post a Comment

Phaniraavi@gmail.com

PeopleSoft Real Time Training

PeopleSoft Real Time Training

PeopleSoft RealTime Training

PeopleSoft RealTime Training