Database details

The Sterling Order Management System Software database holds payment data mainly in six tables, YFS_CHARGE_TRANSACTION, YFS_CHARGE_TRAN_DIST, YFS_ORDER_HEADER, YFS_CHARGE_TRAN_REQUEST, YFS_CHARGE_TRAN_RQ_MAP, and YFS_PMNT_TRANS_ERROR.

The system uses the YFS_CHARGE_TRANSACTION table as the driver table to interface with the time-triggered transactions for processing authorizations and charges. The driver table also serves as the journal for all credits and debits against the order at any time.

YFS_CHARGE_TRANSACTION table

The key fields and their values in the YFS_CHARGE_TRANSACTION table are detailed below.

Table 1. YFS_CHARGE_TRANSACTION table fields
Field Description
STATUS This field has the following valid values:
  • OPEN - A new request is created in the YFS_CHARGE_TRANSACTION table. These records are picked up by the Payment transactions.
  • CLOSED - Payment collection was performed external to Sterling Order Management System Software and reported collection details through the recordCollection() API or payments were authorized or collected using the Payment Execution Time-triggered Transaction.
  • CHECKED - The record in YFS_CHARGE_TRANSACTION table has been validated. No further processing occurs on these records.
  • ERROR - Authorization or charging of the associated payment method failed.
CHARGE_TYPE This field has the following valid values:
  • ADDITION - Ordered Quantity of an order line has increased.
  • ADJUSTMENTS - There is mismatch between user-specified TotalAmount of an order and the calculated TotalAmount.
  • AUTHORIZATION - An authorization is requested. The request can be created by calling the requestCollection API or it can be created through the Payment Collection time-triggered transaction.
  • CANCEL - An order or part of an order was cancelled.
  • CASH_BACK - Cashback is requested.
  • CHANGE_PRICE - An order has been repriced. An order can be repriced by changing unit price, charges, and taxes.
  • CHARGE - A charge is requested. The request can be created by calling the requestCollection API or it can be created through the Payment Collection time-triggered transaction. They place such requests for open orders or shipments.
  • CREATE_ORDER - An order was created in the system.
  • ORDER_INVOICE - An order has been partially or completely invoiced, using CREATE_ORDER_INVOICE transaction.
  • REQUEST_SETTLEMENT - Ordered Quantity (partially or completely) of an order line has been settled before invoicing. This occurs during pre-settlement requests.
  • REFUND_SETTLEMENT - Amount of the settlement has been refunded for the refund amount.
  • RETURN - Return order is partially or completely invoiced.
 
  • SHIPMENT - An order or part of an order has shipped and then invoiced using the Create Shipment Invoice time-triggered transaction.
  • SHIPMENT_ADJUSTMENT - This charge request is created if any adjustment is needed after an order is completely shipped.
  • SPLIT_LINE - Order line is split and the new line requires payment processing.
  • TRANSFER_IN - This record is displayed when amounts have been transferred in from another order. Clicking on this link takes the user to the order the amounts have been transferred from.
  • TRANSFER_OUT - This record is displayed when amounts have been transferred to another order. Clicking on this link takes the user to the order the amounts have been transferred to.
CREDIT_AMOUNT The amount credited to the customer against the associated payment method for the order. The amount reflects funds collected from the customer. It increases only after actual fund collections have taken place.
DEBIT_AMOUNT The amount debited to the customer against this order.
BOOK_AMOUNT The open order amount. At any point, it reflects the total order amount that has not yet shipped. The amount is increased by order creation, addition of Ordered Quantity, addition of lines, increase in price, charges and taxes and reduced by cancellations, reduction in prices, charges and taxes, and invoicing.
OPEN_AUTHORIZED_ AMOUNT The amount authorized against the customer's payment method. The authorizations are used for actual fund collection.
REQUEST_AMOUNT The amount for which an authorization or charge request has been made.
SETTLED_AMOUNT The amount for which a pre-settlement request has been made.
USER_EXIT_STATUS This field gets updated to 'INVOKED' when Payment Execution transaction picks up records for processing. While waiting on processing from an external payment system, this field is updated to ‘ONLINE', which prevents agents from picking up the record. It is set back to <blank> when the transaction returns successfully. If this field remains populated, it indicates that a problem occurred with the external system call and requires manual intervention.
VOID_TRANSACTION The charge transaction was created for a void, not a return. Valid values are:
  • V - Void
  • R - Forced Refund
  • S - Submitted. The original charge transaction for which the void or refund was created is updated with this value to indicate a void or refund was submitted for the record.
  • C - Closed error record, already recreated
  • NULL - No value
CALL_FOR_AUTH_
STATUS
 The transaction is valid for a call for authorization. Valid values are:
  • REQUIRES_CALL - a call for authorization must be placed
  • AUTH_CODE_ACQUIRED - the authorization code has been entered
  • CLOSED - the payment gateway has been contacted and the funds transferred
  • NULL - No value

The following table describes the various amounts logged in the YFS_CHARGE_TRANSACTION table at different points in the order life cycle.

