-- Declare a table variable to hold the provided data
DECLARE @DocumentTypeUpdates TABLE (
componentname NVARCHAR(255),
documenttype NVARCHAR(255),
documenttypeconfig NVARCHAR(MAX)
);
-- Insert the provided data into the table variable
INSERT INTO @DocumentTypeUpdates (componentname, documenttype, documenttypeconfig)
VALUES
('documentIn', '*PZK', 'firmID, taxCode, invoiceNR, documentOutNR'),
('documentOut', '*WZK', 'firmID'),
('orderInvoice', 'FZ-K', 'invoiceMemoExport'),
('documentOut', 'KO_MM-', 'warehouseIDB, locationIDB'),
('documentOut', 'KO_PI-', 'organisationUnitID'),
('documentIn', 'KO_PO', 'commisionID'),
('documentIn', 'KO_PS+', 'commisionID'),
('documentOut', 'KO_PS-', 'commisionID'),
('documentIn', 'KO_PW', 'commisionID'),
('documentIn', 'KO_PZ', 'firmID'),
('documentIn', 'KO_PZI', 'currency, firmID, taxCode, invoiceNR, documentOutNR'),
('documentOut', 'KO_RW', 'organisationUnitID'),
('documentOut', 'KO_RWL', 'organisationUnitID'),
('documentOut', 'KO_RWP', 'organisationUnitID, commisionID'),
('documentOut', 'KO_WM-', 'locationIDB'),
('documentOut', 'KO_WZ', 'firmID'),
('documentOut', 'KO_WZD', 'firmID'),
('documentOut', 'KO_WZR', 'firmID'),
('documentOut', 'KO_ZO', 'commisionID'),
('documentOut', 'MM-', 'warehouseIDB, locationIDB'),
('documentOut', 'PI-', 'organisationUnitID'),
('documentIn', 'PO', 'commisionID'),
('documentIn', 'PS+', 'commisionID'),
('documentOut', 'PS-', 'commisionID'),
('documentIn', 'PW', 'commisionID'),
('documentIn', 'PZ', 'firmID, taxCode, invoiceNR, documentOutNR'),
('documentIn', 'PZD', 'firmID, taxCode, invoiceNR, documentOutNR'),
('documentIn', 'PZI', 'currency, firmID, taxCode, invoiceNR, documentOutNR'),
('documentOut', 'RW', 'organisationUnitID'),
('documentOut', 'RW-L', 'organisationUnitID'),
('documentOut', 'RW-P', 'organisationUnitID, commisionID'),
('documentOut', 'WM-', 'locationIDB'),
('documentOut', 'WZ', 'firmID'),
('documentOut', 'WZD', 'firmID'),
('documentOut', 'WZR', 'firmID'),
('documentOut', 'ZO', 'commisionID'),
('order', 'ZP', 'warehouseID'),
('order', 'ZS', 'warehouseID'),
('order', 'ZSC', 'warehouseID'),
('order', 'ZU', 'warehouseID'),
('documentIn', 'ZW', 'commisionID'),
('documentIn', 'ZWR', 'firmID, taxCode, invoiceNR, documentOutNR'),
('order', 'ZZ', 'warehouseID'),
('order', 'ZZA', 'warehouseID'),
('order', 'ZZW', 'warehouseID');
-- Generate the UPDATE statements
DECLARE @componentname NVARCHAR(255);
DECLARE @documenttype NVARCHAR(255);
DECLARE @documenttypeconfig NVARCHAR(MAX);
DECLARE update_cursor CURSOR FOR
SELECT componentname, documenttype, documenttypeconfig
FROM @DocumentTypeUpdates;
OPEN update_cursor;
FETCH NEXT FROM update_cursor INTO @componentname, @documenttype, @documenttypeconfig;
WHILE @@FETCH_STATUS = 0
BEGIN
PRINT 'UPDATE document.documenttype SET documenttypeconfig = ''' + @documenttypeconfig + ''' WHERE componentname = ''' + @componentname + ''' AND documenttype = ''' + @documenttype + ''';';
FETCH NEXT FROM update_cursor INTO @componentname, @documenttype, @documenttypeconfig;
END;
CLOSE update_cursor;
DEALLOCATE update_cursor;
