ProgramID: 2402|Service Records

procedure ServiceInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
  const BatchDescription : string; nBatchTypeID : integer);
var
  DeleteFromDC( nBatch, EmptyList, lContinue);
  GetServiceCharges( nBatch, dStartDate, dEndDate, BatchDescription,
    nBatchTypeID, lContinue);
end;  // ServiceInvoice.


procedure DeleteFromDC(const nThisBatch:LongInt; const FieldValues: TStringList;
 const lSuccess: boolean);
    Message 'Flushing tDataCollection data...';
    DatabaseName := 'dbPPdata';
    'DELETE
    'FROM tDataCollection
    'WHERE BatchID = $nthisBatch
end // DeleteFromDC


procedure GetServiceCharges(
           const nBatchNumber : LongInt;
           const dStartDate, dEndDate : TDateTime;
           const BatchDescription : string;
           const nBatchTypeID : integer;
           var lReturnCode: Boolean );
var


  tblTemp.DatabaseName := 'dbPPdata';
  tblTemp.TableName := 'tDC_TempRJC_ExService';

  // Establish the query to be used to save the data read.
  qryServices.DatabaseName := 'dbPPdata';

  // Ensure the temporary table does not exist.
  if tblTemp.Exists then begin
    tblTemp.DeleteTable;
  end;
  lReturnCode := True;

  MessageForm.Caption := 'Creating Service Charges ...';
  MessageForm.Msg.Caption := 'Reading system variables ...';

  // Define transaction types.
  // qryServices 'dbPPdata';
  'SELECT ID
  'FROM tTransactionType     // Service Records Invoice.
  'WHERE Description LIKE '%Service Record%'
  'AND Active = 1
  if FOUND
    sTransactionID := 'id'
  end;

  // qryServices 'dbPPdata';
  qryServices.SQL.Add   // (Strings[1])
  'SELECT ID');
  'FROM tStatus');
  'AND Active = 1');                  //   In Service(=2),
  qryServices.Open;                   //   Ex Service(=4).
  // build a string with the comma separated list of  the FOUND tStatus.ID's
  sStatusID := '(';
  while not qryServices.Eof do begin
    if (Length(sStatusID) > 1) then sStatusID := sStatusID +',';
    sStatusID := sStatusID + 'id';
    qryServices.Next;
  end;
  sStatusID := sStatusID +')';

  // get ExServicveStausID
  // For amending charges later for "Ex Service" items...
  qryServices.SQL.Strings[2] :=
    'WHERE Status LIKE ''Ex Service''
  qryServices.Open;                              //   Ex Service(=4).
  if FOUNDthen begin
    sExServiceStatusID := 'id
  end;
  qryServices.Close;

  // get VRT tCustomer.ID
  // qryServices 'dbPPdata';
  'SELECT ID
  'FROM tCustomer
  'WHERE ShipTo LIKE ''VRT''
  'AND Active = 1
  if NOTFOUND
    sVRTID := '0'                                   // Define VRT CustomerID.
  else
    sVRTID := 'id'

  // get tSystem.ID for the passwd nBatchTypeID
  // qryServices 'dbPPdata';
  'SELECT ID
  'FROM tSystem
  'WHERE BatchTypeID = '+nBatchTypeID
  'AND Active = 1
  if NOTFOUND.Eof then
    sSystemID := '0'
  else
    sSystemID := 'ID').AsInteger);

  // This is the proc I previously sent
  // I will repeat a decoded version of this proc bellow for completeness
  // Define the maximum charges (ie rate) for ExService types by CustomerID.
  SetExServiceRates(sSystemID, sExServiceStatusID);

  // Save the invoicing data using a temporary file.
  MessageForm.Msg.Caption := 'Copying charges to a Temporary table ...';
  MainForm.ProcessWinMessages;
