ccdw_fact_line_item
This table captures details of individual line items within orders. Each row represents a line item in a placed order.
Column Alias | Data Type | Key | Description | Index? |
---|---|---|---|---|
line_item_id | BIGINT | PK | Unique identifier for the line item. | Yes |
nline_item_id | VARCHAR(50) | The natural key representing the line item in the source system. | No | |
order_id | BIGINT | FK | A surrogate key representing the order associated with the line item. | Yes |
norder_id | VARCHAR(50) | The natural key representing the order in the source system. | Yes | |
document_no | VARCHAR(50) | Document number associated with the order. | No | |
utc_submit_timestamp | TIMESTAMP | UTC timestamp when the line item was submitted. | Yes | |
utc_submit_day_id | SMALLINT | FK | Foreign key to ccdw_dim_date for the UTC submission day. | Yes |
utc_submit_time_id | SMALLINT | FK | Foreign key to ccdw_dim_time for the UTC submission time. | No |
utc_update_timestamp | TIMESTAMP | Last modified UTC timestamp of this record. | Yes | |
std_submit_timestamp | TIMESTAMP | Timestamp when the line item was submitted, in standard time. | No | |
std_submit_day_id | SMALLINT | FK | Foreign key to ccdw_dim_date for the standard submission day. | Yes |
std_submit_time_id | SMALLINT | FK | Foreign key to ccdw_dim_time for the standard submission time. | No |
std_timezone_id | SMALLINT | FK | Foreign key to ccdw_dim_timezone for the standard time zone. | No |
sku_id | INTEGER | FK | Foreign key to ccdw_dim_product for the SKU. | Yes |
locale_id | SMALLINT | FK | Foreign key to ccdw_dim_locale . | Yes |
site_id | SMALLINT | FK | Foreign key to ccdw_dim_site for the submission site. | Yes |
business_channel_id | SMALLINT | FK | Foreign key to ccdw_dim_business_channel . | Yes |
user_agent_id | INTEGER | FK | Foreign key to ccdw_dim_user_agent . | Yes |
source_code_group_id | INTEGER | FK | Foreign key to ccdw_dim_source_code_group . | No |
visitor_id | VARCHAR(100) | Unique key representing an ID of the visitor. | No | |
customer_id | BIGINT | FK | Foreign key to ccdw_dim_customer . | Yes |
lcl_currency_id | SMALLINT | FK | Foreign key to ccdw_dim_currency for local currency. | No |
std_currency_id | SMALLINT | FK | Foreign key to ccdw_dim_currency for standard currency. | No |
shipping_geography_id | INTEGER | FK | Foreign key to ccdw_dim_geography for shipping location. | No |
invoice_geography_id | INTEGER | FK | Foreign key to ccdw_dim_geography for invoice location. | No |
customer_geography_id | INTEGER | FK | Foreign key to ccdw_dim_geography for customer's address. | No |
quantity | NUMERIC | The number of units of this line item that were purchased. | No | |
lcl_li_retail_price | NUMERIC | Line item retail price in local currency. | No | |
lcl_li_markdown | NUMERIC | Line item markdown amount in local currency. | No | |
lcl_li_markdown_price | NUMERIC | Line item markdown price in local currency. | No | |
lcl_li_unit_price | NUMERIC | The unit price, before promotional or manual discounts, associated with the line item. | No | |
lcl_li_markdown_disc | NUMERIC | Line item markdown discount in local currency. | No | |
lcl_li_promo_disc | NUMERIC | The line item promotional discounts associated with the line item. | No | |
lcl_li_promo_disc_order_alloc | NUMERIC | The order level promotional discounts associated with the line item (pro-rated). | No | |
lcl_li_promo_disc_ship_alloc | NUMERIC | The line item shipping level promotional discounts associated with the line item (pro-rated). | No | |
lcl_li_manual_disc | NUMERIC | The line item manual discounts associated with the line item. | No | |
lcl_li_manual_disc_order_alloc | NUMERIC | The order level manual discounts associated with the line item (pro-rated). | No | |
lcl_li_manual_disc_ship_alloc | NUMERIC | The shipping level manual discounts associated with the line item (pro-rated). | No | |
lcl_li_tax | NUMERIC | Line item tax in local currency. | No | |
lcl_li_tax_order_alloc | NUMERIC | The order level tax (pro-rated). | No | |
lcl_li_shipping | NUMERIC | The line item level shipping. | No | |
lcl_li_shipping_order_alloc | NUMERIC | The order level shipping (pro-rated). | No | |
lcl_li_gross_merchandise_value | NUMERIC | The Gross Merchandise Value (GMV) of the line item in the tenderd currency. GMV is derived using the following row-based calculation (quantity * lcl_li_unit_price) - ((lcl_li_promo_disc + lcl_li_manual_disc_order_alloc) + (lcl_li_manual_disc + lcl_li_manual_disc_order_alloc)) | No | |
lcl_li_net_merchandise_value | NUMERIC | Line item net merchandise value in tenderd currency. This is derived using the following row-based calculation lcl_li_gross_merchandise_value + (lcl_li_tax + lcl_li_tax_order_alloc) + (lcl_li_shipping + lcl_li_shipping_order_alloc) - (lcl_li_promo_disc_ship_alloc + lcl_li_manual_disc_ship_alloc) | No | |
std_li_retail_price | NUMERIC | Line item retail price in standard currency. | No | |
std_li_markdown | NUMERIC | Line item markdown amount in standard currency. | No | |
std_li_markdown_price | NUMERIC | Line item markdown price in standard currency. | No | |
std_li_unit_price | NUMERIC | The unit price, before promotional or manual discounts, associated with the line item. | No | |
std_li_markdown_disc | NUMERIC | Line item markdown discount in standard currency. | No | |
std_li_promo_disc | NUMERIC | The line item promotional discounts associated with the line item. | No | |
std_li_promo_disc_order_alloc | NUMERIC | The order level promotional discounts associated with the line item (pro-rated). | No | |
std_li_promo_disc_ship_alloc | NUMERIC | The line item shipping level promotional discounts associated with the line item (pro-rated). | No | |
std_li_manual_disc | NUMERIC | The line item manual discounts associated with the line item. | No | |
std_li_manual_disc_order_alloc | NUMERIC | The order level manual discounts associated with the line item (pro-rated). | No | |
std_li_manual_disc_ship_alloc | NUMERIC | The shipping level manual discounts associated with the line item (pro-rated). | No | |
std_li_tax | NUMERIC | The line item level tax. | No | |
std_li_tax_order_alloc | NUMERIC | The order level tax (pro-rated). | No | |
std_li_shipping | NUMERIC | The line item level shipping. | No | |
std_li_shipping_order_alloc | NUMERIC | The order level shipping (pro-rated). | No | |
std_li_gross_merchandise_value | NUMERIC | The Gross Merchandise Value (GMV) of the line item in the standard currency. GMV is derived using the following row-based calculation (quantity * std_li_unit_price) - ((std_li_promo_disc + std_li_manual_disc_order_alloc) + (std_li_manual_disc + std_li_manual_disc_order_alloc)) | No | |
std_li_net_merchandise_value | NUMERIC | Line item net merchandise value in standard currency. This is derived using the following row-based calculation std_li_gross_merchandise_value + (std_li_tax + std_li_tax_order_alloc) + (std_li_shipping + std_li_shipping_order_alloc) - (std_li_promo_disc_ship_alloc + std_li_manual_disc_ship_alloc) | No |