Recover HostLog TranIDs

Created: 30 October 2013. Last Reviewed: 09 September 2015.

Under some circumstances, it might become necessary to recover POS HostLog data processed via the EEE HostLogExportStaging tables if the original data was missing TranIDs. This could arise if a store server was mis-configured to not generate TranIds for a period of time and the ERP receiving data from HostLogExportStaging is dependent upon TranIds (this includes EEE .HOS file exporter if configured to use TranID validation).

Should the need arise to recover POS HostLog data processed via the EEE HostLogExportStaging tables a stored procedure exists in EEE to assist.

The parameters are as follows:

@branch TBranchCode
@datefrom datetime
@dateto datetime
@bRegenTranIdsOnly bit
@CopyBackToHostLogExportStaging bit

You can execute the following to preview changes:

exec usp_RecoverHostLog '723', '20131024', '20131024', 1, 0

and to actually recover the data:

exec usp_RecoverHostLog '723', '20131024', '20131024', 1, 1

or better:

select * from HostLogExportStaging
begin tran
exec usp_RecoverHostLog '723', '20131024', '20131024', 1, 1
select * from HostLogExportStaging
rollback OR commit as appropriate

This proc should take care of recovering TranIds for SH and related records only - as well as miscellaneous / stand alone record types TS, VR, DS, BS, TV. It deliberately excludes EV, VI, HB as no likely ERP interface implementation would require these record types. There are other unhandled, miscellaneous record types - hence the preview mode which will reveal if there are other records with unrecovered TranIDs - case by case assessment would be necessary to determine if further steps are required to recover extra record types not handled by this procedure.

Multi-row transactions such as goods receipts and transfers are unhandled - the TranIDs for these are created by EEE not POS so should not need recovery in the same way. However should the need arise, Magenta could extend this procedure to handle these using the same concepts as SH sale transactions.

UPDATE: As of EEE version 1.76 SR-9 support for GH, XH, TH records and their children has been added.