procedure InsertRate(const sServiceTypeID, sCustomerID: string; const fRate: real);
    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');

procedure UpdateRate(const sServiceTypeID, sCustomerID: string; const fRate: real);
    SQL.Add('UPDATE tDiscountedRate');
    SQL.Add('SET Rate = '+FloatToStrF(fRate, ffFixed, 4, 8));
    SQL.Add('WHERE ServiceTypeID = '+ sServiceTypeID);
    SQL.Add('AND CustomerID = '+ sCustomerID);


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


procedure GetServiceCharges(
  MessageForm.Caption := 'Creating Service Charges ...';
  MessageForm.Msg.Caption := 'Reading system variables ...';
  qryServices.SQL.Add('SELECT ID');
  qryServices.SQL.Add('FROM tTransactionType');     // Service Records Invoice.
  qryServices.SQL.Add('WHERE Description LIKE ''%Service Record%''');
  qryServices.SQL.Add('AND Active = 1');
  qryServices.Open;
  if not qryServices.Eof then
    sTransactionID := IntToStr(qryServices['id']);
  qryServices.Close;
  qryServices.SQL.Clear;                            // Define status types.
  qryServices.SQL.Add('SELECT ID');
  qryServices.SQL.Add('FROM tStatus');
//  qryServices.SQL.Add('WHERE Status LIKE ''%Service''');  // Two entries:
// Start temporary charges by tCustomer only (1 of 4):
// Start temporary charges by tCustomer only (1 of 4):
  qryServices.SQL.Add('WHERE Status LIKE ''In Service''');  // One entry:
// The above makes In Service detailed billing - Ex Service temp by Customer only.
// End temporary charges by tCustomer only (1 of 4):
// End temporary charges by tCustomer only (1 of 4):
  qryServices.SQL.Add('AND Active = 1');                  //   In Service(=2),
  qryServices.Open;                                       //   Ex Service(=4).
  sStatusID := '(';
  # ...
  while not qryServices.Eof do begin
    if (Length(sStatusID) > 1) then sStatusID := sStatusID +',';
    sStatusID := sStatusID + IntToStr(qryServices.FieldByName('id').AsInteger);
    qryServices.Next;
  end;

  // For amending charges later for "Ex Service" items...
  qryServices.SQL.Strings[2] := 'WHERE Status LIKE ''Ex Service''';
  qryServices.Open;                                       //   Ex Service(=4).
  if not qryServices.Eof then begin
    sExServiceStatusID := IntToStr(qryServices.FieldByName('id').AsInteger);
  end;
  qryServices.Close;

  qryServices.SQL.Add('SELECT ID');
  qryServices.SQL.Add('FROM tCustomer');
  qryServices.SQL.Add('WHERE ShipTo LIKE ''VRT''');
  qryServices.SQL.Add('AND Active = 1');
  qryServices.Open;
  if qryServices.Eof then
    sVRTID := '0'                                   // Define VRT CustomerID.
  else
    sVRTID := IntToStr(qryServices.FieldByName('id').AsInteger);
  qryServices.SQL.Add('SELECT ID');
  qryServices.SQL.Add('FROM tSystem');
  qryServices.SQL.Add('WHERE BatchTypeID = '+IntToStr(nBatchTypeID));
  qryServices.SQL.Add('AND Active = 1');
  qryServices.Open;
  if qryServices.Eof then sSystemID := '0'
  else
    sSystemID := IntToStr(qryServices.FieldByName('ID').AsInteger);

  // 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;
  with qryStringList do begin
    Clear;

// 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):
    Add('SELECT IDENTITY(int,1,1) AS "SequNo", ServiceID, AmountExGST, GSTFlag,');
    Add(' RateDescription, BatchID, FromDate, ToDate, TransactionTypeID,');
    Add(' GSTAmount, AmountIncGST');
    Add('INTO tDC_TempRJC_ExService');
    Add('FROM (SELECT CID2.*, CAST((CID2.AmountExGST + CID2.GSTAmount) AS money) '+
      'AS "AmountIncGST"');
    Add('FROM (SELECT CID1.*, CAST((CASE WHEN CID1.GSTFlag LIKE ''Y'' '+
      'THEN (CID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"');
    Add('FROM (SELECT (tCustomer.ShipTo + '' Services Pre July 1999'') '+
      'AS "ServiceID", (tCustomer.MaxExServiceAmount / 12) AS "AmountExGST",');
    Add(' (CASE WHEN tCustomer.ShipTo LIKE ''VRT'' THEN ''N'' ELSE ''Y'' END) '+
      'AS "GSTFlag",');
    Add(' ''Network Access'' AS "RateDescription", CAST('+sBatchID+' AS int) '+
      'AS "BatchID",');
    Add(' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",');
    Add(' CAST('+sTransactionID+' AS int) AS "TransactionTypeID"');
    Add('FROM tCustomer');
    Add('WHERE tCustomer.MaxExServiceAmount > 0');
    Add('AND tCustomer.InvoiceServices = 1) AS CID1) AS CID2) AS CID');
    //**********************************************************************
    //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' **
    //**********************************************************************
  end;  // qryStringList.
  qryServices.SQL.Text := qrystringList.Text;
  qryServices.ExecSQL;                 // Copies data into temp table.

  with qryStringList do begin
    Clear;
    Add('SELECT COUNT(*) AS "Max"');
    Add('FROM tDC_TempRJC_ExService');
  end;  // qryStringList.
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.Open;
  if qryServices.Bof and qryServices.Eof then begin
    qryServices.Close;
    sSeqNoStart := '1';
  end
  else begin
    lNothingSaved := False;
    sSeqNoStart := IntToStr(qryServices.FieldByName('Max').AsInteger + 1);
    qryServices.Close;
    with qryStringList do begin
      Clear;
      Add('INSERT INTO tDataCollection');
      Add('(SequenceNo, BatchID, ServiceID, RateDescription,');
      Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID)');
      Add('SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+
        'BatchID, ServiceID, RateDescription,');
      Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID');
      Add('FROM tDC_TempRJC_ExService');
    end;  // qryStringList.
    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.SQL.Clear;
    qryServices.SQL.Add('DROP TABLE tDC_TempRJC_ExService');
    qryServices.ExecSQL;
  end;
  with qryStringList do begin
    Clear;