// Start temporary charges by tCustomer only in lieu of each service (2 of 4):
// These charges account for sStatus = sExServiceStatusID (=2).
// Start temporary charges by tCustomer only in lieu of each service (2 of 4):
   //set qryStringList to...
    'SELECT IDENTITY(int,1,1) AS "SequNo", ServiceID, AmountExGST, GSTFlag,
    ' RateDescription, BatchID, FromDate, ToDate, TransactionTypeID,
    ' GSTAmount, AmountIncGST
    'INTO tDC_TempRJC_ExService
    'FROM (SELECT CID2.*, CAST((CID2.AmountExGST + CID2.GSTAmount) AS money) '+
      'AS "AmountIncGST"
    'FROM (SELECT CID1.*, CAST((CASE WHEN CID1.GSTFlag LIKE ''Y'' '+
      'THEN (CID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"
    'FROM (SELECT (tCustomer.ShipTo + '' Services Pre July 1999'') '+
      'AS "ServiceID", (tCustomer.MaxExServiceAmount / 12) AS "AmountExGST",
    ' (CASE WHEN tCustomer.ShipTo LIKE ''VRT'' THEN ''N'' ELSE ''Y'' END) '+
      'AS "GSTFlag",
    ' ''Network Access'' AS "RateDescription", CAST('+sBatchID+' AS int) '+
      'AS "BatchID",
    ' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",
    ' CAST('+sTransactionID+' AS int) AS "TransactionTypeID"
    'FROM tCustomer
    'WHERE tCustomer.MaxExServiceAmount > 0
    'AND tCustomer.InvoiceServices = 1) AS CID1) AS CID2) AS CID
    //**********************************************************************
    //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' **
    //**********************************************************************
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qrystringList.Text;
  qryServices.ExecSQL;                 // Copies data into temp table.

  // count rows in temp table tDC_TempRJC_ExServicecreated above
  //set qryStringList to...
    'SELECT COUNT(*) AS "Max"
    FROM tDC_TempRJC_ExService
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.Open;
  if NOTFOUND
    sSeqNoStart := '1';
  else FOUND
    lNothingSaved := False;
    sSeqNoStart := Max 	// from select count()S above
    //set qryStringList to...
      'INSERT INTO tDataCollection');
      '(SequenceNo, BatchID, ServiceID, RateDescription,');
      ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        ' FromDate, ToDate, TransactionTypeID)');
      'SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+
        ' BatchID, ServiceID, RateDescription,');
        ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID');
      'FROM tDC_TempRJC_ExService');
    end;  // qryStringList.
    // qryServices 'dbPPdata';
    qryServices.SQL.Text := qryStringList.Text;
    qryServices.ExecSQL;
  end;


  // Import data into tDataCollection.
  // Again, Ensure the temporary table does not exist.
  if tblTemp.Exists then begin
    // qryServices 'dbPPdata';
    DROP TABLE tDC_TempRJC_ExService
    qryServices.ExecSQL;
  end;

  //set qryStringList to...
// End temporary charges by tCustomer only in lieu of each service (2 of 4).

// Start temporary charges by tCustomer only in lieu of each service (3 of 4).
//    'SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+
//      'RateDescription,
    'SELECT IDENTITY(int,'+sSeqNoStart+',1) AS "SequNo", BatchID, ServiceID, '+
      'RateDescription,
