344 lines
12 KiB
Transact-SQL
344 lines
12 KiB
Transact-SQL
UPDATE TBDD_BASECONFIG SET DB_VERSION = '2.0.0';
|
|
GO
|
|
|
|
IF NOT EXISTS (
|
|
SELECT 1
|
|
FROM sys.objects
|
|
WHERE object_id = OBJECT_ID(N'dbo.PRPM_COPY_PROFILE')
|
|
AND type = 'P' -- 'P' steht für Stored Procedure
|
|
)
|
|
BEGIN
|
|
PRINT 'Procedure existiert nicht.'
|
|
Declare @SQL nvarchar(max)
|
|
SET @SQL = '
|
|
CREATE PROCEDURE [dbo].[PRPM_COPY_PROFILE] (@pProfileID as Integer,@pAddedWho Varchar(100))
|
|
AS
|
|
DECLARE @NEWPROFILE_ID INTEGER
|
|
INSERT INTO [dbo].[TBPM_PROFILE]
|
|
([NAME]
|
|
,[TITLE]
|
|
,[WD_OBJECTTYPE]
|
|
,[PRIORITY]
|
|
,[DESCRIPTION]
|
|
,[TYPE]
|
|
,[LOG_INDEX]
|
|
,[IN_WORK]
|
|
,[ACTIVE]
|
|
,[WD_SEARCH]
|
|
,[PM_VEKTOR_INDEX]
|
|
,[NO_OF_DOCUMENTS]
|
|
,[FINAL_PROFILE]
|
|
,[FINAL_TEXT]
|
|
,[MOVE2FOLDER]
|
|
,[SORT_BY_LATEST]
|
|
,[WORK_HISTORY_ENTRY]
|
|
,[ANNOTATE_ALL_WORK_HISTORY_ENTRIES]
|
|
,[ANNOTATE_WORK_HISTORY_ENTRY]
|
|
,[SQL_VIEW]
|
|
,[ADDED_WHO]
|
|
,[DISPLAY_MODE]
|
|
,[SQL_BTN_FINISH])
|
|
SELECT [NAME] + '' - COPY''
|
|
,[TITLE] + '' - COPY''
|
|
,[WD_OBJECTTYPE]
|
|
,[PRIORITY]
|
|
,[DESCRIPTION]
|
|
,[TYPE]
|
|
,[LOG_INDEX]
|
|
,[IN_WORK]
|
|
,0
|
|
,[WD_SEARCH]
|
|
,[PM_VEKTOR_INDEX]
|
|
,[NO_OF_DOCUMENTS]
|
|
,[FINAL_PROFILE]
|
|
,[FINAL_TEXT]
|
|
,[MOVE2FOLDER]
|
|
,[SORT_BY_LATEST]
|
|
,[WORK_HISTORY_ENTRY]
|
|
,[ANNOTATE_ALL_WORK_HISTORY_ENTRIES]
|
|
,[ANNOTATE_WORK_HISTORY_ENTRY]
|
|
,[SQL_VIEW]
|
|
,@pAddedWho
|
|
,[DISPLAY_MODE]
|
|
,[SQL_BTN_FINISH] FROM TBPM_PROFILE WHERE GUID = @pProfileID;
|
|
|
|
SELECT @NEWPROFILE_ID = MAX(GUID) FROM TBPM_PROFILE WHERE ADDED_WHO = @pAddedWho;
|
|
|
|
INSERT INTO [dbo].[TBPM_PROFILE_FINAL_INDEXING]
|
|
([PROFIL_ID]
|
|
,[CONNECTION_ID]
|
|
,[SQL_COMMAND]
|
|
,[INDEXNAME]
|
|
,[VALUE]
|
|
,[ACTIVE]
|
|
,[DESCRIPTION]
|
|
,[ADDED_WHO]
|
|
,[PREVENT_DUPLICATES]
|
|
,[ALLOW_NEW_VALUES]
|
|
,[IF_VEKTOR_BEHAVIOUR]
|
|
,[SEQUENCE])
|
|
SELECT
|
|
@NEWPROFILE_ID
|
|
,[CONNECTION_ID]
|
|
,[SQL_COMMAND]
|
|
,[INDEXNAME]
|
|
,[VALUE]
|
|
,[ACTIVE]
|
|
,[DESCRIPTION]
|
|
,[ADDED_WHO]
|
|
,[PREVENT_DUPLICATES]
|
|
,[ALLOW_NEW_VALUES]
|
|
,[IF_VEKTOR_BEHAVIOUR]
|
|
,[SEQUENCE]
|
|
FROM [TBPM_PROFILE_FINAL_INDEXING] WHERE PROFIL_ID = @pProfileID
|
|
|
|
|
|
|
|
|
|
|
|
INSERT INTO [dbo].[TBPM_PROFILE_CONTROLS]
|
|
([PROFIL_ID]
|
|
,[NAME]
|
|
,[CTRL_TYPE]
|
|
,[CTRL_TEXT]
|
|
,[X_LOC]
|
|
,[Y_LOC]
|
|
,[HEIGHT]
|
|
,[WIDTH]
|
|
,[INDEX_NAME]
|
|
,[TYP]
|
|
,[VALIDATION]
|
|
,[CHOICE_LIST]
|
|
,[CONNECTION_ID]
|
|
,[DEFAULT_VALUE]
|
|
,[SQL_UEBERPRUEFUNG]
|
|
,[READ_ONLY]
|
|
,[LOAD_IDX_VALUE]
|
|
,[FONT_STYLE]
|
|
,[FONT_SIZE]
|
|
,[FONT_FAMILY]
|
|
,[FONT_COLOR]
|
|
,[ADDED_WHO]
|
|
,[MULTISELECT]
|
|
,[VKT_ADD_ITEM]
|
|
,[VKT_PREVENT_MULTIPLE_VALUES]
|
|
,[REGEX_MATCH]
|
|
,[REGEX_MESSAGE_DE]
|
|
,[REGEX_MESSAGE_EN]
|
|
,[IMAGE_CONTROL]
|
|
,[SQL2]
|
|
,[SQL_ENABLE]
|
|
,[BACKGROUND_COLOR]
|
|
,[OVERWRITE_DATA]
|
|
,[SET_CONTROL_DATA])
|
|
SELECT @NEWPROFILE_ID
|
|
,[NAME]
|
|
,[CTRL_TYPE]
|
|
,[CTRL_TEXT]
|
|
,[X_LOC]
|
|
,[Y_LOC]
|
|
,[HEIGHT]
|
|
,[WIDTH]
|
|
,[INDEX_NAME]
|
|
,[TYP]
|
|
,[VALIDATION]
|
|
,[CHOICE_LIST]
|
|
,[CONNECTION_ID]
|
|
,[DEFAULT_VALUE]
|
|
,[SQL_UEBERPRUEFUNG]
|
|
,[READ_ONLY]
|
|
,[LOAD_IDX_VALUE]
|
|
,[FONT_STYLE]
|
|
,[FONT_SIZE]
|
|
,[FONT_FAMILY]
|
|
,[FONT_COLOR]
|
|
,@pAddedWho
|
|
,[MULTISELECT]
|
|
,[VKT_ADD_ITEM]
|
|
,[VKT_PREVENT_MULTIPLE_VALUES]
|
|
,[REGEX_MATCH]
|
|
,[REGEX_MESSAGE_DE]
|
|
,[REGEX_MESSAGE_EN]
|
|
,[IMAGE_CONTROL]
|
|
,[SQL2]
|
|
,[SQL_ENABLE]
|
|
,[BACKGROUND_COLOR]
|
|
,[OVERWRITE_DATA]
|
|
,[SET_CONTROL_DATA] FROM [TBPM_PROFILE_CONTROLS] WHERE PROFIL_ID = @pProfileID;
|
|
|
|
DECLARE
|
|
@OLD_ID INTEGER,
|
|
@NEW_ID INTEGER
|
|
|
|
DECLARE cursor1 CURSOR FOR
|
|
select T.GUID, T1.GUID from TBPM_PROFILE_CONTROLS T,TBPM_PROFILE_CONTROLS T1 WHERE
|
|
T.CTRL_TYPE = ''TABLE'' and T1.CTRL_TYPE = ''TABLE'' AND T.PROFIL_ID = @pProfileID AND T1.PROFIL_ID = @NEWPROFILE_ID AND T.NAME = T1.NAME
|
|
OPEN cursor1
|
|
FETCH NEXT FROM cursor1 INTO @OLD_ID,@NEW_ID
|
|
WHILE @@FETCH_STATUS = 0
|
|
BEGIN
|
|
INSERT INTO [dbo].[TBPM_CONTROL_TABLE]
|
|
([CONTROL_ID]
|
|
,[SPALTENNAME]
|
|
,[SPALTEN_HEADER]
|
|
,[SPALTENBREITE]
|
|
,[VALIDATION]
|
|
,[CHOICE_LIST]
|
|
,[CONNECTION_ID]
|
|
,[SQL_COMMAND]
|
|
,[READ_ONLY]
|
|
,[LOAD_IDX_VALUE]
|
|
,[ADDED_WHO]
|
|
,[REGEX_MESSAGE_DE]
|
|
,[REGEX_MESSAGE_EN]
|
|
,[REGEX_MATCH]
|
|
,[LOAD_AFT_LOAD_CONTROL]
|
|
,[SEQUENCE])
|
|
SELECT @NEW_ID
|
|
,[SPALTENNAME]
|
|
,[SPALTEN_HEADER]
|
|
,[SPALTENBREITE]
|
|
,[VALIDATION]
|
|
,[CHOICE_LIST]
|
|
,[CONNECTION_ID]
|
|
,[SQL_COMMAND]
|
|
,[READ_ONLY]
|
|
,[LOAD_IDX_VALUE]
|
|
,@pAddedWho
|
|
,[REGEX_MESSAGE_DE]
|
|
,[REGEX_MESSAGE_EN]
|
|
,[REGEX_MATCH]
|
|
,[LOAD_AFT_LOAD_CONTROL]
|
|
,[SEQUENCE] FROM [TBPM_CONTROL_TABLE] WHERE CONTROL_ID = @OLD_ID ORDER BY SEQUENCE
|
|
|
|
|
|
FETCH NEXT FROM cursor1 INTO @OLD_ID,@NEW_ID
|
|
END
|
|
CLOSE cursor1
|
|
DEALLOCATE cursor1'
|
|
|
|
EXEC sp_executesql @sql
|
|
|
|
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, 200, 0, 0),
|
|
('INVOICE_POSITION_UNIT_TYPE', '', 'POSITION', 10, 201, 0, 0),
|
|
('INVOICE_POSITION_ARTICLE', '', 'POSITION', 10, 202, 0, 0),
|
|
('INVOICE_POSITION_ARTICLE_DESCRIPTION', '', 'POSITION', 10, 203, 0, 0),
|
|
('INVOICE_POSITION_NOTE', '', 'POSITION', 10, 205, 0, 0),
|
|
('INVOICE_TAXPOS_TAX_RATE', '', 'POSITION', 10, 206, 0, 0),
|
|
('INVOICE_POSITION_TAX_AMOUNT', '', 'POSITION', 10, 207, 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 25.04.2025 INV_POSITION_UNIT_TYPE integriert
|
|
--CHANGED 06.03.2025 INV_POS_NOTE integriert
|
|
ALTER FUNCTION [dbo].[FNDD_ZUGFERD_VIEW_RECEIPT_CUST_RESULT] (@pMSGID VARCHAR(100))
|
|
RETURNS TABLE
|
|
AS RETURN
|
|
(
|
|
SELECt A.SPEC_NAME,A.ITEM_VALUE,A.Area,A.Row_Caption,A.xPosition,A.Y_eq_lastrow,A.SequenceItem ORDER_SEQ,A.Display FROM VWDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE_ITEMS A
|
|
WHERE REFERENCE_GUID = @pMSGID
|
|
AND A.SPEC_NAME NOT IN ('INVOICE_POSITION_AMOUNT','INVOICE_POSITION_UNIT_TYPE','INVOICE_POSITION_NOTE',
|
|
'INVOICE_TAXPOS_TAX_RATE','INVOICE_POSITION_ARTICLE','INVOICE_POSITION_ARTICLE_DESCRIPTION','INVOICE_TAXPOS_AMOUNT',
|
|
'INVOICE_TAXPOS_RATE','INVOICE_TAXPOS_TYPE','INVOICE_POSITION_TAX_AMOUNT')
|
|
UNION
|
|
SELECT SPEC_NAME,ITEM_VALUE,Area,'' Row_Caption,10 xPosition, 0 Y_eq_lastrow,SequenceItem ORDER_SEQ, Display FROM [dbo].[FNDD_ZUGFERD_VIEW_RECEIPT_POSITIONS] (@pMSGID)
|
|
UNION
|
|
SELECT SPEC_NAME,ITEM_VALUE,Area,'' Row_Caption,10 xPosition, 0 Y_eq_lastrow,SequenceItem ORDER_SEQ, Display FROM [dbo].[FNDD_ZUGFERD_VIEW_RECEIPT_TAX] (@pMSGID)
|
|
UNION
|
|
SELECT SPECNAME SPEC_NAME,Row_Caption,Area,Row_Caption Row_Caption,10 xPosition, 0 Y_eq_lastrow,SequenceItem ORDER_SEQ, Display FROM TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE WHERE Area = 'INTERNAL'
|
|
)
|
|
GO
|
|
|
|
|
|
SET ANSI_NULLS ON
|
|
GO
|
|
|
|
SET QUOTED_IDENTIFIER ON
|
|
GO
|
|
|
|
|
|
--CHANGED 11.07.2025 Index dynamisch
|
|
--CHANGED 02.06.2025 Nur Items mit INhalt
|
|
--CHANGED 25.04.2025 INV_POSITION_UNIT_TYPE integriert
|
|
--CHANGED 06.03.2025 INV_POS_NOTE integriert
|
|
ALTER FUNCTION [dbo].[FNDD_ZUGFERD_VIEW_RECEIPT_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 = MAX(SequenceItem) FROM TBDD_ZUGFERD_VIEW_RECEIPT_TEMPLATE WHERE Area = 'BUYER';
|
|
DECLARE cursGroupCounter CURSOR FOR
|
|
select GROUP_COUNTER from TBEDMI_ITEM_VALUE WHERE REFERENCE_GUID = @pMSGID And SPEC_NAME = 'INVOICE_POSITION_AMOUNT' 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 ('INVOICE_POSITION_AMOUNT','INVOICE_POSITION_UNIT_TYPE','INVOICE_POSITION_NOTE',
|
|
'INVOICE_TAXPOS_TAX_RATE','INVOICE_POSITION_ARTICLE','INVOICE_POSITION_ARTICLE_DESCRIPTION','INVOICE_TAXPOS_AMOUNT',
|
|
'INVOICE_TAXPOS_RATE','INVOICE_TAXPOS_TYPE','INVOICE_POSITION_TAX_AMOUNT') 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,'POSITION',@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
|
|
GO
|
|
ALTER TABLE TBDD_USER
|
|
ALTER COLUMN DELETED_WHO VARCHAR(50)
|
|
GO
|
|
|
|
|
|
|
|
|