4.0 to 5.0 Match Rule Queries Migration

Match Rules Queries - used to export the 4.0 match rules so they can be re-entered into the new 5.0 Rule Builders

-- Content Targeter - Cart Item Rule
select ct.CONTENT_TARGETER_ID, 'Content Targeter Cart Item Rule' as Type, ct.ARCHIVED,  crit.ORDER_ITEM_MATCH_RULE  
from BLC_CONTENT_TARGETER ct 
join BLC_CNTNT_TRGT_ITEM_CRIT crit on ct.CONTENT_TARGETER_ID = crit.CONTENT_TARGETER_ID;

-- Content Targeter - category, product, order, web request
select ct.CONTENT_TARGETER_ID, rules.MAP_KEY, ct.ARCHIVED, ctr.MATCH_RULE
from BLC_CONTENT_TARGETER ct 
join BLC_CNTNT_TRGT_RULES rules on ct.CONTENT_TARGETER_ID = rules.CONTENT_TARGETER_ID
join BLC_CONTENT_TARGET_RULE ctr on rules.CONTENT_TARGETING_RULE_ID = ctr.CONTENT_TARGET_RULE_ID;

-- offer qualifying criteria rules
select o.OFFER_ID, o.ARCHIVED AS OFFER_ARCHIVED, XREF.ARCHIVED AS QUAL_CRIT_ARCHIVED,o.OFFER_NAME, crit.ORDER_ITEM_MATCH_RULE 
from BLC_OFFER o 
join BLC_QUAL_CRIT_OFFER_XREF xref on o.OFFER_ID = xref.OFFER_ID
join BLC_OFFER_ITEM_CRITERIA crit on xref.OFFER_ITEM_CRITERIA_ID = crit.OFFER_ITEM_CRITERIA_ID;

-- offer target criteria rules
select o.OFFER_ID, o.ARCHIVED AS OFFER_ARCHIVED, XREF.ARCHIVED AS TAR_CRIT_ARCHIVED, o.OFFER_NAME, crit.ORDER_ITEM_MATCH_RULE 
from BLC_OFFER o 
join BLC_TAR_CRIT_OFFER_XREF xref on o.OFFER_ID = xref.OFFER_ID
join BLC_OFFER_ITEM_CRITERIA crit on xref.OFFER_ITEM_CRITERIA_ID = crit.OFFER_ITEM_CRITERIA_ID;

-- offer restriction and time rules
select o.OFFER_ID, o.OFFER_NAME, map.MAP_KEY, o.ARCHIVED, rule.MATCH_RULE 
from BLC_OFFER o
join BLC_OFFER_RULE_MAP map on o.OFFER_ID = map.BLC_OFFER_OFFER_ID
join BLC_OFFER_RULE rule on map.OFFER_RULE_ID = rule.OFFER_RULE_ID;

-- page item criteria
--select page.PAGE_ID, page.ARCHIVED, crit.ORDER_ITEM_MATCH_RULE 
--from BLC_PAGE page 
--join BLC_QUAL_CRIT_PAGE_XREF xref on page.PAGE_ID = xref.PAGE_ID
--join BLC_PAGE_ITEM_CRITERIA crit on xref.PAGE_ITEM_CRITERIA_ID = crit.PAGE_ITEM_CRITERIA_ID;

-- page rules
--select page.PAGE_ID, page.ARCHIVED, rule.MATCH_RULE 
--from BLC_PAGE page 
--join BLC_PAGE_RULE_MAP map on page.PAGE_ID = map.BLC_PAGE_PAGE_ID
--join BLC_PAGE_RULE rule on map.PAGE_RULE_ID = rule.PAGE_RULE_ID;

-- query to pull the price list rules
select pl.PRICE_LIST_ID, pl.ARCHIVED, pl.FRIENDLY_NAME, cr.MATCH_RULE 
from BLC_PRICE_LIST pl 
join BLC_PL_RULE_MAP rm on pl.PRICE_LIST_ID = rm.PRICE_LIST_ID 
join BLC_PL_CRITERIA_RULE cr on rm.PL_RULE_CRITERIA_ID = cr.PL_RULE_CRITERIA_ID;