[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
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
3165: l(' RETURN total;');
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
3471: g_party_and_query := null;
3472: cnt := cnt+1;
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
3583: cnt := 1;
3584: for PRIMTRANS IN (
3585: SELECT f.STAGED_ATTRIBUTE_COLUMN
3586: FROM HZ_TRANS_FUNCTIONS_VL f,
3587: HZ_TRANS_ATTRIBUTES_VL a,
3588: HZ_MATCH_RULE_PRIMARY pattr,
3589: HZ_PRIMARY_TRANS pfunc
3590: WHERE pattr.MATCH_RULE_ID = p_rule_id
3591: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3597: UNION
3598:
3599: SELECT f.STAGED_ATTRIBUTE_COLUMN
3600: FROM HZ_TRANS_FUNCTIONS_VL f,
3601: HZ_TRANS_ATTRIBUTES_VL a
3602: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
3603: AND a.entity_name = 'PARTY'
3604: AND a.attribute_name='PARTY_TYPE'
3605: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
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
3716: FIRST := TRUE;
3717: cnt := 1;
3718: FOR PATTRS IN (
3719: SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
3720: FROM HZ_TRANS_ATTRIBUTES_VL a,
3721: HZ_MATCH_RULE_PRIMARY p
3722: WHERE p.match_rule_id = p_rule_id
3723: AND p.attribute_id = a.attribute_id
3724: AND a.ENTITY_NAME = 'PARTY'
3756: END IF;
3757:
3758: FOR PATTRS IN (
3759: SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
3760: FROM HZ_TRANS_ATTRIBUTES_VL a,
3761: HZ_MATCH_RULE_PRIMARY p
3762: WHERE p.match_rule_id = p_rule_id
3763: AND p.attribute_id = a.attribute_id
3764: AND a.ENTITY_NAME = 'PARTY'
3795: ldbg_s('p_party_contains_str string is NOT NULL and other conditions met');
3796: FIRST := TRUE;
3797: FOR DENATTR IN (
3798: SELECT PRIMARY_ATTRIBUTE_ID, ATTRIBUTE_NAME
3799: FROM HZ_TRANS_ATTRIBUTES_VL a,
3800: HZ_MATCH_RULE_PRIMARY p
3801: WHERE p.match_rule_id = p_rule_id
3802: AND p.attribute_id = a.attribute_id
3803: AND a.ENTITY_NAME = 'PARTY_SITES'
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
5915: SELECT a.attribute_name,
5916: f.PROCEDURE_NAME,
5917: f.STAGED_ATTRIBUTE_COLUMN
5918: FROM HZ_TRANS_FUNCTIONS_VL f,
5919: HZ_TRANS_ATTRIBUTES_VL a
5920: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
5921: AND a.entity_name = 'PARTY'
5922: AND a.attribute_name='PARTY_TYPE'
5923: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
7344:
7345: FIRST := TRUE;
7346: FOR ATTRS IN (
7347: SELECT a.attribute_id, a.attribute_name, a.entity_name
7348: FROM HZ_TRANS_ATTRIBUTES_VL a,
7349: HZ_MATCH_RULE_PRIMARY p
7350: WHERE p.match_rule_id = p_rule_id
7351: AND p.attribute_id = a.attribute_id
7352:
7352:
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');
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'
8506:
8507: FIRST := TRUE;
8508: FOR PRIMATTRS IN (
8509: SELECT ATTRIBUTE_NAME
8510: FROM HZ_TRANS_ATTRIBUTES_VL a,
8511: HZ_MATCH_RULE_PRIMARY p
8512: WHERE p.match_rule_id = p_rule_id
8513: AND p.attribute_id = a.attribute_id
8514: AND a.ENTITY_NAME = 'PARTY'
8555: l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
8556: FIRST := TRUE;
8557: FOR PRIMATTRS IN (
8558: SELECT ATTRIBUTE_NAME
8559: FROM HZ_TRANS_ATTRIBUTES_VL a,
8560: HZ_MATCH_RULE_PRIMARY p
8561: WHERE p.match_rule_id = p_rule_id
8562: AND a.ENTITY_NAME = 'PARTY_SITES'
8563: AND p.attribute_id = a.attribute_id)
8619: l(' FOR I IN 1..p_contact_list.COUNT LOOP');
8620: FIRST := TRUE;
8621: FOR PRIMATTRS IN (
8622: SELECT ATTRIBUTE_NAME
8623: FROM HZ_TRANS_ATTRIBUTES_VL a,
8624: HZ_MATCH_RULE_PRIMARY p
8625: WHERE p.match_rule_id = p_rule_id
8626: AND a.ENTITY_NAME = 'CONTACTS'
8627: AND p.attribute_id = a.attribute_id)
8681: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
8682: FIRST := TRUE;
8683: FOR PRIMATTRS IN (
8684: SELECT ATTRIBUTE_NAME
8685: FROM HZ_TRANS_ATTRIBUTES_VL a,
8686: HZ_MATCH_RULE_PRIMARY p
8687: WHERE p.match_rule_id = p_rule_id
8688: AND a.ENTITY_NAME = 'CONTACT_POINTS'
8689: AND p.attribute_id = a.attribute_id)
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
8895: l(' RETURN total;');
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
9114: g_party_and_query := null;
9115: cnt := cnt+1;
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
9170: cnt := 1;
9171: for PRIMTRANS IN (
9172: SELECT f.STAGED_ATTRIBUTE_COLUMN
9173: FROM HZ_TRANS_FUNCTIONS_VL f,
9174: HZ_TRANS_ATTRIBUTES_VL a,
9175: HZ_MATCH_RULE_PRIMARY pattr,
9176: HZ_PRIMARY_TRANS pfunc
9177: WHERE pattr.MATCH_RULE_ID = p_rule_id
9178: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
9184: UNION
9185:
9186: SELECT f.STAGED_ATTRIBUTE_COLUMN
9187: FROM HZ_TRANS_FUNCTIONS_VL f,
9188: HZ_TRANS_ATTRIBUTES_VL a
9189: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
9190: AND a.entity_name = 'PARTY'
9191: AND a.attribute_name='PARTY_TYPE'
9192: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
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
10558: SELECT a.attribute_name,
10559: f.PROCEDURE_NAME,
10560: f.STAGED_ATTRIBUTE_COLUMN
10561: FROM HZ_TRANS_FUNCTIONS_VL f,
10562: HZ_TRANS_ATTRIBUTES_VL a
10563: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
10564: AND a.entity_name = 'PARTY'
10565: AND a.attribute_name='PARTY_TYPE'
10566: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
11750:
11751: FIRST := TRUE;
11752: FOR ATTRS IN (
11753: SELECT a.attribute_id, a.attribute_name, a.entity_name
11754: FROM HZ_TRANS_ATTRIBUTES_VL a,
11755: HZ_MATCH_RULE_PRIMARY p
11756: WHERE p.match_rule_id = p_rule_id
11757: AND p.attribute_id = a.attribute_id
11758:
11758:
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');
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'
12858: l(' IF p_search_ctx THEN');
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
12874: SELECT a.ATTRIBUTE_NAME,
12875: f.PROCEDURE_NAME,
12876: f.STAGED_ATTRIBUTE_COLUMN
12877: FROM HZ_TRANS_FUNCTIONS_VL f,
12878: HZ_TRANS_ATTRIBUTES_VL a,
12879: HZ_MATCH_RULE_PRIMARY pattr,
12880: HZ_PRIMARY_TRANS pfunc
12881: WHERE pattr.MATCH_RULE_ID = p_rule_id
12882: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12889: SELECT a.ATTRIBUTE_NAME,
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
12904: SELECT a.attribute_name,
12905: f.PROCEDURE_NAME,
12906: f.STAGED_ATTRIBUTE_COLUMN
12907: FROM HZ_TRANS_FUNCTIONS_VL f,
12908: HZ_TRANS_ATTRIBUTES_VL a
12909: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
12910: AND a.entity_name = 'PARTY'
12911: AND a.attribute_name='PARTY_TYPE'
12912: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
13038: l(' x_stage_list(I).CONTACT_POINT_TYPE := p_search_list(I).CONTACT_POINT_TYPE;');
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
13089: SELECT a.ATTRIBUTE_NAME,
13090: f.PROCEDURE_NAME,
13091: f.STAGED_ATTRIBUTE_COLUMN
13092: FROM HZ_TRANS_FUNCTIONS_VL f,
13093: HZ_TRANS_ATTRIBUTES_VL a,
13094: HZ_MATCH_RULE_PRIMARY pattr,
13095: HZ_PRIMARY_TRANS pfunc
13096: WHERE pattr.MATCH_RULE_ID = p_rule_id
13097: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13104: SELECT a.ATTRIBUTE_NAME,
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
13205:
13206: for FUNCS IN (
13207: SELECT a.ATTRIBUTE_ID, a.ATTRIBUTE_NAME
13208: FROM HZ_TRANS_FUNCTIONS_VL f,
13209: HZ_TRANS_ATTRIBUTES_VL a,
13210: HZ_MATCH_RULE_PRIMARY pattr,
13211: HZ_PRIMARY_TRANS pfunc
13212: WHERE pattr.MATCH_RULE_ID = p_rule_id
13213: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13218: UNION
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
13315: l(' x_stage_list(I).CONTACT_POINT_TYPE := p_search_list(I).CONTACT_POINT_TYPE;');
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
13335: SELECT a.ATTRIBUTE_NAME,
13336: f.PROCEDURE_NAME,
13337: f.STAGED_ATTRIBUTE_COLUMN
13338: FROM HZ_TRANS_FUNCTIONS_VL f,
13339: HZ_TRANS_ATTRIBUTES_VL a,
13340: HZ_MATCH_RULE_PRIMARY pattr,
13341: HZ_PRIMARY_TRANS pfunc
13342: WHERE pattr.MATCH_RULE_ID = p_rule_id
13343: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13350: SELECT a.ATTRIBUTE_NAME,
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
13403: SELECT a.ATTRIBUTE_NAME,
13404: f.PROCEDURE_NAME,
13405: f.STAGED_ATTRIBUTE_COLUMN
13406: FROM HZ_TRANS_FUNCTIONS_VL f,
13407: HZ_TRANS_ATTRIBUTES_VL a,
13408: HZ_MATCH_RULE_PRIMARY pattr,
13409: HZ_PRIMARY_TRANS pfunc
13410: WHERE pattr.MATCH_RULE_ID = p_rule_id
13411: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13418: SELECT a.ATTRIBUTE_NAME,
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
13517: l(' g_other_party_level_attribs:=FALSE;');
13518: NONE:=TRUE;
13519: for PRIMATTRS IN (
13520: SELECT a.ATTRIBUTE_NAME
13521: FROM HZ_TRANS_ATTRIBUTES_VL a,
13522: HZ_MATCH_RULE_PRIMARY sattr
13523: WHERE sattr.MATCH_RULE_ID = p_rule_id
13524: AND sattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13525: AND a.ENTITY_NAME = 'PARTY'
13542:
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
13558: SELECT a.ATTRIBUTE_NAME,a.attribute_id,
13559: f.PROCEDURE_NAME,
13560: f.STAGED_ATTRIBUTE_COLUMN
13561: FROM HZ_TRANS_FUNCTIONS_VL f,
13562: HZ_TRANS_ATTRIBUTES_VL a,
13563: HZ_MATCH_RULE_PRIMARY pattr,
13564: HZ_PRIMARY_TRANS pfunc
13565: WHERE pattr.MATCH_RULE_ID = p_rule_id
13566: AND pattr.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13573: SELECT a.ATTRIBUTE_NAME,a.attribute_id,
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
13588: SELECT a.attribute_name,a.attribute_id,
13589: f.PROCEDURE_NAME,
13590: f.STAGED_ATTRIBUTE_COLUMN
13591: FROM HZ_TRANS_FUNCTIONS_VL f,
13592: HZ_TRANS_ATTRIBUTES_VL a
13593: WHERE f.ATTRIBUTE_ID = a.ATTRIBUTE_ID
13594: AND a.entity_name = 'PARTY'
13595: AND a.attribute_name='PARTY_TYPE'
13596: AND f.PROCEDURE_NAME='HZ_TRANS_PKG.EXACT'
13712: l(' BEGIN');
13713:
13714: FIRST := TRUE;
13715: FOR CPTS IN (SELECT ATTRIBUTE_NAME
13716: FROM HZ_TRANS_ATTRIBUTES_VL a,
13717: HZ_MATCH_RULE_PRIMARY p
13718: WHERE p.match_rule_id = p_rule_id
13719: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13720: AND p.attribute_id = a.attribute_id
13740: END IF;
13741:
13742: FOR PRIMATTRS IN (
13743: SELECT ATTRIBUTE_NAME
13744: FROM HZ_TRANS_ATTRIBUTES_VL a,
13745: HZ_MATCH_RULE_PRIMARY p
13746: WHERE p.match_rule_id = p_rule_id
13747: AND p.attribute_id = a.attribute_id
13748: AND a.ENTITY_NAME = 'PARTY'
13753: END LOOP;
13754:
13755: FOR PRIMATTRS IN (
13756: SELECT ENTITY_NAME, 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'
13761: AND p.attribute_id = a.attribute_id
13809: l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
13810: FIRST := TRUE;
13811: FOR PRIMATTRS IN (
13812: SELECT ATTRIBUTE_NAME
13813: FROM HZ_TRANS_ATTRIBUTES_VL a,
13814: HZ_MATCH_RULE_PRIMARY p
13815: WHERE p.match_rule_id = p_rule_id
13816: AND a.ENTITY_NAME = 'PARTY_SITES'
13817: AND p.attribute_id = a.attribute_id)
13836: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
13837: FIRST := TRUE;
13838: FOR PRIMATTRS IN (
13839: SELECT ATTRIBUTE_NAME
13840: FROM HZ_TRANS_ATTRIBUTES_VL a,
13841: HZ_MATCH_RULE_PRIMARY p
13842: WHERE p.match_rule_id = p_rule_id
13843: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13844: AND p.attribute_id = a.attribute_id)
13865: l(' FOR I IN 1..p_party_site_list.COUNT LOOP');
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)
13892: l(' FOR I IN 1..p_contact_point_list.COUNT 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)
13947: l(' FOR I IN 1..p_contact_list.COUNT LOOP');
13948: FIRST := TRUE;
13949: FOR PRIMATTRS IN (
13950: SELECT ATTRIBUTE_NAME
13951: FROM HZ_TRANS_ATTRIBUTES_VL a,
13952: HZ_MATCH_RULE_PRIMARY p
13953: WHERE p.match_rule_id = p_rule_id
13954: AND a.ENTITY_NAME = 'CONTACTS'
13955: AND p.attribute_id = a.attribute_id)
13974: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
13975: FIRST := TRUE;
13976: FOR PRIMATTRS IN (
13977: SELECT ATTRIBUTE_NAME
13978: FROM HZ_TRANS_ATTRIBUTES_VL a,
13979: HZ_MATCH_RULE_PRIMARY p
13980: WHERE p.match_rule_id = p_rule_id
13981: AND a.ENTITY_NAME = 'CONTACT_POINTS'
13982: AND p.attribute_id = a.attribute_id)
14002: l(' FOR I IN 1..p_contact_list.COUNT 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)
14030: l(' FOR I IN 1..p_contact_point_list.COUNT 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)
14090: l(' FOR I IN 1..p_contact_point_list.COUNT LOOP');
14091: FIRST := TRUE;
14092: FOR PRIMATTRS IN (
14093: SELECT ATTRIBUTE_NAME
14094: FROM HZ_TRANS_ATTRIBUTES_VL a,
14095: HZ_MATCH_RULE_PRIMARY p
14096: WHERE p.match_rule_id = p_rule_id
14097: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14098: AND p.attribute_id = a.attribute_id)
14117: l(' FOR I IN 1..p_contact_point_list.COUNT 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)
14165: ldbg_s('Inside calling procedure - generate_custom_code');
14166:
14167: FOR CUSTATTRS IN (
14168: SELECT distinct ATTRIBUTE_NAME, CUSTOM_ATTRIBUTE_PROCEDURE
14169: FROM HZ_TRANS_ATTRIBUTES_VL a,
14170: HZ_MATCH_RULE_PRIMARY p
14171: WHERE p.match_rule_id = p_rule_id
14172: AND p.attribute_id = a.attribute_id
14173: AND a.ENTITY_NAME = p_entity
14174: AND (a.SOURCE_TABLE = 'CUSTOM'
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
14231: -- Query number of party attributes
14232: SELECT COUNT(*) INTO l_num_attrs
14233: FROM (
14234: SELECT a.attribute_id
14235: FROM HZ_TRANS_ATTRIBUTES_VL a,
14236: HZ_MATCH_RULE_PRIMARY p
14237: WHERE p.match_rule_id = p_rule_id
14238: AND p.attribute_id = a.attribute_id
14239: AND a.ENTITY_NAME = 'PARTY'
14240:
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'
14256:
14257: SELECT COUNT(*) INTO l_num_attrs
14258: FROM (
14259: SELECT a.attribute_id
14260: FROM HZ_TRANS_ATTRIBUTES_VL a,
14261: HZ_MATCH_RULE_PRIMARY p
14262: WHERE p.match_rule_id = p_rule_id
14263: AND p.attribute_id = a.attribute_id
14264: AND a.ENTITY_NAME = 'PARTY_SITES'
14265:
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'
14322: -- Query number of contact attributes
14323: SELECT COUNT(*) INTO l_num_attrs
14324: FROM (
14325: SELECT a.attribute_id
14326: FROM HZ_TRANS_ATTRIBUTES_VL a,
14327: HZ_MATCH_RULE_PRIMARY p
14328: WHERE p.match_rule_id = p_rule_id
14329: AND p.attribute_id = a.attribute_id
14330: AND a.ENTITY_NAME = 'CONTACTS'
14331:
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'
14361: -- Query number of contact point attributes
14362: SELECT COUNT(*) INTO l_num_attrs
14363: FROM (
14364: SELECT a.attribute_id
14365: FROM HZ_TRANS_ATTRIBUTES_VL a,
14366: HZ_MATCH_RULE_PRIMARY p
14367: WHERE p.match_rule_id = p_rule_id
14368: AND p.attribute_id = a.attribute_id
14369: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14370:
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'
14450: -- Query number of party attributes
14451: SELECT COUNT(*) INTO l_num_attrs
14452: FROM (
14453: SELECT a.attribute_id
14454: FROM HZ_TRANS_ATTRIBUTES_VL a,
14455: HZ_MATCH_RULE_PRIMARY p
14456: WHERE p.match_rule_id = p_rule_id
14457: AND p.attribute_id = a.attribute_id
14458: AND a.ENTITY_NAME = 'PARTY'
14459:
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'
14476:
14477: SELECT COUNT(*) INTO l_num_attrs
14478: FROM (
14479: SELECT a.attribute_id
14480: FROM HZ_TRANS_ATTRIBUTES_VL a,
14481: HZ_MATCH_RULE_PRIMARY p
14482: WHERE p.match_rule_id = p_rule_id
14483: AND p.attribute_id = a.attribute_id
14484: AND a.ENTITY_NAME = 'PARTY_SITES'
14485:
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'
14504: -- Query number of contact attributes
14505: SELECT COUNT(*) INTO l_num_attrs
14506: FROM (
14507: SELECT a.attribute_id
14508: FROM HZ_TRANS_ATTRIBUTES_VL a,
14509: HZ_MATCH_RULE_PRIMARY p
14510: WHERE p.match_rule_id = p_rule_id
14511: AND p.attribute_id = a.attribute_id
14512: AND a.ENTITY_NAME = 'CONTACTS'
14513:
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'
14531: -- Query number of contact point attributes
14532: SELECT COUNT(*) INTO l_num_attrs
14533: FROM (
14534: SELECT a.attribute_id
14535: FROM HZ_TRANS_ATTRIBUTES_VL a,
14536: HZ_MATCH_RULE_PRIMARY p
14537: WHERE p.match_rule_id = p_rule_id
14538: AND p.attribute_id = a.attribute_id
14539: AND a.ENTITY_NAME = 'CONTACT_POINTS'
14540:
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'
14591:
14592: SELECT COUNT(*) INTO l_num_attrs
14593: FROM (
14594: SELECT a.attribute_id
14595: FROM HZ_TRANS_ATTRIBUTES_VL a,
14596: HZ_MATCH_RULE_PRIMARY p
14597: WHERE p.match_rule_id = p_rule_id
14598: AND p.attribute_id = a.attribute_id
14599: AND a.ENTITY_NAME = 'PARTY'
14603: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14620: l(' SELECT ');
14621: FIRST := TRUE;
14622: FOR ATTRS IN (
14623: 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
14624: FROM HZ_TRANS_ATTRIBUTES_VL a,
14625: HZ_MATCH_RULE_PRIMARY p
14626: WHERE p.match_rule_id = p_rule_id
14627: AND p.attribute_id = a.attribute_id
14628: AND a.ENTITY_NAME = 'PARTY'
14632: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14655: l(' INTO ');
14656: FIRST := TRUE;
14657: FOR ATTRS IN (
14658: SELECT distinct a.ATTRIBUTE_NAME
14659: FROM HZ_TRANS_ATTRIBUTES_VL a,
14660: HZ_MATCH_RULE_PRIMARY p
14661: WHERE p.match_rule_id = p_rule_id
14662: AND p.attribute_id = a.attribute_id
14663: AND a.ENTITY_NAME = 'PARTY'
14667: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14699:
14700: SELECT COUNT(*) INTO l_num_attrs
14701: FROM (
14702: SELECT a.attribute_id
14703: FROM HZ_TRANS_ATTRIBUTES_VL a,
14704: HZ_MATCH_RULE_PRIMARY p
14705: WHERE p.match_rule_id = p_rule_id
14706: AND p.attribute_id = a.attribute_id
14707: AND a.ENTITY_NAME = 'PARTY'
14711: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14728: l(' SELECT ');
14729: FIRST := TRUE;
14730: FOR ATTRS IN (
14731: 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
14732: FROM HZ_TRANS_ATTRIBUTES_VL a,
14733: HZ_MATCH_RULE_PRIMARY p
14734: WHERE p.match_rule_id = p_rule_id
14735: AND p.attribute_id = a.attribute_id
14736: AND a.ENTITY_NAME = 'PARTY'
14740: a.SOURCE_TABLE = 'HZ_PERSON_PROFILES, HZ_ORGANIZATION_PROFILES')
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'
14763: l(' INTO ');
14764: FIRST := TRUE;
14765: FOR ATTRS IN (
14766: SELECT distinct a.ATTRIBUTE_NAME
14767: FROM HZ_TRANS_ATTRIBUTES_VL a,
14768: HZ_MATCH_RULE_PRIMARY p
14769: WHERE p.match_rule_id = p_rule_id
14770: AND p.attribute_id = a.attribute_id
14771: AND a.ENTITY_NAME = 'PARTY'
14775: a.SOURCE_TABLE = 'HZ_ORGANIZATION_PROFILES, HZ_PERSON_PROFILES')
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'
14828: l(' CURSOR c_party_sites(cp_party_site_id NUMBER) IS');
14829: l(' SELECT party_site_id');
14830: FOR ATTRS IN (
14831: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14832: FROM HZ_TRANS_ATTRIBUTES_VL a,
14833: HZ_MATCH_RULE_PRIMARY p
14834: WHERE p.match_rule_id = p_rule_id
14835: AND p.attribute_id = a.attribute_id
14836: AND a.ENTITY_NAME = 'PARTY_SITES'
14836: AND a.ENTITY_NAME = 'PARTY_SITES'
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'
14864: l(' FETCH c_party_sites INTO');
14865: l(' l_party_site_id');
14866: FOR ATTRS IN (
14867: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14868: FROM HZ_TRANS_ATTRIBUTES_VL a,
14869: HZ_MATCH_RULE_PRIMARY p
14870: WHERE p.match_rule_id = p_rule_id
14871: AND p.attribute_id = a.attribute_id
14872: AND a.ENTITY_NAME = 'PARTY_SITES'
14874:
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'
14918: l(' CURSOR c_contacts(cp_org_contact_id NUMBER) IS');
14919: l(' SELECT org_contact_id');
14920: FOR ATTRS IN (
14921: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14922: FROM HZ_TRANS_ATTRIBUTES_VL a,
14923: HZ_MATCH_RULE_PRIMARY p
14924: WHERE p.match_rule_id = p_rule_id
14925: AND p.attribute_id = a.attribute_id
14926: AND a.ENTITY_NAME = 'CONTACTS'
14926: AND a.ENTITY_NAME = 'CONTACTS'
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'
14959: l(' FETCH c_contacts INTO');
14960: l(' l_org_contact_id');
14961: FOR ATTRS IN (
14962: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
14963: FROM HZ_TRANS_ATTRIBUTES_VL a,
14964: HZ_MATCH_RULE_PRIMARY p
14965: WHERE p.match_rule_id = p_rule_id
14966: AND p.attribute_id = a.attribute_id
14967: AND a.ENTITY_NAME = 'CONTACTS'
14969:
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'
15013: l(' CURSOR c_cpts(cp_contact_point_id NUMBER) IS');
15014: l(' SELECT contact_point_id, contact_point_type');
15015: FOR ATTRS IN (
15016: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
15017: FROM HZ_TRANS_ATTRIBUTES_VL a,
15018: HZ_MATCH_RULE_PRIMARY p
15019: WHERE p.match_rule_id = p_rule_id
15020: AND p.attribute_id = a.attribute_id
15021: AND a.ENTITY_NAME = 'CONTACT_POINTS'
15023:
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'
15052: l(' l_contact_point_id, x_contact_point_list(J).contact_point_type');
15053:
15054: FOR ATTRS IN (
15055: SELECT distinct a.ATTRIBUTE_NAME, a.SOURCE_TABLE
15056: FROM HZ_TRANS_ATTRIBUTES_VL a,
15057: HZ_MATCH_RULE_PRIMARY p
15058: WHERE p.match_rule_id = p_rule_id
15059: AND p.attribute_id = a.attribute_id
15060: AND a.ENTITY_NAME = 'CONTACT_POINTS'
15062:
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'
15551: l_num_primary NUMBER;
15552: BEGIN
15553: SELECT count(1) INTO l_num_primary
15554: FROM HZ_MATCH_RULE_PRIMARY p,
15555: HZ_TRANS_ATTRIBUTES_VL a
15556: WHERE p.match_rule_id = p_rule_id
15557: AND p.ATTRIBUTE_ID = a.ATTRIBUTE_ID
15558: AND ENTITY_NAME = p_entity;
15559: RETURN l_num_primary;
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;
15575: RETURN l_num_secondary;
16136:
16137: IF p_eval_level = 'PARTY' THEN
16138: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16139: ' attr.attribute_name,operation,value,rank '||
16140: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16141: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16142: ' AND cond.attribute_id= attr.attribute_id (+) '||
16143: ' ORDER BY rank ASC';
16144: ELSIF p_eval_level = 'PARTY_SITES' then
16143: ' ORDER BY rank ASC';
16144: ELSIF p_eval_level = 'PARTY_SITES' then
16145: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16146: ' attr.attribute_name,operation,value,rank '||
16147: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16148: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16149: ' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''CONTACTS'') '||
16150: ' AND cond.attribute_id= attr.attribute_id (+) '||
16151: ' ORDER BY rank ASC';
16151: ' ORDER BY rank ASC';
16152: ELSIF p_eval_level = 'CONTACTS' then
16153: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16154: ' attr.attribute_name,operation,value,rank '||
16155: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16156: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16157: ' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''PARTY_SITES'') '||
16158: ' AND cond.attribute_id= attr.attribute_id (+) '||
16159: ' ORDER BY rank ASC';
16159: ' ORDER BY rank ASC';
16160: ELSIF p_eval_level = 'CONTACT_POINTS' then
16161: l_sql :='SELECT condition_match_rule_id,attr.entity_name,'||
16162: ' attr.attribute_name,operation,value,rank '||
16163: ' FROM hz_match_rule_conditions cond,hz_trans_attributes_vl attr '||
16164: ' WHERE cond.match_rule_set_id = '||P_MRULE_SET_ID||
16165: ' AND nvl(attr.entity_name,''XYZ'') NOT IN (''PARTY'',''PARTY_SITES'',''CONTACTS'') '||
16166: ' AND cond.attribute_id= attr.attribute_id (+) '||
16167: ' ORDER BY rank ASC';
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: