Votre modèle de données pour la planification de la production

/*
This is not a runnable script but a configuration and transformation plan for the Dynamics 365 Data Management Framework.

**Step 1: Configure the Export Project**
Export the following entities with the specified fields:

1.  **Entity: Production orders (ProdTable)**
    *   `ProductionOrderNumber` (PRODID)
    *   `ProductionOrderStatus` (PRODSTATUS)
    *   `CreationDate` (CREATEDDATETIME)
    *   `LastUpdateDateStatusEstimate` (LASTUPDATEDATESTATUSESTIMATE)
    *   `LastUpdateDateStatusScheduled` (LASTUPDATEDATESTATUSSCHEDULED)
    *   `ReleaseDate` (RELEASEDATETIME)
    *   `StartDate` (STARTEDDATETIME)
    *   `ReportedFinishedDate` (REPORTEDFINISHEDDATETIME)
    *   `LastUpdateDateStatusEnded` (LASTUPDATEDATESTATUSENDED)
    *   `ModifiedDateTime` (MODIFIEDDATETIME)
    *   `CreatedByUser` (CREATEDBY)
    *   `ProductionSite` (INVENTSITEID)
    *   `ProductNumber` (ITEMID)
    *   `ProductionType` (PRODTYPE)
    *   `Quantity` (QTYsched)
    *   `ActualQuantity` (QTYCALC)
    *   `DeliveryDate` (DLVDATE)

2.  **Entity: Production order route transactions (ProdRouteTrans)**
    *   `ProductionOrderNumber` (PRODID)
    *   `OperationNumber` (OPRNUM)
    *   `TransactionDate` (TRANSDATE)
    *   `TransactionTime` (TRANSTIME)
    *   `CreatedDateTime` (CREATEDDATETIME)
    *   `CreatedByUser` (CREATEDBY)

3.  **Entity: Inventory quality orders (InventQualityOrderTable)**
    *   `QualityOrderNumber` (INVENTQUALITYORDERID)
    *   `ReferenceType` (REFTYPE)
    *   `ReferenceNumber` (PRODID)
    *   `QualityOrderStatus` (STATUS)
    *   `CreationDate` (CREATEDDATETIME)
    *   `ModifiedDateTime` (MODIFIEDDATETIME)

4.  **Entity: Production picking list journal lines (ProdJournalBOM)**
    *   `JournalId` (JOURNALID)
    *   `ProductionOrderNumber` (PRODID)
    *   `PostedDateTime` (POSTEDDATETIME)
    *   `CreatedByUser` (CREATEDBY)

**Step 2: Apply Transformation Logic (Post-Export)**
Use a script or ETL tool to transform the exported files into a single event log CSV. For each row in the source files, generate events as described below.
*/

-- Production Order Created
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Created' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    PRODSTATUS AS ProductionOrderStatus,
    DLVDATE AS PlannedStartDate,
    -- [Logic to calculate PlannedEndDate] AS PlannedEndDate,
    CREATEDBY AS UserId,
    INVENTSITEID AS ProductionPlant,
    ITEMID AS ProductNumber,
    PRODTYPE AS ProductionType,
    QTYCALC AS ActualQuantity,
    -- [Your field for Production Priority] AS ProductionPriority
FROM ProdTable
WHERE CREATEDDATETIME IS NOT NULL

UNION ALL

-- Production Order Estimated
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Estimated' AS ActivityName,
    LASTUPDATEDATESTATUSESTIMATE AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE LASTUPDATEDATESTATUSESTIMATE IS NOT NULL

UNION ALL

-- Production Order Scheduled
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Scheduled' AS ActivityName,
    LASTUPDATEDATESTATUSSCHEDULED AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE LASTUPDATEDATESTATUSSCHEDULED IS NOT NULL

UNION ALL

-- Materials Picked For Production (based on the first picking list journal posting)
SELECT
    PRODID AS ProductionOrderNumber,
    'Materials Picked For Production' AS ActivityName,
    MIN(POSTEDDATETIME) AS EventTime, -- Use the earliest posting time for a given order
    -- Other attributes from ProdTable...
FROM ProdJournalBOM
WHERE POSTEDDATETIME IS NOT NULL
GROUP BY PRODID

