Creating views as per audience levels for Unica InteractDT

When you create audience in Products, you must create views as per audience level.

Note: All views created must be for multiple audience support.

UARI_ICP_IPCH_ALL   =UARI_ICP_IPCH + UARI_ICP_IPCH_<Audience> 
UARI_ICP_IPRH_DY_ALL =UARI_ICP_IPRH_DY + UARI_ICP_IPRH_DY_<Audience>
UARI_ICP_IPCH_DY_ALL =UARI_ICP_IPCH_DY + UARI_ICP_IPCH_DY_<Audience>

<Audience> replace this with Audience created.
<AudienceId> replace this with Audience ID field name.
<ACCT_UA_DTlContactHist>  replace this with table of that audience level

`UA_DTlContactHist-customer`

CREATE OR REPLACE VIEW UARI_ICP_IPCH_<Audience> AS ( SELECT DISTINCT InteractiveChannel.ICID AS ICID, InteractiveChannel.ICName AS ICNAME, IPID_TABLE.IPID AS IPID, IPNAME_TABLE.IPNAME AS IPNAME, COUNT(ACCT_UA_DTlContactHist.<AudienceId>) AS NUM_TIMES_PRES, COUNT( CASE WHEN ACCT_UA_DTlContactHist.RTSelectionMethod > 1 THEN ACCT_UA_DTlContactHist.<AudienceId> END ) AS NUM_PRES_MODELDRVN, COUNT( CASE WHEN ACCT_UA_DTlContactHist.RTSelectionMethod = 1 THEN ACCT_UA_DTlContactHist.<AudienceId> END ) AS NUM_PRES_RANDOM FROM UA_Treatment, ACCT_UA_DTLCONTACTHIST, UA_ContactStatus, ( SELECT DISTINCT UA_Treatment.OfferHistoryID, NumberValue AS IPID FROM UA_Treatment LEFT OUTER JOIN UA_OfferHistAttrib ON UA_Treatment.OfferHistoryID = UA_OfferHistAttrib.OfferHistoryID AND UA_OfferHistAttrib.AttributeID = 33 WHERE UA_OfferHistAttrib.NumberValue IS NOT NULL ) IPID_TABLE, ( SELECT DISTINCT UA_Treatment.OfferHistoryID, UA_OfferHistAttrib.StringValue AS IPNAME FROM UA_Treatment LEFT OUTER JOIN UA_OfferHistAttrib ON UA_Treatment.OfferHistoryID = UA_OfferHistAttrib.OfferHistoryID AND UA_OfferHistAttrib.AttributeID = 34 WHERE UA_OfferHistAttrib.StringValue IS NOT NULL ) IPNAME_TABLE, ( SELECT DISTINCT UACI_TrmtRuleInv.ICID, UACI_TrmtRuleInv.ICName, UACI_TrmtRuleInv.OfferID, UACI_TrmtRuleInv.CellID FROM UACI_TrmtRuleInv, UACI_Deployment WHERE UACI_TrmtRuleInv.DeploymentID = UACI_Deployment.DeploymentID AND UACI_Deployment.DeploymentStatusID = 3 AND UACI_Deployment.IsProduction = 1 ) InteractiveChannel WHERE UA_Treatment.HasDetailHistory = 1 AND UA_Treatment.TreatmentInstID = ACCT_UA_DTLCONTACTHIST.TreatmentInstID AND UA_ContactStatus.CountsAsContact = 1 AND ACCT_UA_DTLCONTACTHIST.ContactStatusID = UA_ContactStatus.ContactStatusID AND InteractiveChannel.OfferID = UA_Treatment.OfferID AND InteractiveChannel.CellID = UA_Treatment.CellID AND UA_Treatment.OfferHistoryID = IPID_TABLE.OfferHistoryID AND UA_Treatment.OfferHistoryID = IPNAME_TABLE.OfferHistoryID GROUP BY InteractiveChannel.ICID, InteractiveChannel.ICName, IPID_TABLE.IPID, IPNAME_TABLE.IPNAME );

