Commerce Service Center uses the following database tables to store customer service information. These tables are installed when you run the CIM script.
The csr_order_cmts table is used by the Commerce order repository. The Nucleus component for this repository is /atg/commerce/order/OrderRepository. The rest of the tables described in this section are used by the Commerce Service Center returns and exchanges repository (/atg/commerce/custsvc/).
CsrRepository
csr_order_cmts
This table stores agent comments associated with orders.
Column | Data Type | Constraint | Description |
|---|---|---|---|
comment_id (primary key) | varchar(40) | not null | The unique ID associated with the comment. |
order_id (primary key) | varchar(40) | not null | The ID of the order the comment is associated with. References dcspp_order (order_id). |
agent_id | varchar(40) | null | The profile ID of the agent who submitted the comment. |
comment_data | varchar | not null | The text of the comment. |
creation_date | timestamp | null | The date and time when the comment was submitted. |
version | integer | not null | The GSA version of the repository item. |
csr_exch
This table stores information about order exchanges and returns.
Column | Data Type | Constraint | Description |
|---|---|---|---|
id (primary key) | varchar(40) | not null | The unique ID of the return. |
order_id | varchar(40) | not null | The ID of the order the return is associated with. References dcspp_order (order_id). |
created_date | timestamp | not null | The date and time the return was submitted. |
status | varchar(40) | not null | The current status of the return. Possible values: pending_customer_action, partial_return, full_return, complete. |
rma | varchar(40) | null | The RMA number associated with the return. (This value is the same as the ID of the return.) |
repl_order_id | varchar(40) | null | The ID of the order containing the replacement items for the return. |
bal_pmt_id | varchar(40) | null | The ID of the balance payment for the return. |
sugg_tax_refund | double precision | not null | The suggested tax refund calculated by return processing. |
actl_tax_refund | double precision | not null | The actual tax refund given by the agent. |
sugg_ship_refund | double precision | not null | The suggested shipping refund calculated by return processing. |
actl_ship_refund | double precision | not null | The actual shipping refund given by the agent. |
other_refund | double precision | not null | Other refund types. |
proc_immed | Boolean | not null | Boolean indicating whether the return should be processed immediately (i.e., before the return shipment is received). |
processed | Boolean | not null | Boolean indicating whether the return has been processed. |
origin_of_return | enum | null | The numeric code for the origin of the return. |
agent_id | varchar(40) | null | The unique ID of the agent. |
csr_exch_cmts
This table stores agent comments associated with exchanges or returns.
Column | Data Type | Constraint | Description |
|---|---|---|---|
comment_id (primary key) | varchar(40) | not null | The unique ID of this comment. |
return_id | varchar(40) | not null | The ID of the return the comment is associated with. References csr_exch (id). |
agent_id | varchar(40) | null | The profile ID of the agent who submitted the comment. |
comment_data | varchar | not null | The text of the comment. |
creation_date | timestamp | null | The date and time when the comment was submitted. |
version | integer | not null | The GSA version number of the repository item. |
csr_exch_reasons
This table stores the return reason codes available to agents.
Column | Data Type | Constraint | Description |
|---|---|---|---|
id (primary key) | varchar(40) | not null | The unique ID of the return reason. |
description | varchar(254) | not null | The description of the return reason. |
csr_exch_item_disp
This table stores the returned item dispositions available to agents.
Column | Data Type | Constraint | Description |
|---|---|---|---|
id (primary key) | varchar(40) | not null | The unique ID of the disposition. |
description | varchar(254) | not null | The description of the disposition. |
upd_inventory | Boolean | not null | Boolean indicating whether the inventory should be updated when an item is returned with this disposition. |
csr_return_fee
This table stores the processing fees associated with returns or exchanges.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id (primary key) | varchar(40) | not null | The unique ID of the return or exchange. References csr_exch (id). |
return_fee | double precision | not null | The amount of the return fee. |
csr_exch_item
This table stores information about items that have been marked for return or exchange.
Column | Data Type | Constraint | Description |
|---|---|---|---|
id (primary key) | varchar(40) | not null | The unique ID of the item being returned. |
commerce_item_id | varchar(40) | not null | The ID in the order repository of the commerce item being returned. |
shipping_group_id | varchar(40) | not null | The ID in the order repository of the shipping group that the item was shipped in. References dcspp_ship_group (shipping_group_id). |
quantity_to_return | long(19,0) | not null | The quantity of the item being returned. |
quantity_to_repl | long(19,0) | not null | The quantity of the item to replace. |
reason | varchar(40) | not null | The ID of the return reason code for the item. References csr_exch_reasons (id). |
ret_shipment_req | Boolean | not null | Boolean indicating whether return shipment of the item is required before processing the refund or exchange. |
bonus_refund | Boolean | not null | Boolean indicating whether any bonus refund is associated with this item. |
quantity_received | long(19,0) | not null | The quantity of the item that has been received through return shipment. |
disposition | varchar(40) | null | The ID of the disposition code for the item. References csr_exch_item_disp (id). |
refund_amount | double precision | not null | The amount to be refunded for this item. |
status | varchar(40) | not null | The return status of the item. Possible values: return_not_required, awaiting_return, partial_return, returned. |
exch_ref | varchar(40) | not null | The ID of the return that the item is associated with. |
sugg_ship_refund | double-precision | not null | Suggested shipping refund amount. |
actl_ship_refund | double-precision | not null | The actual shipping refund amount. |
sugg_tax_refund | double-precision | not null | Suggested tax refund amount. |
actl_tax_refund | double-precision | not null | The actual tax refund amount. |
csr_exch_repl_item
This table stores information about a replacement items in an exchange.
Column | Data Type | Constraint | Description |
|---|---|---|---|
id (primary key) | varchar(40) | not null | The unique ID of the replacement item. |
sku_id | varchar(40) | not null | The SKU number of the replacement item. |
quantity | long(19,0) | not null | The quantity of the replacement item. |
csr_exch_repl_itms
This table associates exchanged items with their replacement items.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_item_id(primary key) | varchar(40) | not null | The ID of the exchanged item. References csr_exch_item (id). |
repl_item_id | set | not null | The ID of the replacement item. References csr_exch_repl_item (id). |
csr_exch_items
This table associates returned items with their returns.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id (primary key) | varchar(40) | not null | The ID of the return. References csr_exch (id). |
exchange_item_id (primary key) | set | not null | The ID of the replacement item. References csr_exch_item (id). |
csr_exch_method
This table stores information about the refund associated with a return.
Column | Data Type | Constraint | Description |
|---|---|---|---|
id | varchar(40) | not null | The unique ID of the refund. |
type | enum | not null | The type of refund. 1=credit card; 2=store credit. |
amount | double precision | null | The amount of the refund. |
csr_exch_methods
This table associates refunds with their returns.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id | varchar(40) | not null | The ID of the return. References csr_exch (id). |
exchange_method_id (primary key) | set | not null | The ID of the refund. References csr_exch_method (id). |
csr_exch_ipromos
This table stores information about the item promotion IDs used to determine refund values.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id | varchar(40) | not null | The unique ID of the exchange. |
promotion | string | not null | The name of the promotion. |
csr_exch_opromos
This table stores information about the order promotion IDs used to determine refund values.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id | varchar(40) | not null | The unique ID of the exchange. |
promotion | string | not null | The name of the promotion. |
csr_cc_exch_method
This table stores information about credits applied to credit cards.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_method_id | varchar(40) | not null | The ID of the refund. References csr_exch_method (id). |
payment_group_id | varchar(40) | not null | The ID of the payment group the refund is being credited to. |
csr_sc_exch_method
This table stores information specific to store credits.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_method_id | varchar(40) | not null | The ID of the refund. References csr_exch_method (id). |
payment_group_id | varchar(40) | null | This field is not used. |
sc_id | varchar(40) | null | The unique ID of the store credit. |
csr_promo_adjust
This table stores information about the change in promotion values for a return.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id (primary key) | varchar(40) | not null | The unique ID of the exchange |
value_adjust | map | null | The value of the change |
csr_nonreturn_adj
This table stores information specific to non-Return Item Adjustments.
Column | Data Type | Constraint | Description |
|---|---|---|---|
exchange_id (primary key) | varchar(40) | not null | The ID of the exchange. |
ica_id (primary key) | set | not null | The Item Cost Adjustment ID. |
csr_item_adj
This table stores information specific to item adjustments.
Column | Data Type | Constraint | Description |
|---|---|---|---|
ica_id (primary key) | varchar(40) | not null | The ID of the Item Cost Adjustment. |
commerce_item_id | varchar(40) | not null | The ID of the commerce item. |
shipping_group_id | varchar(40) | not null | The ID of the shipping group. |
quantity_adj | long | null | The quantity of adjusted items |
amount_adj | double-precision | null | The amount of the adjusted item. |
ods_adj | double-precision | null | The amount of the order discount share adjustment. |
mas_adj | double-precision | null | The amount of the manual adjustment share adjustment. |
tax_adj | double-precision | null | The tax adjustment. |
shipping_adj | double-precision | null | The shipping adjustment. |
