Needs completing: Second day of month end (see system calendar)

  

To check for and hopefully eliminate these duplicates in advance, around 3-4pm of the second month end day, raise a ticket with the DBAs asking them to run the following script against Live DMS:

 

SELECT client_id, creditor_id, debt_sub_key, COUNT(*)
 FROM debt_info
 GROUP BY client_id, creditor_id, debt_sub_key
 HAVING COUNT(*) > 1

 

If this returns any items, please pass the details to CAM as an URGENT email and ask them to delete the duplicate debt (we suggest the one with the highest debt sub key/debt number).

 

Please note: a result like the following wouldn't be considered a duplicate as it doesn't have values for creditor_id and debt_sub_key:






These are 'duplicates' of items with no info and wouldn't be considered as duplicate by the disbursement. Ones with these values present will be the duplicates we need to remove.


If a duplicate has been found then the below script will need passing to the DBA's to run in Live to remove the disbursement checkpoint file, so disbursement can start again: 


USE DMS
BEGIN TRY
    BEGIN TRANSACTION
    
        UPDATE [DMS].[dbo].[system_codes]
        SET value = 'PASS'
        WHERE type = 'DAILYDISBURSEMENT'
        AND CODE = 'STATUS'

        IF ROWCOUNT_BIG() <> 1
        RAISERROR('Incorrect number of records updated',11,1)

    COMMIT TRANSACTION
    PRINT 'Success - script completed without errors'
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION
    PRINT 'Error:'
    PRINT ERROR_MESSAGE()
END CATCH