ATG Commerce Service Center uses the following database tables to store customer service information. These tables are installed by the <ATG9dir>/CSC9.3/DCS-CSR/sql/db_components/database-vendor/DCS-CSR_ddl.sql 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 ATG 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 |
|---|---|---|
comment_id | varchar(40) | not null |
(primary key) | The unique ID associated with the comment. | |
order_id | varchar(40) | not null |
(primary key) | 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(254) | 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 |
|---|---|---|
id | varchar(40) | not null |
(primary key) | 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. | ||
sc_recipient | varchar(40) | null |
The profile ID of the customer receiving any store credits associated with this return. | ||
proc_immed | tinyint | not null |
Boolean indicating whether the return should be processed immediately (i.e., before the return shipment is received). | ||
processed | tinyint | not null |
Boolean indicating whether the return has been processed. | ||
origin_of_return | integer | 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 |
|---|---|---|
comment_id | varchar(40) | not null |
(primary key) | The unique ID of this comment. | |
return_id | varchar(40) | not null |
(primary key) | 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(254) | 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 |
|---|---|---|
id | varchar(40) | not null |
(primary key) | 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 |
|---|---|---|
id | varchar(40) | not null |
(primary key) | The unique ID of the disposition. | |
description | varchar(254) | not null |
The description of the disposition. | ||
upd_inventory | tinyint | 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 |
|---|---|---|
exchange_id | varchar(40) | not null |
(primary key) | 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 |
|---|---|---|
id | varchar(40) | not null |
(primary key) | 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 | numeric(19,0) | not null |
The quantity of the item being returned. | ||
quantity_to_repl | numeric(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 | tinyint | not null |
Boolean indicating whether return shipment of the item is required before processing the refund or exchange. | ||
bonus_refund | tinyint | not null |
Boolean indicating whether any bonus refund is associated with this item. | ||
quantity_received | numeric(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 |
|---|---|---|
id | varchar(40) | not null |
(primary key) | The unique ID of the replacement item. | |
sku_id | varchar(40) | not null |
The SKU number of the replacement item. | ||
quantity | numeric(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 |
|---|---|---|
exchange_item_id | varchar(40) | not null |
(primary key) | The ID of the exchanged item. References csr_exch_item (id). | |
repl_item_id | varchar(40) | not null |
(primary key) | 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 |
|---|---|---|
exchange_id | varchar(40) | not null |
(primary key) | The ID of the return. References csr_exch (id). | |
exchange_item_id | varchar(40) | not null |
(primary key) | 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 |
|---|---|---|
id | varchar(40) | not null |
(primary key) | The unique ID of the refund. | |
type | integer | 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 |
|---|---|---|
exchange_id | varchar(40) | not null |
(primary key) | The ID of the return. References csr_exch (id). | |
exchange_method_id | varchar(40) | not null |
(primary key) | The ID of the refund. References csr_exch_method (id). | |
csr_cc_exch_method
This table stores information about credits applied to credit cards.
Column | Data Type | Constraint |
|---|---|---|
exchange_method_id | varchar(40) | not null |
(primary key) | 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 |
|---|---|---|
exchange_method_id | varchar(40) | not null |
(primary key) | The ID of the refund. References csr_exch_method (id). | |
fixed_amount | tinyint | null |
The amount of the store credit. | ||
bonus_credit | tinyint | null |
Boolean indicating whether a bonus was added to the store credit. | ||
payment_group_id | varchar(40) | null |
This field is not used. | ||
sc_id | varchar(40) | null |
The unique ID of the store credit. | ||
