Create Audit Form Data

1. Procedure Identity

• Procedure Name: Sp_DataSetQuery_Quality_AuditFormReport
• Database: SooperWizer
• Schema: dbo
• Version: 1.2 (Last Modified: 2026-06-04)

2. Purpose & Functionality

• Business Purpose:

  Builds and maintains a persistent audit form reporting dataset by querying quality inspection session data from the last 120 days, enriched with worker, machine, operation, bundle, order, and fault details.

• Functional Description:

  • Queries Quality.AuditFormSession for all sessions created within the last 120 days (using a UTC+7 offset).
  • Joins the session data with multiple dimension tables to enrich it with worker info, machine info, operation info, line/section info, bundle/cut job/purchase order/style info, fault details (code, classification, severity), and the creating user.
  • Stores the enriched result in a temporary table #AuditFormDataSet.
  • Checks the current server hour:
    • Between 1 AM and 2 AM: Performs a full refresh — truncates the persistent table dbo.AuditFormDataSet and reloads it entirely from the temp table (or creates it if it doesn’t exist).
    • All other hours: Performs an incremental refresh — deletes records from the last 3 days (UTC+7) from the persistent table and re-inserts them from the temp table (or creates the table if it doesn’t exist).
  • Drops the temporary table #AuditFormDataSet at the end.

• Return Values:

  • This procedure does not return a result set to the caller. It writes data into the table dbo.AuditFormDataSet.

3. Parameters

This procedure does not take any input parameters.

4. Dependencies

Base Tables

Table Name	Schema	Action(s) in Procedure	Notes
AuditFormSession	Quality	SELECT (source)	Primary source table; drives all joins
AuditFormFaultLog	Quality	SELECT (LEFT JOIN)	Provides fault log entries per session
Worker	Essentials	SELECT (LEFT JOIN)	Provides worker code, name, and type
Machine	Essentials	SELECT (LEFT JOIN)	Provides machine code and name
Operation	Essentials	SELECT (LEFT JOIN)	Provides operation code, name, and type
Line	Essentials	SELECT (LEFT JOIN)	Provides line code and name
Section	Essentials	SELECT (LEFT JOIN)	Provides section code and name
Color	Essentials	SELECT (LEFT JOIN)	Provides color code and name
Size	Essentials	SELECT (LEFT JOIN)	Provides size code and name
Fault	Essentials	SELECT (LEFT JOIN)	Provides fault code and name
FaultClassification	Essentials	SELECT (LEFT JOIN)	Provides fault classification name
FaultSeverity	Essentials	SELECT (LEFT JOIN)	Provides fault severity level
Bundle	Cutting	SELECT (LEFT JOIN)	Provides bundle code and links to order/style
CutJob	Cutting	SELECT (LEFT JOIN)	Provides cut job code
PurchaseOrder	Orders	SELECT (LEFT JOIN)	Provides PO code and production status
PurchaseOrderWorkOrderStyle	Orders	SELECT (LEFT JOIN)	Bridge table linking bundles to work order styles
WorkOrderStyle	Orders	SELECT (LEFT JOIN)	Links work order to style
WorkOrder	Orders	SELECT (LEFT JOIN)	Provides work order code
Style	Orders	SELECT (LEFT JOIN)	Provides style name
USER_ENTITY	auth	SELECT (LEFT JOIN)	Provides username of the session creator
AuditFormDataSet	dbo	INSERT, DELETE, TRUNCATE, SELECT INTO	Persistent output/reporting table; created if not exists

• Referenced Objects: None (self-contained procedure)
• Inter-Database Dependencies: None
• Server Dependencies: None

5. Technical Implementation

T-SQL Code

