97: AND referral_URL is not null
98: AND length(referral_URL) <> 0
99: AND not exists
100: (SELECT patterns.type_id
101: FROM ibw_url_patterns_b patterns
102: WHERE patterns.TYPE = 'R'
103: AND UPPER(referral_url) LIKE
104: UPPER(REPLACE (patterns.url_pattern, '*', '%') || '%' ));
105:
422: , ( SELECT DECODE( pat.attribute16
423: ,'I', NVL ( sites1.msite_id, -1 ) --Put site id as -1 if not there, for processing later
424: ,'E', NVL ( sites2.msite_id -- If doesnt have site id , resolve it.
425: , NVL( ( SELECT type_id -- Match the URL stripping query string with all the url_pattern and take the site id
426: FROM ibw_url_patterns_b
427: WHERE TYPE = 'S'
428: AND ROWNUM = 1
429: AND SUBSTR ( pat.attribute24
430: , 1
979: , DECODE( pat.attribute16
980: ,'I', NVL ( sites1.msite_id, -1 ) --Put site id as -1 if not there, for processing later
981: ,'E', NVL ( sites2.msite_id -- If doesnt have site id , resolve it.
982: , NVL( ( SELECT type_id -- Match the URL stripping query string with all the url_pattern and take the site id
983: FROM ibw_url_patterns_b
984: WHERE TYPE = 'S'
985: AND SUBSTR ( pat.attribute24
986: , 1
987: , DECODE( INSTR( pat.attribute24
1404: l_ref_url := page_view.referral_url;
1405: printLog('Creating Referral Category for the URL:' ||l_ref_url);
1406: BEGIN
1407: SELECT patterns.type_id INTO x_ref_id
1408: FROM ibw_url_patterns_b patterns
1409: WHERE patterns.TYPE = 'R'
1410: AND UPPER(l_ref_url) LIKE
1411: UPPER(REPLACE (patterns.url_pattern, '*', '%') || '%' )
1412: AND rownum =1
1602: ) as repeat_visit_flag
1603: , NVL((SELECT type_id
1604: FROM (SELECT patterns.type_id
1605: ,VISIT_ID, patterns.program_id ,patterns.url_pattern ,patterns.creation_date
1606: FROM ibw_url_patterns_b patterns
1607: ,(SELECT upper(pv.referral_url) URL ,VISIT_ID
1608: FROM ibw_page_views_tmp pv
1609: WHERE pv.page_view_seq_num = 1
1610: AND process_flag is null
2145: SET tmp.referral_category_id =
2146: NVL((SELECT type_id
2147: FROM (SELECT patterns.type_id
2148: ,VISIT_ID, patterns.program_id ,patterns.url_pattern ,patterns.creation_date
2149: FROM ibw_url_patterns_b patterns
2150: ,(SELECT upper(pv.referral_url) URL ,VISIT_ID
2151: FROM ibw_page_views_tmp pv
2152: WHERE pv.page_view_seq_num = 1
2153: AND process_flag is null
2164: )
2165: ,-1)
2166: WHERE tmp.last_update_date >
2167: ( SELECT patterns.last_update_date
2168: FROM ibw_url_patterns_b patterns
2169: WHERE patterns.TYPE = 'R'
2170: AND patterns.type_id = tmp.referral_category_id );
2171:
2172: