Pylon SQL - Change Pylon Codes
-- Get Customer Category
SELECT
hecustomers.hecode,
hecustomers.hename,
HECATEGORIESCSTM.hecode AS cat
FROM hecustomers
INNER JOIN HECATEGORIESCSTM
ON hecustomers.hecat01id = HECATEGORIESCSTM.heid
-- Change Customer Code
UPDATE hecustomers
SET hecustomers.hecode = REPLACE(hecustomers.hecode, 'ΠΕΛΑ-', CONCAT('C', HECATEGORIESCSTM.hecode, '-'))
FROM hecustomers
INNER JOIN HECATEGORIESCSTM
ON hecustomers.hecat01id = HECATEGORIESCSTM.heid
WHERE hecustomers.hecode LIKE 'ΠΕΛΑ-%'
UPDATE hecustomers
SET hecustomers.hecode = REPLACE(hecustomers.hecode, RIGHT(hecustomers.hecode, 8), RIGHT(hecustomers.hecode, 6))
FROM hecustomers
INNER JOIN HECATEGORIESCSTM
ON hecustomers.hecat01id = HECATEGORIESCSTM.heid
WHERE hecustomers.hecode LIKE 'C%'
-- Change Services Code
UPDATE HEITEMS
SET HECODE = REPLACE(HECODE, RIGHT(HEITEMS.hecode, 8), CONCAT('S-0000', RIGHT(HEITEMS.hecode, 1) + 58))
WHERE HECODE LIKE '00000%'
-- Change Payment Methods Name
UPDATE heretpaymentmethods
SET HENAME = CONCAT(SUBSTRING(HECODE, 1, 2), ' - ', HENAME)
WHERE HENAME IS NOT NULL
OR HECODE IS NOT NULL
-- Get Doc Series for specific suffix
SELECT
*
FROM HEDOCSERIES
WHERE HECODE LIKE '%AG'
-- Get Old Doc Series and New Doc Series Code before update
SELECT
HECODE,
REPLACE(HECODE, '_AG', '-AG') AS HENewCODE
FROM HEDOCSERIES
WHERE HECODE LIKE '%_AG'
-- Change Doc Series Code Suffix
UPDATE HEDOCSERIES
SET HECODE = REPLACE(HECODE, '_AG', '-AG'),
HESHORTCUT = REPLACE(HESHORTCUT, '_AG', '-AG')
WHERE HECODE LIKE '%_AG'