---
title: "View: Intermediary DRP Table(s): drp.fulfillment_cost"
description: drp.fulfillment_cost 4140_CST_BAS_fulfillment_cost.sql Pulls fulfillment id, fulfillment cost, shipping cost, number of order lines and total items at the fulfillment level. Fulfillments from all inte
---

[Back to home](https://support.daasity.com/migrated/knowledge)

- [ Welcome to Daasity
  
  
  
  ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity) 
    - [ Integration Setup | Connect Your Data Sources ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity#integration-setup-connect-your-data-sources)
    - [ Integration Setup | Amazon Seller Central ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity#integration-setup-amazon-seller-central)
    - [ Navigating the Daasity App ](https://support.daasity.com/migrated/knowledge/welcome-to-daasity#navigating-the-daasity-app)
- [ Daasity Platform | Business User Analytics
  
  
  
  ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics) 
    - [ Brand Supplied Data (BSD) ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#brand-supplied-data-bsd)
    - [ Dashboards ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#dashboards)
    - [ Reports ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#reports)
    - [ Explores ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#explores)
    - [ Views ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#views)
    - [ Audiences ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#audiences)
    - [ Customizations ](https://support.daasity.com/migrated/knowledge/daasity-platform-business-user-analytics#customizations)
- [ ELT+ | Technical User Documentation
  
  
  
  ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation) 
    - [ Platform ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation#platform)
    - [ Transformation ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation#transformation)
    - [ Integrations ](https://support.daasity.com/migrated/knowledge/elt-technical-user-documentation#integrations)
- [ User Manual and Support
  
  
  
  ](https://support.daasity.com/migrated/knowledge/user-manual-and-support) 
    - [ Daasity Logic and Best Practices ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#daasity-logic-and-best-practices)
    - [ Marketing Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#marketing-analytics)
    - [ Customer Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#customer-analytics)
    - [ Date Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#date-analytics)
    - [ Inventory and Returns Analytics ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#inventory-and-returns-analytics)
    - [ Submit a Support Request ](https://support.daasity.com/migrated/knowledge/user-manual-and-support#submit-a-support-request)
- [ Release Updates
  
  
  
  ](https://support.daasity.com/migrated/knowledge/release-updates) 
    - [ Release Updates ](https://support.daasity.com/migrated/knowledge/release-updates#release-updates)

# View: Intermediary DRP Table(s): drp.fulfillment_cost

### Source Database Table

drp.fulfillment_cost

### Script that Populates Database Table

4140_CST_BAS_fulfillment_cost.sql

### Script Description and Logic

Pulls fulfillment id, fulfillment cost, shipping cost, number of order lines and total items at the fulfillment level. Fulfillments from all integrations are included in the primary source table, uos.fulfillments, and reported by unique fulfillment id, shop id and integration id combination. From the uos.fulfillments and uos.order_item_fulfillments table, a lookup is made to return a list of fulfillments that have been updated in the last seven days. This list is then inserted into uos_fulfillments_to_update. Fulfillments and order_item_fulfillments are then joined back to uos_fulfillments_to_update, the number of fulfilled items is summed, the cost is determined at the item level. The unique combinations of fulfillment id, shop id, fulfillment cost, shipping cost and number of items are then placed in a staging table in preparation for incremental functionality. Incremental functionality is included such that only the records that require an update or are new get inserted into the final drp.fulfillment_cost table. The code looks back 7 days from last_load_dt to get that list of orders and then holds them in a staging table. The records in staging are compared to those in the final table. Any fulfillments that match are removed from the final table and updated by way of insertion from staging. The records that appear in staging, but not in the final table are inserted into drp.fulfillment_cost.

### Source Tables Used In Script

| **Schema** | **Table (or Derived Table) Name** | **Table Type** | **Purpose** |
| --- | --- | --- | --- |
| uos | fulfillments | database | Primary source |
| uos | order_item_fulfillments | database | Primary source |
| drp_staging | uos_fulfillments_to_update | database | Lookup |
| drp_staging | fulfillment_cost | database | Pre-insert |
| N/A | last_sync_date | derived | Obtain last sync date for each record |
| N/A | fulfillments_since_last_sync | derived | Determine which fulfillments or orders have been updated in the last seven days |

### SQL Flow

### Calculated and Derived Fields

| **Target Column** | **Target** **Column**** Data Type** | **Source schema.table** | **Source Column** | **Transformation/Logic** |
| --- | --- | --- | --- | --- |
| num_order_lines | INT | uos.order_item_fulfillments | order_line_id | COUNT(unique order line) per unique fulfillment id and shop_id |
| num_items | INT | uos.order_item_fulfillments | ordered_quantity, remaining_to_fulfill | SUM(ordered_quanity - reminaing_to_fulfill) per unique fulfillment id and shop_id |

 

All content © Daasity 2021. Do not copy, share or distribute.