Skip to content
WiMetrix Docs

Style Integration

This procedure is designed to synchronize style information between the SooperWizer database and the ERP system, inserting any missing data into the ParentStyleTemplate and StyleTemplate tables.

1. Drop Temporary Table if Exists

Section titled “1. Drop Temporary Table if Exists”
DROP TABLE IF EXISTS #StyleInfoFromERP;
  • Explanation: This line ensures that any previous instance of the temporary table #StyleInfoFromERP is dropped before creating a new one. It prevents potential conflicts from leftover data.

2. Fetch Style Data from ERP

Section titled “2. Fetch Style Data from ERP”
WITH StyleInfoInTarget AS (
    SELECT
        DISTINCT pst.ParentStyleTemplateDescription AS StyleNo,
        st.StyleTemplateCode AS DocNo,
        stb.RevNo,
        stb.LayoutDep,
        st.ModificationDate
    FROM
        [Essentials].[ParentStyleTemplate] pst
    JOIN
        [Essentials].[StyleTemplate] st
        ON st.ParentStyleTemplateID = pst.ParentStyleTemplateID
    JOIN
        [Essentials].[StyleBulletin] stb
        ON stb.StyleTemplateID = st.StyleTemplateID AND stb.IsActive = 1
)
SELECT
    DISTINCT sv.* INTO #StyleInfoFromERP
FROM
    [NCPMS].[dbo].[style_vw] sv
LEFT JOIN
    StyleInfoInTarget sit
    ON sit.StyleNo = sv.StyleNo
    AND sit.DocNo = sv.DocNo
    AND sit.RevNo = sv.RevNo
    AND TRIM(sit.LayoutDep) = TRIM(sv.LayoutDep)
    AND sit.ModificationDate = sv.StyleModificationDate
WHERE
    sit.StyleNo IS NULL
    AND sit.DocNo IS NULL
    AND sit.RevNo IS NULL
    AND sit.LayoutDep IS NULL
    AND sit.ModificationDate IS NULL
    AND sv.OperationDesc NOT LIKE '(%';

  • Purpose: This CTE (StyleInfoInTarget) retrieves distinct style information from the SooperWizer database, including:

    • StyleNo: Parent style description.
    • DocNo: Document number (style code).
    • RevNo: Revision number.
    • LayoutDep: Layout department.
    • ModificationDate: Date when the style was last modified.

  • Joins:

    • ParentStyleTemplate to StyleTemplate: Joins ParentStyleTemplate and StyleTemplate tables by ParentStyleTemplateID.
    • StyleTemplate to StyleBulletin: Filters on active style bulletins with a join on StyleTemplateID and the IsActive = 1 condition.

  • Explanation: The query pulls style data from the style_vw view in the NCPMS database. It identifies styles that are not already present in the target database (StyleInfoInTarget), using a LEFT JOIN to compare:

    • StyleNo
    • DocNo
    • RevNo
    • LayoutDep
    • ModificationDate

  • Condition: The query filters out any rows where a match is found in StyleInfoInTarget by checking that all key fields are NULL. Additionally, rows with operation descriptions containing parentheses are excluded.

  • Action: The distinct results are inserted into a temporary table #StyleInfoFromERP for further processing.

3. Insert Non-Existing Style Numbers (ParentStyleTemplate)

Section titled “3. Insert Non-Existing Style Numbers (ParentStyleTemplate)”
INSERT INTO [Essentials].[ParentStyleTemplate] (ParentStyleTemplateDescription)
SELECT
    sie.StyleNo
FROM
    #StyleInfoFromERP sie
LEFT JOIN
    [Essentials].[ParentStyleTemplate] pst
    ON pst.ParentStyleTemplateDescription = sie.StyleNo
WHERE
    pst.ParentStyleTemplateDescription IS NULL
GROUP BY
    sie.StyleNo;

  • Purpose: This query inserts new styles into the ParentStyleTemplate table that do not already exist.

  • Explanation:

    • It uses a LEFT JOIN to check if the StyleNo from #StyleInfoFromERP already exists in the ParentStyleTemplate table.
    • If the style does not exist (NULL condition), the style is inserted.

4. Insert Non-Existing Document Numbers (StyleTemplate)

Section titled “4. Insert Non-Existing Document Numbers (StyleTemplate)”
INSERT INTO [Essentials].[StyleTemplate] (StyleTemplateCode, ParentStyleTemplateID, ModificationDate)
SELECT
    sie.DocNo,
    pst.ParentStyleTemplateID,
    MAX(sie.StyleModificationDate) AS ModificationDate
FROM
    #StyleInfoFromERP sie
JOIN
    [Essentials].[ParentStyleTemplate] pst
    ON pst.ParentStyleTemplateDescription = sie.StyleNo