UNION ALL

-- Production Order Released
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Released' AS ActivityName,
    RELEASEDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE RELEASEDATETIME IS NOT NULL

UNION ALL

-- Production Order Adjusted (inferred from modification after release)
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Adjusted' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE MODIFIEDDATETIME > RELEASEDATETIME AND PRODSTATUS NOT IN ('Ended', 'Cancelled') -- Example logic

UNION ALL

-- Production Started
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Started' AS ActivityName,
    STARTEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE STARTEDDATETIME IS NOT NULL

UNION ALL

-- Operation Completed
SELECT
    PRODID AS ProductionOrderNumber,
    'Operation Completed' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    -- Other attributes from ProdTable linked on PRODID...
FROM ProdRouteTrans
WHERE CREATEDDATETIME IS NOT NULL

UNION ALL

-- Production Reported As Finished
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Reported As Finished' AS ActivityName,
    REPORTEDFINISHEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE REPORTEDFINISHEDDATETIME IS NOT NULL

UNION ALL

-- Quality Order Generated
SELECT
    PRODID AS ProductionOrderNumber,
    'Quality Order Generated' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    -- Other attributes from ProdTable linked on PRODID...
FROM InventQualityOrderTable
WHERE REFTYPE = 'Production' AND CREATEDDATETIME IS NOT NULL

UNION ALL

-- Quality Inspection Passed
SELECT
    PRODID AS ProductionOrderNumber,
    'Quality Inspection Passed' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    -- Other attributes from ProdTable linked on PRODID...
FROM InventQualityOrderTable
WHERE REFTYPE = 'Production' AND STATUS = 'Pass' AND MODIFIEDDATETIME IS NOT NULL

UNION ALL

-- Production Order Ended
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Ended' AS ActivityName,
    LASTUPDATEDATESTATUSENDED AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE LASTUPDATEDATESTATUSENDED IS NOT NULL

UNION ALL

-- Production Order Cancelled
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Cancelled' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE PRODSTATUS = 'Stopped' -- Or the equivalent 'Cancelled' status enum value in your system

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Created' AS ActivityName,
    p.CREATEDDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.CREATEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYCALC AS ActualQuantity, -- Using calculated quantity at creation
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Estimated' AS ActivityName,
    p.COSTCALCDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYCALC AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.COSTCALCDATETIME IS NOT NULL AND p.PRODSTATUS >= 2 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Scheduled' AS ActivityName,
    p.SCHEDDATE AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYCALC AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.SCHEDDATE IS NOT NULL AND p.PRODSTATUS >= 3 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Materials Picked For Production' AS ActivityName,
    pl.LEDGERVOUCHERPOSTEDDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    pl.CREATEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM INVENTPICKINGLISTJOUR pl
JOIN PRODTABLE p ON pl.INVENTREFID = p.PRODID AND pl.DATAREAID = p.DATAREAID
WHERE pl.LEDGERVOUCHERPOSTEDDATETIME IS NOT NULL AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Released' AS ActivityName,
    p.RELEASEDATE AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.RELEASEDATE IS NOT NULL AND p.PRODSTATUS >= 4 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Adjusted' AS ActivityName,
    p.MODIFIEDDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.RELEASEDATE IS NOT NULL AND p.MODIFIEDDATETIME > p.RELEASEDATE AND p.PRODSTATUS IN (4, 5) AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Started' AS ActivityName,
    p.STARTEDUPDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.STARTEDUPDATETIME IS NOT NULL AND p.PRODSTATUS >= 5 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    prt.PRODID AS ProductionOrderNumber,
    'Operation Completed' AS ActivityName,
    prt.TRANSDATE AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    prt.CREATEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODROUTETRANS prt
JOIN PRODTABLE p ON prt.PRODID = p.PRODID AND prt.DATAREAID = p.DATAREAID
WHERE prt.POSTINGTYPE = 8 AND prt.TRANSDATE IS NOT NULL AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Reported As Finished' AS ActivityName,
    p.LASTUPDDATEPRINT AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.PRODSTATUS = 6 AND p.LASTUPDDATEPRINT IS NOT NULL AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Quality Order Generated' AS ActivityName,
    qo.CREATEDDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    qo.CREATEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM INVENTQUALITYORDERTABLE qo
