/
Avery Dennison DB Archival

Avery Dennison DB Archival

Owned by Rajeev Rai
Last updated: Jul 03, 2024
9 min read

Solution DB Archival Process for Avery Dennison

Introduction

This document holds all the information related to the archival process of solution database in Avery Dennison project. As of now we are going to archive the whole data except the data set that is stored into database in previous six months.

Applicable Roles

All of the following roles in AOC will be able to see only previous six months data set in AOC:

  • Super Admin

  • CSR

  • Site Manager

Database Enhancements

There are following enhancements that will took place for archival process:

  •  Creation of archive tables in the specific database of a environment

  • There will be insertion of archived data set into the archival table

  • All the archive database will be deleted from the main tables after the archival process

Query Formation

Archiving data set:

 While forming MySQL for creating the archive tables we need to copy the DDL command by opening the table and then we need to change following things in that: 

  • First we will add the suffix to the table name e.g: orderline is the table name then its archive table name will be orderlin_archive

  • First create those tables which are parent tables and after its creation create child tables with the changed foreign key

  • Also edit the table name which is written in the reference section of DDL command

  • There will be manual effort for all AOC environments to archive data

Deletion of data set after archival:

Deletion of data set should be in following way:

  • First we need to delete the child table data set and then their parent 

  • Deletion should be done on basis of ID provided and on basis of this ID we can get the referential tables ID and then we can also delete them

Impacted Components of AOC

Below are the area of impact in AOC:

  • Solution DB (Environment wise)

  • Data set on Order Email Queue screen

  • Data set on Order Queue screen

  • Data set on Order line screen

  • Data set on Order line details screen

  • Data set on Sales Order line screen

  • Data set on Sales Order line details screen

  • Parent child relationships between tables

Tables to archive

  • OrderEmailQueue

  • OrderFileAttachment

  • OrderFileQueue

  • Orderline

  • OrderlineDetails

  • SalesOrderline

  • SalesOrderlineDetails

Queries

 

orderemailqueue_archive

CREATE TABLE

    orderemailqueue_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        acknowledgementDate DATETIME,

        assignCSR VARCHAR(100),

        assignee VARCHAR(100),

        ccMailId VARCHAR(1000),

        COMMENT VARCHAR(250),

        createdBy VARCHAR(50),

        createdDate DATETIME,

        lastModifiedBy VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        error longtext,

        mailBody longtext,

        orderMail TINYINT(1),

        orderSource VARCHAR(50),

        processId VARCHAR(100),

        readDate DATETIME,

        receivedDate DATETIME,

        senderEmailId VARCHAR(100),

        status VARCHAR(100),

        subject VARCHAR(1000),

        toMailId VARCHAR(500),

        emailSubjectProductLineMatch VARCHAR(250),

        emailSubjectRBOMatch VARCHAR(250),

        emailBodyProductLineMatch VARCHAR(100),

        emailBodyRBOMatch VARCHAR(100),

        siteId INT DEFAULT '0',

        emailSubjectPartnerMatch VARCHAR(250),

        systemCsrCode VARCHAR(100),

        PRIMARY KEY (id)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

orderfileattachment_archive

CREATE TABLE

    orderfileattachment_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        additionalDataFileKey longtext,

        COMMENT longtext,

        createdBy VARCHAR(50),

        createdDate DATETIME,

        error VARCHAR(50),

        fileContentType VARCHAR(1000),

        fileData VARCHAR(100),

        fileExtension VARCHAR(50),

        fileName VARCHAR(250),

        filePath VARCHAR(250),

        lastModifiedBy VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        orderFileId INT,

        status VARCHAR(100),

        orderEmailQueueId INT,

        productLineId INT,

        productLineMatch VARCHAR(250),

        rboMatch VARCHAR(250),

        fileContentMatch VARCHAR(250),

        partnerMatch VARCHAR(250),

        PRIMARY KEY (id),

        CONSTRAINT orderfileattachment_fkarchive FOREIGN KEY (orderEmailQueueId) REFERENCES

        orderemailqueue_archive (id),

        INDEX FKCFCBF2ADAC1E2DE1 (orderEmailQueueId),

        INDEX FKCFCBF2AD849E69F9 (productLineId)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

orderfilequeue_archive

CREATE TABLE

    orderfilequeue_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        acknowledgementDate DATETIME,

        COMMENT VARCHAR(100),

        createdDate DATETIME,

        csrCode VARCHAR(100),

        error longtext,

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        pId VARCHAR(100),

        poNumber text,

        prevOrderQueueId INT DEFAULT '0',

        productLineId INT,

        status VARCHAR(100),

        subject VARCHAR(50),

        createdBy VARCHAR(50),

        lastModifiedBy VARCHAR(50),

        orderFileAttachmentId INT,

        feedbackAcknowledgementDate DATETIME,

        submittedBy VARCHAR(50),

        submittedDate DATETIME,

        PRIMARY KEY (id),

        CONSTRAINT FKarchive9 FOREIGN KEY (productLineId) REFERENCES partner_rboproductline (id) ,

        CONSTRAINT orderfilequeue_fkarchive FOREIGN KEY (orderFileAttachmentId) REFERENCES

        orderfileattachment_archive (id),

        INDEX FK60600187F0F7E2F5 (orderFileAttachmentId),

        INDEX FK60600187849E69F9 (productLineId)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

orderline_archive

CREATE TABLE

    orderline_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        APOType VARCHAR(5),

        ATOValidationFlag VARCHAR(50),

        averyATO VARCHAR(50),

        FOO VARCHAR(100),

        HTLSizePageValidationFlag VARCHAR(50),

        MOQDiffQty VARCHAR(100),

        MOQValidationFlag VARCHAR(50),

        averyMOQ VARCHAR(50),

        averyRoundupQty VARCHAR(50),

        PONumber VARCHAR(500),

        account VARCHAR(100),

        agreement VARCHAR(50),

        artWorkhold VARCHAR(5),

        artworkAttachment VARCHAR(5),

        averyItemNumber VARCHAR(50),

        siteId VARCHAR(50),

        bankCharge VARCHAR(10),

        billToAddress1 VARCHAR(250),

        billToAddress2 VARCHAR(250),

        billToAddress3 VARCHAR(250),

        billToCity VARCHAR(100),

        billToContact VARCHAR(250),

        billToCountry VARCHAR(100),

        billToCustomer VARCHAR(250),

        billToEmail VARCHAR(250),

        billToFax VARCHAR(100),

        billToState VARCHAR(100),

        billToTelephone VARCHAR(250),

        billToZip VARCHAR(50),

        bulk VARCHAR(50) DEFAULT 'Y',

        averyBulk VARCHAR(50),

        bulkSampleValidationFlag VARCHAR(50),

        calculatedOrderdedQty VARCHAR(10),

        carrier VARCHAR(100),

        COMMENT text,

        contractNumber VARCHAR(100),

        cooTranslationFlag VARCHAR(50),

        createdBy VARCHAR(50),

        createdByName VARCHAR(50),

        createdDate DATETIME,

        csr VARCHAR(50),

        customerColorCode VARCHAR(250),

        customerColorDescription VARCHAR(250),

        customerCost VARCHAR(50),

        customerItemNumber VARCHAR(250),

        customerItemNumber1 VARCHAR(250),

        customerItemNumber2 VARCHAR(50),

        customerNumber VARCHAR(100),

        customerOrderedQty VARCHAR(50),

        customerPOFlag VARCHAR(50),

        customerPONumber VARCHAR(500),

        customerRequestDate DATETIME,

        customerSeason VARCHAR(50),

        customerSize VARCHAR(250),

        customerUOM VARCHAR(50),

        customerUnitPrice VARCHAR(50),

        divisionForInterfaceERPORG VARCHAR(100),

        duplicatePOFlag VARCHAR(50),

        endCustomer VARCHAR(50),

        fabricCode VARCHAR(100),

        febricPercentageFlag VARCHAR(50),

        freightCharge VARCHAR(10),

        freightTerms VARCHAR(50),

        invoicelineInstruction longtext,

        itemDescription VARCHAR(1000),

        lastModifiedBy VARCHAR(50),

        lastModifiedByName VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        mandatoryVariableDataFieldFlag VARCHAR(50),

        manufacturingNotes longtext,

        modelSerialNumber VARCHAR(50),

        oracleBillToSiteNumber VARCHAR(100),

        oracleShipToSiteNumber VARCHAR(100),

        orderBy VARCHAR(50),

        orderDate DATETIME,

        orderReceivedDate DATETIME,

        orderType VARCHAR(200),

        orderedDate DATETIME,

        packingInstruction longtext,

        pageSize VARCHAR(100),

        partnerCustomerName VARCHAR(250),

        partnerVendorName VARCHAR(250),

        productionHold VARCHAR(5),

        productLineType VARCHAR(50),

        averyProductLineType VARCHAR(50),

        promiseDate DATETIME,

        qtyUnit VARCHAR(100),

        region VARCHAR(100),

        averyRegion VARCHAR(50),

        remark text,

        requestedDevliveryDate DATETIME,

        retailerPO_CustomerJob VARCHAR(100),

        reviseOrderFlag VARCHAR(500),

        roundQty VARCHAR(100),

        rushOrderCheck VARCHAR(100),

        sample VARCHAR(100),

        sentToOracleDate DATETIME,

        shipToAddress1 VARCHAR(250),

        shipToAddress2 VARCHAR(250),

        shipToAddress3 VARCHAR(250),

        shipToCity VARCHAR(100),

        shipToContact VARCHAR(250),

        shipToCountry VARCHAR(100),

        shipToCustomer VARCHAR(250),

        shipToEmail VARCHAR(250),

        shipToFax VARCHAR(100),

        shipToState VARCHAR(100),

        shipToTelephone VARCHAR(250),

        shipToZip VARCHAR(50),

        shipVia VARCHAR(100),

        shippingHold VARCHAR(50),

        shippingInstructions longtext,

        shippingMethod VARCHAR(50),

        shippingOnlyNotes longtext,

        soldToRBONumber VARCHAR(50),

        specialInstruction longtext,

        splitShipset VARCHAR(5),

        status VARCHAR(100) NOT NULL,

        styleNo VARCHAR(1000),

        targetSystem VARCHAR(50),

        shipMark longtext,

        additionalLabelInternalItem longtext,

        updateMOQ VARCHAR(100),

        variableDataBreakdown longtext,

        waiveMOQ VARCHAR(5),

        orderQueueId INT NOT NULL,

        additionalFileId VARCHAR(100),

        productionLine longtext,

        COO VARCHAR(1000),

        GroupingField_1 VARCHAR(250),

        GroupingField_2 VARCHAR(250),

        GroupingField_3 VARCHAR(250),

        GroupingField_4 VARCHAR(250),

        GroupingField_5 VARCHAR(250),

        GroupingField_6 VARCHAR(250),

        GroupingField_7 VARCHAR(250),

        GroupingField_8 VARCHAR(250),

        GroupingField_9 VARCHAR(250),

        GroupingField_10 VARCHAR(250),

        GroupingField_11 VARCHAR(250),

        GroupingField_12 VARCHAR(250),

        GroupingField_13 VARCHAR(250),

        GroupingField_14 VARCHAR(250),

        GroupingField_15 VARCHAR(250),

        GroupingField_16 VARCHAR(250),

        GroupingField_17 VARCHAR(250),

        GroupingField_18 VARCHAR(250),

        GroupingField_19 VARCHAR(250),

        GroupingField_20 VARCHAR(250),

        RN VARCHAR(250),

        CN VARCHAR(250),

        ArticleNumber VARCHAR(250),

        orderFileOrderType VARCHAR(50),

        skuQtyDifference VARCHAR(100),

        systemOrgCode VARCHAR(50),

        queryOrgCode VARCHAR(50),

        division VARCHAR(50),

        sortingId bigint,

        addditionalFileId VARCHAR(100),

        PRIMARY KEY (id),

        INDEX FKE8A5546233DF1C25 (orderQueueId)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