// End temporary charges by tCustomer only in lieu of each service (2 of 4).
// 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).
// Start temporary charges by tCustomer only in lieu of each service (3 of 4).
//    Add('SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+
//      'RateDescription,');
    Add('SELECT IDENTITY(int,'+sSeqNoStart+',1) AS "SequNo", BatchID, ServiceID, '+
      'RateDescription,');
// End temporary charges by tCustomer only in lieu of each service (3 of 4).
// End temporary charges by tCustomer only in lieu of each service (3 of 4).
    Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+
      'ToDate, TransactionTypeID,');
    Add(' Var01, Var02, Var03,');
    Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,');
    Add(' Var11, Var12, Var13, Var14, Var15');
    Add('INTO tDC_TempRJC_ExService');
    Add('FROM (SELECT SID2.*, '+
      'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"');
    Add('FROM (SELECT SID1.*,');
    Add(' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+
      '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"');
    Add('FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+
      'CAST('+sBatchID+' AS int) AS "BatchID",');
    Add(' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",');
    Add(' CAST('+sTransactionID+' AS int) AS "TransactionTypeID",');
//    Add(' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+
//      'SID.Cost ELSE SID.Invoiced END)/12*tServiceType.Frequency) AS money) '+
//      'AS "AmountExGST",');
    Add(' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+
      'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+
      'AS money) AS "AmountExGST",');
    Add(' (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.
    Add('CONVERT(varchar(10), SI.InstallationDate, 103) AS "Var01",');
    Add('SI.Service AS "Var02",');
    Add('tStatus.Status AS "Var03",');
    Add('CONVERT(varchar(50), SI.ServiceCode) AS "Var04",');
    Add('tApplication.Description AS "Var05",');

    Add(' CAST(SID.Cost AS varchar) AS "Var06",');
    Add(' CAST(SID.Cost100 AS varchar) AS "Var07", '+
      'CAST(SID.Invoiced AS varchar) AS "Var08",');
    Add(' CAST(SID.ServiceLen AS varchar) AS "Var09",');
    Add(' SI.UpMDF AS "Var10", '+
      'SI.DownMDF AS "Var11",');
    Add(' 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.
    Add(' 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.
    Add('FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+
      ' Max(tRate.Cost100) AS "Cost100",');
    Add(' SI0.Length AS "ServiceLen", SI0.Invoiced');
    Add('FROM (SELECT SC.ID, SC.Rate');
    Add(' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+
      'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate"');
    Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,');
    Add(' tCustomer.ID AS "CustomerID", (CASE');
    Add('  WHEN ST.Frequency IS NULL THEN 1');
    Add('  WHEN ST.Frequency = 0 THEN 1');
    Add(' WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+
      ' AS Real) / ST.Frequency) =');
    Add('  ROUND((Month(tServiceID.InstallationDate)-'+sMonth+
      ') / ST.Frequency,0,0) THEN 0');
    Add('  ELSE 1');
    Add(' END) AS "Period"');
    Add(' FROM tServiceID');
    Add(' LEFT JOIN tServiceType AS ST '+
      'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))');
    Add(' LEFT JOIN tCentre '+
      'ON ((tServiceID.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))');
    Add(' LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))');
    Add(' LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))');
    Add(' LEFT JOIN tCustomer '+
      'ON ((tGroup.CustomerID = tCustomer.ID) AND (tCustomer.Active = 1))');
    Add(' WHERE ST.SystemID = '+sSystemID);
    Add(' AND tServiceID.StatusID IN '+sStatusID);
    Add(' AND tServiceID.Active = 1');
    Add(' AND tCustomer.InvoiceServices = 1) AS SC1');
    Add('LEFT JOIN tDiscountedRate '+
      'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND');
    Add(' (SC1.CustomerID = tDiscountedRate.CustomerID) '+
      'AND (tDiscountedRate.Active = 1))');
    Add('WHERE SC1.Period = 0) AS SC) AS SI1');
    Add('INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID');
    Add('LEFT JOIN tRate '+
      'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+
      'AND (SI0.Length >= tRate.Length) '+
      'AND (tRate.Active = 1))');
    Add('GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID');
    Add('INNER JOIN tServiceID AS SI ON SID.ID = SI.ID');
    Add('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.
    Add('LEFT JOIN tApplication ON '+
      '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1))');
// End this part of change.
    Add('LEFT JOIN tStatus ON SI.StatusID = tStatus.ID');
    Add(' LEFT JOIN tCentre '+
      'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))');
    Add(' LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))');
    Add(' LEFT JOIN tGroup '+
      'ON ((tDepartment.GroupID = tGroup.ID) AND (tGroup.Active = 1))) AS SID1');
    Add('WHERE SID1.AmountExGST IS NOT NULL) AS SID2) AS SIF');
  end;  // qryStringList.
  //**********************************************************************
  //** COPY SERVICES INTO 'tDataCollection' VIA 'tDC_TempRJC_ExService' **
  //**********************************************************************
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.ExecSQL;                 // Copies data into temp table.

  with qryStringList do begin
    Clear;
    Add('SELECT BatchID');
    Add('FROM tDC_TempRJC_ExService');
  end;  // qryStringList.
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.Open;
  if qryServices.Bof and qryServices.Eof then begin
    qryServices.Close;
  end
  else begin
    lNothingSaved := False;
    qryServices.Close;
    with qryStringList do begin
      Clear;
      Add('INSERT INTO tDataCollection');
      Add('(SequenceNo, BatchID, ServiceID, RateDescription,');
      Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,');
      Add(' Var01, Var02, Var03,');
      Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,');
      Add(' Var11, Var12, Var13, Var14, Var15)');
      Add('SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+
        'BatchID, ServiceID, RateDescription,');
      Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,');
      Add(' Var01, Var02, Var03,');
      Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,');
      Add(' Var11, Var12, Var13, Var14, Var15');
      Add('FROM tDC_TempRJC_ExService');
    end;  // qryStringList.
    qryServices.SQL.Text := qryStringList.Text;
    qryServices.ExecSQL;
  end;  // if no data saved.
  MessageForm.Msg.Caption := 'Adding new services ...';
  MainForm.ProcessWinMessages;
  qryStringList.Clear;
  if tblTemp.Exists then begin
    qryStringList.Add('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.
  with qryStringList do begin
    Clear;
// Start temporary charges by tCustomer only in lieu of each service (4 of 4).
// Start temporary charges by tCustomer only in lieu of each service (4 of 4).
//    Add('SELECT IDENTITY(int,1,1) AS "SequNo", BatchID, ServiceID, '+
//      'RateDescription,');
    Add('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).
    Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, FromDate, '+
      'ToDate, TransactionTypeID,');
    Add(' Var01, Var02, Var03,');
    Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,');
    Add(' Var11, Var12, Var13, Var14, Var15');
    Add('INTO tDC_TempRJC_ExService');
    Add('FROM (SELECT SID2.*, '+
      'CAST((SID2.AmountExGST + SID2.GSTAmount) AS money) AS "AmountIncGST"');
    Add('FROM (SELECT SID1.*,');
    Add(' CAST((CASE WHEN SID1.GSTFlag LIKE ''Y'' THEN '+
      '(SID1.AmountExGST * '+sGSTRate+') ELSE 0 END) AS money) AS "GSTAmount"');
    Add('FROM (SELECT SID.*, SI.ServiceID, ''Network Access'' AS "RateDescription", '+
      'CAST('+sBatchID+' AS int) AS "BatchID",');
    Add(' ('''+sFromDate+''') AS "FromDate", ('''+sToDate+''') AS "ToDate",');
    Add(' CAST('+sTransactionID+' AS int) AS "TransactionTypeID",');
    Add(' CAST(((CASE WHEN SID.Invoiced IS NULL THEN '+
      'SID.Cost ELSE SID.Invoiced END)*SID.Rate/12*tServiceType.Frequency) '+
      'AS money) AS "AmountExGST",');
    Add(' (CASE WHEN tGroup.CustomerID = '+sVRTID+' THEN '+
      '''N'' ELSE ''Y'' END) AS "GSTFlag",');

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

    Add(' CAST(SID.Cost AS varchar) AS "Var06",');
    Add(' CAST(SID.Cost100 AS varchar) AS "Var07", '+
      'CAST(SID.Invoiced AS varchar) AS "Var08",');
    Add(' CAST(SID.ServiceLen AS varchar) AS "Var09",');
    Add(' SI.UpMDF AS "Var10", '+
      'SI.DownMDF AS "Var11",');
    Add(' 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.
    Add(' 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"');
}
    Add('FROM (SELECT SI0.ID, SI1.Rate, Max(tRate.Cost) AS "Cost",'+
      ' Max(tRate.Cost100) AS "Cost100",');
    Add(' SI0.Length AS "ServiceLen", SI0.Invoiced');
    Add('FROM (SELECT SC.ID, SC.Rate');
    Add(' FROM (SELECT SC1.ID, (CASE WHEN tDiscountedRate.Rate '+
      'IS NULL THEN 100.0 ELSE tDiscountedRate.Rate END) / 100.0 AS "Rate"');
    Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID,');
//    Add(' FROM (SELECT tServiceID.ID, tServiceID.ServiceTypeID, '+
//      'SUBSTRING(tServiceID.SACCnumber,1,3) AS "SACCShipTo",');
    Add(' tCustomer.ID AS "CustomerID", (CASE');
    Add('  WHEN ST.Frequency IS NULL THEN 1');
    Add('  WHEN ST.Frequency = 0 THEN 1');
    Add('  WHEN (CAST(Month(tServiceID.InstallationDate)-'+sMonth+
      ' AS Real) / ST.Frequency) =');
    Add('  ROUND((Month(tServiceID.InstallationDate)-'+sMonth+
      ') / ST.Frequency,0,0) THEN 0');
    Add('  ELSE 1');
    Add(' END) AS "Period"');
    Add(' FROM tServiceID');
    Add(' LEFT JOIN tServiceType AS ST '+
      'ON ((tServiceID.ServiceTypeID = ST.ID) AND (ST.Active = 1))');
    Add(' LEFT JOIN tCustomer '+
      'ON ((SUBSTRING(tServiceID.SACCnumber,1,3) = tCustomer.ShipTo) '+
      'AND (tCustomer.Active = 1))');
    Add(' WHERE ST.SystemID = '+sSystemID);
    Add(' AND tServiceID.StatusID IN '+sStatusID);
    Add(' AND tServiceID.Active = 1');
    Add(' AND ((tServiceID.SACCnumber IS NOT NULL) '+
      'AND (tServiceID.CustomerCentreID IS NULL))');
    Add(' AND tCustomer.InvoiceServices = 1) AS SC1');
    Add('LEFT JOIN tDiscountedRate '+
      'ON ((SC1.ServiceTypeID = tDiscountedRate.ServiceTypeID) AND');
    Add(' (SC1.CustomerID = tDiscountedRate.CustomerID) '+
      'AND (tDiscountedRate.Active = 1))');
    Add('WHERE SC1.Period = 0) AS SC) AS SI1');
    Add('INNER JOIN tServiceID AS SI0 ON SI1.ID = SI0.ID');
    Add('LEFT JOIN tRate '+
      'ON ((SI0.ServiceTypeID = tRate.ServiceTypeID) '+
      'AND (SI0.Length >= tRate.Length))');
    Add('GROUP BY SI0.ID, SI0.Length, SI0.Invoiced, SI1.Rate) AS SID');
    Add('INNER JOIN tServiceID AS SI ON SID.ID = SI.ID');
    Add('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.
    Add('LEFT JOIN tApplication ON '+
      '((SI.ApplicationID = tApplication.ID) AND (tApplication.Active = 1))');
// End this part of change.
    Add('LEFT JOIN tStatus ON SI.StatusID = tStatus.ID');
    Add('LEFT JOIN tCentre '+
      'ON ((SI.CustomerCentreID = tCentre.ID) AND (tCentre.Active = 1))');
    Add('LEFT JOIN tDepartment '+
      'ON ((tCentre.DepartmentID = tDepartment.ID) AND (tDepartment.Active = 1))');
    Add('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.SQL.Text := qryStringList.Text;
  qryServices.ExecSQL;                 // Copies data into temp table.

  with qryStringList do begin
    Clear;
    Add('SELECT BatchID');
    Add('FROM tDC_TempRJC_ExService');
  end;  // qryStringList.
  qryServices.SQL.Text := qryStringList.Text;
  qryServices.Open;
  if qryServices.Bof and qryServices.Eof and lNothingSaved then begin
    qryServices.Close;
    MessageDlg('No Services are available for invoicing.', mtWarning, [mbOk], 0);
  end
  else begin
    qryServices.Close;
    with qryStringList do begin
      Clear;
      Add('INSERT INTO tDataCollection');
      Add('(SequenceNo, BatchID, ServiceID, RateDescription,');
      Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,');
      Add(' Var01, Var02, Var03,');
      Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,');
      Add(' Var11, Var12, Var13, Var14, Var15)');
      Add('SELECT CAST(SequNo AS varchar) AS "SequenceNo", '+
        'BatchID, ServiceID, RateDescription,');
      Add(' AmountExGST, GSTFlag, GSTAmount, AmountIncGST, '+
        'FromDate, ToDate, TransactionTypeID,');
      Add(' Var01, Var02, Var03,');
      Add(' Var04, Var05, Var06, Var07, Var08, Var09, Var10,');
      Add(' Var11, Var12, Var13, Var14, Var15');
      Add('FROM tDC_TempRJC_ExService');
    end;  // qryStringList.
    MessageForm.Msg.Caption := 'Saving charges to tDataCollection ...';
    MainForm.ProcessWinMessages;
    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.
    UpdateBatchStatus(nBatchNumber, MainForm.BATCH_STATUS_IMPORT);
  end;  // if no data saved.
  MessageForm.Msg.Caption := 'Cleaning up ...';
  MainForm.ProcessWinMessages;
  qryStringList.Clear;
  // 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.SQL.Clear;
    qryServices.SQL.Add('DROP TABLE tDC_TempRJC_ExService');
    qryServices.ExecSQL;                 // Drop the temporary table.
  end;
}

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


procedure ServiceInvoice(nBatch: LongInt;const dStartDate, dEndDate: TDateTime;
  const BatchDescription : string; nBatchTypeID : integer);
var
  lContinue : Boolean;
  EmptyList : TStringList;
begin
  EmptyList := TStringList.Create;         // Removes all entries for the batch.
  lContinue := True;
  DeleteFromDC( nBatch, EmptyList, lContinue);
  EmptyList.Free;
  GetServiceCharges( nBatch, dStartDate, dEndDate, BatchDescription,
    nBatchTypeID, lContinue);
end;  // ServiceInvoice.

end.  // Services.