118 lines
6.3 KiB
Transact-SQL
118 lines
6.3 KiB
Transact-SQL
UPDATE TBDD_BASECONFIG SET DB_VERSION = '2.2';
|
|
GO
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM sys.objects
|
|
WHERE object_id = OBJECT_ID(N'dbo.TBFILE_EXTENSION_OVERRIDE')
|
|
)
|
|
CREATE TABLE dbo.TBFILE_EXTENSION_OVERRIDE(
|
|
GUID INT IDENTITY(1,1) NOT NULL,
|
|
FILE_EXTENSION NVARCHAR(10) NOT NULL,
|
|
PROCESS NVARCHAR(100) NOT NULL,
|
|
ADDED_WHO VARCHAR(50) DEFAULT 'DEFAULT',
|
|
ADDED_WHEN DATETIME DEFAULT GETDATE(),
|
|
CHANGED_WHO VARCHAR(50) NULL,
|
|
CHANGED_WHEN DATETIME NULL,
|
|
CONSTRAINT PK_TBFILE_EXTENSION_OVERRIDE PRIMARY KEY (FILE_EXTENSION)
|
|
)
|
|
GO
|
|
CREATE OR ALTER TRIGGER [dbo].[TBFILE_EXTENSION_OVERRIDE_AFT_UPD] ON [dbo].TBFILE_EXTENSION_OVERRIDE
|
|
FOR UPDATE
|
|
AS
|
|
BEGIN
|
|
UPDATE TBFILE_EXTENSION_OVERRIDE SET CHANGED_WHEN = GETDATE() FROM INSERTED WHERE TBFILE_EXTENSION_OVERRIDE.GUID = INSERTED.GUID
|
|
END
|
|
GO
|
|
DELETE FROM TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE;
|
|
Insert INTO TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE
|
|
(SPECNAME, Row_Caption, Area, xPosition, SequenceItem, Y_eq_lastrow, Display) values
|
|
('INVOICE_TYPE', '', 'TYPE', 10, 1, 0, 0),
|
|
('INVOICE_CURRENCY', '', 'TYPE', 10, 2, 0, 0),
|
|
('INVOICE_NUMBER', 'Rechnungsnummer/Invoice-No:', 'HEAD', 10, 3, 0, 1),
|
|
('INVOICE_DATE', 'Datum/Date:', 'HEAD', 10, 4, 0, 1),
|
|
('INVOICE_SERVICE_DATE','Leistungsdatum/Service date:', 'HEAD', 10, 5, 0, 1),
|
|
('INVOICE_COST_CENTER','Kostenstelle/CostCenter:', 'HEAD', 10, 6, 0, 1),
|
|
('INVOICE_REFERENCE','Best.Ref/OrderReference:', 'HEAD', 10, 7, 0, 1),
|
|
('INVOICE_SELLER_NAME', '', 'SELLER', 10, 8, 0, 1),
|
|
('INVOICE_SELLER_ADDRESS', '', 'SELLER', 10, 9, 0, 1),
|
|
('INVOICE_SELLER_POSTALCODE', '', 'SELLER', 10, 10, 0, 1),
|
|
('INVOICE_SELLER_CITY', '', 'SELLER', 35, 11, 1, 1),
|
|
('INVOICE_SELLER_TAX_ID', 'USt-ID/Seller tax ID:', 'SELLER', 10, 12, 0, 1),
|
|
('INVOICE_SELLER_ID', 'Lieferant-Nr/Seller ID:', 'SELLER', 10, 13, 0, 1),
|
|
('INVOICE_BUYER_NAME', '', 'BUYER', 10, 14, 0, 1),
|
|
('INVOICE_BUYER_ADRESS2', '', 'BUYER', 10, 15, 0, 1),
|
|
('INVOICE_BUYER_ADRESS', '', 'BUYER', 10, 16, 0, 1),
|
|
('INVOICE_BUYER_POSTALCODE', '', 'BUYER', 10, 17, 0, 1),
|
|
('INVOICE_BUYER_CITY', '', 'BUYER', 35, 18, 1, 1),
|
|
--('STATIC_Y_SWITCH', '271', 'INTERNAL', 35, 15, 0, 0),
|
|
('INVOICE_POSITION_AMOUNT', '', 'POSITION', 10, 20, 0, 0),
|
|
('INVOICE_POSITION_UNIT_TYPE', '', 'POSITION', 10, 21, 0, 0),
|
|
('INVOICE_POSITION_ARTICLE', '', 'POSITION', 10, 22, 0, 0),
|
|
('INVOICE_POSITION_ARTICLE_DESCRIPTION', '', 'POSITION', 10, 23, 0, 0),
|
|
('INVOICE_POSITION_NOTE', '', 'POSITION', 10, 24, 0, 0),
|
|
('INVOICE_TAXPOS_TAX_RATE', '', 'POSITION', 10, 25, 0, 0),
|
|
('INVOICE_POSITION_TAX_AMOUNT', '', 'POSITION', 10, 26, 0, 1),
|
|
('POSITION_ALLOWANCE_ACTUAL_AMOUNT', '', 'ALLOWANCE',10, 100, 0, 1),
|
|
('POSITION_ALLOWANCE_REASON', '', 'ALLOWANCE',10, 101, 0, 1),
|
|
('POSITION_ALLOWANCE_REASON_CODE', '', 'ALLOWANCE',10, 102, 0, 1),
|
|
('POSITION_ALLOWANCE_CALCULATION_PERCENT', '', 'ALLOWANCE',10, 103, 0, 1),
|
|
('RECEIPT_ALLOWANCE_CHARGE_INDICATOR', '', 'ALLOWANCE',10, 110, 0, 0),
|
|
('RECEIPT_ALLOWANCE_ACTUAL_AMOUNT', '', 'ALLOWANCE',10, 111, 0, 1),
|
|
('RECEIPT_ALLOWANCE_REASON', '', 'ALLOWANCE',10, 112, 0, 1),
|
|
('RECEIPT_ALLOWANCE_REASON_CODE', '', 'ALLOWANCE',10, 113, 0, 1),
|
|
('RECEIPT_ALLOWANCE_CALCULATION_PERCENT', '', 'ALLOWANCE',10, 114, 0, 1),
|
|
('RECEIPT_ALLOWANCE_VAT_RATE', '', 'ALLOWANCE',10, 115, 0, 1),
|
|
('RECEIPT_ALLOWANCE_VAT_CODE', '', 'ALLOWANCE',10, 116, 0, 1),
|
|
('INVOICE_TOTAL_NET', 'Nettobetrag/Net amount:', 'AMOUNT', 10, 400, 0, 1),
|
|
('INVOICE_TOTAL_TAX', 'Steuerbetrag/Tax amount:', 'AMOUNT', 10, 401, 0, 1),
|
|
('INVOICE_TOTAL_GROSS', 'Bruttobetrag/Gross amount:', 'AMOUNT', 10, 402, 0, 1),
|
|
('INVOICE_TAXPOS_RATE', '', 'TAXPOS', 10, 403, 1, 0),
|
|
('INVOICE_TAXPOS_AMOUNT', '', 'TAXPOS', 10, 404, 0, 0),
|
|
('INVOICE_TAXPOS_TYPE', '', 'TAXPOS', 10, 405, 1, 1),
|
|
('INVOICE_TAXPOS_EXEMPTION_REASON', '', 'EXEMPTION', 10, 406, 0, 1),
|
|
('INVOICE_TAXPOS_EXEMPTION_REASON_CODE', '', 'EXEMPTION', 10, 407, 0, 1),
|
|
('INVOICE_PAYMENT_TERMS','', 'PAYMENT', 10, 500, 0, 1),
|
|
('INVOICE_PAYMENT_IBAN','IBAN:', 'PAYMENT', 10, 501, 0, 1),
|
|
('INVOICE_PAYMENT_BIC','BIC:', 'PAYMENT', 10, 502, 0, 1);
|
|
GO
|
|
|
|
--CHANGED 27.10.2025 Beide Allowance Varianten
|
|
ALTER FUNCTION [dbo].[FNDD_ZUGFERD_VIEW_ALLOWANCE_POSITIONS] (@pMSGID VARCHAR(100))
|
|
RETURNS @TABLE TABLE(SPEC_NAME VARCHAR(100),ITEM_VALUE VARCHAR(250),Area VARCHAR(60),SequenceItem INT,Display BIT)
|
|
AS
|
|
BEGIN
|
|
|
|
DECLARE @GROUP_COUNTER INT,@INDEX INT
|
|
SELECT @INDEX = MIN(SequenceItem) FROM TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE WHERE Area = 'ALLOWANCE';
|
|
DECLARE cursGroupCounter CURSOR FOR
|
|
select GROUP_COUNTER from TBEDMI_ITEM_VALUE WHERE REFERENCE_GUID = @pMSGID
|
|
And SPEC_NAME IN ('POSITION_ALLOWANCE_ACTUAL_AMOUNT','RECEIPT_ALLOWANCE_ACTUAL_AMOUNT')
|
|
AND LEN(ITEM_VALUE) > 0 ORDER BY GROUP_COUNTER
|
|
OPEN cursGroupCounter
|
|
FETCH NEXT FROM cursGroupCounter INTO @GROUP_COUNTER
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
DECLARE @SPEC_NAME VARCHAR(100),@ITEM_VALUE VARCHAR(100),@Display BIT
|
|
DECLARE cursPosItems CURSOR FOR
|
|
select A.SPEC_NAME,ITEM_VALUE,Display from TBEDMI_ITEM_VALUE A INNER JOIN TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE B ON A.SPEC_NAME = B.SPECNAME
|
|
WHERE REFERENCE_GUID = @pMSGID And A.SPEC_NAME IN (SELECT SPECNAME FROM TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE WHERE Area = 'ALLOWANCE')
|
|
AND A.GROUP_COUNTER = @GROUP_COUNTER
|
|
AND LEN(ITEM_VALUE) > 0
|
|
ORDER BY B.SequenceItem
|
|
OPEN cursPosItems
|
|
FETCH NEXT FROM cursPosItems INTO @SPEC_NAME,@ITEM_VALUE,@Display
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
SET @INDEX += 1
|
|
INSERT INTO @TABLE (SPEC_NAME,ITEM_VALUE,Area,SequenceItem,Display) VALUES
|
|
(@SPEC_NAME,@ITEM_VALUE,'ALLOWANCE',@INDEX,@Display)
|
|
FETCH NEXT FROM cursPosItems INTO @SPEC_NAME,@ITEM_VALUE,@Display
|
|
END
|
|
CLOSE cursPosItems
|
|
DEALLOCATE cursPosItems
|
|
FETCH NEXT FROM cursGroupCounter INTO @GROUP_COUNTER
|
|
END
|
|
CLOSE cursGroupCounter
|
|
DEALLOCATE cursGroupCounter
|
|
RETURN
|
|
END |