JOIN PRODTABLE p ON qo.INVENTREFID = p.PRODID AND qo.DATAREAID = p.DATAREAID
WHERE qo.INVENTREFTYPE = 9 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Quality Inspection Passed' AS ActivityName,
    qo.MODIFIEDDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    qo.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM INVENTQUALITYORDERTABLE qo
JOIN PRODTABLE p ON qo.INVENTREFID = p.PRODID AND qo.DATAREAID = p.DATAREAID
WHERE qo.INVENTREFTYPE = 9 AND qo.STATUS = 2 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Ended' AS ActivityName,
    p.LASTUPDDATEPRINT AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.PRODSTATUS = 7 AND p.LASTUPDDATEPRINT IS NOT NULL AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}'

UNION ALL

SELECT
    p.PRODID AS ProductionOrderNumber,
    'Production Order Cancelled' AS ActivityName,
    p.MODIFIEDDATETIME AS EventTime,
    'Dynamics 365 BYOD' AS SourceSystem,
    GETUTCDATE() AS LastDataUpdate,
    p.PRODSTATUS AS ProductionOrderStatus,
    p.SCHEDSTART AS PlannedStartDate,
    p.SCHEDEND AS PlannedEndDate,
    p.MODIFIEDBY AS UserId,
    p.INVENTSITEID AS ProductionPlant,
    p.ITEMID AS ProductNumber,
    p.PRODTYPE AS ProductionType,
    p.QTYGOOD AS ActualQuantity,
    p.PRODPRIO AS ProductionPriority
FROM PRODTABLE p
WHERE p.PRODSTATUS = 0 AND p.CREATEDDATETIME >= '{StartDate}' AND p.CREATEDDATETIME <= '{EndDate}' AND p.DATAREAID = '{CompanyCode}';

/*
This is not a runnable script but a configuration and transformation plan for the Dynamics 365 Data Management Framework.

**Step 1: Configure the Export Project**
Export the following entities with the specified fields:

1.  **Entity: Production orders (ProdTable)**
    *   `ProductionOrderNumber` (PRODID)
    *   `ProductionOrderStatus` (PRODSTATUS)
    *   `CreationDate` (CREATEDDATETIME)
    *   `LastUpdateDateStatusEstimate` (LASTUPDATEDATESTATUSESTIMATE)
    *   `LastUpdateDateStatusScheduled` (LASTUPDATEDATESTATUSSCHEDULED)
    *   `ReleaseDate` (RELEASEDATETIME)
    *   `StartDate` (STARTEDDATETIME)
    *   `ReportedFinishedDate` (REPORTEDFINISHEDDATETIME)
    *   `LastUpdateDateStatusEnded` (LASTUPDATEDATESTATUSENDED)
    *   `ModifiedDateTime` (MODIFIEDDATETIME)
    *   `CreatedByUser` (CREATEDBY)
    *   `ProductionSite` (INVENTSITEID)
    *   `ProductNumber` (ITEMID)
    *   `ProductionType` (PRODTYPE)
    *   `Quantity` (QTYsched)
    *   `ActualQuantity` (QTYCALC)
    *   `DeliveryDate` (DLVDATE)

2.  **Entity: Production order route transactions (ProdRouteTrans)**
    *   `ProductionOrderNumber` (PRODID)
    *   `OperationNumber` (OPRNUM)
    *   `TransactionDate` (TRANSDATE)
    *   `TransactionTime` (TRANSTIME)
    *   `CreatedDateTime` (CREATEDDATETIME)
    *   `CreatedByUser` (CREATEDBY)

3.  **Entity: Inventory quality orders (InventQualityOrderTable)**
    *   `QualityOrderNumber` (INVENTQUALITYORDERID)
    *   `ReferenceType` (REFTYPE)
    *   `ReferenceNumber` (PRODID)
    *   `QualityOrderStatus` (STATUS)
    *   `CreationDate` (CREATEDDATETIME)
    *   `ModifiedDateTime` (MODIFIEDDATETIME)

4.  **Entity: Production picking list journal lines (ProdJournalBOM)**
    *   `JournalId` (JOURNALID)
    *   `ProductionOrderNumber` (PRODID)
    *   `PostedDateTime` (POSTEDDATETIME)
    *   `CreatedByUser` (CREATEDBY)

**Step 2: Apply Transformation Logic (Post-Export)**
Use a script or ETL tool to transform the exported files into a single event log CSV. For each row in the source files, generate events as described below.
*/

