Overview

There might be cases when one or more contract renewal letters are showing as processed but haven't yet been delivered and you want to know why this is happening. If needed, a list of such customers can also be exported from the database.

Solution

This might be because the customer's Correspondence Preferences aren't configured correctly for the letters to be delivered. This can be checked as shown below by going to the customer's profile, and clicking on Edit in Invoice Delivery Type in Customer Info > Billing Information. Both of the CONTRACT_RENEWAL options should be enabled for Print.

To configure delivery of the Contract Renewal letter, click Edit on the top of the screen which has the Correspondence Preferences shown above. On the subsequent screen, select the Print option for both the CONTRACT_RENEWAL rows.

If you want a list of customers who didn't receive these letters even though they were printed, open a support ticket mentioning a date in the past from when this information is needed.

<supportagent>

This ticket might be created by one of our clients, Stream, asking for more information on this behavior. The customers receiving the printed copies of messages is dependent on a few conditions which you can share with the client if they want more information on how the preferences work:

1. All Disconnect letters are printed and mailed.
2. Any other letter for a specific customer is only printed if
   1. Delivery Type ID is set to 1 (known as Standard on the UI) for that customer AND
   2. No preference set in the CorrespondenceDeliveryTypePref table.
3. All letters are printed if CorrespondencePrefType for Letter is set to Print in the CorrespondenceDeliveryTypePref table.

So any letter, including Contract Renewal letters, that don't need to be printed will show as processed but will never show up as delivered, which would mean anything sent via email will exhibit this behavior. Other options in Delivery Type are EDI, Email or Bank Consolidated, Email invoice as Notification(customer informed that invoice is available on portal), Email invoice as Attachment(invoice mailed as attachment).

If the client has asked for a list of such customers for whom the Contract Renewal letters were printed but never delivered, then follow the below steps to export that information:

1. Login to the Jump server 10.185.40.141.
2. Open Microsoft SSMS and connect to aes.cons.com.
3. Select New Query in the top bar.
4. Select the client-specific database.
5. Execute the following query with the date supplied by the client

   select l.LetterID,l.printstatusid, l.CustID, l.LetterTypeID, l.createdate, ps.description, lt.type
   from letter l
   join printstatus ps on l.printstatusid = ps.printstatusid
   join lettertype lt on l.lettertypeid = lt.lettertypeid
   join Customer c on c.CustID = l.CustID
   where l.createdate > '2020-08-20 00:00:00.000'
   and lt.type like '%ContractRenewal%'
   and ps.printstatusid <> 4
   order by 1 desc    

6. Share the resultset with the client.

</supportagent>