How Can I Find the Fees Associated With an Amazon Order?

Question

How can I find the fees associated with an Amazon order using the AMAZON_ORDER_ID?

Environment

Connector: Amazon Selling Partner

Answer

To find the fees associated with an Amazon order using the AMAZON_ORDER_ID, do the following:

1. Retrieve the ORDER_ITEM_ID from the ORDER_ITEM table:

   SELECT *  
   FROM <schema>.ORDER_ITEM  
   WHERE AMAZON_ORDER_ID = '<amazon_order_id>';
   

2. Find shipment event items in the FINANCIAL_SHIPMENT_EVENT_ITEM table using the ORDER_ITEM_ID from step 1.
   • Match using ORDER_ITEM_ID:

     SELECT *  
     FROM <schema>.FINANCIAL_SHIPMENT_EVENT_ITEM  
     WHERE ORDER_ITEM_ID = '<order_item_id>';
     

   • Or match using ORDER_ADJUSTMENT_ITEM_ID:

     SELECT *  
     FROM <schema>.FINANCIAL_SHIPMENT_EVENT_ITEM  
     WHERE ORDER_ADJUSTMENT_ITEM_ID = '<order_adujstment_item_id>';
     

   Ensure the ORDER_ITEM_ID and ORDER_ADJUSTMENT_ITEM_ID columns are included in your schema.

3. Retrieve the fee details by joining the _FIVETRAN_ID values from step 2 to the LINKED_TO_ID column in the FINANCIAL_FEE_COMPONENT table:

   SELECT FEE_TYPE, FEE_KIND, "INDEX", LINKED_TO, LINKED_TO_ID,  
          CURRENCY_CODE, CURRENCY_AMOUNT, _FIVETRAN_SYNCED  
   FROM <schema>.FINANCIAL_FEE_COMPONENT  
   WHERE LINKED_TO_ID IN ('LINKED_TO_ID_001', 'LINKED_TO_ID_002');
   

The ItemFee and ItemFeeAdjustment records are stored in the FINANCIAL_FEE_COMPONENT table.