[Home] [Help]
34: temp BOOLEAN := FALSE ;
35: BEGIN
36: FOR attrs in (
37: SELECT primary_attribute_id
38: FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
39: where p.match_rule_id= p_match_rule_id
40: and p.attribute_id=a.attribute_id
41: and a.entity_name = p_entity_name
42: )
364: -- Generate the Primary Attribute section of the query for the passed in entity
365: FIRST1 := TRUE;
366: FOR attrs in (
367: SELECT primary_attribute_id
368: FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
369: where p.match_rule_id=p_rule_id
370: and p.attribute_id=a.attribute_id
371: and a.entity_name = p_entity
372: and nvl(p.filter_flag,'N') = 'N' )
416: -- Generate the Primary Attribute section of the query for the passed in entity
417: FIRST1 := TRUE;
418: FOR attrs in (
419: SELECT primary_attribute_id
420: FROM HZ_MATCH_RULE_PRIMARY p, HZ_TRANS_ATTRIBUTES_VL a
421: where p.match_rule_id=p_rule_id
422: and p.attribute_id=a.attribute_id
423: and a.entity_name = p_entity
424: and nvl(p.filter_flag,'N') = 'N' )
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
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
495: FROM hz_match_rule_primary p, hz_trans_attributes_vl a
496: WHERE a.attribute_id = p.attribute_id
497: AND p.match_rule_id = p_rule_id;
498:
499: CURSOR check_inactive IS
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
2311: l(' FOR J IN 1..'||l_list||'.COUNT LOOP');
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
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
2409:
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
2516: l(' IF p_entity = '''||entity||''' THEN');
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
2667: -- Setup of contains str
2668: l_num_primary := 0;
2669: SELECT count(1) INTO l_num_primary
2670: FROM HZ_MATCH_RULE_PRIMARY p,
2671: HZ_TRANS_ATTRIBUTES_VL a
2672: WHERE p.match_rule_id = p_rule_id
2673: AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
2674: AND ENTITY_NAME = p_entity;
2675:
2720: FIRST := TRUE;
2721: FOR PRIMATTRS IN (
2722: SELECT a.ATTRIBUTE_ID, PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME,nvl(FILTER_FLAG,'N') FILTER_FLAG,
2723: nvl(DENORM_FLAG,'N') DENORM_FLAG
2724: FROM HZ_TRANS_ATTRIBUTES_VL a,
2725: HZ_MATCH_RULE_PRIMARY p
2726: WHERE p.match_rule_id = p_rule_id
2727: AND p.attribute_id = a.attribute_id
2728: AND a.ENTITY_NAME = p_entity) LOOP
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
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
3158: l(' RETURN total;');
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
3464: g_party_and_query := null;
3465: cnt := cnt+1;
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
3576: cnt := 1;
3577: for PRIMTRANS IN (
3578: SELECT f.STAGED_ATTRIBUTE_COLUMN
3579: FROM HZ_TRANS_FUNCTIONS_VL f,
3580: HZ_TRANS_ATTRIBUTES_VL a,
3581: HZ_MATCH_RULE_PRIMARY pattr,
3582: HZ_PRIMARY_TRANS pfunc
3583: WHERE pattr.MATCH_RULE_ID = p_rule_id
3584: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3590: UNION
3591:
3592: SELECT f.STAGED_ATTRIBUTE_COLUMN
3593: FROM HZ_TRANS_FUNCTIONS_VL f,
3594: HZ_TRANS_ATTRIBUTES_VL a
3595: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3596: AND a.entity_name = 'PARTY'
3597: AND a.attribute_name='PARTY_TYPE'
3598: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
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
3709: FIRST := TRUE;
3710: cnt := 1;
3711: FOR PATTRS IN (
3712: SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
3713: FROM HZ_TRANS_ATTRIBUTES_VL a,
3714: HZ_MATCH_RULE_PRIMARY p
3715: WHERE p.match_rule_id = p_rule_id
3716: AND p.attribute_id = a.attribute_id
3717: AND a.ENTITY_NAME = 'PARTY'
3749: END IF;
3750:
3751: FOR PATTRS IN (
3752: SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
3753: FROM HZ_TRANS_ATTRIBUTES_VL a,
3754: HZ_MATCH_RULE_PRIMARY p
3755: WHERE p.match_rule_id = p_rule_id
3756: AND p.attribute_id = a.attribute_id
3757: AND a.ENTITY_NAME = 'PARTY'
3788: ldbg_s('p_party_contains_str string is NOT NULL and other conditions met');
3789: FIRST := TRUE;
3790: FOR DENATTR IN (
3791: SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
3792: FROM HZ_TRANS_ATTRIBUTES_VL a,
3793: HZ_MATCH_RULE_PRIMARY p
3794: WHERE p.match_rule_id = p_rule_id
3795: AND p.attribute_id = a.attribute_id
3796: AND a.ENTITY_NAME = 'PARTY_SITES'
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
5892: SELECT a.attribute_name,
5893: f.PROCEDURE_NAME,
5894: f.STAGED_ATTRIBUTE_COLUMN
5895: FROM HZ_TRANS_FUNCTIONS_VL f,
5896: HZ_TRANS_ATTRIBUTES_VL a
5897: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
5898: AND a.entity_name = 'PARTY'
5899: AND a.attribute_name='PARTY_TYPE'
5900: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
7305:
7306: FIRST := TRUE;
7307: FOR ATTRS IN (
7308: SELECT a.attribute_id, a.attribute_name, a.entity_name
7309: FROM HZ_TRANS_ATTRIBUTES_VL a,
7310: HZ_MATCH_RULE_PRIMARY p
7311: WHERE p.match_rule_id = p_rule_id
7312: AND p.attribute_id = a.attribute_id
7313:
7313:
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');
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'
8460:
8461: FIRST := TRUE;
8462: FOR PRIMATTRS IN (
8463: SELECT ATTRIBUTE_NAME
8464: FROM HZ_TRANS_ATTRIBUTES_VL a,
8465: HZ_MATCH_RULE_PRIMARY p
8466: WHERE p.match_rule_id = p_rule_id
8467: AND p.attribute_id = a.attribute_id
8468: AND a.ENTITY_NAME = 'PARTY'
8509: l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
8510: FIRST := TRUE;
8511: FOR PRIMATTRS IN (
8512: SELECT ATTRIBUTE_NAME
8513: FROM HZ_TRANS_ATTRIBUTES_VL a,
8514: HZ_MATCH_RULE_PRIMARY p
8515: WHERE p.match_rule_id = p_rule_id
8516: AND a.ENTITY_NAME = 'PARTY_SITES'
8517: AND p.attribute_id = a.attribute_id)
8573: l(' FOR I IN 1..p_contact_list.COUNT LOOP');
8574: FIRST := TRUE;
8575: FOR PRIMATTRS IN (
8576: SELECT ATTRIBUTE_NAME
8577: FROM HZ_TRANS_ATTRIBUTES_VL a,
8578: HZ_MATCH_RULE_PRIMARY p
8579: WHERE p.match_rule_id = p_rule_id
8580: AND a.ENTITY_NAME = 'CONTACTS'
8581: AND p.attribute_id = a.attribute_id)
8635: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
8636: FIRST := TRUE;
8637: FOR PRIMATTRS IN (
8638: SELECT ATTRIBUTE_NAME
8639: FROM HZ_TRANS_ATTRIBUTES_VL a,
8640: HZ_MATCH_RULE_PRIMARY p
8641: WHERE p.match_rule_id = p_rule_id
8642: AND a.ENTITY_NAME = 'CONTACT_POINTS'
8643: AND p.attribute_id = a.attribute_id)
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
8849: l(' RETURN total;');
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
9068: g_party_and_query := null;
9069: cnt := cnt+1;
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
9124: cnt := 1;
9125: for PRIMTRANS IN (
9126: SELECT f.STAGED_ATTRIBUTE_COLUMN
9127: FROM HZ_TRANS_FUNCTIONS_VL f,
9128: HZ_TRANS_ATTRIBUTES_VL a,
9129: HZ_MATCH_RULE_PRIMARY pattr,
9130: HZ_PRIMARY_TRANS pfunc
9131: WHERE pattr.MATCH_RULE_ID = p_rule_id
9132: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
9138: UNION
9139:
9140: SELECT f.STAGED_ATTRIBUTE_COLUMN
9141: FROM HZ_TRANS_FUNCTIONS_VL f,
9142: HZ_TRANS_ATTRIBUTES_VL a
9143: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
9144: AND a.entity_name = 'PARTY'
9145: AND a.attribute_name='PARTY_TYPE'
9146: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
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
10502: SELECT a.attribute_name,
10503: f.PROCEDURE_NAME,
10504: f.STAGED_ATTRIBUTE_COLUMN
10505: FROM HZ_TRANS_FUNCTIONS_VL f,
10506: HZ_TRANS_ATTRIBUTES_VL a
10507: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
10508: AND a.entity_name = 'PARTY'
10509: AND a.attribute_name='PARTY_TYPE'
10510: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
11694:
11695: FIRST := TRUE;
11696: FOR ATTRS IN (
11697: SELECT a.attribute_id, a.attribute_name, a.entity_name
11698: FROM HZ_TRANS_ATTRIBUTES_VL a,
11699: HZ_MATCH_RULE_PRIMARY p
11700: WHERE p.match_rule_id = p_rule_id
11701: AND p.attribute_id = a.attribute_id
11702:
11702:
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');
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'
12802: l(' IF p_search_ctx THEN');
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
12818: SELECT a.ATTRIBUTE_NAME,
12819: f.PROCEDURE_NAME,
12820: f.STAGED_ATTRIBUTE_COLUMN
12821: FROM HZ_TRANS_FUNCTIONS_VL f,
12822: HZ_TRANS_ATTRIBUTES_VL a,
12823: HZ_MATCH_RULE_PRIMARY pattr,
12824: HZ_PRIMARY_TRANS pfunc
12825: WHERE pattr.MATCH_RULE_ID = p_rule_id
12826: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12833: SELECT a.ATTRIBUTE_NAME,
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
12848: SELECT a.attribute_name,
12849: f.PROCEDURE_NAME,
12850: f.STAGED_ATTRIBUTE_COLUMN
12851: FROM HZ_TRANS_FUNCTIONS_VL f,
12852: HZ_TRANS_ATTRIBUTES_VL a
12853: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12854: AND a.entity_name = 'PARTY'
12855: AND a.attribute_name='PARTY_TYPE'
12856: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
12982: l(' x_stage_list(I).CONTACT_POINT_TYPE := p_search_list(I).CONTACT_POINT_TYPE;');
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
13033: SELECT a.ATTRIBUTE_NAME,
13034: f.PROCEDURE_NAME,
13035: f.STAGED_ATTRIBUTE_COLUMN
13036: FROM HZ_TRANS_FUNCTIONS_VL f,
13037: HZ_TRANS_ATTRIBUTES_VL a,
13038: HZ_MATCH_RULE_PRIMARY pattr,
13039: HZ_PRIMARY_TRANS pfunc
13040: WHERE pattr.MATCH_RULE_ID = p_rule_id
13041: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13048: SELECT a.ATTRIBUTE_NAME,
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
13149:
13150: for FUNCS IN (
13151: SELECT a.ATTRIBUTE_ID, a.ATTRIBUTE_NAME
13152: FROM HZ_TRANS_FUNCTIONS_VL f,
13153: HZ_TRANS_ATTRIBUTES_VL a,
13154: HZ_MATCH_RULE_PRIMARY pattr,
13155: HZ_PRIMARY_TRANS pfunc
13156: WHERE pattr.MATCH_RULE_ID = p_rule_id
13157: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13162: UNION
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
13259: l(' x_stage_list(I).CONTACT_POINT_TYPE := p_search_list(I).CONTACT_POINT_TYPE;');
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
13279: SELECT a.ATTRIBUTE_NAME,
13280: f.PROCEDURE_NAME,
13281: f.STAGED_ATTRIBUTE_COLUMN
13282: FROM HZ_TRANS_FUNCTIONS_VL f,
13283: HZ_TRANS_ATTRIBUTES_VL a,
13284: HZ_MATCH_RULE_PRIMARY pattr,
13285: HZ_PRIMARY_TRANS pfunc
13286: WHERE pattr.MATCH_RULE_ID = p_rule_id
13287: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13294: SELECT a.ATTRIBUTE_NAME,
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
13347: SELECT a.ATTRIBUTE_NAME,
13348: f.PROCEDURE_NAME,
13349: f.STAGED_ATTRIBUTE_COLUMN
13350: FROM HZ_TRANS_FUNCTIONS_VL f,
13351: HZ_TRANS_ATTRIBUTES_VL a,
13352: HZ_MATCH_RULE_PRIMARY pattr,
13353: HZ_PRIMARY_TRANS pfunc
13354: WHERE pattr.MATCH_RULE_ID = p_rule_id
13355: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13362: SELECT a.ATTRIBUTE_NAME,
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
13461: l(' g_other_party_level_attribs:=FALSE;');
13462: NONE:=TRUE;
13463: for PRIMATTRS IN (
13464: SELECT a.ATTRIBUTE_NAME
13465: FROM HZ_TRANS_ATTRIBUTES_VL a,
13466: HZ_MATCH_RULE_PRIMARY sattr
13467: WHERE sattr.MATCH_RULE_ID = p_rule_id
13468: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13469: AND a.ENTITY_NAME = 'PARTY'
13486:
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
13502: SELECT a.ATTRIBUTE_NAME,a.attribute_id,
13503: f.PROCEDURE_NAME,
13504: f.STAGED_ATTRIBUTE_COLUMN
13505: FROM HZ_TRANS_FUNCTIONS_VL f,
13506: HZ_TRANS_ATTRIBUTES_VL a,
13507: HZ_MATCH_RULE_PRIMARY pattr,
13508: HZ_PRIMARY_TRANS pfunc
13509: WHERE pattr.MATCH_RULE_ID = p_rule_id
13510: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13517: SELECT a.ATTRIBUTE_NAME,a.attribute_id,
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
13532: SELECT a.attribute_name,a.attribute_id,
13533: f.PROCEDURE_NAME,
13534: f.STAGED_ATTRIBUTE_COLUMN
13535: FROM HZ_TRANS_FUNCTIONS_VL f,
13536: HZ_TRANS_ATTRIBUTES_VL a
13537: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13538: AND a.entity_name = 'PARTY'
13539: AND a.attribute_name='PARTY_TYPE'
13540: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
13656: l(' BEGIN');
13657:
13658: FIRST := TRUE;
13659: FOR CPTS IN (SELECT ATTRIBUTE_NAME
13660: FROM HZ_TRANS_ATTRIBUTES_VL a,
13661: HZ_MATCH_RULE_PRIMARY p
13662: WHERE p.match_rule_id = p_rule_id
13663: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13664: AND p.attribute_id = a.attribute_id
13684: END IF;
13685:
13686: FOR PRIMATTRS IN (
13687: SELECT ATTRIBUTE_NAME
13688: FROM HZ_TRANS_ATTRIBUTES_VL a,
13689: HZ_MATCH_RULE_PRIMARY p
13690: WHERE p.match_rule_id = p_rule_id
13691: AND p.attribute_id = a.attribute_id
13692: AND a.ENTITY_NAME = 'PARTY'
13697: END LOOP;
13698:
13699: FOR PRIMATTRS IN (
13700: SELECT ENTITY_NAME, ATTRIBUTE_NAME
13701: FROM HZ_TRANS_ATTRIBUTES_VL a,
13702: HZ_MATCH_RULE_PRIMARY p
13703: WHERE p.match_rule_id = p_rule_id
13704: AND a.ENTITY_NAME <> 'PARTY'
13705: AND p.attribute_id = a.attribute_id
13753: l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
13754: FIRST := TRUE;
13755: FOR PRIMATTRS IN (
13756: SELECT ATTRIBUTE_NAME
13757: FROM HZ_TRANS_ATTRIBUTES_VL a,
13758: HZ_MATCH_RULE_PRIMARY p
13759: WHERE p.match_rule_id = p_rule_id
13760: AND a.ENTITY_NAME = 'PARTY_SITES'
13761: AND p.attribute_id = a.attribute_id)
13780: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
13781: FIRST := TRUE;
13782: FOR PRIMATTRS IN (
13783: SELECT ATTRIBUTE_NAME
13784: FROM HZ_TRANS_ATTRIBUTES_VL a,
13785: HZ_MATCH_RULE_PRIMARY p
13786: WHERE p.match_rule_id = p_rule_id
13787: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13788: AND p.attribute_id = a.attribute_id)
13809: l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
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)
13836: l(' FOR I IN 1..p_contact_point_list.COUNT 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)
13891: l(' FOR I IN 1..p_contact_list.COUNT LOOP');
13892: FIRST := TRUE;
13893: FOR PRIMATTRS IN (
13894: SELECT ATTRIBUTE_NAME
13895: FROM HZ_TRANS_ATTRIBUTES_VL a,
13896: HZ_MATCH_RULE_PRIMARY p
13897: WHERE p.match_rule_id = p_rule_id
13898: AND a.ENTITY_NAME = 'CONTACTS'
13899: AND p.attribute_id = a.attribute_id)
13918: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
13919: FIRST := TRUE;
13920: FOR PRIMATTRS IN (
13921: SELECT ATTRIBUTE_NAME
13922: FROM HZ_TRANS_ATTRIBUTES_VL a,
13923: HZ_MATCH_RULE_PRIMARY p
13924: WHERE p.match_rule_id = p_rule_id
13925: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13926: AND p.attribute_id = a.attribute_id)
13946: l(' FOR I IN 1..p_contact_list.COUNT 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)
13974: l(' FOR I IN 1..p_contact_point_list.COUNT 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)
14034: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
14035: FIRST := TRUE;
14036: FOR PRIMATTRS IN (
14037: SELECT ATTRIBUTE_NAME
14038: FROM HZ_TRANS_ATTRIBUTES_VL a,
14039: HZ_MATCH_RULE_PRIMARY p
14040: WHERE p.match_rule_id = p_rule_id
14041: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14042: AND p.attribute_id = a.attribute_id)
14061: l(' FOR I IN 1..p_contact_point_list.COUNT 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)
14109: ldbg_s('Inside calling procedure - generate_custom_code');
14110:
14111: FOR CUSTATTRS IN (
14112: SELECT distinct ATTRIBUTE_NAME, CUSTOM_ATTRIBUTE_PROCEDURE
14113: FROM HZ_TRANS_ATTRIBUTES_VL a,
14114: HZ_MATCH_RULE_PRIMARY p
14115: WHERE p.match_rule_id = p_rule_id
14116: AND p.attribute_id = a.attribute_id
14117: AND a.ENTITY_NAME = p_entity
14118: AND (a.SOURCE_TABLE = 'CUSTOM'
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
14175: -- Query number of party attributes
14176: SELECT COUNT(*) INTO l_num_attrs
14177: FROM (
14178: SELECT a.attribute_id
14179: FROM HZ_TRANS_ATTRIBUTES_VL a,
14180: HZ_MATCH_RULE_PRIMARY p
14181: WHERE p.match_rule_id = p_rule_id
14182: AND p.attribute_id = a.attribute_id
14183: AND a.ENTITY_NAME = 'PARTY'
14184:
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'
14200:
14201: SELECT COUNT(*) INTO l_num_attrs
14202: FROM (
14203: SELECT a.attribute_id
14204: FROM HZ_TRANS_ATTRIBUTES_VL a,
14205: HZ_MATCH_RULE_PRIMARY p
14206: WHERE p.match_rule_id = p_rule_id
14207: AND p.attribute_id = a.attribute_id
14208: AND a.ENTITY_NAME = 'PARTY_SITES'
14209:
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'
14266: -- Query number of contact attributes
14267: SELECT COUNT(*) INTO l_num_attrs
14268: FROM (
14269: SELECT a.attribute_id
14270: FROM HZ_TRANS_ATTRIBUTES_VL a,
14271: HZ_MATCH_RULE_PRIMARY p
14272: WHERE p.match_rule_id = p_rule_id
14273: AND p.attribute_id = a.attribute_id
14274: AND a.ENTITY_NAME = 'CONTACTS'
14275:
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'
14305: -- Query number of contact point attributes
14306: SELECT COUNT(*) INTO l_num_attrs
14307: FROM (
14308: SELECT a.attribute_id
14309: FROM HZ_TRANS_ATTRIBUTES_VL a,
14310: HZ_MATCH_RULE_PRIMARY p
14311: WHERE p.match_rule_id = p_rule_id
14312: AND p.attribute_id = a.attribute_id
14313: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14314:
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'
14394: -- Query number of party attributes
14395: SELECT COUNT(*) INTO l_num_attrs
14396: FROM (
14397: SELECT a.attribute_id
14398: FROM HZ_TRANS_ATTRIBUTES_VL a,
14399: HZ_MATCH_RULE_PRIMARY p
14400: WHERE p.match_rule_id = p_rule_id
14401: AND p.attribute_id = a.attribute_id
14402: AND a.ENTITY_NAME = 'PARTY'
14403:
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'
14420:
14421: SELECT COUNT(*) INTO l_num_attrs
14422: FROM (
14423: SELECT a.attribute_id
14424: FROM HZ_TRANS_ATTRIBUTES_VL a,
14425: HZ_MATCH_RULE_PRIMARY p
14426: WHERE p.match_rule_id = p_rule_id
14427: AND p.attribute_id = a.attribute_id
14428: AND a.ENTITY_NAME = 'PARTY_SITES'
14429:
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'
14448: -- Query number of contact attributes
14449: SELECT COUNT(*) INTO l_num_attrs
14450: FROM (
14451: SELECT a.attribute_id
14452: FROM HZ_TRANS_ATTRIBUTES_VL a,
14453: HZ_MATCH_RULE_PRIMARY p
14454: WHERE p.match_rule_id = p_rule_id
14455: AND p.attribute_id = a.attribute_id
14456: AND a.ENTITY_NAME = 'CONTACTS'
14457:
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'
14475: -- Query number of contact point attributes
14476: SELECT COUNT(*) INTO l_num_attrs
14477: FROM (
14478: SELECT a.attribute_id
14479: FROM HZ_TRANS_ATTRIBUTES_VL a,
14480: HZ_MATCH_RULE_PRIMARY p
14481: WHERE p.match_rule_id = p_rule_id
14482: AND p.attribute_id = a.attribute_id
14483: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14484:
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'
14535:
14536: SELECT COUNT(*) INTO l_num_attrs
14537: FROM (
14538: SELECT a.attribute_id
14539: FROM HZ_TRANS_ATTRIBUTES_VL a,
14540: HZ_MATCH_RULE_PRIMARY p
14541: WHERE p.match_rule_id = p_rule_id
14542: AND p.attribute_id = a.attribute_id
14543: AND a.ENTITY_NAME = 'PARTY'
14547: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14564: l(' SELECT ');
14565: FIRST := TRUE;
14566: FOR ATTRS IN (
14567: 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
14568: FROM HZ_TRANS_ATTRIBUTES_VL a,
14569: HZ_MATCH_RULE_PRIMARY p
14570: WHERE p.match_rule_id = p_rule_id
14571: AND p.attribute_id = a.attribute_id
14572: AND a.ENTITY_NAME = 'PARTY'
14576: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14599: l(' INTO ');
14600: FIRST := TRUE;
14601: FOR ATTRS IN (
14602: SELECT distinct a.ATTRIBUTE_NAME
14603: FROM HZ_TRANS_ATTRIBUTES_VL a,
14604: HZ_MATCH_RULE_PRIMARY p
14605: WHERE p.match_rule_id = p_rule_id
14606: AND p.attribute_id = a.attribute_id
14607: AND a.ENTITY_NAME = 'PARTY'
14611: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14643:
14644: SELECT COUNT(*) INTO l_num_attrs
14645: FROM (
14646: SELECT a.attribute_id
14647: FROM HZ_TRANS_ATTRIBUTES_VL a,
14648: HZ_MATCH_RULE_PRIMARY p
14649: WHERE p.match_rule_id = p_rule_id
14650: AND p.attribute_id = a.attribute_id
14651: AND a.ENTITY_NAME = 'PARTY'
14655: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14672: l(' SELECT ');
14673: FIRST := TRUE;
14674: FOR ATTRS IN (
14675: 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
14676: FROM HZ_TRANS_ATTRIBUTES_VL a,
14677: HZ_MATCH_RULE_PRIMARY p
14678: WHERE p.match_rule_id = p_rule_id
14679: AND p.attribute_id = a.attribute_id
14680: AND a.ENTITY_NAME = 'PARTY'
14684: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14707: l(' INTO ');
14708: FIRST := TRUE;
14709: FOR ATTRS IN (
14710: SELECT distinct a.ATTRIBUTE_NAME
14711: FROM HZ_TRANS_ATTRIBUTES_VL a,
14712: HZ_MATCH_RULE_PRIMARY p
14713: WHERE p.match_rule_id = p_rule_id
14714: AND p.attribute_id = a.attribute_id
14715: AND a.ENTITY_NAME = 'PARTY'
14719: a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES')
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'
14772: l(' CURSOR c_party_sites(cp_party_site_id NUMBER) IS');
14773: l(' SELECT party_site_id');
14774: FOR ATTRS IN (
14775: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14776: FROM HZ_TRANS_ATTRIBUTES_VL a,
14777: HZ_MATCH_RULE_PRIMARY p
14778: WHERE p.match_rule_id = p_rule_id
14779: AND p.attribute_id = a.attribute_id
14780: AND a.ENTITY_NAME = 'PARTY_SITES'
14780: AND a.ENTITY_NAME = 'PARTY_SITES'
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'
14808: l(' FETCH c_party_sites INTO');
14809: l(' l_party_site_id');
14810: FOR ATTRS IN (
14811: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14812: FROM HZ_TRANS_ATTRIBUTES_VL a,
14813: HZ_MATCH_RULE_PRIMARY p
14814: WHERE p.match_rule_id = p_rule_id
14815: AND p.attribute_id = a.attribute_id
14816: AND a.ENTITY_NAME = 'PARTY_SITES'
14818:
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'
14862: l(' CURSOR c_contacts(cp_org_contact_id NUMBER) IS');
14863: l(' SELECT org_contact_id');
14864: FOR ATTRS IN (
14865: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14866: FROM HZ_TRANS_ATTRIBUTES_VL a,
14867: HZ_MATCH_RULE_PRIMARY p
14868: WHERE p.match_rule_id = p_rule_id
14869: AND p.attribute_id = a.attribute_id
14870: AND a.ENTITY_NAME = 'CONTACTS'
14870: AND a.ENTITY_NAME = 'CONTACTS'
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'
14903: l(' FETCH c_contacts INTO');
14904: l(' l_org_contact_id');
14905: FOR ATTRS IN (
14906: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14907: FROM HZ_TRANS_ATTRIBUTES_VL a,
14908: HZ_MATCH_RULE_PRIMARY p
14909: WHERE p.match_rule_id = p_rule_id
14910: AND p.attribute_id = a.attribute_id
14911: AND a.ENTITY_NAME = 'CONTACTS'
14913:
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'
14957: l(' CURSOR c_cpts(cp_contact_point_id NUMBER) IS');
14958: l(' SELECT contact_point_id, contact_point_type');
14959: FOR ATTRS IN (
14960: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14961: FROM HZ_TRANS_ATTRIBUTES_VL a,
14962: HZ_MATCH_RULE_PRIMARY p
14963: WHERE p.match_rule_id = p_rule_id
14964: AND p.attribute_id = a.attribute_id
14965: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14967:
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'
14996: l(' l_contact_point_id, x_contact_point_list(J).contact_point_type');
14997:
14998: FOR ATTRS IN (
14999: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
15000: FROM HZ_TRANS_ATTRIBUTES_VL a,
15001: HZ_MATCH_RULE_PRIMARY p
15002: WHERE p.match_rule_id = p_rule_id
15003: AND p.attribute_id = a.attribute_id
15004: AND a.ENTITY_NAME = 'CONTACT_POINTS'
15006:
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'
15495: l_num_primary NUMBER;
15496: BEGIN
15497: SELECT count(1) INTO l_num_primary
15498: FROM HZ_MATCH_RULE_PRIMARY p,
15499: HZ_TRANS_ATTRIBUTES_VL a
15500: WHERE p.match_rule_id = p_rule_id
15501: AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
15502: AND ENTITY_NAME = p_entity;
15503: RETURN l_num_primary;
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;
15519: RETURN l_num_secondary;
16078:
16079: IF p_eval_level = 'PARTY' THEN
16080: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16081: ' attr.attribute_name,operation,value,rank '||
16082: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16083: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16084: ' AND cond.attribute_id= attr.attribute_id (+) '||
16085: ' ORDER BY rank ASC';
16086: ELSIF p_eval_level = 'PARTY_SITES' then
16085: ' ORDER BY rank ASC';
16086: ELSIF p_eval_level = 'PARTY_SITES' then
16087: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16088: ' attr.attribute_name,operation,value,rank '||
16089: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16090: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16091: ' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''CONTACTS'') '||
16092: ' AND cond.attribute_id= attr.attribute_id (+) '||
16093: ' ORDER BY rank ASC';
16093: ' ORDER BY rank ASC';
16094: ELSIF p_eval_level = 'CONTACTS' then
16095: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16096: ' attr.attribute_name,operation,value,rank '||
16097: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16098: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16099: ' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''PARTY_SITES'') '||
16100: ' AND cond.attribute_id= attr.attribute_id (+) '||
16101: ' ORDER BY rank ASC';
16101: ' ORDER BY rank ASC';
16102: ELSIF p_eval_level = 'CONTACT_POINTS' then
16103: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16104: ' attr.attribute_name,operation,value,rank '||
16105: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16106: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16107: ' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''PARTY_SITES'',''CONTACTS'') '||
16108: ' AND cond.attribute_id= attr.attribute_id (+) '||
16109: ' ORDER BY rank ASC';
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: