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.HZ_DQM_DUP_ID_PKG dependencies on HZ_DUP_RESULTS

Line 70: * PROCEDURE update_hz_dup_results

66: ------------------------------------------------------------------------------------
67:
68:
69: /**
70: * PROCEDURE update_hz_dup_results
71: *
72: * DESCRIPTION
73: *
74: *

Line 91: -- update_hz_dup_results ::: This is a generic procedure, that would do a bulk update

87: *
88: */
89:
90: --------------------------------------------------------------------------------------
91: -- update_hz_dup_results ::: This is a generic procedure, that would do a bulk update
92: -- of hz_dup_results, using a passed in open cursor
93: --------------------------------------------------------------------------------------
94:
95:

Line 92: -- of hz_dup_results, using a passed in open cursor

88: */
89:
90: --------------------------------------------------------------------------------------
91: -- update_hz_dup_results ::: This is a generic procedure, that would do a bulk update
92: -- of hz_dup_results, using a passed in open cursor
93: --------------------------------------------------------------------------------------
94:
95:
96: PROCEDURE update_hz_dup_results (

Line 96: PROCEDURE update_hz_dup_results (

92: -- of hz_dup_results, using a passed in open cursor
93: --------------------------------------------------------------------------------------
94:
95:
96: PROCEDURE update_hz_dup_results (
97: p_cur IN EntityCur )
98: is
99: l_limit NUMBER := 200;
100: l_last_fetch BOOLEAN := FALSE;

Line 126: UPDATE HZ_DUP_RESULTS A

122:
123: BEGIN
124:
125: FORALL I in H_FID.FIRST..H_FID.LAST
126: UPDATE HZ_DUP_RESULTS A
127: SET A.SCORE = A.SCORE + H_SCORE(I)
128: WHERE
129: ( A.FID = H_FID(I) and
130: A.TID = H_TID(I)

Line 143: FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_DUP_RESULTS');

139: ---------- exception block ---------------
140: EXCEPTION
141: WHEN OTHERS THEN
142: FND_MESSAGE.SET_NAME('AR', 'HZ_DQM_API_ERROR');
143: FND_MESSAGE.SET_TOKEN('PROC','UPDATE_HZ_DUP_RESULTS');
144: FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM );
145: FND_MSG_PUB.ADD;
146: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
147: END;

Line 162: from hz_dup_results;

158: p_count number ;
159: BEGIN
160:
161: select count(1) into p_count
162: from hz_dup_results;
163:
164: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
165: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before sanitization '|| p_count );
166: FND_FILE.put_line(FND_FILE.log,'Parties ::: Delete based on subset sql');

Line 165: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before sanitization '|| p_count );

161: select count(1) into p_count
162: from hz_dup_results;
163:
164: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
165: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before sanitization '|| p_count );
166: FND_FILE.put_line(FND_FILE.log,'Parties ::: Delete based on subset sql');
167: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of delete '||to_char(sysdate,'hh24:mi:ss'));
168:
169:

Line 184: EXECUTE IMMEDIATE 'delete from hz_dup_results a where ' ||

180: -- Filter only if the flag is 'Y' and the subset sql is not null
181:
182: IF p_within_subset = 'Y' and p_subset_sql is not null
183: THEN
184: EXECUTE IMMEDIATE 'delete from hz_dup_results a where ' ||
185: 'not exists ' ||
186: '(Select 1 from hz_dup_results b, hz_parties parties ' ||
187: 'where b.ord_tid = parties.party_id ' ||
188: 'and ' ||

Line 186: '(Select 1 from hz_dup_results b, hz_parties parties ' ||

182: IF p_within_subset = 'Y' and p_subset_sql is not null
183: THEN
184: EXECUTE IMMEDIATE 'delete from hz_dup_results a where ' ||
185: 'not exists ' ||
186: '(Select 1 from hz_dup_results b, hz_parties parties ' ||
187: 'where b.ord_tid = parties.party_id ' ||
188: 'and ' ||
189: p_subset_sql || ')' ;
190: END IF;

Line 193: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);

189: p_subset_sql || ')' ;
190: END IF;
191:
192: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
193: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);
194: FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of delete '||to_char(sysdate,'hh24:mi:ss'));
195:
196: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
197: FND_FILE.put_line(FND_FILE.log,'Parties ::: Beginning delete on HZ_DUP_RESULTS, based on threshold, reversed pairs and indirect transitives '||SQL%ROWCOUNT);

