[Home] [Help]
7149: | update_contact_point_search |
7150: | |
7151: | DESCRIPTION |
7152: | This procedure updates the address_text column of |
7153: | hz_cust_acct_sites_all with the NULL value |
7154: | only to change the address_text column status |
7155: | so that interMedia index can be created on it to perform text searches. |
7156: | |
7157: | NOTE :- After Calling this procedure the user has to execute the |
7184:
7185: ----(Party level Contact Point)
7186: CURSOR c_pl_cp(p_party_id NUMBER) IS
7187: SELECT ac.CUST_ACCT_SITE_ID
7188: FROM HZ_CUST_ACCOUNTS c,HZ_CUST_ACCT_SITES_ALL ac
7189: WHERE c.party_id=p_party_id
7190: AND c.cust_account_id = ac.cust_account_id;
7191:
7192: ----(Site level contact point)
7192: ----(Site level contact point)
7193: CURSOR c_sl_cp(p_party_site_id NUMBER) IS
7194: SELECT ac.CUST_ACCT_SITE_ID
7195: FROM HZ_PARTY_SITES ps,
7196: HZ_CUST_ACCT_SITES_ALL ac
7197: WHERE ps.PARTY_SITE_ID=p_party_site_id
7198: AND ps.PARTY_SITE_ID=ac.PARTY_SITE_ID;
7199:
7200: ----(Party level relationship's contact point )
7200: ----(Party level relationship's contact point )
7201: CURSOR c_pl_rel_cp(p_party_id NUMBER) IS
7202: SELECT distinct ac.CUST_ACCT_SITE_ID
7203: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7204: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7205: WHERE rel.party_id=p_party_id
7206: AND ar.ROLE_TYPE = 'CONTACT'
7207: AND rel.party_id=ar.party_id
7208: AND rel.subject_id=p.party_id
7212: -----(Site Level relationship's contact point )
7213: CURSOR c_sl_rel_cp(p_party_id NUMBER) IS
7214: SELECT distinct ac.CUST_ACCT_SITE_ID
7215: FROM HZ_PARTIES p, HZ_CUST_ACCOUNT_ROLES ar,
7216: HZ_RELATIONSHIPS rel,HZ_CUST_ACCT_SITES_ALL ac
7217: WHERE rel.party_id=p_party_id
7218: AND ar.ROLE_TYPE = 'CONTACT'
7219: AND ar.party_id = rel.party_id
7220: AND p.party_id = rel.subject_id
7227: WHERE party_id=p_party_id;
7228:
7229: l_owner_table_name HZ_CONTACT_POINTS.OWNER_TABLE_NAME%TYPE;
7230: l_contact_point_type HZ_CONTACT_POINTS.CONTACT_POINT_TYPE%TYPE;
7231: TYPE siteidtab IS TABLE OF HZ_CUST_ACCT_SITES_ALL.CUST_ACCT_SITE_ID%TYPE;
7232: l_siteidtab siteidtab;
7233: l_party_type HZ_PARTIES.PARTY_TYPE%TYPE;
7234: BEGIN
7235: savepoint update_contact_point_search;
7251: FETCH c_sl_cp BULK COLLECT INTO l_siteidtab;
7252: CLOSE c_sl_cp;
7253: IF l_siteidtab.COUNT >0 THEN
7254: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7255: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7256: END IF;
7257: ELSE ---l_owner_table_name ='HZ_PARTIES'
7258: OPEN c_party_type(p_cp_rec.owner_table_id);
7259: FETCH c_party_type INTO l_party_type;
7264: FETCH c_pl_rel_cp BULK COLLECT INTO l_siteidtab;
7265: CLOSE c_pl_rel_cp;
7266: IF l_siteidtab.COUNT >0 THEN
7267: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7268: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7269: END IF;
7270: --Process site level relationship's records
7271: OPEN c_sl_rel_cp(p_cp_rec.owner_table_id);
7272: FETCH c_sl_rel_cp BULK COLLECT INTO l_siteidtab;
7272: FETCH c_sl_rel_cp BULK COLLECT INTO l_siteidtab;
7273: CLOSE c_sl_rel_cp;
7274: IF l_siteidtab.COUNT >0 THEN
7275: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7276: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7277: END IF;
7278: ELSE
7279: OPEN c_pl_cp(p_cp_rec.owner_table_id);
7280: FETCH c_pl_cp BULK COLLECT INTO l_siteidtab;
7280: FETCH c_pl_cp BULK COLLECT INTO l_siteidtab;
7281: CLOSE c_pl_cp;
7282: IF l_siteidtab.COUNT >0 THEN
7283: FORALL i IN l_siteidtab.FIRST..l_siteidtab.LAST
7284: update HZ_CUST_ACCT_SITES_ALL set address_text=NULL where cust_acct_site_id=l_siteidtab(i);
7285: END IF;
7286: END IF;
7287: END IF;
7288: END IF;