Scribd
Upload
5 views3 pages

Update VAT Rate Procedure for Orders

The document contains SQL procedures for updating VAT rates for order items and integrating orders. The first procedure updates VAT information for specific order items based on given parameters, while the second procedure performs similar updates and also adjusts total VAT values in related tables. Both procedures ensure that the correct VAT rates are applied and total VAT values are recalculated accordingly.

Uploaded by

Andrei Iancu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views3 pages

Update VAT Rate Procedure for Orders

The document contains SQL procedures for updating VAT rates for order items and integrating orders. The first procedure updates VAT information for specific order items based on given parameters, while the second procedure performs similar updates and also adjusts total VAT values in related tables. Both procedures ensure that the correct VAT rates are applied and total VAT values are recalculated accordingly.

Uploaded by

Andrei Iancu
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd

create procedure [dbo].

[UpdateVatRateForPackOrderItem]

(@order_id bigint=0,

@orderItem_id bigint=0,

@restaurantZone_id bigint=0,

@table_id bigint=0,

@posGroup_id bigint=0,

@operator_id bigint=0,

@client_id bigint=0)

as

begin

declare @paramVat bigint = COALESCE((select TOP 1 id from STVat where vatRate = 0.05), 0)

declare @paramVatToChange bigint = COALESCE((select TOP 1 id from STVat where vatRate = 0.09), 0)

declare @paramVatRateToChange decimal(18,6) = COALESCE((select TOP 1 vatRate from STVat where vatRate =
0.09), 0)

update StOrderItem set

vat_id = @paramVatToChange,

vatRate = @paramVatRateToChange,

totalVatValue = finalValue * (1 - 1/(1 + @paramVatRateToChange)),

isPack = 1

where order_id = @order_id and vat_id = @paramVat

update STOrder set

totalVatValue = (select sum([Link]) from StOrderItem itm where itm.order_id = @order_id)

where id = @order_id

end

GO
ALTER procedure [dbo].[Event_IntegratorAddOrder]

(@order_id bigint=0)

as

begin

declare @paramVat bigint = COALESCE((select TOP 1 id from STVat where vatRate = 0.05), 0)

declare @paramVatToChange bigint = COALESCE((select TOP 1 id from STVat where vatRate = 0.09), 0)

declare @paramVatRateToChange decimal(18,6) = COALESCE((select TOP 1 vatRate from STVat where vatRate =
0.09), 0)

update StOrderItem set

vat_id = @paramVatToChange,

vatRate = @paramVatRateToChange,

totalVatValue = finalValue * (1 - 1/(1 + @paramVatRateToChange)),

isPack = 1

where order_id = @order_id and vat_id = @paramVat

update STOrder set

totalVatValue = (select sum([Link]) from StOrderItem itm where itm.order_id = @order_id)

where id = @order_id

update STDeliveryOrder set

totalVatValue = (select totalVatValue from STOrder ord where [Link] = @order_id)

where order_id = @order_id

end
GO

You might also like