Table 2. Payment charge amounts within the order life cycle
When Charge Type Credit Amount Debit Amount Book Amount Authorized Amount Requested Amount
Creation of Order ORDER_CREATE     + TOTAL AMOUNT on order    
Authorizations performed on front-end and passed with CreateOrder XML AUTHORIZATION       + Authorized Amount as passed  
Funds collected on front-end and passed with CreateOrder XML CHARGE + Charge Amount as passed        
Cancellations CANCEL     - Cancel Amount as    
Shipments when payment collection is recorded in the Console database SHIPMENT   + Shipment Amount - Shipment Amount    
Shipments when payment collection is not recorded in the Console database SHIPMENT + Shipment Amount + Shipment Amount - Shipment Amount    
Creation of authorization requests AUTHORIZATION         Authorization amount
Creation of charge requests CHARGE         Charge Amount
Actual Authorization         Amount Authorized  
Actual Fund Collection CHARGE Amount Collected        
Reversal of Authorization AUTHORIZATION     -
Amount
Authorized		    
Price Change through API/ Console CHANGE_PRICE     Price Change Amount    
Debit Memo ADJUSTMENT   Amount on Memo      
Credit Memo ADJUSTMENT   Negative amount on the memo      
Return RETURN   Negative of the total credit given to customer      
Ordered quantity of line(s) increases ADDITION     Additional amount    
Order is invoiced ORDER_INVOICE   Invoiced Amount Negative of Invoiced amount    
Order line is split SPLIT_LINE     Change in Total Amount    
Creation of Last Shipment SHIPMENT_ ADJUSTMENT     Difference between order book amount and total invoiced amount    
Creation of Return Order TRANSFER_IN Amount transferred to order        
Creation of Return Order TRANSFER_OUT Negative of amount transferred to order        
Deferred Credit DEFERRED_CREDIT Amount Deferred        

YFS_CHARGE_TRAN_DIST table

This table contains records for pre-collected funds that are being refunded. It also contains the keys of the pre-collected charge transactions (transfer in, charge), the charge transactions to which it was refunded (transfer out, negative charge), and the amount refunded.

Table 3. YFS_CHARGE_TRAN_DIST table fields
Field Description
DISTRIBUTED_FROM_CT_KEY The charge transaction that is associated with this charge transaction distribution. This is the charge transaction that distributed the funds.
DISTRIBUTED_TO_CT_KEY The charge transaction that is associated with this charge transaction distribution. This is the charge transaction that funds were distributed to.
CHARGE_AMOUNT The amount that was distributed to the charge transaction record, DISTRIBUTED_TO_CT_KEY, from the charge transaction record, DISTRIBUTED_TO_CT_KEY.
CASH_BACK The charge transaction that is associated with this charge transaction distribution. This is the charge transaction for the cashback amount.

YFS_ORDER_HEADER table

The key field related to payment processing in the YFS_ORDER_HEADER table is PAYMENT_STATUS.

YFS_CHARGE_TRAN_REQUEST table

This table contains the requests for payment authorization and identifiers. The combination of the ORDER_HEADER_KEY and the identifier creates a set of unique groups for the order. Authorizations will be split such that each authorization is for only one identifier.

Table 4. YFS_CHARGE_TRAN_REQUEST table fields
Field Description
CHARGE_TRAN_REQUEST_KEY Primary key
ORDER_HEADER_KEY The order the request is for.
CHARGE_TRAN_REQUEST_ID The unique identifier for a request of the amount on an order.
PAYMENT_STATUS Similar to the payment status in the YFS_ORDER_HEADER table.
REQUEST_SEQUENCE The order in which the groups are authorized. Records with REQUEST_SEQUENCE not null are processed first; null sequences are ordered by CHARGE_TRAN_REQUEST_ID, in ascending order.
MAX_REQUEST_AMOUNT The maximum amount this unique identifier represents before the distribution to another group begins to become Authorized.

YFS_CHARGE_TRAN_RQ_MAP table

This table maps the charge transaction requests to the charge transactions filling them. Records are no longer inserted into this table in dynamic CTR mode.

Table 5. YFS_CHARGE_TRAN_RQ_MAP table fields
Field Description
CHARGE_TRAN_REQUEST_ MAP_KEY Primary key
CHARGE_TRANSACTION_KEY Reference to the YFS_CHARGE_TRANSACTION table.
CHARGE_TRAN_REQUEST_ KEY Reference to the YFS_CHARGE_TRAN_REQUEST table.
REQUEST_AMOUNT The amount requested on the mapped charge transaction.
PROCESSED_AMOUNT The amount processed on the mapped charge transaction.

YFS_PMNT_TRANS_ERROR table

This table contains a set of error messages obtained from the user exit output during payment processing. When records for a charge transaction are removed, any transaction error associated to the charge transaction must also be removed. This applies to the changeOrder() API, deleteOrder() API, and the PurgeOrderAgent.

Table 6. YFS_PMNT_TRANS_ERROR table fields
Field Description
PMNT_TRANS_ERROR_KEY The unique identifier that is associated with this payment transaction error record.
CHARGE_TRANSACTION_KEY The charge transaction that is associated with this payment transaction error message. Each charge transaction may have multiple messages.
MESSAGE_TYPE The type of error message for this payment transaction error record.
MESSAGE The error messages for this payment transaction error record.