eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.IBW_OE_PVT dependencies on IBW_PAGE_VIEWS

Line 59: l_ref_url IBW_PAGE_VIEWS.REFERRAL_URL%TYPE;

55: l_error_message VARCHAR2(240);
56: l_index_tablespace VARCHAR2(250);
57:
58: x_ref_id NUMBER(30);
59: l_ref_url IBW_PAGE_VIEWS.REFERRAL_URL%TYPE;
60: l_ref_name ibw_referral_categories_tl.REFERRAL_CATEGORY_NAME%TYPE;
61:
62:
63:

Line 72: FROM ibw_page_views_tmp tmp

68:
69:
70: CURSOR tmp_page_views_instances IS
71: SELECT DISTINCT page_id, business_context, business_context_value
72: FROM ibw_page_views_tmp tmp
73: WHERE page_instance_id = -1
74: AND process_flag is null
75: AND page_id <> -1
76: AND not exists ( SELECT page_instance_id

Line 88: FROM ibw_page_views_tmp

84: SELECT rec_id, page_id, page_view_seq_num, site_id, visit_id, evnt_type,
85: evnt_id, tracked_page_code, tracked_page_name, tracked_page_url,
86: tracked_application_context, business_context, business_context_value,
87: search_phrase,search_result_size,EXACT_RESULT_SIZE_FLAG,referral_url
88: FROM ibw_page_views_tmp
89: WHERE process_flag = flag;
90:
91:
92: CURSOR page_views_new_referral_cat IS

Line 94: FROM ibw_page_views_tmp

90:
91:
92: CURSOR page_views_new_referral_cat IS
93: SELECT distinct referral_url
94: FROM ibw_page_views_tmp
95: WHERE page_view_seq_num = 1
96: AND process_flag is null
97: AND referral_URL is not null
98: AND length(referral_URL) <> 0

Line 111: FROM ibw_page_views_tmp tmp

107: SELECT pages.page_id as page_id
108: FROM ibw_pages_b pages
109: WHERE pages.page_status = 'N'
110: AND exists (SELECT tmp.page_id
111: FROM ibw_page_views_tmp tmp
112: WHERE tmp.page_id = pages.page_id);
113:
114:
115:

Line 206: -- Porcess express check out cache stored in ibw_page_views_tmp

202:
203:
204:
205:
206: -- Porcess express check out cache stored in ibw_page_views_tmp
207:
208:
209: printLog('Clean up Page views temp table');
210:

Line 211: DELETE FROM ibw_page_views_tmp

207:
208:
209: printLog('Clean up Page views temp table');
210:
211: DELETE FROM ibw_page_views_tmp
212: WHERE process_flag is null;
213:
214: printLog('Processing Express Checkout cache stored in ibw_page_views_tmp');
215: FOR page_view IN tmp_page_views_cache(5)

Line 214: printLog('Processing Express Checkout cache stored in ibw_page_views_tmp');

210:
211: DELETE FROM ibw_page_views_tmp
212: WHERE process_flag is null;
213:
214: printLog('Processing Express Checkout cache stored in ibw_page_views_tmp');
215: FOR page_view IN tmp_page_views_cache(5)
216: LOOP
217:
218: SELECT COUNT(order_id)

Line 224: UPDATE ibw_page_views_tmp tmp

220: FROM aso_quote_headers_all
221: WHERE quote_header_id = SUBSTR(page_view.evnt_id,0,INSTR(page_view.evnt_id,',NOORDER')-1); --Changed by Sanjay. Changed NULL to NOORDER
222: IF l_rec_count > 0
223: THEN
224: UPDATE ibw_page_views_tmp tmp
225: SET tmp.evnt_id =
226: (SELECT quote_header_id|| ','|| NVL(order_id,'NULL')
227: FROM aso_quote_headers_all
228: WHERE quote_header_id = SUBSTR(tmp.evnt_id,0,INSTR(tmp.evnt_id,',NOORDER')-1)) --Changed by Sanjay. Changed NULL to NOORDER

Line 233: UPDATE ibw_page_views_tmp tmp

229: ,tmp.evnt_type=l_xchkout_code
230: ,tmp.process_flag = null
231: WHERE tmp.rec_id = page_view.rec_id;
232: ELSE
233: UPDATE ibw_page_views_tmp tmp
234: SET tmp.evnt_id =
235: (SELECT quote_header_id
236: FROM aso_quote_headers_all
237: WHERE quote_header_id = SUBSTR(tmp.evnt_id,0,INSTR(tmp.evnt_id,',NOORDER')-1)) --Changed by Sanjay. Changed NULL to NOORDER

Line 259: INSERT INTO ibw_page_views_tmp

255:
256:
257:
258:
259: INSERT INTO ibw_page_views_tmp
260: ( rec_id
261: , page_view_seq_num
262: , page_instance_id
263: , visit_id

Line 302: SELECT ibw_page_views_s1.NEXTVAL

298: , program_application_id
299: , request_id
300: , process_flag
301: )
302: SELECT ibw_page_views_s1.NEXTVAL
303: , seqnum
304: , -1 AS page_instance_id -- Set page instance id as -1 for processing it again later
305: , visitid
306: , pageviewstarttime

Line 862: INSERT INTO ibw_page_views_tmp

858: -- they will be processed in next run
859:
860: printLog('Migrating Processing iStore pageviews FROM PAT schema into temp table');
861:
862: INSERT INTO ibw_page_views_tmp
863: ( rec_id
864: , page_view_seq_num
865: , page_instance_id
866: , visit_id

Line 905: SELECT ibw_page_views_s1.NEXTVAL

901: , program_application_id
902: , request_id
903: , process_flag
904: )
905: SELECT ibw_page_views_s1.NEXTVAL
906: , seqnum
907: , -1
908: , visitid
909: , SYSDATE

Line 1152: --Get all page views from ibw_page_views for the visits in current run of offline engine

1148: AND pv.visitorid NOT IN ( -1, 0 );
1149:
1150:
1151:
1152: --Get all page views from ibw_page_views for the visits in current run of offline engine
1153: printLog('Get all page views for visits split across offline engine runs');
1154: INSERT INTO ibw_page_views_tmp( rec_id,
1155: PAGE_VIEW_SEQ_NUM,
1156: PAGE_INSTANCE_ID,

Line 1154: INSERT INTO ibw_page_views_tmp( rec_id,

1150:
1151:
1152: --Get all page views from ibw_page_views for the visits in current run of offline engine
1153: printLog('Get all page views for visits split across offline engine runs');
1154: INSERT INTO ibw_page_views_tmp( rec_id,
1155: PAGE_VIEW_SEQ_NUM,
1156: PAGE_INSTANCE_ID,
1157: VISIT_ID,
1158: PAGE_VIEW_START_TIME,

Line 1227: FROM ibw_page_views pv

1223: fnd_global.user_id,
1224: SYSDATE,
1225: fnd_global.user_id,
1226: last_update_date
1227: FROM ibw_page_views pv
1228: WHERE exists (SELECT tmp.rec_id
1229: FROM ibw_page_views_tmp tmp
1230: WHERE pv.visit_id = tmp.visit_id
1231: AND (process_flag <> 5 or process_flag is null));

Line 1229: FROM ibw_page_views_tmp tmp

1225: fnd_global.user_id,
1226: last_update_date
1227: FROM ibw_page_views pv
1228: WHERE exists (SELECT tmp.rec_id
1229: FROM ibw_page_views_tmp tmp
1230: WHERE pv.visit_id = tmp.visit_id
1231: AND (process_flag <> 5 or process_flag is null));
1232:
1233: DELETE FROM ibw_page_views pv

Line 1233: DELETE FROM ibw_page_views pv

1229: FROM ibw_page_views_tmp tmp
1230: WHERE pv.visit_id = tmp.visit_id
1231: AND (process_flag <> 5 or process_flag is null));
1232:
1233: DELETE FROM ibw_page_views pv
1234: WHERE exists (SELECT tmp.rec_id
1235: FROM ibw_page_views_tmp tmp
1236: WHERE pv.visit_id = tmp.visit_id
1237: AND (process_flag <> 5 or process_flag is null));

Line 1235: FROM ibw_page_views_tmp tmp

1231: AND (process_flag <> 5 or process_flag is null));
1232:
1233: DELETE FROM ibw_page_views pv
1234: WHERE exists (SELECT tmp.rec_id
1235: FROM ibw_page_views_tmp tmp
1236: WHERE pv.visit_id = tmp.visit_id
1237: AND (process_flag <> 5 or process_flag is null));
1238:
1239: -- Create Pages IF required for all pages with page id -1

Line 1248: UPDATE ibw_page_views_tmp

1244: IF (l_page_id = -1) THEN
1245: printLog('Cannot create a new page for visit_id :' || page_view.visit_id || ' and seq num :' || page_view.page_view_seq_num );
1246: ELSE
1247:
1248: UPDATE ibw_page_views_tmp
1249: SET page_id = l_page_id ,process_flag = null
1250: WHERE rec_id = page_view.rec_id;
1251: END IF;
1252: END LOOP;

Line 1260: FROM ibw_page_views_tmp

1256: -- populate reference column for exisiting pages
1257:
1258: UPDATE ibw_pages_b pag
1259: SET REFERENCE = ( SELECT tracked_page_url
1260: FROM ibw_page_views_tmp
1261: WHERE page_id = pag.page_id and rownum = 1) -- Changed by Venky. Added rownum=1 because select query could return more than 1 row
1262: WHERE REFERENCE IS NULL
1263: AND EXISTS ( SELECT 'x' -- Changed by Venky. Replaces 'IN' with 'EXISTS'
1264: FROM ibw_page_views_tmp tmp

Line 1264: FROM ibw_page_views_tmp tmp

1260: FROM ibw_page_views_tmp
1261: WHERE page_id = pag.page_id and rownum = 1) -- Changed by Venky. Added rownum=1 because select query could return more than 1 row
1262: WHERE REFERENCE IS NULL
1263: AND EXISTS ( SELECT 'x' -- Changed by Venky. Replaces 'IN' with 'EXISTS'
1264: FROM ibw_page_views_tmp tmp
1265: WHERE tmp.page_id=pag.page_id)
1266: AND application_context = 'N';
1267:
1268: -- Populate page instance table

Line 1284: UPDATE ibw_page_views_tmp pv

1280: ,error_messages);
1281:
1282: END LOOP;
1283:
1284: UPDATE ibw_page_views_tmp pv
1285: SET pv.page_instance_id =
1286: NVL ( ( SELECT pi.page_instance_id
1287: FROM ibw_page_instances pi
1288: WHERE pi.page_id = pv.page_id

Line 1298: FROM ibw_page_views_tmp

1294:
1295: printLog('Getting Page views COUNT');
1296: SELECT COUNT(rec_id)
1297: INTO l_page_view_count
1298: FROM ibw_page_views_tmp
1299: WHERE page_id NOT IN ( -2, -3, -1 )
1300: AND page_id IS NOT NULL
1301: AND process_flag is null;
1302:

Line 1305: UPDATE ibw_page_views_tmp tmp

1301: AND process_flag is null;
1302:
1303: printLog('Process login events into page views tmp table');
1304:
1305: UPDATE ibw_page_views_tmp tmp
1306: SET (visitant_id,user_id ,party_id) =
1307: (SELECT visitant_id
1308: , user_id , party_id
1309: FROM ibw_page_views_tmp

Line 1309: FROM ibw_page_views_tmp

1305: UPDATE ibw_page_views_tmp tmp
1306: SET (visitant_id,user_id ,party_id) =
1307: (SELECT visitant_id
1308: , user_id , party_id
1309: FROM ibw_page_views_tmp
1310: WHERE process_flag = 8 AND visit_id = tmp.visit_id AND ROWNUM=1 )
1311: WHERE exists
1312: (SELECT 'x'
1313: FROM ibw_page_views_tmp

Line 1313: FROM ibw_page_views_tmp

1309: FROM ibw_page_views_tmp
1310: WHERE process_flag = 8 AND visit_id = tmp.visit_id AND ROWNUM=1 )
1311: WHERE exists
1312: (SELECT 'x'
1313: FROM ibw_page_views_tmp
1314: WHERE visit_id = tmp.visit_id
1315: AND process_flag=8);
1316:
1317: printLog('Updating vistant Id,user_id,party_id for missing login events');

Line 1318: UPDATE ibw_page_views_tmp tmp

1314: WHERE visit_id = tmp.visit_id
1315: AND process_flag=8);
1316:
1317: printLog('Updating vistant Id,user_id,party_id for missing login events');
1318: UPDATE ibw_page_views_tmp tmp
1319: SET (visitant_id,user_id ,party_id) =
1320: (SELECT visitant_id
1321: , user_id
1322: , party_id

Line 1323: FROM ibw_page_views_tmp

1319: SET (visitant_id,user_id ,party_id) =
1320: (SELECT visitant_id
1321: , user_id
1322: , party_id
1323: FROM ibw_page_views_tmp
1324: WHERE visit_id = tmp.visit_id AND ROWNUM=1 AND user_id <> l_guest_user_id)
1325: WHERE (SELECT count(distinct user_id)
1326: FROM ibw_page_views_tmp
1327: WHERE visit_id = tmp.visit_id ) > 1;

Line 1326: FROM ibw_page_views_tmp

1322: , party_id
1323: FROM ibw_page_views_tmp
1324: WHERE visit_id = tmp.visit_id AND ROWNUM=1 AND user_id <> l_guest_user_id)
1325: WHERE (SELECT count(distinct user_id)
1326: FROM ibw_page_views_tmp
1327: WHERE visit_id = tmp.visit_id ) > 1;
1328:
1329:
1330:

Line 1342: FROM ibw_page_views_tmp tmp

1338: FOR page_view IN tmp_page_views_cache(-4)
1339: LOOP
1340: SELECT COUNT(rec_id) --Changed by Venky. Removed count(*)
1341: INTO l_rec_count
1342: FROM ibw_page_views_tmp tmp
1343: WHERE tmp.visit_id = page_view.visit_id
1344: AND tmp.page_view_seq_num = page_view.page_view_seq_num;
1345:
1346: IF l_rec_count > 0 THEN

Line 1347: UPDATE ibw_page_views_tmp tmp

1343: WHERE tmp.visit_id = page_view.visit_id
1344: AND tmp.page_view_seq_num = page_view.page_view_seq_num;
1345:
1346: IF l_rec_count > 0 THEN
1347: UPDATE ibw_page_views_tmp tmp
1348: SET tmp.evnt_id = page_view.evnt_id
1349: ,tmp.evnt_type=page_view.evnt_type
1350: ,tmp.search_phrase=page_view.search_phrase
1351: ,tmp.search_result_size = page_view.search_result_size

Line 1355: DELETE FROM ibw_page_views_tmp

1351: ,tmp.search_result_size = page_view.search_result_size
1352: ,tmp.EXACT_RESULT_SIZE_FLAG = page_view.EXACT_RESULT_SIZE_FLAG
1353: WHERE tmp.visit_id = page_view.visit_id AND tmp.page_view_seq_num = page_view.page_view_seq_num;
1354: ELSE
1355: DELETE FROM ibw_page_views_tmp
1356: WHERE rec_id = page_view.rec_id;
1357: END IF;
1358: END LOOP;
1359:

Line 1366: FROM ibw_page_views_tmp tmp

1362: FOR page_view IN tmp_page_views_cache(4)
1363: LOOP
1364: SELECT COUNT(rec_id) --Changed by Venky. Removed count(*)
1365: INTO l_rec_count
1366: FROM ibw_page_views_tmp tmp
1367: WHERE tmp.visit_id = page_view.visit_id
1368: AND tmp.page_view_seq_num = page_view.page_view_seq_num;
1369:
1370: IF l_rec_count > 0 THEN

Line 1371: UPDATE ibw_page_views_tmp tmp

1367: WHERE tmp.visit_id = page_view.visit_id
1368: AND tmp.page_view_seq_num = page_view.page_view_seq_num;
1369:
1370: IF l_rec_count > 0 THEN
1371: UPDATE ibw_page_views_tmp tmp
1372: SET tmp.evnt_id = page_view.evnt_id
1373: ,tmp.evnt_type=page_view.evnt_type
1374: ,tmp.search_phrase=page_view.search_phrase
1375: ,tmp.search_result_size = page_view.search_result_size

Line 1380: UPDATE ibw_page_views_tmp

1376: ,tmp.EXACT_RESULT_SIZE_FLAG = page_view.EXACT_RESULT_SIZE_FLAG
1377: WHERE tmp.visit_id = page_view.visit_id
1378: AND tmp.page_view_seq_num = page_view.page_view_seq_num;
1379: ELSE
1380: UPDATE ibw_page_views_tmp
1381: SET process_flag =-4
1382: WHERE rec_id = page_view.rec_id;
1383: END IF;
1384: END LOOP;

Line 1388: UPDATE ibw_page_views_tmp pv

1384: END LOOP;
1385:
1386: -- Repopulate all campaign ids to all page views in a visit
1387: printLog('Repopulate all campaign ids to all page views in a visit');
1388: UPDATE ibw_page_views_tmp pv
1389: SET pv.campaign_source_code_id =
1390: NVL ( ( SELECT max(tmp.campaign_source_code_id)
1391: FROM ibw_page_views_tmp tmp
1392: WHERE tmp.visit_id = pv.visit_id), NULL )

Line 1391: FROM ibw_page_views_tmp tmp

1387: printLog('Repopulate all campaign ids to all page views in a visit');
1388: UPDATE ibw_page_views_tmp pv
1389: SET pv.campaign_source_code_id =
1390: NVL ( ( SELECT max(tmp.campaign_source_code_id)
1391: FROM ibw_page_views_tmp tmp
1392: WHERE tmp.visit_id = pv.visit_id), NULL )
1393: WHERE EXISTS (
1394: SELECT 'x'
1395: FROM ibw_page_views_tmp tmp2

Line 1395: FROM ibw_page_views_tmp tmp2

1391: FROM ibw_page_views_tmp tmp
1392: WHERE tmp.visit_id = pv.visit_id), NULL )
1393: WHERE EXISTS (
1394: SELECT 'x'
1395: FROM ibw_page_views_tmp tmp2
1396: WHERE tmp2.process_flag is null
1397: AND tmp2.visit_id = pv.visit_id
1398: AND tmp2.campaign_source_code_id is not null);
1399:

Line 1446: INSERT INTO ibw_page_views

1442: -- Populate page views table FROM temp table.
1443:
1444: printLog('Populating Page views table');
1445:
1446: INSERT INTO ibw_page_views
1447: ( page_view_id
1448: , page_view_seq_num
1449: , page_instance_id
1450: , visit_id

Line 1528: FROM ibw_page_views_tmp

1524: , fnd_global.conc_program_id
1525: , fnd_global.conc_login_id
1526: , fnd_global.prog_appl_id
1527: , fnd_global.conc_request_id
1528: FROM ibw_page_views_tmp
1529: WHERE page_id NOT IN ( -2, -3, -1 )
1530: AND page_id IS NOT NULL
1531: AND process_flag is NULL
1532: );

Line 1537: FROM ibw_page_views_tmp

1533:
1534:
1535:
1536: DELETE
1537: FROM ibw_page_views_tmp
1538: WHERE process_flag IN (4, 8,5,9); --Changed by Venky. Removed one delete
1539:
1540:
1541:

Line 1573: FROM ibw_page_views_tmp

1569: AND visit_start_time < innerquery.visit_start_time
1570: AND ROWNUM = 1
1571: )
1572: ,0, DECODE ((SELECT COUNT (rec_id)
1573: FROM ibw_page_views_tmp
1574: WHERE visitor_id = innerquery.visitor_id
1575: AND visit_id <> innerquery.visit_id
1576: AND page_view_start_time < innerquery.visit_start_time
1577: AND ROWNUM = 1

Line 1591: FROM ibw_page_views_tmp

1587: AND visit_start_time < innerquery.visit_start_time
1588: AND ROWNUM = 1
1589: )
1590: ,0, DECODE ((SELECT COUNT (rec_id)
1591: FROM ibw_page_views_tmp
1592: WHERE party_id = innerquery.party_id
1593: AND visit_id <> innerquery.visit_id
1594: AND page_view_start_time < innerquery.visit_start_time
1595: AND ROWNUM = 1

Line 1608: FROM ibw_page_views_tmp pv

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
1611: ) PV
1612: WHERE patterns.TYPE = 'R'

Line 1661: FROM ibw_page_views_tmp pv

1657: , COUNT ( DECODE ( pv.evnt_type
1658: , l_invinq_code , 1
1659: , NULL
1660: )) total_invoice_inquiries
1661: FROM ibw_page_views_tmp pv
1662: WHERE pv.page_id NOT IN ( -1, -2 )
1663: AND pv.page_id IS NOT NULL
1664: AND exists (SELECT 'x'
1665: FROM ibw_page_views_tmp tmp

Line 1665: FROM ibw_page_views_tmp tmp

1661: FROM ibw_page_views_tmp pv
1662: WHERE pv.page_id NOT IN ( -1, -2 )
1663: AND pv.page_id IS NOT NULL
1664: AND exists (SELECT 'x'
1665: FROM ibw_page_views_tmp tmp
1666: WHERE process_flag is null
1667: AND tmp.visit_id=pv.visit_id)
1668: GROUP BY pv.visit_id
1669: ,pv.site_id

Line 1734: FROM ibw_page_views_tmp;

1730: printLog('Mark PAT records for purging');
1731:
1732: SELECT MAX(last_update_date)
1733: INTO l_pat_date
1734: FROM ibw_page_views_tmp;
1735:
1736:
1737: printLog('Got Pat date from Tmp'||l_pat_date);
1738:

Line 1742: FROM ibw_page_views;

1738:
1739: IF l_pat_date IS NULL THEN
1740: SELECT MAX(last_update_date)
1741: INTO l_pat_date
1742: FROM ibw_page_views;
1743:
1744: printLog('Got Pat date from page views'||l_pat_date);
1745: END IF;
1746:

Line 1842: DELETE FROM ibw_page_views_tmp

1838: END LOOP;
1839:
1840:
1841: -- Remove all page views to be ignored
1842: DELETE FROM ibw_page_views_tmp
1843: WHERE process_flag in (4,3,6,7) or process_flag is null;
1844:
1845:
1846:

Line 2151: FROM ibw_page_views_tmp pv

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
2154: ) PV
2155: WHERE patterns.TYPE = 'R'