// End temporary charges by tCustomer only in lieu of each service (3 of 4).
    ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+
      'ToDate, TransactionTypeID,
    ' Var01, Var02, Var03,
    ' Var04, Var05, Var06, Var07, Var08, Var09, Var10,
    ' Var11, Var12, Var13, Var14, Var15
    'INTO tDC_TempRJC_ExService
    'FROM (SELECT SID2.*, '+
      'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"
    'FROM (SELECT SID1.*,
    ' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+
      '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"
    'FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+
      'CAST('+sBatchID+' AS int) AS "BatchID",
    ' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",
    ' CAST('+sTransactionID+' AS int) AS "TransactionTypeID",
    ' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+
      'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+
      'AS money) AS "AmountExGST",
    ' (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN '+
      '''N'' ELSE ''Y'' END) AS "GSTFlag",

// Changes per Dec 2008 RLD: change Var orders to use generic report & avoid new.
    'CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01",');
    'SI.Service AS "Var02",
    'tStatus.Status AS "Var03",
    'CONVERT(varchar(50), SI.ServiceCode) AS "Var04",
    'tApplication.Description AS "Var05",

    ' CAST(SID.Cost AS varchar) AS "Var06",
    ' CAST(SID.Cost100 AS varchar) AS "Var07", '+
      'CAST(SID.Invoiced AS varchar) AS "Var08",
    ' CAST(SID.ServiceLen AS varchar) AS "Var09",
    ' SI.UpMDF AS "Var10", '+
      'SI.DownMDF AS "Var11",
    ' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var12", '+
      'CAST(SI.StatusID AS varchar) AS "Var13", '+
      'SI.SACCnumber AS "Var14", '+
      'tServiceType.Code AS "Var15"
//{ Changes per Dec 2008 RLD: change Var orders to use generic report & avoid new.
//    ' CAST(SID.Cost AS varchar) AS "Var01",
//    Add(' CAST(SID.Cost100 AS varchar) AS "Var02", '+
//      'CAST(SID.Invoiced AS varchar) AS "Var03",
//    Add(' CAST(SID.ServiceLen AS varchar) AS "Var04",
//    Add(' SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
//    Add(' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", '+
//      'CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", '+
//      'SI.SACCnumber AS "Var10"
//}
// End changes per dec 2008.
    'FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+
      ' Max(tRate.Cost100) AS "Cost100",
    ' SI0.Length AS "ServiceLen", SI0.Invoiced
    'FROM (SELECT SC.ID, SC.Rate
    ' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+
      'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate"
    ' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,
    ' tCustomer.ID AS "CustomerID", (CASE
    '  WHEN ST.Frequency IS NULL THEN 1
    '  WHEN ST.Frequency = 0 THEN 1
    ' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+
      ' AS Real) / ST.Frequency) =
    '  ROUND((Month(tServiceID.InstallationDate)-'+sMonth+
      ') / ST.Frequency,0,0) THEN 0
    '  ELSE 1
    ' END) AS "Period"
    ' FROM tServiceID
    ' LEFT JOIN tServiceType AS ST '+
      'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))
    ' LEFT JOIN tCentre '+
      'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
    ' LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
    ' LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))
    ' LEFT JOIN tCustomer '+
      'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))
    ' WHERE ST.SystemID = '+sSystemID);
    ' AND tServiceID.StatusID IN '+sStatusID);
    ' AND tServiceID.Active = 1
    ' AND tCustomer.InvoiceServices = 1) AS SC1
    'LEFT JOIN tDiscountedRate '+
      'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND
    ' (SC1.CustomerID = tDiscountedRate.CustomerID) '+
      'AND (tDiscountedRate.Active = 1))
    'WHERE SC1.Period = 0) AS SC) AS SI1
    'INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID
    'LEFT JOIN tRate '+
      'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+
      'AND (SI0.Length >= tRate.Length))
    'GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID
    'INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
    'LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID
// Changes per Dec 2008 RLD: Next line added to change Var orders
//  and to use generic report & avoid creating a new report.
    'LEFT JOIN tApplication ON '+
      '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1))
// End this part of change.
    'LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
    ' LEFT JOIN tCentre '+
      'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
    ' LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
    ' LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1
    'WHERE SID1.AmountExGST IS NOT NULL) AS SID2) AS SIF
  end;  // qryStringList.
  //**********************************************************************
  //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' **
  //**********************************************************************
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.ExecSQL;                 // Copies data into temp table.


  //set qryStringList to...
    'SELECT BatchID
    'FROM tDC_TempRJC_ExService
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.Open;
  if FOUND
    lNothingSaved := False;
    //set qryStringList to...
      'INSERT INTO tDataCollection
      '(SequenceNo, BatchID, ServiceID, RateDescription,
      ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,
      ' Var01, Var02, Var03,
      ' Var04, Var05, Var06, Var07, Var08, Var09, Var10,
      ' Var11, Var12, Var13, Var14, Var15)
      'SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+
        'BatchID, ServiceID, RateDescription,
      ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,
      ' Var01, Var02, Var03,
      ' Var04, Var05, Var06, Var07, Var08, Var09, Var10,
      ' Var11, Var12, Var13, Var14, Var15
      'FROM tDC_TempRJC_ExService
    end;  // qryStringList.
    // qryServices 'dbPPdata';
    qryServices.SQL.Text := qryStringList.Text;
    qryServices.ExecSQL;
  end;  // if no data saved.
  MessageForm.Msg.Caption := 'Adding new services ...';

  if tblTemp.Exists then begin
    // qryServices 'dbPPdata';
    'DROP TABLE tDC_TempRJC_ExService
    qryServices.SQL.Text := qryStringList.Text;
    qryServices.ExecSQL;                // Drop the temporary table.
  end;



// *************************************************************************
// *************************************************************************
  // Now add any new services that have not yet been completed in tServiceID.
    //set qryStringList to...
// Start temporary charges by tCustomer only in lieu of each service (4 of 4).
//    'SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+
//      'RateDescription,
    'SELECT IDENTITY(int,'+sSeqNoStart+',1) AS "SequNo", BatchID, ServiceID, '+
      'RateDescription,
// End temporary charges by tCustomer only in lieu of each service (4 of 4).
// End temporary charges by tCustomer only in lieu of each service (4 of 4).
    ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+
      'ToDate, TransactionTypeID,
    ' Var01, Var02, Var03,
    ' Var04, Var05, Var06, Var07, Var08, Var09, Var10,
    ' Var11, Var12, Var13, Var14, Var15
    'INTO tDC_TempRJC_ExService
    'FROM (SELECT SID2.*, '+
      'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"
    'FROM (SELECT SID1.*,
    ' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+
      '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"
    'FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+
      'CAST('+sBatchID+' AS int) AS "BatchID",
    ' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",
    ' CAST('+sTransactionID+' AS int) AS "TransactionTypeID",
    ' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+
      'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+
      'AS money) AS "AmountExGST",
    ' (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN '+
      '''N'' ELSE ''Y'' END) AS "GSTFlag",

    'CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01",
    'SI.Service AS "Var02",
    'tServiceType.Code AS "Var04",
    'tApplication.Description AS "Var05",

    ' CAST(SID.Cost AS varchar) AS "Var06",
    ' CAST(SID.Cost100 AS varchar) AS "Var07", '+
      'CAST(SID.Invoiced AS varchar) AS "Var08",
    ' CAST(SID.ServiceLen AS varchar) AS "Var09",
    ' SI.UpMDF AS "Var10", '+
      'SI.DownMDF AS "Var11",
    ' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var12", '+
      'CAST(SI.StatusID AS varchar) AS "Var13", '+
      'tStatus.Status AS "Var03", '+
      'SI.SACCnumber AS "Var14", '+
      'CAST(SID.Invoiced AS varchar) AS "Var15"
