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.
The key fields and their values in the YFS_CHARGE_TRANSACTION table are detailed below.
|STATUS||This field has the following valid values:
|CHARGE_TYPE||This field has the following valid values:
|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:
|The transaction is valid for a call for authorization.
Valid values are:
The following table describes the various amounts logged in the YFS_CHARGE_TRANSACTION table at different points in 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||-
|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|
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.
|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.|
The key field related to payment processing in the YFS_ORDER_HEADER table is PAYMENT_STATUS.
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.
|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
|MAX_REQUEST_AMOUNT||The maximum amount this unique identifier represents before the distribution to another group begins to become Authorized.|
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.
|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.|
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.
|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.|