[Home] [Help]
PACKAGE BODY: APPS.IBW_OE_PVT
Source
1 PACKAGE BODY IBW_OE_PVT AS
2 /* $Header: IBWOEB.pls 120.115 2008/06/20 09:48:14 saradhak ship $ */
3
4
5
6 --========================================================================
7 -- PROCEDURE : offline_engine PUBLIC
8 -- PARAMETERS: errbuf Error Buffer for Concurrent Program asset value
9 -- : retcode Return Code for Concurrent Program
10 -- COMMENT : Main Fact Population Program which migrates Data FROM Page Access Tracking
11 -- to Web Analytics schema
12 --========================================================================
13
14
15 PROCEDURE offline_engine(
16 errbuf OUT NOCOPY VARCHAR2,
17 retcode OUT NOCOPY NUMBER
18 ) AS
19 l_guest_user_pwd VARCHAR2(30);
20 l_guest_table_count PLS_INTEGER;
21 l_guest_username VARCHAR2(30);
22 error_messages VARCHAR2(240);
23 l_guest_party_id PLS_INTEGER;
24 l_guest_user_id PLS_INTEGER;
25 l_guest_person_id PLS_INTEGER;
26 l_visit_count PLS_INTEGER;
27 l_page_view_count PLS_INTEGER;
28
29 l_cart_code VARCHAR2(250);
30 l_xchkout_code VARCHAR2(250);
31 l_order_code VARCHAR2(250);
32 l_ordinq_code VARCHAR2(250);
33 l_invinq_code VARCHAR2(250);
34 l_payinq_code VARCHAR2(250);
35 l_userreg_code VARCHAR2(250);
36 l_optout_code VARCHAR2(250);
37
38
39 l_start_time date;
40 l_pat_date date;
41 --l_pat_3_date date;
42 --l_pat_4_date date;
43 l_pat_count PLS_INTEGER;
44 l_pat_3_count PLS_INTEGER;
45 l_pat_4_count PLS_INTEGER;
46 l_rec_count PLS_INTEGER;
47 l_page_id IBW_PAGES_B.PAGE_ID%TYPE;
48 l_page_name IBW_PAGES_TL.PAGE_NAME%TYPE;
49 l_ref_catg_id IBW_REFERRAL_CATEGORIES_B.REFERRAL_CATEGORY_ID%TYPE;
50 l_page_instance_id IBW_PAGE_INSTANCES.PAGE_INSTANCE_ID%TYPE;
51 l_return NUMBER(30);
52 l_message_text FND_NEW_MESSAGES.MESSAGE_TEXT%TYPE;
53 TEMP_CREATE_ERR EXCEPTION;
54
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
64 CURSOR pat_cursor IS
65 SELECT MAX (pat.last_update_date) AS patdate, pat.track_purpose
66 FROM jtf_pf_wa_info_vl pat
67 GROUP BY pat.track_purpose;
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
77 FROM ibw_page_instances
78 WHERE page_id = tmp.page_id
79 AND business_context=tmp.business_context
80 AND business_context_value = NVL(tmp.business_context_value,'-999'));
81
82
83 CURSOR tmp_page_views_cache(flag number) IS
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
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
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
106 CURSOR inactive_pages is
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
116 BEGIN
117
118 retcode := 0;
119
120 printLog('Starting Fact Population Concurrent Program');
121
122 -- Variable Initialization
123 l_cart_code := 'CART';
124 l_xchkout_code := 'XCHKOUT';
125 l_order_code := 'ORDER';
126 l_ordinq_code := 'ORDINQ';
127 l_invinq_code := 'INVINQ';
128 l_payinq_code := 'PMTINQ';
129 l_userreg_code := 'USRREG';
130 l_optout_code := 'OPTOUT';
131
132
133 --Bug 6727218
134 -- get guest party AND user ids
135 printLog('Getting Guest user name/Password');
136 --FND_PROFILE.GET ('GUEST_USER_PWD', l_guest_user_pwd);
137 l_guest_user_pwd:=FND_WEB_SEC.get_guest_username_pwd;
138
139 -- Get the user name out from the profile value which is in the format USERNAME/PASSWORD
140 l_guest_username := substr(l_guest_user_pwd,1,instr(l_guest_user_pwd,'/',1,1)-1);
141
142 SELECT customer_id,person_party_id,user_id
143 INTO l_guest_party_id,l_guest_person_id,l_guest_user_id
144 FROM fnd_user
145 WHERE user_name LIKE l_guest_username;
146
147 l_start_time := sysdate;
148
149 -- Insert record into ibw_guest_party table
150
151 printLog('Populating ibw_guest_party_table');
152 SELECT COUNT(PERSON_PARTY_ID) --Changed by Venky
153 INTO l_guest_table_count
154 FROM ibw_guest_party
155 WHERE rownum < 2;
156
157 IF l_guest_table_count = 0
158 THEN
159 INSERT INTO ibw_guest_party(CUSTOMER_ID
160 ,OBJECT_VERSION_NUMBER
161 ,PERSON_PARTY_ID
162 ,CREATED_BY
163 ,CREATION_DATE
164 ,LAST_updateD_BY
165 ,LAST_update_DATE
166 ,LAST_update_LOGIN)
167 VALUES(l_guest_party_id
168 ,1
169 ,l_guest_person_id
170 ,fnd_global.user_id
171 ,SYSDATE
172 ,fnd_global.user_id
173 ,SYSDATE,fnd_profile.VALUE('LOGIN_ID'));
174
175 ELSE
176 UPDATE ibw_guest_party
177 SET customer_id = l_guest_party_id
178 , person_party_id = l_guest_person_id;
179 END IF;
180
181 -- get Last migrated date FROM pat table.
182 printLog('Get last pat update date');
183
184 --Changes after code review to hold earliest possible date by default
185
186 BEGIN --Changed by Venky. Added code to handle no_data found
187
188 l_pat_date := TO_DATE('01/01/1970','MM/DD/YYYY');
189
190 SELECT last_record_migrated_time
191 INTO l_pat_date
192 FROM JTF_PF_PURGEABLE
193 WHERE track_purpose in (2,3,4)
194 AND ROWNUM = 1;
195
196 EXCEPTION
197 WHEN NO_DATA_FOUND THEN
198 l_pat_date := TO_DATE('01/01/1970','MM/DD/YYYY');
199
200 END;
201
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
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)
219 INTO l_rec_count
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
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
238 ,tmp.evnt_type=l_cart_code
239 ,tmp.process_flag = null
240 WHERE tmp.rec_id = page_view.rec_id;
241 END IF;
242 END LOOP;
243
244
245
246 -- Migrate display pages into page views temp table FROM the PAT Schema
247 -- Process Flag Explanation
248 -- Process_flag = null : Pages which are ready to be moved to page views
249 -- process_flag = 7 : Pages which have invalid context and are to be ignored
250 -- process_flag = 5 : Express check out page views which dont have order id yet,
251 -- they will be processed in next run
252
253 printLog('Migrating Display pageviews from PAT schema into temp table starting from '
254 || l_pat_date );
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
264 , page_view_start_time
265 , page_view_duration
266 , tracked_application_context
267 , tracked_site_code
268 , tracked_page_code
269 , tracked_page_name
270 , tracked_page_url
271 , search_result_size
272 , search_phrase
273 , exact_result_size_flag
274 , site_id
275 , page_id
276 , business_context_value
277 , business_context
278 , party_id
279 , visitor_id
280 , visitant_id
281 , evnt_type
282 , evnt_id
283 , campaign_source_code_id
284 , referral_url
285 , ip_address
286 , browser_os_info
287 , user_id
288 , user_guid
289 , party_relationship_id
290 , created_by
291 , creation_date
292 , last_updated_by
293 , last_update_date
294 , last_update_login
295 , object_version_number
296 , program_id
297 , program_login_id
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
307 , DURATION
308 , appctx
309 , tracked_site_code
310 , tracked_page_code
311 , tracked_page_name
312 , url
313 , srch_size
314 , srch_str
315 , srch_more
316 , site_id
317 , NVL ( pages.page_id, -1 ) AS page_id
318 , DECODE(pv.business_context_value
319 ,'-1',pv.attribute10
320 ,pv.business_context_value
321 )
322 , pv.business_context
323 , party_id
324 , visitorid
325 , DECODE ( user_id
326 , l_guest_user_id, NVL2 ( guid
327 , 'g'
328 || guid
329 , 'v'
330 || visitorid
331 )
332 , NULL, NVL2 ( guid
333 , 'g'
334 || guid
335 , 'v'
336 || visitorid
337 )
338 , NVL2 ( party_id
339 , 'p'
340 || party_id
341 , 'f'
342 || user_id
343 )
344 )
345 , event_code
346 , evnt_id
347 , campaign_source_code
348 , referrer
349 , ip_address
350 , useragent
351 , user_id
352 , guid
353 , DECODE(customer_id,party_id,null,customer_id) AS rel_id
354 , fnd_global.user_id
355 , pat_last_update_date
356 , fnd_global.user_id
357 , pat_last_update_date
358 , fnd_global.conc_login_id
359 , 1
360 , fnd_global.conc_program_id
361 , fnd_global.conc_login_id
362 , fnd_global.prog_appl_id
363 , fnd_global.conc_request_id
364 , DECODE ( INSTR ( evnt_id
365 , ',NULL'
366 , 1
367 , 1
368 )
369 , 0, DECODE ( business_context_value
370 , '-1', NVL2(page_id,7,DECODE(site_id
371 ,-2,3
372 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
373 ))
374 , '-999',DECODE(pages.business_context
375 ,'NONE',DECODE(site_id
376 ,-2,3
377 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
378 ) --Perf:63:Changed 1 to NULL by Sanjay
379 ,NULL,DECODE(site_id
380 ,-2,3
381 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
382 ) --Perf:63:Changed 1 to NULL by Sanjay
383 ,NVL2(page_id,6,DECODE(site_id
384 ,-2,3
385 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
386 )
387 ) --Perf:63:Changed 1 to NULL by Sanjay
388 )
389 ,DECODE(site_id
390 ,-2,3
391 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
392 )
393 )
394 , NULL, DECODE ( business_context_value
395 , '-1',NVL2(page_id,7,DECODE(site_id
396 ,-2,3
397 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
398 ))
399 , '-999',DECODE(pages.business_context
400 ,'NONE',DECODE(site_id
401 ,-2,3
402 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
403 ) --Perf:63:Changed 1 to NULL by Sanjay
404 ,NULL,DECODE(site_id
405 ,-2,3
406 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
407 ) --Perf:63:Changed 1 to NULL by Sanjay
408 ,NVL2(page_id,6,DECODE(site_id
409 ,-2,3
410 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
411 )
412 ) --Perf:63:Changed 1 to NULL by Sanjay
413 )
414 ,DECODE(site_id
415 ,-2,3
416 ,NVL2(page_id,NULL,9) --Changed 1 to NULL by Sanjay
417 )
418 )
419 , 5
420 ) AS process_flag
421 FROM ibw_pages_b pages
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
431 , DECODE( INSTR( pat.attribute24
432 , '?'
433 , 1
434 )
435 , 0, LENGTH( pat.attribute24 )
436 , INSTR( pat.attribute24
437 , '?'
438 , 1
439 ) - 1
440 )
441 ) LIKE
442 REPLACE ( url_pattern
443 , '*'
444 , '%'
445 ) || '%'
446 )
447 , -2
448 )
449 )
450 ) AS site_id
451 , TO_NUMBER ( pat.attribute1 ) AS visitid
452 , TO_NUMBER ( pat.attribute2 ) AS visitorid
453 , pat.TIMESTAMP AS pageviewstarttime -- Put PAT.Timestamp as starttime for calculating duration
454 , TO_NUMBER ( pat.attribute6 ) AS seqnum
455 , NVL ( SUBSTR ( pat.attribute24
456 , 0
457 , 3999
458 ), ' ' ) AS url
459 , pat.attribute16 AS appctx
460 /*
461 * Page View Duration will be recorded as visitid-seqnum-duration by tracking either in the next page view or next next page view.
462 * It means that for page view 1 tracking may put page view duration in page view 2 or page view 3.
463 * Use Lead function to get next or next next page view.
464 * If page view duration is not tracked then put -1 for calculating duration with latency.
465 */
466 , TO_NUMBER ( DECODE ( ( pat.attribute1
467 || '-'
468 || pat.attribute6 )
469 , LEAD ( ( SUBSTR ( pat.attribute8
470 , 0
471 , ( INSTR ( pat.attribute8
472 , '-'
473 , 1
474 , 2
475 )
476 - 1
477 )
478 )
479 )) OVER ( PARTITION BY TO_NUMBER ( pat.attribute1 ) ORDER BY TO_NUMBER ( pat.attribute6 )), NVL ( LEAD ( ( SUBSTR ( pat.attribute8
480 , INSTR ( pat.attribute8
481 , '-'
482 , 1
483 , 2
484 )
485 + 1 )
486 )) OVER ( PARTITION BY TO_NUMBER ( pat.attribute1 ) ORDER BY TO_NUMBER ( pat.attribute6 ))
487 , -1 )
488 , LEAD ( ( SUBSTR ( pat.attribute8
489 , 0
490 , ( INSTR ( pat.attribute8
491 , '-'
492 , 1
493 , 2
494 )
495 - 1
496 )
497 )
498 )
499 , 2 ) OVER ( PARTITION BY TO_NUMBER ( pat.attribute1 ) ORDER BY TO_NUMBER ( pat.attribute6 )), NVL ( LEAD ( ( SUBSTR ( pat.attribute8
500 , INSTR ( pat.attribute8
501 , '-'
502 , 1
503 , 2
504 )
505 + 1 )
506 )
507 , 2 ) OVER ( PARTITION BY TO_NUMBER ( pat.attribute1 ) ORDER BY TO_NUMBER ( pat.attribute6 ))
508 , -1 )
509 , NVL ( DECODE( LEAD( pat.attribute6) over ( PARTITION BY TO_NUMBER(pat.attribute1)
510 ORDER BY TO_NUMBER(pat.starttime)
511 )
512 , pat.attribute6 + 1, LEAD( pat.starttime) over ( PARTITION BY TO_NUMBER(pat.attribute1)
513 ORDER BY TO_NUMBER(pat.starttime)
514 )
515 , pat.attribute6, LEAD( pat.starttime) over ( PARTITION BY TO_NUMBER(pat.attribute1)
516 ORDER BY TO_NUMBER(pat.starttime)
517 )
518 , pat.starttime ) - pat.starttime , 0) -- Perf:66:Changed page view duration update to decode
519 )) AS DURATION
520 /*
521 * Event validations and getting more information out of the published:
522 * For Express Check out: iStore will publish the cart id, we should get the order id if the cart has been converted.
523 * Otherwise the event will be cached in the page_views_tmp table to be picked up in the next run of this program.
524 * To know whether the cart has been converted into order the select stmt on the aso_quote_headers_all is giving a string
525 * like "cart_id,order_id" where order_id could be a known string like 'NOORDER'
526 *
527 * For Order Creation: iStore will publish cart id, we need to get the order id.
528 */
529 , DECODE( pat.attribute16
530 , 'I', DECODE ( pat.attribute20
531 , l_xchkout_code, NVL ( ( SELECT quote_header_id
532 || ','
533 || DECODE ( order_id
534 , NULL, 'NOORDER' --Changed by Sanjay. Changed from NULL to NOORDER
535 , TO_CHAR ( order_id )
536 )
537 FROM aso_quote_headers_all
538 WHERE quote_header_id =
539 SUBSTR ( pat.attribute21
540 , INSTR ( pat.attribute21
541 , '=' )
542 + 1 ))
543 , '-1' )
544 , l_order_code, ( SELECT TO_CHAR ( order_id )
545 FROM aso_quote_headers_all
546 WHERE quote_header_id =
547 SUBSTR ( pat.attribute21
548 , INSTR ( pat.attribute21
549 , '=' )
550 + 1 ))
551 , 'SRCH',pat.attribute21
552 , SUBSTR ( pat.attribute21 , INSTR ( pat.attribute21 , '=' ) + 1 )
553 )
554 , 'E', DECODE ( pat.attribute20
555 ,l_cart_code,null
556 ,l_order_code,null
557 ,'SRCH',null
558 ,l_xchkout_code,null
559 , l_ordinq_code, NVL2 ( pat.attribute21
560 , ( SELECT TO_CHAR ( header_id )
561 FROM oe_order_headers_all
562 WHERE header_id =
563 NVL (
564 DECODE(LTRIM(SUBSTR ( pat.attribute21
565 ,INSTR ( pat.attribute21
566 , '='
567 ) +1
568 )
569 ,'0123456789'
570 )
571 ,NULL,pat.attribute10
572 ,-1
573 )
574 ,-1)
575 )
576 , '-1'
577 )
578 , l_payinq_code, NVL2 ( pat.attribute21
579 , ( SELECT TO_CHAR ( cash_receipt_id )
580 FROM ar_cash_receipts_all
581 WHERE cash_receipt_id =
582 NVL (
583 DECODE(LTRIM(SUBSTR ( pat.attribute21
584 ,INSTR ( pat.attribute21
585 , '='
586 ) +1
587 )
588 ,'0123456789'
589 )
590 ,NULL,pat.attribute10
591 ,-1
592 )
593 ,-1)
594 )
595 , '-1'
596 )
597 , l_invinq_code, NVL2 ( pat.attribute21
598 , ( SELECT TO_CHAR ( customer_trx_id )
599 FROM ra_customer_trx_all
600 WHERE customer_trx_id =
601 NVL (
602 DECODE(LTRIM(SUBSTR ( pat.attribute21
603 ,INSTR ( pat.attribute21
604 , '='
605 ) +1
606 )
607 ,'0123456789'
608 )
609 ,NULL,pat.attribute10
610 ,-1
611 )
612 ,-1)
613 ) , '-1'
614 )
615 , pat.attribute21
616 )
617 ) AS evnt_id
618 , pat.attribute7 AS tracked_site_code
619 , pat.attribute4 AS tracked_page_code
620 , pat.attribute5 AS tracked_page_name
621 , NVL ( pat.attribute9, 'NONE' ) AS business_context
622 , NVL2 (pat.attribute9
623 , NVL ( DECODE ( pat.attribute9
624 , 'PRODUCT', ( SELECT NVL2 ( inventory_item_id
625 , inventory_item_id
626 || '-'
627 , NULL
628 )
629 || NVL2 ( master_id
630 , SUBSTR ( master_id
631 , INSTR ( master_id
632 , '-'
633 , 1
634 , 1
635 )
636 + 1 )
637 || '-'
638 , organization_id
639 || '-'
640 )
641 || NVL2 ( organization_id
642 , organization_id
643 , NULL
644 )
645 FROM eni_oltp_item_star
646 WHERE inventory_item_id =
647 NVL ( DECODE(LTRIM(pat.attribute10,'0123456789'),NULL,pat.attribute10,-1),-1)
648 AND organization_id =
649 NVL ( DECODE(LTRIM(pat.attribute15,'0123456789'),NULL,pat.attribute15,-1),-1)
650 )
651 , 'SECTION', ( SELECT section_id
652 FROM ibe_dsp_sections_b --Perf:70:Changed from sections_vl to sections_v
653 WHERE section_id = pat.attribute10 )
654 , NULL
655 )
656 , '-1' )
657 , '-999'
658 ) AS business_context_value -- For section context:just validating the section id,
659 -- for product context: getting a combination of childitem-masteritem-org
660 , camptab.source_code_id AS campaign_source_code -- Validating if the campaign id is valid and numeric
661 --Perf:removed inner select and made outer join
662 , DECODE ( DECODE(pat.attribute16,'I',sites1.enable_traffic_filter,sites2.enable_traffic_filter)
663 , 'Y', NVL ( ( SELECT tag
664 FROM fnd_lookup_values
665 WHERE lookup_type = 'IBW_IP_ADDRESS'
666 AND view_application_id = 666
667 AND security_group_id = 0
668 AND lookup_code = meaning
669 AND ROWNUM=1
670 AND pat.clientip LIKE
671 REPLACE ( tag
672 , '*'
673 , '%'
674 ))
675 , 'N' )
676 , 'N'
677 ) AS ipfilter -- assigning ipfilter=N if page doesnt qualify to be filtered out
678 , pat.attribute20 AS event_code
679 , TO_NUMBER ( DECODE ( pat.attribute20
680 , 'SRCH', SUBSTR ( pat.attribute21
681 , INSTR ( attribute21
682 , 'SRCHSIZE=' )
683 + 9
684 , DECODE ( INSTR ( attribute21
685 , ':'
686 , INSTR ( attribute21
687 , 'SRCHSIZE=' )
688 , 1
689 )
690 , 0, LENGTH ( attribute21 )
691 + 1
692 , INSTR ( attribute21
693 , ':'
694 , INSTR ( attribute21
695 , 'SRCHSIZE=' )
696 , 1
697 )
698 - INSTR ( attribute21
699 , 'SRCHSIZE=' )
700 - LENGTH ( 'SRCHSIZE=' )
701 )
702 )
703 , -1
704 )) AS srch_size -- Update search size by looking for key word SRCHSIZE=20,
705 -- may be a ':' could come to start another saerch attribute
706 , DECODE ( pat.attribute20
707 , 'SRCH', SUBSTR ( pat.attribute21
708 , INSTR ( attribute21, 'SRCHSTR=' )
709 + 8
710 , DECODE ( INSTR ( attribute21
711 , ':'
712 , INSTR ( attribute21
713 , 'SRCHSTR=' )
714 , 1
715 )
716 , 0, LENGTH ( attribute21 )
717 + 1
718 , INSTR ( attribute21
719 , ':'
720 , INSTR ( attribute21
721 , 'SRCHSTR=' )
722 , 1
723 )
724 - INSTR ( attribute21, 'SRCHSTR=' )
725 - LENGTH ( 'SRCHSTR=' )
726 )
727 )
728 , NULL
729 ) AS srch_str -- Update search string by looking for key workd SRCHSTR=Web,
730 -- may be a ':' could come to start another search attribute
731 , DECODE ( pat.attribute20
732 , 'SRCH', DECODE ( INSTR ( attribute21
733 , 'SRCHMORE'
734 , 1
735 , 1
736 )
737 , 0, 'Y'
738 , 'N'
739 )
740 , NULL
741 ) AS srch_more -- Update more result flag by looking for key word SRCHMORE
742 , NVL ( NVL2 ( DECODE(pat.attribute16,'I',usertab1.customer_id,usertab2.customer_id)
743 , NVL ( DECODE(pat.attribute16,'I',rel1.object_id,rel2.object_id), DECODE(pat.attribute16,'I',usertab1.customer_id,usertab2.customer_id) )
744 , l_guest_party_id
745 )
746 , l_guest_party_id ) AS party_id -- Get correct party id for b2b or b2c user if he is not guest user.
747 , DECODE(pat.attribute16,'I',usertab1.customer_id,usertab2.customer_id) AS customer_id
748 , pat.attribute14 AS loginevent
749 , pat.clientip AS ip_address
750 ,NVL ( SUBSTR ( pat.referrer
751 , 0
752 , 3999
753 ), NULL ) AS referrer
754 , pat.useragent AS useragent
755 , DECODE ( pat.attribute16
756 ,'I', NVL2 ( ( SELECT access_name
757 FROM ibe_dsp_attachments_v
758 WHERE UPPER(pat.attribute24) LIKE '%/' || UPPER(file_name) || '%' --Changed by Venky.
759 AND ROWNUM = 1 )
760 , 'R'
761 , NVL2 ( pat.attribute4
762 , 'C',NVL2 ( pat.attribute5
763 , 'N', 'U'
764 )
765 )
766 ) -- Matching criteria is R, if template is found for the jsp in the page view URL
767 -- else if page code is found then C, else if page name is found then N, else U.
768 ,'E', NVL2 ( pat.attribute4
769 , 'C', NVL2( pat.attribute5
770 , 'N', 'U'
771 )
772 ) -- Matching criteria for non-EBS is similar to EBS but no template mapping involved
773 ) AS matching_criteria
774 , DECODE( pat.attribute16
775 ,'I', UPPER(NVL ( ( SELECT access_name
776 FROM ibe_dsp_attachments_v
777 WHERE UPPER(pat.attribute24) LIKE '%/' || UPPER(file_name) || '%' --Changed by Venky
778 AND ROWNUM = 1 )
779 , NVL ( pat.attribute4
780 , NVL ( pat.attribute5
781 , SUBSTR ( pat.attribute24
782 , 1
783 , DECODE ( INSTR ( pat.attribute24
784 , '?'
785 , 1
786 , 1
787 )
788 , 0, LENGTH ( pat.attribute24 )
789 , INSTR ( pat.attribute24
790 , '?'
791 , 1
792 , 1
793 ) - 1
794 )
795 )
796 )
797 )
798 )
799 )
800 ,'E', UPPER( NVL2 ( pat.attribute4
801 , pat.attribute4, NVL2 ( pat.attribute5
802 , pat.attribute5, SUBSTR ( pat.attribute24
803 , 1
804 , DECODE ( INSTR ( pat.attribute24
805 , '?'
806 , 1
807 )
808 , 0, LENGTH ( pat.attribute24 )
809 , INSTR ( pat.attribute24
810 , '?'
811 , 1
812 ) - 1
813 )
814 )
815 )
816 )
817 )
818 ) AS matching_value -- Based on the matching_criteria get the matching_value also
819 , NVL ( pat.userid, l_guest_user_id ) AS user_id -- Make all numm user id value as guest user id
820 , pat.attribute3 AS guid
821 , pat.attribute10 AS attribute10
822 ,pat.last_update_date as pat_last_update_date
823 FROM jtf_pf_wa_info_vl pat
824 , ibe_msites_b sites1
825 , ibe_msites_b sites2
826 , ams_source_codes camptab
827 , fnd_user usertab1
828 , fnd_user usertab2
829 , hz_relationships rel2
830 , hz_relationships rel1
831 WHERE pat.last_update_date > l_pat_date -- Consider only records logged after the last puged date
832 AND pat.attribute11 = 'true' -- Consider only display pages
833 AND sites1.msite_id(+) = DECODE(pat.attribute16,'I',pat.attribute7,-1)
834 AND sites2.access_name(+) = DECODE(pat.attribute16,'E',pat.attribute7,NULL)
835 AND camptab.source_code_id (+) = NVL ( DECODE(LTRIM(pat.attribute13,'0123456789'),NULL,pat.attribute13,-1),-1) --Perf: Changed from inner select to outter join
836 AND rel1.party_id(+) = usertab1.customer_id
837 AND rel1.directional_flag(+) = 'F'
838 AND rel2.party_id(+) = usertab2.customer_id
839 AND rel2.directional_flag(+) = 'F'
840 AND usertab1.user_id (+) = DECODE(pat.attribute16,'I',pat.userid,-1) --Perf: Removed inner select and made it join
841 AND usertab2.user_guid (+) = DECODE(pat.attribute16,'E',pat.attribute3,NULL) ) pv
842 WHERE pv.ipfilter = 'N' -- ipfilter is processed in the inner selects and
843 -- made 'Y' if it has to be filtered out
844 AND pages.page_matching_criteria(+) = pv.matching_criteria -- The matching_criteria is evaluated in the inner select
845 -- this should match the page matching criteria or be null
846 AND pages.page_matching_value(+) = UPPER(pv.matching_value) -- The matching_value is evaluated in the inner selects's
847 -- this should match the page matching value or be null
848 AND pv.visitid NOT IN ( 0, -1 ) -- Bug # for visit id -1 (being defensive on the wrong data)
849 AND pv.visitorid NOT IN ( -1, 0 );
850
851 -- Migrate Processing Pages into page views temp table from PAT schema
852
853 -- Process Flag Explanationch
854
855 -- Process_flag = 8 : Processing Pages which have login event info
856 -- process_flag = 4 : Processing Pages which have business events
857 -- process_flag = 5 : Express check out page views which dont have order id yet,
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
867 , page_view_start_time
868 , page_view_duration
869 , tracked_application_context
870 , tracked_site_code
871 , tracked_page_code
872 , tracked_page_name
873 , tracked_page_url
874 , search_result_size
875 , search_phrase
876 , exact_result_size_flag
877 , site_id
878 , page_id
879 , business_context_value
880 , business_context
881 , party_id
882 , visitor_id
883 , visitant_id
884 , evnt_type
885 , evnt_id
886 , campaign_source_code_id
887 , referral_url
888 , ip_address
889 , browser_os_info
890 , user_id
891 , user_guid
892 , party_relationship_id
893 , created_by
894 , creation_date
895 , last_updated_by
896 , last_update_date
897 , last_update_login
898 , object_version_number
899 , program_id
900 , program_login_id
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
910 , 0
911 , appctx
912 , NULL
913 , NULL
914 , NULL
915 , ' '
916 , srch_size
917 , srch_str
918 , srch_more
919 , site_id
920 , -2
921 , NULL
922 , NULL
923 , party_id
924 , visitorid
925 , DECODE ( user_id
926 , l_guest_user_id, NVL2 ( guid
927 , 'g'
928 || guid
929 , 'v'
930 || visitorid
931 )
932 , NULL, NVL2 ( guid
933 , 'g'
934 || guid
935 , 'v'
936 || visitorid
937 )
938 , NVL2 ( party_id
939 , 'p'
940 || party_id
941 , 'f'
942 || user_id
943 )
944 )
945 , event_code
946 , evnt_id
947 , NULL
948 , NULL
949 , ip_address
950 , NULL
951 , user_id
952 , guid
953 , NULL
954 , fnd_global.user_id
955 , pat_last_update_date
956 , fnd_global.user_id
957 , SYSDATE
958 , fnd_global.conc_login_id
959 , 1
960 , fnd_global.conc_program_id
961 , fnd_global.conc_login_id
962 , fnd_global.prog_appl_id
963 , fnd_global.conc_request_id
964 , DECODE ( loginevent
965 , 'true', 8
966 , DECODE ( INSTR ( evnt_id
967 , 'NULL'
968 , 1
969 , 1
970 )
971 , 0, 4
972 , DECODE ( event_code
973 , l_xchkout_code, 5
974 , 4
975 )
976 )
977 ) AS process_flag
978 FROM ( SELECT pat.recid
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
988 , '?'
989 , 1
990 )
991 , 0, LENGTH( pat.attribute24 )
992 , INSTR( pat.attribute24
993 , '?'
994 , 1
995 ) - 1
996 )
997 )
998 LIKE
999 REPLACE ( url_pattern
1000 , '*'
1001 , '%'
1002 ) || '%'
1003 )
1004 , -2
1005 )
1006 )
1007 ) AS site_id
1008 , TO_NUMBER ( pat.attribute1 ) AS visitid
1009 , TO_NUMBER ( pat.attribute2 ) AS visitorid
1010 , TO_NUMBER ( pat.attribute6 ) AS seqnum
1011 , pat.attribute16 AS appctx
1012 , ( DECODE ( pat.attribute20
1013 , l_xchkout_code, ( SELECT quote_header_id
1014 || ','
1015 || DECODE ( order_id
1016 , NULL, 'NOORDER' --Changed by Sanjay. NULL to NORDER
1017 , TO_CHAR ( order_id )
1018 )
1019 FROM aso_quote_headers_all
1020 WHERE quote_header_id =
1021 SUBSTR ( pat.attribute21
1022 , INSTR ( pat.attribute21
1023 , '=' )
1024 + 1 ))
1025 , l_order_code, ( SELECT order_id
1026 FROM aso_quote_headers_all
1027 WHERE quote_header_id =
1028 SUBSTR ( pat.attribute21
1029 , INSTR ( pat.attribute21
1030 , '=' )
1031 + 1 ))
1032 , 'SRCH',pat.attribute21
1033 ,SUBSTR ( pat.attribute21
1034 , INSTR ( pat.attribute21
1035 , '=' )
1036 + 1 )--Removed validations for enquiries by sanjay
1037 )
1038 ) AS evnt_id
1039 , DECODE ( DECODE(pat.attribute16,'I',sites1.enable_traffic_filter,sites2.enable_traffic_filter)
1040 , 'Y', NVL ( ( SELECT tag
1041 FROM fnd_lookup_values
1042 WHERE lookup_type = 'IBW_IP_ADDRESS'
1043 AND ROWNUM=1
1044 AND pat.clientip LIKE
1045 REPLACE ( tag
1046 , '*'
1047 , '%'
1048 ))
1049 , 'N' )
1050 , 'N'
1051 ) AS ipfilter
1052 , pat.clientip AS ip_address
1053 , pat.attribute20 AS event_code
1054 , TO_NUMBER ( DECODE ( pat.attribute20
1055 , 'SRCH', SUBSTR ( pat.attribute21
1056 , INSTR ( attribute21
1057 , 'SRCHSIZE=' )
1058 + LENGTH ( 'SRCHSIZE' )
1059 + 1
1060 , DECODE ( INSTR ( attribute21
1061 , ':'
1062 , INSTR ( attribute21
1063 , 'SRCHSIZE=' )
1064 , 1
1065 )
1066 , 0, LENGTH ( attribute21 )
1067 + 1
1068 , INSTR ( attribute21
1069 , ':'
1070 , INSTR ( attribute21
1071 , 'SRCHSIZE=' )
1072 , 1
1073 )
1074 - INSTR ( attribute21
1075 , 'SRCHSIZE=' )
1076 - LENGTH ( 'SRCHSIZE=' )
1077 )
1078 )
1079 , -1
1080 )) AS srch_size
1081 , DECODE ( pat.attribute20
1082 , 'SRCH', SUBSTR ( pat.attribute21
1083 , INSTR ( attribute21
1084 , 'SRCHSTR=' )
1085 + LENGTH ( 'SRCHSTR' )
1086 + 1
1087 , DECODE ( INSTR ( attribute21
1088 , ':'
1089 , INSTR ( attribute21
1090 , 'SRCHSTR=' )
1091 , 1
1092 )
1093 , 0, LENGTH ( attribute21 )
1094 + 1
1095 , INSTR ( attribute21
1096 , ':'
1097 , INSTR ( attribute21
1098 , 'SRCHSTR=' )
1099 , 1
1100 )
1101 - INSTR ( attribute21
1102 , 'SRCHSTR=' )
1103 - LENGTH ( 'SRCHSTR=' )
1104 )
1105 )
1106 , NULL
1107 ) AS srch_str
1108 , DECODE ( pat.attribute20
1109 , 'SRCH', DECODE ( INSTR ( attribute21
1110 , 'SRCHMORE'
1111 , 1
1112 , 1
1113 )
1114 , 0, 'Y'
1115 , 'N'
1116 )
1117 , NULL
1118 ) AS srch_more
1119 , NVL ( DECODE(pat.attribute16,'I',usertab1.user_id,usertab2.user_id), l_guest_user_id )
1120 AS user_id
1121 , pat.attribute14 AS loginevent
1122 , NVL ( NVL2 ( DECODE(pat.attribute16,'I',usertab1.customer_id,usertab2.customer_id)
1123 , NVL ( DECODE(pat.attribute16,'I',rel1.object_id,rel2.object_id), DECODE(pat.attribute16,'I',usertab1.customer_id,usertab2.customer_id) )
1124 , l_guest_party_id
1125 )
1126 , l_guest_party_id ) AS party_id -- Get correct party id for b2b or b2c user if he is not guest user.
1127 , DECODE(pat.attribute16,'I',usertab1.customer_id,usertab2.customer_id) AS customer_id
1128 , pat.attribute3 guid
1129 ,pat.last_update_date as pat_last_update_date
1130 FROM jtf_pf_wa_info_vl pat
1131 , ibe_msites_b sites1
1132 , ibe_msites_b sites2
1133 , fnd_user usertab1
1134 , fnd_user usertab2
1135 , hz_relationships rel1
1136 , hz_relationships rel2
1137 WHERE pat.attribute11 = 'false'
1138 AND pat.last_update_date > l_pat_date
1139 AND sites1.msite_id(+) = DECODE(pat.attribute16,'I',pat.attribute7,-1)
1140 AND sites2.access_name(+) = DECODE(pat.attribute16,'E',pat.attribute7,NULL)
1141 AND rel1.party_id(+) = usertab1.customer_id
1142 AND rel1.directional_flag(+) = 'F'
1143 AND rel2.party_id(+) = usertab2.customer_id
1144 AND rel2.directional_flag(+) = 'F'
1145 AND usertab1.user_id (+) = DECODE(pat.attribute16,'I',pat.userid,-1) --Perf: Removed inner select and made it join
1146 AND usertab2.user_guid (+) = DECODE(pat.attribute16,'E',pat.attribute3,NULL)) pv
1147 WHERE pv.visitid NOT IN ( 0, -1 )
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,
1157 VISIT_ID,
1158 PAGE_VIEW_START_TIME,
1159 PAGE_VIEW_DURATION,
1160 TRACKED_APPLICATION_CONTEXT,
1161 TRACKED_SITE_CODE,
1162 TRACKED_PAGE_CODE,
1163 TRACKED_PAGE_NAME,
1164 TRACKED_PAGE_URL,
1165 SEARCH_RESULT_SIZE,
1166 SEARCH_PHRASE,
1167 EXACT_RESULT_SIZE_FLAG,
1168 SITE_ID,
1169 PAGE_ID,
1170 BUSINESS_CONTEXT_VALUE,
1171 BUSINESS_CONTEXT,
1172 PARTY_ID,
1173 VISITOR_ID,
1174 VISITANT_ID,
1175 EVNT_TYPE,
1176 EVNT_ID,
1177 CAMPAIGN_SOURCE_CODE_ID,
1178 REFERRAL_URL,
1179 IP_ADDRESS,
1180 BROWSER_OS_INFO,
1181 USER_ID,
1182 USER_GUID,
1183 PARTY_RELATIONSHIP_ID,
1184 PROCESS_FLAG,
1185 OBJECT_VERSION_NUMBER,
1186 CREATED_BY,
1187 CREATION_DATE,
1188 LAST_UPDATED_BY,
1189 LAST_UPDATE_DATE
1190 )
1191 SELECT PAGE_VIEW_ID,
1192 PAGE_VIEW_SEQ_NUM,
1193 PAGE_INSTANCE_ID,
1194 VISIT_ID,
1195 PAGE_VIEW_START_TIME,
1196 PAGE_VIEW_DURATION,
1197 TRACKED_APPLICATION_CONTEXT,
1198 TRACKED_SITE_CODE,
1199 TRACKED_PAGE_CODE,
1200 TRACKED_PAGE_NAME,
1201 TRACKED_PAGE_URL,
1202 SEARCH_RESULT_SIZE,
1203 SEARCH_PHRASE,
1204 EXACT_RESULT_SIZE_FLAG,
1205 SITE_ID,
1206 PAGE_ID,
1207 BUSINESS_CONTEXT_VALUE,
1208 BUSINESS_CONTEXT,
1209 PARTY_ID,
1210 VISITOR_ID,
1211 VISITANT_ID,
1212 EVNT_TYPE,
1213 EVNT_ID,
1214 CAMPAIGN_SOURCE_CODE_ID,
1215 REFERRAL_URL,
1216 IP_ADDRESS,
1217 BROWSER_OS_INFO,
1218 USER_ID,
1219 USER_GUID,
1220 PARTY_RELATIONSHIP_ID,
1221 NULL,
1222 1,
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));
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
1240 printLog('Creating pages');
1241 FOR page_view IN tmp_page_views_cache(9)
1242 LOOP
1243 CREATEPAGE(page_view.tracked_page_code,page_view.tracked_page_name,page_view.tracked_page_url,page_view.tracked_application_context,page_view.business_context,l_page_id);
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;
1253
1254
1255
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
1265 WHERE tmp.page_id=pag.page_id)
1266 AND application_context = 'N';
1267
1268 -- Populate page instance table
1269
1270 printLog('Creating new Page instances');
1271 FOR page_view IN tmp_page_views_instances
1272 LOOP
1273 --Changed by Venky. Using Exceptions to handle no_data condition
1274
1275
1276 IBW_PAGE_INSTANCES_PVT.INSERT_row (l_page_instance_id
1277 ,page_view.page_id
1278 ,page_view.business_context
1279 ,page_view.business_context_value
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
1289 AND pi.business_context_value = pv.business_context_value )
1290 , -1 );
1291
1292 -- get page view COUNT for logging output
1293
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
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
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');
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
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
1331
1332 -- find out if events can be matched to next page views
1333
1334 printLog('Updating processing page events from cache');
1335
1336
1337
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
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
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
1360 printLog('Updating processing page events from temp table');
1361
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
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
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;
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 )
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
1400
1401 FOR page_view IN page_views_new_referral_cat
1402 LOOP
1403
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
1413 ORDER BY program_id desc
1414 ,length(url_pattern) desc
1415 ,creation_date ;
1416 EXCEPTION
1417 WHEN NO_DATA_FOUND THEN
1418 x_ref_id := NULL;
1419 END;
1420
1421
1422 IF x_ref_id is NULL THEN
1423 --Bug 7191178
1424 IF INSTR (l_ref_url, '/',1,3) = 0
1425 THEN
1426 l_ref_name := SUBSTR (l_ref_url, 1,LENGTH (l_ref_url));
1427 ELSE
1428 l_ref_name := SUBSTR (l_ref_url, 1,INSTR(l_ref_url, '/',1,3)-1 );
1429 END IF;
1430 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Creating Referral Category for referral name: ' || l_ref_name);
1431
1432 IBW_REFERRAL_PVT.INSERT_row(x_ref_id,l_ref_name,l_ref_name,l_error_message);
1433 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL)
1434 THEN
1435 fnd_message.set_name('IBW','IBW_OE_NEW_REF_CAT');
1436 fnd_message.set_token('CATEGORY_NAME',l_ref_name);
1437 fnd_log.string(FND_LOG.LEVEL_EVENT,'IBW.PLSQL.IBWOE_PVT',fnd_message.get);
1438 END IF;
1439 END IF;
1440 END LOOP;
1441
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
1451 , page_view_start_time
1452 , page_view_duration
1453 , tracked_application_context
1454 , tracked_site_code
1455 , tracked_page_code
1456 , tracked_page_name
1457 , tracked_page_url
1458 , search_result_size
1459 , search_phrase
1460 , exact_result_size_flag
1461 , site_id
1462 , page_id
1463 , business_context_value
1464 , business_context
1465 , party_id
1466 , visitor_id
1467 , visitant_id
1468 , evnt_type
1469 , evnt_id
1470 , campaign_source_code_id
1471 , referral_url
1472 , ip_address
1473 , browser_os_info
1474 , user_id
1475 , user_guid
1476 , party_relationship_id
1477 , created_by
1478 , creation_date
1479 , last_updated_by
1480 , last_update_date
1481 , last_update_login
1482 , object_version_number
1483 , program_id
1484 , program_login_id
1485 , program_application_id
1486 , request_id
1487 )
1488 ( SELECT rec_id
1489 , page_view_seq_num
1490 , page_instance_id
1491 , visit_id
1492 , page_view_start_time
1493 , page_view_duration
1494 , tracked_application_context
1495 , tracked_site_code
1496 , tracked_page_code
1497 , tracked_page_name
1498 , tracked_page_url
1499 , search_result_size
1500 , search_phrase
1501 , exact_result_size_flag
1502 , site_id
1503 , page_id
1504 , business_context_value
1505 , business_context
1506 , party_id
1507 , visitor_id
1508 , visitant_id
1509 , evnt_type
1510 , evnt_id
1511 , campaign_source_code_id
1512 , referral_url
1513 , ip_address
1514 , browser_os_info
1515 , user_id
1516 , user_guid
1517 , party_relationship_id
1518 , fnd_global.user_id
1519 , SYSDATE
1520 , fnd_global.user_id
1521 , SYSDATE
1522 , fnd_global.conc_login_id
1523 , 1
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 );
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
1542 printLog('Inserting into site_visits table');
1543
1544
1545 MERGE INTO ibw_site_visits sv
1546 USING (SELECT -1 AS site_visit_id
1547 , visit_id
1548 , site_id
1549 , visit_start_time
1550 , visitor_id
1551 , party_id
1552 , num_page_views
1553 , site_visit_duration
1554 , total_carts_created
1555 , total_orders_created
1556 , total_web_registrations
1557 , total_order_inquiries
1558 , total_payment_inquiries
1559 , total_invoice_inquiries
1560 , total_opt_outs
1561 , user_id
1562 , visitant_id
1563 , campaign_source_code_id
1564 , DECODE (INSTR (innerquery.visitant_id, 'p', 1, 1),
1565 0, DECODE ((SELECT COUNT (site_visit_id)
1566 FROM ibw_site_visits
1567 WHERE visitor_id = innerquery.visitor_id
1568 AND visit_id <> innerquery.visit_id
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
1578 )
1579 ,0, NULL
1580 , 'Y'
1581 )
1582 , 'Y'),
1583 DECODE ((SELECT COUNT (site_visit_id)
1584 FROM ibw_site_visits
1585 WHERE party_id = innerquery.party_id
1586 AND visit_id <> innerquery.visit_id
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
1596 )
1597 ,0, NULL
1598 , 'Y'
1599 )
1600 , 'Y'
1601 )
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
1611 ) PV
1612 WHERE patterns.TYPE = 'R'
1613 AND PV.URL LIKE
1614 upper(patterns.url_pattern || '%')
1615 ORDER BY program_id desc
1616 ,length(url_pattern) desc
1617 ,creation_date desc
1618 )
1619 WHERE visit_id = innerquery.visit_id
1620 AND ROWNUM =1
1621 )
1622 ,-1) as referral_category_id
1623 FROM (SELECT pv.visit_id
1624 , pv.site_id
1625 , MIN ( pv.page_view_start_time ) visit_start_time
1626 , pv.user_id AS user_id
1627 , pv.visitor_id
1628 , pv.party_id
1629 , pv.visitant_id
1630 , pv.campaign_source_code_id
1631 , COUNT ( DECODE ( pv.evnt_type
1632 , l_optout_code, 1
1633 , NULL
1634 )) AS total_opt_outs
1635 , COUNT ( pv.rec_id ) num_page_views
1636 , SUM ( pv.page_view_duration ) site_visit_duration
1637 , COUNT ( DECODE ( pv.evnt_type
1638 , l_cart_code , 1
1639 , NULL
1640 )) total_carts_created
1641 , COUNT ( DECODE ( pv.evnt_type
1642 , l_order_code, 1
1643 , NULL
1644 )) total_orders_created
1645 , COUNT ( DECODE ( pv.evnt_type
1646 , l_userreg_code, 1
1647 , NULL
1648 )) total_web_registrations
1649 , COUNT ( DECODE ( pv.evnt_type
1650 , l_ordinq_code, 1
1651 , NULL
1652 )) total_order_inquiries
1653 , COUNT ( DECODE ( pv.evnt_type
1654 , l_payinq_code, 1
1655 , NULL
1656 )) total_payment_inquiries
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
1666 WHERE process_flag is null
1667 AND tmp.visit_id=pv.visit_id)
1668 GROUP BY pv.visit_id
1669 ,pv.site_id
1670 ,pv.visitor_id
1671 ,pv.party_id
1672 ,pv.VISITANT_ID
1673 ,pv.user_id
1674 ,pv.campaign_source_code_id) innerquery) tmp
1675 ON ( sv.site_id = tmp.site_id
1676 AND sv.visit_id = tmp.visit_id )
1677 WHEN MATCHED THEN
1678 UPDATE
1679 SET sv.num_page_views = tmp.num_page_views
1680 , sv.site_visit_duration = tmp.site_visit_duration
1681 , sv.total_carts_created = tmp.total_carts_created
1682 , sv.total_orders_created = tmp.total_orders_created
1683 , sv.total_web_registrations = tmp.total_web_registrations
1684 , sv.total_order_inquiries = tmp.total_order_inquiries
1685 , sv.total_payment_inquiries = tmp.total_payment_inquiries
1686 , sv.total_invoice_inquiries = tmp.total_invoice_inquiries
1687 , sv.total_opt_outs = tmp.total_opt_outs
1688 , sv.last_update_date = SYSDATE
1689 , sv.last_updated_by = fnd_global.user_id
1690 , sv.repeat_visit_flag = tmp.repeat_visit_flag
1691 , sv.referral_category_id = tmp.referral_category_id
1692 , sv.user_id = tmp.user_id, sv.party_id = tmp.party_id
1693 , sv.visitant_id = tmp.visitant_id
1694 , sv.campaign_source_code_id = tmp.campaign_source_code_id
1695 WHEN NOT MATCHED THEN
1696 INSERT ( site_visit_id, visit_id, site_id, visit_start_time, visitor_id
1697 , referral_category_id, party_id, num_page_views
1698 , site_visit_duration, total_carts_created, total_orders_created
1699 , total_web_registrations, total_order_inquiries
1700 , total_payment_inquiries, total_invoice_inquiries
1701 , total_opt_outs, user_id, visitant_id, campaign_source_code_id
1702 , repeat_visit_flag, created_by, creation_date, last_updated_by
1703 , last_update_date, object_version_number )
1704 VALUES ( ibw_site_visits_s1.NEXTVAL, tmp.visit_id, tmp.site_id
1705 , tmp.visit_start_time, tmp.visitor_id, tmp.referral_category_id
1706 , tmp.party_id, tmp.num_page_views, tmp.site_visit_duration
1707 , tmp.total_carts_created, tmp.total_orders_created
1708 , tmp.total_web_registrations, tmp.total_order_inquiries
1709 , tmp.total_payment_inquiries, tmp.total_invoice_inquiries
1710 , tmp.total_opt_outs, tmp.user_id, tmp.visitant_id
1711 , tmp.campaign_source_code_id, tmp.repeat_visit_flag
1712 , fnd_global.user_id, SYSDATE, fnd_global.user_id, SYSDATE, 1 );
1713
1714
1715
1716
1717
1718
1719 -- get visit count for logging output
1720 printLog('Get visit count for logging output');
1721
1722 SELECT COUNT(distinct(visit_id))
1723 INTO l_visit_count
1724 FROM ibw_site_visits
1725 WHERE creation_date > l_start_time;
1726
1727
1728
1729 -- mark PAT records for purging
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
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
1747 IF l_pat_date IS NULL THEN
1748 SELECT Min(last_update_date)
1749 INTO l_pat_date
1750 FROM jtf_pf_wa_info_vl;
1751 printLog('Got Pat date from Pat'||l_pat_date);
1752 END IF;
1753
1754 IF l_pat_date IS NOT NULL THEN
1755 jtf_pf_conv_pkg.MIGRATED_DATA(2,l_pat_date);
1756 jtf_pf_conv_pkg.MIGRATED_DATA(3,l_pat_date);
1757 jtf_pf_conv_pkg.MIGRATED_DATA(4,l_pat_date);
1758
1759 END IF;
1760
1761
1762 -- Calling context configuration table population progrma
1763 printLog('Calling context configuration table population program');
1764
1765 CONTEXT_LOAD;
1766 --log messages for invalid sites AND business contexts
1767
1768
1769 printOutput('Messages for Invalid External Sites:');
1770 printOutput('====================================');
1771 FOR page_view IN tmp_page_views_cache(3)
1772 LOOP
1773 retcode := 1;
1774 fnd_message.set_name('IBW','IBW_OE_NO_SITE_FOUND');
1775 fnd_message.set_token('PAGE_URL',page_view.tracked_page_url);
1776 l_message_text := fnd_message.get;
1777 printOutput(l_message_text);
1778 END LOOP;
1779
1780
1781 printOutput('Missing page context identifiers:');
1782 printOutput('============================');
1783 FOR page_view IN tmp_page_views_cache(7)
1784 LOOP
1785 retcode := 1;
1786 fnd_message.set_name('IBW','IBW_OE_INVALID_CTX_ID');
1787 l_page_id := page_view.page_id;
1788 SELECT pages_tl.page_name
1789 INTO l_page_name
1790 FROM ibw_pages_tl pages_tl
1791 WHERE pages_tl.page_id = l_page_id
1792 AND language = userenv('LANG');
1793 fnd_message.set_token('PAGE_NAME',l_page_name);
1794 fnd_message.set_token('CONTEXT_ID',page_view.business_context_value);
1795 l_message_text := fnd_message.get;
1796 printOutput(l_message_text);
1797 END LOOP;
1798
1799
1800 printOutput('Page Views Invalid page context information');
1801 printOutput('=================================');
1802 FOR page_view IN tmp_page_views_cache(6)
1803 LOOP
1804 retcode := 1;
1805 fnd_message.set_name('IBW','IBW_OE_CTX_ID_NOT_FOUND');
1806 l_page_id := page_view.page_id;
1807 BEGIN
1808 SELECT pages_tl.page_name
1809 INTO l_page_name
1810 FROM ibw_pages_tl pages_tl
1811 WHERE pages_tl.page_id = l_page_id
1812 AND language = userenv('LANG');
1813 EXCEPTION
1814 WHEN OTHERS THEN
1815 l_page_name := page_view.tracked_page_url;
1816 END;
1817 fnd_message.set_token('PAGE_NAME',l_page_name);
1818 l_message_text := fnd_message.get;
1819 printOutput(l_message_text);
1820 END LOOP;
1821
1822 printOutput('Inactive pages for which Page Views are tracked');
1823 printOutput('===============================================');
1824
1825 FOR page_view IN inactive_pages
1826 LOOP
1827 retcode := 1;
1828 fnd_message.set_name('IBW','IBW_OE_INACTIVEPAGE_MATCH');
1829 l_page_id := page_view.page_id;
1830 SELECT pages_tl.page_name
1831 INTO l_page_name
1832 FROM ibw_pages_tl pages_tl
1833 WHERE pages_tl.page_id = l_page_id
1834 AND language = userenv('LANG');
1835 fnd_message.set_token('PAGE_NAME',l_page_name);
1836 l_message_text := fnd_message.get;
1837 printOutput(l_message_text);
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
1847
1848 printOutput('Fact Population Program Output:');
1849 printOutput('===============================');
1850
1851 FND_MESSAGE.SET_NAME('IBW','IBW_OE_OUTPUT_NUM_PG_VIEWS');
1852 FND_MESSAGE.SET_TOKEN('NUM_PAGE_VIEWS',l_page_view_count);
1853 l_message_text := fnd_message.get;
1854 printOutput(l_message_text);
1855
1856 FND_MESSAGE.SET_NAME('IBW','IBW_OE_OUTPUT_NUM_SITE_VISITS');
1857 FND_MESSAGE.SET_TOKEN('NUM_VISITS',l_visit_count);
1858 l_message_text := fnd_message.get;
1859 printOutput(l_message_text);
1860
1861
1862 printLog('Done with Fact Population Concurrent Program');
1863 errbuf := 'SUCCESS';
1864
1865 EXCEPTION
1866 WHEN OTHERS THEN
1867 printOutput('Exception :'||sqlerrm);
1868 retcode := 2;
1869 errbuf := ' errbuf' || ' '||SQLCODE||'-'||SQLERRM;
1870 END offline_engine;
1871
1872 -- ===========================================================
1873 -- Procedure printLog uses FND_FILE.PUT_LINE to write in the
1874 -- "log" file of a concurrent program
1875 -- ===========================================================
1876 PROCEDURE printLog(p_message IN VARCHAR2)
1877 IS
1878 pragma AUTONOMOUS_TRANSACTION;
1879 BEGIN
1880 FND_FILE.PUT_LINE(FND_FILE.LOG,p_message);
1881 commit;
1882 EXCEPTION
1883 WHEN OTHERS THEN
1884 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception :'||sqlerrm);
1885 END printLog;
1886
1887 -- ===========================================================
1888 -- Procedure printOutput uses FND_FILE.PUT_LINE to write in the
1889 -- "Output" file of a concurrent program
1890 -- ===========================================================
1891 PROCEDURE printOutput(p_message IN VARCHAR2)
1892 IS
1893 pragma AUTONOMOUS_TRANSACTION;
1894 BEGIN
1895 FND_FILE.PUT_LINE(FND_FILE.OUTPUT,p_message);
1896 printLog(p_message);
1897 commit;
1898 EXCEPTION
1899 WHEN OTHERS THEN
1900 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception :'||sqlerrm);
1901 END printOutput;
1902
1903
1904 --=================================================================================
1905 -- PROCEDURE :createpage PUBLIC
1906 -- PARAMETERS: pagecode Page code for the page to be created
1907 -- : pagename Page name for the page to be created
1908 -- : URL Page view URL forthe page to be created
1909 -- : appctx Application context for the page to be created
1910 -- : bizctx Business Context for the page to be created
1911 -- : pageid Out variable to hold the page id
1912 -- COMMENT : Procedure checks IF a page exists for a given page view
1913 -- AND created a page IF necessary
1914 --===============================================================================
1915
1916
1917 PROCEDURE createpage (
1918 pagecode IN VARCHAR2
1919 ,pagename IN VARCHAR2
1920 ,url IN VARCHAR2
1921 ,appctx IN VARCHAR2
1922 ,bizctx IN VARCHAR2
1923 ,pageid OUT NOCOPY NUMBER
1924 )
1925 IS
1926 x_page_id NUMBER (30);
1927 l_rec_count NUMBER (30);
1928 l_error_message VARCHAR2(240);
1929 l_template_access_name ibe_dsp_attachments_v.access_name%type;
1930 l_template_description ibe_dsp_attachments_v.description%type;
1931 l_template_name jtf_amv_items_vl.item_name%type;
1932 l_appctx VARCHAR2(30);
1933 l_url VARCHAR2(3000);
1934 BEGIN
1935 --find IF page matches AND then create page
1936 l_appctx := appctx;
1937 l_template_description := NULL;
1938 IF l_appctx = 'I' THEN
1939
1940
1941 BEGIN --Changed by Venky. Added code to handle no_data_found exception
1942 SELECT dsp.access_name,item.item_name,dsp.description
1943 INTO l_template_access_name ,l_template_name,l_template_description
1944 FROM ibe_dsp_attachments_v dsp,jtf_amv_items_vl item
1945 WHERE UPPER(url) LIKE '%/' || UPPER(file_name) || '%' --Changed by Venky
1946 AND item.item_id = dsp.logical_id
1947 AND ROWNUM = 1;
1948
1949 BEGIN --Changed by Venky. Added code to handle no_data_found exception
1950 SELECT page_id
1951 INTO pageid
1952 FROM ibw_pages_b pages
1953 WHERE pages.page_matching_criteria =
1954 NVL2(l_template_access_name
1955 ,'R'
1956 ,DECODE (pagecode,
1957 NULL, DECODE (pagename,
1958 NULL, 'U'
1959 ,'N'
1960 )
1961 ,'C'
1962 )
1963 )
1964 AND UPPER(pages.page_matching_value) =
1965 UPPER(NVL(l_template_access_name,
1966 NVL(pagecode,
1967 NVL(pagename,
1968 SUBSTR (url
1969 ,1
1970 ,DECODE(INSTR (url, '?', 1, 1) - 1
1971 ,-1,LENGTH(url)
1972 ,INSTR (url, '?', 1, 1) - 1
1973 )
1974 )
1975 )
1976 )
1977 ));
1978
1979 EXCEPTION
1980 WHEN NO_DATA_FOUND THEN
1981 NULL;
1982 END;
1983
1984 EXCEPTION
1985 WHEN NO_DATA_FOUND THEN
1986 l_template_access_name := NULL;
1987 l_template_name := NULL;
1988 l_appctx := 'E';
1989 l_rec_count := 0 ;
1990 END;
1991
1992 ELSIF l_appctx = 'E'
1993 THEN
1994 l_appctx := 'N';
1995
1996 BEGIN --Changed by Venky. Added code to handle no_data_found exception
1997 SELECT page_id
1998 INTO pageid
1999 FROM ibw_pages_b pages
2000 WHERE pages.page_matching_criteria =
2001 DECODE (pagecode,
2002 NULL, DECODE (pagename,
2003 NULL, 'U',
2004 'N'
2005 ),
2006 'C'
2007 )
2008 AND UPPER(pages.page_matching_value) =
2009 UPPER(DECODE (pagecode,
2010 NULL, DECODE (pagename,
2011 NULL, SUBSTR (url,
2012 1,
2013 DECODE (INSTR (url, '?',
2014 1),
2015 0, LENGTH (url),
2016 INSTR (url,
2017 '?',
2018 1
2019 )
2020 - 1
2021 )
2022 ),
2023 pagename
2024 ),
2025 pagecode
2026 ));
2027 EXCEPTION
2028 WHEN NO_DATA_FOUND THEN
2029 pageid := 0;
2030 END;
2031 END IF;
2032
2033 IF pageid = 0
2034 THEN
2035 IF l_appctx = 'I'
2036 THEN
2037
2038 l_appctx := 'T';
2039 ibw_pages_pvt.INSERT_row (x_page_id,
2040 l_template_name,
2041 l_template_description,
2042 FND_API.G_MISS_CHAR,
2043 l_appctx,
2044 bizctx,
2045 l_template_name,
2046 'R',
2047 l_template_access_name,
2048 l_error_message);
2049 pageid := x_page_id;
2050 ELSE
2051 IF pagecode is NULL
2052 THEN
2053 IF pagename is NULL
2054 THEN
2055 SELECT SUBSTR (url,
2056 1,
2057 DECODE (INSTR (url, '?',
2058 1),
2059 0, LENGTH (url),
2060 INSTR (url,
2061 '?',
2062 1
2063 )
2064 - 1
2065 )
2066 )
2067 INTO l_url FROM DUAL;
2068 ibw_pages_pvt.INSERT_row (x_page_id,
2069 l_url,
2070 l_template_description,
2071 FND_API.G_MISS_CHAR,
2072 l_appctx,
2073 bizctx,
2074 url,
2075 'U',
2076 l_url,
2077 l_error_message
2078 );
2079 ELSE
2080 ibw_pages_pvt.INSERT_row (x_page_id,
2081 pagename,
2082 l_template_description,
2083 FND_API.G_MISS_CHAR,
2084 l_appctx,
2085 bizctx,
2086 url,
2087 'N',
2088 pagename,
2089 l_error_message
2090 );
2091 END IF;
2092 ELSE
2093 ibw_pages_pvt.INSERT_row (x_page_id,
2094 pagecode,
2095 l_template_description,
2096 pagecode,
2097 l_appctx,
2098 bizctx,
2099 url,
2100 'C',
2101 pagecode,
2102 l_error_message
2103 );
2104 END IF;
2105 pageid := x_page_id;
2106 END IF;
2107 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Page created page id ' || pageid);
2108 IF (FND_LOG.LEVEL_EVENT >= FND_LOG.G_CURRENT_RUNTIME_LEVEL) THEN
2109 fnd_message.set_name('IBW','IBW_OE_PAGE_NOT_FOUND');
2110 fnd_message.set_token('PAGE_NAME',pagename);
2111 fnd_log.string(FND_LOG.LEVEL_EVENT,'IBW.PLSQL.IBWOE_PVT',fnd_message.get);
2112 fnd_file.put_line(fnd_file.log,fnd_message.get);
2113 END IF;
2114 END IF;
2115 pageid := pageid;
2116 IF pageid is null then
2117 RAISE no_data_found;
2118 END IF;
2119 EXCEPTION
2120 WHEN NO_DATA_FOUND THEN
2121 pageid := -1;
2122 WHEN OTHERS THEN
2123 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception :'||sqlerrm);
2124 pageid := -1;
2125 END createpage;
2126
2127
2128
2129
2130 --========================================================================
2131 -- PROCEDURE : recategorize_referrals PUBLIC
2132 -- PARAMETERS: errbuf Error Buffer for Concurrent Program asset value
2133 -- : retcode Return Code for Concurrent Program
2134 -- COMMENT : This procedure re populates all referral categories into the site visits table
2135 --========================================================================
2136
2137 Procedure recategorize_referrals(errbuf OUT NOCOPY VARCHAR2, retcode OUT NOCOPY NUMBER)
2138 is
2139 Begin
2140
2141
2142 printLog('Starting Referral Category Concurrent Program');
2143
2144 UPDATE ibw_site_visits tmp
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
2154 ) PV
2155 WHERE patterns.TYPE = 'R'
2156 AND PV.URL LIKE
2157 upper(patterns.url_pattern || '%')
2158 ORDER BY program_id desc
2159 ,length(url_pattern) desc
2160 ,creation_date desc
2161 )
2162 WHERE visit_id = tmp.visit_id
2163 AND ROWNUM =1
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
2173
2174
2175 printLog('Done Referral Category Concurrent Program');
2176 printOutput('Recategorization of Referrals successful');
2177 EXCEPTION
2178 WHEN OTHERS THEN
2179 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception :'||sqlerrm);
2180 retcode := 2;
2181 END recategorize_referrals;
2182
2183 --------------------------------------------------------------------------------------------------
2184 -- This procedure populates all the data required for IBW_CONTEXT_INTERFACE_B/TL table
2185 --
2186 -- PROCEDURE CONTEXT_LOAD
2187 --------------------------------------------------------------------------------------------------
2188
2189 PROCEDURE CONTEXT_LOAD
2190 IS
2191 l_user_id NUMBER := FND_GLOBAL.USER_ID();
2192 l_business_context VARCHAR2(30);
2193 x_return_status VARCHAR2(30);
2194 l_last_update_login NUMBER;
2195 l_program_id NUMBER;
2196 l_program_login_id NUMBER;
2197 l_program_app_id NUMBER;
2198 l_request_id NUMBER;
2199
2200 -- This cursor holds business_context for record with 'NONE' AS the business context
2201 CURSOR bus_ctx_cur IS SELECT business_context
2202 FROM ibw_context_interface_vl
2203 WHERE context_instance_value = -999;
2204
2205 BEGIN
2206 FND_PROFILE.GET('LOGIN_ID', l_last_update_login);
2207 FND_PROFILE.GET('CONC_PROGRAM_ID', l_program_id);
2208 FND_PROFILE.GET('CONC_LOGIN_ID', l_program_login_id);
2209 FND_PROFILE.GET('CONC_PROGRAM_APPLICATION_ID', l_program_app_id);
2210 FND_PROFILE.GET('CONC_REQUEST_ID', l_request_id);
2211
2212 begin
2213
2214 OPEN bus_ctx_cur;
2215 FETCH bus_ctx_cur INTO l_business_context;
2216 CLOSE bus_ctx_cur;
2217 -- Checking whether No Context record exists.
2218
2219 IF (l_business_context IS NULL) THEN
2220 -- Inserting record with 'NONE' AS the business context
2221 INSERT INTO ibw_context_interface_b cont
2222 (
2223 cont.context_interface_id
2224 ,cont.context_instance_value
2225 ,cont.context_instance_code
2226 ,cont.business_context
2227 ,cont.object_version_number
2228 ,cont.created_by
2229 ,cont.creation_date
2230 ,cont.last_updated_by
2231 ,cont.last_update_date
2232 ,cont.last_update_login,cont.program_id,cont.program_login_id,cont.program_application_id,cont.request_id )
2233 VALUES
2234 (ibw_context_interface_b_s1.nextval
2235 ,-999
2236 ,NULL
2237 ,'NONE',1,l_user_id,SYSDATE,l_user_id,SYSDATE,l_last_update_login,l_program_id,l_program_login_id,l_program_app_id,l_request_id);
2238
2239 -- Inserting record with 'NONE' AS the business context IN TL Table
2240
2241 INSERT INTO ibw_context_interface_tl cont_tl
2242 ( context_interface_id
2243 ,language
2244 ,context_instance_name
2245 ,source_lang
2246 ,object_version_number
2247 ,created_by
2248 ,creation_date
2249 ,last_updated_by
2250 ,last_update_date
2251 ,last_update_login
2252 ,program_id
2253 ,program_login_id
2254 ,program_application_id
2255 ,request_id )
2256 SELECT cont.context_interface_id context_interface_id
2257 ,lang.language_code language
2258 ,lookup.meaning context_instance_name
2259 ,USERENV('LANG') source_lang
2260 ,1 object_version_number
2261 ,l_user_id created_by
2262 ,SYSDATE creation_date
2263 ,l_user_id last_updated_by
2264 ,SYSDATE last_update_date
2265 ,l_last_update_login last_update_login
2266 ,l_program_id program_id
2267 ,l_program_login_id program_login_id
2268 ,l_program_app_id program_application_id
2269 ,l_request_id request_id
2270 FROM ibw_context_interface_b cont
2271 ,fnd_languages lang
2272 ,fnd_lookup_values lookup
2273 WHERE cont.context_instance_value = -999
2274 and lookup.lookup_type = 'IBW_BUSINESS_CONTEXT'
2275 and lookup.lookup_code = 'NONE'
2276 and lookup.LANGUAGE = lang.LANGUAGE_CODE
2277 AND lang.installed_flag in ('B','I');
2278
2279 END IF;
2280 COMMIT;
2281
2282 EXCEPTION
2283 WHEN OTHERS THEN
2284 ROLLBACK;
2285 --dbms_output.put_line('CONTEXT_LOAD:' || sqlerrm);
2286 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2287 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in Context Load Program - NONE Context :' || sqlerrm);
2288 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Context Load Program Return Status: - NONE Context' || x_return_status );
2289 END;
2290
2291
2292 -- For populating the IBW_CONTEXT_INTERFACE_B table with the Section related data
2293
2294 begin
2295 MERGE INTO ibw_context_interface_b cont
2296 USING (SELECT page.business_context
2297 ,sect.section_id
2298 ,sect.access_name
2299 FROM ibw_page_instances page
2300 -- To get the section related information for all the pages that have been tracked by web analytics.
2301 ,ibe_dsp_sections_vl sect
2302 -- To get the sections related information FROM this iStore view
2303 WHERE page.business_context = 'SECTION'
2304 -- Required AS we are taking into account pages with context of 'SECTION' other contexts eg 'PRODUCT' are hANDled separately
2305 AND page.business_context_value = sect.section_id
2306 group by
2307 page.business_context
2308 ,sect.section_id
2309 ,sect.access_name
2310 ) pagesect
2311 ON(
2312 cont.context_instance_value = pagesect.section_id
2313 AND cont.business_context = pagesect.business_context
2314 )
2315 WHEN MATCHED THEN
2316 UPDATE
2317 SET cont.context_instance_code = pagesect.access_name
2318 ,cont.last_updated_by = l_user_id
2319 ,cont.last_update_date = SYSDATE
2320 ,cont.object_version_number = cont.object_version_number + 1
2321 ,cont.last_update_login = l_last_update_login
2322 ,cont.program_id = l_program_id
2323 ,cont.program_login_id = l_program_login_id
2324 ,cont.program_application_id = l_program_app_id
2325 ,cont.request_id = l_request_id
2326 WHEN NOT MATCHED THEN
2327 INSERT(
2328 cont.context_interface_id
2329 ,cont.context_instance_value
2330 ,cont.context_instance_code
2331 ,cont.business_context
2332 ,cont.object_version_number
2333 ,cont.created_by
2334 ,cont.creation_date
2335 ,cont.last_updated_by
2336 ,cont.last_update_date
2337 ,cont.last_update_login
2338 ,cont.program_id
2339 ,cont.program_login_id
2340 ,cont.program_application_id
2341 ,cont.request_id)
2342 VALUES(
2343 IBW_CONTEXT_INTERFACE_B_S1.nextval
2344 ,pagesect.section_id
2345 ,pagesect.access_name
2346 ,pagesect.business_context
2347 ,1
2348 ,l_user_id
2349 ,SYSDATE
2350 ,l_user_id
2351 ,SYSDATE
2352 ,l_last_update_login
2353 ,l_program_id
2354 ,l_program_login_id
2355 ,l_program_app_id
2356 ,l_request_id);
2357 EXCEPTION
2358
2359 WHEN OTHERS THEN
2360 ROLLBACK;
2361 --dbms_output.put_line('CONTEXT_LOAD:' || sqlerrm);
2362 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2363 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in Context Load Program-Merge of ibw_context_interface_b :' || sqlerrm);
2364 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Context Load Program Return Status 1:' || x_return_status );
2365
2366 end;
2367
2368 -- For populating the IBW_CONTEXT_INTERFACE_TL table
2369
2370 -- Here the Primary Key is combination of context_interface_id (FROM ibw_context_interface_b) AND language column
2371
2372 begin
2373 MERGE INTO ibw_context_interface_tl cont_tl
2374 USING (SELECT
2375 cont.context_interface_id context_interface_id /* Changed the query for Performance Bug No 4777097 */
2376 ,sect.display_name context_instance_name /* SQLID: 14752675. Removed FND_LANGUAGES table from the */
2377 ,sect.language language /* query thereby avoiding MERGE CARTESIAN JOIN */
2378 ,USERENV('LANG') source_lang /* Chamge by gjothiku */
2379 FROM
2380 ibe_dsp_sections_tl sect
2381 ,ibw_context_interface_b cont
2382 WHERE
2383 cont.context_instance_value = sect.section_id
2384 ) conttl
2385 ON(
2386 cont_tl.context_interface_id = conttl.context_interface_id
2387 AND cont_tl.language = conttl.language
2388 )
2389 WHEN MATCHED THEN
2390 UPDATE
2391 SET cont_tl.context_instance_name = conttl.context_instance_name
2392 ,cont_tl.last_updated_by = l_user_id
2393 ,cont_tl.last_update_date = SYSDATE
2394 ,cont_tl.object_version_number = cont_tl.object_version_number + 1
2395 ,cont_tl.last_update_login = l_last_update_login
2396 ,cont_tl.program_id = l_program_id
2397 ,cont_tl.program_login_id = l_program_login_id
2398 ,cont_tl.program_application_id = l_program_app_id
2399 ,cont_tl.request_id = l_request_id
2400 WHEN NOT MATCHED THEN
2401 INSERT(
2402 cont_tl.context_interface_id
2403 ,cont_tl.language
2404 ,cont_tl.context_instance_name
2405 ,cont_tl.source_lang
2406 ,cont_tl.object_version_number
2407 ,cont_tl.created_by
2408 ,cont_tl.creation_date
2409 ,cont_tl.last_updated_by
2410 ,cont_tl.last_update_date
2411 ,cont_tl.last_update_login
2412 ,cont_tl.program_id
2413 ,cont_tl.program_login_id
2414 ,cont_tl.program_application_id
2415 ,cont_tl.request_id
2416 )
2417 VALUES(
2418 conttl.context_interface_id
2419 ,conttl.language
2420 ,conttl.context_instance_name
2421 ,conttl.source_lang
2422 ,1
2423 ,l_user_id
2424 ,SYSDATE
2425 ,l_user_id
2426 ,SYSDATE
2427 ,l_last_update_login ,l_program_id,l_program_login_id ,l_program_app_id ,l_request_id
2428 );
2429
2430 EXCEPTION
2431
2432 WHEN OTHERS THEN
2433 ROLLBACK;
2434 --dbms_output.put_line('CONTEXT_LOAD:' || sqlerrm);
2435 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2436 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in Context Load Program -Merge of ibw_context_interface_tl :' || sqlerrm);
2437 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Context Load Program Return Status 2:' || x_return_status );
2438 end;
2439 COMMIT;
2440
2441 EXCEPTION
2442
2443 WHEN OTHERS THEN
2444 ROLLBACK;
2445 --dbms_output.put_line('CONTEXT_LOAD:' || sqlerrm);
2446 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2447 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Exception in Context Load Program :' || sqlerrm);
2448 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Context Load Program Return Status:' || x_return_status );
2449 END CONTEXT_LOAD;
2450
2451 END IBW_OE_PVT;