//{ Changes per Dec 2008 RLD: change Var orders to use generic report & avoid new.
//    ' CAST(SID.Cost AS varchar) AS "Var01",
//    ' CAST(SID.Cost100 AS varchar) AS "Var02", '+
//      'CAST(SID.Invoiced AS varchar) AS "Var03",
//    ' CAST(SID.ServiceLen AS varchar) AS "Var04",
//    ' SI.UpMDF AS "Var05", SI.DownMDF AS "Var06",
//    ' RTRIM(SUBSTRING(SI.Description,1,50)) AS "Var07", '+
//      'CAST(SI.StatusID AS varchar) AS "Var08", tStatus.Status AS "Var09", '+
//      'SI.SACCnumber AS "Var10"
//}
    'FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+
      ' Max(tRate.Cost100) AS "Cost100",
    ' SI0.Length AS "ServiceLen", SI0.Invoiced
    'FROM (SELECT SC.ID, SC.Rate
    ' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+
      'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate"
    ' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,
    ' tCustomer.ID AS "CustomerID", (CASE
    '  WHEN ST.Frequency IS NULL THEN 1
    '  WHEN ST.Frequency = 0 THEN 1
    '  WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+
      ' AS Real) / ST.Frequency) =
    '  ROUND((Month(tServiceID.InstallationDate)-'+sMonth+
      ') / ST.Frequency,0,0) THEN 0
    '  ELSE 1
    ' END) AS "Period"
    ' FROM tServiceID
    ' LEFT JOIN tServiceType AS ST '+
      'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))
    ' LEFT JOIN tCustomer '+
      'ON ((SUBSTRING(tServiceID.SACCnumber,1,3) = tCustomer.ShipTo) '+
      'AND (tCustomer.Active = 1))
    ' WHERE ST.SystemID = '+sSystemID);
    ' AND tServiceID.StatusID IN '+sStatusID);
    ' AND tServiceID.Active = 1
    ' AND ((tServiceID.SACCnumber IS NOT NULL) '+
      'AND (tServiceID.CustomerCentreID IS NULL))
    ' AND tCustomer.InvoiceServices = 1) AS SC1
    'LEFT JOIN tDiscountedRate '+
      'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND
    ' (SC1.CustomerID = tDiscountedRate.CustomerID) '+
      'AND (tDiscountedRate.Active = 1))
    'WHERE SC1.Period = 0) AS SC) AS SI1
    'INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID
    'LEFT JOIN tRate '+
      'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+
      'AND (SI0.Length >= tRate.Length))
    'GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID
    'INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
    'LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID
