Application Engine Peoplecode bind variables

Question

I have the below PeopleCode step in an Application Engine program that reads a CSV file using a File Layout and then inserts the data into a table, and I am just trying to get a better understanding of how the the line of code (&SQL1 = CreateSQL("%Insert(:1)");) in the below script gets generated. It looks like the CreateSQL is using a bind variable (:1) inside the Insert statement, but I am struggling as where to find where this variable is defined in the program.

Function EditRecord(&REC As Record) Returns boolean;
   Local integer &E;

   &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)");
   rem &SQL1 = CreateSQL("%Insert(:1) Order by COUNT_ORDER");
   &RecordName = "RECORD." | &RS2.DBRecordName;
   &REC2 = CreateRecord(@(&RecordName));
   &RECP = &RSParent(1).GetRecord(@(&RecordName));
   For &I = 1 To &RS2.ActiveRowCount
      &RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
      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, &FILE3;
Local Record &REC1;
Local SQL &SQL1;
Local Rowset &RS1, &RS2;
Local integer &M;

&FILE1 = GetFile("\\nt115\apps\interface_prod\interface_in\Item_Loader\ItemPriceFile.csv", "r", "a", %FilePath_Absolute);
&LOGFILE = GetFile("\\nt115\apps\interface_prod\interface_in\Item_Loader\ItemPriceFile.txt", "r", "a", %FilePath_Absolute);

&FILE1.SetFileLayout(FileLayout.GH_ITM_PR_UPDT);
&LOGFILE.SetFileLayout(FileLayout.GH_ITM_PR_UPDT); 
&RS1 = &FILE1.CreateRowset();
&RS = CreateRowset(Record.GH_ITM_PR_UPDT);
REM &SQL1 = CreateSQL("%Insert(:1)");
&SQL1 = CreateSQL("%Insert(:1)");
/*Skip Header Row:  The following line of code reads the first line in the file layout (the header) 
and does nothing.  Then the pointer goes to the next line in the file and starts using the 
file.readrowset*/
&some_boolean = &FILE1.ReadLine(&string);
&RS1 = &FILE1.ReadRowset();

While &RS1 <> Null
   ImportSegment(&RS1, &RS);
   &RS1 = &FILE1.ReadRowset();
End-While;

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

Answer 1

The :1 is coming from the line further down &SQL1.Execute(&REC2);

&REC2 gets assigned a record object, so the line &SQL1.Execute(&REC2); evaluates to %Insert(your_record_object)

Here is a simple example that's doing basically the same thing

Here is a description of %Insert

Answer 2

Answer because too long to comment:

The table name is most likely (PS_)GH_ITM_PR_UPDT. The general consensus is to name the FileLayout the same as the record it is based on.

If not, it is defined in FileLayout.GH_ITM_PR_UPDT. Open the FileLayout, right click the segment and under 'Selected Node Properties' you will find the 'File Record Name'.

In your code this record is carried over into &RS1.

&FILE1.SetFileLayout(FileLayout.GH_ITM_PR_UPDT);
&RS1 = &FILE1.CreateRowset();

The rowset is a collection of rows. A row consists of records and a record is a row of data from a database table. (Peoplesoft Object Data Types are fun...) This rowset is filled with data in the following statement:

&RS1 = &FILE1.ReadRowset();

This uses your file as input and outputs a rowset collection, mapping the data to records based on how you defined your FileLayout. The result is fed into the ImportSegment function:

ImportSegment(&RS1, &RS); 
Function ImportSegment(&RS2 As Rowset, &RSParent As Rowset)

&RS2 in the function is a reference to &RS1 in the rest of your code. The table name is also hidden here:

&RecordName = "RECORD." | &RS2.DBRecordName;

So if you can't/don't want to check the FileLayout, you could output &RS2.DBRecordName with a messagebox and your answer will be Message Log of your Process Monitor.

Finally a record object is created for this database table and it is filled with a row from the rowset. This record is inserted into the database table:

&REC2 = CreateRecord(@(&RecordName));
&RS2(&I).GetRecord(1).CopyFieldsTo(&REC2);
&SQL1 = CreateSQL("%Insert(:1)");
&SQL1.Execute(&REC2); 

TLDR:

Table name can be found in the FileLayout or output in the ImportSegment Function as &RS2.DBRecordName