LEFT JOIN
    [Essentials].[StyleTemplate] st
    ON st.StyleTemplateCode = sie.DocNo AND st.ParentStyleTemplateID = pst.ParentStyleTemplateID
WHERE
    st.StyleTemplateCode IS NULL
GROUP BY
    sie.DocNo,
    pst.ParentStyleTemplateID;

  • Purpose: This query inserts new document numbers (DocNo) into the StyleTemplate table that do not already exist..

  • Explanation:

    • A JOIN is performed between #StyleInfoFromERP and ParentStyleTemplate to get the corresponding ParentStyleTemplateID.
    • A LEFT JOIN checks if the DocNo already exists in the StyleTemplate table.
    • If no matching document is found (NULL), the DocNo, ParentStyleTemplateID, and ModificationDate are inserted.

5. Drop Temporary Table if Exists

Section titled “5. Drop Temporary Table if Exists”
DROP TABLE IF EXISTS #OperationDataSetFromERP;
  • Explanation: This line ensures that any previous instance of the temporary table #OperationDataSetFromERP is dropped before creating a new one, preventing potential conflicts from leftover data.

6. DataSet for Operations

Section titled “6. DataSet for Operations”
SELECT
    dt.*,
    CASE WHEN Heading = 'FIRST OPERATION' THEN 1 ELSE 0 END AS IsFirst,
    CASE WHEN QCStation = 'END LINE QC 100%' THEN 1 ELSE 0 END AS IsLast,
    CASE WHEN QCStation = 'STATION QC 100%' THEN 1 ELSE 0 END AS IsQcOperation,
  ROW_NUMBER() OVER(PARTITION BY DocNo,RevNo,DepartmentID ORDER BY OperationNo) AS OpSeq,'Bundle' AS ScanType
  INTO #OperationDataSetFromERP
