Sync PO Ex-Factory Split Status

1. Procedure Identity

• Procedure Name: uspSyncPOExFactorySplitStatus
• Database: SooperWizer
• Schema: Reports

2. Purpose & Functionality

• Business Purpose: This procedure performs a full refresh of the [Reports].[POExFactorySplitStatus] reporting table. It consolidates daily work order status data and packing plan information into a single, color-level dataset that supports ex-factory planning, shipment scheduling, and operational reporting.

• Functional Description:

1. Builds a temporary staging table (#TempPOSplitDataSetExfactoryWise) using a multi-CTE query.
2. Aggregates packing plan data (quantities, Ex-Factory dates, Handover dates, Ship types) from [Reports].[PackingPlan].
3. Aggregates size-level production progress from [Reports].[DailyWorkOrderStatus] across the full manufacturing pipeline.
4. Rolls up size-level data to color level (one row per work order + color combination).
5. LEFT JOINs production data with packing plan data to retain all production records even where no packing plan exists.
6. Truncates [Reports].[POExFactorySplitStatus] and reloads it entirely from the staging table.
7. Returns a success/failure message with a status code.

• Return Values / Effects:
  • Populates [Reports].[POExFactorySplitStatus] with up-to-date production and packing metrics.
  • No explicit return value; completion indicates success.
  • Errors are raised if critical operations fail.

3. Parameters

This procedure takes no input parameters

4. Dependencies

• Base Tables:
  • [Reports].[POExFactorySplitStatus] (TRUNCATE, INSERT) - Final reporting target table
  • [Reports].[PackingPlan] (SELECT) - Source of packing quantities and shipment dates
  • [Reports].[DailyWorkOrderStatus] (SELECT) - Source of production pipeline quantities
• Temporary Objects:
  • #TempPOSplitDataSetExfactoryWise - Staging table holding the final dataset before truncate & reload
• Referenced Objects:
  • Job : Job_To_Sync_Daily_WorkOrder_Status_Every_11_Minutes

5. Technical Implementation

T-SQL Code

CREATE PROCEDURE [Reports].[uspSyncPOExFactorySplitStatus]
AS
BEGIN
  SET NOCOUNT ON;
  SET XACT_ABORT ON;

  BEGIN TRY
    BEGIN TRANSACTION

    DROP TABLE
    IF EXISTS #TempPOSplitDataSetExfactoryWise;;
      WITH PackingPlan
      AS (
        SELECT
                     WorkOrderCode, StyleName, BuyMonth ,PurchaseOrderCode, FactoryName, BranchName ,ColorCode,
                     ColorName, StoreDetail, ShipType, ExFactoryDate, HandOverDate, SUM(OrderQuantity) AS OrderQuantity
          ,SUM(PackedQty) AS PackedQty
        FROM (
          SELECT DISTINCT
                        WorkOrderCode, StyleName, BuyMonth, PurchaseOrderCode, FactoryName, BranchName, ColorCode,
                        ColorName, SizeCode, SizeName, StoreDetail, ShipType, ExFactoryDate, HandOverDate, OrderQuantity
            ,PackedQty
          FROM [Reports].[PackingPlan]
          ) f
        GROUP BY
                    WorkOrderCode, StyleName, BuyMonth, PurchaseOrderCode, FactoryName, BranchName, ColorCode,
                    ColorName, StoreDetail, ShipType, ExFactoryDate, HandOverDate
        )
        ,OrderStatsSizeWise
      AS (
        SELECT
                    WorkOrderCode, CustomerCode, CustomerName, BuyMonth, StyleName, PurchaseOrderCode ,ColorCode,
                    ColorName, FactoryCode, FactoryName, BranchCode, BranchName, SizeName, OrderQuantity
          ,MAX(CASE
              WHEN SewingLastOperationQuantity != 0
                OR EndLineQualityPassedQuantity != 0
                THEN StatusDate
              ELSE NULL
              END) AS SendDownDate
          ,SUM(SyncedQuantity) AS CutQuantity
          ,SUM(CuttingOutQuantity) AS InputQuantity
          ,SUM(InductionQuantity) AS InductionQuantity
          ,SUM(EndLineQualityPassedQuantity) AS EndLineQualityPassedQuantity
          ,SUM(EndLineQualityRejectedQuantity) AS EndLineQualityRejectedQuantity
          ,SUM(SendDownQuantity) AS SendDownQuantity
          ,SUM(SewingLastOperationQuantity) AS SewingLastOperationQuantity
          ,SUM(FinishingQualityPassQuantity) AS FinishingQualityPassQuantity
          ,SUM(FinishingQualityRejectionQuantity) AS FinishingQualityRejectionQuantity
          ,0 AS RecordedSendDownQuantity
          ,0 AS RecordedLostQuantity
        FROM [Reports].[DailyWorkOrderStatus]
        GROUP BY
                    WorkOrderCode, CustomerCode, CustomerName, BuyMonth, StyleName, PurchaseOrderCode, ColorCode,
                    ColorName, FactoryCode, FactoryName, BranchCode, BranchName, SizeName, OrderQuantity
        )
        ,OrderStatsColorWise
      AS (
        SELECT
                    WorkOrderCode, CustomerCode, CustomerName, BuyMonth, StyleName, PurchaseOrderCode, ColorCode,
                    ColorName, FactoryCode, FactoryName, BranchCode, BranchName
          ,SUM(OrderQuantity) AS OrderQuantity
          ,MAX(SendDownDate) AS SendDownDate
          ,SUM(CutQuantity) AS TotalCutQuantity
          ,SUM(InputQuantity) AS TotalInputQuantity
          ,SUM(InductionQuantity) AS TotalInductionQuantity
          ,SUM(EndLineQualityPassedQuantity) AS TotalEndLineQualityPassedQuantity
          ,SUM(EndLineQualityRejectedQuantity) AS TotalEndLineQualityRejectedQuantity
          ,SUM(SendDownQuantity) AS TotalSendDownQuantity
          ,SUM(SewingLastOperationQuantity) AS TotalSewingLastOperationQuantity
          ,SUM(FinishingQualityPassQuantity) AS TotalFinishingQualityPassQuantity
          ,SUM(FinishingQualityRejectionQuantity) AS TotalFinishingQualityRejectionQuantity
          ,0 AS TotalRecordedSendDownQuantity
          ,0 AS TotalRecordedLostQuantity
        FROM OrderStatsSizeWise
        GROUP BY
                    WorkOrderCode, CustomerCode, CustomerName, BuyMonth, StyleName, PurchaseOrderCode, ColorCode,
                    ColorName, FactoryCode, FactoryName, BranchCode, BranchName
        )
        ,FinalDataSet
      AS (
        SELECT
                    ocs.WorkOrderCode, ocs.CustomerCode, ocs.CustomerName, ocs.StyleName, ocs.BuyMonth,
                    ocs.PurchaseOrderCode, ocs.FactoryName, ocs.BranchName, ocs.ColorCode, ocs.ColorName,
                    pp.StoreDetail, pp.ShipType, pp.ExFactoryDate, pp.HandOverDate, ocs.SendDownDate,
                    ocs.OrderQuantity, pp.PackedQty, ocs.TotalCutQuantity, ocs.TotalInputQuantity,
                    ocs.TotalInductionQuantity, TotalSewingLastOperationQuantity, TotalEndLineQualityPassedQuantity,TotalEndLineQualityRejectedQuantity, TotalFinishingQualityPassQuantity,
                    TotalFinishingQualityRejectionQuantity, TotalRecordedSendDownQuantity, TotalRecordedLostQuantity
        FROM OrderStatsColorWise ocs
        LEFT JOIN PackingPlan pp ON pp.WorkOrderCode = ocs.WorkOrderCode
          AND pp.StyleName = ocs.StyleName
          AND pp.BuyMonth = ocs.BuyMonth
          AND pp.FactoryName = ocs.FactoryName
          AND pp.BranchName = ocs.BranchName
          AND pp.ColorCode = ocs.ColorCode
        )
      SELECT *
      INTO #TempPOSplitDataSetExfactoryWise
      FROM FinalDataSet

    TRUNCATE TABLE [Reports].[POExFactorySplitStatus];

    INSERT INTO [Reports].[POExFactorySplitStatus] (
      WorkOrderCode, CustomerCode, CustomerName, StyleName, BuyMonth, PurchaseOrderCode, FactoryName,
            BranchName, ColorCode, ColorName, StoreDetail, ShipType, ExFactoryDate, HandOverDate, SendDownDate,
            OrderQuantity, PackedQty, TotalCutQuantity, TotalInputQuantity, TotalInductionQuantity,
            TotalSewingLastOperationQuantity, TotalEndLineQualityPassedQuantity, TotalEndLineQualityRejectedQuantity,TotalFinishingQualityPassQuantity, TotalFinishingQualityRejectionQuantity, TotalRecordedSendDownQuantity
      ,TotalRecordedLostQuantity
      )
    SELECT
            WorkOrderCode, CustomerCode, CustomerName, StyleName, BuyMonth, PurchaseOrderCode, FactoryName,
            BranchName, ColorCode, ColorName, StoreDetail, ShipType, ExFactoryDate, HandOverDate,SendDownDate,
            ISNULL(OrderQuantity, 0) AS OrderQuantity
      ,ISNULL(PackedQty, 0) AS PackedQty
      ,ISNULL(TotalCutQuantity, 0) AS TotalCutQuantity
      ,ISNULL(TotalInputQuantity, 0) AS TotalInputQuantity
      ,ISNULL(TotalInductionQuantity, 0) AS TotalInductionQuantity
      ,ISNULL(TotalSewingLastOperationQuantity, 0) AS TotalSewingLastOperationQuantity
      ,ISNULL(TotalEndLineQualityPassedQuantity, 0) AS TotalEndLineQualityPassedQuantity
      ,ISNULL(TotalEndLineQualityRejectedQuantity, 0) AS TotalEndLineQualityRejectedQuantity
      ,ISNULL(TotalFinishingQualityPassQuantity, 0) AS TotalFinishingQualityPassQuantity
      ,ISNULL(TotalFinishingQualityRejectionQuantity, 0) AS TotalFinishingQualityRejectionQuantity
      ,ISNULL(TotalRecordedSendDownQuantity, 0) AS TotalRecordedSendDownQuantity
      ,ISNULL(TotalRecordedLostQuantity, 0) AS TotalRecordedLostQuantity
    FROM #TempPOSplitDataSetExfactoryWise;

    COMMIT TRANSACTION

    SELECT 'Data Processed Successfully !.' AS Message
      ,0 AS Code;
  END TRY

  BEGIN CATCH
    IF @@TRANCOUNT > 0
      ROLLBACK TRANSACTION;

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

CTE Logic Explanation

1. PackingPlan: De-duplicates size rows from [Reports].[PackingPlan] and aggregates OrderQuantity and PackedQty to color level, grouped by ExFactoryDate and HandOverDate.
2. OrderStatsSizeWise: Reads [Reports].[DailyWorkOrderStatus] and sums all production pipeline quantities at the size level. Also captures the latest SendDownDate (last date sewing or end-line activity was recorded).
3. OrderStatsColorWise: Rolls up OrderStatsSizeWise to color level by summing all quantity columns and taking MAX(SendDownDate). Produces one row per WorkOrderCode + ColorCode.
4. FinalDataSet: LEFT JOINs OrderStatsColorWise with PackingPlan on WorkOrderCode, StyleName, BuyMonth, FactoryName, BranchName, and ColorCode. All production records are retained even where no packing plan entry exists.

Performance Considerations

• Indexes Used:
  • Clustered/default indexes on POExFactorySplitStatus.
  • Recommended: indexes on DailyWorkOrderStatus (WorkOrderCode, PurchaseOrderCode, ColorCode) and PackingPlan (WorkOrderCode, ColorCode).
• Potential Bottlenecks:
  • Large SUM/MAX aggregations over DailyWorkOrderStatus and PackingPlan.
  • Multiple CTEs with GROUP BY.
  • TRUNCATE + full reload on every run.
• Transaction Handling:
  • Entire operation (temp table build, TRUNCATE, INSERT) is wrapped in a single explicit transaction. A failure at any point rolls back completely, protecting the target table.
• Optimization:
  • Pre-aggregation in CTEs reduces repeated scans. Single INSERT INTO … SELECT minimizes round-trips. ISNULL at insert time avoids downstream NULL issues.

6. Usage Information

Calling Jobs

• Job : Job_To_Sync_Daily_WorkOrder_Status_Every_11_Minutes

Calling Applications

• SQL Server Agent (scheduled execution)

Usage Examples

-- Standard execution (most common)
EXEC [Reports].[uspSyncPOExFactorySplitStatus];

-- Verify data in target table after execution
SELECT TOP 10
    WorkOrderCode, PurchaseOrderCode, ColorName,
    ExFactoryDate, OrderQuantity, PackedQty
FROM [Reports].[POExFactorySplitStatus]
ORDER BY ExFactoryDate DESC;

Expected Results

• Success Scenario:
  • Data Aggregation: Production, packing, and order data aggregated into POExFactorySplitStatus.
  • Correct Totals: Totals for cut, input, induction, quality passed, sewing, finishing, and end-line rejections correctly calculated.
  • Packing Linkage: Packed quantities and handover dates correctly linked from PackingPlan via LEFT JOIN.
  • Null Safety: All numeric fields are zero-filled via ISNULL; no NULL quantities in output.
  • Return Value: Result set returns ‘Data Processed Successfully !.’ with Code = 0.
• No Data Scenario:
  • Procedure completes successfully with no changes if source tables contain no relevant records. The target table is truncated but no rows are inserted.
• Error Scenarios:
  • Procedure fails and transaction is rolled back if any SQL error occurs, including data integrity issues, remote database unavailability, or system problems. The result set returns the SQL error message and error number.

7. Data Quality & Integrity

Null Value Handling

• Numeric Columns: All numeric quantity columns are wrapped in ISNULL(…, 0) before insert, ensuring zero values instead of NULLs.
• Packing Columns (PackedQty, HandOverDate, StoreDetail, ShipType, ExFactoryDate): May be NULL if no matching packing plan record exists (LEFT JOIN with PackingPlan).
• SendDownDate: May be NULL if no sewing or end-line quality quantities have been recorded for the work order.

Validation Rules

• WorkOrderCode, PurchaseOrderCode, StyleName: Must exist and be consistent across production and packing datasets for JOIN to resolve correctly.
• ExFactoryDate, BuyMonth: Must be valid dates in source tables.
• Quantity Fields: TotalCutQuantity, TotalInputQuantity, TotalInductionQuantity, TotalEndLineQualityPassedQuantity, TotalSewingLastOperationQuantity, TotalFinishingQualityPassQuantity, TotalEndLineQualityRejectedQuantity, TotalFinishingQualityRejectionQuantity, PackedQty must be numeric and non-negative.
• CustomerCode: Must exist in the DailyWorkOrderStatus source; no explicit FK validation enforced in this procedure.
• ColorCode + ColorName: Used as part of JOIN key between packing and production — must be consistent across both source tables.

8. Monitoring & Troubleshooting

• Performance Metrics:
  • Execution Time: Monitor end-to-end execution time, particularly for large datasets with high PO/color cardinality.
  • Rows Processed: Track row counts in #TempPOSplitDataSetExfactoryWise and final INSERT into POExFactorySplitStatus.
  • Aggregation Efficiency: Monitor CTE performance across OrderStatsSizeWise and OrderStatsColorWise aggregations.
• Data Quality Checks: Validate consistency between production and packing quantities
• Error Handling: Monitor for SQL errors, remote database access issues, and aggregation failures
• Maintenance Requirements: Regular index maintenance, statistics updates, and monitoring of remote database connections for optimal performance