Creating a WebSphere Commerce e-Marketing Spot w/SQL (Text Content)

The following SQL can be used to create a new e-marketing spot that is used to display text or other text-based creative, like HTML. If you’re looking for the SQL used to create an e-marketing spot that can be used to display image assets, you can reference the following article:

Creating a WebSphere Commerce E-Marketing Spot w/SQL (Image Assets)

The following tables are used or referenced when creating an e-marketing spot with the SQL shown below:

• COLLATERAL : This table contains data describing the advertisements used by marketing campaigns.
• STOREENT : Each row of this table represents a StoreEntity. A StoreEntity is an abstract superclass that can represent either a Store or a StoreGroup.
• COLLTYPE : This table defines all possible types of Collateral. Examples of Collateral types are image, flash and text.
• ESMAPOBJ : This table contains objects scheduled to E-Marketing Spot.
• ESMAPTYP : This table contains object types that can be scheduled to E-Marketing Spot.
• EMSPOT : This table contains registered e-Marketing Spots.
• INTVSCHED : This table describes the scheduling of campaign activities and content onto e-Marketing Spots.
• COLLDESC : This table holds language-dependent information related to a Collateral.
• LANGUAGE : Each row of this table represents a language. WebSphere Commerce supports multiple languages and is translated into ten languages by default. Using the predefined ISO codes users can add other supported languages.
• DMEMSPOTDEF : The default content to display in an e-Marketing Spot.

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

• {STORE_IDENTIFIER} : The STOREENT.IDENTIFIER column for the store you wish to associate this content/e-marketing spot to.
• {COLLATERAL_NAME} : The desired collateral name.
• {ESPOT_NAME} : The desired e-marketing spot name.
• {ESPOT_DESC} : The desired e-marketing spot description.
• {START_DATE} : The desired e-marketing spot start date.
• {END_DATE} : The desired e-marketing spot end date.
• {LOCALE_NAME} : The LANGUAGE.LOCALENAME column for the language you wish to use. (e.g. ‘en_US’)
• {MARKETING_TEXT} : The desired e-marketing spot text or creative.

A few notes regarding the SQL shown below. I always start database inserts, not handled through the WebSphere Commerce framework beginning at the number ‘-10001′. The SQL will automatically generate the appropriate primary key identifier for the following tables starting at -10001 and decrementing each subsequent insert by 1.

INSERT INTO COLLATERAL (COLLATERAL_ID, STOREENT_ID, COLLTYPE_ID, NAME, URL) VALUES (
    (SELECT CASE WHEN MIN(COLLATERAL_ID) > -10001 THEN -10001 ELSE MIN(COLLATERAL_ID) - 1 END FROM COLLATERAL),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    (SELECT COLLTYPE_ID FROM COLLTYPE WHERE NAME = 'Text'),
    '{COLLATERAL_NAME}',
    NULL
);

INSERT INTO ESMAPOBJ (ESMAPOBJ_ID, ESMAPTYP_ID, OBJECT_ID) VALUES (
    (SELECT CASE WHEN MIN(ESMAPOBJ_ID) > -10001 THEN -10001 ELSE MIN(ESMAPOBJ_ID) - 1 END FROM ESMAPOBJ),
    (SELECT ESMAPTYP_ID FROM ESMAPTYP WHERE NAME = 'General Content'),
    (SELECT COLLATERAL_ID FROM COLLATERAL WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{COLLATERAL_NAME}')
);

INSERT INTO EMSPOT (EMSPOT_ID, STOREENT_ID, NAME, DESCRIPTION, USAGETYPE) VALUES (
    (SELECT CASE WHEN MIN(EMSPOT_ID) > -10001 THEN -10001 ELSE MIN(EMSPOT_ID) - 1 END FROM EMSPOT),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    '{ESPOT_NAME}',
    '{ESPOT_DESC}',
    'MARKETING'
);

INSERT INTO INTVSCHED (INTVSCHED_ID, INITIATIVE_ID, EMSPOT_ID, STARTDATE, ENDDATE, PRIORITY, STOREENT_ID) VALUES (
    (SELECT CASE WHEN MIN(INTVSCHED_ID) > -10001 THEN -10001 ELSE MIN(INTVSCHED_ID) - 1 END FROM INTVSCHED),
    (SELECT ESMAPOBJ_ID FROM ESMAPOBJ WHERE OBJECT_ID = (SELECT COLLATERAL_ID FROM COLLATERAL WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{COLLATERAL_NAME}')),
    (SELECT EMSPOT_ID FROM EMSPOT WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{ESPOT_NAME}'),
    '{START_DATE}',
    '{END_DATE}',
    1,
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')
);

INSERT INTO COLLDESC (COLLATERAL_ID, LANGUAGE_ID, MARKETINGTEXT) VALUES (
    (SELECT COLLATERAL_ID FROM COLLATERAL WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{COLLATERAL_NAME}'),
    (SELECT LANGUAGE_ID FROM LANGUAGE WHERE LOCALENAME = '{LOCALE_NAME}'),
    '{MARKETING_TEXT}'
);

INSERT INTO DMEMSPOTDEF (DMEMSPOTDEF_ID, EMSPOT_ID, STOREENT_ID, CONTENTTYPE, CONTENT) VALUES (
    (SELECT CASE WHEN MIN(DMEMSPOTDEF_ID) > -10001 THEN -10001 ELSE MIN(DMEMSPOTDEF_ID) - 1 END FROM DMEMSPOTDEF),
    (SELECT EMSPOT_ID FROM EMSPOT WHERE NAME = '{ESPOT_NAME}' AND STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}')),
    (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}'),
    'MarketingContent',
    (SELECT COLLATERAL_ID FROM COLLATERAL WHERE STOREENT_ID = (SELECT STOREENT_ID FROM STOREENT WHERE IDENTIFIER = '{STORE_IDENTIFIER}') AND NAME = '{COLLATERAL_NAME}')
);