DW y DW2
Creacion de tablas (Schema) para base de datos DW en sucursal
Script SQL completo para la creación de la estructura de tablas del Data Warehouse local.
Este documento contiene el script técnico necesario para generar la estructura de tablas en la base de datos XETUXDW. Este esquema es fundamental para el correcto funcionamiento del procedimiento de poblado y consolidación de reportes.
Se recomienda ejecutar este script después de haber creado la base de datos
XETUXDW con el Collation MODERN_SPANISH_CI_AS.CREATEDW.sql
USE [XETUXDW]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_CONSOLIDATE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[report_tag_id] [int] NULL,
[description_tag] [nvarchar](50) NULL,
[groups] [int] NULL,
[position] [int] NULL,
[value] [numeric](18, 4) NULL,
[quantity] [numeric](18, 4) NULL,
[has_conversion] [int] NULL,
[label] [nvarchar](50) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[order_type_id] [int] NULL,
[order_type_description] [nvarchar](50) NULL,
[commisionPctg] [numeric](18, 2) NULL,
[net_sale] [numeric](18, 4) NULL,
[transactions_qty] [int] NULL,
[service_total] [numeric](18, 4) NULL,
[diners] [int] NULL,
[has_conversion] [int] NULL,
[netSalesNoCommision] [numeric](18, 4) NULL,
[totalCommision] [numeric](18, 4) NULL,
[averageByTransaction] [numeric](18, 4) NULL,
[averageByDiner] [numeric](18, 4) NULL,
[salesPercentage] [numeric](18, 4) NULL,
[salePercentage] [numeric](18, 4) NULL,
[transactionPercentage] [numeric](18, 4) NULL,
[dinersPercentage] [numeric](18, 4) NULL,
[discount] [numeric](18, 4) NULL,
[annulations] [numeric](18, 4) NULL,
[credit_note] [numeric](18, 4) NULL,
[tax_value] [numeric](18, 4) NULL,
[total] [numeric](18, 4) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_PAYFORM] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_PAYFORM](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[pos] [int] NULL,
[payform_description] [nvarchar](100) NULL,
[quantity] [int] NULL,
[net_sale] [numeric](18, 4) NULL,
[change] [numeric](18, 4) NULL,
[tips] [numeric](18, 4) NULL,
[service_payform_value] [numeric](18, 4) NULL,
[net_amount] [numeric](18, 4) NULL,
[commission_percentage] [numeric](18, 4) NULL,
[NetSalesNoCommision] [numeric](18, 4) NULL,
[totalCommision] [numeric](18, 4) NULL,
[currency_symbol] [nvarchar](50) NULL,
[Total_secondary] [numeric](18, 4) NULL,
[has_conversion] [bit] NULL,
[tips_paid] [numeric](18, 4) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_USER] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_USER](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[user_id] [int] NULL,
[user_name] [nvarchar](200) NULL,
[net_sales] [numeric](18, 4) NULL,
[transactions_qty] [int] NULL,
[cancellations] [numeric](18, 4) NULL,
[tax_value] [numeric](18, 4) NULL,
[total_sales] [numeric](18, 4) NULL,
[diners] [int] NULL,
[total_tips] [numeric](18, 4) NULL,
[tips_paid] [numeric](18, 4) NULL,
[has_conversion] [int] NULL,
[averageByTransaction] [numeric](18, 4) NULL,
[averageByDiner] [numeric](18, 4) NULL,
[salePercentage] [numeric](18, 4) NULL,
[transactionPercentage] [numeric](18, 4) NULL,
[dinersPercentage] [numeric](18, 4) NULL,
[service_value] [numeric](18, 4) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_DISCOUNT_DETAILS] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_DISCOUNT_DETAILS](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[discount_description] [nvarchar](100) NULL,
[order_product_id] [int] NULL,
[discount_id] [int] NULL,
[customer_document] [nvarchar](50) NULL,
[customer_name] [nvarchar](100) NULL,
[message_discount] [nvarchar](150) NULL,
[campaign_code] [nvarchar](100) NULL,
[campaign_confirmation_key] [nvarchar](100) NULL,
[item_type_description] [nvarchar](50) NULL,
[item_description] [nvarchar](500) NULL,
[op_quantity_ordered] [decimal](12, 3) NULL,
[op_sale_price] [float] NULL,
[op_net_price] [float] NULL,
[opd_discount_value] [numeric](18, 4) NULL,
[o_order_id] [int] NULL,
[b_bill_number] [int] NULL,
[autorizo] [nvarchar](100) NULL,
[dateApply] [datetime] NULL,
[o_order_type_description] [nvarchar](100) NULL,
[environment_description] [nvarchar](100) NULL,
[mesa] [nvarchar](20) NULL,
[coupon_code] [nvarchar](50) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_DISCOUNT_RECHARGE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_DISCOUNT_RECHARGE](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[discount_description] [nvarchar](200) NULL,
[discount_value_percentage] [numeric](18, 2) NULL,
[discount_quantity] [numeric](18, 2) NULL,
[product_discount] [numeric](18, 2) NULL,
[discount_total_quantity] [numeric](18, 2) NULL,
[total_discount] [numeric](18, 2) NULL,
[discount_percentage] [numeric](18, 2) NULL,
[discount_sale_percentage] [numeric](18, 2) NULL,
[discount_category_description] [nvarchar](50) NULL,
[has_conversion] [int] NULL,
[total_by_last_cost] [numeric](18, 4) NOT NULL,
[total_by_average_cost] [numeric](18, 4) NOT NULL,
[item_code] [nvarchar](50) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[APP_REPORT_SALES_MENUITEM_REPORT] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[APP_REPORT_SALES_MENUITEM_REPORT](
[branch_id] [int] NOT NULL,
[journal_date] [date] NOT NULL,
[hour_sale] [int] NULL,
[order_type_description] [nvarchar](100) NULL,
[item_type_description] [nvarchar](100) NULL,
[family_description] [nvarchar](100) NULL,
[o_open_order_user_name] [nvarchar](100) NULL,
[item_description] [nvarchar](100) NULL,
[net_price] [numeric](18, 8) NULL,
[quantity] [numeric](18, 4) NULL,
[discount_value] [numeric](18, 4) NULL,
[total_cost] [numeric](18, 4) NULL,
[tax_value] [numeric](18, 8) NULL,
[total] [numeric](18, 8) NULL,
[item_code] [nvarchar](50) NOT NULL,
[group_description] [nvarchar](100) NULL,
[subgroup_description] [nvarchar](100) NULL,
[created_at] [datetime] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[PBI_MASTER_PAYFORMS_CONSOLIDATE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PBI_MASTER_PAYFORMS_CONSOLIDATE](
[journal_id] [int] NULL,
[fecha] [date] NULL,
[branchid] [int] NULL,
[company] [nvarchar](200) NULL,
[payform_description] [nvarchar](50) NULL,
[importeRecibidoMXN] [numeric](18, 4) NULL,
[importeRecibidoMoneda] [numeric](18, 4) NULL,
[consumo] [numeric](18, 4) NULL,
[cambio] [numeric](18, 4) NULL,
[factor] [numeric](18, 4) NULL,
[propinaMXN] [numeric](18, 4) NULL,
[propinaMoneda] [numeric](18, 4) NULL,
[gastos] [numeric](18, 4) NULL,
[TipsPaid] [numeric](18, 4) NULL,
[currency_id_default] [numeric](18, 4) NULL,
[is_cash] [bit] NULL,
[is_credit] [bit] NULL,
[is_debit] [bit] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_ANNULATION_BY_PRODUCT_BASE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_ANNULATION_BY_PRODUCT_BASE](
[branch_id] [int] NOT NULL,
[journal_date] [date] NULL,
[journal_id] [int] NOT NULL,
[order_id] [int] NOT NULL,
[suborder_id] [int] NOT NULL,
[order_product_id] [int] NOT NULL,
[annulation_datetime] [datetime] NULL,
[autorizo] [nvarchar](50) NULL,
[UsuarioOrden] [nvarchar](50) NULL,
[annulation_description] [nvarchar](50) NULL,
[message_annulation] [nvarchar](500) NULL,
[item_code] [nvarchar](50) NULL,
[item_name] [nvarchar](100) NULL,
[item_type_description] [nvarchar](100) NULL,
[quantity] [decimal](12, 3) NULL,
[net_price] [float] NULL,
[lastCost] [numeric](18, 4) NULL,
[avgCost] [numeric](18, 4) NULL,
[bill_id] [int] NULL,
[tipo] [nvarchar](50) NULL,
[TicketValueCash] [float] NULL,
CONSTRAINT [PK_REPORT_ANNULATION_BY_PRODUCT_BASE] PRIMARY KEY CLUSTERED
(
[branch_id] ASC,
[journal_id] ASC,
[order_id] ASC,
[suborder_id] ASC,
[order_product_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_BY_PRODUCT_BASE_V2] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_BY_PRODUCT_BASE_V2](
[branch_id] [int] NOT NULL,
[op_order_product_id] [int] NOT NULL,
[op_order_id] [int] NOT NULL,
[op_suborder_id] [int] NOT NULL,
[op_product_id] [int] NULL,
[op_quantity_ordered] [decimal](12, 3) NULL,
[op_sale_price] [float] NULL,
[op_subtotal] [float] NULL,
[op_order_product_status_id] [tinyint] NULL,
[op_net_price] [float] NULL,
[op_tax_id] [int] NULL,
[op_tax_value] [float] NULL,
[op_send_at] [datetime2](3) NULL,
[op_item_id] [int] NULL,
[op_lastCost] [decimal](18, 4) NULL,
[op_avgCost] [decimal](18, 4) NULL,
[op_send_user_id] [int] NULL,
[op_station_id] [int] NULL,
[op_total] [float] NULL,
[op_is_additional] [bit] NULL,
[order_product_parent_id] [int] NULL,
[item_id_additional] [int] NULL,
[family_name_additional] [nvarchar](200) NULL,
[item_type_description_additional] [nvarchar](200) NULL,
[op_order_additional_for_sort] [nvarchar](50) NULL,
[is_promotion_parent] [bit] NULL,
[order_product_id_promotion_parent] [int] NULL,
[is_product_send_promotion] [bit] NULL,
[item_name_promotion] [nvarchar](200) NULL,
[item_id_promotion] [int] NULL,
[product_id_promotion] [int] NULL,
[op_order_promotion_for_sort] [nvarchar](50) NULL,
[opd_discount_value] [decimal](38, 4) NULL,
[opd_recharge_value] [decimal](38, 4) NULL,
[so_suborder_status_id] [tinyint] NULL,
[so_total] [decimal](18, 4) NULL,
[so_tax_total_value] [decimal](18, 4) NULL,
[so_discount_total_value] [decimal](18, 4) NULL,
[so_service_value] [decimal](18, 4) NULL,
[o_order_type_id] [int] NULL,
[o_order_type_description] [nvarchar](50) NULL,
[o_open_order_user_id] [int] NULL,
[o_open_order_user_name] [nvarchar](101) NOT NULL,
[o_journal_start_date] [datetime2](3) NULL,
[o_environment_id] [int] NULL,
[environment_description] [nvarchar](50) NULL,
[o_journal_id] [int] NULL,
[b_bill_number] [int] NULL,
[b_bill_datetime] [datetime2](3) NULL,
[b_station_bill_id] [int] NOT NULL,
[b_station_bill_description] [nvarchar](50) NOT NULL,
[b_customer_id] [int] NULL,
[item_description] [nvarchar](500) NULL,
[item_type_id] [int] NULL,
[item_type_description] [nvarchar](50) NULL,
[item_subgroup_id] [int] NOT NULL,
[item_group_description] [nvarchar](50) NOT NULL,
[item_group_parent_id] [int] NOT NULL,
[item_group_parent_description] [nvarchar](50) NOT NULL,
[family_id] [int] NOT NULL,
[family_name] [nvarchar](50) NOT NULL,
[family_description] [nvarchar](500) NULL,
[year_] [int] NULL,
[month_] [int] NULL,
[day_] [int] NULL,
[hour_] [int] NULL,
[discount100] [int] NULL,
[additional] [int] NOT NULL,
[md_parent_family] [nvarchar](100) NULL,
[bar_code] [nvarchar](50) NULL,
[item_code] [nvarchar](50) NULL,
[has_conversion] [bit] NULL,
[opening_datetime] [datetime] NULL,
[customer_identification] [nvarchar](50) NULL,
[customer_full_identification] [nvarchar](50) NULL,
[customer_name] [nvarchar](500) NULL,
[customer_email] [nvarchar](50) NULL,
[customer_phone] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_CONSOLIDATE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_CONSOLIDATE](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[report_tag_id] [int] NULL,
[description_tag] [nvarchar](50) NULL,
[groups] [int] NULL,
[position] [int] NULL,
[value] [numeric](18, 4) NULL,
[quantity] [numeric](18, 4) NULL,
[has_conversion] [int] NULL,
[label] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[order_type_id] [int] NULL,
[order_type_description] [nvarchar](50) NULL,
[commisionPctg] [numeric](18, 2) NULL,
[net_sale] [numeric](18, 4) NULL,
[transactions_qty] [int] NULL,
[service_total] [numeric](18, 4) NULL,
[diners] [int] NULL,
[has_conversion] [int] NULL,
[netSalesNoCommision] [numeric](18, 4) NULL,
[totalCommision] [numeric](18, 4) NULL,
[averageByTransaction] [numeric](18, 4) NULL,
[averageByDiner] [numeric](18, 4) NULL,
[salesPercentage] [numeric](18, 4) NULL,
[salePercentage] [numeric](18, 4) NULL,
[transactionPercentage] [numeric](18, 4) NULL,
[dinersPercentage] [numeric](18, 4) NULL,
[discount] [numeric](18, 4) NULL,
[annulations] [numeric](18, 4) NULL,
[credit_note] [numeric](18, 4) NULL,
[tax_value] [numeric](18, 4) NULL,
[total] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_CONSOLIDATE_BY_PAYFORM] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_CONSOLIDATE_BY_PAYFORM](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[pos] [int] NULL,
[payform_description] [nvarchar](100) NULL,
[quantity] [int] NULL,
[net_sale] [numeric](18, 4) NULL,
[change] [numeric](18, 4) NULL,
[tips] [numeric](18, 4) NULL,
[service_payform_value] [numeric](18, 4) NULL,
[net_amount] [numeric](18, 4) NULL,
[commission_percentage] [numeric](18, 4) NULL,
[NetSalesNoCommision] [numeric](18, 4) NULL,
[totalCommision] [numeric](18, 4) NULL,
[currency_symbol] [nvarchar](50) NULL,
[Total_secondary] [numeric](18, 4) NULL,
[has_conversion] [bit] NULL,
[tips_paid] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_CONSOLIDATE_BY_USER] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_CONSOLIDATE_BY_USER](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[user_id] [int] NULL,
[user_name] [nvarchar](50) NULL,
[net_sales] [numeric](18, 4) NULL,
[transactions_qty] [int] NULL,
[cancellations] [numeric](18, 4) NULL,
[tax_value] [numeric](18, 4) NULL,
[total_sales] [numeric](18, 4) NULL,
[diners] [int] NULL,
[total_tips] [numeric](18, 4) NULL,
[tips_paid] [numeric](18, 4) NULL,
[has_conversion] [int] NULL,
[averageByTransaction] [numeric](18, 4) NULL,
[averageByDiner] [numeric](18, 4) NULL,
[salePercentage] [numeric](18, 4) NULL,
[transactionPercentage] [numeric](18, 4) NULL,
[dinersPercentage] [numeric](18, 4) NULL,
[service_value] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_DISCOUNT_DETAILS] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_DISCOUNT_DETAILS](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[discount_description] [nvarchar](100) NULL,
[order_product_id] [int] NULL,
[discount_id] [int] NULL,
[customer_document] [nvarchar](50) NULL,
[customer_name] [nvarchar](100) NULL,
[message_discount] [nvarchar](150) NULL,
[campaign_code] [nvarchar](100) NULL,
[campaign_confirmation_key] [nvarchar](100) NULL,
[item_type_description] [nvarchar](50) NULL,
[item_description] [nvarchar](500) NULL,
[op_quantity_ordered] [decimal](12, 3) NULL,
[op_sale_price] [float] NULL,
[op_net_price] [float] NULL,
[opd_discount_value] [numeric](18, 4) NULL,
[o_order_id] [int] NULL,
[b_bill_number] [int] NULL,
[autorizo] [nvarchar](100) NULL,
[dateApply] [datetime] NULL,
[o_order_type_description] [nvarchar](100) NULL,
[environment_description] [nvarchar](100) NULL,
[mesa] [nvarchar](20) NULL,
[coupon_code] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_DISCOUNT_RECHARGE] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_DISCOUNT_RECHARGE](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[discount_description] [nvarchar](200) NULL,
[discount_value_percentage] [numeric](18, 2) NULL,
[discount_quantity] [numeric](18, 2) NULL,
[product_discount] [numeric](18, 2) NULL,
[discount_total_quantity] [numeric](18, 2) NULL,
[total_discount] [numeric](18, 2) NULL,
[discount_percentage] [numeric](18, 2) NULL,
[discount_sale_percentage] [numeric](18, 2) NULL,
[discount_category_description] [nvarchar](50) NULL,
[has_conversion] [int] NULL,
[total_by_last_cost] [numeric](18, 4) NULL,
[total_by_average_cost] [numeric](18, 4) NULL,
[item_code] [nvarchar](50) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_HEADER_DETAILS] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_HEADER_DETAILS](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[hour_order] [int] NULL,
[o_order_type_description] [nvarchar](100) NULL,
[o_open_order_user_name] [nvarchar](100) NULL,
[environment_name] [nvarchar](100) NULL,
[discount100] [bit] NULL,
[o_number_of_diners] [numeric](18, 2) NULL,
[bills] [int] NULL,
[netPrice] [numeric](18, 8) NULL,
[discounts] [numeric](18, 4) NULL,
[taxes] [numeric](18, 8) NULL,
[total] [numeric](18, 8) NULL,
[tips] [numeric](18, 4) NULL,
[numProductos] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_MENUITEM_REPORT] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_MENUITEM_REPORT](
[branch_id] [int] NOT NULL,
[journal_date] [date] NOT NULL,
[hour_sale] [int] NULL,
[order_type_description] [nvarchar](100) NULL,
[item_type_description] [nvarchar](100) NULL,
[family_description] [nvarchar](100) NULL,
[o_open_order_user_name] [nvarchar](100) NULL,
[item_description] [nvarchar](100) NULL,
[net_price] [numeric](18, 8) NULL,
[quantity] [numeric](18, 4) NULL,
[discount_value] [numeric](18, 4) NULL,
[total_cost] [numeric](18, 4) NULL,
[tax_value] [numeric](18, 8) NULL,
[total] [numeric](18, 8) NULL,
[item_code] [nvarchar](50) NULL,
[group_description] [nvarchar](100) NULL,
[subgroup_description] [nvarchar](100) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_SALES_PROFITABILITY_REPORT] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_SALES_PROFITABILITY_REPORT](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[item_group_id] [int] NULL,
[item_group_description] [nvarchar](100) NULL,
[item_id] [int] NULL,
[item_name] [nvarchar](200) NULL,
[product_id] [int] NULL,
[average_cost] [numeric](18, 4) NULL,
[weighted_cost] [numeric](18, 4) NULL,
[last_cost] [numeric](18, 4) NULL,
[sale_price_1] [numeric](18, 4) NULL,
[TotalVenta] [numeric](18, 4) NULL,
[Margen] [numeric](18, 4) NULL,
[quantity_total] [numeric](18, 4) NULL,
[total_average_cost] [numeric](18, 4) NULL,
[total_weighted_cost] [numeric](18, 4) NULL,
[total_last_cost] [numeric](18, 4) NULL,
[total_weighted_cost_2] [numeric](18, 4) NULL,
[add_item_cost] [numeric](18, 4) NULL,
[add_net_price] [numeric](18, 4) NULL,
[company_rif] [nvarchar](50) NULL,
[company_name] [nvarchar](200) NULL,
[PctjeCostoBase] [numeric](18, 4) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[REPORT_UNIT_SOLD_ITEM] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[REPORT_UNIT_SOLD_ITEM](
[branch_id] [int] NULL,
[journal_date] [date] NULL,
[item_code] [nvarchar](50) NULL,
[item_name] [nvarchar](200) NULL,
[item_unit_measure] [nvarchar](50) NULL,
[quantity] [numeric](18, 4) NULL,
[total_cost] [numeric](18, 4) NULL,
[unit_cost] [numeric](18, 4) NULL,
[avg_cost] [numeric](18, 4) NULL,
[datetime1] [datetime] NULL,
[order_id] [int] NULL,
[suborder] [int] NULL,
[bill_number] [int] NULL,
[group_field1] [nvarchar](200) NULL,
[group_field2] [nvarchar](200) NULL,
[group_field3] [nvarchar](200) NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[RPTVARIATIONSREPORTHIST] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[RPTVARIATIONSREPORTHIST](
[branch_id] [int] NULL,
[item_server_id] [int] NULL,
[item_code] [nvarchar](50) NULL,
[min_item] [numeric](18, 2) NULL,
[max_item] [numeric](18, 2) NULL,
[item_id] [int] NULL,
[item_type_id] [int] NULL,
[item_type_description] [nvarchar](50) NULL,
[unit_of_measure_standard_name_abbreviation] [nvarchar](50) NULL,
[item_catery_id] [int] NULL,
[item_catery_description] [nvarchar](50) NULL,
[item_name] [nvarchar](200) NULL,
[inv_inicial] [numeric](18, 4) NULL,
[compras] [numeric](18, 4) NULL,
[production] [numeric](18, 4) NULL,
[Procesamiento] [numeric](18, 4) NULL,
[Trans] [numeric](18, 4) NULL,
[Trans_in] [numeric](18, 4) NULL,
[Trans_out] [numeric](18, 4) NULL,
[Trans_InCost] [numeric](18, 4) NULL,
[Trans_outCost] [numeric](18, 4) NULL,
[inv_final] [numeric](18, 4) NULL,
[waste_total] [numeric](18, 4) NULL,
[devolucion] [numeric](18, 4) NULL,
[ComidaEmpleado] [numeric](18, 4) NULL,
[Ajustes_realizados] [numeric](18, 4) NULL,
[usoCalculado] [numeric](18, 4) NULL,
[usoReal] [numeric](18, 4) NULL,
[variacion] [numeric](18, 4) NULL,
[C_promedio] [numeric](18, 4) NULL,
[C_ultimo] [numeric](18, 4) NULL,
[C_ponderado] [numeric](18, 4) NULL,
[inv_inicial_c_ponderado] [numeric](18, 4) NULL,
[inv_inicial_c_promedio] [numeric](18, 4) NULL,
[inv_inicial_c_ultimo] [numeric](18, 4) NULL,
[compras_c_ponderado] [numeric](18, 4) NULL,
[compras_c_promedio] [numeric](18, 4) NULL,
[compras_c_ultimo] [numeric](18, 4) NULL,
[inv_final_c_ponderado] [numeric](18, 4) NULL,
[inv_final_c_promedio] [numeric](18, 4) NULL,
[inv_final_c_ultimo] [numeric](18, 4) NULL,
[diferencia_ponderado] [numeric](18, 4) NULL,
[diferencia_promedio] [numeric](18, 4) NULL,
[diferencia_ultimo] [numeric](18, 4) NULL,
[net] [numeric](18, 4) NULL,
[porcentaje_ponderado] [numeric](18, 4) NULL,
[porcentaje_promedio] [numeric](18, 4) NULL,
[porcentaje_ultimo] [numeric](18, 4) NULL,
[fecha_inicial] [datetime] NULL,
[fecha_final] [datetime] NULL,
[CantReceta_ini] [numeric](18, 4) NULL,
[CantReceta_fin] [numeric](18, 4) NULL,
[C_ini_pond] [numeric](18, 4) NULL,
[C_ini_prom] [numeric](18, 4) NULL,
[C_ini_ult] [numeric](18, 4) NULL,
[company_rif] [nvarchar](50) NULL,
[company_name] [nvarchar](50) NULL,
[desde] [datetime] NULL,
[hasta] [datetime] NULL,
[uso_promedio] [numeric](18, 4) NULL,
[uso_ultimo] [numeric](18, 4) NULL,
[whr_id] [int] NULL,
[whr_description] [nvarchar](50) NULL,
[modo] [int] NULL,
[is_sale_warehouse] [int] NULL,
[warehouse_type_unit_id] [int] NULL,
[grupoHijo_id] [int] NULL,
[grupoHijo_description] [nvarchar](50) NULL,
[grupoPadre_id] [int] NULL,
[grupoPadre_description] [nvarchar](50) NULL,
[unit_measure_id] [int] NULL,
[unit_measure_description] [nvarchar](50) NULL,
[unit_measure_quantity] [numeric](18, 4) NULL,
[inventory_log_id_inicial] [int] NULL,
[inventory_log_id_final] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_BRANCH] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_BRANCH](
[id] [int] NOT NULL,
[idbrand] [int] NULL,
[branchName] [nvarchar](50) NULL,
[latitud] [float] NULL,
[longitud] [float] NULL,
CONSTRAINT [PK_T_BRANCH] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_BRAND] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_BRAND](
[id] [int] NOT NULL,
[brandName] [nvarchar](50) NULL,
[idCompany] [int] NULL,
CONSTRAINT [PK_T_BRAND] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_COMPANY] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_COMPANY](
[id] [int] IDENTITY(1,1) NOT NULL,
[company] [nvarchar](50) NULL,
CONSTRAINT [PK_T_COMPANY] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_INVENTORY] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_INVENTORY](
[id] [int] IDENTITY(1,1) NOT NULL,
[branchid] [int] NULL,
[inventoryId] [int] NULL,
CONSTRAINT [PK_T_INVENTORY] PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_MOVEMENTS] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_MOVEMENTS](
[branchid] [int] NOT NULL,
[journal_date] [date] NOT NULL,
[item_server_id] [int] NULL,
[item_code] [nvarchar](50) NOT NULL,
[movement_quantity] [numeric](18, 4) NULL,
[total_cost] [numeric](22, 4) NULL,
[movement_type_id] [int] NOT NULL,
[item_name] [nvarchar](200) NULL,
[warehouse_description] [nvarchar](50) NULL,
[movement_id] [int] NULL
) ON [PRIMARY]
GO
/****** Object: Table [dbo].[T_PURCHASES] Script Date: 14/10/2024 10:10:44 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[T_PURCHASES](
[branch_id] [int] NULL,
[sucursal] [nvarchar](50) NULL,
[numberDocument] [nvarchar](50) NULL,
[supplier] [nvarchar](500) NULL,
[dateDocument] [date] NULL,
[dateReception] [datetime] NULL,
[item_code] [nvarchar](50) NULL,
[item_server_id] [int] NULL,
[item] [nvarchar](200) NULL,
[unit_measure_hist] [nvarchar](50) NULL,
[CantStandard] [numeric](18, 4) NULL,
[costUnit] [numeric](18, 6) NULL,
[warehouse] [nvarchar](50) NULL,
[unit] [nvarchar](50) NULL,
[quantity] [numeric](18, 4) NULL,
[unit_cost] [numeric](18, 6) NULL,
[tax_pctg] [numeric](18, 6) NULL,
[discount_pctg] [numeric](18, 6) NULL,
[net_cost] [numeric](18, 6) NULL,
[discount] [numeric](18, 6) NULL,
[subtotal] [numeric](18, 6) NULL,
[tax] [numeric](18, 6) NULL,
[total] [numeric](18, 6) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE] ADD CONSTRAINT [DF_APP_REPORT_SALES_CONSOLIDATE_created_at] DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE] ADD CONSTRAINT [DF_APP_REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE_created_at] DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_PAYFORM] ADD CONSTRAINT [DF_APP_REPORT_SALES_CONSOLIDATE_BY_PAYFORM_created_at] DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_CONSOLIDATE_BY_USER] ADD CONSTRAINT [DF_APP_REPORT_SALES_CONSOLIDATE_BY_USER_created_at] DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_DISCOUNT_DETAILS] ADD CONSTRAINT [DF_APP_REPORT_SALES_DISCOUNT_DETAILS_created_at] DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_DISCOUNT_RECHARGE] ADD CONSTRAINT [DF_APP_REPORT_SALES_DISCOUNT_RECHARGE_created_at] DEFAULT (getdate()) FOR [created_at]
GO
ALTER TABLE [dbo].[APP_REPORT_SALES_MENUITEM_REPORT] ADD CONSTRAINT [DF_APP_REPORT_SALES_MENUITEM_REPORT_created_at] DEFAULT (getdate()) FOR [created_at]
GO
Gestión DW Sucursal
Guía técnica para la inicialización, depuración y sincronización del Data Warehouse local (XETUXDW) con el servidor central de reportes.
Procedimiento de Poblado DWH_POPULATE_REPORTS
Script SQL del procedimiento almacenado encargado de la consolidación y transferencia de datos transaccionales hacia el Data Warehouse local.