orderlinedetails_archive

CREATE TABLE

    orderlinedetails_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        SKUno VARCHAR(50),

        COMMENT VARCHAR(250),

        createdBy VARCHAR(50),

        createdDate DATETIME,

        fiberPercent INT DEFAULT '0',

        lastModifiedBy VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        level VARCHAR(50),

        mandatory VARCHAR(100),

        sumOfFiberPercentage INT DEFAULT '0',

        typeSetter VARCHAR(50),

        variableDataValue VARCHAR(1000),

        variableFieldName VARCHAR(100),

        orderLineId INT,

        helpMessage longtext,

        PRIMARY KEY (id),

        CONSTRAINT orderlinedetails_fktest FOREIGN KEY (orderLineId) REFERENCES orderline_archive

        (id),

        INDEX FKA416A3C053E3B93F (orderLineId)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

salesorderdetails_archive

CREATE TABLE

    salesorderdetails_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        SKUno VARCHAR(100),

        COMMENT VARCHAR(250),

        createdBy VARCHAR(50),

        createdDate DATE,

        division VARCHAR(100),

        fiberPercent INT,

        lastModifiedBy VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        level VARCHAR(100),

        oracleItemNumber VARCHAR(50),

        soDetails VARCHAR(100),

        soNumber VARCHAR(100),

        sumOfFiberPercentage INT,

        typeSetter VARCHAR(100),

        variableDataValue VARCHAR(1000),

        variableFieldName VARCHAR(100),

        orderQueueId INT,

        salesOrderId INT,

        PRIMARY KEY (id),

        CONSTRAINT salesorderdetails_fkarchive FOREIGN KEY (salesOrderId) REFERENCES

        salesorderline_archive (id),

        INDEX FKD2E4E4033DF1C25 (orderQueueId),

        INDEX FKD2E4E40251BC313 (salesOrderId)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

