Unified Order Schema (UOS) Code

This page provides information on the transform code block that is needed to populate the Unified Order Schema (UOS) and what should be added to a Script Manifest file in order to execute the code

Workflows

Upstream Data Mart Dependencies

There are no Data Marts that need to be updated prior to UOS

The proper sequence of either a Workflow or position in a Script Manifest file to populate this Data Mart is highlighted in the Transform Code Sequence - Data Mart page

Script Manifest File (YML)

Transformation Code Requirements

Transformation code should be added to the UOS block below depending on the integration. Instructions on what code to add for each integration is available in the following sections:

• Amazon Vendor Central

• BigCommerce

• Lightspeed

• Salesforce Commerce Cloud

• Shopify and Shopify Plus

• Skubana

The following code should also be added to the UOS block to make sure that all the Brand Supplied Data is included in UOS and additional updates to UOS are made

  #--------------------------------------------------------------------------------------------------------------------
  # SECTION: 0500_UOS
  # DEPENDENCIES: Commmerce integrations
  # OPTIONAL:
  #   - Integration: Shopify
  #   - Integration: Amazon
  #   - Integration: Skubana (Entensiv)
  #   - Integration: Lightspeed
  #   - Integration: Shipstation
  # REQUIRED: No
  # PURPOSE: These SQL scripts transform data from the source schema into the UOS schema.  The code should be enabled
  #   when the integration has been added
  #--------------------------------------------------------------------------------------------------------------------
  
  # INTEGRATION CODE GOES HERE
  
    uos_bsd:
    scripts:
      - "github://platform-sql-shared/scripts/base/0500_uos/bsd/0952_UOS_BAS_BSD_customers.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/bsd/0954_UOS_BAS_BSD_product_variants.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/bsd/0955_UOS_BAS_BSD_orders.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/bsd/0956_UOS_BAS_BSD_order_line_items.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/bsd/0963_UOS_BAS_BSD_fulfillments.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/bsd/0964_UOS_BAS_BSD_order_item_fulfillments.sql"
      - "github://platform-sql-internal/scripts/base/0500_uos/bsd/0966_UOS_BAS_BSD_sales_report.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/0980_UOS_BAS_update_sku_mapping.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/0985_UOS_BAS_update_sku_costs.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/0986_UOS_BAS_update_sku_costs_sales_report.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/0990_UOS_BAS_update_fulfillment_costs.sql"
      - "github://platform-sql-shared/scripts/base/0500_uos/0992_UOS_BAS_unique_customer_info.sql"

  uos_shipstation:
    integrations:
      - shipstation
    scripts:
      - "github://platform-sql-internal/scripts/templates/shipstation/0990_UOS_PRO_shipstation_update_fulfillment_costs.sql"

Unified Order Schema

Add this code to populate all of the UOS tables and include data from the Brand Supplied Data section of the Daasity App

UOS BSD

Add this code to incorporate the order data from the BSD into UOS

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/bsd/0952_UOS_BAS_BSD_customers.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/bsd/0954_UOS_BAS_BSD_product_variants.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/bsd/0955_UOS_BAS_BSD_orders.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/bsd/0956_UOS_BAS_BSD_order_line_items.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/bsd/0963_UOS_BAS_BSD_fulfillments.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/bsd/0964_UOS_BAS_BSD_order_item_fulfillments.sql

Update UOS tables based on other sheets in the BSD

• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/0980_UOS_BAS_update_sku_mapping.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/0985_UOS_BAS_update_sku_costs.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/0986_UOS_BAS_update_sku_costs_sales_report.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/0990_UOS_BAS_update_fulfillment_costs.sql
• https://github.com/Daasity/platform-sql-shared/blob/master/scripts/base/0500_uos/0992_UOS_BAS_unique_customer_info.sql