DW y DW2
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.
Este documento contiene la lógica transaccional del procedimiento almacenado DWH_POPULATE_REPORTS. Su función principal es extraer la información de ventas, pagos, descuentos y costos desde XETUXPOS para poblar las tablas de reportes en XETUXDW.
Este procedimiento utiliza una transacción interna (
BEGIN TRAN). Si ocurre un error durante el proceso de una jornada, se realizará un ROLLBACK automático para mantener la integridad de los datos.El procedimiento está diseñado para procesar hasta 30 jornadas (
@MaxStopValue = 30) por ejecución para evitar bloqueos prolongados en el motor de base de datos.DWH_POPULATE_REPORTS_NewVersion_Ago2025.sql
USE [XETUXPOS]
GO
/****** Object: StoredProcedure [dbo].[DWH_POPULATE_REPORTS] Script Date: 03/02/2026 08:33:03 p. m. ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[DWH_POPULATE_REPORTS]
AS
BEGIN
BEGIN TRY
BEGIN TRAN;
SET nocount ON;
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
DECLARE @tempInitialdate DATE;
DECLARE @endDate date;
DECLARE @MaxStopValue int = 30;
DECLARE @counter int = 0;
DECLARE @currency_id int = 1;
DECLARE @consolidateBi bit = 0;
declare @consolidate_from_year int;
--====================================================================================================================================================================================
-- CREACION DE CAMPOS EN LA BASE DE DATOS XETUXDW
IF NOT EXISTS(SELECT * FROM XETUXDW.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'total_by_last_cost' AND TABLE_NAME = 'REPORT_SALES_DISCOUNT_RECHARGE')
ALTER TABLE XETUXDW.dbo.REPORT_SALES_DISCOUNT_RECHARGE ADD total_by_last_cost NUMERIC(18,4) CONSTRAINT DF_REPORT_SALES_DISCOUNT_RECHARGE_total_by_last_cost DEFAULT 0 NOT NULL
IF NOT EXISTS(SELECT * FROM XETUXDW.INFORMATION_SCHEMA.COLUMNS WHERE COLUMN_NAME = 'total_by_average_cost' AND TABLE_NAME = 'REPORT_SALES_DISCOUNT_RECHARGE')
ALTER TABLE XETUXDW.dbo.REPORT_SALES_DISCOUNT_RECHARGE ADD total_by_average_cost NUMERIC(18,4) CONSTRAINT DF_REPORT_SALES_DISCOUNT_RECHARGE_total_by_average_cost DEFAULT 0 NOT NULL;
--====================================================================================================================================================================================
select top 1 @currency_id = currency_id from T_POS_CURRENCY where is_default = 1
select @consolidateBi = parameter_is_require, @consolidate_from_year= iif(parameter_value = 0,2015,parameter_value) from T_SYS_PARAMETERS where parameter_id = 361
select @tempInitialdate= min(cast(journal_start_datetime as date)) from T_POS_JOURNAL where is_active = 0 and is_reported = 0
AND journal_id in (select journal_id from t_pos_order)
and year(journal_start_datetime) >= @consolidate_from_year
print @tempInitialdate
select @endDate= max(cast(journal_start_datetime as date)) from T_POS_JOURNAL where is_active = 0 and is_reported = 0
DECLARE @report_sales_consolidate TABLE
(
branch_id int,
journal_date date,
report_tag_id int,
description_tag nvarchar(50),
groups int,
position int,
value_amount numeric(18,4),
quantity numeric(18,4),
has_conversion int,
label_text nvarchar(50)
);
DECLARE @REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE TABLE
(
branch_id int,
journal_date date,
order_type_id int,
order_type_description nvarchar(50),
comisionPctg numeric(18,2),
net_sale numeric(18,4),
tax_value numeric(18,4),
total numeric(18,4),
transactions_qty int,
service_total numeric(18,4),
diners int,
has_conversion int,
discount numeric(18,4),
annulations numeric(18,4),
credit_note numeric(18,4),
netSalesNoCommision numeric(18,4),
totalCommision numeric(18,4),
averageByTransaction numeric(18,4),
averageByDiner numeric(18,4),
salesPercentage numeric(18,4),
salePercentage numeric(18,4),
transactionPercentage numeric(18,4),
dinersPercentage numeric(18,4)
);
DECLARE @REPORT_SALES_CONSOLIDATE_BY_USER TABLE
(
branch_id int,
journal_date date,
user_id int,
user_name nvarchar(200),
net_sales numeric(18,4),
transactions_qty int,
cancellations numeric(18,4),
tax_value numeric(18,4),
total_sales numeric(18,4),
diners int,
total_tips numeric(18,4),
tips_paid numeric(18,4),
has_conversion int,
service_value numeric(18,4),
averageByTransaction numeric(18,4),
averageByDiner numeric(18,4),
salePercentage numeric(18,4),
transactionPercentage numeric(18,4),
dinersPercentage numeric(18,4)
);
DECLARE @REPORT_SALES_DISCOUNT_RECHARGE TABLE
(
branch_id int,
journal_date date,
discount_description nvarchar(200),
discount_value_percentage numeric(18,2),
discount_quantity numeric(18,2),
product_discount numeric(18,2),
discount_total_quantity numeric(18,2),
total_discount numeric(18,2),
discount_percentage numeric(18,2),
discount_sale_percentage numeric(18,2),
discount_category_description nvarchar(50),
total_by_last_cost numeric(18,4),
total_by_average_cost numeric(18,4),
has_conversion int
);
DECLARE @REPORT_SALES_PROFITABILITY_REPORT TABLE
(
branch_id int,
journal_date date,
item_group_id int,
item_group_description nvarchar(100),
item_id int,
item_name nvarchar(200),
product_id int,
average_cost numeric(18,4),
weighted_cost numeric(18,4),
last_cost numeric(18,4),
sale_price_1 numeric(18,4),
TotalVenta numeric(18,4),
Margen numeric(18,4),
quantity_total numeric(18,4),
total_average_cost numeric(18,4),
total_weighted_cost numeric(18,4),
total_last_cost numeric(18,4),
total_weighted_cost_2 numeric(18,4),
add_item_cost numeric(18,4),
add_net_price numeric(18,4),
company_rif nvarchar(50),
company_name nvarchar(100),
pctjeCostoBase numeric(18,4)
);
DECLARE @REPORT_SALES_CONSOLIDATE_BY_PAYFORM TABLE
(
pos int,
payform_description nvarchar(100),
quantity int,
net_sale numeric(18,4),
change numeric(18,4),
tips numeric(18,4),
service_payform_value numeric(18,4),
net_amount numeric(18,4),
commission_percentage numeric(18,4),
NetSalesNoCommision numeric(18,4),
totalCommision numeric(18,4),
currency_symbol nvarchar(50),
Total_secondary numeric(18,4),
has_conversion numeric(18,4),
tips_paid numeric(18,4)
);
DECLARE @REPORT_SALES_MENUITEM_REPORT TABLE
(
branch_id int,
journal_date date,
hour_sale int,
order_type_description nvarchar(100),
item_type_description nvarchar(100),
family_description nvarchar(100),
o_open_order_user_name nvarchar(100),
item_description nvarchar(100),
net_price numeric(18,8),
quantity numeric(18,4),
discount_value numeric(18,4),
total_cost numeric(18,4),
tax_value numeric(18,8),
total numeric(18,8),
item_code nvarchar(50),
group_description nvarchar(100),
subgroup_description nvarchar(100)
);
DECLARE @REPORT_UNIT_SOLD_ITEM TABLE
(
item_code nvarchar(50),
item_name nvarchar(200),
item_unit_measure nvarchar(50),
quantity numeric(18,4),
total_cost numeric(18,4),
total_avg_cost numeric(18,4),
unit_cost numeric(18,4),
avg_cost numeric(18,8),
datetime1 datetime,
order_id int,
suborder int,
bill_number int,
group_field1 nvarchar(200),
group_field2 nvarchar(200),
group_field3 nvarchar(200)
);
DECLARE @REPORT_SALES_DISCOUNT_DETAILS TABLE
(
discount_description nvarchar(100),
order_product_id int,
discount_id int,
customer_document nvarchar(50),
customer_name nvarchar(100),
message_discount nvarchar(150),
campaign_code nvarchar(100),
campaign_confirmation_key nvarchar(100),
item_type_description nvarchar(50),
item_description nvarchar(500),
op_quantity_ordered decimal(12,3),
op_sale_price float,
op_net_price float,
opd_discount_value numeric(18,4),
o_order_id int,
b_bill_number int,
autorizo nvarchar(100),
dateApply datetime,
o_order_type_description nvarchar(100),
environment_description nvarchar(100),
mesa nvarchar(20),
coupon_code nvarchar(50)
);
while (@tempInitialdate <= @endDate) and (@counter < @MaxStopValue)
begin
DELETE FROM @report_sales_consolidate;
DELETE FROM @REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE;
DELETE FROM @REPORT_SALES_CONSOLIDATE_BY_USER;
DELETE FROM @REPORT_SALES_DISCOUNT_RECHARGE;
DELETE FROM @REPORT_SALES_PROFITABILITY_REPORT;
DELETE FROM @REPORT_SALES_CONSOLIDATE_BY_PAYFORM;
DELETE FROM @REPORT_SALES_MENUITEM_REPORT;
DELETE FROM @REPORT_UNIT_SOLD_ITEM
DELETE FROM @REPORT_SALES_DISCOUNT_DETAILS
-- Insertamos en temporal la informacion del USP PA de consolidado
INSERT INTO @report_sales_consolidate
(
[report_tag_id]
,[description_tag]
,[groups]
,[position]
,[value_amount]
,[quantity]
,[has_conversion]
,[label_text])
exec USP_PA_REPORT_SALES_CONSOLIDATE @tempInitialdate,@tempInitialdate,null,null,@currency_id,null,1
-- Insertamos en tabla final el resultado de la tabla temporal
INSERT INTO XETUXDW.dbo.report_sales_consolidate
([branch_id]
,[journal_date]
,[report_tag_id]
,[description_tag]
,[groups]
,[position]
,[value]
,[quantity]
,[has_conversion]
,[label])
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate, [report_tag_id]
,[description_tag]
,[groups]
,[position]
,[value_amount]
,[quantity]
,[has_conversion]
,[label_text] from @report_sales_consolidate where report_tag_id in (22,
4,
17,
1,
3,
20,
2,
5,
7,
21,
6,
10,
28,
8) )
-------------------------------------------------------------------------------
-- Insertamos en temporal la informacion del USP PA de consolidado order type
begin try
INSERT INTO @REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE
(
[order_type_id]
,[order_type_description]
,comisionPctg
,[net_sale]
,tax_value
,total
,[transactions_qty]
,[service_total]
,[diners]
,[has_conversion]
,discount
,annulations
,credit_note
,[netSalesNoCommision]
,[totalCommision]
,[averageByTransaction]
,[averageByDiner]
,[salesPercentage]
,[salePercentage]
,[transactionPercentage]
,[dinersPercentage])
exec USP_PA_REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE @tempInitialdate,@tempInitialdate,@currency_id
end try
begin catch
PRINT 'Error en INSERT Tabla1: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
end catch
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE
([branch_id]
,[journal_date]
,[order_type_id]
,[order_type_description]
,[commisionPctg]
,[net_sale]
,[transactions_qty]
,[service_total]
,[diners]
,[has_conversion]
,[netSalesNoCommision]
,[totalCommision]
,[averageByTransaction]
,[averageByDiner]
,[salesPercentage]
,[salePercentage]
,[transactionPercentage]
,[dinersPercentage]
,discount
,annulations
,credit_note
,tax_value
,total)
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate
,rpt.[order_type_id]
,[order_type_description]
,comisionPctg
,[net_sale]
,[transactions_qty]
,[service_total]
,[diners]
,[has_conversion]
,[netSalesNoCommision]
,[totalCommision]
,[averageByTransaction]
,[averageByDiner]
,[salesPercentage]
,[salePercentage]
,[transactionPercentage]
,[dinersPercentage]
,discount
,annulations + isnull((select sum(op.net_price) netAnnulationOrder from T_POS_SUBORDER so
inner join T_POS_ORDER_PRODUCT op on op.suborder_id = so.suborder_id
inner join T_POS_ORDER o on o.order_id = so.order_id
inner join T_POS_JOURNAL j on j.journal_id = o.journal_id
and op.order_product_status_id in (3,4,6)
where suborder_status_id = 3
and o.order_status_id in (0,3,4)
and cast(j.journal_start_datetime as date) = @tempInitialdate and order_type_id = rpt.order_type_id
group by cast(j.journal_start_datetime as date), o.order_type_id ),0)
,credit_note ,tax_value
,total
from @REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE as rpt)
end try
begin catch
PRINT 'Error en INSERT REPORT_SALES_CONSOLIDATE_BY_ORDER_TYPE: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
end catch
-------------------------------------------------------------------------------
-- Insertamos en temporal la informacion del USP PA de consolidado by user
begin try
INSERT INTO @REPORT_SALES_CONSOLIDATE_BY_USER
(
user_id ,
user_name ,
net_sales ,
transactions_qty ,
cancellations ,
tax_value ,
total_sales ,
diners ,
total_tips ,
tips_paid,
has_conversion,
service_value,
averageByTransaction,
averageByDiner,
salePercentage,
transactionPercentage,
dinersPercentage)
exec USP_PA_REPORT_SALES_CONSOLIDATE_BY_USER @tempInitialdate,@tempInitialdate,@currency_id,null
end try
begin catch
PRINT 'Error en INSERT consolidate user: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
end catch
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.[REPORT_SALES_CONSOLIDATE_BY_USER]
([branch_id]
,[journal_date]
,[user_id]
,[user_name]
,[net_sales]
,[transactions_qty]
,[cancellations]
,[tax_value]
,[total_sales]
,[diners]
,[total_tips]
,tips_paid
,[has_conversion]
,service_value
,[averageByTransaction]
,[averageByDiner]
,[salePercentage]
,[transactionPercentage]
,[dinersPercentage])
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate,
user_id ,
user_name ,
net_sales ,
transactions_qty ,
cancellations ,
tax_value ,
total_sales ,
diners ,
total_tips ,
tips_paid ,
has_conversion,
service_value,
averageByTransaction,
averageByDiner,
salePercentage,
transactionPercentage,
dinersPercentage from @REPORT_SALES_CONSOLIDATE_BY_USER)
end try
BEGIN CATCH
PRINT 'Error en INSERT report sales consolidate by user: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-------------------------------------------------------------------------------
-- Insertamos en temporal la informacion del USP PA de consolidado by discount recharge
begin try
INSERT INTO @REPORT_SALES_DISCOUNT_RECHARGE
(
journal_date ,
discount_description ,
discount_value_percentage ,
discount_quantity ,
product_discount ,
discount_total_quantity ,
total_discount ,
discount_percentage ,
discount_sale_percentage ,
discount_category_description ,
total_by_last_cost,
total_by_average_cost,
has_conversion )
exec USP_PA_REPORT_SALES_DISCOUNT_RECHARGE NULL,@tempInitialdate,@tempInitialdate,null,1,@currency_id
end try
BEGIN CATCH
PRINT 'Error en INSERT discount recharge: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.[REPORT_SALES_DISCOUNT_RECHARGE]
([branch_id]
,[journal_date]
,[discount_description]
,[discount_value_percentage]
,[discount_quantity]
,[product_discount]
,[discount_total_quantity]
,[total_discount]
,[discount_percentage]
,[discount_sale_percentage]
,[discount_category_description]
,total_by_last_cost
,total_by_average_cost
,[has_conversion])
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate,
discount_description ,
discount_value_percentage ,
discount_quantity ,
product_discount ,
discount_total_quantity ,
total_discount ,
discount_percentage ,
discount_sale_percentage ,
discount_category_description ,
total_by_last_cost ,
total_by_average_cost ,
has_conversion from @REPORT_SALES_DISCOUNT_RECHARGE)
end try
BEGIN CATCH
PRINT 'Error en INSERT sales discount recharge: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-------------------------------------------------------------------------------
-- Insertamos en temporal la informacion del SP de Profit
begin try
INSERT INTO @REPORT_SALES_PROFITABILITY_REPORT
(
[item_group_id]
,[item_group_description]
,[item_id]
,[item_name]
,[product_id]
,[average_cost]
,[weighted_cost]
,[last_cost]
,[sale_price_1]
,[TotalVenta]
,[Margen]
,[quantity_total]
,[total_average_cost]
,[total_weighted_cost]
,[total_last_cost]
,[total_weighted_cost_2]
,[add_item_cost]
,[add_net_price]
,[company_rif]
,[company_name]
,[PctjeCostoBase])
exec SP_BACKEND_PROFITABILITY_REPORT @tempInitialdate,@tempInitialdate
end try
BEGIN CATCH
PRINT 'Error en INSERT profit: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.[REPORT_SALES_PROFITABILITY_REPORT]
([branch_id]
,[journal_date]
,[item_group_id]
,[item_group_description]
,[item_id]
,[item_name]
,[product_id]
,[average_cost]
,[weighted_cost]
,[last_cost]
,[sale_price_1]
,[TotalVenta]
,[Margen]
,[quantity_total]
,[total_average_cost]
,[total_weighted_cost]
,[total_last_cost]
,[total_weighted_cost_2]
,[add_item_cost]
,[add_net_price]
,[company_rif]
,[company_name]
,[PctjeCostoBase])
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate ,[item_group_id]
,[item_group_description]
,[item_id]
,[item_name]
,[product_id]
,[average_cost]
,[weighted_cost]
,[last_cost]
,[sale_price_1]
,[TotalVenta]
,[Margen]
,[quantity_total]
,[total_average_cost]
,[total_weighted_cost]
,[total_last_cost]
,[total_weighted_cost_2]
,[add_item_cost]
,[add_net_price]
,[company_rif]
,[company_name]
,[PctjeCostoBase] from @REPORT_SALES_PROFITABILITY_REPORT )
-------------------------------------------------------------------------------
end try
BEGIN CATCH
PRINT 'Error en INSERT report sales profit: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-- Insertamos en temporal la informacion del USP PA de consolidado by payform
begin try
INSERT INTO @REPORT_SALES_CONSOLIDATE_BY_PAYFORM
(
pos ,
payform_description ,
quantity ,
net_sale ,
change ,
tips ,
service_payform_value ,
net_amount ,
commission_percentage ,
NetSalesNoCommision ,
totalCommision ,
currency_symbol ,
Total_secondary ,
has_conversion ,
tips_paid
)
exec USP_PA_REPORT_SALES_CONSOLIDATE_BY_PAYFORM @tempInitialdate,@tempInitialdate,@currency_id
end try
BEGIN CATCH
PRINT 'Error en INSERT payform: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.REPORT_SALES_CONSOLIDATE_BY_PAYFORM
([branch_id]
,[journal_date]
, pos ,
payform_description ,
quantity ,
net_sale ,
change ,
tips ,
service_payform_value ,
net_amount ,
commission_percentage ,
NetSalesNoCommision ,
totalCommision ,
currency_symbol ,
Total_secondary ,
has_conversion ,
tips_paid
)
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate,
pos ,
payform_description ,
quantity ,
net_sale ,
change ,
tips ,
service_payform_value ,
net_amount ,
commission_percentage ,
NetSalesNoCommision ,
totalCommision ,
currency_symbol ,
Total_secondary ,
has_conversion,
tips_paid
from @REPORT_SALES_CONSOLIDATE_BY_PAYFORM)
end try
BEGIN CATCH
PRINT 'Error en INSERT payform2: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
if @consolidateBi = 1
begin
begin try
INSERT INTO @REPORT_SALES_MENUITEM_REPORT
(
journal_date ,
hour_sale ,
order_type_description ,
item_type_description ,
family_description,
o_open_order_user_name,
item_description,
net_price,
quantity,
discount_value ,
total_cost ,
tax_value ,
total ,
item_code,
group_description,
subgroup_description
)
(select
cast(o_journal_start_date as date), datepart(hour,op_send_at), o_order_type_description, item_type_description, family_description, o_open_order_user_name,
item_description, sum(op_net_price-isnull(net_price_product_nc,0)),sum(op_quantity_ordered),
sum(opd_discount_value), (iif((select parameter_is_require from t_sys_parameters where parameter_id =358) = 0,sum(isnull(op_lastCost,0)*op_quantity_ordered),sum(isnull(op_avgCost,0)*op_quantity_ordered))) , sum(op_tax_value), sum(op_total-isnull(total_product_nc,0)), item_code, group_description, subgroup_description
FROM UTF_PA_REPORT_SALES_BY_PRODUCT_BASE_V2 ('2000-01-01',@tempInitialdate,@currency_id)
where cast(o_journal_start_date as date) = @tempInitialdate
group by cast(o_journal_start_date as date), datepart(hour,op_send_at),o_order_type_description,item_type_description, item_description, family_description, o_open_order_user_name, item_code, group_description,subgroup_description)
end try
BEGIN CATCH
PRINT 'Error en INSERT report sales menu item: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.REPORT_SALES_MENUITEM_REPORT
([branch_id]
,[journal_date]
, hour_sale ,
order_type_description ,
item_type_description ,
family_description ,
o_open_order_user_name ,
item_description ,
net_price ,
quantity ,
discount_value ,
total_cost ,
tax_value ,
total,
item_code,
group_description,
subgroup_description
)
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate,
hour_sale ,
order_type_description ,
item_type_description ,
family_description,
o_open_order_user_name,
item_description,
net_price,
quantity,
discount_value ,
total_cost ,
tax_value ,
total,
item_code,
group_description,
subgroup_description
from @REPORT_SALES_MENUITEM_REPORT)
end try
BEGIN CATCH
PRINT 'Error en INSERT sales menu item report: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
end
begin try
INSERT INTO @REPORT_UNIT_SOLD_ITEM
(
[item_code]
,[item_name]
,[item_unit_measure]
,[quantity]
,[total_cost]
,total_avg_cost
,[unit_cost]
,[avg_cost]
,[datetime1]
,[order_id]
,[suborder]
,[bill_number]
,[group_field1]
,[group_field2]
,[group_field3]
)
exec [USP_PA_REPORT_SOLD_UNITS] 1,@tempInitialdate,@tempInitialdate,1,@currency_id,1,3,NULL
end try
BEGIN CATCH
PRINT 'Error en INSERT unit sold here: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
begin try
INSERT INTO XETUXDW.dbo.REPORT_UNIT_SOLD_ITEM
(
[branch_id]
,[journal_date]
,[item_code]
,[item_name]
,[item_unit_measure]
,[quantity]
,[total_cost]
,[unit_cost]
,[avg_cost]
,[order_id]
,[suborder]
,[bill_number]
,[group_field1]
,[group_field2]
,[group_field3]
)
select (select (select min(branch_id) from T_SYS_COMPANY )),@tempInitialdate
,[item_code]
,[item_name]
,[item_unit_measure]
,[quantity]
,[total_cost]
,[unit_cost]
,[avg_cost]
,[order_id]
,[suborder]
,[bill_number]
,[group_field1]
,[group_field2]
,[group_field3] from @REPORT_UNIT_SOLD_ITEM
end try
BEGIN CATCH
PRINT 'Error en INSERT sold item 1: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-------------------------------------------------------
begin try
INSERT INTO @REPORT_SALES_DISCOUNT_DETAILS
(
discount_description,
order_product_id,
discount_id,
customer_document,
customer_name,
message_discount,
campaign_code,
campaign_confirmation_key,
item_type_description,
item_description,
op_quantity_ordered,
op_sale_price,
op_net_price,
opd_discount_value,
o_order_id,
b_bill_number,
autorizo ,
dateApply,
o_order_type_description,
environment_description,
mesa,
coupon_code
)
(
select d.discount_description, opd.order_product_id,opd.discount_id,utf.customer_document,utf.customer_name,opd.message_discount, d.campaign_code,d.campaign_confirmation_key, utf.item_type_description, utf.item_description, utf.op_quantity_ordered, utf.op_sale_price, utf.op_net_price, op.amount_discount , o_order_id,b_bill_number, CONCAT(u.first_name,' ',u.last_name) autorizo, opd.dateApply, ot.order_type_Description, e.environment_description, s.space_name, cl.coupon_code from UTF_PA_REPORT_SALES_BY_PRODUCT_BASE_V2('2000-01-01',@tempinitialdate,1) utf
inner join T_POS_ORDER_PRODUCT op on op.order_product_id = utf.op_order_product_id
inner join t_pos_order_product_discount opd on opd.order_product_id = utf.op_order_product_id
inner join T_POS_DISCOUNT d on d.discount_id = opd.discount_id
inner join T_SYS_USER u on u.user_id = opd.auth_user_id
left join t_pos_coupon_log cl on cl.suborder_id = op.suborder_id and d.discount_id = cl.discount_id
inner join t_pos_order o on o.order_id = op.order_id
inner join t_pos_order_type ot on ot.order_type_id = o.order_type_id
inner join t_pos_space s on s.space_id = o.space_id
inner join t_pos_environment e on e.environment_id = o.environment_id
where opd.discount_value <> 0
and cast(o_journal_start_date as date) = @tempinitialdate)
end try
BEGIN CATCH
PRINT 'Error en INSERT report sales discount details: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
-- Insertamos en tabla final el resultado de la tabla temporal
begin try
INSERT INTO XETUXDW.dbo.REPORT_SALES_DISCOUNT_DETAILS
([branch_id]
,[journal_date],
discount_description,
order_product_id,
discount_id,
customer_document,
customer_name,
message_discount,
campaign_code,
campaign_confirmation_key,
item_type_description,
item_description,
op_quantity_ordered,
op_sale_price,
op_net_price,
opd_discount_value,
b_bill_number,
o_order_id,
autorizo,
dateApply,
o_order_type_description,
environment_description,
mesa,
coupon_code
)
(select (select min(branch_id) from T_SYS_COMPANY ),@tempInitialdate,
discount_description,
order_product_id,
discount_id,
customer_document,
customer_name,
message_discount,
campaign_code,
campaign_confirmation_key,
item_type_description,
item_description,
op_quantity_ordered,
op_sale_price,
op_net_price,
opd_discount_value,
b_bill_number,
o_order_id,
autorizo,
dateApply,
o_order_type_description,
environment_description,
mesa,
coupon_code
from @REPORT_SALES_DISCOUNT_DETAILS)
end try
BEGIN CATCH
PRINT 'Error en INSERT sales discount details: ' + ERROR_MESSAGE();
ROLLBACK TRAN;
RETURN;
END CATCH
------------------------------------------------------------------------------
UPDATE XETUXPOS.DBO.T_POS_JOURNAL SET is_reported = 1 where cast(journal_start_datetime as date) = @tempInitialdate
set @tempInitialdate = DATEADD(day,1,@tempInitialdate)
set @counter=@counter+1
end
COMMIT TRAN;
END try
BEGIN catch
SET @ErrorMessage = ERROR_MESSAGE()
SET @ErrorSeverity = ERROR_SEVERITY()
SET @ErrorState = ERROR_STATE()
IF @@TRANCOUNT > 0
ROLLBACK TRAN;
RAISERROR(@ErrorMessage,@ErrorSeverity,@ErrorState);
END catch
END
GO