CREATE OR REPLACE VIEW UARI_ICP_IPRH_DY_<Audience>  AS ( SELECT DISTINCT InteractiveChannel.ICID AS ICID, InteractiveChannel.ICName AS ICNAME, IPID_TABLE.IPID AS IPID, IPNAME_TABLE.IPNAME AS IPNAME, UA_Calendar.ActualDate AS ACTUALDATE, COUNT( CASE WHEN UA_UsrResponseType.CountsAsResponse = 1 THEN ACCT_UA_ResponseHistory.<AudienceId> END ) AS NUM_TIMES_ACCP, COUNT( CASE WHEN UA_UsrResponseType.CountsAsResponse = 2 THEN ACCT_UA_ResponseHistory.<AudienceId> END ) AS NUM_TIME_REJ, COUNT( CASE WHEN UA_UsrResponseType.CountsAsResponse = 1 AND ACCT_UA_ResponseHistory.RTSelectionMethod > 1 THEN ACCT_UA_ResponseHistory.<AudienceId> END ) AS NUM_ACCP_MODELDRVN, COUNT( CASE WHEN UA_UsrResponseType.CountsAsResponse = 1 AND ACCT_UA_ResponseHistory.RTSelectionMethod = 1 THEN ACCT_UA_ResponseHistory.<AudienceId> END ) AS NUM_ACCP_RANDOM FROM UA_Treatment, ACCT_UA_ResponseHistory, UA_UsrResponseType, UA_Calendar, UA_Time, ( SELECT DISTINCT UA_Treatment.OfferHistoryID, NumberValue AS IPID FROM UA_Treatment LEFT OUTER JOIN UA_OfferHistAttrib ON UA_Treatment.OfferHistoryID = UA_OfferHistAttrib.OfferHistoryID AND UA_OfferHistAttrib.AttributeID = 33 WHERE UA_OfferHistAttrib.NumberValue IS NOT NULL ) IPID_TABLE, ( SELECT DISTINCT UA_Treatment.OfferHistoryID, StringValue AS IPNAME FROM UA_Treatment LEFT OUTER JOIN UA_OfferHistAttrib ON UA_Treatment.OfferHistoryID = UA_OfferHistAttrib.OfferHistoryID AND UA_OfferHistAttrib.AttributeID = 34 WHERE UA_OfferHistAttrib.StringValue IS NOT NULL ) IPNAME_TABLE, ( SELECT DISTINCT UACI_TrmtRuleInv.ICID, UACI_TrmtRuleInv.ICName, UACI_TrmtRuleInv.OfferID, UACI_TrmtRuleInv.CellID FROM UACI_TrmtRuleInv, UACI_Deployment WHERE UACI_TrmtRuleInv.DeploymentID = UACI_Deployment.DeploymentID AND UACI_Deployment.DeploymentStatusID = 3 AND UACI_Deployment.IsProduction = 1 ) InteractiveChannel WHERE UA_Treatment.TreatmentInstID = ACCT_UA_ResponseHistory.TreatmentInstID AND UA_Calendar.DateID = ACCT_UA_ResponseHistory.DateID AND UA_Time.TimeID = ACCT_UA_ResponseHistory.TimeID AND IPID_TABLE.OfferHistoryID = UA_Treatment.OfferHistoryID AND IPNAME_TABLE.OfferHistoryID = UA_Treatment.OfferHistoryID AND ACCT_UA_ResponseHistory.ResponseTypeID = UA_UsrResponseType.ResponseTypeID AND ACCT_UA_ResponseHistory.BestAttrib = 1 AND InteractiveChannel.OfferID = UA_Treatment.OfferID AND InteractiveChannel.CellID = UA_Treatment.CellID GROUP BY InteractiveChannel.ICID, InteractiveChannel.ICName, IPID_TABLE.IPID, IPNAME_TABLE.IPNAME, UA_Calendar.ActualDate );