CREATE OR ALTER PROCEDURE [dbo].[Sp_DataSetQuery_Quality_AuditFormReport]
AS
BEGIN
  SELECT
    afs.[AuditFormSessionID] ,afs.[WorkerID], W.WorkerCode, W.WorkerName as WorkerDescription, W.WorkerType,
    afs.[MachineID], M.MachineCode, M.MachineName as MachineDescription,
    afs.[OperationID], O.OperationCode, O.OperationName as OperationDescription, O.OperationType,
    afs.[LineID], L.LineCode, L.LineName as LineDescription,
    afs.[SectionID], S.SectionCode, S.SectionName as SectionDescription,
    afs.[BundleID], B.BundleCode, CJ.CutJobCode, PO.PurchaseOrderCode, PO.ProductionStatus, C.ColorCode, C.ColorName, SI.SizeCode, SI.SizeName, WO.WorkOrderCode, ST.StyleName,
    afs.[MachineRound], afs.[FollowUp], afs.[DefectedPieces], afs.[RoundColor],
    afs.[CreatedBy], UE.USERNAME, afs.[UpdatedBy],
    DATEADD(HOUR, 7, afs.[CreatedAt]) as ScanningTime,
    DATEADD(HOUR, 7, afs.[UpdatedAt]) UpdatedAt,
    CONVERT(DATE, DATEADD(HOUR, 7, afs.[CreatedAt])) as [CreatedAtDate],
    COALESCE(AFF.FaultID,0) AS FaultID, COALESCE(f.FaultCode, '000') AS FaultCode, COALESCE(F.FaultName, '000') AS FaultDescription,
    FC.ClassificationName AS FaultClassificationName, ISNULL(AFF.FaultCount,0) as FaultCount,
    FS.SeverityLevel AS FaultSeverityLevel, DATEADD(HOUR, 7, aff.CreatedAt) as [DefectTime]
  INTO #AuditFormDataSet
  FROM  [Quality].[AuditFormSession] afs
  LEFT JOIN Essentials.Worker W ON AFS.WorkerID=W.WorkerID
  LEFT JOIN Essentials.Operation O ON AFS.OperationID=O.OperationID
  LEFT JOIN Essentials.Line L ON AFS.[LineID]=L.[LineID]
  LEFT JOIN Essentials.Section S ON AFS.[SectionID]=S.[SectionID]
  LEFT JOIN Essentials.Machine M ON AFS.[MachineID]=M.[MachineID]
  LEFT JOIN Cutting.Bundle B ON AFS.[BundleID]=B.[BundleID]
  LEFT JOIN Cutting.CutJob CJ ON CJ.CutJobID=B.CutJobID
  LEFT JOIN Orders.PurchaseOrder PO ON PO.PurchaseOrderID=B.PurchaseOrderID
  LEFT JOIN Essentials.Color C ON C.ColorID=B.ColorID
  LEFT JOIN Essentials.Size SI ON SI.SizeID=B.SizeID
  LEFT JOIN Orders.PurchaseOrderWorkOrderStyle POWS ON POWS.PurchaseOrderWorkOrderStyleID=B.PurchaseOrderWorkOrderStyleID
  LEFT JOIN Orders.WorkOrderStyle WOS ON WOS.WorkOrderStyleID=POWS.WorkOrderStyleID
  LEFT JOIN Orders.Style ST ON WOS.StyleID=ST.StyleID
  LEFT JOIN Orders.WorkOrder WO ON WO.WorkOrderID= WOS.WorkOrderID
  LEFT JOIN [Quality].AuditFormFaultLog AFF ON AFF.AuditFormSessionID= afs.AuditFormSessionID
  LEFT JOIN Essentials.Fault F ON F.FaultID=AFF.FaultID
  LEFT JOIN Essentials.FaultClassification FC ON F.FaultClassificationID=FC.FaultClassificationID
  LEFT JOIN Essentials.FaultSeverity FS  ON FS.FaultSeverityID=F.FaultSeverityID
  LEFT JOIN [auth].[USER_ENTITY] UE ON UE.ID= afs.CreatedBy
  WHERE afs.CreatedAtDate>= CONVERT(DATE,GETDATE()-120)
  IF DATEPART(HOUR, GETDATE()) = 1
  BEGIN
    -- Only between 1 AM and 2 AM
    IF OBJECT_ID('AuditFormDataSet', 'U') IS NOT NULL
    BEGIN
        TRUNCATE TABLE AuditFormDataSet;
        INSERT INTO AuditFormDataSet
        SELECT * FROM #AuditFormDataSet;
    END
    ELSE
    BEGIN
        SELECT * INTO AuditFormDataSet
        FROM #AuditFormDataSet;
    END
  END
  ELSE
  BEGIN
    -- All other hours
    IF OBJECT_ID('AuditFormDataSet', 'U') IS NOT NULL
    BEGIN
        DELETE FROM AuditFormDataSet
        WHERE AuditFormSessionID IN (
            SELECT AuditFormSessionID
            FROM #AuditFormDataSet
            WHERE CreatedAtDate > CONVERT(DATE, DATEADD(HOUR, 7, GETDATE() - 3))
        );
        INSERT INTO AuditFormDataSet
        SELECT *
        FROM #AuditFormDataSet
        WHERE CreatedAtDate > CONVERT(DATE, DATEADD(HOUR, 7, GETDATE() - 3));
    END
    ELSE
    BEGIN
        SELECT * INTO AuditFormDataSet
        FROM #AuditFormDataSet;
    END
  END
  DROP TABLE #AuditFormDataSet;
END

Performance Considerations

• Indexes Used: None
• Potential Bottlenecks:
  • The initial SELECT spans 20 tables via LEFT JOINs, which can be expensive on large datasets.
  • The 120-day rolling window on AuditFormSession.CreatedAtDate may return a large number of rows.
• Transaction Handling: N/A

6. Usage Information

Calling Jobs

• Update AuditFormDataSet - Every 30 Minutes
• Manual execution for data recovery or catch-up processing

Calling Applications

• SQL Server Agent (scheduled execution)
• Manufacturing dashboard refresh processes
• Data recovery and maintenance scripts

Usage Examples

-- Execute the procedure manually
EXEC [dbo].[Sp_DataSetQuery_Quality_AuditFormReport];

-- Query the resulting persistent dataset after execution
SELECT * FROM [dbo].[AuditFormDataSet];

-- Check record count after execution
SELECT COUNT(*) AS TotalRecords FROM [dbo].[AuditFormDataSet];

Expected Results

• Success Scenario: The persistent table dbo.AuditFormDataSet is created (if it didn’t exist) or updated with enriched audit form session records.
• No Data Scenario: Procedure completes successfully with no changes if no audit form records exist within the last 120 days.
• Error Scenarios: Procedure fails with SQL error if master data integrity issues or system problems occur.