Open Forum

Expand all | Collapse all

SQL Update on locked field: Ship to ID/Address ID/Address Code

  • 1.  SQL Update on locked field: Ship to ID/Address ID/Address Code

    Posted 5 days ago
    We've got an incorrect address ID. Well, actually a bunch of 'em.

    The Address ID field is locked once entered. No editing allowed. No PSTL modification option either.


    In the GP tables the columns are either ADRSCODE (address code) or PRSTADCD (primary ship to address code).

    In some tables they are a primary key.


    A search for the incorrect address ID 'DAV' reveals it's present in 12 tables. It appears a SQL update is the best/only option.

    The update statement below touches each table swapping out one address ID 'DAV' for another 'DVE'.

    This SQL statement appears to be successful in our test database.


    Am I missing something else? Anyone have another preferred update method?

    Highlighted Address ID field



    BEGIN TRANSACTION

    --GP
    UPDATE RM00102 SET ADRSCODE = 'DVE' WHERE ADRSCODE = 'DAV'
    UPDATE SVC00950 SET ADRSCODE = 'DVE' WHERE ADRSCODE = 'DAV'
    UPDATE SY01200 SET ADRSCODE = 'DVE' WHERE ADRSCODE = 'DAV'
    UPDATE SOP10100 SET PRSTADCD = 'DVE' WHERE PRSTADCD = 'DAV'
    UPDATE SOP10200 SET PRSTADCD = 'DVE' WHERE PRSTADCD = 'DAV'
    UPDATE SOP30200 SET PRSTADCD = 'DVE' WHERE PRSTADCD = 'DAV'
    UPDATE SOP30300 SET PRSTADCD = 'DVE' WHERE PRSTADCD = 'DAV'
    UPDATE SY90000 SET PROPERTYNAME = 'DVE' WHERE PROPERTYNAME = 'DAV'

    --SALESPAD
    UPDATE SPCUSTOMERADDR SET ADDRESS_CODE = 'DVE' WHERE ADDRESS_CODE = 'DAV'
    UPDATE SPXCUSTOMERADDR SET ADDRESS_CODE = 'DVE' WHERE ADDRESS_CODE = 'DAV'
    UPDATE SPXSALESDOCUMENT SET XSHIPTOADDRESSCODE = 'DVE' WHERE XSHIPTOADDRESSCODE = 'DAV'
    UPDATE SPXSALESLINEITEM SET XSTORE = 'DVE' WHERE XSTORE = 'DAV'

    COMMIT


    ------------------------------
    Brad Smith
    SE Retail
    Fountain Inn SC
    ------------------------------
    Conference-GPUG_200x200


  • 2.  RE: SQL Update on locked field: Ship to ID/Address ID/Address Code

    TOP CONTRIBUTOR
    Posted 4 days ago
    The value you want to change for address code is stored under several different column names in core GP including:
    ADRSCODE
    PRBTADCD
    PRSTADCD
    STADDRCD
    SVC_BILL_TO_ADDRESS_CODE

    And covers 100's of tables.

    This does not include third parties.

    If you have a lot to do you are better off first putting the old and new values in a temp sql table with the columns names.
    Something like this:

    IF OBJECT_ID('dbo.zzcolumnchanger', 'U') IS NOT NULL
    DROP TABLE dbo.zzcolumnchanger
    GO

    CREATE TABLE dbo.zzcolumnchanger(
    iUpdate tinyint NOT NULL,
    strColumn varchar(25) NOT NULL,
    iColIsNum tinyint NOT NULL,
    strOldField varchar(35) NOT NULL,
    strNewField varchar(35) NOT NULL
    )
    GO

    Then use a table cursor to cursor the sysobjects to find all table incidents of the columns you provided that way you do not miss any tables.
    Something like this:

    DECLARE Table_Cursor CURSOR FOR
    select o.name from sysobjects o, syscolumns c
    where o.id = c.id
    and o.type = 'U'
    and c.name = @Column
    order by o.name

    OPEN Table_Cursor

    FETCH NEXT FROM Table_Cursor into @Table

    WHILE @@FETCH_STATUS = 0
    BEGIN

    And then run your update in the cursor script using the temp sql table with your values.

    Before PSTL this was how everything was updated. After PSTL, now need for things like Address code changes and others that are not in PSTL





    ------------------------------
    Thaddeus Suter
    Retus, Inc
    HELOTES TX
    ------------------------------

    Conference-GPUG_200x200


  • 3.  RE: SQL Update on locked field: Ship to ID/Address ID/Address Code

    TOP CONTRIBUTOR
    Posted 3 days ago
    I'd check out the Professional Services Tools Library.  It is the safest way to do this (although its going to do exactly what you're doing here).

    And you're probably already doing this, but do this in a test environment first AND do a complete backup in production before you do it there.

    ------------------------------
    Blair Christensen
    Database Administrator
    Oppenheimer Companies, Inc.
    Boise Idaho GPUG Chapter Leader
    ------------------------------

    Conference-GPUG_200x200


  • 4.  RE: SQL Update on locked field: Ship to ID/Address ID/Address Code

    TOP CONTRIBUTOR
    Posted 3 days ago
    PSTL does not do RM or PM address code changes or combines......  GP2016 and forward

    ------------------------------
    Thaddeus Suter
    Retus, Inc
    HELOTES TX
    ------------------------------

    Conference-GPUG_200x200


If you've found this thread useful, dive deeper into User Group community content by role