salesorderline_archive

CREATE TABLE

    salesorderline_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        agreement VARCHAR(50),

        apoType VARCHAR(5),

        artworkAttachment VARCHAR(5),

        artworkHold VARCHAR(5),

        atoValidationFlag TINYINT(1),

        bankCharge VARCHAR(10),

        billToContact VARCHAR(250),

        billToEmail VARCHAR(250),

        billToFax VARCHAR(100),

        billToTel VARCHAR(250),

        bulkSampleValidationFlag TINYINT(1),

        COMMENT text,

        contractNumber VARCHAR(100),

        createdBy VARCHAR(50),

        createdDate DATETIME,

        csr VARCHAR(50),

        customerColorCode VARCHAR(250),

        customerColorDescription VARCHAR(250),

        customerCost VARCHAR(50),

        customerItemNumber VARCHAR(250),

        customerItemNumber1 VARCHAR(250),

        customerItemNumber2 VARCHAR(50),

        customerOrderedQty VARCHAR(50),

        customerPoFlag TINYINT(1),

        customerPoNumber VARCHAR(500),

        customerRequestDate DATETIME,

        customerSeason VARCHAR(50),

        customerSize VARCHAR(250),

        customerUnitPrice VARCHAR(50),

        customerUom VARCHAR(50),

        dateOrdered DATETIME,

        division VARCHAR(50),

        divisionForInterfaceErporg VARCHAR(10),

        duplicatePoFlag TINYINT(1),

        endCustomer VARCHAR(50),

        freightCharge VARCHAR(10),

        freightTerms VARCHAR(50),

        grpedOlid longtext,

        htlSizePageValidationFlag TINYINT(1),

        invoiceLineInstruction text,

        itemDescription VARCHAR(1000),

        lastModifiedBy VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        mandatoryVariableDataFieldFlag TINYINT(1),

        manufacturingNotes longtext,

        modelSerialNumber VARCHAR(50),

        moqValidationFlag TINYINT(1),

        oracleBillToSiteNumber VARCHAR(100),

        oracleExportId INT,

        oracleItemNumber VARCHAR(100),

        oracleShipToSiteNumber VARCHAR(100),

        orderBy VARCHAR(50),

        orderSource VARCHAR(100),

        orderType VARCHAR(200),

        orderdedQty VARCHAR(100),

        packingInstruction text,

        productionHold VARCHAR(5),

        promiseDate DATETIME,

        retailerPo_CustomerJob VARCHAR(100),

        sentToOracleDate DATETIME,

        shipToContact VARCHAR(250),

        shipToEmail VARCHAR(250),

        shipToFax VARCHAR(100),

        shipToTel VARCHAR(250),

        shippingHold VARCHAR(50),

        shippingInstructions text,

        shippingMethod VARCHAR(50),

        shippingOnlyNotes longtext,

        soldToRboNumber VARCHAR(50),

        splitShipSet VARCHAR(5),

        shipMark longtext,

        additionalLabelInternalItem longtext,

        targetSystem VARCHAR(50),

        systemUniqueId VARCHAR(100),

        systemUniqueIdLineNo VARCHAR(100),

        status VARCHAR(100),

        system_Status text,

        variableDataBreakdown longtext,

        waiveMOQ VARCHAR(5),

        orderQueueId INT,

        orderLineId INT,

        partnerId INT,

        rboId INT,

        productionLine VARCHAR(250),

        siteId VARCHAR(50),

        systemOrgCode VARCHAR(50),

        queryOrgCode VARCHAR(50),

        PRIMARY KEY (id),

        CONSTRAINT uidx UNIQUE (orderLineId),

        INDEX FK792504B633DF1C25 (orderQueueId),

        INDEX FK792504B6581734AB (partnerId),

        INDEX FK792504B66597E5D9 (rboId),

        INDEX FK792504B653E3B93F (orderLineId)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