// Changes per Dec 2008 RLD: Next line added to change Var orders
//  and to use generic report & avoid creating a new report.
    'LEFT JOIN tApplication ON '+
      '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1))
// End this part of change.
    'LEFT JOIN tStatus ON SI.StatusID = tStatus.ID
    'LEFT JOIN tCentre '+
      'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
    'LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
    'LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1'+
      ') AS SID2) AS SIF
  end;  // qryStringList.
  //**********************************************************************
  //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' **
  //**********************************************************************
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.ExecSQL;                 // Copies data into temp table.

  //set qryStringList to...
    'SELECT BatchID
    'FROM tDC_TempRJC_ExService
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.Open;
  if NOT FOUND
    qryServices.Close;
    MessageDlg('No Services are available for invoicing.', mtWarning, [mbOk], 0);
  else FOUND
    //set qryStringList to...
      'INSERT INTO tDataCollection
      '(SequenceNo, BatchID, ServiceID, RateDescription,
      ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,
      ' Var01, Var02, Var03,
      ' Var04, Var05, Var06, Var07, Var08, Var09, Var10,
      ' Var11, Var12, Var13, Var14, Var15)
      'SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+
        'BatchID, ServiceID, RateDescription,
      ' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,
      ' Var01, Var02, Var03,
      ' Var04, Var05, Var06, Var07, Var08, Var09, Var10,
      ' Var11, Var12, Var13, Var14, Var15
      'FROM tDC_TempRJC_ExService
    MessageForm.Msg.Caption := 'Saving charges to tDataCollection ...';
    // qryServices 'dbPPdata';
    qryServices.SQL.Text := qryStringList.Text;
    qryServices.ExecSQL;                 // Import data into tDataCollection.
    // Note tBatch does not display any amounts - do not calculate them.
    // Update the status of the batch.
    // (see below)	
    UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT);
  end;

  MessageForm.Msg.Caption := 'Cleaning up ...';
  // NOTE: Do not delete temporary table as existence persists for the session
  // even if dropped or deleted.
  // It remains okay for the 1st run, but subsequent runs would otherwise
  // cause an error.  RJC:090720.
//{
//  if tblTemp.Exists then begin
//    // qryServices 'dbPPdata';
//    qryServices.SQL.Clear;
//    qryServices.SQL.'DROP TABLE tDC_TempRJC_ExService
//    qryServices.ExecSQL;                 // Drop the temporary table.
//  end;
//}

  MessageForm.Msg.Caption := 'Updating Cost/Cost100 in tServiceID ...';
  //set qryStringList to...
  'UPDATE tServiceID');
  'SET Cost = CostAmt, Cost100 = Cost100Amt');
  'FROM (SELECT SI0.ID, Max(tRate.Cost) AS "CostAmt", '+
    'Max(tRate.Cost100) AS "Cost100Amt"');
  'FROM tServiceID AS SI0');
  'LEFT JOIN tServiceType '+
  'ON SI0.ServiceTypeID = tServiceType.ID');
  'LEFT JOIN tRate '+
  'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) AND (SI0.Length >= tRate.Length))');
  'WHERE tServiceType.SystemID = '+sSystemID);
  'AND SI0.Active = 1');
  'GROUP BY SI0.ID) AS CostRate');
  'WHERE tServiceID.ID = CostRate.ID');
  // qryServices 'dbPPdata';
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.ExecSQL;                 // Drop the temporary table.
  //****************************************************************
  //**************************** END *******************************
  //****************************************************************
