Store database purging manually

***************************** Internal to Citixsys Support Team*********************************

Environment : 

iVend 6.6 7989 with SAPB1

Problem Statement :  

Store database doesn't purge by iVend MC End of day process. so there is need to purge the store database manually .

Note : It should be discussed with Level 3 team first before using this one

SQL Queries : 

        SELECT DISTINCT A.TransactionKey INTO #TempTransactionsToBeCleaned FROM TrxTransaction A 

        WHERE CONVERT(NVARCHAR, A.BusinessDate, 112) < '20130201'    

        

        DELETE FROM TrxTransactionPromotionDetail WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionQuotation WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionPrinting WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionStatus WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionSurcharge WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionSalesPerson WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionSignature WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionPayment WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionGiftCertificate WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionInstallmentDetail WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionFulfillmentDetail WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionItemAttribute WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionLoyalty WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionOrder WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionSaleItem WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionLayaway WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionLostSaleItem WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionAttribute WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxARPayment WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionAudit WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionFulfillment WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionConsignment WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransactionCoupon WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        DELETE FROM TrxTransaction WHERE TransactionKey IN (SELECT TransactionKey FROM #TempTransactionsToBeCleaned)

        

       

DROP TABLE #TempTransactionsToBeCleaned

• Related Articles

• Data not purging on store server properly

  *********** For Internal CitiXsys Use only *********** Environment : iVend 6.6 Problem Description : Data is not purging on for a single store at the time of EOD. Scenario : On a store data not purging properly and the database growing larger by the ...

• Addon information was not updated for newly created store

  Environment: iVend 6.6 integrated with SAPB1 Problem Statement: Addon information was not updated for newly created store Symptoms: Not Applicable Resolution/Work Around: Issue is already fixed in the latest iVend patch (8567). As a workaround we ...

• Cannot open database 'CXSIntegration' login failed

  Environment: iVend 6.6 Problem Statement: Cannot open database 'CXSIntegration' login failed Symptoms: Environment Specific Resolution/Work Around: It seems wrong SQL password is entered for user "sa". So need to check correct password for SQL login ...

• ISSUES WHEN INTIALIZING MASTER TABLES FROM SAP: "iVend 6.*.*.* add-on failed to update the database"

  Product version 6.5 Update 6 Problem statement Getting Error "iVend 6.*.*.* add-on failed to update the database" when initializing master tables from SAP Reason CXSINTEGRATION DATABASE IS DELETED SAP database, already initialized with iVend database ...

• iVend POS - Purge Data to Reduce iVend Store database size

  Introduction The Data Purging feature in iVend helps the Administrator to control the size of the iVend database at each store. Data purging at stores is controlled centrally and is managed from the iVend Enterprise at Head Office. The Administrator ...