Votre modèle de données Purchase to Pay - Requisition

WITH REQUISITIONS AS (
  SELECT
    PurchaseRequisition,
    PurchaseRequisitionType,
    PurReqnDescription,
    CreatedByUser,
    CreationDate,
    CAST(CONCAT(CreationDate, 'T', LPAD(CreationTime, 6, '0')) AS TIMESTAMP) AS CreationTimestamp,
    SourceOfSupplyIsAssigned
  FROM I_PurchaseRequisitionAPI01
  WHERE CreationDate BETWEEN '[YYYY-MM-DD]' AND '[YYYY-MM-DD]'
    AND CompanyCode IN ('[Your Company Code]')
),
CHANGE_DOCS AS (
  SELECT
    ObjectValue AS PurchaseRequisition,
    UserName,
    CAST(CONCAT(CreationDate, 'T', LPAD(CreationTime, 6, '0')) AS TIMESTAMP) AS ChangeTimestamp,
    FieldName,
    ValueNew,
    ValueOld
  FROM I_ChangeDocument AS H
  JOIN I_ChangeDocumentItem AS I
    ON H.ChangeDocument = I.ChangeDocument
  WHERE H.Objectclass = 'EINKBELEG'
    AND H.CreationDate BETWEEN '[YYYY-MM-DD]' AND '[YYYY-MM-DD]'
)
-- 1. Requisition Created
SELECT
  R.PurchaseRequisition AS "PurchaseRequisitionId",
  'Requisition Created' AS "ActivityName",
  R.CreationTimestamp AS "EventTime",
  R.CreatedByUser AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM REQUISITIONS AS R
JOIN I_PurchaseRequisitionItemAPI01 AS I
  ON R.PurchaseRequisition = I.PurchaseRequisition

UNION ALL

-- 2. Requisition Submitted For Approval & 5. Approval Step Started
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  CASE
    WHEN C.ValueOld = ''
    THEN 'Requisition Submitted For Approval'
    ELSE 'Approval Step Started'
  END AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  R.CreatedByUser AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R
  ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I
  ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'FRGZU' AND C.ValueNew != ''

UNION ALL

-- 3. Requisition Amended
SELECT DISTINCT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Requisition Amended' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  C.UserName AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName IN ('MENGE', 'PREIS', 'MATNR', 'LIFNR', 'INFNR')
  AND C.ChangeTimestamp > R.CreationTimestamp

UNION ALL

-- 4. Approval Reset
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Approval Reset' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  C.UserName AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'FRGZU' AND C.ValueOld != '' AND C.ValueNew = ''

UNION ALL

-- 6. Approval Step Completed
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Approval Step Completed' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  NULL AS "UserId",
  C.UserName AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'FRGZU' AND C.ValueNew IN ('1', '2', '3', '4', '5', '6', '7') -- Adjust release codes as per your config

UNION ALL

-- 7. Requisition Approved
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Requisition Approved' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  NULL AS "UserId",
  C.UserName AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'FRGKE' AND C.ValueNew = '2' -- Final release indicator '2' is common for approved

UNION ALL

-- 8. Requisition Rejected
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Requisition Rejected' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  NULL AS "UserId",
  C.UserName AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'FRGZU' AND C.ValueNew = 'B' -- 'B' for Blocked/Rejected is a common setting

UNION ALL

-- 9. Requisition Withdrawn
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Requisition Withdrawn' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  C.UserName AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'LOEKZ' AND C.ValueNew = 'X'

UNION ALL

-- 10. Source of Supply Assigned
SELECT DISTINCT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Source of Supply Assigned' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  C.UserName AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName IN ('LIFNR', 'INFNR') AND C.ValueNew != ''
  AND C.ChangeTimestamp > R.CreationTimestamp

UNION ALL

-- 11. Purchase Order Created
SELECT DISTINCT
  I.PurchaseRequisition AS "PurchaseRequisitionId",
  'Purchase Order Created' AS "ActivityName",
  CAST(CONCAT(H.PurchaseOrderDate, 'T', LPAD(H.CreationTime, 6, '0')) AS TIMESTAMP) AS "EventTime",
  H.CreatedByUser AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.OrderPriceUnit * I.OrderQuantity AS "RequisitionAmount",
  'PO Created' AS "RequisitionStatus"
FROM I_PurchaseOrderItemAPI01 AS I
JOIN I_PurchaseOrderAPI01 AS H
  ON I.PurchaseOrder = H.PurchaseOrder
JOIN REQUISITIONS AS R
  ON I.PurchaseRequisition = R.PurchaseRequisition
WHERE I.PurchaseRequisition IS NOT NULL AND I.PurchaseRequisition != ''

UNION ALL

-- 12. Requisition Closed
SELECT
  C.PurchaseRequisition AS "PurchaseRequisitionId",
  'Requisition Closed' AS "ActivityName",
  C.ChangeTimestamp AS "EventTime",
  C.UserName AS "UserId",
  NULL AS "ApproverId",
  R.PurchaseRequisitionType AS "RequisitionType",
  I.CostCenter AS "Department",
  I.RequisitionPrice * I.RequestedQuantity AS "RequisitionAmount",
  I.PurReqnProcessingStatus AS "RequisitionStatus"
FROM CHANGE_DOCS AS C
JOIN REQUISITIONS AS R ON C.PurchaseRequisition = R.PurchaseRequisition
JOIN I_PurchaseRequisitionItemAPI01 AS I ON C.PurchaseRequisition = I.PurchaseRequisition
WHERE C.FieldName = 'EBAKZ' AND C.ValueNew = 'X'

WITH
base_items AS (
    SELECT
        eban.[Purchase requisition number field] AS PurchaseRequisitionId,
        eban.[Purchase requisition item field] AS RequisitionItem,
        eban.[Creation date field] AS CreationDate,
        eban.[Creation time field] AS CreationTime,
        eban.[Created by field] AS CreatedBy,
        eban.[Requisition type field] AS RequisitionType,
        eban.[Company code field] AS CompanyCode,
        eban.[Plant field] AS Plant,
        eban.[Purchasing group field] AS PurchasingGroup,
        eban.[Quantity field] AS Quantity,
        eban.[Net price field] AS NetPrice,
        eban.[Currency field] AS Currency,
        eban.[Material field] AS Material,
        eban.[Deletion indicator field] AS DeletionIndicator,
        eban.[Overall release status field] AS OverallReleaseStatus,
        eban.[Item processing status field] AS ItemProcessingStatus,
        ebkn.[Cost center field] AS CostCenter,
        ebkn.[Department field] AS Department,
        CAST(eban.[Quantity field] * eban.[Net price field] AS DECIMAL(19,4)) AS RequisitionAmount
    FROM [Your SAP schema].EBAN eban
    LEFT JOIN [Your SAP schema].EBKN ebkn
        ON ebkn.[Purchase requisition number field] = eban.[Purchase requisition number field]
       AND ebkn.[Purchase requisition item field] = eban.[Purchase requisition item field]
    WHERE eban.[Creation date field] BETWEEN '[Start date]' AND '[End date]'
      AND ('[Company code filter]' = '' OR eban.[Company code field] = '[Company code filter]')
      AND ('[Document type filter]' = '' OR eban.[Requisition type field] = '[Document type filter]')
      AND ('[Purchasing group filter]' = '' OR eban.[Purchasing group field] = '[Purchasing group filter]')
      AND ('[Plant filter]' = '' OR eban.[Plant field] = '[Plant filter]')
),
created_events AS (
    SELECT
        PurchaseRequisitionId,
        'Requisition Created' AS ActivityName,
        TO_TIMESTAMP(CAST(CreationDate AS NVARCHAR(8)) || LPAD(COALESCE(CAST(CreationTime AS NVARCHAR(6)), '000000'), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        CreatedBy AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        RequisitionType,
        Department,
        RequisitionAmount,
        'Created' AS RequisitionStatus
    FROM base_items
),
submitted_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Requisition Submitted For Approval' AS ActivityName,
        TO_TIMESTAMP(CAST(a.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(a.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        a.[Requester or submitter field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        a.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition approval event source] a
        ON a.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND a.[Purchase requisition item field] = b.RequisitionItem
    WHERE a.[Event type field] = 'SUBMITTED'
       OR a.[Status field] = 'In Approval'
),
amended_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Requisition Amended' AS ActivityName,
        TO_TIMESTAMP(CAST(c.[Change date field] AS NVARCHAR(8)) || LPAD(CAST(c.[Change time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        c.[Change user field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        'Amended' AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition change document source] c
        ON c.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND c.[Purchase requisition item field] = b.RequisitionItem
    WHERE c.[Changed field field] IN ('QUANTITY', 'PRICE', 'MATERIAL', 'DELIVERY_DATE', 'ACCOUNT_ASSIGNMENT')
),
reset_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Approval Reset' AS ActivityName,
        TO_TIMESTAMP(CAST(a.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(a.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        a.[Event user field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        a.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition approval event source] a
        ON a.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND a.[Purchase requisition item field] = b.RequisitionItem
    WHERE a.[Event type field] = 'RESET'
       OR a.[Status field] = 'Approval Reset'
),
step_started_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Approval Step Started' AS ActivityName,
        TO_TIMESTAMP(CAST(a.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(a.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        CAST(NULL AS NVARCHAR(80)) AS UserId,
        a.[Approver or approval group field] AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        a.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition approval event source] a
        ON a.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND a.[Purchase requisition item field] = b.RequisitionItem
    WHERE a.[Event type field] = 'STEP_STARTED'
       OR a.[Status field] = 'Pending Release'
),
step_completed_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Approval Step Completed' AS ActivityName,
        TO_TIMESTAMP(CAST(a.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(a.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        CAST(NULL AS NVARCHAR(80)) AS UserId,
        a.[Approver or approval group field] AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        a.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition approval event source] a
        ON a.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND a.[Purchase requisition item field] = b.RequisitionItem
    WHERE a.[Event type field] = 'STEP_COMPLETED'
       OR a.[Status field] = 'Step Approved'
),
approved_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Requisition Approved' AS ActivityName,
        TO_TIMESTAMP(CAST(a.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(a.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        CAST(NULL AS NVARCHAR(80)) AS UserId,
        a.[Approver or approval group field] AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        a.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition approval event source] a
        ON a.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND a.[Purchase requisition item field] = b.RequisitionItem
    WHERE a.[Event type field] = 'FINAL_APPROVED'
       OR a.[Status field] = 'Approved'
),
rejected_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Requisition Rejected' AS ActivityName,
        TO_TIMESTAMP(CAST(a.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(a.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        CAST(NULL AS NVARCHAR(80)) AS UserId,
        a.[Approver or approval group field] AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        a.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition approval event source] a
        ON a.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND a.[Purchase requisition item field] = b.RequisitionItem
    WHERE a.[Event type field] = 'REJECTED'
       OR a.[Status field] = 'Rejected'
),
withdrawn_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Requisition Withdrawn' AS ActivityName,
        TO_TIMESTAMP(CAST(c.[Change date field] AS NVARCHAR(8)) || LPAD(CAST(c.[Change time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        c.[Change user field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        'Withdrawn' AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition change document source] c
        ON c.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND c.[Purchase requisition item field] = b.RequisitionItem
    WHERE c.[Changed field field] = 'DELETION_INDICATOR'
      AND c.[New value field] IS NOT NULL
      AND c.[New value field] <> ''
),
source_assigned_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Source of Supply Assigned' AS ActivityName,
        TO_TIMESTAMP(CAST(s.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(s.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        s.[Event user field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        'Source Assigned' AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition source assignment source] s
        ON s.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND s.[Purchase requisition item field] = b.RequisitionItem
    WHERE s.[Source identifier field] IS NOT NULL
      AND s.[Source identifier field] <> ''
),
purchase_order_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Purchase Order Created' AS ActivityName,
        TO_TIMESTAMP(CAST(p.[Purchase order creation date field] AS NVARCHAR(8)) || LPAD(CAST(p.[Purchase order creation time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        p.[Purchase order creator field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        'Purchase Order Created' AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition purchase order reference source] p
        ON p.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND p.[Purchase requisition item field] = b.RequisitionItem
    WHERE p.[Purchase order number field] IS NOT NULL
      AND p.[Purchase order number field] <> ''
),
closed_events AS (
    SELECT
        b.PurchaseRequisitionId,
        'Requisition Closed' AS ActivityName,
        TO_TIMESTAMP(CAST(cl.[Event date field] AS NVARCHAR(8)) || LPAD(CAST(cl.[Event time field] AS NVARCHAR(6)), 6, '0'), 'YYYYMMDDHH24MISS') AS EventTime,
        cl.[Event user field] AS UserId,
        CAST(NULL AS NVARCHAR(80)) AS ApproverId,
        b.RequisitionType,
        b.Department,
        b.RequisitionAmount,
        cl.[Status field] AS RequisitionStatus
    FROM base_items b
    INNER JOIN [Your requisition closure source] cl
        ON cl.[Purchase requisition number field] = b.PurchaseRequisitionId
       AND cl.[Purchase requisition item field] = b.RequisitionItem
    WHERE cl.[Event type field] = 'CLOSED'
       OR cl.[Status field] = 'Closed'
)
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM created_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM submitted_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM amended_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM reset_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM step_started_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM step_completed_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM approved_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM rejected_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM withdrawn_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM source_assigned_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM purchase_order_events
UNION ALL
SELECT PurchaseRequisitionId, ActivityName, EventTime, UserId, ApproverId, RequisitionType, Department, RequisitionAmount, RequisitionStatus FROM closed_events
ORDER BY PurchaseRequisitionId, EventTime, ActivityName;