Query for fetching the MAX id for Six months before records.

SELECT MAX(id) FROM orderemailqueue WHERE createdDate < curdate()-INTERVAL 180 DAY ORDER BY id DESC;

Queries for the Archiving the Records.(Sequence must be followed)

/* Inserting the archive data of orderemailqueue.The id will be fetch by above mentioned query */

INSERT INTO orderemailqueue_archive SELECT * FROM orderemailqueue WHERE id<=47895;

/* Inserting the archive data of orderfileattachment with respect to theorderemailqueue  */

INSERT INTO orderfileattachment_archive SELECT * FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue_archive);

/* Inserting the archive data of orderfilequeue with respect to orderfileattachment and its parent */

INSERT INTO orderfilequeue_archive SELECT * FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment_archive);

/* Inserting the archive data of orderline with respect to orderfilequeue and its parent */

INSERT INTO orderline_archive SELECT * FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue_archive)

/* Inserting the archive data of orderlinedetails with respect to orderline and its parent */

INSERT INTO orderlinedetails_archive SELECT * FROM orderlinedetails WHERE orderLineId =ANY(SELECT id FROM orderline_archive)

/* Inserting the archive data of orderline with respect to orderfilequeue and its parent */

INSERT INTO salesorderline_archive SELECT * FROM salesorderline WHERE orderLineId =ANY(SELECT id FROM orderline_archive);

/* Inserting the archive data of orderline with respect to orderfilequeue and its parent */

INSERT INTO salesorderdetails_archive SELECT * FROM salesorderdetails WHERE salesOrderId = ANY(SELECT id FROM salesorderline_archive)

/* Deleting the record from the table which were inserted in the Archive table */

DELETE FROM salesorderdetails WHERE id = ANY(SELECT id FROM salesorderdetails_archive);

DELETE FROM salesorderline WHERE id = ANY(SELECT id FROM salesorderline_archive);

DELETE FROM orderlinedetails WHERE id = ANY(SELECT id FROM orderlinedetails_archive);