Line 197: FND_FILE.put_line(FND_FILE.log,'Parties ::: Beginning delete on HZ_DUP_RESULTS, based on threshold, reversed pairs and indirect transitives '||SQL%ROWCOUNT);

193: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);
194: FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of delete '||to_char(sysdate,'hh24:mi:ss'));
195:
196: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
197: FND_FILE.put_line(FND_FILE.log,'Parties ::: Beginning delete on HZ_DUP_RESULTS, based on threshold, reversed pairs and indirect transitives '||SQL%ROWCOUNT);
198:
199:
200: -- At the end of this, we would have the following:
201:

Line 207: delete from hz_dup_results a

203: -- The pair with fid < tid, if both the pairs have the same score.
204: -- The pair with the higher score, if in case, the pairs of different scores.
205:
206:
207: delete from hz_dup_results a
208: where
209: -- delete anything less than the threshold
210: a.score < p_threshold
211: or

Line 215: (Select 1 from hz_dup_results b

211: or
212: -- if scores are same, delete the one with highest source
213: -- or if scores are different, delete the one with lower score
214: (exists
215: (Select 1 from hz_dup_results b
216: where
217:
218: (
219: (

Line 233: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);

229: )
230: ));
231:
232: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
233: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties deleted from HZ_DUP_RESULTS '||SQL%ROWCOUNT);
234: FND_FILE.put_line(FND_FILE.log,'Parties ::: End time of delete '||to_char(sysdate,'hh24:mi:ss'));
235:
236:
237:

Line 695: -- THESE ARE ALL PRECISELY THE ORD_FIDs IN HZ_DUP_RESULTS WHICH ARE NOT ORD_TIDs

691: p_count number ;
692: BEGIN
693:
694: -- FIRST, REPORT WINNER PARTIES TO DUP SETS
695: -- THESE ARE ALL PRECISELY THE ORD_FIDs IN HZ_DUP_RESULTS WHICH ARE NOT ORD_TIDs
696: -- OF ANY ROW AND OCCUR AT ODD LEVELS IN THE CONNECT BY.
697:
698: ----------------------------------------------------
699: --- EXAMPLE :

Line 711: from hz_dup_results ;

707: -- INTO ACCOUNT.
708: -----------------------------------------------------
709:
710: select count(1) into p_count
711: from hz_dup_results ;
712:
713: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
714: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before reporting '|| p_count );
715: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));

Line 714: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before reporting '|| p_count );