end;  // GetServiceCharges.


procedure SetExServiceRates(const sSystemID, sExServiceStatusID: string);
  qInsert.DatabaseName := 'dbPPdata';
  qUpdate.DatabaseName := 'dbPPdata';

  // set qry string
    DatabaseName := 'dbPPdata';
    'SELECT ST1.ServiceTypeID, ST1.CustomerID,
//    SQL.Add('(100.0 * (CASE WHEN (ST1.Cost100 IS NULL) OR (ST1.Cost100 = 0) '+
//      'THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost100) END)) AS "ExRate",
    '(100.0 * (CASE WHEN (ST1.Cost IS NULL) OR (ST1.Cost = 0) '+
      'THEN 1 ELSE (ST1.ChargeAmount / ST1.Cost) END)) AS "ExRate",
    '(CASE WHEN ST1.Rate IS NULL THEN 1 ELSE 0 END) AS "InsertNew"
    'FROM (SELECT ST0.*,
    '(CASE WHEN ST0.MaxExServiceAmount IS NULL THEN ST0.AmountExGST
    ' WHEN (ST0.MaxExServiceAmount < ST0.AmountExGST) '+
      'THEN ST0.MaxExServiceAmount
    ' ELSE ST0.AmountExGST END) AS "ChargeAmount"
    'FROM (SELECT ST.ServiceTypeID, ST.CustomerID, ST.Rate, '+
      'ST.MaxExServiceAmount,
    'SUM(ST.AmountExGST) AS "AmountExGST", SUM(ST.Cost) AS "Cost", '+
      'SUM(ST.Cost100) AS "Cost100"
    'FROM (SELECT SID.*, SI.ServiceID, SI.ServiceTypeID, '+
      'tGroup.CustomerID, tCustomer.MaxExServiceAmount,
    'CAST((CASE WHEN SID.Invoiced IS NULL '+
      'THEN SID.Cost ELSE SID.Invoiced END) AS money) AS "AmountExGST"
    'FROM ( SELECT SI0.ID, SI1.Rate,
    ' Max(CASE WHEN tRate.Cost IS NULL THEN 0 ELSE tRate.Cost END) '+
      'AS "Cost",
    ' Max(CASE WHEN tRate.Cost100 IS NULL THEN 0 ELSE tRate.Cost100 END) '+
      'AS "Cost100",
    ' SI0.Length AS "ServiceLen", SI0.Invoiced
    ' FROM (SELECT SC1.ID, tDiscountedRate.Rate
    '  FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,
    '  tCustomer.ID AS "CustomerID"
    '  FROM tServiceID
    '  LEFT JOIN tServiceType AS ST '+
      'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))
    '  LEFT JOIN tCentre '+
      'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
    '  LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
    '  LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))
    '  LEFT JOIN tCustomer '+
      'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))
    '  WHERE ST.SystemID = '+sSystemID
    '  AND tServiceID.StatusID = '+sExServiceStatusID
    '  AND tServiceID.Active = 1
    '  AND tCustomer.InvoiceServices = 1) AS SC1
    ' LEFT JOIN tDiscountedRate '+
      'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND
    '  (SC1.CustomerID = tDiscountedRate.CustomerID) '+
      'AND (tDiscountedRate.Active = 1))) AS SI1
    'INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID
    'LEFT JOIN tRate '+
      'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+
      'AND (SI0.Length >= tRate.Length))
    'GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID
    'INNER JOIN tServiceID AS SI ON SID.ID = SI.ID
    'LEFT JOIN tServiceType ON SI.ServiceTypeID = tServiceType.ID
    'LEFT JOIN tCentre '+
      'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))
    'LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))
    'LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))
    'LEFT JOIN tCustomer '+
      'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))) AS ST
    'GROUP BY ST.ServiceTypeID, ST.CustomerID, ST.Rate, '+
      'ST.MaxExServiceAmount) AS ST0) AS ST1
    while FOUND
      sServiceTypeID := 'ServiceTypeID
      sCustomerID := 'CustomerID
      fRate := 'ExRate AsFloat;
      if FieldByName('InsertNew').AsBoolean then
        InsertRate(sServiceTypeID, sCustomerID, fRate)
      else
        UpdateRate(sServiceTypeID, sCustomerID, fRate);
      Next;
    end;  // not Eof.
  end;  // qry.
  qry.Free;
