Transfer content of the transaction tables

The tables call_events, agent_events, enqreg_header and surveys are normally bigger, and an incremental transfer of the data (i.e only new and changed records) is strongly recommended. Please note that Sql Server’s “Change Data Capture” (CDC) functionality is not supported, hence you should do the incremental transfer as described further down.

Note: Puzzel, according to contract, have the right to strongly reduce, or even disconnect, connections forcing too heavy data transfer load on the connections.

Common to all tables mentioned above (and call_relations from dv v1.7) is that they have a column named dte_updated. This is a date stamp holding the exact time for when the record was inserted or last updated. We suggest that you, per table, keep track of the highest value for this column after each transfer - and use this value as a starting point the next time you select from the tables.

The tables call_events_extras and call_variables do not have an explicit dte_updated column. Though, call_variables relate to a call_id in table call_events and call_events_extras relate to the rec_id in call_events. Ensure loading of records from these tables each time the “parent” (or “owner”) in table call_events are loaded.

Please note the important field call_id has datatype Numeric (19,0).

Important for call_events and agent_events records:

For different reasons, part of the data for one call_id might be added on a later stage. This is caused either by later callback sessions (related to the original call), by changed wrap-up information caused by the agent after the (related) call ended, or due to a Dialler call happening (much) later then the contact was put into the queue. In such cases, all data (events) for the call_id are removed and reloaded with a complete set of the new/modified data. This will also include the agent_event table if agents are involved in the call (agent_events of type ‘c’). The column rec_id in the tables could therefore change over time for a particular call.

If you need to avoid double loading of call_events/ agent_events records in such cases, you will probably need a locally stored history log holding the call_ids loaded recently.

“Late update” of a call may only occur for a limited period of time. For the time being, no calls will be updated after 72 hours. There is one exception; emails might have a “gap” between the pre-initiation event and “the rest” with duration of up to 60 days (because an email can be waiting in queue for max 60 days).

Important for enqreg_header, enqreg_category and enqreg_topic:

The records in these three tables are related, and records related to the same enquiry registration will always be loaded together. Thus, you can rely on the column dte_updated in enqreg_header to detect new records in all three tables. During normal operations, an enquiry registration will never be changed after arrival to the database.