Setup, Start or Schedule an Aurora Page Layout w/SQL

On my last post, Create and Enable Aurora Page Layouts in WebSphere Commerce, I left the last step in that tutorial to be completed through the use of Management Center by business users. As there are sometimes where it’s entirely a techie responsibility, I wanted to follow that post up with the actual SQL used to setup and schedule that newly created page layout to the actual WebSphere Commerce page.

The example used below is to replace the Page Layout for a static page, in this particular example the Home Page. I’m fairly confident that the SQL is pretty similar if you’re planning to setup a category, product, or product type page.

I have generalized the following SQL statement so you’ll need to replace the following items in the SQL match up with your specific needs:

• {STORE_IDENTIFIER}: The Store Identifier of the store you wish to associate the Page Layout with.
• {DMACTIVITY_NAME}: A unique name used to identify the DMACTIVITY record.
• {START_DATE}: The date and time that the Page Layout will begin take effect.
• {END_DATE}: The date and time that the Page Layout will cease to take effect.
• {LOGON_ID}: The Logon ID of the user to be recorded as making the change for historical reasons.
• {DESCRIPTION}: The description of the marketing activity.
• {PAGELAYOUT_NAME}: Name of the Page Layout you’ll be assigning to the respective page type.

As this example is entirely for the Home Page, if you choose to do it for another type of static page, there are additional properties that will need to be replaced. I have also not generalized the SQL to include priority and/or sequence columns, and I’m defaulting them to the highest available priority and/or sequence.