-- Production Order Created
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Created' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    PRODSTATUS AS ProductionOrderStatus,
    DLVDATE AS PlannedStartDate,
    -- [Logic to calculate PlannedEndDate] AS PlannedEndDate,
    CREATEDBY AS UserId,
    INVENTSITEID AS ProductionPlant,
    ITEMID AS ProductNumber,
    PRODTYPE AS ProductionType,
    QTYCALC AS ActualQuantity,
    -- [Your field for Production Priority] AS ProductionPriority
FROM ProdTable
WHERE CREATEDDATETIME IS NOT NULL

UNION ALL

-- Production Order Estimated
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Estimated' AS ActivityName,
    LASTUPDATEDATESTATUSESTIMATE AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE LASTUPDATEDATESTATUSESTIMATE IS NOT NULL

UNION ALL

-- Production Order Scheduled
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Scheduled' AS ActivityName,
    LASTUPDATEDATESTATUSSCHEDULED AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE LASTUPDATEDATESTATUSSCHEDULED IS NOT NULL

UNION ALL

-- Materials Picked For Production (based on the first picking list journal posting)
SELECT
    PRODID AS ProductionOrderNumber,
    'Materials Picked For Production' AS ActivityName,
    MIN(POSTEDDATETIME) AS EventTime, -- Use the earliest posting time for a given order
    -- Other attributes from ProdTable...
FROM ProdJournalBOM
WHERE POSTEDDATETIME IS NOT NULL
GROUP BY PRODID

UNION ALL

-- Production Order Released
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Released' AS ActivityName,
    RELEASEDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE RELEASEDATETIME IS NOT NULL

UNION ALL

-- Production Order Adjusted (inferred from modification after release)
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Adjusted' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE MODIFIEDDATETIME > RELEASEDATETIME AND PRODSTATUS NOT IN ('Ended', 'Cancelled') -- Example logic

UNION ALL

-- Production Started
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Started' AS ActivityName,
    STARTEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE STARTEDDATETIME IS NOT NULL

UNION ALL

-- Operation Completed
SELECT
    PRODID AS ProductionOrderNumber,
    'Operation Completed' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    -- Other attributes from ProdTable linked on PRODID...
FROM ProdRouteTrans
WHERE CREATEDDATETIME IS NOT NULL

UNION ALL

-- Production Reported As Finished
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Reported As Finished' AS ActivityName,
    REPORTEDFINISHEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE REPORTEDFINISHEDDATETIME IS NOT NULL

UNION ALL

-- Quality Order Generated
SELECT
    PRODID AS ProductionOrderNumber,
    'Quality Order Generated' AS ActivityName,
    CREATEDDATETIME AS EventTime,
    -- Other attributes from ProdTable linked on PRODID...
FROM InventQualityOrderTable
WHERE REFTYPE = 'Production' AND CREATEDDATETIME IS NOT NULL

UNION ALL

-- Quality Inspection Passed
SELECT
    PRODID AS ProductionOrderNumber,
    'Quality Inspection Passed' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    -- Other attributes from ProdTable linked on PRODID...
FROM InventQualityOrderTable
WHERE REFTYPE = 'Production' AND STATUS = 'Pass' AND MODIFIEDDATETIME IS NOT NULL

UNION ALL

-- Production Order Ended
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Ended' AS ActivityName,
    LASTUPDATEDATESTATUSENDED AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE LASTUPDATEDATESTATUSENDED IS NOT NULL

UNION ALL

-- Production Order Cancelled
SELECT
    PRODID AS ProductionOrderNumber,
    'Production Order Cancelled' AS ActivityName,
    MODIFIEDDATETIME AS EventTime,
    -- Other attributes from ProdTable...
FROM ProdTable
WHERE PRODSTATUS = 'Stopped' -- Or the equivalent 'Cancelled' status enum value in your system