PeopleSoft - Create Query in PeopleCode

import PT_MCF_MAIL:*;
import PSXP_XMLGEN:*;
import PSXP_ENGINE:*;

Declare Function LogToFile PeopleCode WR_MS_DERIVED.WR_LOG_TO_FILE FieldFormula;

Local boolean &CreateLogFile;

/** Email Results Function  ***/
Function EmailResults(&s_Email_Subject As string, &s_Email_Message As string, &s_Email_Recipients As string, &s_Email_From_Address As string, &s_Output_Attach_FileName As string);
  
  
   Local PT_MCF_MAIL:MCFOutboundEmail &email = create PT_MCF_MAIL:MCFOutboundEmail();

   &email.Priority = 1;
   &email.From = &s_Email_From_Address;
   &email.Recipients = &s_Email_Recipients;
   &email.Subject = &s_Email_Subject;
  
  
   Local PT_MCF_MAIL:MCFBodyPart &text = create PT_MCF_MAIL:MCFBodyPart();
   &text.Text = &s_Email_Message;
   &text.ContentType = "text/html;charset=utf8";
  
   Local PT_MCF_MAIL:MCFBodyPart &attachFile = create PT_MCF_MAIL:MCFBodyPart();
   &attachFile.SetAttachmentContent(&s_Output_Attach_FileName, %FilePath_Absolute, &s_Output_Attach_FileName, " ", "", "");
  
  
   Local PT_MCF_MAIL:MCFMultipart &mp = create PT_MCF_MAIL:MCFMultipart();
   &mp.AddBodyPart(&text);
  
   /** Check for an attachment  **/
   If All(&s_Output_Attach_FileName) Then
      &mp.AddBodyPart(&attachFile);
   End-If;
  
   /** Assign the Multipart to the email object **/
   &email.MultiPart = ∓
  
   /** Send the email  **/
   Local integer &res = &email.Send();
   Local boolean &done;
  
   Evaluate &res
   When %ObEmail_Delivered
      /* every thing ok */
      &done = True;
      Break;
     
   When %ObEmail_NotDelivered
      /*-- Check &email.InvalidAddresses, &email.ValidSentAddresses
and &email.ValidUnsentAddresses */
      &done = False;
      Break;
     
   When %ObEmail_PartiallyDelivered
      /* Check &email.InvalidAddresses, &email.ValidSentAddresses
and &email.ValidUnsentAddresses; */
      &done = True;
      Break;
     
   When %ObEmail_FailedBeforeSending
      /* Get the Message Set Number, message number;
Or just get the formatted messages from &email.ErrorDescription,
&email.ErrorDetails;*/
     
      &done = False;
      Break;
   End-Evaluate;
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, " ");
      LogToFile(&LogFileName, "******************* Finished Emailing ******************************* ");
      LogToFile(&LogFileName, " ");
   End-If;
  
End-Function;


/*=======================================================================
App Engine Program:    Get Email Recipients based on Distribution List
Description:          returns email address of distribution list passed
======================================================================== */
Function Get_Email_Recipients(&s_Distribution_List_ID) Returns string;
  
   Local number &e_count;
  
   /*  get email addresses */
   &oSQL_Obj = CreateSQL("Select WR_SETTING_VALUE from PS_WR_SETTING WHERE WR_GRP_SETTING_ID='" | &s_Distribution_List_ID | "'");
  
   While &oSQL_Obj.Fetch(&EmailAddress_ID)
      &e_count = &e_count + 1;
     
      SQLExec("Select WR_SETTING_VALUE from PS_WR_SETTING WHERE WR_GRP_SETTING_ID='EMAIL_ADDRESSES' AND WR_SETTING_ID=:1", &EmailAddress_ID, &EmailAddress);
     
      If &e_count > 1 Then
         &s_Email_List = &s_Email_List | "," | &EmailAddress;
      Else
         &s_Email_List = &EmailAddress;
      End-If;
     
   End-While;
  
   &oSQL_Obj.Close();
  
   Return &s_Email_List
End-Function;


