Recovery

Overview

The stored procedure [dbo].[uspFetchRecoveryDataSet] retrieves data from multiple tables in the [Essentials] schema in [NCLSooperWizerReporting] database and updates a central table called [dbo].[RecoveryDataSet].

It performs checks and uses a temporary table to hold intermediate data before updating and inserting records into the main dataset.

1. Drop Existing Temporary Table: Checks if the temporary table #TempRecoveryDataSet exists and drops it if it does.
2. Create Temporary Table: Defines a new temporary table to hold the intermediate data.
3. Insert Data into Temporary Table: Queries data from several source tables and populates the temporary table.
4. Update Existing Records: Updates existing records in the RecoveryDataSet with values from the temporary table based on matching PieceID.
5. Insert New Records: Inserts new records from the temporary table into the RecoveryDataSet if they do not already exist.
6. Transaction and Error Handling: Uses transactions to ensure atomicity and handles errors with a TRY-CATCH block.

Dependencies

The procedure relies on the following tables from the [Essentials] schema:

• [Essentials].[PieceWiseCutReport]: Contains details related to pieces including recovery dates.
• [Essentials].[CutReport]: Provides information about cut reports, including BundleID, PO, and Color.
• [Essentials].[CutJob]: Contains job-specific details like StyleNo, BuyMonth, and CutNo.
• [Essentials].[Line]: Contains production line details such as LineCode and LineDescription.
• [dbo].[RecoveryDataSet]: The target table where data is updated and new records are inserted.

Parameters

This procedure does not accept any input parameters.

Query Breakdown

---

1. Drop Existing Temporary Table

Checks for the existence of the temporary table #TempRecoveryDataSet and drops it if found to ensure a clean start.

IF OBJECT_ID('tempdb..#TempRecoveryDataSet') IS NOT NULL
BEGIN
    DROP TABLE #TempRecoveryDataSet;
END

2. Create Temporary Table

Creates a temporary table #TempRecoveryDataSet to hold intermediate results with the following structure:

CREATE TABLE #TempRecoveryDataSet (
    StyleNo NVARCHAR(50),
    BuyMonth NVARCHAR(50),
    CompanyId INT,
    OrId NVARCHAR(50),
    PO NVARCHAR(50),
    CutNo NVARCHAR(50),
    ColorCode NVARCHAR(50),
    Color NVARCHAR(50),
    Size NVARCHAR(50),
    BundleID NVARCHAR(50),
    BundleCode NVARCHAR(50),
    PieceID INT,
    PieceNumber NVARCHAR(50),
    Grade NVARCHAR(50),
    LineID INT,
    LineCode NVARCHAR(50),
    LineDescription NVARCHAR(100),
    BrId INT,
    SvisorId INT,
    Department NVARCHAR(50),
    RecoveryReceivedDate DATETIME,
    RecoveryDate DATETIME,
    IssuanceDate DATETIME,
    OrderCompanyId INT
);

3. Insert Data into Temporary Table

Inserts relevant data into #TempRecoveryDataSet using a SELECT statement that joins the necessary tables.

INSERT INTO #TempRecoveryDataSet (
    StyleNo, BuyMonth, CompanyId, OrId, PO, CutNo, ColorCode, Color, Size, BundleID, BundleCode, PieceID,
    PieceNumber, Grade, LineID, LineCode, LineDescription, BrId, SvisorId, Department, RecoveryReceivedDate,
    RecoveryDate, IssuanceDate, OrderCompanyId
)
SELECT
    cj.StyleNo, cj.BuyMonth, l.CompanyId, cr.OrId, cr.PO, cj.CutNo, cr.ColorCode, cr.Color, cr.Size,
    cr.BundleID, cr.BundleCode, pwcr.PieceID, pwcr.PieceNumber, pwcr.Grade, pwcr.LineID,
    l.LineCode, l.LineDescription, l.BrId, l.SvisorId, pwcr.Department,
    pwcr.RecoveryReceivedDate, pwcr.RecoveryDate, pwcr.IssuanceDate, cr.CompanyId AS OrderCompanyId
FROM
    [NCLSoopreWizerReporting].[Essentials].[PieceWiseCutReport] pwcr
