using peoplesoft file layout
DESCRIPTION
Using Peoplesoft File LayoutTRANSCRIPT
![Page 1: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/1.jpg)
Using PeopleSoft File Layout(Third Party Administrator File Generation)( y )
•Judi Doolittle, OCP, Oracle ACE•Natasha GarciaS di N i l L b iSandia National Laboratories
![Page 2: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/2.jpg)
What we will cover
• Writing Data to a Flat File• Methods to Read and Write (Plain Text Files)• Writing Files (Defined by a File Layout)• Creating an App Engine (to Export Data to
Flat File)• Creating Multilevel File Layout• PeopleCode for Multilevel File Layout• Segments in File Layout
N
![Page 3: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/3.jpg)
Writing Data to a Flat File
• File Class Methods– ReadLine (string)
W it Li ( t i )– WriteLine (string)– WriteString (string)
N
![Page 4: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/4.jpg)
PeopleCode ExampleLocal string &string;Local string &string;Local File &Myfile, &Myfile2;
&MyFile = GetFile(“test txt” “R”);&MyFile = GetFile( test.txt , R );&MyFile2 = GetFile(“c:\temp\test2.txt”, “W”, %FilePath_Absolute);
If &M Fil I O ThIf &MyFile.IsOpen ThenWhile &Myfile.ReadLine(&string);
&MyFile2.WriteLine(&string);E d WhilEnd-While;
End-If;
&M Fil Cl ()&MyFile.Close();&MyFile2.Close();
N
![Page 5: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/5.jpg)
PeopleCode ExampleL l X lD &i ldLocal XmlDoc &inxmldoc/* load message to file */&xmlfile = GetFile("IB MSG TEMP xml" "W");&xmlfile = GetFile("IB_MSG_TEMP.xml", "W");If &xmlfile.IsOpen Then
&xmlstring = &inxmldoc GenFormattedXmlString();&xmlstring = &inxmldoc.GenFormattedXmlString();&xmlfile.WriteString(&xmlstring);&xmlfile.Close();&xmlfile.Close();
End-If;
N
![Page 6: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/6.jpg)
Methods to Read and WriteMethods to Read and Write(Plain Text Files)• WriteRecord- writes the contents of a record
object to the file. You can use this to write one file record at a time and not have toone file record at a time and not have to instantiate and populate a rowset object.
• WriteRowset- writes the contents of a rowset object rowset to the output file associatedobject, rowset, to the output file associated with the file object that calls the method. Calling this method one time writes the entire gcontents of the rowset to the file.
N
![Page 7: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/7.jpg)
CreateRowset
Local Rowset &rs_carry_desig;
&rs_carry_desig = CreateRowset(Record.AV_DESIGNATION);
N
![Page 8: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/8.jpg)
CreateSQL FunctionI t ti t SQL bj t f th SQL l th it• Instantiates a SQL object from the SQL class—then it opens it on the sqlstring and/or input values.
• The sqlstring is the parameter that holds the SQLThe sqlstring is the parameter that holds the SQL statement
• SQL errors will cause PeopleCode to terminate with an error
• MetaSQL%SelectAll is a way to select everything in a record– %SelectAll is a way to select everything in a record
– Tip: Remember to wrap DateTime fields with %DateOut, %TimeOut, %DateIn, and %TimeIn
N
![Page 9: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/9.jpg)
PeopleCode ExampleL l R d & 1Local Record &rec1;Local SQL &SQL;
/*Create Instance of Record */&rec1 = CreateRecord(record tc help me tbl);&rec1 = CreateRecord(record.tc_help_me_tbl);
/*Create SQL object to populate rowset *// Create SQL object to populate rowset /&SQL = CreateSQL(“%selectall(:1)”, &rec1);…
N
![Page 10: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/10.jpg)
Writing Files using File LayoutLocal File &M FileLocal File &MyFile;Local Record &rec1;Local SQL &SQL1;/*Create Instance of Record */&rec1 = CreateRecord(record.tc_help_me_tbl);&MyFile = GetFile(“c:\temp\help me.txt”, “A”, %FilePath Absolute);y ( p p_ , , _ );&MyFile.setFileLayout(FileLayout.help_me);/*Create SQL object to populate rowset */&SQL1 = CreateSQL(“%selectall(:1)” &rec1);&SQL1 = CreateSQL( %selectall(:1) , &rec1);While &SQL1.Fetch(&rec1)
&MyFile.WriteRecord(&rec1);End-While;&MyFile.Close();
N
![Page 11: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/11.jpg)
Creating an App EngineCreating an App Engine(to Export Data to Flat File)• Steps
– Application Designer, File, New, Application Engine ProgramEngine Program
– Select Insert, Action– Save the ProgramSave the Program– Change the Action type to PeopleCode– Enter Code– Save– Run
N
![Page 12: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/12.jpg)
Steps for Creating a Multilevel Steps for Creating a Multilevel File Layout1 C t Vi1. Create a View2. Create a new file layout3 Drop and drag the view into the layout3. Drop and drag the view into the layout4. Use the toolbar arrows to determine hierarchy5. Double-click the record name to set segment
properties6. Assign a unique file record ID to each record7 Note: start position and length are set automatically7. Note: start position and length are set automatically8. Click OK9. Click Yes to increment the start position of all fields 10. Save the File
J
![Page 13: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/13.jpg)
Easiest Way to do File LayoutEasiest Way to do File Layout—Match to View• Record SL_BEN_COBC_VW
![Page 14: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/14.jpg)
File Layout one to one to View
![Page 15: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/15.jpg)
File Record ID
• Identifies the record• Can be used with fixed and CSV files• ID must be included in your flat file• Assigned at the file layout segment level
![Page 16: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/16.jpg)
![Page 17: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/17.jpg)
COBRA TPA (Thi d P t COBRA TPA (Third Party Administrator) File Layout Example
![Page 18: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/18.jpg)
![Page 19: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/19.jpg)
Creating Rowsets and File/***Create Rowset Instance***// Create Rowset Instance /&sl_rowset = CreateRowset(Record.SL_BEN_COBC_VW);&sl_rowset2 = &sl_rowset.GetRow(1).GetRowset(1);/***Create File Instance***/If &whereto = "local" Then
&path = "c:\temp\";Else
/* Get Unix path for files *//* Get Unix path for files */SQLExec("Select sl_charval from sl_pr_parm_tbl where sl_keyval = 'PATH-PROD-UNIX'", &path);
End-If;/* Filename */&filename = &curdate | "_CB_PAR_" | &clientid | ".txt";/** Create an instance of the File - this will be opened in Write mode with an absolute path. Then associate it with the appropriate File Layout */&COBRAFil G tFil (& th | &fil "W" %Fil P th Ab l t )&COBRAFile = GetFile(&path | &filename, "W", %FilePath_Absolute);/*********** Filelayout ************************************/&COBRAFile.SetFileLayout(FileLayout.SL_COB_PAR);
![Page 20: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/20.jpg)
SQL Statements
/* Create SQL object to retrieve values */
&SQL1 = CreateSQL("%selectall(:1)", &recl);&SQL2 = CreateSQL("%selectall(:1)", &rec2);&SQL3 = CreateSQL("%selectall(:1)", &rec3);
![Page 21: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/21.jpg)
Looping the Level One Record/* Loop through the rowset to get the values and write the rowset to file *// Loop through the rowset to get the values and write the rowset to file /While &SQL_HDR.Fetch(&recl)
/*copy record to rowset */&recl.CopyFieldsTo(&rowset1.GetRow(1).SL_BEN_COB_HDR);/* Write rowset to file */&COBRAFil W it R t(& t1)&COBRAFile.WriteRowset(&rowset1);
End-While;/* Fetch data from SL_BEN_COBP_VW */While &SQL_PAR.Fetch(&rec2)
&I = 1;/* Copy record to the rowset */&rec2.CopyFieldsTo(&rowset2.GetRow(&I).SL_BEN_COBP_VW);&rowset2.InsertRow(&I);&COBRAFile.WriteRowset(&rowset2);&I = &I + 1;&I &I + 1;
End-While;While &SQL_TRL.Fetch(&rec3)
/*copy record to rowset */&rec3.CopyFieldsTo(&rowset3.GetRow(1).SL_BEN_COB_TRL);/* Write rowset to file */&COBRAFile.WriteRowset(&rowset3);
End-While;
![Page 22: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/22.jpg)
Segments
• Uses:– Specify the action to take for rows of data
Li it fi ld th fil l t– Limit fields on the file layout– Identify File Layout
![Page 23: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/23.jpg)
Indicate Action
• You need to send a flat file to another app– Indicate Action
Insert• Insert• Update• Delete
• Use Segment to indicate the action
![Page 24: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/24.jpg)
Limit Fields
• Following provided from: “PeopleSoft Integration Tools and Utilities”, File Layout and Data Interchange sectionand Data Interchange section
• You have a file and you only want two fields from itfrom it
• One way is to update everything and then manually delete the unwanted datamanually delete the unwanted data
• Or insert a segment name it the same as the table and insert only the two fields you wanttable and insert only the two fields you want
![Page 25: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/25.jpg)
Limit Fields Cont.
• You have data that changes or you need to insert new rowsC t i l fi ld d AUDIT ACTION• Create a single field named AUDIT_ACTION which will indicate if the record is new or changedchanged
• Use PeopleCode to look for the field and value and process conditional logicvalue and process conditional logic
![Page 26: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/26.jpg)
Identifying the File Layout
• Use a segment to identify the part of the file you want to import
999 P d t /* PRODUCT l t*/999 Product /*use PRODUCT layout*/
![Page 27: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/27.jpg)
Steps to work with a SegmentD d d d i t th fil l t• Drag and drop your record into the file layout
• Select InsertSegment• Segment
• Record Name, Max Length, & File Record ID (if you have one))
• Place your cursor on the Segment Name• Select
– Insert File Field or– Insert Database Field
Then arrange the Segments in the order you want• Then arrange the Segments in the order you want
![Page 28: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/28.jpg)
Stepping Through Inserting a Stepping Through Inserting a Segment
![Page 29: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/29.jpg)
Creating a Child Segment
• Insert a Child Segment—highlight record, select Insert, ChildSegment, and enter file record namerecord name
![Page 30: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/30.jpg)
Putting it all togetherPutting it all together(real world example)
![Page 31: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/31.jpg)
What we covered
• Writing Data to a Flat File• Methods to Read and Write (Plain Text Files)• Writing Files (Defined by a File Layout)• Creating an App Engine (to Export Data to
Flat File)• Creating Multilevel File Layout• PeopleCode for Multilevel File Layout• Segments in File Layout
N
![Page 32: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/32.jpg)
Book Signing
• Time: 11:45 am to 12:45 pm• Location: Bookstore
![Page 33: Using Peoplesoft File Layout](https://reader034.vdocuments.us/reader034/viewer/2022050703/55cf9920550346d0339bb848/html5/thumbnails/33.jpg)
Download Presentation
• http://www.oraclepressbooks.com/downloads/