[Home] [Help]
486: ) IS
487:
488: CURSOR check_null_set IS
489: SELECT DISTINCT a.entity_name
490: FROM hz_match_rule_secondary s, hz_trans_attributes_vl a
491: WHERE a.attribute_id = s.attribute_id
492: AND s.match_rule_id = p_rule_id
493: MINUS
494: SELECT DISTINCT a.entity_name
504: AND f.function_id = pt.function_id
505: AND f.ACTIVE_FLAG = 'N'
506: UNION
507: SELECT 1
508: FROM hz_match_rule_secondary s, hz_secondary_trans pt, hz_trans_functions_vl f
509: WHERE s.match_rule_id = p_rule_id
510: AND pt.SECONDARY_ATTRIBUTE_ID = s.SECONDARY_ATTRIBUTE_ID
511: AND f.function_id = pt.function_id
512: AND f.ACTIVE_FLAG = 'N';
2282: FIRST := TRUE;
2283: FOR TX IN (
2284: SELECT f.staged_attribute_column
2285: FROM hz_trans_functions_vl f, hz_secondary_trans st,
2286: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
2287: WHERE sa.match_rule_id = p_rule_id
2288: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
2289: AND st.function_id = f.function_id
2290: AND a.attribute_id = sa.attribute_id
2312: l(' l_current_score := 0;');
2313: FOR SECATTRS IN (
2314: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
2315: FROM HZ_TRANS_ATTRIBUTES_VL a,
2316: HZ_MATCH_RULE_SECONDARY s
2317: WHERE s.match_rule_id = p_rule_id
2318: AND s.attribute_id = a.attribute_id
2319: AND a.entity_name = p_entity ) LOOP
2320: FIRST := TRUE;
2390: FIRST := TRUE;
2391: FOR TX IN (
2392: SELECT f.staged_attribute_column
2393: FROM hz_trans_functions_vl f, hz_secondary_trans st,
2394: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
2395: WHERE sa.match_rule_id = p_rule_id
2396: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
2397: AND st.function_id = f.function_id
2398: AND a.attribute_id = sa.attribute_id
2410: FOR SECATTRS IN (
2411: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id,
2412: USER_DEFINED_ATTRIBUTE_NAME
2413: FROM HZ_TRANS_ATTRIBUTES_VL a,
2414: HZ_MATCH_RULE_SECONDARY s
2415: WHERE s.match_rule_id = p_rule_id
2416: AND s.attribute_id = a.attribute_id
2417: AND a.entity_name = p_entity ) LOOP
2418: FIRST := TRUE;
2517: FOR SECATTRS IN (
2518: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id,
2519: USER_DEFINED_ATTRIBUTE_NAME, SOURCE_TABLE, CUSTOM_ATTRIBUTE_PROCEDURE
2520: FROM HZ_TRANS_ATTRIBUTES_VL a,
2521: HZ_MATCH_RULE_SECONDARY s
2522: WHERE s.match_rule_id = p_rule_id
2523: AND s.attribute_id = a.attribute_id
2524: AND a.entity_name = entity) LOOP
2525: l(' IF p_attribute = '''||SECATTRS.ATTRIBUTE_NAME||''' THEN');
2846: FIRST := TRUE;
2847: FOR TX IN (
2848: SELECT f.staged_attribute_column, a.attribute_name, f.procedure_name
2849: FROM hz_trans_functions_vl f, hz_secondary_trans st,
2850: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
2851: WHERE sa.match_rule_id = p_rule_id
2852: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
2853: AND st.function_id = f.function_id
2854: AND a.attribute_id = sa.attribute_id
2942: FROM HZ_MATCH_RULES_VL
2943: WHERE match_rule_id = p_rule_id;
2944:
2945: SELECT nvl(SUM(SCORE),1) INTO l_max_score
2946: FROM HZ_MATCH_RULE_SECONDARY
2947: WHERE match_rule_id = p_rule_id;
2948:
2949: --bug 5878732
2950: IF l_purpose in ('S','W') AND Nvl(l_match_threshold,0)<>0 THEN
3135: FIRST := TRUE;
3136: FOR TX IN (
3137: SELECT f.staged_attribute_column
3138: FROM hz_trans_functions_vl f, hz_secondary_trans st,
3139: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
3140: WHERE sa.match_rule_id = p_rule_id
3141: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
3142: AND st.function_id = f.function_id
3143: AND a.attribute_id = sa.attribute_id
3166: l(' END IF;');
3167: FOR SECATTRS IN (
3168: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
3169: FROM HZ_TRANS_ATTRIBUTES_VL a,
3170: HZ_MATCH_RULE_SECONDARY s
3171: WHERE s.match_rule_id = p_rule_id
3172: AND s.attribute_id = a.attribute_id
3173: AND a.entity_name = 'PARTY') LOOP
3174: FIRST := TRUE;
3473: FOR PRIMATTRS IN (
3474: SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME, nvl(SCORE,0) SCORE
3475: FROM HZ_TRANS_ATTRIBUTES_VL a,
3476: HZ_MATCH_RULE_PRIMARY p,
3477: HZ_MATCH_RULE_SECONDARY s
3478: WHERE p.match_rule_id = p_rule_id
3479: AND s.match_rule_id (+) = p_rule_id
3480: AND s.attribute_id (+) = a.attribute_id
3481: AND p.attribute_id = a.attribute_id
3663: IF l_purpose IN ('S','W') THEN
3664: l_party_name_score:='decode(TX8,g_party_stage_rec.TX8||'' '',100,90)';
3665: ELSE
3666: BEGIN
3667: SELECT to_char(score) INTO l_party_name_score from HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
3668: WHERE a.attribute_id = s.attribute_id
3669: AND s.match_rule_id = p_rule_id
3670: AND attribute_name = 'PARTY_NAME';
3671: EXCEPTION
4880: l('');
4881: FOR TX IN (
4882: SELECT distinct f.staged_attribute_column
4883: FROM hz_trans_functions_vl f, hz_secondary_trans st,
4884: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
4885: WHERE sa.match_rule_id = p_rule_id
4886: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
4887: AND st.function_id = f.function_id
4888: AND a.attribute_id = sa.attribute_id) LOOP
5860: l('');
5861: FOR TX IN (
5862: SELECT distinct f.staged_attribute_column
5863: FROM hz_trans_functions_vl f, hz_secondary_trans st,
5864: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
5865: WHERE sa.match_rule_id = p_rule_id
5866: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
5867: AND st.function_id = f.function_id
5868: AND a.attribute_id = sa.attribute_id) LOOP
7353: UNION
7354:
7355: SELECT a.attribute_id, a.attribute_name, a.entity_name
7356: FROM HZ_TRANS_ATTRIBUTES_VL a,
7357: HZ_MATCH_RULE_SECONDARY s
7358: WHERE s.match_rule_id = p_rule_id
7359: AND s.attribute_id = a.attribute_id) LOOP
7360: l(' IF AttrVals.EXISTS('||ATTRS.attribute_id||') THEN');
7361: IF ATTRS.entity_name='PARTY' THEN
7487: l('');
7488: FOR TX IN (
7489: SELECT distinct f.staged_attribute_column
7490: FROM hz_trans_functions_vl f, hz_secondary_trans st,
7491: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
7492: WHERE sa.match_rule_id = p_rule_id
7493: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
7494: AND st.function_id = f.function_id
7495: AND (a.ENTITY_NAME='PARTY_SITES' OR a.ENTITY_NAME='CONTACT_POINTS')
7761: l('');
7762: FOR TX IN (
7763: SELECT distinct f.staged_attribute_column
7764: FROM hz_trans_functions_vl f, hz_secondary_trans st,
7765: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
7766: WHERE sa.match_rule_id = p_rule_id
7767: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
7768: AND st.function_id = f.function_id
7769: AND (a.ENTITY_NAME='CONTACTS' OR a.ENTITY_NAME='CONTACT_POINTS')
8029: l('');
8030: FOR TX IN (
8031: SELECT distinct f.staged_attribute_column
8032: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8033: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8034: WHERE sa.match_rule_id = p_rule_id
8035: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8036: AND st.function_id = f.function_id
8037: AND a.ENTITY_NAME='CONTACT_POINTS'
8216: l('');
8217: FOR TX IN (
8218: SELECT distinct f.staged_attribute_column
8219: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8220: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8221: WHERE sa.match_rule_id = p_rule_id
8222: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8223: AND st.function_id = f.function_id
8224: AND a.attribute_id = sa.attribute_id) LOOP
8323: l(' l_max_idx := l_match_idx;');
8324: FOR TX IN (
8325: SELECT distinct f.staged_attribute_column
8326: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8327: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8328: WHERE sa.match_rule_id = p_rule_id
8329: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8330: AND st.function_id = f.function_id
8331: AND a.entity_name = 'PARTY_SITES'
8356: l(' l_max_idx := l_match_idx;');
8357: FOR TX IN (
8358: SELECT distinct f.staged_attribute_column
8359: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8360: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8361: WHERE sa.match_rule_id = p_rule_id
8362: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8363: AND st.function_id = f.function_id
8364: AND a.entity_name = 'CONTACTS'
8393: l(' l_max_idx := l_match_idx;');
8394: FOR TX IN (
8395: SELECT distinct f.staged_attribute_column
8396: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8397: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8398: WHERE sa.match_rule_id = p_rule_id
8399: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8400: AND st.function_id = f.function_id
8401: AND a.entity_name = 'CONTACT_POINTS'
8784: FROM HZ_MATCH_RULES_VL
8785: WHERE match_rule_id = p_rule_id;
8786:
8787: SELECT nvl(SUM(SCORE),1) INTO l_max_score
8788: FROM HZ_MATCH_RULE_SECONDARY
8789: WHERE match_rule_id = p_rule_id;
8790:
8791: l('CREATE or REPLACE PACKAGE BODY ' || p_pkg_name || ' AS');
8792: l('/*=======================================================================+');
8867: FIRST := TRUE;
8868: FOR TX IN (
8869: SELECT f.staged_attribute_column
8870: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8871: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8872: WHERE sa.match_rule_id = p_rule_id
8873: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8874: AND st.function_id = f.function_id
8875: AND a.attribute_id = sa.attribute_id
8896: l(' END IF;');
8897: FOR SECATTRS IN (
8898: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
8899: FROM HZ_TRANS_ATTRIBUTES_VL a,
8900: HZ_MATCH_RULE_SECONDARY s
8901: WHERE s.match_rule_id = p_rule_id
8902: AND s.attribute_id = a.attribute_id
8903: AND a.entity_name = 'PARTY') LOOP
8904: FIRST := TRUE;
9116: FOR PRIMATTRS IN (
9117: SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME, nvl(SCORE,0) SCORE
9118: FROM HZ_TRANS_ATTRIBUTES_VL a,
9119: HZ_MATCH_RULE_PRIMARY p,
9120: HZ_MATCH_RULE_SECONDARY s
9121: WHERE p.match_rule_id = p_rule_id
9122: AND s.match_rule_id (+) = p_rule_id
9123: AND s.attribute_id (+) = a.attribute_id
9124: AND p.attribute_id = a.attribute_id
9750: l('');
9751: FOR TX IN (
9752: SELECT distinct f.staged_attribute_column
9753: FROM hz_trans_functions_vl f, hz_secondary_trans st,
9754: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
9755: WHERE sa.match_rule_id = p_rule_id
9756: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
9757: AND st.function_id = f.function_id
9758: AND a.attribute_id = sa.attribute_id) LOOP
10495: l('');
10496: FOR TX IN (
10497: SELECT distinct f.staged_attribute_column
10498: FROM hz_trans_functions_vl f, hz_secondary_trans st,
10499: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
10500: WHERE sa.match_rule_id = p_rule_id
10501: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
10502: AND st.function_id = f.function_id
10503: AND a.attribute_id = sa.attribute_id) LOOP
11759: UNION
11760:
11761: SELECT a.attribute_id, a.attribute_name, a.entity_name
11762: FROM HZ_TRANS_ATTRIBUTES_VL a,
11763: HZ_MATCH_RULE_SECONDARY s
11764: WHERE s.match_rule_id = p_rule_id
11765: AND s.attribute_id = a.attribute_id) LOOP
11766: l(' IF AttrVals.EXISTS('||ATTRS.attribute_id||') THEN');
11767: IF ATTRS.entity_name='PARTY' THEN
11890: l('');
11891: FOR TX IN (
11892: SELECT distinct f.staged_attribute_column
11893: FROM hz_trans_functions_vl f, hz_secondary_trans st,
11894: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
11895: WHERE sa.match_rule_id = p_rule_id
11896: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
11897: AND st.function_id = f.function_id
11898: AND (a.ENTITY_NAME='PARTY_SITES' OR a.ENTITY_NAME='CONTACT_POINTS')
12131: l('');
12132: FOR TX IN (
12133: SELECT distinct f.staged_attribute_column
12134: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12135: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12136: WHERE sa.match_rule_id = p_rule_id
12137: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12138: AND st.function_id = f.function_id
12139: AND (a.ENTITY_NAME='CONTACTS' OR a.ENTITY_NAME='CONTACT_POINTS')
12363: l('');
12364: FOR TX IN (
12365: SELECT distinct f.staged_attribute_column
12366: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12367: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12368: WHERE sa.match_rule_id = p_rule_id
12369: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12370: AND st.function_id = f.function_id
12371: AND a.ENTITY_NAME='CONTACT_POINTS'
12529: l('');
12530: FOR TX IN (
12531: SELECT distinct f.staged_attribute_column
12532: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12533: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12534: WHERE sa.match_rule_id = p_rule_id
12535: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12536: AND st.function_id = f.function_id
12537: AND a.attribute_id = sa.attribute_id) LOOP
12619: l(' l_max_idx := l_match_idx;');
12620: FOR TX IN (
12621: SELECT distinct f.staged_attribute_column
12622: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12623: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12624: WHERE sa.match_rule_id = p_rule_id
12625: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12626: AND st.function_id = f.function_id
12627: AND a.entity_name = 'PARTY_SITES'
12652: l(' l_max_idx := l_match_idx;');
12653: FOR TX IN (
12654: SELECT distinct f.staged_attribute_column
12655: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12656: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12657: WHERE sa.match_rule_id = p_rule_id
12658: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12659: AND st.function_id = f.function_id
12660: AND a.entity_name = 'CONTACTS'
12685: l(' l_max_idx := l_match_idx;');
12686: FOR TX IN (
12687: SELECT distinct f.staged_attribute_column
12688: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12689: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12690: WHERE sa.match_rule_id = p_rule_id
12691: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12692: AND st.function_id = f.function_id
12693: AND a.entity_name = 'CONTACT_POINTS'
12859: l(' x_entered_max_score:=0;');
12860: for SECATTRS IN (
12861: SELECT a.ATTRIBUTE_NAME, SCORE
12862: FROM HZ_TRANS_ATTRIBUTES_VL a,
12863: HZ_MATCH_RULE_SECONDARY sattr
12864: WHERE sattr.MATCH_RULE_ID = p_rule_id
12865: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12866: AND a.ENTITY_NAME = 'PARTY') LOOP
12867: l(' IF p_search_rec.'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
12890: f.PROCEDURE_NAME,
12891: f.STAGED_ATTRIBUTE_COLUMN
12892: FROM HZ_TRANS_FUNCTIONS_VL f,
12893: HZ_TRANS_ATTRIBUTES_VL a,
12894: HZ_MATCH_RULE_SECONDARY sattr,
12895: HZ_SECONDARY_TRANS sfunc
12896: WHERE sattr.MATCH_RULE_ID = p_rule_id
12897: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12898: AND a.ENTITY_NAME = 'PARTY'
13039: END IF;
13040: for SECATTRS IN (
13041: SELECT a.ATTRIBUTE_NAME, SCORE, nvl(a.denorm_flag,'N') DENORM_FLAG
13042: FROM HZ_TRANS_ATTRIBUTES_VL a,
13043: HZ_MATCH_RULE_SECONDARY sattr
13044: WHERE sattr.MATCH_RULE_ID = p_rule_id
13045: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13046: AND a.ENTITY_NAME = p_entity) LOOP
13047: l(' IF p_search_list(I).'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
13105: f.PROCEDURE_NAME,
13106: f.STAGED_ATTRIBUTE_COLUMN
13107: FROM HZ_TRANS_FUNCTIONS_VL f,
13108: HZ_TRANS_ATTRIBUTES_VL a,
13109: HZ_MATCH_RULE_SECONDARY sattr,
13110: HZ_SECONDARY_TRANS sfunc
13111: WHERE sattr.MATCH_RULE_ID = p_rule_id
13112: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13113: AND a.ENTITY_NAME = p_entity
13219:
13220: SELECT a.ATTRIBUTE_ID, a.ATTRIBUTE_NAME
13221: FROM HZ_TRANS_FUNCTIONS_VL f,
13222: HZ_TRANS_ATTRIBUTES_VL a,
13223: HZ_MATCH_RULE_SECONDARY sattr,
13224: HZ_SECONDARY_TRANS sfunc
13225: WHERE sattr.MATCH_RULE_ID = p_rule_id
13226: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13227: AND a.ENTITY_NAME = p_entity
13316: END IF;
13317: for SECATTRS IN (
13318: SELECT a.ATTRIBUTE_NAME, SCORE
13319: FROM HZ_TRANS_ATTRIBUTES_VL a,
13320: HZ_MATCH_RULE_SECONDARY sattr
13321: WHERE sattr.MATCH_RULE_ID = p_rule_id
13322: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13323: AND a.ENTITY_NAME = p_entity) LOOP
13324: l(' IF p_search_list(I).'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
13351: f.PROCEDURE_NAME,
13352: f.STAGED_ATTRIBUTE_COLUMN
13353: FROM HZ_TRANS_FUNCTIONS_VL f,
13354: HZ_TRANS_ATTRIBUTES_VL a,
13355: HZ_MATCH_RULE_SECONDARY sattr,
13356: HZ_SECONDARY_TRANS sfunc
13357: WHERE sattr.MATCH_RULE_ID = p_rule_id
13358: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13359: AND a.ENTITY_NAME = p_entity
13419: f.PROCEDURE_NAME,
13420: f.STAGED_ATTRIBUTE_COLUMN
13421: FROM HZ_TRANS_FUNCTIONS_VL f,
13422: HZ_TRANS_ATTRIBUTES_VL a,
13423: HZ_MATCH_RULE_SECONDARY sattr,
13424: HZ_SECONDARY_TRANS sfunc
13425: WHERE sattr.MATCH_RULE_ID = p_rule_id
13426: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13427: AND a.ENTITY_NAME = p_entity
13543: l(' x_entered_max_score:=0;');
13544: for SECATTRS IN (
13545: SELECT a.ATTRIBUTE_NAME, SCORE
13546: FROM HZ_TRANS_ATTRIBUTES_VL a,
13547: HZ_MATCH_RULE_SECONDARY sattr
13548: WHERE sattr.MATCH_RULE_ID = p_rule_id
13549: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13550: AND a.ENTITY_NAME = 'PARTY') LOOP
13551: l(' IF p_search_rec.'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
13574: f.PROCEDURE_NAME,
13575: f.STAGED_ATTRIBUTE_COLUMN
13576: FROM HZ_TRANS_FUNCTIONS_VL f,
13577: HZ_TRANS_ATTRIBUTES_VL a,
13578: HZ_MATCH_RULE_SECONDARY sattr,
13579: HZ_SECONDARY_TRANS sfunc
13580: WHERE sattr.MATCH_RULE_ID = p_rule_id
13581: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13582: AND a.ENTITY_NAME = 'PARTY'
13866: FIRST := TRUE;
13867: FOR SECATTRS IN (
13868: SELECT ATTRIBUTE_NAME
13869: FROM HZ_TRANS_ATTRIBUTES_VL a,
13870: HZ_MATCH_RULE_SECONDARY s
13871: WHERE s.match_rule_id = p_rule_id
13872: AND a.ENTITY_NAME = 'PARTY_SITES'
13873: AND s.attribute_id = a.attribute_id)
13874: LOOP
13893: FIRST := TRUE;
13894: FOR SECATTRS IN (
13895: SELECT ATTRIBUTE_NAME
13896: FROM HZ_TRANS_ATTRIBUTES_VL a,
13897: HZ_MATCH_RULE_SECONDARY s
13898: WHERE s.match_rule_id = p_rule_id
13899: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13900: AND s.attribute_id = a.attribute_id)
13901: LOOP
14003: FIRST := TRUE;
14004: FOR SECATTRS IN (
14005: SELECT ATTRIBUTE_NAME
14006: FROM HZ_TRANS_ATTRIBUTES_VL a,
14007: HZ_MATCH_RULE_SECONDARY s
14008: WHERE s.match_rule_id = p_rule_id
14009: AND a.ENTITY_NAME = 'CONTACTS'
14010: AND s.attribute_id = a.attribute_id)
14011: LOOP
14031: FIRST := TRUE;
14032: FOR SECATTRS IN (
14033: SELECT ATTRIBUTE_NAME
14034: FROM HZ_TRANS_ATTRIBUTES_VL a,
14035: HZ_MATCH_RULE_SECONDARY s
14036: WHERE s.match_rule_id = p_rule_id
14037: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14038: AND s.attribute_id = a.attribute_id)
14039: LOOP
14118: FIRST := TRUE;
14119: FOR SECATTRS IN (
14120: SELECT ATTRIBUTE_NAME
14121: FROM HZ_TRANS_ATTRIBUTES_VL a,
14122: HZ_MATCH_RULE_SECONDARY s
14123: WHERE s.match_rule_id = p_rule_id
14124: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14125: AND s.attribute_id = a.attribute_id)
14126: LOOP
14175: OR a.CUSTOM_ATTRIBUTE_PROCEDURE IS NOT NULL)
14176: UNION
14177: SELECT distinct ATTRIBUTE_NAME, CUSTOM_ATTRIBUTE_PROCEDURE
14178: FROM HZ_TRANS_ATTRIBUTES_VL a,
14179: HZ_MATCH_RULE_SECONDARY s
14180: WHERE s.match_rule_id = p_rule_id
14181: AND s.attribute_id = a.attribute_id
14182: AND a.ENTITY_NAME = p_entity
14183: AND (a.SOURCE_TABLE = 'CUSTOM'
14241: UNION
14242:
14243: SELECT a.attribute_id
14244: FROM HZ_TRANS_ATTRIBUTES_VL a,
14245: HZ_MATCH_RULE_SECONDARY s
14246: WHERE s.match_rule_id = p_rule_id
14247: AND s.attribute_id = a.attribute_id
14248: AND a.ENTITY_NAME = 'PARTY'
14249: );
14266: UNION
14267:
14268: SELECT a.attribute_id
14269: FROM HZ_TRANS_ATTRIBUTES_VL a,
14270: HZ_MATCH_RULE_SECONDARY s
14271: WHERE s.match_rule_id = p_rule_id
14272: AND s.attribute_id = a.attribute_id
14273: AND a.ENTITY_NAME = 'PARTY_SITES'
14274: );
14332: UNION
14333:
14334: SELECT a.attribute_id
14335: FROM HZ_TRANS_ATTRIBUTES_VL a,
14336: HZ_MATCH_RULE_SECONDARY s
14337: WHERE s.match_rule_id = p_rule_id
14338: AND s.attribute_id = a.attribute_id
14339: AND a.ENTITY_NAME = 'CONTACTS'
14340: );
14371: UNION
14372:
14373: SELECT a.attribute_id
14374: FROM HZ_TRANS_ATTRIBUTES_VL a,
14375: HZ_MATCH_RULE_SECONDARY s
14376: WHERE s.match_rule_id = p_rule_id
14377: AND s.attribute_id = a.attribute_id
14378: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14379: );
14460: UNION
14461:
14462: SELECT a.attribute_id
14463: FROM HZ_TRANS_ATTRIBUTES_VL a,
14464: HZ_MATCH_RULE_SECONDARY s
14465: WHERE s.match_rule_id = p_rule_id
14466: AND s.attribute_id = a.attribute_id
14467: AND a.ENTITY_NAME = 'PARTY'
14468: );
14486: UNION
14487:
14488: SELECT a.attribute_id
14489: FROM HZ_TRANS_ATTRIBUTES_VL a,
14490: HZ_MATCH_RULE_SECONDARY s
14491: WHERE s.match_rule_id = p_rule_id
14492: AND s.attribute_id = a.attribute_id
14493: AND a.ENTITY_NAME = 'PARTY_SITES'
14494: );
14514: UNION
14515:
14516: SELECT a.attribute_id
14517: FROM HZ_TRANS_ATTRIBUTES_VL a,
14518: HZ_MATCH_RULE_SECONDARY s
14519: WHERE s.match_rule_id = p_rule_id
14520: AND s.attribute_id = a.attribute_id
14521: AND a.ENTITY_NAME = 'CONTACTS'
14522: );
14541: UNION
14542:
14543: SELECT a.attribute_id
14544: FROM HZ_TRANS_ATTRIBUTES_VL a,
14545: HZ_MATCH_RULE_SECONDARY s
14546: WHERE s.match_rule_id = p_rule_id
14547: AND s.attribute_id = a.attribute_id
14548: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14549: );
14604: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14605: UNION
14606: SELECT a.attribute_id
14607: FROM HZ_TRANS_ATTRIBUTES_VL a,
14608: HZ_MATCH_RULE_SECONDARY s
14609: WHERE s.match_rule_id = p_rule_id
14610: AND s.attribute_id = a.attribute_id
14611: AND a.ENTITY_NAME = 'PARTY'
14612: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14633: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14634: UNION
14635: SELECT distinct a.ATTRIBUTE_NAME, decode(a.SOURCE_TABLE, 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES', 'HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES', 'HZ_ORGANIZATION_PROFILES', a.SOURCE_TABLE) SOURCE_TABLE
14636: FROM HZ_TRANS_ATTRIBUTES_VL a,
14637: HZ_MATCH_RULE_SECONDARY s
14638: WHERE s.match_rule_id = p_rule_id
14639: AND s.attribute_id = a.attribute_id
14640: AND a.ENTITY_NAME = 'PARTY'
14641: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14668: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14669: UNION
14670: SELECT distinct a.ATTRIBUTE_NAME
14671: FROM HZ_TRANS_ATTRIBUTES_VL a,
14672: HZ_MATCH_RULE_SECONDARY s
14673: WHERE s.match_rule_id = p_rule_id
14674: AND s.attribute_id = a.attribute_id
14675: AND a.ENTITY_NAME = 'PARTY'
14676: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14712: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14713: UNION
14714: SELECT a.attribute_id
14715: FROM HZ_TRANS_ATTRIBUTES_VL a,
14716: HZ_MATCH_RULE_SECONDARY s
14717: WHERE s.match_rule_id = p_rule_id
14718: AND s.attribute_id = a.attribute_id
14719: AND a.ENTITY_NAME = 'PARTY'
14720: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14741: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14742: UNION
14743: SELECT distinct a.ATTRIBUTE_NAME, decode(a.SOURCE_TABLE, 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES', 'HZ_PERSON_PROFILES', 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES', 'HZ_PERSON_PROFILES', a.SOURCE_TABLE) SOURCE_TABLE
14744: FROM HZ_TRANS_ATTRIBUTES_VL a,
14745: HZ_MATCH_RULE_SECONDARY s
14746: WHERE s.match_rule_id = p_rule_id
14747: AND s.attribute_id = a.attribute_id
14748: AND a.ENTITY_NAME = 'PARTY'
14749: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14776: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14777: UNION
14778: SELECT distinct a.ATTRIBUTE_NAME
14779: FROM HZ_TRANS_ATTRIBUTES_VL a,
14780: HZ_MATCH_RULE_SECONDARY s
14781: WHERE s.match_rule_id = p_rule_id
14782: AND s.attribute_id = a.attribute_id
14783: AND a.ENTITY_NAME = 'PARTY'
14784: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14837: AND a.SOURCE_TABLE <> 'CUSTOM'
14838: UNION
14839: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14840: FROM HZ_TRANS_ATTRIBUTES_VL a,
14841: HZ_MATCH_RULE_SECONDARY s
14842: WHERE s.match_rule_id = p_rule_id
14843: AND s.attribute_id = a.attribute_id
14844: AND a.ENTITY_NAME = 'PARTY_SITES'
14845: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14875: UNION
14876:
14877: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14878: FROM HZ_TRANS_ATTRIBUTES_VL a,
14879: HZ_MATCH_RULE_SECONDARY s
14880: WHERE s.match_rule_id = p_rule_id
14881: AND s.attribute_id = a.attribute_id
14882: AND a.ENTITY_NAME = 'PARTY_SITES'
14883: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14927: AND a.SOURCE_TABLE <> 'CUSTOM'
14928: UNION
14929: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14930: FROM HZ_TRANS_ATTRIBUTES_VL a,
14931: HZ_MATCH_RULE_SECONDARY s
14932: WHERE s.match_rule_id = p_rule_id
14933: AND s.attribute_id = a.attribute_id
14934: AND a.ENTITY_NAME = 'CONTACTS'
14935: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14970: UNION
14971:
14972: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14973: FROM HZ_TRANS_ATTRIBUTES_VL a,
14974: HZ_MATCH_RULE_SECONDARY s
14975: WHERE s.match_rule_id = p_rule_id
14976: AND s.attribute_id = a.attribute_id
14977: AND a.ENTITY_NAME = 'CONTACTS'
14978: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
15024: UNION
15025:
15026: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
15027: FROM HZ_TRANS_ATTRIBUTES_VL a,
15028: HZ_MATCH_RULE_SECONDARY s
15029: WHERE s.match_rule_id = p_rule_id
15030: AND s.attribute_id = a.attribute_id
15031: AND a.ENTITY_NAME = 'CONTACT_POINTS'
15032: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
15063: UNION
15064:
15065: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
15066: FROM HZ_TRANS_ATTRIBUTES_VL a,
15067: HZ_MATCH_RULE_SECONDARY s
15068: WHERE s.match_rule_id = p_rule_id
15069: AND s.attribute_id = a.attribute_id
15070: AND a.ENTITY_NAME = 'CONTACT_POINTS'
15071: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
15108:
15109: UNION
15110:
15111: SELECT f.FUNCTION_ID
15112: FROM hz_secondary_trans f, hz_match_rule_secondary a
15113: WHERE a.MATCH_RULE_ID = p_rule_id
15114: AND a.SECONDARY_ATTRIBUTE_ID = f.SECONDARY_ATTRIBUTE_ID;
15115:
15116: ---Start of Code Change for Match Rule Set
15125:
15126: UNION
15127:
15128: SELECT f.FUNCTION_ID
15129: FROM hz_secondary_trans f, hz_match_rule_secondary a
15130: WHERE a.MATCH_RULE_ID IN (SELECT UNIQUE CONDITION_MATCH_RULE_ID
15131: FROM HZ_MATCH_RULE_CONDITIONS
15132: WHERE MATCH_RULE_SET_ID = p_rule_id
15133: )
15566:
15567: l_num_secondary NUMBER;
15568: BEGIN
15569: SELECT count(1) INTO l_num_secondary
15570: FROM HZ_MATCH_RULE_SECONDARY p,
15571: HZ_TRANS_ATTRIBUTES_VL a
15572: WHERE p.match_rule_id = p_rule_id
15573: AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
15574: AND ENTITY_NAME = p_entity;
16270: IS
16271: l_score NUMBER;
16272: BEGIN
16273: SELECT nvl(sum(sec.score),0) INTO l_score
16274: FROM HZ_MATCH_RULE_SECONDARY sec,HZ_TRANS_ATTRIBUTES_VL attr
16275: WHERE sec.match_rule_id = p_match_rule_id
16276: AND sec.attribute_id = attr.attribute_id
16277: AND attr.entity_name = p_entity_name;
16278: