[Home] [Help]
6993: | update_contact_point_search |
6994: | |
6995: | DESCRIPTION |
6996: | This procedure updates the address_text column of |
6997: | hz_cust_acct_sites_all with the NULL value |
6998: | only to change the address_text column status |
6999: | so that interMedia index can be created on it to perform text searches. |
7000: | |
7001: | NOTE :- After Calling this procedure the user has to execute the |
7028:
7029: ----(Party level Contact Point)
7030: CURSOR c_pl_cp(p_party_id NUMBER) IS
7031: SELECT ac.CUST_ACCT_SITE_ID
7032: FROM HZ_CUST_ACCOUNTS c,HZ_CUST_ACCT_SITES_ALL ac
7033: WHERE c.party_id=p_party_id
7034: AND c.cust_account_id = ac.cust_account_id;
7035:
7036: ----(Site level contact point)
7036: ----(Site level contact point)
7037: CURSOR c_sl_cp(p_party_site_id NUMBER) IS
7038: SELECT ac.CUST_ACCT_SITE_ID
7039: FROM HZ_PARTY_SITES ps,
7040: HZ_CUST_ACCT_SITES_ALL ac
7041: WHERE ps.PARTY_SITE_ID=p_party_site_id
7042: AND ps.PARTY_SITE_ID=ac.PARTY_SITE_ID;
7043:
7044: ----(Party level relationship's contact point )
7044: ----(Party level relationship's contact point )
7045: CURSOR c_pl_rel_cp(p_party_id NUMBER) IS
7046: SELECT distinct ac.CUST_ACCT_SITE_ID
7047: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7048: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7049: WHERE rel.party_id=p_party_id
7050: AND ar.ROLE_TYPE = 'CONTACT'
7051: AND rel.party_id=ar.party_id
7052: AND rel.subject_id=p.party_id
7056: -----(Site Level relationship's contact point )
7057: CURSOR c_sl_rel_cp(p_party_id NUMBER) IS
7058: SELECT distinct ac.CUST_ACCT_SITE_ID
7059: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7060: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7061: WHERE rel.party_id=p_party_id
7062: AND ar.ROLE_TYPE = 'CONTACT'
7063: AND ar.party_id = rel.party_id
7064: AND p.party_id = rel.subject_id
7071: WHERE party_id=p_party_id;
7072:
7073: l_owner_table_name HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
7074: l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
7075: TYPE siteidtab IS TABLE OF HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;
7076: l_siteidtab siteidtab;
7077: l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
7078: BEGIN
7079: savepoint update_contact_point_search;
7095: FETCH c_sl_cp BULK COLLECT INTO l_siteidtab;
7096: CLOSE c_sl_cp;
7097: IF l_siteidtab.COUNT >0 THEN
7098: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7099: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7100: END IF;
7101: ELSE ---l_owner_table_name ='HZ_PARTIES'
7102: OPEN c_party_type(p_cp_rec.owner_table_id);
7103: FETCH c_party_type INTO l_party_type;
7108: FETCH c_pl_rel_cp BULK COLLECT INTO l_siteidtab;
7109: CLOSE c_pl_rel_cp;
7110: IF l_siteidtab.COUNT >0 THEN
7111: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7112: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7113: END IF;
7114: --Process site level relationship's records
7115: OPEN c_sl_rel_cp(p_cp_rec.owner_table_id);
7116: FETCH c_sl_rel_cp BULK COLLECT INTO l_siteidtab;
7116: FETCH c_sl_rel_cp BULK COLLECT INTO l_siteidtab;
7117: CLOSE c_sl_rel_cp;
7118: IF l_siteidtab.COUNT >0 THEN
7119: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7120: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7121: END IF;
7122: ELSE
7123: OPEN c_pl_cp(p_cp_rec.owner_table_id);
7124: FETCH c_pl_cp BULK COLLECT INTO l_siteidtab;
7124: FETCH c_pl_cp BULK COLLECT INTO l_siteidtab;
7125: CLOSE c_pl_cp;
7126: IF l_siteidtab.COUNT >0 THEN
7127: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7128: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7129: END IF;
7130: END IF;
7131: END IF;
7132: END IF;