Datatemplate voor de verwerking van crediteurenbetalingen

/* 
   SQL Transformation Script for D365 Finance AP Process
   Assumes data is loaded into Staging tables in a SQL environment (BYOD/Data Lake)
*/

SELECT
    I.InvoiceNumber AS [InvoiceNumber],
    'Invoice Created' AS [Activity],
    I.CreatedDateTime AS [EventTime],
    I.DataAreaId AS [CompanyCode],
    I.InvoiceAccount AS [VendorAccount],
    I.InvoiceAmount AS [InvoiceAmount],
    I.CurrencyCode AS [Currency],
    'D365 FO' AS [SourceSystem],
    GETDATE() AS [LastDataUpdate]
FROM Staging_VendorInvoiceHeaderEntity I

UNION ALL

/* Capture updates to invoice headers */
SELECT
    I.InvoiceNumber,
    'Invoice Updated',
    I.ModifiedDateTime,
    I.DataAreaId,
    I.InvoiceAccount,
    I.InvoiceAmount,
    I.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorInvoiceHeaderEntity I
WHERE I.ModifiedDateTime > I.CreatedDateTime

UNION ALL

/* Invoice Matching Activities */
SELECT
    I.InvoiceNumber,
    'Invoice Matched to PO',
    I.ModifiedDateTime,
    I.DataAreaId,
    I.InvoiceAccount,
    I.InvoiceAmount,
    I.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorInvoiceHeaderEntity I
WHERE I.MatchStatus = 'Matched' -- Adjust value based on system config

UNION ALL

SELECT
    I.InvoiceNumber,
    'Invoice Match Failed',
    I.ModifiedDateTime,
    I.DataAreaId,
    I.InvoiceAccount,
    I.InvoiceAmount,
    I.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorInvoiceHeaderEntity I
WHERE I.MatchStatus = 'Failed'

UNION ALL

/* Workflow Activities */
SELECT
    RelatedContext AS InvoiceNumber,
    CASE 
        WHEN Status = 'Submitted' THEN 'Invoice Submitted for Approval'
        WHEN Status = 'Approved' THEN 'Invoice Approved'
        ELSE 'Workflow Activity'
    END AS [Activity],
    CreatedDateTime AS [EventTime],
    DataAreaId,
    NULL AS [VendorAccount],
    NULL AS [InvoiceAmount],
    NULL AS [Currency],
    'D365 FO',
    GETDATE()
FROM Staging_WorkflowHistoryEntity
WHERE ContextTableId = 12345 -- Replace with TableId for VendInvoiceInfoTable
AND Status IN ('Submitted', 'Approved')

UNION ALL

/* Invoice Posted */
SELECT
    J.InvoiceNumber,
    'Invoice Posted',
    J.PostedDateTime,
    J.DataAreaId,
    J.InvoiceAccount,
    J.InvoiceAmount,
    J.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorInvoiceJournalHeaderEntity J

UNION ALL

/* Payment Block */
SELECT
    I.InvoiceNumber,
    'Payment Block Applied',
    I.ModifiedDateTime,
    I.DataAreaId,
    I.InvoiceAccount,
    I.InvoiceAmount,
    I.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorInvoiceJournalHeaderEntity I
WHERE I.OnHold = 'Yes'

UNION ALL

/* Payment Activities */
SELECT
    J.InvoiceId AS [InvoiceNumber],
    'Payment Journal Created' AS [Activity],
    P.CreatedDateTime AS [EventTime],
    P.DataAreaId,
    P.AccountDisplayValue AS [VendorAccount],
    P.DebitAmount AS [InvoiceAmount],
    P.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId

UNION ALL

SELECT
    J.InvoiceId AS [InvoiceNumber],
    'Payment Journal Approved' AS [Activity],
    P.ModifiedDateTime AS [EventTime],
    P.DataAreaId,
    P.AccountDisplayValue,
    P.DebitAmount,
    P.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
WHERE P.PaymentStatus = 'Approved'

UNION ALL

SELECT
    J.InvoiceId AS [InvoiceNumber],
    'Payment Generated' AS [Activity],
    P.ModifiedDateTime AS [EventTime],
    P.DataAreaId,
    P.AccountDisplayValue,
    P.DebitAmount,
    P.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
WHERE P.PaymentStatus = 'Sent'

UNION ALL

SELECT
    J.InvoiceId AS [InvoiceNumber],
    'Payment Posted' AS [Activity],
    P.PostedDate AS [EventTime],
    P.DataAreaId,
    P.AccountDisplayValue,
    P.DebitAmount,
    P.CurrencyCode,
    'D365 FO',
    GETDATE()
FROM Staging_VendorPaymentJournalLineEntity P
JOIN Staging_VendorInvoiceJournalHeaderEntity J ON P.InvoiceId = J.InvoiceNumber AND P.DataAreaId = J.DataAreaId
WHERE P.IsPosted = 'Yes'

/* T-SQL Extraction Script for D365 AP Payment Processing */
/* Tables required: VendInvoiceInfoTable, VendInvoiceInfoLine, VendInvoiceJour, VendTrans, LedgerJournalTrans, LedgerJournalTable, WorkflowTrackingStatusTable */

DECLARE @StartDate DATETIME = '2023-01-01 00:00:00';
DECLARE @EndDate DATETIME = GETDATE();

WITH RawData AS (
    /* 1. Invoice Created: Pending Invoice Header Creation */
    SELECT 
        T1.Num AS InvoiceNumber,
        'Invoice Created' AS Activity,
        T1.CreatedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        T1.InvoiceAccount AS VendorAccount,
        T1.DataAreaId AS CompanyCode,
        CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        T1.DueDate AS DueDate,
        T1.PurchId AS PurchaseOrderNumber,
        T1.CreatedBy AS UserId,
        T1.VendorName AS VendorName,
        T1.DocumentDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.VendInvoiceInfoTable T1
    WHERE T1.CreatedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 2. Invoice Updated: Modifications to Pending Invoice */
    SELECT 
        T1.Num AS InvoiceNumber,
        'Invoice Updated' AS Activity,
        T1.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        T1.InvoiceAccount AS VendorAccount,
        T1.DataAreaId AS CompanyCode,
        CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        T1.DueDate AS DueDate,
        T1.PurchId AS PurchaseOrderNumber,
        T1.ModifiedBy AS UserId,
        T1.VendorName AS VendorName,
        T1.DocumentDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.VendInvoiceInfoTable T1
    WHERE T1.ModifiedDateTime BETWEEN @StartDate AND @EndDate
      AND T1.ModifiedDateTime > T1.CreatedDateTime

    UNION ALL

    /* 3. Invoice Matched to PO: Line Matching Success */
    SELECT 
        H.Num AS InvoiceNumber,
        'Invoice Matched to PO' AS Activity,
        L.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        H.InvoiceAccount AS VendorAccount,
        H.DataAreaId AS CompanyCode,
        CAST(H.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        H.DueDate AS DueDate,
        H.PurchId AS PurchaseOrderNumber,
        L.ModifiedBy AS UserId,
        H.VendorName AS VendorName,
        H.DocumentDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.VendInvoiceInfoLine L
    JOIN dbo.VendInvoiceInfoTable H ON L.TableRefId = H.TableRefId AND L.DataAreaId = H.DataAreaId
    WHERE L.MatchStatus = 1 /* 1 usually denotes Matched/Passed in enum */
      AND L.ModifiedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 4. Invoice Match Failed: Line Matching Discrepancy */
    SELECT 
        H.Num AS InvoiceNumber,
        'Invoice Match Failed' AS Activity,
        L.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        H.InvoiceAccount AS VendorAccount,
        H.DataAreaId AS CompanyCode,
        CAST(H.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        H.DueDate AS DueDate,
        H.PurchId AS PurchaseOrderNumber,
        L.ModifiedBy AS UserId,
        H.VendorName AS VendorName,
        H.DocumentDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.VendInvoiceInfoLine L
    JOIN dbo.VendInvoiceInfoTable H ON L.TableRefId = H.TableRefId AND L.DataAreaId = H.DataAreaId
    WHERE L.MatchStatus = 2 /* 2 usually denotes Failed in enum */
      AND L.ModifiedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 5. Invoice Submitted for Approval: Workflow Submission */
    SELECT 
        T1.Num AS InvoiceNumber,
        'Invoice Submitted for Approval' AS Activity,
        W.CreatedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        T1.InvoiceAccount AS VendorAccount,
        T1.DataAreaId AS CompanyCode,
        CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        T1.DueDate AS DueDate,
        T1.PurchId AS PurchaseOrderNumber,
        W.User AS UserId,
        T1.VendorName AS VendorName,
        T1.DocumentDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.WorkflowTrackingStatusTable W
    JOIN dbo.VendInvoiceInfoTable T1 ON W.ContextRecId = T1.RecId 
    WHERE W.TrackingStatus = 1 /* Submitted */
      AND W.ContextTableId = 1425 /* TableId for VendInvoiceInfoTable, adjust if different in version */
      AND W.CreatedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 6. Invoice Approved: Workflow Completion */
    SELECT 
        T1.Num AS InvoiceNumber,
        'Invoice Approved' AS Activity,
        W.CreatedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        T1.InvoiceAccount AS VendorAccount,
        T1.DataAreaId AS CompanyCode,
        CAST(T1.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        T1.DueDate AS DueDate,
        T1.PurchId AS PurchaseOrderNumber,
        W.User AS UserId,
        T1.VendorName AS VendorName,
        T1.DocumentDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.WorkflowTrackingStatusTable W
    JOIN dbo.VendInvoiceInfoTable T1 ON W.ContextRecId = T1.RecId 
    WHERE W.TrackingStatus = 2 /* Completed/Approved */
      AND W.ContextTableId = 1425
      AND W.CreatedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 7. Invoice Posted: Creation of VendInvoiceJour */
    SELECT 
        J.InvoiceId AS InvoiceNumber,
        'Invoice Posted' AS Activity,
        J.CreatedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        J.InvoiceAccount AS VendorAccount,
        J.DataAreaId AS CompanyCode,
        CAST(J.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        J.DueDate AS DueDate,
        J.PurchId AS PurchaseOrderNumber,
        J.CreatedBy AS UserId,
        J.InvoicingName AS VendorName,
        J.InvoiceDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.VendInvoiceJour J
    WHERE J.CreatedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 8. Payment Block Applied: Updated on VendTrans */
    SELECT 
        J.InvoiceId AS InvoiceNumber,
        'Payment Block Applied' AS Activity,
        VT.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        J.InvoiceAccount AS VendorAccount,
        J.DataAreaId AS CompanyCode,
        CAST(J.InvoiceAmount AS DECIMAL(18,2)) AS InvoiceAmount,
        J.DueDate AS DueDate,
        J.PurchId AS PurchaseOrderNumber,
        VT.ModifiedBy AS UserId,
        J.InvoicingName AS VendorName,
        J.InvoiceDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.VendTrans VT
    JOIN dbo.VendInvoiceJour J ON VT.Invoice = J.InvoiceId AND VT.AccountNum = J.InvoiceAccount AND VT.DataAreaId = J.DataAreaId
    WHERE VT.Approved = 0 /* 0 indicates Not Approved/Blocked */
      AND VT.ModifiedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 9. Payment Journal Created: Line added to Journal */
    SELECT 
        LJT.Invoice AS InvoiceNumber,
        'Payment Journal Created' AS Activity,
        LJT.CreatedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        LJT.AccountNum AS VendorAccount,
        LJT.DataAreaId AS CompanyCode,
        CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
        NULL AS DueDate,
        NULL AS PurchaseOrderNumber,
        LJT.CreatedBy AS UserId,
        NULL AS VendorName,
        LJT.TransDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.LedgerJournalTrans LJT
    WHERE LJT.AccountType = 2 /* Vendor */
      AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
      AND LJT.CreatedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 10. Payment Journal Approved: Workflow on Journal Header */
    SELECT 
        LJT.Invoice AS InvoiceNumber,
        'Payment Journal Approved' AS Activity,
        LJH.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        LJT.AccountNum AS VendorAccount,
        LJT.DataAreaId AS CompanyCode,
        CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
        NULL AS DueDate,
        NULL AS PurchaseOrderNumber,
        LJH.ModifiedBy AS UserId,
        NULL AS VendorName,
        LJT.TransDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.LedgerJournalTable LJH
    JOIN dbo.LedgerJournalTrans LJT ON LJH.JournalNum = LJT.JournalNum AND LJH.DataAreaId = LJT.DataAreaId
    WHERE LJH.WorkflowApprovalStatus = 2 /* Approved */
      AND LJT.AccountType = 2 
      AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
      AND LJH.ModifiedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 11. Payment Generated: Payment Status Changed to Sent */
    SELECT 
        LJT.Invoice AS InvoiceNumber,
        'Payment Generated' AS Activity,
        LJT.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        LJT.AccountNum AS VendorAccount,
        LJT.DataAreaId AS CompanyCode,
        CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
        NULL AS DueDate,
        NULL AS PurchaseOrderNumber,
        LJT.ModifiedBy AS UserId,
        NULL AS VendorName,
        LJT.TransDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.LedgerJournalTrans LJT
    WHERE LJT.PaymentStatus = 2 /* Sent/Generated */
      AND LJT.AccountType = 2 
      AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
      AND LJT.ModifiedDateTime BETWEEN @StartDate AND @EndDate

    UNION ALL

    /* 12. Payment Posted: Journal Line Posted */
    SELECT 
        LJT.Invoice AS InvoiceNumber,
        'Payment Posted' AS Activity,
        LJT.ModifiedDateTime AS EventTime,
        'D365 FO' AS SourceSystem,
        GETDATE() AS LastDataUpdate,
        LJT.AccountNum AS VendorAccount,
        LJT.DataAreaId AS CompanyCode,
        CAST(LJT.AmountCurCredit AS DECIMAL(18,2)) AS InvoiceAmount,
        NULL AS DueDate,
        NULL AS PurchaseOrderNumber,
        LJT.ModifiedBy AS UserId,
        NULL AS VendorName,
        LJT.TransDate AS InvoiceDate,
        'Unknown' AS Department
    FROM dbo.LedgerJournalTrans LJT
    WHERE LJT.Posted = 1 /* Posted */
      AND LJT.AccountType = 2
      AND LJT.Invoice IS NOT NULL AND LJT.Invoice <> ''
      AND LJT.ModifiedDateTime BETWEEN @StartDate AND @EndDate
)
SELECT *
FROM RawData
WHERE InvoiceNumber IS NOT NULL AND InvoiceNumber <> ''
ORDER BY InvoiceNumber, EventTime;