FROM (
  -- SubQuery Start
    SELECT
        sie.*,
        st.StyleTemplateID,
        op.OperationID,
    d.DepartmentID,
        LAG(sie.OperationDesc) OVER (PARTITION BY sie.DocNo, sie.RevNo, sie.LayoutDep ORDER BY sie.OperationNo) AS PrevOperationDesc
    FROM
        #StyleInfoFromERP sie
    JOIN
        [Essentials].[StyleTemplate] st ON st.StyleTemplateCode = sie.DocNo
  JOIN
        [Essentials].[Operation] op ON op.OperationName = sie.OperationDesc
    LEFT JOIN
        [Essentials].[Department] d ON d.DepartmentName = sie.LayoutDep
  -- SubQuery End
) AS dt
WHERE
    PrevOperationDesc IS NULL OR PrevOperationDesc <> OperationDesc;

  • Purpose: This query constructs a temporary dataset (#OperationDataSetFromERP) for operations, adding columns for first operation flags (IsFirst), last operation flags (IsLast), and quality control flags (IsQcOperation).

  • Explanation:

    • The outer query creates flags based on specific criteria:

      • IsFirst: Marks rows as the first operation when the Heading is “FIRST OPERATION”.

      • IsLast: Marks rows as the last operation when the QCStation is “END LINE QC 100%”.

      • IsQcOperation: Marks quality control operations when the QCStation is “STATION QC 100%”.

    • The ROW_NUMBER() function assigns a sequence (OpSeq) to operations within each DocNo, RevNo, and DepartmentID, ordering by OperationNo.

    • A constant 'Bundle' is assigned to the ScanType column.

  • Subquery Purpose: The subquery selects relevant operation data from the #StyleInfoFromERP table, joining the StyleTemplate, Operation, and Department tables to retrieve necessary identifiers and department information.

    • A LAG function is used to retrieve the previous operation description (PrevOperationDesc) for each row, partitioned by DocNo, RevNo, and LayoutDep, and ordered by OperationNo.

    • The purpose of using LAG is to eliminate duplicate operations; if the same operation appears in the style with two different OperationNo values, the function allows us to select the operation with the lower OperationNo.

  • Condition: The final WHERE clause filters out rows where the PrevOperationDesc is equal to the current OperationDesc to avoid duplications.

7. Non-Existing Operations to Insert into StyleBulletin

Section titled “7. Non-Existing Operations to Insert into StyleBulletin”
INSERT INTO [Essentials].[StyleBulletin] (
    StyleTemplateID, DocNo, OperationID, OperationDesc, OperationSequence, SMV, RevNo, CompanyID, BrID, WorkOrder, MachineID, MachineAbv, MachineName, QCStation, NoQCS,
    Heading, Target, Line_no, LayoutType, LayoutPos, LayoutPos2, HeaderSr, OprSr, RGBColor, R, G, B, Mod_Date, Ent_Date, User_Name, Comp_Name, LayoutDep, DepartmentID,
    IsFirst, IsLast, IsQcOperation, OpSeq, ScanType
)
-- DataSet
SELECT
    ds.StyleTemplateID, ds.DocNo, ds.OperationID, ds.OperationDesc, ds.OperationNo, ds.SMV, ds.RevNo, ds.CompanyID, ds.BrID, ds.WorkOrder, ds.MachineID, ds.MachineAbv, ds.MachineName, ds.QCStation, ds.NoQCS,
    ds.Heading, ds.Target, ds.Line_no, ds.LayoutType, ds.LayoutPos, ds.LayoutPos2, ds.HeaderSr, ds.OprSr, ds.RGBColor, ds.R, ds.G, ds.B, ds.Mod_Date, ds.Ent_Date, ds.User_Name, ds.Comp_Name, ds.LayoutDep, ds.DepartmentID,
    ds.IsFirst, ds.IsLast, ds.IsQcOperation, ds.OpSeq, ds.ScanType
FROM
    #OperationDataSetFromERP ds
LEFT JOIN
    (
        SELECT
            DISTINCT StyleTemplateID, DocNo, RevNo, OperationID, OperationDesc, DepartmentID, LayoutDep, Mod_Date
        FROM
            [Essentials].[StyleBulletin]
        WHERE
            IsActive = 1 AND OperationDesc NOT IN ('Out From Cutting', 'QC Pass')
    ) stb
    ON stb.StyleTemplateID = ds.StyleTemplateID
    AND stb.RevNo = ds.RevNo
    AND stb.DepartmentID = ds.DepartmentID
    AND stb.OperationID = ds.OperationID
WHERE
    stb.StyleTemplateID IS NULL
ORDER BY
    ds.StyleTemplateID;

  • Purpose: This query inserts non-existing operations into the StyleBulletin table from the #OperationDataSetFromERP temporary dataset.

  • Explanation:

    • The INSERT INTO statement targets the StyleBulletin table, specifying all relevant columns.

    • The SELECT statement retrieves data from the #OperationDataSetFromERP temporary table (ds), which contains operations data to be inserted.

    • A LEFT JOIN is performed with a subquery (stb) that selects distinct records from the StyleBulletin table where IsActive is 1 and the OperationDesc is not in the specified exclusion list ('Out From Cutting', 'QC Pass').

    • The WHERE clause filters for records where no matching StyleTemplateID is found in the StyleBulletin table, indicating that the operation does not already exist.

    • The results are ordered by StyleTemplateID for better organization.

8. Corrected OperationSequence in StyleBulletin

Section titled “8. Corrected OperationSequence in StyleBulletin”
WITH ModifiedStyles AS (
SELECT
  DISTINCT StyleNo, DocNo, RevNo, LayoutDep, StyleModificationDate
FROM
  #StyleInfoFromERP
),
CorrectOpSeq AS (
SELECT
  stb.DocNo,stb.LayoutDep,stb.RevNo,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,
  ROW_NUMBER() OVER(PARTITION BY stb.DocNo, stb.RevNo, stb.DepartmentID ORDER BY stb.OperationSequence) AS NewOpSeq,
  stb.CreatedAt,stb.UpdatedAt
FROM
  [Essentials].[StyleBulletin] stb
JOIN
  ModifiedStyles ms
  ON ms.DocNo=stb.DocNo AND ms.RevNo=stb.RevNo
  AND TRIM(ms.LayoutDep)=TRIM(stb.LayoutDep)
WHERE
  IsActive=1 AND OpSeq NOT LIKE '%-%'
)
UPDATE CorrectOpSeq
SET
  OpSeq = NewOpSeq,
  UpdatedAt=GETDATE()
WHERE
  OpSeq!=NewOpSeq;

  • Purpose: This query corrects the OperationSequence in the StyleBulletin table by assigning new operation sequence numbers (OpSeq) based on their order of appearance.

  • Explanation:

    • Common Table Expressions (CTEs):

      • ModifiedStyles: This CTE retrieves distinct style information, including StyleNo, DocNo, RevNo, LayoutDep, and StyleModificationDate, from the #StyleInfoFromERP temporary dataset.

      • CorrectOpSeq: This CTE generates a new operation sequence (NewOpSeq) for records in the StyleBulletin table that match the modified styles. It uses the ROW_NUMBER() function, partitioned by DocNo, RevNo, and DepartmentID, and orders the results by OperationSequence.

    • The UPDATE statement updates the OpSeq in the CorrectOpSeq CTE with the newly calculated NewOpSeq where the existing OpSeq does not match NewOpSeq.

    • The UpdatedAt column is also set to the current date and time to reflect when the update occurred.

9. Deactivate Old StyleBulletin

Section titled “9. Deactivate Old StyleBulletin”
UPDATE stb
SET stb.IsActive = 0
FROM
    [Essentials].[StyleBulletin] stb
WHERE NOT EXISTS (
    SELECT 1
    FROM [NCPMS].[dbo].[MchLayoutDetail_vw_union] mld
    WHERE mld.DocNo = stb.DocNo
      AND mld.RevNo = stb.RevNo
      AND mld.LayoutDep = stb.LayoutDep
);

  • Purpose: This query deactivates old entries in the StyleBulletin table by setting the IsActive flag to 0 for styles that no longer exist in the MchLayoutDetail view In ERP.

  • Explanation:

    • The UPDATE statement targets the StyleBulletin table (stb).

    • The WHERE NOT EXISTS clause checks for the absence of matching records in the MchLayoutDetail view (mld).

      • It compares DocNo, RevNo, and LayoutDep between the two tables.

    • If no matching record is found in MchLayoutDetail, the IsActive column for that style in the StyleBulletin table is set to 0, effectively deactivating it.

10. Insert Manual Operations without Duplicates

Section titled “10. Insert Manual Operations without Duplicates”
INSERT INTO [Essentials].[StyleBulletin](StyleTemplateID, DocNo, RevNo, LayoutDep, DepartmentID, OperationSequence, OperationDesc, OpSeq, ScanType, OperationID, IsLast)
SELECT
    sb.StyleTemplateID,sb.DocNo,sb.RevNo,op.LayoutDep,op.DepartmentID,op.OperationSequence,op.OperationDesc,op.OpSeq,op.ScanType,o.OperationID,op.IsLast
FROM
(
    VALUES ('Out From Cutting', 1, 'CUTTING', -1, -1, 'Bundle', 1),('QC Pass', 5, 'SEWING', -2, -2, 'Bundle', 0)
) AS op(OperationDesc, DepartmentID, LayoutDep, OperationSequence, OpSeq, ScanType, IsLast)
JOIN
    [Essentials].[Operation] o ON o.OperationName = op.OperationDesc
JOIN
(
  SELECT
    DISTINCT StyleTemplateID, DocNo, RevNo, LayoutDep
  FROM
    [Essentials].[StyleBulletin]
  WHERE
    IsActive = 1 AND LayoutDep IN ('CUTTING', 'SEWING')
) AS sb
ON
    sb.LayoutDep = op.LayoutDep
LEFT JOIN
    [Essentials].[StyleBulletin] stb
ON
    stb.DocNo = sb.DocNo AND stb.RevNo = sb.RevNo AND stb.LayoutDep = sb.LayoutDep
    AND stb.OperationDesc = op.OperationDesc AND stb.IsActive = 1
WHERE
    stb.DocNo IS NULL;

  • Purpose: This query inserts manual operations into the StyleBulletin table while ensuring that no duplicates are created.

  • Explanation:

    • The INSERT INTO statement targets the StyleBulletin table, specifying the columns to be filled.

    • The SELECT statement retrieves data from the following sources:

      • A VALUES clause defines manual operation entries (like ‘Out From Cutting’ and ‘QC Pass’) along with their associated attributes.

      • A JOIN with the Operation table retrieves the OperationID based on the OperationDesc.

      • A subquery (sb) fetches distinct style entries from the StyleBulletin table where styles are active and belong to specific layout departments (‘CUTTING’ and ‘SEWING’).

    • A LEFT JOIN is performed with the StyleBulletin table (stb) to check for existing records matching the DocNo, RevNo, LayoutDep, and OperationDesc.

    • The WHERE clause filters out any entries that already exist in the StyleBulletin table by checking if stb.DocNo is NULL, ensuring that only new, non-duplicate records are inserted.

10. Mark Unique IsFirst Operation For Sewing

Section titled “10. Mark Unique IsFirst Operation For Sewing”
WITH StyleForSewingFirst AS (
SELECT
  DISTINCT StyleNo, DocNo, RevNo, LayoutDep, StyleModificationDate
FROM
  #StyleInfoFromERP
WHERE
  LayoutDep='SEWING'
),
FirstOpMinSeqSewing   AS (
SELECT
    stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,stb.Heading,stb.IsFirst,
    MIN(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) AS MinOpSeq,
    CASE WHEN  stb.OpSeq=MIN(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) THEN 1 ELSE 0 END AS ActualFirstOp
FROM
    [Essentials].[StyleBulletin] stb
JOIN StyleForSewingFirst ssf
ON ssf.DocNo=stb.DocNo AND ssf.RevNo=stb.RevNo
AND TRIM(ssf.LayoutDep)=TRIM(stb.LayoutDep)
WHERE
    stb.Heading = 'FIRST OPERATION' AND stb.IsActive = 1
)
UPDATE stb
SET stb.IsFirst = CASE WHEN stb.OpSeq = c.MinOpSeq THEN 1 ELSE 0 END,
stb.UpdatedAt=GETDATE()
/**
SELECT
  stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,c.MinOpSeq,stb.Heading,stb.IsFirst,c.ActualFirstOp
**/
FROM [Essentials].[StyleBulletin] stb
JOIN FirstOpMinSeqSewing c ON stb.StyleBulletinID = c.StyleBulletinID;

  • Purpose: This query identifies and marks the first operation in the StyleBulletin table for sewing operations while eliminating duplicate entries for the IsFirst operation flag.

  • Explanation:

    • Common Table Expressions (CTEs):

      • StyleForSewingFirst: This CTE retrieves distinct style information (including StyleNo, DocNo, RevNo, LayoutDep, and StyleModificationDate) for styles where the LayoutDep is ‘SEWING’.

      • FirstOpMinSeqSewing: This CTE calculates the minimum operation sequence (MinOpSeq) for sewing operations. It also determines whether each operation is the actual first operation (ActualFirstOp) by comparing the current operation sequence (OpSeq) with the minimum sequence.

        • The MIN function is used with the OVER clause to get the minimum operation sequence partitioned by DocNo, RevNo, and LayoutDep, ordered by OperationSequence.

        • A case statement marks ActualFirstOp as 1 if the current OpSeq equals the minimum operation sequence.

    • UPDATE Statement:

      • The UPDATE statement modifies the IsFirst flag in the StyleBulletin table for sewing operations.

      • It sets IsFirst to 1 for the operation with the minimum sequence (MinOpSeq) and updates the UpdatedAt column to the current date and time.

  • Notes: The commented-out SELECT statement can be used to view the results before performing the update, helping to validate which records are being modified.

11. Mark Unique IsLast Operation For Sewing

Section titled “11. Mark Unique IsLast Operation For Sewing”
WITH StyleForSewingLast AS (
SELECT
  DISTINCT StyleNo, DocNo, RevNo, LayoutDep, StyleModificationDate
FROM
  #StyleInfoFromERP
WHERE
  LayoutDep='SEWING'
),
LastOpMaxSeqSewing AS (
SELECT
    stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,stb.QCStation,stb.IsLast,
    MAX(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) AS MaxOpSeq,
    CASE WHEN  stb.OpSeq=MAX(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) THEN 1 ELSE 0 END AS ActualLastOp
FROM
    [Essentials].[StyleBulletin] stb
JOIN StyleForSewingLast ssl
ON ssl.DocNo=stb.DocNo AND ssl.RevNo=stb.RevNo
AND TRIM(ssl.LayoutDep)=TRIM(stb.LayoutDep)
WHERE
    stb.QCStation = 'END LINE QC 100%' AND stb.IsActive=1
)
UPDATE stb
SET stb.IsLast = CASE WHEN stb.OpSeq = c.MaxOpSeq THEN 1 ELSE 0 END
/**
SELECT
  stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,c.MaxOpSeq,stb.QCStation,stb.IsLast,c.ActualLastOp
**/
FROM [Essentials].[StyleBulletin] stb
JOIN LastOpMaxSeqSewing c ON stb.StyleBulletinID = c.StyleBulletinID;

  • Purpose: This query identifies and marks the last operation in the StyleBulletin table for sewing operations while eliminating duplicate entries for the IsLast operation flag.

  • Explanation:

    • Common Table Expressions (CTEs):

      • StyleForSewingLast: This CTE retrieves distinct style information (including StyleNo, DocNo, RevNo, LayoutDep, and StyleModificationDate) for styles where the LayoutDep is ‘SEWING’.

      • LastOpMaxSeqSewing: This CTE calculates the maximum operation sequence (MaxOpSeq) for sewing operations. It also determines whether each operation is the actual last operation (ActualLastOp) by comparing the current operation sequence (OpSeq) with the maximum sequence.

        • The MAX function is used with the OVER clause to get the maximum operation sequence partitioned by DocNo, RevNo, and LayoutDep, ordered by OperationSequence.

        • A case statement marks ActualLastOp as 1 if the current OpSeq equals the maximum operation sequence.

    • UPDATE Statement:

      • The UPDATE statement modifies the IsLast flag in the StyleBulletin table for sewing operations.

      • It sets IsLast to 1 for the operation with the maximum sequence (MaxOpSeq).

  • Notes: The commented-out SELECT statement can be used to view the results before performing the update, helping to validate which records are being modified.

12. Mark Unique IsFirst Operation For All Departments Except Sewing

Section titled “12. Mark Unique IsFirst Operation For All Departments Except Sewing”
WITH StyleForFirstExcludeSewing AS (
SELECT
  DISTINCT StyleNo, DocNo, RevNo, LayoutDep, StyleModificationDate
FROM
  #StyleInfoFromERP
WHERE
  LayoutDep!='SEWING'
),
FirstOpMinSeqExcludeSewing AS (
SELECT
  stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,stb.Heading,stb.IsFirst,
  MIN(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) AS MinOpSeq,
  CASE WHEN  stb.OpSeq=MIN(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) THEN 1 ELSE 0 END AS ActualFirstOp
FROM
    [Essentials].[StyleBulletin] stb
JOIN
  StyleForFirstExcludeSewing ssf
  ON ssf.DocNo=stb.DocNo AND ssf.RevNo=stb.RevNo
  AND TRIM(ssf.LayoutDep)=TRIM(stb.LayoutDep)
WHERE
    stb.IsActive=1 AND stb.LayoutDep != 'SEWING' AND stb.OpSeq!=-1
)
UPDATE stb
SET stb.IsFirst = CASE WHEN stb.OpSeq = c.MinOpSeq THEN 1 ELSE 0 END
/**
SELECT
  stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,c.MinOpSeq,stb.Heading,stb.IsFirst,c.ActualFirstOp
**/
FROM [Essentials].[StyleBulletin] stb
JOIN FirstOpMinSeqExcludeSewing c ON stb.StyleBulletinID = c.StyleBulletinID;

  • Purpose: This query identifies the first operation in the StyleBulletin table for all departments except sewing, ensuring that only relevant operations are marked as the first operation.

  • Explanation:

    • Common Table Expressions (CTEs):

      • StyleForFirstExcludeSewing: This CTE selects distinct style information (including StyleNo, DocNo, RevNo, LayoutDep, and StyleModificationDate) from the #StyleInfoFromERP temporary dataset, filtering out any entries with LayoutDep equal to ‘SEWING’.

      • FirstOpMinSeqExcludeSewing: This CTE retrieves information from the StyleBulletin table and calculates the minimum operation sequence (MinOpSeq) for each style, while also determining if the current operation is the first operation (ActualFirstOp).

        • It uses the MIN function with the OVER clause to calculate the minimum OpSeq for each group of operations partitioned by DocNo, RevNo, and LayoutDep, ordered by OperationSequence.

        • The CASE statement assigns ActualFirstOp as 1 if the current OpSeq equals the minimum operation sequence.

    • UPDATE Statement:

      • The UPDATE statement modifies the IsFirst flag in the StyleBulletin table.

      • It sets IsFirst to 1 if the current operation’s OpSeq matches the calculated MinOpSeq, effectively marking the first operation for each style.

      • The FROM clause joins the original StyleBulletin table with the FirstOpMinSeqExcludeSewing CTE based on StyleBulletinID.

  • Notes:

    • The commented-out SELECT statement provides a way to preview the results, showing details of the operations being updated, including StyleBulletinID, MinOpSeq, and the current state of IsFirst.
    • This query aims to ensure that only one operation per style is marked as the first operation, avoiding duplication and confusion.

13. Mark Unique IsFirst Operation For All Departments Except Sewing

Section titled “13. Mark Unique IsFirst Operation For All Departments Except Sewing”
WITH StyleForLastExcludeSewCut AS (
SELECT
  DISTINCT StyleNo, DocNo, RevNo, LayoutDep, StyleModificationDate
FROM
  #StyleInfoFromERP
WHERE
  LayoutDep NOT IN ('SEWING','CUTTING')
),
LastOpMaxSeqExcludeSewCut AS (
SELECT
  stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,stb.QCStation,stb.IsLast,
  MAX(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) AS MaxOpSeq,
  CASE WHEN  stb.OpSeq=MAX(stb.OpSeq) OVER (PARTITION BY stb.DocNo,stb.RevNo,stb.LayoutDep ORDER BY stb.OperationSequence ROWS BETWEEN UNBOUNDED PRECEDING and UNBOUNDED FOLLOWING) THEN 1 ELSE 0 END AS ActualLastOp
FROM
    [Essentials].[StyleBulletin] stb
JOIN
  StyleForLastExcludeSewCut ssl
  ON ssl.DocNo=stb.DocNo AND ssl.RevNo=stb.RevNo
  AND TRIM(ssl.LayoutDep)=TRIM(stb.LayoutDep)
WHERE
  stb.IsActive=1 AND stb.LayoutDep NOT IN ('SEWING','CUTTING')
)
UPDATE stb
SET stb.IsLast = CASE WHEN stb.OpSeq = c.MaxOpSeq THEN 1 ELSE 0 END
/**
SELECT
  stb.StyleBulletinID,stb.StyleTemplateID,stb.DocNo,stb.RevNo,stb.LayoutDep,stb.OperationDesc,stb.OperationSequence,stb.OpSeq,c.MaxOpSeq,stb.Heading,stb.IsLast,c.ActualLastOp
**/
FROM [Essentials].[StyleBulletin] stb
JOIN LastOpMaxSeqExcludeSewCut c ON stb.StyleBulletinID = c.StyleBulletinID;

  • Purpose: This query identifies the last operation in the StyleBulletin table for all departments except sewing and cutting, ensuring that only relevant operations are marked as the last operation.

  • Explanation:

    • Common Table Expressions (CTEs):

      • StyleForLastExcludeSewCut: This CTE retrieves distinct style information (including StyleNo, DocNo, RevNo, LayoutDep, and StyleModificationDate) from the #StyleInfoFromERP temporary dataset, filtering out any entries with LayoutDep equal to ‘SEWING’ or ‘CUTTING’.

      • LastOpMaxSeqExcludeSewCut: This CTE retrieves information from the StyleBulletin table and calculates the maximum operation sequence (MaxOpSeq) for each style while determining if the current operation is the last operation (ActualLastOp).

        • It uses the MAX function with the OVER clause to calculate the maximum OpSeq for each group of operations partitioned by DocNo, RevNo, and LayoutDep, ordered by OperationSequence.

        • The CASE statement assigns ActualLastOp as 1 if the current OpSeq equals the maximum operation sequence.

    • UPDATE Statement:

      • The UPDATE statement modifies the IsLast flag in the StyleBulletin table.

      • It sets IsLast to 1 if the current operation’s OpSeq matches the calculated MaxOpSeq, effectively marking the last operation for each style.

      • The FROM clause joins the original StyleBulletin table with the LastOpMaxSeqExcludeSewCut CTE based on StyleBulletinID.

  • Notes:

    • The commented-out SELECT statement provides a way to preview the results, showing details of the operations being updated, including StyleBulletinID, MaxOpSeq, and the current state of IsLast.

    • This query aims to ensure that only one operation per style is marked as the last operation, avoiding duplication and confusion.

14. Mark Unique IsFirst Operation For All Departments Where No Operation Marked

Section titled “14. Mark Unique IsFirst Operation For All Departments Where No Operation Marked”
UPDATE stb
SET stb.IsFirst = CASE WHEN stb.OpSeq = f.MinOpSeq THEN 1 ELSE 0 END
FROM [Essentials].[StyleBulletin] stb
JOIN (
SELECT
    DocNo,
    LayoutDep,
    RevNo,
  MIN(CASE WHEN OpSeq NOT LIKE '%-%' THEN OpSeq ELSE OpSeq END) AS MinOpSeq
FROM
    [Essentials].[StyleBulletin]
WHERE
    IsActive = 1
GROUP BY
    DocNo,
    LayoutDep,
    RevNo
HAVING
    SUM(CASE WHEN IsFirst = 1 THEN 1 ELSE 0 END) = 0  -- No IsFirst = 1
) AS f
ON stb.DocNo = f.DocNo
AND stb.LayoutDep = f.LayoutDep
AND stb.RevNo = f.RevNo
AND stb.IsActive=1;

  • Purpose: This query updates the IsFirst flag in the StyleBulletin table to indicate the first operation for each unique combination of DocNo, LayoutDep, and RevNo where no operation has already been marked as the first.

  • Explanation:

    • UPDATE Statement: The UPDATE statement modifies the IsFirst column in the StyleBulletin table (stb).

    • JOIN Clause:

      • A derived table (aliased as f) is created to identify the first operation for each style by selecting the minimum OpSeq (operation sequence) for active styles.

      • The MIN function is used to find the smallest OpSeq value that does not contain a hyphen (%-%). The CASE statement ensures that the minimum is calculated correctly.

    • Conditions:

      • The WHERE clause in the subquery filters for active styles (IsActive = 1).

      • The GROUP BY clause groups the results by DocNo, LayoutDep, and RevNo, allowing for the identification of the first operation within each group.

      • The HAVING clause ensures that the query only includes groups where no operation is currently marked as the first (IsFirst = 1). This prevents the query from incorrectly marking an operation as the first if one already exists.

    • ON Clause: The JOIN condition links the main StyleBulletin table with the derived table f based on DocNo, LayoutDep, and RevNo, ensuring that only those records that need updating are selected.

    • SET Clause: The SET clause updates the IsFirst flag. It assigns 1 to IsFirst if the OpSeq matches the MinOpSeq calculated in the subquery, indicating that this operation is the first for that style. Otherwise, it sets IsFirst to 0.

  • Notes:

    • The query ensures that only one operation per style is marked as the first operation, thereby eliminating duplicates in the IsFirst designation.

15. Mark Unique IsLast Operation For All Departments Where No Operation Marked

Section titled “15. Mark Unique IsLast Operation For All Departments Where No Operation Marked”
UPDATE sb
SET IsLast = CASE WHEN sb.OpSeq = l.MaxOpSeq THEN 1 ELSE 0 END
FROM [Essentials].[StyleBulletin] sb
JOIN (
SELECT
    DocNo,
    LayoutDep,
    RevNo,
  MAX(OpSeq) AS MaxOpSeq
FROM
    [Essentials].[StyleBulletin]
WHERE
    IsActive = 1
GROUP BY
    DocNo,
    LayoutDep,
    RevNo
HAVING
    SUM(CASE WHEN IsLast = 1 THEN 1 ELSE 0 END) = 0   -- No IsLast = 1;
) AS l
ON sb.DocNo = l.DocNo
AND sb.LayoutDep = l.LayoutDep
AND sb.RevNo = l.RevNo
AND IsActive=1;

  • Purpose: This query updates the IsLast flag in the StyleBulletin table to indicate the last operation for each unique combination of DocNo, LayoutDep, and RevNo where no operation has already been marked as the last.

  • Explanation:

    • UPDATE Statement: The UPDATE statement modifies the IsLast column in the StyleBulletin table (sb).

    • JOIN Clause:

      • A derived table (aliased as l) is created to identify the last operation for each style by selecting the maximum OpSeq (operation sequence) for active styles.

      • The MAX function is used to find the largest OpSeq value, which represents the last operation in the sequence.

    • Conditions:

      • The WHERE clause in the subquery filters for active styles (IsActive = 1).

      • The GROUP BY clause groups the results by DocNo, LayoutDep, and RevNo, allowing for the identification of the last operation within each group.

      • The HAVING clause ensures that the query only includes groups where no operation is currently marked as the last (IsLast = 1). This prevents the query from incorrectly marking an operation as the last if one already exists.

    • ON Clause: The JOIN condition links the main StyleBulletin table with the derived table l based on DocNo, LayoutDep, and RevNo, ensuring that only those records that need updating are selected.

    • SET Clause: The SET clause updates the IsLast flag. It assigns 1 to IsLast if the OpSeq matches the MaxOpSeq calculated in the subquery, indicating that this operation is the last for that style. Otherwise, it sets IsLast to 0.

  • Notes:

    • The query ensures that only one operation per style is marked as the last operation, thereby eliminating duplicates in the IsLast designation.

16. Update ModificationDate of Existing DocNos

Section titled “16. Update ModificationDate of Existing DocNos”
UPDATE st
SET st.ModificationDate = source.ModificationDate
FROM
    [Essentials].[StyleTemplate] st
JOIN
    [Essentials].[ParentStyleTemplate] pst
    ON st.ParentStyleTemplateID = pst.ParentStyleTemplateID
JOIN (
    SELECT
        sie.StyleNo, sie.DocNo, MAX(sie.StyleModificationDate) AS ModificationDate
    FROM
        #StyleInfoFromERP sie
    GROUP BY
        sie.StyleNo, sie.DocNo
) AS source
ON st.StyleTemplateCode = source.DocNo
AND pst.ParentStyleTemplateDescription = source.StyleNo;

  • Purpose: This query updates the ModificationDate column in the StyleTemplate table for entries that match existing DocNo values based on the most recent modification date from a temporary dataset (#StyleInfoFromERP).

  • Explanation:

    • UPDATE Statement: The UPDATE statement targets the StyleTemplate table (aliased as st) to modify the ModificationDate column.

    • JOIN Clauses:

      • The first JOIN links the StyleTemplate table to the ParentStyleTemplate table (pst) on the ParentStyleTemplateID. This ensures that you have access to the parent style’s description.

      • The second JOIN connects the StyleTemplate table to a derived table (source) created from the #StyleInfoFromERP temporary table. This derived table aggregates the most recent modification dates for each combination of StyleNo and DocNo.

    • Derived Table (source):

      • The derived table (source) selects distinct StyleNo and DocNo values from #StyleInfoFromERP, along with the maximum StyleModificationDate for each combination. This effectively captures the latest modification date for each style-document pair.

      • The GROUP BY clause groups the results by StyleNo and DocNo to ensure that the aggregation correctly reflects the most recent date for each combination.

    • ON Clause: The ON conditions ensure that:

      • The DocNo from the StyleTemplate table matches the DocNo from the source derived table.

      • The ParentStyleTemplateDescription from the ParentStyleTemplate table matches the StyleNo from the source derived table.

  • Effect: After executing this query, the ModificationDate in the StyleTemplate table will reflect the most recent modification date from the temporary dataset for the corresponding styles and documents.

17. Flow Diagram Of StyleBulletIn Integration

Section titled “17. Flow Diagram Of StyleBulletIn Integration”