title: Perform logic if last row of SQL Fetch/Loop doesn't find match
body: <p>I have the below Application Engine program that I am modifying. I am basically adding in code for cases when the Unit of Measure (&amp;UOMResult) of an item is entered in a CSV/File Layout does not match the value of the existing row(s) retrieved for that same item, then I perform a REC.INSERT, otherwise if there is not a match found then I go through a different REC1.INSERT logic.</p>
  
  <p>My understanding is that the SQL Fetch function (&amp;SQL2.Fetch) only can fetch and store one row of data at a time into a variable (Please correct me if I am wrong on that) and therefore I have to include a While looping statement to continue getting all rows, one at a time (If there is an alternative to &amp;SQL2.Fetch that can process multiple rows in the same iteration please let me know). Based on this I added in code to loop through the &amp;SQL2.Fetch and if there is a matching row (to the variable &amp;UOM) then I do a REC.INSERT processing and then Break out of the loop, otherwise it continues looping (Used Continue function- don't know if it's necessary) through to compare the next row(s) until it either finds a match and does the break or - (what I intend for it to do) go into REC2.Insert logic. I don't want it to go to Continue if it has processed the last row.</p>
  
  <p>The way it's coded now I am afraid it will be an infinite loop in cases where there is never a match. I would like it to be that after the entire looping iterations have completed (last row has been read) and there isn't a match then go to REC2.Insert logic. Is there a way for the code to know that the last row has been looped through, if so how can I change it accordingly?</p>
  
  <pre><code>Local SQL &amp;SQL;
  Local string &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;UOMResult, &amp;ConvRate, &amp;PriceVndr, &amp;Oprid, &amp;PriceStatus, &amp;VendorId_check, &amp;insert_cols, &amp;insert_select, &amp;insert_where, &amp;insert_sql, &amp;Error, &amp;MaxEffdt, &amp;ItmField;
  Local date &amp;Effdt;
  Local Record &amp;ITM_VNDR_UOM_PR, &amp;REC, &amp;MASTER_ITEM_TBL;
  
  &amp;Oprid = "'BATCH'";
  &amp;PriceStatus = "2";
  &amp;Error = "N";
  
  MessageBox(0, "", 0, 0, "**** BEGINING OF VALIDATION ERRORS ****");
  
  &amp;SQL = CreateSQL("SELECT VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR, UNIT_OF_MEASURE, CONVERSION_RATE, PRICE_VNDR, %DateOut(EFFDT), ITEM_FIELD_C10_B FROM PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Effdt, &amp;ItmField);
  MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField | " " | &amp;Error);
  While &amp;SQL.Fetch(&amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Effdt, &amp;ItmField)
  
     SQLExec("SELECT A.VENDOR_ID FROM PS_ITM_VENDOR A, PS_ITM_VNDR_UOM B WHERE A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.VENDOR_SETID = B.VENDOR_SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VENDOR_ID = :1 AND A.INV_ITEM_ID = :2 AND A.ITM_ID_VNDR = :3 AND A.ITM_VNDR_PRIORITY = 1 AND B.UNIT_OF_MEASURE = :4 AND B.CONVERSION_RATE = :5", &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;VendorId_check);
  
     If None(&amp;VendorId_check) Then;
        &amp;Error = "Y";
        MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField | " " | &amp;Error);
       &amp;CurrentDateTime = %Datetime;
       &amp;Oprid = %OperatorId;
       &amp;RECITEM2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.LAST_DTTM_UPDATE = (SELECT MAX(A_ED.LAST_DTTM_UPDATE) FROM PS_ITM_VNDR_UOM_PR A_ED WHERE C.SETID = A_ED.SETID AND C.INV_ITEM_ID = A_ED.INV_ITEM_ID AND C.VENDOR_SETID = A_ED.VENDOR_SETID AND C.VENDOR_ID = A_ED.VENDOR_ID AND C.VNDR_LOC = A_ED.VNDR_LOC AND C.CURRENCY_CD = A_ED.CURRENCY_CD AND C.QTY_MIN = A_ED.QTY_MIN ) ", &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt);
       &amp;SQL2 = CreateSQL("SELECT C.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN) ", &amp;InvItemId, &amp;VendorId, &amp;UOMResult);
        While &amp;SQL2.Fetch(&amp;UOMResult)
         If &amp;UOM = &amp;UOMResult Then;
         SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &amp;RECITEM2, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM2);
         /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM); */
         &amp;new_UNIT_OF_MEASURE = &amp;RECITEM2.UNIT_OF_MEASURE.Value;
         &amp;REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
         &amp;REC2.SETID.Value = &amp;RECITEM.SETID.Value;
         &amp;REC2.INV_ITEM_ID.Value = &amp;RECITEM2.INV_ITEM_ID.Value;
         &amp;REC2.VENDOR_SETID.Value = &amp;RECITEM2.VENDOR_SETID.Value;
         &amp;REC2.VENDOR_ID.Value = &amp;RECITEM2.VENDOR_ID.Value;
         &amp;REC2.VNDR_LOC.Value = &amp;RECITEM2.VNDR_LOC.Value;
         &amp;REC2.UNIT_OF_MEASURE.Value = &amp;RECITEM2.UNIT_OF_MEASURE.Value;
         &amp;REC2.CURRENCY_CD.Value = &amp;RECITEM2.CURRENCY_CD.Value;
         &amp;REC2.QTY_MIN.Value = &amp;RECITEM2.QTY_MIN.Value;
         &amp;REC2.EFFDT.Value = &amp;Effdt;
         &amp;REC2.EFF_STATUS.Value = &amp;RECITEM.EFF_STATUS.Value;
         &amp;REC2.PRICE_VNDR.Value = &amp;PriceVndr;
         &amp;REC2.UNIT_PRC_TOL.Value = &amp;RECITEM.UNIT_PRC_TOL.Value;
         &amp;REC2.EXT_PRC_TOL.Value = &amp;RECITEM.EXT_PRC_TOL.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL.Value;
         &amp;REC2.PCT_EXT_PRC_TOL.Value = &amp;RECITEM.PCT_EXT_PRC_TOL.Value;
         &amp;REC2.USE_STD_TOLERANCES.Value = &amp;RECITEM.USE_STD_TOLERANCES.Value;
         &amp;REC2.QTY_RECV_TOL_PCT.Value = &amp;RECITEM.QTY_RECV_TOL_PCT.Value;
         &amp;REC2.UNIT_PRC_TOL_L.Value = &amp;RECITEM.UNIT_PRC_TOL_L.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL_L.Value;
         &amp;REC2.EXT_PRC_TOL_L.Value = &amp;RECITEM.EXT_PRC_TOL_L.Value;
         &amp;REC2.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM.PCT_EXT_PRC_TOL_L.Value;
         &amp;REC2.BU_PRICE_STATUS.Value = "2";
         &amp;REC2.STD_PRICE_STATUS.Value = "2";
         &amp;REC2.LEAD_TIME.Value = &amp;RECITEM.LEAD_TIME.Value;
         &amp;REC2.OPRID_MODIFIED_BY.Value = &amp;Oprid;
         &amp;REC2.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
         &amp;REC2.PRICE_CHANGE.Value = &amp;RECITEM.PRICE_CHANGE.Value;
         &amp;REC2.Insert();
         Break;
         End-While;
  
  
         Else;
         Continue;    /* Unless last row has been processed in While - NEED HELP HERE */
  
  
        End-If;  /* End-If here or below next code block NEED HELP HERE ?? */
  
         /* Final Else - If all rows have been looped through and there was not a match found then do the following - NEED HELP HERE */
  
         SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &amp;RECITEM3, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM3);
         &amp;new_UNIT_OF_MEASURE = &amp;RECITEM2.UNIT_OF_MEASURE.Value;
         &amp;REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
         &amp;REC2.SETID.Value = &amp;RECITEM3.SETID.Value;
         &amp;REC2.INV_ITEM_ID.Value = &amp;RECITEM3.INV_ITEM_ID.Value;
         &amp;REC2.VENDOR_SETID.Value = &amp;RECITEM3.VENDOR_SETID.Value;
         &amp;REC2.VENDOR_ID.Value = &amp;RECITEM3.VENDOR_ID.Value;
         &amp;REC2.VNDR_LOC.Value = &amp;RECITEM3.VNDR_LOC.Value;
         &amp;REC2.UNIT_OF_MEASURE.Value = &amp;UOMResult
         &amp;REC2.CURRENCY_CD.Value = &amp;RECITEM3.CURRENCY_CD.Value;
         &amp;REC2.QTY_MIN.Value = &amp;RECITEM3.QTY_MIN.Value;
         &amp;REC2.EFFDT.Value = &amp;Effdt;
         &amp;REC2.EFF_STATUS.Value = &amp;RECITEM3.EFF_STATUS.Value;
         &amp;REC2.PRICE_VNDR.Value = &amp;PriceVndr;
         &amp;REC.UNIT_PRC_TOL.Value = &amp;RECITEM3.UNIT_PRC_TOL.Value;
         &amp;REC2.EXT_PRC_TOL.Value = &amp;RECITEM3.EXT_PRC_TOL.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM3.PCT_UNIT_PRC_TOL.Value;
         &amp;REC2.PCT_EXT_PRC_TOL.Value = &amp;RECITEM3.PCT_EXT_PRC_TOL.Value;
         &amp;REC2.USE_STD_TOLERANCES.Value = &amp;RECITEM3.USE_STD_TOLERANCES.Value;
         &amp;REC2.QTY_RECV_TOL_PCT.Value = &amp;RECITEM3.QTY_RECV_TOL_PCT.Value;
         &amp;REC2.UNIT_PRC_TOL_L.Value = &amp;RECITEM3.UNIT_PRC_TOL_L.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM3.PCT_UNIT_PRC_TOL_L.Value;
         &amp;REC2.EXT_PRC_TOL_L.Value = &amp;RECITEM3.EXT_PRC_TOL_L.Value;
         &amp;REC2.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM3.PCT_EXT_PRC_TOL_L.Value;
         &amp;REC2.BU_PRICE_STATUS.Value = "2";
         &amp;REC2.STD_PRICE_STATUS.Value = "2";
         &amp;REC2.LEAD_TIME.Value = &amp;RECITEM3.LEAD_TIME.Value;
         &amp;REC2.OPRID_MODIFIED_BY.Value = &amp;Oprid;
         &amp;REC2.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
         &amp;REC2.PRICE_CHANGE.Value = &amp;RECITEM3.PRICE_CHANGE.Value;
         &amp;REC2.Insert();
       End-If;  
      End-While;
     Else;
        &amp;CurrentDateTime = %Datetime;
        &amp;Oprid = %OperatorId;
        &amp;RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);
  
        /*GETTING MAX EFFECTIVE DATED ROW TO COPY EXISTING ROW VALUES FROM INTO NEW ROW */
        SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.UNIT_OF_MEASURE = :3 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN)  ", &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt);
  
        SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM);
        /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM); */
        &amp;new_UNIT_OF_MEASURE = &amp;RECITEM.UNIT_OF_MEASURE.Value;
        &amp;REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
        &amp;REC.SETID.Value = &amp;RECITEM.SETID.Value;
        &amp;REC.INV_ITEM_ID.Value = &amp;RECITEM.INV_ITEM_ID.Value;
        &amp;REC.VENDOR_SETID.Value = &amp;RECITEM.VENDOR_SETID.Value;
        &amp;REC.VENDOR_ID.Value = &amp;RECITEM.VENDOR_ID.Value;
        &amp;REC.VNDR_LOC.Value = &amp;RECITEM.VNDR_LOC.Value;
        &amp;REC.UNIT_OF_MEASURE.Value = &amp;RECITEM.UNIT_OF_MEASURE.Value;
        &amp;REC.CURRENCY_CD.Value = &amp;RECITEM.CURRENCY_CD.Value;
        &amp;REC.QTY_MIN.Value = &amp;RECITEM.QTY_MIN.Value;
        &amp;REC.EFFDT.Value = &amp;Effdt;
        &amp;REC.EFF_STATUS.Value = &amp;RECITEM.EFF_STATUS.Value;
        &amp;REC.PRICE_VNDR.Value = &amp;PriceVndr;
        &amp;REC.UNIT_PRC_TOL.Value = &amp;RECITEM.UNIT_PRC_TOL.Value;
        &amp;REC.EXT_PRC_TOL.Value = &amp;RECITEM.EXT_PRC_TOL.Value;
        &amp;REC.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL.Value;
        &amp;REC.PCT_EXT_PRC_TOL.Value = &amp;RECITEM.PCT_EXT_PRC_TOL.Value;
        &amp;REC.USE_STD_TOLERANCES.Value = &amp;RECITEM.USE_STD_TOLERANCES.Value;
        &amp;REC.QTY_RECV_TOL_PCT.Value = &amp;RECITEM.QTY_RECV_TOL_PCT.Value;
        &amp;REC.UNIT_PRC_TOL_L.Value = &amp;RECITEM.UNIT_PRC_TOL_L.Value;
        &amp;REC.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL_L.Value;
        &amp;REC.EXT_PRC_TOL_L.Value = &amp;RECITEM.EXT_PRC_TOL_L.Value;
        &amp;REC.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM.PCT_EXT_PRC_TOL_L.Value;
        &amp;REC.BU_PRICE_STATUS.Value = "2";
        &amp;REC.STD_PRICE_STATUS.Value = "2";
        &amp;REC.LEAD_TIME.Value = &amp;RECITEM.LEAD_TIME.Value;
        &amp;REC.OPRID_MODIFIED_BY.Value = &amp;Oprid;
        &amp;REC.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
        &amp;REC.PRICE_CHANGE.Value = &amp;RECITEM.PRICE_CHANGE.Value;
        &amp;REC.Insert();
  
     End-If;
  
       If None(&amp;ItmField) Then;
        &amp;Error = "Y";
        MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField);
     Else;
        SQLExec("UPDATE PS_MASTER_ITEM_TBL SET ITEM_FIELD_C10_B = :1 WHERE INV_ITEM_ID = :2 AND SETID = 'SHARE' ", &amp;ItmField, &amp;InvItemId);
        /* Else;
         &amp;REC1 = CreateRecord(Record.MASTER_ITEM_TBL);
         &amp;REC1.ITEM_FIELD_C10_B.Value = &amp;ItmField;
         &amp;REC1.SETID.Value = "SHARE";
         &amp;REC1.INV_ITEM_ID.Value = &amp;InvItemId;
         &amp;REC1.Update();
      End-If;  */
  
     End-If;
  
  End-While;
  MessageBox(0, "", 0, 0, "**** END OF VALIDATION ERRORS ****");
  MessageBox(0, "", 0, 0, " ");
  </code></pre>
  
  <p>You will see references to the &amp;SQL2 and While statements in the code above, along with my 'NEED HELP HERE' comments with how to deal with after comparing the last row. Any suggestions with overall program flow are also welcome. Thank you!</p>
  
  <p><strong>10/23/18 Edit:</strong></p>
  
  <pre><code>Local SQL &amp;SQL;
  Local string &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;UOMResult, &amp;ConvRate, &amp;PriceVndr, &amp;Oprid, &amp;PriceStatus, &amp;VendorId_check, &amp;insert_cols, &amp;insert_select, &amp;insert_where, &amp;insert_sql, &amp;Error, &amp;MaxEffdt, &amp;ItmField;
  Local boolean &amp;RowFound;
  Local date &amp;Effdt;
  Local Record &amp;ITM_VNDR_UOM_PR, &amp;REC, &amp;MASTER_ITEM_TBL;
  
  &amp;Oprid = "'BATCH'";
  &amp;PriceStatus = "2";
  &amp;Error = "N";
  
  MessageBox(0, "", 0, 0, "**** BEGINING OF VALIDATION ERRORS ****");
  
  &amp;SQL = CreateSQL("SELECT VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR, UNIT_OF_MEASURE, CONVERSION_RATE, PRICE_VNDR, %DateOut(EFFDT), ITEM_FIELD_C10_B FROM PS_GH_ITM_PR_UPDT ORDER BY VENDOR_ID, INV_ITEM_ID, ITM_ID_VNDR", &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Effdt, &amp;ItmField);
  MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField | " " | &amp;Error);
  While &amp;SQL.Fetch(&amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;PriceVndr, &amp;Effdt, &amp;ItmField)
  
     SQLExec("SELECT A.VENDOR_ID FROM PS_ITM_VENDOR A, PS_ITM_VNDR_UOM B WHERE A.SETID = B.SETID AND A.INV_ITEM_ID = B.INV_ITEM_ID AND A.VENDOR_SETID = B.VENDOR_SETID AND A.VENDOR_ID = B.VENDOR_ID AND A.VENDOR_ID = :1 AND A.INV_ITEM_ID = :2 AND A.ITM_ID_VNDR = :3 AND A.ITM_VNDR_PRIORITY = 1 AND B.UNIT_OF_MEASURE = :4 AND B.CONVERSION_RATE = :5", &amp;VendorId, &amp;InvItemId, &amp;ItmIdVndr, &amp;UOM, &amp;ConvRate, &amp;VendorId_check);
  
     If None(&amp;VendorId_check) Then;
       MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField | " " | &amp;Error);
       &amp;CurrentDateTime = %Datetime;
       &amp;Oprid = %OperatorId;
       &amp;RECITEM2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
       SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.LAST_DTTM_UPDATE = (SELECT MAX(A_ED.LAST_DTTM_UPDATE) FROM PS_ITM_VNDR_UOM_PR A_ED WHERE C.SETID = A_ED.SETID AND C.INV_ITEM_ID = A_ED.INV_ITEM_ID AND C.VENDOR_SETID = A_ED.VENDOR_SETID AND C.VENDOR_ID = A_ED.VENDOR_ID AND C.VNDR_LOC = A_ED.VNDR_LOC AND C.CURRENCY_CD = A_ED.CURRENCY_CD AND C.QTY_MIN = A_ED.QTY_MIN ) ", &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt);
       &amp;SQL2 = CreateSQL("SELECT C.UNIT_OF_MEASURE FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN) ", &amp;InvItemId, &amp;VendorId, &amp;UOMResult);
       &amp;RowFound = False;
       While &amp;SQL2.Fetch(&amp;UOMResult)
         If &amp;UOM = &amp;UOMResult Then;
         &amp;RowFound = True;
         SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &amp;RECITEM2, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM2);
         /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM); */
         &amp;new_UNIT_OF_MEASURE = &amp;RECITEM2.UNIT_OF_MEASURE.Value;
         &amp;REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
         &amp;REC2.SETID.Value = &amp;RECITEM.SETID.Value;
         &amp;REC2.INV_ITEM_ID.Value = &amp;RECITEM2.INV_ITEM_ID.Value;
         &amp;REC2.VENDOR_SETID.Value = &amp;RECITEM2.VENDOR_SETID.Value;
         &amp;REC2.VENDOR_ID.Value = &amp;RECITEM2.VENDOR_ID.Value;
         &amp;REC2.VNDR_LOC.Value = &amp;RECITEM2.VNDR_LOC.Value;
         &amp;REC2.UNIT_OF_MEASURE.Value = &amp;RECITEM2.UNIT_OF_MEASURE.Value;
         &amp;REC2.CURRENCY_CD.Value = &amp;RECITEM2.CURRENCY_CD.Value;
         &amp;REC2.QTY_MIN.Value = &amp;RECITEM2.QTY_MIN.Value;
         &amp;REC2.EFFDT.Value = &amp;Effdt;
         &amp;REC2.EFF_STATUS.Value = &amp;RECITEM.EFF_STATUS.Value;
         &amp;REC2.PRICE_VNDR.Value = &amp;PriceVndr;
         &amp;REC2.UNIT_PRC_TOL.Value = &amp;RECITEM.UNIT_PRC_TOL.Value;
         &amp;REC2.EXT_PRC_TOL.Value = &amp;RECITEM.EXT_PRC_TOL.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL.Value;
         &amp;REC2.PCT_EXT_PRC_TOL.Value = &amp;RECITEM.PCT_EXT_PRC_TOL.Value;
         &amp;REC2.USE_STD_TOLERANCES.Value = &amp;RECITEM.USE_STD_TOLERANCES.Value;
         &amp;REC2.QTY_RECV_TOL_PCT.Value = &amp;RECITEM.QTY_RECV_TOL_PCT.Value;
         &amp;REC2.UNIT_PRC_TOL_L.Value = &amp;RECITEM.UNIT_PRC_TOL_L.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL_L.Value;
         &amp;REC2.EXT_PRC_TOL_L.Value = &amp;RECITEM.EXT_PRC_TOL_L.Value;
         &amp;REC2.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM.PCT_EXT_PRC_TOL_L.Value;
         &amp;REC2.BU_PRICE_STATUS.Value = "2";
         &amp;REC2.STD_PRICE_STATUS.Value = "2";
         &amp;REC2.LEAD_TIME.Value = &amp;RECITEM.LEAD_TIME.Value;
         &amp;REC2.OPRID_MODIFIED_BY.Value = &amp;Oprid;
         &amp;REC2.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
         &amp;REC2.PRICE_CHANGE.Value = &amp;RECITEM.PRICE_CHANGE.Value;
         &amp;REC2.Insert();
         Break;
         End-If;  /*Kevin added this on 10/22 3:55PM */
        End-While;
  
         /* Else;  */
         /* Continue; */   
  
         /* Final Else - If all rows have been looped through and there was not a match found then do the following  */
        If Not &amp;RowFound Then;
         SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &amp;RECITEM3, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM3);
         &amp;REC3 = CreateRecord(Record.ITM_VNDR_UOM_PR);
         &amp;new_UNIT_OF_MEASURE = &amp;RECITEM2.UNIT_OF_MEASURE.Value;
         &amp;REC2 = CreateRecord(Record.ITM_VNDR_UOM_PR);
         &amp;REC2.SETID.Value = &amp;RECITEM3.SETID.Value;
         &amp;REC2.INV_ITEM_ID.Value = &amp;RECITEM3.INV_ITEM_ID.Value;
         &amp;REC2.VENDOR_SETID.Value = &amp;RECITEM3.VENDOR_SETID.Value;
         &amp;REC2.VENDOR_ID.Value = &amp;RECITEM3.VENDOR_ID.Value;
         &amp;REC2.VNDR_LOC.Value = &amp;RECITEM3.VNDR_LOC.Value;
         &amp;REC2.UNIT_OF_MEASURE.Value = &amp;UOMResult
         &amp;REC2.CURRENCY_CD.Value = &amp;RECITEM3.CURRENCY_CD.Value;
         &amp;REC2.QTY_MIN.Value = &amp;RECITEM3.QTY_MIN.Value;
         &amp;REC2.EFFDT.Value = &amp;Effdt;
         &amp;REC2.EFF_STATUS.Value = &amp;RECITEM3.EFF_STATUS.Value;
         &amp;REC2.PRICE_VNDR.Value = &amp;PriceVndr;
         &amp;REC.UNIT_PRC_TOL.Value = &amp;RECITEM3.UNIT_PRC_TOL.Value;
         &amp;REC2.EXT_PRC_TOL.Value = &amp;RECITEM3.EXT_PRC_TOL.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM3.PCT_UNIT_PRC_TOL.Value;
         &amp;REC2.PCT_EXT_PRC_TOL.Value = &amp;RECITEM3.PCT_EXT_PRC_TOL.Value;
         &amp;REC2.USE_STD_TOLERANCES.Value = &amp;RECITEM3.USE_STD_TOLERANCES.Value;
         &amp;REC2.QTY_RECV_TOL_PCT.Value = &amp;RECITEM3.QTY_RECV_TOL_PCT.Value;
         &amp;REC2.UNIT_PRC_TOL_L.Value = &amp;RECITEM3.UNIT_PRC_TOL_L.Value;
         &amp;REC2.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM3.PCT_UNIT_PRC_TOL_L.Value;
         &amp;REC2.EXT_PRC_TOL_L.Value = &amp;RECITEM3.EXT_PRC_TOL_L.Value;
         &amp;REC2.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM3.PCT_EXT_PRC_TOL_L.Value;
         &amp;REC2.BU_PRICE_STATUS.Value = "2";
         &amp;REC2.STD_PRICE_STATUS.Value = "2";
         &amp;REC2.LEAD_TIME.Value = &amp;RECITEM3.LEAD_TIME.Value;
         &amp;REC2.OPRID_MODIFIED_BY.Value = &amp;Oprid;
         &amp;REC2.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
         &amp;REC2.PRICE_CHANGE.Value = &amp;RECITEM3.PRICE_CHANGE.Value;
         &amp;REC2.Insert();
       End-If;
  
      /* End-While; */
     Else;
        &amp;CurrentDateTime = %Datetime;
        &amp;Oprid = %OperatorId;
        &amp;RECITEM = CreateRecord(Record.ITM_VNDR_UOM_PR);
  
        /*GETTING MAX EFFECTIVE DATED ROW TO COPY EXISTING ROW VALUES FROM INTO NEW ROW */
        SQLExec("SELECT C.EFFDT FROM PS_ITM_VNDR_UOM_PR C WHERE C.INV_ITEM_ID = :1 AND C.VENDOR_ID = :2 AND C.UNIT_OF_MEASURE = :3 AND C.EFFDT = (SELECT MAX(C1.EFFDT) FROM PS_ITM_VNDR_UOM_PR C1 WHERE C.SETID = C1.SETID AND C.INV_ITEM_ID = C1.INV_ITEM_ID AND C.VENDOR_SETID = C1.VENDOR_SETID AND C.VENDOR_ID = C1.VENDOR_ID AND C.VNDR_LOC = C1.VNDR_LOC AND C.UNIT_OF_MEASURE = C1.UNIT_OF_MEASURE AND C.CURRENCY_CD = C1.CURRENCY_CD AND C.QTY_MIN = C1.QTY_MIN)  ", &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt);
  
        SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND (CONVERT(CHAR(10),EFFDT,121)) = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM);
        /*SQLExec("%SelectAll(:1) WHERE INV_ITEM_ID = :2 AND VENDOR_ID = :3 AND UNIT_OF_MEASURE = :4 AND EFFDT = :5", &amp;RECITEM, &amp;InvItemId, &amp;VendorId, &amp;UOM, &amp;MaxEffdt, &amp;RECITEM); */
        &amp;new_UNIT_OF_MEASURE = &amp;RECITEM.UNIT_OF_MEASURE.Value;
        &amp;REC = CreateRecord(Record.ITM_VNDR_UOM_PR);
        &amp;REC.SETID.Value = &amp;RECITEM.SETID.Value;
        &amp;REC.INV_ITEM_ID.Value = &amp;RECITEM.INV_ITEM_ID.Value;
        &amp;REC.VENDOR_SETID.Value = &amp;RECITEM.VENDOR_SETID.Value;
        &amp;REC.VENDOR_ID.Value = &amp;RECITEM.VENDOR_ID.Value;
        &amp;REC.VNDR_LOC.Value = &amp;RECITEM.VNDR_LOC.Value;
        &amp;REC.UNIT_OF_MEASURE.Value = &amp;RECITEM.UNIT_OF_MEASURE.Value;
        &amp;REC.CURRENCY_CD.Value = &amp;RECITEM.CURRENCY_CD.Value;
        &amp;REC.QTY_MIN.Value = &amp;RECITEM.QTY_MIN.Value;
        &amp;REC.EFFDT.Value = &amp;Effdt;
        &amp;REC.EFF_STATUS.Value = &amp;RECITEM.EFF_STATUS.Value;
        &amp;REC.PRICE_VNDR.Value = &amp;PriceVndr;
        &amp;REC.UNIT_PRC_TOL.Value = &amp;RECITEM.UNIT_PRC_TOL.Value;
        &amp;REC.EXT_PRC_TOL.Value = &amp;RECITEM.EXT_PRC_TOL.Value;
        &amp;REC.PCT_UNIT_PRC_TOL.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL.Value;
        &amp;REC.PCT_EXT_PRC_TOL.Value = &amp;RECITEM.PCT_EXT_PRC_TOL.Value;
        &amp;REC.USE_STD_TOLERANCES.Value = &amp;RECITEM.USE_STD_TOLERANCES.Value;
        &amp;REC.QTY_RECV_TOL_PCT.Value = &amp;RECITEM.QTY_RECV_TOL_PCT.Value;
        &amp;REC.UNIT_PRC_TOL_L.Value = &amp;RECITEM.UNIT_PRC_TOL_L.Value;
        &amp;REC.PCT_UNIT_PRC_TOL_L.Value = &amp;RECITEM.PCT_UNIT_PRC_TOL_L.Value;
        &amp;REC.EXT_PRC_TOL_L.Value = &amp;RECITEM.EXT_PRC_TOL_L.Value;
        &amp;REC.PCT_EXT_PRC_TOL_L.Value = &amp;RECITEM.PCT_EXT_PRC_TOL_L.Value;
        &amp;REC.BU_PRICE_STATUS.Value = "2";
        &amp;REC.STD_PRICE_STATUS.Value = "2";
        &amp;REC.LEAD_TIME.Value = &amp;RECITEM.LEAD_TIME.Value;
        &amp;REC.OPRID_MODIFIED_BY.Value = &amp;Oprid;
        &amp;REC.LAST_DTTM_UPDATE.Value = &amp;CurrentDateTime;
        &amp;REC.PRICE_CHANGE.Value = &amp;RECITEM.PRICE_CHANGE.Value;
        &amp;REC.Insert();
  
        /*MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt);*/
  
     End-If;
  
     /* 10/11/2018 - Added field ITEM_FIELD_C10_B (&amp;ItmField) to CSV file layout and insert into PS_MASTER_ITEM_TBL (&amp;MASTER_ITEM_TBL) */
     If None(&amp;ItmField) Then;
        &amp;Error = "Y";
        MessageBox(0, "", 0, 0, &amp;VendorId | " " | &amp;InvItemId | " " | &amp;ItmIdVndr | " " | &amp;UOM | " " | &amp;ConvRate | " " | &amp;PriceVndr | " " | &amp;Effdt | " " | &amp;ItmField);
     Else;
        SQLExec("UPDATE PS_MASTER_ITEM_TBL SET ITEM_FIELD_C10_B = :1 WHERE INV_ITEM_ID = :2 AND SETID = 'SHARE' ", &amp;ItmField, &amp;InvItemId);
        /* Else;
         &amp;REC1 = CreateRecord(Record.MASTER_ITEM_TBL);
         &amp;REC1.ITEM_FIELD_C10_B.Value = &amp;ItmField;
         &amp;REC1.SETID.Value = "SHARE";
         &amp;REC1.INV_ITEM_ID.Value = &amp;InvItemId;
         &amp;REC1.Update();
      End-If;  */
  
     End-If;
  
  End-While;
  MessageBox(0, "", 0, 0, "**** END OF VALIDATION ERRORS ****");
  MessageBox(0, "", 0, 0, " ");
  </code></pre>