710: select count(1) into p_count
711: from hz_dup_results ;
712:
713: FND_FILE.put_line(FND_FILE.log,'------------------------------------------------------------');
714: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties in HZ_DUP_RESULTS before reporting '|| p_count );
715: FND_FILE.put_line(FND_FILE.log,'Parties ::: Begin time of insert '||to_char(sysdate,'hh24:mi:ss'));
716:
717:
718: insert into hz_dup_sets ( winner_party_id, dup_set_id, dup_batch_id,

Line 728: from hz_dup_results d

724: hz_utility_pub.user_id
725: from
726: (
727: select distinct d.ord_fid as win_party_id, level as levelu
728: from hz_dup_results d
729: start with d.ord_fid not in
730: (
731: select c.ord_tid
732: from hz_dup_results c

Line 732: from hz_dup_results c

728: from hz_dup_results d
729: start with d.ord_fid not in
730: (
731: select c.ord_tid
732: from hz_dup_results c
733: )
734: connect by prior ord_tid = ord_fid
735: )
736: where mod(levelu, 2) = 1 ;

Line 767: from hz_dup_sets a, hz_dup_results b

763: select b.ord_tid, a.dup_set_id, 0, 0, b.score ,decode( sign(b.score - p_auto_merge_threshold),-1,'N','Y'),
764: hz_utility_pub.created_by,hz_utility_pub.creation_date,
765: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
766: hz_utility_pub.user_id,a.dup_batch_id
767: from hz_dup_sets a, hz_dup_results b
768: where a.dup_batch_id = p_batch_id
769: and a.winner_party_id = b.ord_fid ;
770:
771: FND_FILE.put_line(FND_FILE.log,'Parties ::: Number of parties inserted to Dup Set Parties'||SQL%ROWCOUNT);

Line 904: /* insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)

900: END IF;
901: -- dbms_output.put_line('start '||start_idx);
902: -- dbms_output.put_line('end '||end_idx);
903: -- dbms_output.put_line('limit '||chunk_limit);
904: /* insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)
905: values (-1,-1,-1,-1,chunk_limit,cnt,sysdate);
906: */
907:
908: -- truncate chunk table before inserting into it

Line 918: EXISTS (Select 1 from HZ_DUP_RESULTS t

914:
915: x_rows_in_chunk:=SQL%ROWCOUNT;
916:
917: DELETE FROM hz_dup_worker_chunk_gt WHERE
918: EXISTS (Select 1 from HZ_DUP_RESULTS t
919: WHERE t.tid = party_id);
920: x_rows_in_chunk:=x_rows_in_chunk-SQL%ROWCOUNT;
921: start_idx:=start_idx+chunk_limit;
922: ELSE

Line 952: insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)

948: END IF;
949:
950:
951: /*
952: insert into hz_dup_results(fid, tid, ord_fid, ord_tid, score,chunk_num,chunk_stime)
953: values (-1,-1,-1,-1,chunk_limit,cnt,sysdate);
954: */
955: -- truncate chunk table before inserting into it
956: l_owner := HZ_IMP_DQM_STAGE.get_owner_name('HZ_DUP_WORKER_CHUNK_GT', 'TABLE');

Line 972: EXISTS (Select 1 from HZ_DUP_RESULTS t

968: -- ( for example if 1 finds 2 as a duplicate
969: -- we would like to avoid doing anything with 2, if 2 indeed happens
970: -- to be allocated to this worker )
971: DELETE FROM hz_dup_worker_chunk_gt WHERE
972: EXISTS (Select 1 from HZ_DUP_RESULTS t
973: WHERE t.tid = party_id);
974:
975: x_rows_in_chunk:=x_rows_in_chunk-SQL%ROWCOUNT;
976: FND_FILE.put_line(FND_FILE.log,'Number of rows in chunk table ' || x_rows_in_chunk);

Line 1120: -- of hz_dup_results, using a passed in open cursor

1116: */
1117:
1118: --------------------------------------------------------------------------------------
1119: -- update_hz_imp_dup_parties ::: This is a generic procedure, that would do a bulk update
1120: -- of hz_dup_results, using a passed in open cursor
1121: --------------------------------------------------------------------------------------
1122:
1123:
1124: PROCEDURE update_hz_imp_dup_parties (

Line 2276: update hz_dup_results a

2272: l_yn number ;
2273: BEGIN
2274: -- Update all intersecting dup pairs viz, (a,b) intersects with (c,d) if one of
2275: -- a = c, a = d, b = c, b = d is true.
2276: update hz_dup_results a
2277: set flag = 'D'
2278: where exists
2279: (select 1
2280: from hz_dup_results b

Line 2280: from hz_dup_results b

2276: update hz_dup_results a
2277: set flag = 'D'
2278: where exists
2279: (select 1
2280: from hz_dup_results b
2281: where ( a.fid = b.fid and a.tid <> b.tid)
2282: or (a.tid = b.tid and a.fid <> b.fid)
2283: or (a.fid = b.tid)
2284: or (a.tid = b.fid)

Line 2311: delete from hz_dup_results a

2307: -- The pair with the higher score, if in case, the pairs of different scores.
2308:
2309:
2310: -- delete the smallest source, if scores are equal
2311: delete from hz_dup_results a
2312: where
2313: a.score < p_threshold
2314: or
2315: (exists

Line 2316: (Select 1 from hz_dup_results b

2312: where
2313: a.score < p_threshold
2314: or
2315: (exists
2316: (Select 1 from hz_dup_results b
2317: where a.fid=b.tid and b.fid=a.tid and a.score = b.score)
2318: and a.fid < a.tid );
2319:
2320: -- delete the one with the smallest score, if scores are not equal

Line 2321: delete from hz_dup_results a

2317: where a.fid=b.tid and b.fid=a.tid and a.score = b.score)
2318: and a.fid < a.tid );
2319:
2320: -- delete the one with the smallest score, if scores are not equal
2321: delete from hz_dup_results a
2322: where
2323: a.score < p_threshold
2324: or
2325: exists

Line 2326: (Select 1 from hz_dup_results b

2322: where
2323: a.score < p_threshold
2324: or
2325: exists
2326: (Select 1 from hz_dup_results b
2327: where a.fid=b.tid and b.fid=a.tid and a.score < b.score) ;
2328:
2329: ------------------------------------------------------------------------------------------------------
2330: -- Step 2 - IDENTIFY DUP SETS AND REPORT THEM APPROPRIATELY, TO ANOTHER TEMP TABLE

Line 2355: from hz_dup_results a

2351: IS
2352: -- Get a row that intersects with the passed in fid and tid and already has
2353: -- the stamp of the master party id
2354: select a.fid, a.tid , a.flag, a.dup_set_id
2355: from hz_dup_results a
2356: where (a.flag <> 'D') and
2357: (a.fid = p_fid or a.fid = p_tid or a.tid = p_fid or a.tid = p_tid) and
2358: rownum = 1
2359: order by a.flag ;

Line 2392: update hz_dup_results a

2388: END IF;
2389:
2390: -- Stamp all intersecting rows (including the passed in row itself), that still have a 'D' flag,
2391: -- with the stamp of the Master and also populate the dup set id column
2392: update hz_dup_results a
2393: set flag = master_party_id, dup_set_id = x_dup_set_id
2394: where (a.flag = 'D') and
2395: (a.fid = p_fid or a.fid = p_tid or a.tid = p_fid or a.tid = p_tid) ;
2396:

Line 2411: update hz_dup_results a

2407: BEGIN
2408:
2409: -- Stamp the dup set id column of all rows with flag = 'ND', with sequence obtained from
2410: -- HZ_DUP_SETS
2411: update hz_dup_results a
2412: set dup_set_id = HZ_MERGE_BATCH_S.nextval
2413: where a.flag = 'ND' ;
2414: return 0 ;
2415: END stamp_trivial_dup_sets ;

Line 2434: from hz_dup_results

2430: temp_rowid rowid;
2431: CURSOR dup_set_cur
2432: IS
2433: select fid, tid, dup_set_id, rowid
2434: from hz_dup_results
2435: where flag = 'D'
2436: and rownum = 1
2437: order by flag ;
2438:

Line 2507: from hz_dup_results

2503: select dup_set_id, p_batch_id, fid,
2504: 'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
2505: hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
2506: hz_utility_pub.user_id
2507: from hz_dup_results
2508: where flag = 'ND' ;
2509:
2510: -- Insert winner and its only duplicate, into HZ_DUP_SET_PARTIES
2511: insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,

Line 2518: from hz_dup_results

2514: select fid, dup_set_id, 0, p_batch_id, score, decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2515: hz_utility_pub.created_by,hz_utility_pub.creation_date,
2516: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2517: hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2518: from hz_dup_results
2519: where flag = 'ND';
2520:
2521: insert into hz_dup_set_parties (dup_party_id,dup_set_id,merge_seq_id,
2522: merge_batch_id,score,merge_flag, created_by,creation_date,last_update_login,

Line 2528: from hz_dup_results

2524: select tid, dup_set_id, 0,p_batch_id, score,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2525: hz_utility_pub.created_by,hz_utility_pub.creation_date,
2526: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2527: hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2528: from hz_dup_results
2529: where flag = 'ND';
2530:
2531: ------------------------------------------------------
2532: -- Step2: Report non trivial dup sets

Line 2543: from hz_dup_results

2539: select distinct dup_set_id, p_batch_id, flag ,
2540: 'SYSBATCH', 'PARTY_MERGE', hz_utility_pub.created_by, hz_utility_pub.creation_date,
2541: hz_utility_pub.last_update_login, hz_utility_pub.last_update_date,
2542: hz_utility_pub.user_id
2543: from hz_dup_results
2544: where flag <> 'ND' ;
2545:
2546: -- Insert winner party and all its duplicates into HZ_DUP_SET_PARTIES
2547:

Line 2555: from hz_dup_results

2551: select distinct to_number(flag), dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2552: hz_utility_pub.created_by,hz_utility_pub.creation_date,
2553: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2554: hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2555: from hz_dup_results
2556: where flag <> 'ND'
2557: union
2558: select distinct fid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2559: hz_utility_pub.created_by,hz_utility_pub.creation_date,

Line 2562: from hz_dup_results

2558: select distinct fid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2559: hz_utility_pub.created_by,hz_utility_pub.creation_date,
2560: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2561: hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2562: from hz_dup_results
2563: where flag <> 'ND'
2564: union
2565: select distinct tid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2566: hz_utility_pub.created_by,hz_utility_pub.creation_date,

Line 2569: from hz_dup_results

2565: select distinct tid, dup_set_id, 0, p_batch_id, score ,decode( sign(score - x_auto_merge_threshold),-1,'N','Y'),
2566: hz_utility_pub.created_by,hz_utility_pub.creation_date,
2567: hz_utility_pub.last_update_login,hz_utility_pub.last_update_date,
2568: hz_utility_pub.user_id,p_batch_id --Bug No: 4244529
2569: from hz_dup_results
2570: where flag <> 'ND' ;
2571:
2572: return 0 ;
2573: END report_duplicates ;