[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='S' AND Nvl(l_match_threshold,0)<>0 THEN
3128: FIRST := TRUE;
3129: FOR TX IN (
3130: SELECT f.staged_attribute_column
3131: FROM hz_trans_functions_vl f, hz_secondary_trans st,
3132: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
3133: WHERE sa.match_rule_id = p_rule_id
3134: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
3135: AND st.function_id = f.function_id
3136: AND a.attribute_id = sa.attribute_id
3159: l(' END IF;');
3160: FOR SECATTRS IN (
3161: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
3162: FROM HZ_TRANS_ATTRIBUTES_VL a,
3163: HZ_MATCH_RULE_SECONDARY s
3164: WHERE s.match_rule_id = p_rule_id
3165: AND s.attribute_id = a.attribute_id
3166: AND a.entity_name = 'PARTY') LOOP
3167: FIRST := TRUE;
3466: FOR PRIMATTRS IN (
3467: SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME, nvl(SCORE,0) SCORE
3468: FROM HZ_TRANS_ATTRIBUTES_VL a,
3469: HZ_MATCH_RULE_PRIMARY p,
3470: HZ_MATCH_RULE_SECONDARY s
3471: WHERE p.match_rule_id = p_rule_id
3472: AND s.match_rule_id (+) = p_rule_id
3473: AND s.attribute_id (+) = a.attribute_id
3474: AND p.attribute_id = a.attribute_id
3656: IF l_purpose = 'S' THEN
3657: l_party_name_score:='decode(TX8,g_party_stage_rec.TX8||'' '',100,90)';
3658: ELSE
3659: BEGIN
3660: SELECT to_char(score) INTO l_party_name_score from HZ_MATCH_RULE_SECONDARY s, HZ_TRANS_ATTRIBUTES_VL a
3661: WHERE a.attribute_id = s.attribute_id
3662: AND s.match_rule_id = p_rule_id
3663: AND attribute_name = 'PARTY_NAME';
3664: EXCEPTION
4871: l('');
4872: FOR TX IN (
4873: SELECT distinct f.staged_attribute_column
4874: FROM hz_trans_functions_vl f, hz_secondary_trans st,
4875: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
4876: WHERE sa.match_rule_id = p_rule_id
4877: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
4878: AND st.function_id = f.function_id
4879: AND a.attribute_id = sa.attribute_id) LOOP
5837: l('');
5838: FOR TX IN (
5839: SELECT distinct f.staged_attribute_column
5840: FROM hz_trans_functions_vl f, hz_secondary_trans st,
5841: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
5842: WHERE sa.match_rule_id = p_rule_id
5843: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
5844: AND st.function_id = f.function_id
5845: AND a.attribute_id = sa.attribute_id) LOOP
7314: UNION
7315:
7316: SELECT a.attribute_id, a.attribute_name, a.entity_name
7317: FROM HZ_TRANS_ATTRIBUTES_VL a,
7318: HZ_MATCH_RULE_SECONDARY s
7319: WHERE s.match_rule_id = p_rule_id
7320: AND s.attribute_id = a.attribute_id) LOOP
7321: l(' IF AttrVals.EXISTS('||ATTRS.attribute_id||') THEN');
7322: IF ATTRS.entity_name='PARTY' THEN
7448: l('');
7449: FOR TX IN (
7450: SELECT distinct f.staged_attribute_column
7451: FROM hz_trans_functions_vl f, hz_secondary_trans st,
7452: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
7453: WHERE sa.match_rule_id = p_rule_id
7454: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
7455: AND st.function_id = f.function_id
7456: AND (a.ENTITY_NAME='PARTY_SITES' OR a.ENTITY_NAME='CONTACT_POINTS')
7722: l('');
7723: FOR TX IN (
7724: SELECT distinct f.staged_attribute_column
7725: FROM hz_trans_functions_vl f, hz_secondary_trans st,
7726: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
7727: WHERE sa.match_rule_id = p_rule_id
7728: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
7729: AND st.function_id = f.function_id
7730: AND (a.ENTITY_NAME='CONTACTS' OR a.ENTITY_NAME='CONTACT_POINTS')
7990: l('');
7991: FOR TX IN (
7992: SELECT distinct f.staged_attribute_column
7993: FROM hz_trans_functions_vl f, hz_secondary_trans st,
7994: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
7995: WHERE sa.match_rule_id = p_rule_id
7996: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
7997: AND st.function_id = f.function_id
7998: AND a.ENTITY_NAME='CONTACT_POINTS'
8177: l('');
8178: FOR TX IN (
8179: SELECT distinct f.staged_attribute_column
8180: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8181: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8182: WHERE sa.match_rule_id = p_rule_id
8183: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8184: AND st.function_id = f.function_id
8185: AND a.attribute_id = sa.attribute_id) LOOP
8277: l(' l_max_idx := l_match_idx;');
8278: FOR TX IN (
8279: SELECT distinct f.staged_attribute_column
8280: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8281: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8282: WHERE sa.match_rule_id = p_rule_id
8283: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8284: AND st.function_id = f.function_id
8285: AND a.entity_name = 'PARTY_SITES'
8310: l(' l_max_idx := l_match_idx;');
8311: FOR TX IN (
8312: SELECT distinct f.staged_attribute_column
8313: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8314: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8315: WHERE sa.match_rule_id = p_rule_id
8316: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8317: AND st.function_id = f.function_id
8318: AND a.entity_name = 'CONTACTS'
8347: l(' l_max_idx := l_match_idx;');
8348: FOR TX IN (
8349: SELECT distinct f.staged_attribute_column
8350: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8351: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8352: WHERE sa.match_rule_id = p_rule_id
8353: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8354: AND st.function_id = f.function_id
8355: AND a.entity_name = 'CONTACT_POINTS'
8738: FROM HZ_MATCH_RULES_VL
8739: WHERE match_rule_id = p_rule_id;
8740:
8741: SELECT nvl(SUM(SCORE),1) INTO l_max_score
8742: FROM HZ_MATCH_RULE_SECONDARY
8743: WHERE match_rule_id = p_rule_id;
8744:
8745: l('CREATE or REPLACE PACKAGE BODY ' || p_pkg_name || ' AS');
8746: l('/*=======================================================================+');
8821: FIRST := TRUE;
8822: FOR TX IN (
8823: SELECT f.staged_attribute_column
8824: FROM hz_trans_functions_vl f, hz_secondary_trans st,
8825: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
8826: WHERE sa.match_rule_id = p_rule_id
8827: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
8828: AND st.function_id = f.function_id
8829: AND a.attribute_id = sa.attribute_id
8850: l(' END IF;');
8851: FOR SECATTRS IN (
8852: SELECT SECONDARY_ATTRIBUTE_ID, SCORE, ATTRIBUTE_NAME, ENTITY_NAME, a.attribute_id
8853: FROM HZ_TRANS_ATTRIBUTES_VL a,
8854: HZ_MATCH_RULE_SECONDARY s
8855: WHERE s.match_rule_id = p_rule_id
8856: AND s.attribute_id = a.attribute_id
8857: AND a.entity_name = 'PARTY') LOOP
8858: FIRST := TRUE;
9070: FOR PRIMATTRS IN (
9071: SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME, nvl(SCORE,0) SCORE
9072: FROM HZ_TRANS_ATTRIBUTES_VL a,
9073: HZ_MATCH_RULE_PRIMARY p,
9074: HZ_MATCH_RULE_SECONDARY s
9075: WHERE p.match_rule_id = p_rule_id
9076: AND s.match_rule_id (+) = p_rule_id
9077: AND s.attribute_id (+) = a.attribute_id
9078: AND p.attribute_id = a.attribute_id
9704: l('');
9705: FOR TX IN (
9706: SELECT distinct f.staged_attribute_column
9707: FROM hz_trans_functions_vl f, hz_secondary_trans st,
9708: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
9709: WHERE sa.match_rule_id = p_rule_id
9710: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
9711: AND st.function_id = f.function_id
9712: AND a.attribute_id = sa.attribute_id) LOOP
10439: l('');
10440: FOR TX IN (
10441: SELECT distinct f.staged_attribute_column
10442: FROM hz_trans_functions_vl f, hz_secondary_trans st,
10443: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
10444: WHERE sa.match_rule_id = p_rule_id
10445: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
10446: AND st.function_id = f.function_id
10447: AND a.attribute_id = sa.attribute_id) LOOP
11703: UNION
11704:
11705: SELECT a.attribute_id, a.attribute_name, a.entity_name
11706: FROM HZ_TRANS_ATTRIBUTES_VL a,
11707: HZ_MATCH_RULE_SECONDARY s
11708: WHERE s.match_rule_id = p_rule_id
11709: AND s.attribute_id = a.attribute_id) LOOP
11710: l(' IF AttrVals.EXISTS('||ATTRS.attribute_id||') THEN');
11711: IF ATTRS.entity_name='PARTY' THEN
11834: l('');
11835: FOR TX IN (
11836: SELECT distinct f.staged_attribute_column
11837: FROM hz_trans_functions_vl f, hz_secondary_trans st,
11838: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
11839: WHERE sa.match_rule_id = p_rule_id
11840: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
11841: AND st.function_id = f.function_id
11842: AND (a.ENTITY_NAME='PARTY_SITES' OR a.ENTITY_NAME='CONTACT_POINTS')
12075: l('');
12076: FOR TX IN (
12077: SELECT distinct f.staged_attribute_column
12078: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12079: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12080: WHERE sa.match_rule_id = p_rule_id
12081: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12082: AND st.function_id = f.function_id
12083: AND (a.ENTITY_NAME='CONTACTS' OR a.ENTITY_NAME='CONTACT_POINTS')
12307: l('');
12308: FOR TX IN (
12309: SELECT distinct f.staged_attribute_column
12310: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12311: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12312: WHERE sa.match_rule_id = p_rule_id
12313: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12314: AND st.function_id = f.function_id
12315: AND a.ENTITY_NAME='CONTACT_POINTS'
12473: l('');
12474: FOR TX IN (
12475: SELECT distinct f.staged_attribute_column
12476: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12477: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12478: WHERE sa.match_rule_id = p_rule_id
12479: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12480: AND st.function_id = f.function_id
12481: AND a.attribute_id = sa.attribute_id) LOOP
12563: l(' l_max_idx := l_match_idx;');
12564: FOR TX IN (
12565: SELECT distinct f.staged_attribute_column
12566: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12567: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12568: WHERE sa.match_rule_id = p_rule_id
12569: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12570: AND st.function_id = f.function_id
12571: AND a.entity_name = 'PARTY_SITES'
12596: l(' l_max_idx := l_match_idx;');
12597: FOR TX IN (
12598: SELECT distinct f.staged_attribute_column
12599: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12600: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12601: WHERE sa.match_rule_id = p_rule_id
12602: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12603: AND st.function_id = f.function_id
12604: AND a.entity_name = 'CONTACTS'
12629: l(' l_max_idx := l_match_idx;');
12630: FOR TX IN (
12631: SELECT distinct f.staged_attribute_column
12632: FROM hz_trans_functions_vl f, hz_secondary_trans st,
12633: hz_match_rule_secondary sa, HZ_TRANS_ATTRIBUTES_VL a
12634: WHERE sa.match_rule_id = p_rule_id
12635: AND st.SECONDARY_ATTRIBUTE_ID = sa.SECONDARY_ATTRIBUTE_ID
12636: AND st.function_id = f.function_id
12637: AND a.entity_name = 'CONTACT_POINTS'
12803: l(' x_entered_max_score:=0;');
12804: for SECATTRS IN (
12805: SELECT a.ATTRIBUTE_NAME, SCORE
12806: FROM HZ_TRANS_ATTRIBUTES_VL a,
12807: HZ_MATCH_RULE_SECONDARY sattr
12808: WHERE sattr.MATCH_RULE_ID = p_rule_id
12809: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12810: AND a.ENTITY_NAME = 'PARTY') LOOP
12811: l(' IF p_search_rec.'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
12834: f.PROCEDURE_NAME,
12835: f.STAGED_ATTRIBUTE_COLUMN
12836: FROM HZ_TRANS_FUNCTIONS_VL f,
12837: HZ_TRANS_ATTRIBUTES_VL a,
12838: HZ_MATCH_RULE_SECONDARY sattr,
12839: HZ_SECONDARY_TRANS sfunc
12840: WHERE sattr.MATCH_RULE_ID = p_rule_id
12841: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12842: AND a.ENTITY_NAME = 'PARTY'
12983: END IF;
12984: for SECATTRS IN (
12985: SELECT a.ATTRIBUTE_NAME, SCORE, nvl(a.denorm_flag,'N') DENORM_FLAG
12986: FROM HZ_TRANS_ATTRIBUTES_VL a,
12987: HZ_MATCH_RULE_SECONDARY sattr
12988: WHERE sattr.MATCH_RULE_ID = p_rule_id
12989: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12990: AND a.ENTITY_NAME = p_entity) LOOP
12991: l(' IF p_search_list(I).'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
13049: f.PROCEDURE_NAME,
13050: f.STAGED_ATTRIBUTE_COLUMN
13051: FROM HZ_TRANS_FUNCTIONS_VL f,
13052: HZ_TRANS_ATTRIBUTES_VL a,
13053: HZ_MATCH_RULE_SECONDARY sattr,
13054: HZ_SECONDARY_TRANS sfunc
13055: WHERE sattr.MATCH_RULE_ID = p_rule_id
13056: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13057: AND a.ENTITY_NAME = p_entity
13163:
13164: SELECT a.ATTRIBUTE_ID, a.ATTRIBUTE_NAME
13165: FROM HZ_TRANS_FUNCTIONS_VL f,
13166: HZ_TRANS_ATTRIBUTES_VL a,
13167: HZ_MATCH_RULE_SECONDARY sattr,
13168: HZ_SECONDARY_TRANS sfunc
13169: WHERE sattr.MATCH_RULE_ID = p_rule_id
13170: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13171: AND a.ENTITY_NAME = p_entity
13260: END IF;
13261: for SECATTRS IN (
13262: SELECT a.ATTRIBUTE_NAME, SCORE
13263: FROM HZ_TRANS_ATTRIBUTES_VL a,
13264: HZ_MATCH_RULE_SECONDARY sattr
13265: WHERE sattr.MATCH_RULE_ID = p_rule_id
13266: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13267: AND a.ENTITY_NAME = p_entity) LOOP
13268: l(' IF p_search_list(I).'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
13295: f.PROCEDURE_NAME,
13296: f.STAGED_ATTRIBUTE_COLUMN
13297: FROM HZ_TRANS_FUNCTIONS_VL f,
13298: HZ_TRANS_ATTRIBUTES_VL a,
13299: HZ_MATCH_RULE_SECONDARY sattr,
13300: HZ_SECONDARY_TRANS sfunc
13301: WHERE sattr.MATCH_RULE_ID = p_rule_id
13302: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13303: AND a.ENTITY_NAME = p_entity
13363: f.PROCEDURE_NAME,
13364: f.STAGED_ATTRIBUTE_COLUMN
13365: FROM HZ_TRANS_FUNCTIONS_VL f,
13366: HZ_TRANS_ATTRIBUTES_VL a,
13367: HZ_MATCH_RULE_SECONDARY sattr,
13368: HZ_SECONDARY_TRANS sfunc
13369: WHERE sattr.MATCH_RULE_ID = p_rule_id
13370: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13371: AND a.ENTITY_NAME = p_entity
13487: l(' x_entered_max_score:=0;');
13488: for SECATTRS IN (
13489: SELECT a.ATTRIBUTE_NAME, SCORE
13490: FROM HZ_TRANS_ATTRIBUTES_VL a,
13491: HZ_MATCH_RULE_SECONDARY sattr
13492: WHERE sattr.MATCH_RULE_ID = p_rule_id
13493: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13494: AND a.ENTITY_NAME = 'PARTY') LOOP
13495: l(' IF p_search_rec.'||SECATTRS.ATTRIBUTE_NAME || ' IS NOT NULL THEN ');
13518: f.PROCEDURE_NAME,
13519: f.STAGED_ATTRIBUTE_COLUMN
13520: FROM HZ_TRANS_FUNCTIONS_VL f,
13521: HZ_TRANS_ATTRIBUTES_VL a,
13522: HZ_MATCH_RULE_SECONDARY sattr,
13523: HZ_SECONDARY_TRANS sfunc
13524: WHERE sattr.MATCH_RULE_ID = p_rule_id
13525: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13526: AND a.ENTITY_NAME = 'PARTY'
13810: FIRST := TRUE;
13811: FOR SECATTRS IN (
13812: SELECT ATTRIBUTE_NAME
13813: FROM HZ_TRANS_ATTRIBUTES_VL a,
13814: HZ_MATCH_RULE_SECONDARY s
13815: WHERE s.match_rule_id = p_rule_id
13816: AND a.ENTITY_NAME = 'PARTY_SITES'
13817: AND s.attribute_id = a.attribute_id)
13818: LOOP
13837: FIRST := TRUE;
13838: FOR SECATTRS IN (
13839: SELECT ATTRIBUTE_NAME
13840: FROM HZ_TRANS_ATTRIBUTES_VL a,
13841: HZ_MATCH_RULE_SECONDARY s
13842: WHERE s.match_rule_id = p_rule_id
13843: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13844: AND s.attribute_id = a.attribute_id)
13845: LOOP
13947: FIRST := TRUE;
13948: FOR SECATTRS IN (
13949: SELECT ATTRIBUTE_NAME
13950: FROM HZ_TRANS_ATTRIBUTES_VL a,
13951: HZ_MATCH_RULE_SECONDARY s
13952: WHERE s.match_rule_id = p_rule_id
13953: AND a.ENTITY_NAME = 'CONTACTS'
13954: AND s.attribute_id = a.attribute_id)
13955: LOOP
13975: FIRST := TRUE;
13976: FOR SECATTRS IN (
13977: SELECT ATTRIBUTE_NAME
13978: FROM HZ_TRANS_ATTRIBUTES_VL a,
13979: HZ_MATCH_RULE_SECONDARY s
13980: WHERE s.match_rule_id = p_rule_id
13981: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13982: AND s.attribute_id = a.attribute_id)
13983: LOOP
14062: FIRST := TRUE;
14063: FOR SECATTRS IN (
14064: SELECT ATTRIBUTE_NAME
14065: FROM HZ_TRANS_ATTRIBUTES_VL a,
14066: HZ_MATCH_RULE_SECONDARY s
14067: WHERE s.match_rule_id = p_rule_id
14068: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14069: AND s.attribute_id = a.attribute_id)
14070: LOOP
14119: OR a.CUSTOM_ATTRIBUTE_PROCEDURE IS NOT NULL)
14120: UNION
14121: SELECT distinct ATTRIBUTE_NAME, CUSTOM_ATTRIBUTE_PROCEDURE
14122: FROM HZ_TRANS_ATTRIBUTES_VL a,
14123: HZ_MATCH_RULE_SECONDARY s
14124: WHERE s.match_rule_id = p_rule_id
14125: AND s.attribute_id = a.attribute_id
14126: AND a.ENTITY_NAME = p_entity
14127: AND (a.SOURCE_TABLE = 'CUSTOM'
14185: UNION
14186:
14187: SELECT a.attribute_id
14188: FROM HZ_TRANS_ATTRIBUTES_VL a,
14189: HZ_MATCH_RULE_SECONDARY s
14190: WHERE s.match_rule_id = p_rule_id
14191: AND s.attribute_id = a.attribute_id
14192: AND a.ENTITY_NAME = 'PARTY'
14193: );
14210: UNION
14211:
14212: SELECT a.attribute_id
14213: FROM HZ_TRANS_ATTRIBUTES_VL a,
14214: HZ_MATCH_RULE_SECONDARY s
14215: WHERE s.match_rule_id = p_rule_id
14216: AND s.attribute_id = a.attribute_id
14217: AND a.ENTITY_NAME = 'PARTY_SITES'
14218: );
14276: UNION
14277:
14278: SELECT a.attribute_id
14279: FROM HZ_TRANS_ATTRIBUTES_VL a,
14280: HZ_MATCH_RULE_SECONDARY s
14281: WHERE s.match_rule_id = p_rule_id
14282: AND s.attribute_id = a.attribute_id
14283: AND a.ENTITY_NAME = 'CONTACTS'
14284: );
14315: UNION
14316:
14317: SELECT a.attribute_id
14318: FROM HZ_TRANS_ATTRIBUTES_VL a,
14319: HZ_MATCH_RULE_SECONDARY s
14320: WHERE s.match_rule_id = p_rule_id
14321: AND s.attribute_id = a.attribute_id
14322: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14323: );
14404: UNION
14405:
14406: SELECT a.attribute_id
14407: FROM HZ_TRANS_ATTRIBUTES_VL a,
14408: HZ_MATCH_RULE_SECONDARY s
14409: WHERE s.match_rule_id = p_rule_id
14410: AND s.attribute_id = a.attribute_id
14411: AND a.ENTITY_NAME = 'PARTY'
14412: );
14430: UNION
14431:
14432: SELECT a.attribute_id
14433: FROM HZ_TRANS_ATTRIBUTES_VL a,
14434: HZ_MATCH_RULE_SECONDARY s
14435: WHERE s.match_rule_id = p_rule_id
14436: AND s.attribute_id = a.attribute_id
14437: AND a.ENTITY_NAME = 'PARTY_SITES'
14438: );
14458: UNION
14459:
14460: SELECT a.attribute_id
14461: FROM HZ_TRANS_ATTRIBUTES_VL a,
14462: HZ_MATCH_RULE_SECONDARY s
14463: WHERE s.match_rule_id = p_rule_id
14464: AND s.attribute_id = a.attribute_id
14465: AND a.ENTITY_NAME = 'CONTACTS'
14466: );
14485: UNION
14486:
14487: SELECT a.attribute_id
14488: FROM HZ_TRANS_ATTRIBUTES_VL a,
14489: HZ_MATCH_RULE_SECONDARY s
14490: WHERE s.match_rule_id = p_rule_id
14491: AND s.attribute_id = a.attribute_id
14492: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14493: );
14548: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14549: UNION
14550: SELECT a.attribute_id
14551: FROM HZ_TRANS_ATTRIBUTES_VL a,
14552: HZ_MATCH_RULE_SECONDARY s
14553: WHERE s.match_rule_id = p_rule_id
14554: AND s.attribute_id = a.attribute_id
14555: AND a.ENTITY_NAME = 'PARTY'
14556: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14577: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14578: UNION
14579: 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
14580: FROM HZ_TRANS_ATTRIBUTES_VL a,
14581: HZ_MATCH_RULE_SECONDARY s
14582: WHERE s.match_rule_id = p_rule_id
14583: AND s.attribute_id = a.attribute_id
14584: AND a.ENTITY_NAME = 'PARTY'
14585: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14612: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14613: UNION
14614: SELECT distinct a.ATTRIBUTE_NAME
14615: FROM HZ_TRANS_ATTRIBUTES_VL a,
14616: HZ_MATCH_RULE_SECONDARY s
14617: WHERE s.match_rule_id = p_rule_id
14618: AND s.attribute_id = a.attribute_id
14619: AND a.ENTITY_NAME = 'PARTY'
14620: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14656: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14657: UNION
14658: SELECT a.attribute_id
14659: FROM HZ_TRANS_ATTRIBUTES_VL a,
14660: HZ_MATCH_RULE_SECONDARY s
14661: WHERE s.match_rule_id = p_rule_id
14662: AND s.attribute_id = a.attribute_id
14663: AND a.ENTITY_NAME = 'PARTY'
14664: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14685: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14686: UNION
14687: 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
14688: FROM HZ_TRANS_ATTRIBUTES_VL a,
14689: HZ_MATCH_RULE_SECONDARY s
14690: WHERE s.match_rule_id = p_rule_id
14691: AND s.attribute_id = a.attribute_id
14692: AND a.ENTITY_NAME = 'PARTY'
14693: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14720: AND a.CUSTOM_ATTRIBUTE_PROCEDURE IS NULL
14721: UNION
14722: SELECT distinct a.ATTRIBUTE_NAME
14723: FROM HZ_TRANS_ATTRIBUTES_VL a,
14724: HZ_MATCH_RULE_SECONDARY s
14725: WHERE s.match_rule_id = p_rule_id
14726: AND s.attribute_id = a.attribute_id
14727: AND a.ENTITY_NAME = 'PARTY'
14728: AND (a.SOURCE_TABLE = 'HZ_PARTIES' OR
14781: AND a.SOURCE_TABLE <> 'CUSTOM'
14782: UNION
14783: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14784: FROM HZ_TRANS_ATTRIBUTES_VL a,
14785: HZ_MATCH_RULE_SECONDARY s
14786: WHERE s.match_rule_id = p_rule_id
14787: AND s.attribute_id = a.attribute_id
14788: AND a.ENTITY_NAME = 'PARTY_SITES'
14789: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14819: UNION
14820:
14821: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14822: FROM HZ_TRANS_ATTRIBUTES_VL a,
14823: HZ_MATCH_RULE_SECONDARY s
14824: WHERE s.match_rule_id = p_rule_id
14825: AND s.attribute_id = a.attribute_id
14826: AND a.ENTITY_NAME = 'PARTY_SITES'
14827: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14871: AND a.SOURCE_TABLE <> 'CUSTOM'
14872: UNION
14873: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14874: FROM HZ_TRANS_ATTRIBUTES_VL a,
14875: HZ_MATCH_RULE_SECONDARY s
14876: WHERE s.match_rule_id = p_rule_id
14877: AND s.attribute_id = a.attribute_id
14878: AND a.ENTITY_NAME = 'CONTACTS'
14879: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14914: UNION
14915:
14916: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14917: FROM HZ_TRANS_ATTRIBUTES_VL a,
14918: HZ_MATCH_RULE_SECONDARY s
14919: WHERE s.match_rule_id = p_rule_id
14920: AND s.attribute_id = a.attribute_id
14921: AND a.ENTITY_NAME = 'CONTACTS'
14922: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
14968: UNION
14969:
14970: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14971: FROM HZ_TRANS_ATTRIBUTES_VL a,
14972: HZ_MATCH_RULE_SECONDARY s
14973: WHERE s.match_rule_id = p_rule_id
14974: AND s.attribute_id = a.attribute_id
14975: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14976: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
15007: UNION
15008:
15009: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
15010: FROM HZ_TRANS_ATTRIBUTES_VL a,
15011: HZ_MATCH_RULE_SECONDARY s
15012: WHERE s.match_rule_id = p_rule_id
15013: AND s.attribute_id = a.attribute_id
15014: AND a.ENTITY_NAME = 'CONTACT_POINTS'
15015: AND a.SOURCE_TABLE <> 'CUSTOM') LOOP
15052:
15053: UNION
15054:
15055: SELECT f.FUNCTION_ID
15056: FROM hz_secondary_trans f, hz_match_rule_secondary a
15057: WHERE a.MATCH_RULE_ID = p_rule_id
15058: AND a.SECONDARY_ATTRIBUTE_ID = f.SECONDARY_ATTRIBUTE_ID;
15059:
15060: ---Start of Code Change for Match Rule Set
15069:
15070: UNION
15071:
15072: SELECT f.FUNCTION_ID
15073: FROM hz_secondary_trans f, hz_match_rule_secondary a
15074: WHERE a.MATCH_RULE_ID IN (SELECT UNIQUE CONDITION_MATCH_RULE_ID
15075: FROM HZ_MATCH_RULE_CONDITIONS
15076: WHERE MATCH_RULE_SET_ID = p_rule_id
15077: )
15510:
15511: l_num_secondary NUMBER;
15512: BEGIN
15513: SELECT count(1) INTO l_num_secondary
15514: FROM HZ_MATCH_RULE_SECONDARY p,
15515: HZ_TRANS_ATTRIBUTES_VL a
15516: WHERE p.match_rule_id = p_rule_id
15517: AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
15518: AND ENTITY_NAME = p_entity;
16212: IS
16213: l_score NUMBER;
16214: BEGIN
16215: SELECT nvl(sum(sec.score),0) INTO l_score
16216: FROM HZ_MATCH_RULE_SECONDARY sec,HZ_TRANS_ATTRIBUTES_VL attr
16217: WHERE sec.match_rule_id = p_match_rule_id
16218: AND sec.attribute_id = attr.attribute_id
16219: AND attr.entity_name = p_entity_name;
16220: