Votre modèle de données pour le traitement des paiements

DECLARE @StartDate DATETIME = '2023-01-01 00:00:00';
DECLARE @EndDate DATETIME = '2023-01-31 23:59:59';

/* 1. Payment Request Created: Initial transaction request received */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Request Created' AS ActivityName,
    t.datetime_req AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Origination' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.message_type IN ('0100', '0200') -- Authorization/Financial Request

UNION ALL

/* 2. Payment Details Validated: Inferred after request but before routing */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Details Validated' AS ActivityName,
    DATEADD(second, 1, t.datetime_req) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Compliance' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.message_type IN ('0100', '0200')
  AND t.rsp_code = '00' -- Implies validation passed

UNION ALL

/* 3. Payment Sent For Approval: Routing to internal authorization */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Sent For Approval' AS ActivityName,
    DATEADD(second, 2, t.datetime_req) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Risk Management' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.message_type IN ('0100', '0200')
  AND t.tran_amount_req > 1000 -- Example threshold for approval logic

UNION ALL

/* 4. Payment Approved: Successful response code logic */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Approved' AS ActivityName,
    t.datetime_rsp AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'Approver' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Risk Management' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code = '00'
  AND t.message_type IN ('0110', '0210')

UNION ALL

/* 5. Payment Rejected: Specific rejection codes */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Rejected' AS ActivityName,
    t.datetime_rsp AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    t.rsp_code AS ErrorCode,
    1 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Risk Management' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code IN ('51', '05', '61') -- Insufficient funds, Do not honor, etc.

UNION ALL

/* 6. Payment Authorized: Successful authorization completion */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Authorized' AS ActivityName,
    DATEADD(millisecond, 500, t.datetime_rsp) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Operations' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code = '00'
  AND t.message_type = '0110' -- Authorization Response

UNION ALL

/* 7. Payment Instruction Sent: Handoff to Sink Node */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Instruction Sent' AS ActivityName,
    DATEADD(second, 1, t.datetime_req) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    t.sink_node_name AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Network Operations' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.sink_node_name IS NOT NULL
  AND t.message_type IN ('0200', '0100')

UNION ALL

/* 8. Funds Transferred: External network confirmation */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Funds Transferred' AS ActivityName,
    t.datetime_rsp AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    t.sink_node_name AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Treasury' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code = '00'
  AND t.message_type = '0210' -- Financial Response

UNION ALL

/* 9. Payment Confirmed: Final acknowledgment */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Confirmed' AS ActivityName,
    DATEADD(second, 5, t.datetime_rsp) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Customer Service' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code = '00'
  AND t.message_type = '0210'

UNION ALL

/* 10. Payment Settled: Settlement/Reconciliation message */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Settled' AS ActivityName,
    ISNULL(t.settle_date, t.datetime_rsp) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'Settlement Engine' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Accounting' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.message_type = '0500' -- Reconciliation
  AND t.rsp_code = '00'

UNION ALL

/* 11. Payment Failed: System Errors */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Failed' AS ActivityName,
    t.datetime_rsp AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    t.rsp_code AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'IT Operations' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code IN ('91', '96', '06') -- Issuer down, System malfunction

UNION ALL

/* 12. Payment Error Identified: General Error */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Error Identified' AS ActivityName,
    t.datetime_rsp AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    t.rsp_code AS ErrorCode,
    1 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Compliance' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code NOT IN ('00')
  AND t.message_type IN ('0210', '0110')

UNION ALL

/* 13. Payment Error Resolved: Reversal or Correction followed by Success */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Error Resolved' AS ActivityName,
    t.datetime_req AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'System' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    1 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Operations' AS Department
FROM post_tran t WITH (NOLOCK)
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.message_type IN ('0400', '0420') -- Reversal/Advice

UNION ALL

/* 14. Payment Reconciled: Batch processing flag from Custom Table */
SELECT 
    t.retrieval_reference_nr AS PaymentTransactionId,
    'Payment Reconciled' AS ActivityName,
    ISNULL(c.recon_date, DATEADD(hour, 24, t.datetime_req)) AS EventTimestamp,
    CAST(t.tran_amount_req AS DECIMAL(18,2)) AS PaymentAmount,
    t.tran_currency_code AS PaymentCurrency,
    'Recon Module' AS EventUser,
    t.source_node_name AS ProcessingChannel,
    t.tran_type AS PaymentType,
    NULL AS PaymentDueDate,
    NULL AS ErrorCode,
    0 AS IsRework,
    NULL AS EndToEndCycleTime,
    'Finance' AS Department
FROM post_tran t WITH (NOLOCK)
JOIN post_tran_cust c WITH (NOLOCK) ON t.post_tran_cust_id = c.post_tran_cust_id
WHERE t.datetime_req BETWEEN @StartDate AND @EndDate
  AND t.rsp_code = '00'
  AND t.message_type = '0210'
  AND c.recon_date IS NOT NULL;