CREATE OR REPLACE VIEW UARI_ICP_IPCH_DY_<Audience>  AS ( SELECT DISTINCT InteractiveChannel.ICID AS ICID, InteractiveChannel.ICName AS ICNAME, IPID_TABLE.IPID AS IPID, IPNAME_TABLE.IPNAME AS IPNAME, UA_Calendar.ActualDate AS ACTUALDATE, COUNT(ACCT_UA_DtlContactHist.<AudienceId>) AS NUM_TIMES_PRES, COUNT( CASE WHEN ACCT_UA_DtlContactHist.RTSelectionMethod > 1 THEN ACCT_UA_DtlContactHist.<AudienceId> END ) AS NUM_PRES_MODELDRVN, COUNT( CASE WHEN ACCT_UA_DtlContactHist.RTSelectionMethod = 1 THEN ACCT_UA_DtlContactHist.<AudienceId> END ) AS NUM_PRES_RANDOM FROM UA_Treatment, ACCT_UA_DtlContactHist, UA_ContactStatus, UA_Calendar, UA_Time, ( SELECT DISTINCT UA_Treatment.OfferHistoryID, NumberValue AS IPID FROM UA_Treatment LEFT OUTER JOIN UA_OfferHistAttrib ON UA_Treatment.OfferHistoryID = UA_OfferHistAttrib.OfferHistoryID AND UA_OfferHistAttrib.AttributeID = 33 WHERE UA_OfferHistAttrib.NumberValue IS NOT NULL ) IPID_TABLE, ( SELECT DISTINCT UA_Treatment.OfferHistoryID, UA_OfferHistAttrib.StringValue AS IPNAME FROM UA_Treatment LEFT OUTER JOIN UA_OfferHistAttrib ON UA_Treatment.OfferHistoryID = UA_OfferHistAttrib.OfferHistoryID AND UA_OfferHistAttrib.AttributeID = 34 WHERE UA_OfferHistAttrib.StringValue IS NOT NULL ) IPNAME_TABLE, ( SELECT DISTINCT UACI_TrmtRuleInv.ICID, UACI_TrmtRuleInv.ICName, UACI_TrmtRuleInv.OfferID, UACI_TrmtRuleInv.CellID FROM UACI_TrmtRuleInv, UACI_Deployment WHERE UACI_TrmtRuleInv.DeploymentID = UACI_Deployment.DeploymentID AND UACI_Deployment.DeploymentStatusID = 3 AND UACI_Deployment.IsProduction = 1 ) InteractiveChannel WHERE UA_Treatment.HasDetailHistory = 1 AND UA_Treatment.TreatmentInstID = ACCT_UA_DtlContactHist.TreatmentInstID AND UA_ContactStatus.CountsAsContact = 1 AND ACCT_UA_DtlContactHist.ContactStatusID = UA_ContactStatus.ContactStatusID AND InteractiveChannel.OfferID = UA_Treatment.OfferID AND InteractiveChannel.CellID = UA_Treatment.CellID AND UA_Calendar.DateID = ACCT_UA_DtlContactHist.DateID AND UA_Time.TimeID = ACCT_UA_DtlContactHist.TimeID AND UA_Treatment.OfferHistoryID = IPID_TABLE.OfferHistoryID AND UA_Treatment.OfferHistoryID = IPNAME_TABLE.OfferHistoryID GROUP BY InteractiveChannel.ICID, InteractiveChannel.ICName, IPID_TABLE.IPID, IPNAME_TABLE.IPNAME, UA_Calendar.ActualDate );

If common views already exist, you must replace the common views:

CREATE OR REPLACE VIEW UARI_ICP_IPCH_ALL AS ( SELECT ICID, ICNAME, IPID, IPNAME, NUM_TIMES_PRES, NUM_PRES_MODELDRVN, NUM_PRES_RANDOM FROM UARI_ICP_IPCH UNION ALL SELECT ICID, ICNAME, IPID, IPNAME, NUM_TIMES_PRES, NUM_PRES_MODELDRVN, NUM_PRES_RANDOM FROM UARI_ICP_IPCH_<Audience> )
;

CREATE OR REPLACE VIEW UARI_ICP_IPRH_DY_ALL AS (SELECT ICID, IPID, NUM_TIMES_ACCP, NUM_ACCP_MODELDRVN, NUM_ACCP_RANDOM, NUM_TIME_REJ, ACTUALDATE FROM UARI_ICP_IPRH_DY UNION ALL SELECT ICID, IPID, NUM_TIMES_ACCP, NUM_ACCP_MODELDRVN, NUM_ACCP_RANDOM, NUM_TIME_REJ, ACTUALDATE FROM UARI_ICP_IPRH_DY_<Audience>)
;

CREATE OR REPLACE VIEW UARI_ICP_IPCH_DY_ALL AS (SELECT ICID, IPID, ACTUALDATE, NUM_TIMES_PRES, NUM_PRES_MODELDRVN, NUM_PRES_RANDOM FROM UARI_ICP_IPCH_DY UNION ALL SELECT ICID, IPID, ACTUALDATE, NUM_TIMES_PRES, NUM_PRES_MODELDRVN, NUM_PRES_RANDOM FROM UARI_ICP_IPCH_DY_<Audience>)
;