INSERT INTO DMACTIVITY (DMACTIVITY_ID, STOREENT_ID, NAME, VERSION, PUBLISHED, STATE, STARTDATE, ENDDATE, PRIORITY, DMCAMPAIGN_ID, DMACTTYPE_ID, DMTEMPLATETYPE_ID, DMEXPTYPE_ID, REPEATABLE, CREATED, LASTUPDATE, LASTUPDATEDBY, DESCRIPTION, OPTCOUNTER, BEHAVIOR) VALUES (
    (SELECT CASE WHEN MIN(DMACTIVITY_ID) > -10001 THEN -10001 ELSE MIN(DMACTIVITY_ID) - 1 END FROM DMACTIVITY),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    '{DMACTIVITY_NAME}',
    1,
    0,
    0,
    '{START_DATE}',
    '{END_DATE}',
    0,
    NULL,
    (SELECT DMACTTYPE_ID FROM DMACTTYPE WHERE TYPE = 'layoutAssociation'),
    NULL,
    NULL,
    1,
    CURRENT TIMESTAMP,
    CURRENT TIMESTAMP,
    (SELECT 'DM_MbrId=' || U.USERS_ID || '&DM_StoreId=' || (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') || '&DM_PznId=' || U.PERSONALIZATIONID FROM USERS AS U JOIN USERREG AS R ON (U.USERS_ID = R.USERS_ID) WHERE R.LOGONID = '{LOGON_ID}'),
    '{DESCRIPTION}',
    0,
    0
);

INSERT INTO DMELEMENT (DMELEMENT_ID, NAME, DMELETEMPLATE_ID, DMACTIVITY_ID, RELATED_ID, PRIORITY, PARENT, SEQUENCE) VALUES (
    (SELECT CASE WHEN MIN(DMELEMENT_ID) > -10001 THEN -10001 ELSE MIN(DMELEMENT_ID) - 1 END FROM DMELEMENT),
    'path',
    (SELECT DMELETEMPLATE_ID FROM DMELETEMPLATE WHERE NAME = 'path' AND DMELEMENTTYPE_ID = (SELECT DMELEMENTTYPE_ID FROM DMELEMENTTYPE WHERE TYPE = 'Path')),
    (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    NULL,
    NULL,
    NULL,
    1
);

INSERT INTO DMELEMENT (DMELEMENT_ID, NAME, DMELETEMPLATE_ID, DMACTIVITY_ID, RELATED_ID, PRIORITY, PARENT, SEQUENCE, OPTCOUNTER) VALUES (
    (SELECT CASE WHEN MIN(DMELEMENT_ID) > -10001 THEN -10001 ELSE MIN(DMELEMENT_ID) - 1 END FROM DMELEMENT),
    'viewEMarketingSpot',
    (SELECT DMELETEMPLATE_ID FROM DMELETEMPLATE WHERE NAME = 'viewEMarketingSpot' AND DMELEMENTTYPE_ID = (SELECT DMELEMENTTYPE_ID FROM DMELEMENTTYPE WHERE TYPE = 'Trigger')),
    (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    NULL,
    NULL,
    'path',
    2,
    0
);

INSERT INTO DMELEMENTNVP (DMELEMENT_ID, NAME, VALUE, TRIGGERMATCH, PROPERTIES, SEQUENCE, OPTCOUNTER) VALUES (
    (SELECT DMELEMENT_ID FROM DMELEMENT WHERE NAME = 'viewEMarketingSpot' AND DMACTIVITY_ID = (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'))),
    'emsId',
    (SELECT EMSPOT_ID FROM EMSPOT WHERE USAGETYPE = 'LAYOUTMARKETINGSPOT' AND NAME = 'HomePage' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    0,
    NULL,
    NULL,
    0
);

INSERT INTO DMELEMENT (DMELEMENT_ID, NAME, DMELETEMPLATE_ID, DMACTIVITY_ID, RELATED_ID, PRIORITY, PARENT, SEQUENCE, OPTCOUNTER) VALUES (
    (SELECT CASE WHEN MIN(DMELEMENT_ID) > -10001 THEN -10001 ELSE MIN(DMELEMENT_ID) - 1 END FROM DMELEMENT),
    'showPageLayout',
    (SELECT DMELETEMPLATE_ID FROM DMELETEMPLATE WHERE NAME = 'showPageLayout' AND DMELEMENTTYPE_ID = (SELECT DMELEMENTTYPE_ID FROM DMELEMENTTYPE WHERE TYPE = 'Action')),
    (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    NULL,
    NULL,
    'path',
    3,
    0
);

INSERT INTO DMELEMENTNVP (DMELEMENT_ID, NAME, VALUE, TRIGGERMATCH, PROPERTIES, SEQUENCE, OPTCOUNTER) VALUES (
    (SELECT DMELEMENT_ID FROM DMELEMENT WHERE NAME = 'showPageLayout' AND DMACTIVITY_ID = (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'))),
    'pageLayoutId',
    (SELECT PAGELAYOUT_ID FROM PAGELAYOUT WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{PAGELAYOUT_NAME}'),
    0,
    NULL,
    NULL,
    0
);

INSERT INTO DMTRIGLSTN (DMELEMENT_ID, NAME, STOREENT_ID, DMACTIVITY_ID, ALLUSERS, OPTCOUNTER) VALUES (
    (SELECT DMELEMENT_ID FROM DMELEMENT WHERE NAME = 'viewEMarketingSpot' AND DMACTIVITY_ID = (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'))),
    'DisplayEms',
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    1,
    0
);

UPDATE DMELEMENTNVP SET TRIGGERMATCH = 1, OPTCOUNTER = 1
    WHERE DMELEMENTNVP.DMELEMENT_ID = (SELECT DMELEMENT_ID FROM DMELEMENT WHERE NAME = 'viewEMarketingSpot' AND DMACTIVITY_ID = (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')))
        AND DMELEMENTNVP.NAME = 'emsId'
        AND DMELEMENTNVP.VALUE = (SELECT EMSPOT_ID FROM EMSPOT WHERE USAGETYPE = 'LAYOUTMARKETINGSPOT' AND NAME = 'HomePage' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'))
        AND (DMELEMENTNVP.OPTCOUNTER IS NULL OR DMELEMENTNVP.OPTCOUNTER = 0);
        
UPDATE DMACTIVITY SET PUBLISHED = 1, OPTCOUNTER = 1
    WHERE DMACTIVITY.DMACTIVITY_ID = (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'))
        AND (DMACTIVITY.OPTCOUNTER IS NULL OR DMACTIVITY.OPTCOUNTER = 0);

UPDATE DMACTIVITY SET STATE = 1, LASTUPDATE = CURRENT TIMESTAMP, LASTUPDATEDBY = (SELECT 'DM_MbrId=' || U.USERS_ID || '&DM_StoreId=' || (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') || '&DM_PznId=' || U.PERSONALIZATIONID FROM USERS AS U JOIN USERREG AS R ON (U.USERS_ID = R.USERS_ID) WHERE R.LOGONID = '{LOGON_ID}'), OPTCOUNTER = 2, BEHAVIOR = 0
    WHERE DMACTIVITY.DMACTIVITY_ID = (SELECT DMACTIVITY_ID FROM DMACTIVITY WHERE NAME = '{DMACTIVITY_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'))
        AND DMACTIVITY.OPTCOUNTER = 1;