Preparing for deployment

Migration to production is always a heart wrenching affair. With some Siebel releases, with multiple developers coming and going it gets worse.

We have attempted to keep a fairly strict deployment method as we do deployments entirely by hand; our admins have decided not to use ADM for better or worse. We're a multilingual installation and I think there were issues in getting the LOV migration to go well. In any case, using ADM or not, managing deployment artifacts is always tricky and the more time between releases the situation can get very grave.

For the latest release we've been doing a significant overhaul of some processes that use workflow processes, unfortunately some cruft has crept into the repository with multiple versions of the same process marked as Completed without being updated to Not In Use. Add to that, but how do we tell the deployment team what to deploy?

We have a series of deployment artifacts that document what goes into each release (and is maintained in a version control system):

• Bill of Materials.docx - general document that holds a list of the specific files, images, etc that must be installed, instructions etc.
• Dispatch Rule Sets.xlsx - a spreadsheet with dispatch rules
• List Of Values.xlsx - a spreadsheet with the LOV changes (adds in green, updates in black, and deletions (deactivations) in red
• Siebel System Configuration.docx - the standard reference bible for our implementation. Includes the component configurations, workflow policies, repeating jobs, workflows, web services, etc. Basically, the reference guide to our environment

I was having trouble getting a good list of workflow processes that were added, updated and deactivated between this upcoming release and the last one, so I wrote some SQL that helped me come up with a proper list (and helped me clean up the WF repository).

The prerequisite to using these statements is to import the last release repository into the development database under a different name. We typically just increment the version number so we have these repositories to choose from:

EXPIRED WORKFLOWS BETWEEN REPOSITORIES

SELECT DISTINCT A.PROC_NAME
FROM S_WFR_PROC A
    INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
WHERE
    B.NAME = 'Siebel Repository 1.9'
    AND A.STATUS_CD = 'COMPLETED'
    AND A.PROC_NAME IN
    (
        SELECT DISTINCT PROC_NAME
        FROM S_WFR_PROC A
            INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
        WHERE
            B.NAME = 'Siebel Repository'
            AND NOT EXISTS
            (
            SELECT * FROM S_WFR_PROC A1
                INNER JOIN S_REPOSITORY B1 ON A1.REPOSITORY_ID = B1.ROW_ID
            WHERE
                B1.NAME = 'Siebel Repository'
                AND A1.PROC_NAME = A.PROC_NAME
                AND A1.STATUS_CD = 'COMPLETED'
            )   
    )

 NEW AND UPDATED WORKFLOWS BETWEEN REPOSITORIES

SELECT A.PROC_NAME, A.VERSION [v.NEXT], C.VERSION [v.1.9]
FROM S_WFR_PROC A
    INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
    LEFT OUTER JOIN
    (
        -- GETS MOST CURRENT VERSION
        SELECT A.PROC_NAME, A.VERSION
        FROM S_WFR_PROC A
            INNER JOIN S_REPOSITORY B ON A.REPOSITORY_ID = B.ROW_ID
            INNER JOIN
                (
                    SELECT PROC_NAME, MAX(VERSION) [VERSION]
                    FROM S_WFR_PROC A1
                        INNER JOIN S_REPOSITORY B1 ON A1.REPOSITORY_ID = B1.ROW_ID
                    WHERE
                        B1.NAME = 'Siebel Repository 1.9'
                    GROUP BY A1.PROC_NAME
                ) C ON C.PROC_NAME = A.PROC_NAME AND C.VERSION = A.VERSION
        WHERE
            B.NAME = 'Siebel Repository 1.9'
            AND A.STATUS_CD = 'COMPLETED'
    ) C ON A.PROC_NAME = C.PROC_NAME
WHERE
B.NAME = 'Siebel Repository'
AND A.STATUS_CD = 'COMPLETED'
AND A.PROC_NAME LIKE 'ABC%'
AND (A.VERSION != C.VERSION OR C.VERSION IS NULL)
ORDER BY A.PROC_NAME