DELETE FROM orderline WHERE id = ANY(SELECT id FROM orderline_archive);

DELETE FROM orderfilequeue WHERE id = ANY(SELECT id FROM orderfilequeue_archive);

DELETE FROM orderfileattachment WHERE id = ANY(SELECT id FROM orderfileattachment_archive);

DELETE FROM orderemailqueue WHERE id<=47895;

 

Select Query for the particular order

Select * from orderemailqueue_archive WHERE id=48497;

SELECT * FROM orderfileattachment_archive WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48497);

SELECT * FROM orderfilequeue_archive WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48497));

SELECT * FROM orderline_archive WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48497)));

SELECT * FROM orderlinedetails_archive WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48497))));

SELECT * FROM salesorderline_archive WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48497))));

SELECT * FROM salesorderdetails_archive WHERE salesOrderId = ANY(SELECT id FROM salesorderline WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48497)))));

 

Query For inserting the Individual record

Select * from orderemailqueue WHERE id=48559;

INSERT INTO orderemailqueue_archive SELECT * FROM orderemailqueue WHERE id=48559;

INSERT INTO orderfileattachment_archive SELECT * FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559);

INSERT INTO orderfilequeue_archive SELECT * FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559));

INSERT INTO orderline_archive SELECT * FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559)));

INSERT INTO orderlinedetails_archive SELECT * FROM orderlinedetails WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559))));

INSERT INTO salesorderline_archive SELECT * FROM salesorderline WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559))));

INSERT INTO salesorderdetails_archive SELECT * FROM salesorderdetails WHERE salesOrderId = ANY(SELECT id FROM salesorderline WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559)))));

DELETE FROM salesorderdetails WHERE salesOrderId = ANY(SELECT id FROM salesorderline WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559)))));

DELETE FROM salesorderline WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559))));

DELETE FROM orderlinedetails WHERE orderLineId =ANY(SELECT id FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559))));

DELETE FROM orderline WHERE orderQueueId = ANY(SELECT id FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559)));

DELETE FROM orderfilequeue WHERE orderFileAttachmentId = ANY(SELECT id FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559));

DELETE FROM orderfileattachment WHERE orderEmailQueueId = ANY(SELECT id FROM orderemailqueue WHERE id=48559);

DELETE FROM orderemailqueue WHERE id=48559

Query for creating table in QA

orderemailqueue_archive

CREATE TABLE

    orderemailqueue_archive

    (

        id INT NOT NULL AUTO_INCREMENT,

        acknowledgementDate DATETIME,

        assignCSR VARCHAR(100),

        assignee VARCHAR(100),

        ccMailId VARCHAR(1000),

        COMMENT VARCHAR(250),

        createdBy VARCHAR(50),

        createdDate DATETIME,

        lastModifiedBy VARCHAR(50),

        lastModifiedDate TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON

    UPDATE

        CURRENT_TIMESTAMP,

        mailBody longtext,

        orderMail TINYINT(1),

        orderSource VARCHAR(50),

        processId VARCHAR(100),

        readDate DATETIME,

        receivedDate DATETIME,

        senderEmailId VARCHAR(100),

        status VARCHAR(100),

        subject VARCHAR(1000),

        toMailId VARCHAR(500),

        emailSubjectProductLineMatch VARCHAR(250),

        emailSubjectRBOMatch VARCHAR(250),

        emailBodyProductLineMatch VARCHAR(100),

        emailBodyRBOMatch VARCHAR(100),

        emailSubjectPartnerMatch VARCHAR(250),

        siteId INT DEFAULT '0' NOT NULL,

        error longtext,

        systemCsrCode VARCHAR(100),

        PRIMARY KEY (id)

    )

    ENGINE=InnoDB DEFAULT CHARSET=utf8;

 

Archival Process Report

Archival Process Report

 

 

Related content

Cleanup and Archival
Cleanup and Archival
Adeptia Connect Help v3.7
More like this
Cleanup and Archival
Cleanup and Archival
Adeptia Connect Help v3.6
More like this