/*=======================================================================
App Engine Program:   Contracts Expired / Expiring Notification
Description:         
======================================================================== */

/*  ^^^^^^^^^^^^ beginning of job ^^^^^^^^^^^^ beginning of job ^^^^^^^^^^^^ beginning of job ^^^^^^^^^^^^ */
&CreateLogFile = True;

If &CreateLogFile Then
   &LogFileName = "Notify_Contract_Expire." | DateTimeToLocalizedString(%Datetime, "MMddyyHHmm") | ".log";
   LogToFile(&LogFileName, "******************* BEGIN JOB ******************************* ");
End-If;

&ErrorSaveCount = 0;

/*** Retrieve Expired and Expiring Contracts  ***/
&s_SQL_ALL_CONTRACTS = GetSQL(SQL.WR_GET_ALL_EXPIRE_CONTRACTS);

/** Loop through All Expired / Expiring contracts ***/
While &s_SQL_ALL_CONTRACTS.Fetch(&s_main_REG_REGION);
  
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, "******************* Starting Loop for Reg Region ........ ******************************* ");
      LogToFile(&LogFileName, "Region: " | &s_main_REG_REGION);
   End-If;
  
  
   &s_Email_Table_Rows = "";
   &s_Email_Expired_Rows = "";
  
   /*** Set General Email Config here  ***/
   &sEmailSubjectLine = "Expired Contracts / Contracts Expiring Within 6 Months";
   &s_Email_Intro_Text = "The following email message has been sent from the PeopleSoft System";
  
   &s_Email_Header = "<html><head><title>" | &s_Email_Title | "</title></head><body><style type=""text/css"">     .wr_col_hdr {height:25px; font-family: arial; font-weight:normal; color:#666; font-size:12px; border:1px solid #999; background-color:#efefef; padding-left:3px; white-space:nowrap;} .wr_col_center_hdr {height:25px; font-family: arial; font-weight:normal; color:#666; font-size:12px; border:1px solid #999; background-color:#efefef; padding-left:3px; white-space:nowrap; text-align:center;}    .wr_col_text {font-family: arial; font-weight:normal; color:#333; font-size:12px; border:1px solid #999; border-width:0 0px 1px 0px; padding-left:3px; background-color:#fff;} .wr_col_center_text {font-family: arial; font-weight:normal; color:#333; font-size:12px; border:1px solid #999; border-width:0 0px 1px 0px; padding-left:3px; background-color:#fff; text-align:center;} </style> ";
  
   &s_Email_Table_Wrap = "<table style=""width:900px; background-color:#fff;""> <tr> <td style=""font-family:Arial; font-size:12px; font-weight:bold;"">" | &s_Email_Intro_Text | " <br /></td></tr><tr>";
  
   /*******************************************************************************
    CONTRACTS ALREADY EXPIRED!!!  -- 
    ********************************************************************************/
   /*** Get Expired Contracts  and put them in a separate table for the email ***/
   &s_Employee_Expired_Table_Label = "Expired Contracts as of " | DateTimeToLocalizedString(%Datetime, "M/d/y h:mm a");
  
   &s_Email_Expired_Table_Begin = "<table style=""width:100%; background-color:#fff;""><tr><td colspan=""8"" style=""background-color:#336699; font-weight:bold; font-family: Arial; color:#fff; font-size:12px; border:1px solid #003399; border-width:1px 1px 1px 1px; height:25px;  "">&nbsp;" | &s_Employee_Expired_Table_Label | "</td> </tr><tr> <td class=""wr_col_hdr"" style=""width:50px;"">EmplID&nbsp;</td> <td class=""wr_col_hdr"" style=""width:120px;"">Name&nbsp;</td> <td class=""wr_col_hdr"" style=""width:100px;"">Contract #</td> <td class=""wr_col_center_hdr"" style=""width:100px;"">Contract Begin</td> <td class=""wr_col_center_hdr"" style=""width:100px;"">Contract End&nbsp;</td> <td class=""wr_col_hdr"" style=""width:150px;"">Division</td> <td class=""wr_col_hdr"" style=""width:150px;"">Department</td> <td class=""wr_col_hdr"" style=""width:150px;"">Location</td></tr>";
  
   &s_Email_Expired_Table_End = "</table>";
  
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, "******************* Get Expired Contracts SQL  ******************************* ");
      LogToFile(&LogFileName, "SQL: WR_GET_EXPIRED_CONTRACTS: " | &s_main_REG_REGION);
   End-If;
  
   /** Grab SQL to Pull the data for expired contracts  **/
   &sSQL_EXPIRED_CONTRACTS = GetSQL(SQL.WR_GET_EXPIRED_CONTRACTS, &s_main_REG_REGION);
   If &CreateLogFile Then
      LogToFile(&LogFileName, "GetSQL Executed.....");
   End-If;
   /*** Loop through the expired contracts and add to the html string  **/
   While &sSQL_EXPIRED_CONTRACTS.Fetch(&s_EMPLID, &s_NAME, &s_CONTRACT_NUM, &s_CONTRACT_BEGIN_DT, &s_CONTRACT_END_DT, &s_Company, &Division_Descr, &DEPT_DESCR, &LOCATION_DESCR, &POSITION_DESCR);
     
      &s_Email_Expired_Rows = &s_Email_Expired_Rows | "<tr><td class=""wr_col_text"">" | &s_EMPLID | " &nbsp;</td><td class=""wr_col_text"">" | &s_NAME | " &nbsp;</td> <td class=""wr_col_text"">" | &s_CONTRACT_NUM | " &nbsp;</td> <td class=""wr_col_center_text"">" | DateTimeToLocalizedString(&s_CONTRACT_BEGIN_DT, "M/d/y") | " &nbsp;</td><td class=""wr_col_center_text"">" | DateTimeToLocalizedString(&s_CONTRACT_END_DT, "M/d/y") | " &nbsp;</td> <td class=""wr_col_text"">" | &Division_Descr | " &nbsp;</td> <td class=""wr_col_text"">" | &DEPT_DESCR | " &nbsp;</td> <td class=""wr_col_text"">" | &LOCATION_DESCR | " &nbsp;</td></tr>";
     
   End-While;
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, "Completed expired contracts loop.....");
      LogToFile(&LogFileName, " ");
   End-If;
  
   &sSQL_EXPIRED_CONTRACTS.Close();
  
   If None(&s_Email_Expired_Rows) Then
      &s_Employee_Expired_Table_Label = "No Contracts have expired as of " | DateTimeToLocalizedString(%Datetime, "M/d/y h:mm a");
      &s_Email_Expired_Rows = "<tr><td class=""wr_col_text"">There are no contracts that have expired. &nbsp;</td></tr>";
   End-If;
  
   /** Put together the HTML for Expired Contracts  **/
   &s_Email_Expired_HTML = &s_Email_Expired_Table_Begin | &s_Email_Expired_Rows | &s_Email_Expired_Table_End;
  
  
   /*******************************************************************************
    CONTRACTS EXPIRING WITHIN THE NEXT 6 MONTHS
    ********************************************************************************/
   /*** Get Expired Contracts  and put them in a separate table for the email ***/
   &s_Employee_Table_Label = "Contracts Expiring Within 6 Months";
  
   &s_Email_Table = "<br /><br /><table width=""100%""><tr><td colspan=""8"" style=""background-color:#336699; font-weight:bold; font-family: Arial; color:#fff; font-size:12px; border:1px solid #003399; border-width:1px 1px 1px 1px; height:25px;  "">&nbsp;" | &s_Employee_Table_Label | "</td> </tr><tr> <td class=""wr_col_hdr"" style=""width:50px;"">EmplID&nbsp;</td> <td class=""wr_col_hdr"" style=""width:120px;"">Name&nbsp;</td> <td class=""wr_col_hdr"" style=""width:100px;"">Contract #</td> <td class=""wr_col_center_hdr"" style=""width:100px;"">Contract Begin</td> <td class=""wr_col_center_hdr"" style=""width:100px;"">Contract End&nbsp;</td> <td class=""wr_col_hdr"" style=""width:150px;"">Division</td> <td class=""wr_col_hdr"" style=""width:150px;"">Department</td> <td class=""wr_col_hdr"" style=""width:150px;"">Location</td></tr> ";
  
   &s_Email_Table_End = "</table>";
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, "******************* Get Contracts Expiring in 6 months SQL  ******************************* ");
      LogToFile(&LogFileName, "SQL: WR_GET_EXPIRING_CONTRACTS: " | &s_main_REG_REGION);
   End-If;
  
   /*** Grab SQL to Pull the data to populate the email  ***/
   &sSQL_EXPIRING_CONTRACTS = GetSQL(SQL.WR_GET_EXPIRING_CONTRACTS, &s_main_REG_REGION);
  
   While &sSQL_EXPIRING_CONTRACTS.Fetch(&s_EMPLID, &s_NAME, &s_CONTRACT_NUM, &s_CONTRACT_BEGIN_DT, &s_CONTRACT_END_DT, &s_Company, &Division_Descr, &DEPT_DESCR, &LOCATION_DESCR, &POSITION_DESCR);
     
      &s_Email_Table_Rows = &s_Email_Table_Rows | "<tr><td class=""wr_col_text"">" | &s_EMPLID | " &nbsp;</td>  <td class=""wr_col_text"">" | &s_NAME | " &nbsp;</td> <td class=""wr_col_text"">" | &s_CONTRACT_NUM | " &nbsp;</td> <td class=""wr_col_center_text"">" | DateTimeToLocalizedString(&s_CONTRACT_BEGIN_DT, "M/d/y") | " &nbsp;</td><td class=""wr_col_center_text"">" | DateTimeToLocalizedString(&s_CONTRACT_END_DT, "M/d/y") | " &nbsp;</td> <td class=""wr_col_text"">" | &Division_Descr | " &nbsp;</td> <td class=""wr_col_text"">" | &DEPT_DESCR | " &nbsp;</td> <td class=""wr_col_text"">" | &LOCATION_DESCR | " &nbsp;</td> </tr>";
     
   End-While;
  
   &sSQL_EXPIRING_CONTRACTS.Close();
  
   If None(&s_Email_Table_Rows) Then
      &s_Employee_Table_Label = "No Contracts are set to expire within 6 months as of " | DateTimeToLocalizedString(%Datetime, "M/d/y h:mm a");
      &s_Email_Table_Rows = "<tr><td class=""wr_col_text"">There are no contracts that will expire within 6 months. &nbsp;</td></tr>";
   End-If;
  
  
   /** Build HTML for Contracts that will be expiring within the next 6 months  ***/
   &s_Email_HTML = &s_Email_Table | &s_Email_Table_Rows | &s_Email_Table_End;
  
  
  
   /** Would You like to include a copy of the report in a PDF file?? **/
   &IncludeFileInEmail = True;
  
   If &IncludeFileInEmail = True Then
     
      /*******************************************************************************
      QUERY SPECIFIC STUFF - Create on  da fly query
      ********************************************************************************/
      Local ApiObject &oQry;
      Local number &nResult;
      Local Rowset &aRowSet;
     
      &sQueryName = "CONTRACT_DATA_" | &s_main_REG_REGION;
     
      &oQry = %Session.GetQuery();
      /* create a new query : public type-User */
      &nResult = &oQry.Create(&sQueryName, True, 1, &s_main_REG_REGION | " Expired Contracts ", "This query was generated by peoplecode.");
     
     
      If &nResult = 0 Then
         /* Query created successfully */
         /*
         &oQry.Disabled = "";
         &oQry.Folder = "";
          */
         &oQrySelect = &oQry.AddQuerySelect();
        
         /* &oQrySelect.Distinct = True; */
         &oQryRec1 = &oQrySelect.AddQueryRecord("CONTRACT_DATA");
         &oQryRec2 = &oQrySelect.AddQueryRecord("WR_EMPL_FAST1");
         &oQryRec1.RecordAlias = "A";
         &oQryRec2.RecordAlias = "B";
        
         &oQryRecColl = &oQrySelect.QueryRecords;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec1.Name, &oQryRec1.RecordAlias, "EMPLID", "EmployeeID");
         &QryFld.ColumnNumber = 1;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec2.Name, &oQryRec2.RecordAlias, "NAME", "Name");
         &QryFld.ColumnNumber = 2;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec1.Name, &oQryRec1.RecordAlias, "CONTRACT_NUM", "Contract Status");
         &QryFld.ColumnNumber = 3;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec1.Name, &oQryRec1.RecordAlias, "CONTRACT_BEGIN_DT", "Begin Date");
         &QryFld.ColumnNumber = 4;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec1.Name, &oQryRec1.RecordAlias, "CONTRACT_END_DT", "End Date");
         &QryFld.ColumnNumber = 5;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec1.Name, &oQryRec1.RecordAlias, "CONTRCT_EXP_END_DT", "Expected End Date");
         &QryFld.ColumnNumber = 6;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec2.Name, &oQryRec2.RecordAlias, "WR_DIVISION_DESCR", "Division");
         &QryFld.ColumnNumber = 7;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec2.Name, &oQryRec2.RecordAlias, "DEPT_DESCR", "Department");
         &QryFld.ColumnNumber = 8;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         &QryFld = &oQrySelect.AddQuerySelectedField(&oQryRec2.Name, &oQryRec2.RecordAlias, "LOCATION_DESCR", "Location");
         &QryFld.ColumnNumber = 9;
         &QryFld.HeadingType = %Query_HdgRftShort;
        
         /** Add Record and fields to the select  **/
         /*  Use this if you want all fields 
      For &r = 1 To &oQryRecColl.Count;
      &oQryRec = &oQryRecColl.Item(&r);
     
      If &CreateLogFile Then
         LogToFile(&LogFileName, "******************* Retrieving Query Records ******************************* ");
         LogToFile(&LogFileName, "Recname: " | &oQryRec.Name);
      End-If;
     
      &oQryFieldColl = &oQryRec.QueryFields;
     
      For &i = 1 To &oQryFieldColl.Count
         &QryField = &oQryRec.GetField(&i);
        
         If &CreateLogFile Then
            LogToFile(&LogFileName, "******************* Retrieving Record Fields ******************************* ");
            LogToFile(&LogFileName, "Fieldname: " | &QryField.Name);
         End-If;
        
         &fld = &oQrySelect.AddQuerySelectedField(&oQryRec.Name, "A", &QryField.Name, "Field " | &i);
         &fld.ColumnNumber = 1;
         &fld.HeadingType = %Query_HdgRftShort;
        
      End-For;

    End-For;
    */
        
         /*** NOW ADD THE QUERY CRITERIA  ***/
         &oQryCriteria = &oQrySelect.AddCriteria("JoinCriteria");
         &oQryCriteria.Logical = %Query_CombNotUsed;
        
         &oQryCriteria.Expr1Type = %Query_ExprField;
         &oQryCriteria.AddExpr1Field("A", "EMPLID");
         &oQryCriteria.Operator = %Query_CondEqual;
         &oQryCriteria.Expr2Type = %Query_ExprField;
         &oQryCriteria.AddExpr2Field1("B", "EMPLID");
        
         /*** Contract Status Criteria, status = 'A'  ***/
         &Status_Criteria = "A";
         &oQryCriteria = &oQrySelect.AddCriteria("ContractStatus");
         &oQryCriteria.Logical = %Query_CombAnd;
         &oQryCriteria.Expr1Type = %Query_ExprField;
         &oQryCriteria.AddExpr1Field("A", "CONTRACT_STATUS");
         &oQryCriteria.Operator = %Query_CondEqual;
        
         &oQryCriteria.Expr2Type = %Query_ExprConstant;
         &oQryCritExp = &oQryCriteria.AddExpr2Expression();
         &oQryCritExp.Text = &Status_Criteria;
         &oQryCriteria.Expr2Expression1 = &oQryCritExp;
        
         /*** Company Criteria to build the query  ***/
         &oQryCriteria = &oQrySelect.AddCriteria("RegRegion");
         &oQryCriteria.Logical = %Query_CombAnd;
         &oQryCriteria.Expr1Type = %Query_ExprField;
         &oQryCriteria.AddExpr1Field("A", "REG_REGION");
         &oQryCriteria.Operator = %Query_CondEqual;
        
         &oQryCriteria.Expr2Type = %Query_ExprConstant;
         &oQryCritExp = &oQryCriteria.AddExpr2Expression();
         &oQryCritExp.Text = &s_main_REG_REGION;
         &oQryCriteria.Expr2Expression1 = &oQryCritExp;
        
         /*** Contract Expired Date Criteria  ***/
         &oQryCriteria = &oQrySelect.AddCriteria("ContrEndDt");
         &oQryCriteria.Logical = %Query_CombAnd;
         &oQryCriteria.Expr1Type = %Query_ExprField;
         &oQryCriteria.AddExpr1Field("A", "CONTRACT_END_DT");
         &oQryCriteria.Operator = %Query_CondNotGreaterThan;
        
         &oQryCriteria.Expr2Type = %Query_ExprCurDt;
         &oQryCritExp = &oQryCriteria.AddExpr2Expression();
         &oQryCritExp.Text = %Date;
         &oQryCriteria.Expr2Expression1 = &oQryCritExp;

         &oQryCriteria = &oQrySelect.AddCriteria("EmplStatus");
         &oQryCriteria.Logical = %Query_CombAnd;
         &oQryCriteria.Expr1Type = %Query_ExprField;
         &oQryCriteria.AddExpr1Field("B", "EMPL_STATUS");
         &oQryCriteria.Operator = %Query_CondInList;
        
         &oQryCriteria.Expr2Type = %Query_ExprList;
         &oCurrList = &oQryCriteria.AddExpr2List();
         &oCurrList.addListValue("A", False);
         &oCurrList.addListValue("L", False);
         &oCurrList.addListValue("P", False);
         &oCurrList.addListValue("S", False);
         /* &oQryCriteria.Expr2Expression1 = &oCurrList; */
        
         &oQrySQL = &oQry.SQL;
         &oQry.CreateOprId = %UserId;
         &oQry.LastUpdOprId = %UserId;
         &oQry.LastUpdDttm = %Datetime;
        
         &debugSQL = False;
        
         If &debugSQL = True Then
            &s_SQL_Table_Label = "SQL Executed " | DateTimeToLocalizedString(%Datetime, "M/d/y h:mm a");
           
            &s_SQL_Table_Begin = "<br><br><table style=""width:100%; background-color:#fff;""><tr><td colspan=""8"" style=""background-color:#336699; font-weight:bold; font-family: Arial; color:#fff; font-size:12px; border:1px solid #003399; border-width:1px 1px 1px 1px; height:25px;  "">&nbsp;" | &s_SQL_Table_Label | "</td> </tr><tr><td class=""wr_col_hdr"" style=""width:100%;"">SQL Statement&nbsp;</td></tr>";
            &s_SQL_Row = "<tr><td class=""wr_col_text"">" | &oQrySQL | " &nbsp;</td></tr>";
            &s_SQL_Table_End = "</table>";
           
            &s_SQL_HTML = &s_SQL_Table_Begin | &s_SQL_Row | &s_SQL_Table_End;
         Else
            &s_SQL_HTML = "";
         End-If;
        
         &saveQry = &oQry.Save();
         /* &oQry_Close = &oQry.Close(); */
        
         If &CreateLogFile Then
            LogToFile(&LogFileName, "******************* TRYING TO SAVE QUERY........ ******************************* ");
            LogToFile(&LogFileName, " ");
         End-If;
        
         If &saveQry = 0 Then
            /* Query Saved successfully */
            If &CreateLogFile Then
               LogToFile(&LogFileName, "******************* DONE!  QUERY SAVED! ******************************* ");
            End-If;
         Else
            If &CreateLogFile Then
               LogToFile(&LogFileName, "******************* QUERY CREATED but QUERY NOT SAVED! ******************************* ");
            End-If;
           
         End-If;
        
      Else
         If &CreateLogFile Then
            LogToFile(&LogFileName, "******************* ERROR OCCURED - QUERY NOT CREATED! ******************************* ");
         End-If;
      End-If;
     
     
      /** if query was saved to the database successfully then proceed to run the query and attach results **/
      If &saveQry = 0 Then
         /*** Run Query to File so We can send it with the email ***/
         &oQuery = %Session.GetQuery();
         &ret_oQuery = &oQuery.Open(&sQueryName, &bIsPublic, False);
        
         /* Did the Query Open??? If yes, then continue else move on  */
         If &ret_oQuery = 0 Then
           
            &sFileName = &s_main_REG_REGION | "_Expired_Contracts";
            &sFormat = "PDF";
            &sQryFormat = %Query_PDF;
           
            /** Execute Query to PDF Now  ***/
            &sOutputFileName = &sFileName | "." | DateTimeToLocalizedString(%Datetime, "MMddyyHHmm");
            &sOutputRunFileName = &sOutputFileName;
           
            &sOutputGetFilePath = &sOutputFileName | "." | &sFormat;
            &s_OutputAttachFileName = &sOutputFileName | "." | &sFormat;
           
            &oQryPromptRec = &oQuery.PromptRecord;
            &oRowSet = &oQuery.RunToFile(&oQryPromptRec, &sOutputFileName, &sQryFormat, 0);
           
         End-If;
        
         &oQuery_Close = &oQuery.Close();
        
      End-If;
     
     
     
   End-If; /** If IncludeFileInEmail  **/
  
   &s_Email_Footer = "</tr><tr style=""font-family:Arial; font-size:10px;""><td class=""wr_col_text""><br /><br />The information contained in this correspondence is confidential and intended for use of the individual or entity named above. Unauthorized distribution is prohibited. </td> </tr> </table> <br /> </body> </html>";
  
   &s_Email_Body = &s_Email_Header | &s_Email_Table_Wrap | &s_Email_Expired_HTML | &s_Email_HTML | &s_SQL_HTML | &s_Email_Footer;
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, "******************* Going to Email the Results Now! ******************************* ");
      LogToFile(&LogFileName, "Reg Region: " | &s_main_REG_REGION);
   End-If;
  
   Evaluate &s_main_REG_REGION
   When "CAN"
      /** Recipients Configuration from the SETUP HRMS settings table  ***/
      &s_Email_Config_DList = "EMAIL_WM_EXPIRED_CONTRACTS";
      &s_From_Address = "noreply@somewhere.com";
      Break;
     
   When "USA"
      /** Recipients Configuration from the SETUP HRMS settings table  ***/
      &s_Email_Config_DList = "EMAIL_WR_EXPIRED_CONTRACTS";
      &s_From_Address = "noreply@somewhere.com";
     
   End-Evaluate;
  
  
   /*** Get EMAIL Recipients  ***/
   &s_Email_Config_Recipients = Get_Email_Recipients(&s_Email_Config_DList);
  
   If None(&s_Email_Config_Recipients) Then
      &s_Email_Config_Recipients = "recipient@somewhere.com";
   End-If;
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, "******************* Email Recipients! ******************************* ");
      LogToFile(&LogFileName, "Email List: " | &s_Email_Config_Recipients);
   End-If;
  
   /** Send Email via App package  **/
   EmailResults(&sEmailSubjectLine, &s_Email_Body, &s_Email_Config_Recipients, &s_From_Address, &s_OutputAttachFileName);
  
   If &CreateLogFile Then
      LogToFile(&LogFileName, " ");
      LogToFile(&LogFileName, "******************* Next Row Please ******************************* ");
   End-If;
  
End-While;


If &CreateLogFile Then
   LogToFile(&LogFileName, "*****************************************************");
   LogToFile(&LogFileName, "Completed Job Successfully");
End-If;