JOIN
    [NCLSoopreWizerReporting].[Essentials].[CutReport] cr ON cr.BundleID = pwcr.BundleID
JOIN
    [NCLSoopreWizerReporting].[Essentials].[CutJob] cj ON cj.CutJobID = cr.CutJobID
LEFT JOIN
    [NCLSoopreWizerReporting].[Essentials].[Line] l ON l.LineID = pwcr.LineID
WHERE
    pwcr.RecoveryReceivedDate IS NOT NULL
    AND (pwcr.LineID IS NOT NULL AND pwcr.LineID != 0);

• Join Conditions:

  • Joins PieceWiseCutReport with CutReport on BundleID.
  • Joins CutReport with CutJob on CutJobID.
  • Left joins Line on LineID.

• Filter Conditions:

  • Only records with a non-null RecoveryReceivedDate.
  • Ensures LineID is not null or zero.

4. Update Existing Records

Updates existing records in [dbo].[RecoveryDataSet] based on the values from the temporary table where PieceID matches.

UPDATE T
SET
    T.Grade = S.Grade,
    T.LineID = S.LineID,
    T.LineCode = S.LineCode,
    T.LineDescription = S.LineDescription,
    T.BrId = S.BrId,
    T.SvisorId = S.SvisorId,
    T.Department = S.Department,
    T.RecoveryReceivedDate = S.RecoveryReceivedDate,
    T.RecoveryDate = S.RecoveryDate,
    T.IssuanceDate = S.IssuanceDate
FROM
    [dbo].[RecoveryDataSet] AS T
JOIN #TempRecoveryDataSet AS S ON T.PieceID = S.PieceID;

• Updated Fields:
  • Grade, LineID, LineCode, LineDescription, BrId, SvisorId, Department, RecoveryReceivedDate, RecoveryDate, IssuanceDate.

5. Insert New Records

Inserts new records from the temporary table into the main [dbo].[RecoveryDataSet] table where no matching PieceID exists.

INSERT INTO [dbo].[RecoveryDataSet] (
    StyleNo, BuyMonth, CompanyId, OrId, PO, CutNo, ColorCode, Color, Size, BundleID, BundleCode, PieceID,
    PieceNumber, Grade, LineID, LineCode, LineDescription, BrId, SvisorId, Department, RecoveryReceivedDate,
    RecoveryDate, IssuanceDate, OrderCompanyId
)
SELECT
    S.StyleNo, S.BuyMonth, S.CompanyId, S.OrId, S.PO, S.CutNo, S.ColorCode, S.Color, S.Size, S.BundleID,
    S.BundleCode, S.PieceID, S.PieceNumber, S.Grade, S.LineID, S.LineCode, S.LineDescription,
    S.BrId, S.SvisorId, S.Department, S.RecoveryReceivedDate, S.RecoveryDate, S.IssuanceDate, S.OrderCompanyId
FROM #TempRecoveryDataSet AS S
WHERE NOT EXISTS (
    SELECT 1
    FROM [dbo].[RecoveryDataSet] T
    WHERE T.PieceID = S.PieceID
);

6. Transaction Management

The entire operation is wrapped in a transaction. If the procedure completes successfully, it commits the transaction. If an error occurs, it rolls back the transaction.

BEGIN TRY
    BEGIN TRANSACTION;

    -- Steps 1 to 5

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
    BEGIN
        ROLLBACK TRANSACTION;
    END

    SELECT ERROR_MESSAGE() AS [Message], ERROR_NUMBER() AS Code;
END CATCH;

7. Success Message

Upon successful completion, the procedure returns:

• Message: 'Data Fetched Successfully!'
• Code: 0

Error Handling

The TRY...CATCH block captures any errors during execution:

• Error Handling Actions:
  • Rolls back the transaction if an error occurs.
  • Returns the error message and error number for debugging.

Example Usage

To execute the procedure:

EXEC [dbo].[uspFetchRecoveryDataSet];

Summary

The stored procedure [dbo].[uspFetchRecoveryDataSet] efficiently manages the retrieval and updating of recovery data by utilizing a temporary table for intermediate storage, ensuring data integrity and providing robust error handling throughout its execution.