Replicated IDI platform data is often collected and transferred to internal systems, data warehouses, and business intelligence applications by customers. Problems can occur during data transmission, requiring data engineers to determine where the transfer process last left off. Data engineers often rely on the Last Modified date stored in the IDI platform for this purpose. It tracks the date and time a record was inserted or updated into a database table. Not every table has a last modified date. Adding a lastmodifieddate column to additional tables makes it easier for data engineers to analyze where the transfer process ended and determine where to resume. The following improvements for storing the last modified date are provided with this release.
Note:
This field may be labeled LastModifiedDate or LastModified in different tables.
Updates to Existing LastModifiedDate field
The section below addresses scenarios where the database table contains an existing Last Modified Date, but the value was not set every time a record was inserted or updated.
Updates to Billing Process
The billing process has been enhanced to set the LastModifiedDate fields for InvoiceDetail and Non Usage Services (NonUsgSvc) tables when invoice detail records are created and invoice numbers populated.
The LastModifiedDate is set in the InvoiceDetail table when writing new rows to the table.
The LastModifiedDate is updated in the NonUsgSvc table when the invoice number posted is added to the record. The LastModifiedDate is updated in the NonUsgSvc table when the final invoice number is added to the record.
Updates to Billing XML generation process
The IDI platform XML generation process creates the invoice XML file. The file is used to generate the PDF or printed invoice. The XML generation process has been enhanced to set the Last Modified field when the invoice status value is updated in the Invoice table. The status changes to print once the XML file is generated.
Updates to Invoice File Upload process
The Invoice File Upload process has been enhanced to set the LastModified field in the Invoice table when PDF invoices are uploaded to Document Storage.
Updates to Account Posting process
The account Posting process posts payments and adjustments to the customer’s account balance. The Account Posting process has been enhanced to:
Set LastModifiedDate in the AccountArrears table when a record is added or updated.
Set LastModifiedDate in the Payment table when a payment is posted to an invoice.
Set LastModifiedDate in the Adjustment table when an adjustment is posted to an invoice.
Updates to Process for First/Last Call Updates
The Update First Call and Last Call Dates scheduled job analyzes the usage databases and updates the service number with the first and last calls made. The Update First and Last Call Dates scheduled job has been enhanced to set the LastModified field in the UsgSvc table whenever First Call Date or Last Call Date values have been added or updated.
Last Modified Date Column Added to Additional Tables
A new column called ct_LastModifiedDate was added to the following tables to support monitoring records that were added or updated. The tables below did not have an existing Last Modified Data column.
Alert
AttPropertyOccurrence
BillingCycleDetail
Collection
Contact
CorpAccts
Device
EPayRecurring
GCJFulfillmentRespDet
JournalFollowUp
Phonebook
PhonebookEntry
POSTxnDetail
POSTxnTender
ProductCatalog
ProductCatalogGroupMember
ProductCatalogTree
RetailDiscount
RetailInstallmentPlan
RetailProduct
RetailProductJurisdictionTax
SalesEntity
SystemOrder
SystemOrderDetail
The default value for ct_LastModifiedDate is the current date and time.
NOTE:
ct_ indicates change tracking. This column is not accessible through the web services. It is only accessible through replication. Also, these fields do not guarantee an audit log entry, which normally accompanies updates to the Last Modified Date.