end;  // SetExServiceRates.

// from Rod
// I don't think InsertRate does anything as EXeceSQL is notr called
// and also I think there is an error in ithe sql anyhow
// (proc unmolosted)
procedure InsertRate(const sServiceTypeID, sCustomerID: string; const fRate: real);
begin
 with qInsert do begin
    SQL.Clear;
    SQL.Add('INSERT INTO tDiscountedRate');
    SQL.Add('(ServiceTypeID, CustomerID, Rate, Active)');
    SQL.Add('VALUES ('+sServiceTypeID+',');
    SQL.Add('VALUES ('+sCustomerID+',');
    SQL.Add('VALUES ('+FloatToStrF(fRate, ffFixed, 4, 8)+', 1');
  end;
end;  // InsertRate.

procedure UpdateRate(const sServiceTypeID, sCustomerID: string; const fRate: real);
begin
  //set qUpdate string
    'UPDATE tDiscountedRate');
    'SET Rate = '+FloatToStrF(fRate, ffFixed, 4, 8));
    'WHERE ServiceTypeID = '+ sServiceTypeID);
    'AND CustomerID = '+ sCustomerID);
     // qUpdate 'dbPPdata';
     ExecSQL;
end;  // UpdateRate.



UpdateBatchStatus(const nThisBatch, nStatus: LongInt);
var
begin
  nBatch := nThisBatch;
  nOutcome := nStatus;

  qryBA.DatabaseName := 'dbPPdata';
  // set  qryStringList
  'SELECT BatchStatusID FROM tBatch
  'WHERE (ID = '+ IntToStr(nBatch)+'));
  // qryBA. 'dbPPdata';
  qryBA.SQL := qryStringList;
  qryBA.Open;
  if FOUND
    // (Test avoids a possible error - but can't get here unless it exists).
    if (qryBA['BatchStatusID'] <> nOutcome) then begin
      // Update the tBatch table.
      // set qryStringList
      'UPDATE tBatch');
      'SET BatchStatusID = '+nOutcome
      'FROM tBatch');
      'WHERE ID = '+nBatch
      // qryBA. 'dbPPdata';
      qryBA.SQL := qryStringList;
      qryBA.ExecSQL;
    end;  // if (qryDC['BatchStatusID'] <> nOutcome) then.

    // Update the tBatchStatusHistory table.
    // set qryStringList
    'INSERT INTO tBatchStatusHistory');
    '  (BatchID, BatchStatusID, StatusChangeDate,UserID)');
    '  VALUES ('+IntToStr(nBatch)+','+IntToStr(nOutcome)+
      ','''+StandardDateString(Now)+''','+IntToStr(MainForm.OPERATORNUMBER)+')')
    // qryBA. 'dbPPdata';
;
    qryBA.SQL := qryStringList;
    qryBA.ExecSQL;
  end;  // if qryDC.Eof and qryDC.Bof then.
  qryBA.Close;
  qryBA.Free;
  // Now update the list of viewable Batch numbers in tDataCollection
  // (in TabTransactionSummary).
  MainForm.SetBillingPeriodBatch;
end;  // UpdateBatchStatus().