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}')
);