Your Payments Processing Data Template
Your Payments Processing Data Template
- Recommended attributes to collect
- Key activities to track
- Extraction guidance for ACI Worldwide
Payments Processing Attributes
| Name | Description | ||
|---|---|---|---|
| Activity Name ActivityName | The specific step or status change that occurred in the payment lifecycle. | ||
| Description This attribute defines the event node in the process map, such as 'Payment Request Created' or 'Funds Transferred'. In ACI systems, this is often derived from status codes, audit log operation types, or workflow state changes. Accurate mapping of these technical states to readable business activities is crucial for meaningful visualization. Why it matters It defines the process flow and is necessary to visualize the sequence of operations. Where to get Derived from Status Codes (e.g., 100=Created, 200=Validated) or Audit Log Action columns. Examples Payment Request CreatedPayment AuthorizedPayment SettledPayment Failed | |||
| Event Timestamp EventTimestamp | The specific date and time when the activity occurred. | ||
| Description This attribute records the exact moment an event took place within the ACI environment. It is used to calculate all time-based metrics, including cycle times, approval durations, and throughput rates. High precision (milliseconds) is preferred to accurately sequence rapid automated steps. Why it matters Essential for ordering events and calculating performance durations. Where to get Consult the 'Created Date' or 'Update Date' columns in the transaction history or audit tables. Examples 2023-10-25T08:30:15.000Z2023-10-25T08:30:22.500Z2023-10-26T14:10:00.000Z | |||
| Payment Transaction ID PaymentTransactionId | The unique identifier for the specific payment instruction across the ACI system. | ||
| Description This attribute serves as the central key for the process mining analysis, linking all events related to a single payment request. In ACI Worldwide systems (such as MTS or UPP), this corresponds to the unique reference number assigned to a transaction upon entry. It enables the reconstruction of the end-to-end payment journey from the initial request through validation, approval, and final settlement. Why it matters It is the fundamental Case ID required to group discrete events into process instances. Where to get Check transaction header tables, often labeled as TRN_REF, REFERENCE_NUM, or UUID in the main transaction log. Examples TRX-2023-899102ACI-99281-AAPAY-0019283420231025-9981 | |||
| Department Department | The internal department responsible for the current activity. | ||
| Description Maps the Why it matters Aggregates performance by business function. Where to get Derived from User tables or Organizational Hierarchy mapping. Examples OperationsComplianceTreasuryIT Support | |||
| End-to-End Cycle Time EndToEndCycleTime | The total duration from request creation to settlement. | ||
| Description Calculates the time difference between 'Payment Request Created' and 'Payment Settled'. This serves as the primary metric for the 'Average Payment Transaction Cycle Time' KPI and overall efficiency analysis. Why it matters The top-level metric for process speed. Where to get Computed: Timestamp(Payment Settled) - Timestamp(Payment Request Created). Examples 2 days 4 hours45 minutes12 seconds | |||
| Error Code ErrorCode | The code generated when a payment fails or requires repair. | ||
| Description Captures the specific reason for a 'Payment Failed' or 'Payment Error Identified' event. Grouping by this attribute in the 'Payment Failure and Rework Analysis' dashboard allows the business to identify the most common root causes of failure (e.g., 'Insufficient Funds', 'Invalid Account'). Why it matters Essential for Root Cause Analysis of process failures. Where to get Error logs or status reason columns, often REASON_CODE or RETURN_CODE. Examples R01AM04BE05TECH_ERR_001 | |||
| Event User EventUser | The user ID or system agent responsible for the activity. | ||
| Description Captures who performed the action—whether it was a human user (e.g., for approvals) or a system account (e.g., for automated settlement). This attribute is vital for 'Bottleneck Analysis' to identify if specific users or queues are overloaded. Why it matters Enables resource analysis and segregation of duties auditing. Where to get Audit logs or 'UpdatedBy' columns in transaction tables. Examples SYSTEM_AGENT_01j.doeapprover_group_aBATCH_PROCESS | |||
| Is Rework IsRework | Flag indicating if the payment underwent repetitive activities. | ||
| Description A boolean flag calculated during data processing. It is set to true if activities like 'Payment Details Validated' occur more than once or if an error loop is detected. This drives the 'Payment Rework Rate' KPI. Why it matters Quickly identifies inefficient cases without complex process queries. Where to get Computed in the data pipeline by checking for duplicate activities per case. Examples truefalse | |||
| Payment Amount PaymentAmount | The monetary value of the payment transaction. | ||
| Description Indicates the financial value being transferred. This is a critical context field for analyzing 'Payments Throughput' and prioritizing bottlenecks. High-value payments often undergo more rigorous approval paths (variant analysis) compared to low-value automated flows. Why it matters Allows segmentation by value and calculation of total processed volume. Where to get Transaction detail tables, typically fields like AMT, TRANS_AMOUNT, or PRINCIPAL_AMOUNT. Examples 1500.00250000.5050.001000000.00 | |||
| Payment Currency PaymentCurrency | The ISO currency code for the payment amount. | ||
| Description Specifies the currency in which the Why it matters Required to interpret the Payment Amount correctly. Where to get Transaction detail tables, typically fields like CCY, CURRENCY_CODE, or ISO_CODE. Examples USDEURGBPJPY | |||
| Payment Due Date PaymentDueDate | The date by which the payment must be settled to be considered on time. | ||
| Description Stores the contractual or requested execution date. This date is compared against the actual settlement date to calculate the 'On-Time Payment Rate' KPI and support the 'Payment Due Date Compliance' dashboard. Why it matters The benchmark for measuring SLA compliance and on-time performance. Where to get Transaction instructions, usually VALUE_DATE, EXECUTION_DATE, or DUE_DATE. Examples 2023-11-012023-11-05 | |||
| Payment Type PaymentType | The classification of the payment instrument. | ||
| Description Categorizes the payment (e.g., Wire, ACH, SEPA, RTGS). Different payment types often have vastly different SLAs and process flows. This attribute is a primary dimension for filtering the 'End-to-End Payment Cycle Time' dashboard. Why it matters Critical for distinguishing between high-speed and batched payment flows. Where to get Transaction header, fields like PMT_TYPE, INSTRUMENT_TYPE, or SERVICE_ID. Examples Domestic WireInternational WireACH CreditInstant Payment | |||
| Processing Channel ProcessingChannel | The channel through which the payment was initiated. | ||
| Description Indicates the entry point of the payment, such as Mobile, Web Portal, API, or File Upload. This helps in 'Payment Process Variant Analysis' to see if certain channels are more prone to errors or delays than others. Why it matters Segments performance by input method. Where to get Transaction header, often in columns named CHANNEL, SOURCE_TYPE, or INPUT_METHOD. Examples SWIFTInternet BankingMobile AppFile Upload | |||
| Approval Cycle Time ApprovalCycleTime | Duration spent in the approval phase. | ||
| Description Calculates the time between 'Payment Sent For Approval' and 'Payment Approved' (or Rejected). This specific metric feeds the 'Payment Approval Cycle Time Analysis' dashboard, highlighting delays in human decision-making steps. Why it matters Isolates the human-dependent portion of the process. Where to get Computed: Timestamp(Payment Approved) - Timestamp(Payment Sent For Approval). Examples 4 hours15 minutes | |||
| Beneficiary Name BeneficiaryName | The name of the entity receiving the payment. | ||
| Description Identifies the counterparty in the transaction. Analyzing this field can help identify specific vendors or customers associated with high rework rates or delays, supporting the 'Payment Failure and Rework Analysis'. Why it matters Identifies the target of the payment, useful for customer-centric analysis. Where to get Payment detail lines, fields like CREDITOR_NAME, BENE_NAME, or PAYEE. Examples Acme CorpGlobal Supplies LtdJohn Smith | |||
| Is Payment Late IsPaymentLate | Flag indicating if the payment was settled after the due date. | ||
| Description A boolean flag that compares the actual settlement date against the Why it matters Simplifies compliance reporting. Where to get Computed: SettlementDate > PaymentDueDate. Examples truefalse | |||
| Last Data Update LastDataUpdate | The timestamp of when the record was last extracted or updated in the data model. | ||
| Description Tracks the freshness of the data used in the analysis. This does not represent a process event time, but rather the technical time of data ingestion. It ensures analysts know if they are looking at real-time or historical snapshots. Why it matters Ensures data currency and helps identifying stale data in dashboards. Where to get System time at the moment of the ETL script execution. Examples 2023-10-27T00:00:00.000Z2023-10-27T12:00:00.000Z | |||
| Originating Region OriginatingRegion | The geographical region where the payment request originated. | ||
| Description Indicates the physical or logical location of the requestor. This is useful for 'Payment Process Variant Analysis' to see if specific regions follow non-standard paths or experience higher rejection rates. Why it matters Provides geographical context to process performance. Where to get Transaction header, often derived from Branch Code or Country Code. Examples North AmericaEMEAAPAC | |||
| Reconciliation ID ReconciliationId | Identifier linking the payment to the general ledger or reconciliation record. | ||
| Description This ID is populated when the 'Payment Reconciled' activity occurs. It ensures that the payment in the processing engine matches the entry in the accounting system. Absence of this ID on settled payments indicates reconciliation failures. Why it matters Critical for the 'Payment Reconciliation Efficiency' dashboard. Where to get Reconciliation tables or specific fields like RECON_REF or GL_REF. Examples REC-9921GL-Entry-2023-11 | |||
| Source System SourceSystem | The name of the system where the event data originated. | ||
| Description Identifies the specific application or module within the ACI Worldwide ecosystem (e.g., ACI MTS, ACI UPF) or external systems involved in the flow. This is particularly important when stitching data across multiple ledgers or when the payment touches external clearing houses. Why it matters Provides context on where data was extracted, useful for debugging data lineage. Where to get Hardcoded during extraction or derived from a SystemID column if multiple instances exist. Examples ACI MTSACI UPPSAP GLSwift Gateway | |||
Payments Processing Activities
| Activity | Description | ||
|---|---|---|---|
| Funds Transferred | Indicates that confirmation has been received from the payment network that funds have been successfully debited from the payer account. This is usually captured from an inbound status message from the network. | ||
| Why it matters Confirms the successful execution of the payment by the external network. It marks the start of the settlement period and is a key input for the 'Average Payment Settlement Time' KPI. Where to get This is an explicit event triggered by an incoming status update message (e.g., MT103 from SWIFT, or an ACH confirmation) that updates the payment record. Capture Logged upon receipt of an external confirmation message from the clearing network. Event type explicit | |||
| Payment Approved | A key milestone where an authorized user approves the payment, allowing it to proceed to execution. This is usually captured as an explicit event when the approver takes action in the system's user interface. | ||
| Why it matters This activity is a major checkpoint and often a significant bottleneck. Analyzing wait times before this step and the duration of the approval cycle helps identify opportunities to accelerate payments. Where to get Look for an explicit event in an approval log table or a status change to 'Approved' in the main transaction table that is tied to a specific user action and timestamp. Capture Logged when an authorized user completes the approval action in the system. Event type explicit | |||
| Payment Authorized | Represents the system-level authorization of the payment after human approval, verifying funds or checking against fraud rules. This can be an explicit log entry or inferred from a status change indicating readiness for execution. | ||
| Why it matters This is a critical control point before funds are instructed to move. Delays at this stage can indicate system performance issues or problems with compliance and fraud-check subsystems. Where to get Check for an explicit log in a system processing or security log. Alternatively, it can be inferred from a status update from 'Approved' to 'Authorized for Payment'. Capture Logged by the system's payment engine after passing final internal checks. Event type explicit | |||
| Payment Error Identified | Indicates that the system has detected an issue with the payment at some stage, such as invalid data or a compliance alert. This event is typically logged explicitly with an associated error code. | ||
| Why it matters This activity is the starting point for all rework and exception handling analysis. It is essential for the 'Payment Failure and Rework Analysis' and 'Error Resolution Cycle Time' dashboards. Where to get Look for explicit entries in an error log table or a status change to 'Error' or 'Requires Correction' in the transaction table. These events should be linked to the Payment Transaction ID. Capture An explicit event is logged when the system's validation or processing engine flags an error. Event type explicit | |||
| Payment Request Created | This activity marks the initiation of a new payment transaction within the ACI Worldwide system. It is typically an explicit event recorded when a user or an upstream system submits a payment request, creating a new transaction record with a unique ID. | ||
| Why it matters This is the primary start event for the payment process. Analyzing the time from this activity to completion provides the end-to-end cycle time, which is essential for measuring overall process efficiency. Where to get This is likely an explicit event logged in the core transaction table or a dedicated event log in ACI. Look for a creation timestamp associated with the Payment Transaction ID. Capture Identified by the creation record or an explicit 'Create' event in the transaction log. Event type explicit | |||
| Payment Settled | The final confirmation that the payment process is complete and funds have been credited to the payee, concluding the transaction. This is a critical event, representing the successful end of the payment lifecycle. | ||
| Why it matters This is the primary success end event for the process. It is used to calculate the overall cycle time and throughput, and is essential for nearly all end-to-end performance dashboards. Where to get Typically an explicit event logged when a final settlement confirmation message is received from the network or when the internal ledger is updated to reflect the transaction's completion. Capture Logged upon receipt of a final settlement file or message, updating the status to 'Settled'. Event type explicit | |||
| Payment Confirmed | Represents the internal acknowledgement that the payment has been successfully processed and a confirmation has been received. This often acts as the trigger point for notifying the payee or other internal systems. | ||
| Why it matters This milestone is crucial for measuring due date compliance and On-Time Payment Rate. It provides a clear timestamp for when the organization considers the payment successfully executed. Where to get This is typically inferred from a status change in the payment transaction table to a 'Confirmed' or 'Completed' state after external network confirmation is received. Capture Inferred from a status change to 'Confirmed' or 'Processed'. Event type inferred | |||
| Payment Details Validated | Represents the completion of automated or manual checks to ensure payment details, such as payee information and bank codes, are correct. This activity is often inferred from a change in the transaction's status from 'New' to 'Validated' or 'Pending Approval'. | ||
| Why it matters Tracks the efficiency of the initial data validation steps. Delays here can create upstream bottlenecks and increase the likelihood of payment errors later in the process. Where to get Inferred from status change fields in the main payment transaction table. Compare timestamps between the 'Created' status and a subsequent 'Validated' or similar status. Capture Inferred from a change in the payment status field, for example, from 'Entered' to 'Validated'. Event type inferred | |||
| Payment Error Resolved | Marks the point where a previously identified error has been corrected by a user and the payment is resubmitted for processing. This is often inferred when a payment's status changes from an error state back to a normal processing state. | ||
| Why it matters This activity closes the exception loop. The time between 'Payment Error Identified' and this event is the error resolution cycle time, a key measure of operational efficiency. Where to get Inferred from a status change away from an 'Error' state to a processing state like 'Pending Approval' or 'Validated'. It can also be an explicit user action log. Capture Inferred from a status change out of an error state, indicating a correction has been made. Event type inferred | |||
| Payment Failed | A terminal status indicating the payment could not be completed due to an unrecoverable issue. This is distinct from a resolvable error and represents a definitive failure end state. | ||
| Why it matters Tracking this end event is crucial for calculating the overall payment failure rate. Analyzing the reasons for failure can help improve data quality and process rules. Where to get Inferred from a final, terminal status like 'Failed', 'Cancelled', or 'Rejected by Bank' in the transaction data, which does not subsequently change. Capture Inferred from a terminal failure status in the payment record. Event type inferred | |||
| Payment Instruction Sent | Marks the point where the payment instruction is compiled and transmitted to an external payment network like SWIFT, ACH, or SEPA. ACI systems explicitly log this handoff for audit and tracking purposes. | ||
| Why it matters This is the 'point of no return' for many payment types. Tracking this helps measure the internal processing time before external dependencies take over. Where to get This is almost always an explicit event logged in ACI's transaction or messaging logs, often including a network-specific reference number. Capture An explicit log entry is created when the payment message is sent to the external network. Event type explicit | |||
| Payment Reconciled | Represents the final accounting step where the payment transaction recorded in ACI is matched with bank statements or ledger entries. This can be an explicit event from a reconciliation module or inferred by a status change. | ||
| Why it matters This activity measures the efficiency of the back-office reconciliation process. Delays here can impact the accuracy of financial reporting and hide unsettled payment issues. Where to get This information might come from a dedicated reconciliation module within ACI or an external ERP system. It would be captured via a status update to 'Reconciled' on the payment record. Capture Inferred from a final 'Reconciled' status update, or from reconciliation data joined by Payment ID. Event type inferred | |||
| Payment Rejected | Occurs when an approver denies the payment request, often requiring correction and resubmission. This is an explicit event that halts the forward progress of the payment and initiates a rework loop. | ||
| Why it matters Identifies rework and process inefficiencies. Tracking the frequency of rejections helps diagnose issues with initial data quality or submission policies, supporting rework analysis. Where to get Captured as an explicit event in the approval log or a status change to 'Rejected' in the transaction table. The event may include a reason code for the rejection. Capture Logged when an approver completes the rejection action in the system. Event type explicit | |||
| Payment Sent For Approval | Indicates that the payment has passed initial validation and has been routed for necessary managerial or financial approval. This is typically captured by a status change within the payment workflow. | ||
| Why it matters This marks the beginning of the approval sub-process. Measuring the time from this point to 'Payment Approved' is critical for the 'Payment Approval Cycle Time Analysis' dashboard. Where to get Derived from a change in the payment status field in the transaction data, such as moving to 'Pending Approval'. Capture Inferred from a status change to 'Pending Approval' or similar, along with a corresponding timestamp. Event type inferred | |||
Extraction Guides
Steps
Access the Database Environment: Log in to the SQL Server instance hosting the ACI Postilion Realtime database using SQL Server Management Studio (SSMS) or a compatible client.
Identify Core Tables: Locate the
post_tran(transaction log) andpost_tran_cust(custom data extension) tables. Ensure you haveSELECTpermissions on these objects.Determine the Case Identifier: This extraction uses the
retrieval_reference_nras thePaymentTransactionId. If your implementation uses a different unique key (likesystem_trace_audit_nrcombined withtransmission_date_time), adjust the query selection accordingly.Configure Filter Parameters: Open the query provided below. Locate the variables for
@StartDateand@EndDateat the top of the script. Set these to your desired extraction window (e.g., the last 30 to 90 days) to optimize performance.Review Activity Logic: The query maps ISO 8583 message types (e.g., 0200, 0210) and response codes to the required 14 process mining activities. Review the
CASEstatements to ensure they align with your specific ACI interface configurations.Run the Query: Execute the full script. The query uses
UNION ALLto normalize different transaction states into a single event log format.Verify Data Output: Check the results for the required columns:
PaymentTransactionId,ActivityName, andEventTimestamp. Ensure no critical fields containNULLvalues unexpectedly.Export Data: Right-click the results grid in SSMS and save the output as a CSV file (e.g.,
ACI_Payments_EventLog.csv).Format for ProcessMind: Open the CSV and verify that the
EventTimestampis in a standard format (YYYY-MM-DD HH:MM:SS) and thatPaymentAmountcontains numeric values only.Upload: Import the verified CSV into ProcessMind, mapping the columns to Case ID, Activity, and Timestamp respectively.
Configuration
- Date Range: ACI
post_trantables grow very rapidly. It is highly recommended to limit extraction to a 3-month rolling window or use partition switching if available. - Response Codes: The query assumes
rsp_code = '00'indicates success. If your institution uses different codes for approval/success (e.g., '08' or '10'), update the filters. - Message Types (ISO 8583): The script relies on standard message types (0100/0200 for Requests, 0210 for Responses). Custom message types defined in your
source_node_nameconfiguration may require adjustments. - System Performance: This query uses
NOLOCKhints to prevent blocking live transaction processing. Do not remove these hints in a production environment. - Currencies: Amounts are extracted as raw figures. Ensure
tran_currency_codeis used if multi-currency normalization is required during analysis.
a Sample Query sql
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;