[Home] [Help]
PACKAGE BODY: APPS.RA_CUSTOMER_TEXT_PKG
Source
1 PACKAGE BODY RA_CUSTOMER_TEXT_PKG as
2 /* $Header: ARXCUTXB.pls 120.28 2011/12/07 15:20:22 rgokavar ship $ */
3
4
5 /** Private Proc **/
6 PROCEDURE sync_index(
7 p_index_name IN VARCHAR2,
8 retcode OUT NOCOPY VARCHAR2,
9 err OUT NOCOPY VARCHAR2) IS
10
11
12 BEGIN
13
14 retcode := 0;
15 err := null;
16
17 --Call to sync index
18 ad_ctx_Ddl.Sync_Index ( p_index_name );
19
20 EXCEPTION
21 WHEN OTHERS THEN
22 retcode := 1;
23 err := SQLERRM;
24
25 END sync_index;
26
27
28
29 /*----------------------------------------------------------------------------*
30 | PROCEDURE |
31 | update_text_addr |
32 | |
33 | DESCRIPTION |
34 | This procedure updates the address_text column of ra_addresses table |
35 | with the concatenated customer,contacts,phones,address information |
36 | so that interMedia index can be created on it to perform text searches. |
37 | |
38 | PARAMETERS |
39 | INPUT |
40 | |
41 | |
42 | OUTPUT |
43 | Errbuf VARCHAR2 -- Conc Pgm Error mesgs. |
44 | RetCode VARCHAR2 -- Conc Pgm Error Code. |
45 | 0 - Success, 2 - Failure. |
46 | |
47 | |
48 | HISTORY |
49 | 12-Apr-1999 Ujjal Singh Created. |
50 *----------------------------------------------------------------------------*/
51
52 PROCEDURE update_text_addr (
53 Errbuf OUT NOCOPY VARCHAR2,
54 Retcode OUT NOCOPY VARCHAR2,
55 p_idx_cust_contacts IN VARCHAR2) IS
56
57 cursor cust_index (p_schema IN VARCHAR2)is
58 /* Bug Fix: 4095863 */
59 select STATUS , DOMIDX_OPSTATUS , PARAMETERS from all_indexes where index_name = 'HZ_CUST_ACCT_SITES_ALL_T1'
60 and owner = p_schema;
61
62 x_dummy BOOLEAN;
63 x_status varchar2(30);
64 x_ind varchar2(30);
65 x_index_owner varchar2(50);
66 l_idx_mem varchar2(30);
67 x_index_exist varchar2(30) := NULL;
68 x_dom_index_status varchar2(30) := NULL;
69 l_count NUMBER := 0;
70 L_DATASTORE varchar2(30);
71 /* Bug Fix: 4095863 */
72 l_param_str varchar2(255);
73 x_parameters all_indexes.parameters%TYPE;
74 l_pos NUMBER := 0;
75 X_DROP_INDEX varchar2(255);
76 X_INDEX_STRING varchar2(255);
77 v_return_value BOOLEAN;
78 x_profile_date varchar2(30);
79 idx_retcode VARCHAR2(1);
80 idx_err VARCHAR2(2000);
81
82 BEGIN
83
84 retcode := 0;
85
86 IF nvl(p_idx_cust_contacts,'Y') = 'Y' THEN
87 l_datastore := 'APPS.siteud'; -- Bug 3162951
88 ELSE
89 l_datastore := 'APPS.siteud1'; -- Bug 3162951
90 END IF;
91 /* Bug Fix: 4095863 */
92 l_param_str := 'replace datastore ' || l_datastore ;
93
94 x_dummy := fnd_installation.GET_APP_INFO('AR',x_status,x_ind,x_index_owner);
95
96 open cust_index(x_index_owner);
97 /* Bug Fix: 409586 */
98 fetch cust_index into x_index_exist,x_dom_index_status,x_parameters;
99 IF cust_index%FOUND THEN
100 /* Index Exist */
101 IF x_index_exist IS NOT NULL AND x_index_exist = 'VALID' AND
102 x_dom_index_status IS NOT NULL AND x_dom_index_status = 'VALID'
103 THEN
104 select count(1) into l_count
105 from ctxsys.ctx_index_errors
106 where err_index_name = 'HZ_CUST_ACCT_SITES_ALL_T1'
107 and rownum =1;
108 IF l_count <> 1 THEN
112 (l_pos = 0 AND l_datastore = 'APPS.siteud1'))
109 /* Bug Fix: 4095863 */
110 l_pos := instr(x_parameters,'siteud1');
111 IF ((l_pos <> 0 AND l_datastore = 'APPS.siteud') OR
113 THEN
114 EXECUTE IMMEDIATE ' ALTER INDEX ' || x_index_owner || '.HZ_CUST_ACCT_SITES_ALL_T1 REBUILD
115 online parameters (''' || l_param_str || ''') PARALLEL ' ;
116 END IF;
117 /* Index is valid */
118 SYNC_INDEX(x_index_owner || '.HZ_CUST_ACCT_SITES_ALL_T1', idx_retcode , idx_err);
119 IF idx_retcode = 1 THEN
120 RAISE FND_API.G_EXC_ERROR;
121 END IF;
122 --to populate the profile option
123 x_profile_date := fnd_date.date_to_canonical(sysdate);
124 v_return_value := fnd_profile.save('AR_CUSTOMER_TEXT_LAST_SUCCESSFUL_RUN',x_profile_date,'SITE');
125 IF not(v_return_value) then
126 arp_util.debug('Profile option AR_CUSTOMER_TEXT_LAST_SUCCESSFUL_RUN ');
127 END IF;
128 RETURN;
129 END IF;
130 END IF;
131 /* Index is invalid */
132 x_drop_index := 'drop index '||x_index_owner||'.'|| 'HZ_CUST_ACCT_SITES_ALL_T1 force';
133 EXECUTE IMMEDIATE x_drop_index;
134 END IF;
135 close cust_index;
136
137 BEGIN
138 SELECT PAR_VALUE INTO l_idx_mem
139 FROM CTX_PARAMETERS
140 WHERE PAR_NAME = 'MAX_INDEX_MEMORY';
141 EXCEPTION
142 WHEN NO_DATA_FOUND THEN
143 BEGIN
144 SELECT PAR_VALUE INTO l_idx_mem
145 FROM CTX_PARAMETERS
146 WHERE PAR_NAME = 'DEFAULT_INDEX_MEMORY';
147 EXCEPTION
148 WHEN NO_DATA_FOUND THEN
149 l_idx_mem := '0';
150 END;
151 END;
152
153 BEGIN
154 /* Bug Fix : 2910426
155 ctx_output.start_log('cust_index');
156 */
157 execute immediate 'begin ctx_output.start_log(''cust_index''); end;';
158 EXCEPTION
159 WHEN OTHERS THEN
160 NULL;
161 END;
162
163 IF l_idx_mem <> '0' THEN
164 x_index_string := 'create index '||x_index_owner||'.'||
165 'HZ_CUST_ACCT_SITES_ALL_T1 on '||x_index_owner||'.'||'HZ_CUST_ACCT_SITES_ALL '||
166 '(ADDRESS_TEXT) indextype is ctxsys.context ' ||
167 'parameters ('' datastore ' || l_datastore ||
168 ' stoplist ctxsys.empty_stoplist lexer APPS.text_lexer ' ||
169 ' memory ' || l_idx_mem || ' '') PARALLEL ONLINE';
170 ELSE
171 x_index_string := 'create index '||x_index_owner||'.'||
172 'HZ_CUST_ACCT_SITES_ALL_T1 on '||x_index_owner||'.'||'HZ_CUST_ACCT_SITES_ALL '||
173 '(ADDRESS_TEXT) indextype is ctxsys.context '||
174 'parameters ('' datastore ' || l_datastore ||
175 ' stoplist ctxsys.empty_stoplist lexer APPS.text_lexer ' ||' '') PARALLEL ONLINE';
176 END IF;
177
178 hz_common_pub.disable_cont_source_security;
179 EXECUTE IMMEDIATE x_index_string;
180
181 --to populate the profile option
182 x_profile_date := fnd_date.date_to_canonical(sysdate);
183 v_return_value := fnd_profile.save('AR_CUSTOMER_TEXT_LAST_SUCCESSFUL_RUN',x_profile_date,'SITE');
184 IF not(v_return_value) then
185 arp_util.debug('Profile option AR_CUSTOMER_TEXT_LAST_SUCCESSFUL_RUN ');
186 END IF;
187
188 BEGIN
189 /* Bug Fix : 2910426
190 ctx_output.end_log;
191 */
192 execute immediate 'begin ctx_output.end_log; end;';
193 EXCEPTION
194 WHEN OTHERS THEN
195 NULL;
196 END;
197
198 EXCEPTION
199 WHEN OTHERS THEN
200 arp_util.debug('OTHERS : ra_customer_text_pkg.update_text_addr');
201 Errbuf := fnd_message.get||' '||SQLERRM;
202 Retcode := 2;
203
204 END update_text_addr;
205
206 PROCEDURE write_to_char (
207 vl IN VARCHAR2,
208 sv IN OUT NOCOPY VARCHAR2 ) IS
209
210 BEGIN
211 IF (nvl(lengthb(sv),0) + nvl(lengthb(vl),0)) < 32000 THEN
212 sv := sv || vl;
213 END IF;
214 END;
215
216 PROCEDURE write_to_lob(
217 cl IN OUT NOCOPY CLOB,
218 vl IN VARCHAR2,
219 sv IN OUT NOCOPY VARCHAR2 ) IS
220
221 len NUMBER;
222
223 BEGIN
224 IF (nvl(lengthb(sv),0) + nvl(lengthb(vl),0)) >= 32000 THEN
225 len := nvl(lengthb(sv),0);
226 dbms_lob.writeappend(cl,len,sv);
227 sv := vl;
228 ELSE
229 sv := sv || vl;
230 END IF;
231 END;
232
233 Procedure site_info2 (
234 rid IN ROWID,
235 site_lob IN OUT NOCOPY CLOB) IS
236
237 stvar VARCHAR2(32000);
238 l_party_id NUMBER;
239 BEGIN
240
241 /* Bug Fix: 4006266 */
242 FOR sites in (
243 SELECT cust_account_id, ac.cust_acct_site_id, ps.party_site_id, loc.address1||' '||loc.address2||' '||loc.address3||' '||loc.address4||' '|| loc.city||' '||loc.state||' '||
244 loc.province||' '||loc.postal_code || ' ' cust_address
245 FROM APPS.HZ_LOCATIONS loc, APPS.HZ_PARTY_SITES ps,
246 APPS.HZ_CUST_ACCT_SITES_ALL ac
247 WHERE ac.PARTY_SITE_ID=ps.PARTY_SITE_ID
248 AND loc.LOCATION_ID = ps.LOCATION_ID
249 AND ac.ROWID = rid)
250 LOOP
251 write_to_lob(site_lob,sites.cust_address,stvar);
252
253 FOR cust in (
254 SELECT cust_account_id, p.party_id,
255 party_name||' '||ACCOUNT_NUMBER||' '||tax_reference || ' ' data
256 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNTS c
260 l_party_id := cust.party_id;
257 WHERE p.party_id = c.party_id
258 AND c.cust_account_id = sites.cust_account_id)
259 LOOP
261 write_to_lob(site_lob,cust.data,stvar);
262 FOR tax IN (
263 SELECT distinct customer_exemption_number || ' ' taxinfo
264 FROM APPS.ra_tax_exemptions_all
265 WHERE customer_id = cust.cust_account_id)
266 LOOP
267 write_to_lob(site_lob,tax.taxinfo,stvar);
268 END LOOP;
269 END LOOP;
270
271 FOR cont in (
272 SELECT rel.PARTY_ID rel_party_id,
273 p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
274 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
275 APPS.HZ_RELATIONSHIPS rel
276 WHERE ar.cust_account_id = sites.cust_account_id
277 AND (ar.cust_acct_site_id is null)
278 AND ar.ROLE_TYPE = 'CONTACT'
279 AND ar.party_id = rel.party_id
280 AND p.party_id = rel.subject_id
281 AND rel.subject_table_name = 'HZ_PARTIES'
282 AND rel.object_table_name = 'HZ_PARTIES'
283 AND rel.DIRECTIONAL_FLAG = 'F')
284 LOOP
285 write_to_lob(site_lob,cont.name,stvar);
286
287 FOR cp1 IN (
288 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
289 ' ' || EMAIL_ADDRESS || ' ' phone
290 FROM APPS.hz_contact_points
291 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
292 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
293 AND OWNER_TABLE_ID = cont.rel_party_id)
294 LOOP
295 write_to_lob(site_lob,cp1.phone,stvar);
296 END LOOP;
297 END LOOP;
298
299 FOR cp1 IN (
300 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
301 ' ' || EMAIL_ADDRESS || ' ' phone
302 FROM APPS.hz_contact_points
303 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
304 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
305 AND OWNER_TABLE_ID = l_party_id)
306 LOOP
307 write_to_lob(site_lob,cp1.phone,stvar);
308 END LOOP;
309
310 FOR cont in (
311 SELECT rel.PARTY_ID rel_party_id,
312 p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
313 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
314 APPS.HZ_RELATIONSHIPS rel
315 WHERE ar.cust_account_id = sites.cust_account_id
316 AND (ar.cust_acct_site_id = sites.cust_acct_site_id)
317 AND ar.ROLE_TYPE = 'CONTACT'
318 AND ar.party_id = rel.party_id
319 AND p.party_id = rel.subject_id
320 AND rel.subject_table_name = 'HZ_PARTIES'
321 AND rel.object_table_name = 'HZ_PARTIES'
322 AND rel.DIRECTIONAL_FLAG = 'F')
323 LOOP
324 write_to_lob(site_lob,cont.name,stvar);
325
326 FOR cp1 IN (
327 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
328 ' ' || EMAIL_ADDRESS || ' ' phone
329 FROM APPS.hz_contact_points
330 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
331 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
332 AND OWNER_TABLE_ID = cont.rel_party_id)
333 LOOP
334 write_to_lob(site_lob,cp1.phone,stvar);
335 END LOOP;
336 END LOOP;
337
338 FOR cp1 IN (
339 SELECT PHONE_NUMBER||phone_area_code||phone_country_code|| ' ' phone
340 FROM APPS.hz_contact_points
341 WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
342 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'EMAIL', 'WEB')
343 AND OWNER_TABLE_ID = sites.party_site_id)
344 LOOP
345 write_to_lob(site_lob,cp1.phone,stvar);
346 END LOOP;
347 END LOOP;
348 --Bug 12665166
349 --Before appending stvar to site_lob, verifying length of stvar
350 --dbms_lob.writeappend(site_lob,lengthb(stvar),stvar);
351 IF length(stvar) <> 0 THEN
352 dbms_lob.writeappend(site_lob,nvl(length(stvar),0),stvar) ;
353 END IF;
354 END;
355
356 Procedure site_info (
357 rid IN ROWID,
358 site_char IN OUT NOCOPY VARCHAR2) IS
359
360 stvar VARCHAR2(32000);
361 l_party_id NUMBER;
362 BEGIN
363 site_char := ' ';
364 /* Bug Fix: 4006266 */
365 FOR sites in (
366 SELECT ac.status, cust_account_id, ac.cust_acct_site_id, ps.party_site_id, loc.address1||' '||loc.address2||' '||loc.address3 ||' '||loc.address4||' '|| loc.city||' '||loc.state||' '||
367 loc.province||' '||loc.postal_code || ' ' cust_address
368 FROM APPS.HZ_LOCATIONS loc, APPS.HZ_PARTY_SITES ps,
369 APPS.HZ_CUST_ACCT_SITES_ALL ac
370 WHERE ac.PARTY_SITE_ID=ps.PARTY_SITE_ID
371 AND loc.LOCATION_ID = ps.LOCATION_ID
372 AND ac.ROWID = rid)
373 LOOP
374 IF sites.status is null or sites.status = 'A' THEN
375 write_to_char(sites.cust_address,site_char);
376
377 FOR cust in (
378 SELECT cust_account_id, p.party_id,
379 party_name||' '||ACCOUNT_NUMBER||' '||tax_reference || ' ' data
380 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNTS c
381 WHERE p.party_id = c.party_id
382 AND c.cust_account_id = sites.cust_account_id)
383 LOOP
384 l_party_id := cust.party_id;
385
386 write_to_char(cust.data,site_char);
387 FOR tax IN (
388 SELECT distinct customer_exemption_number || ' ' taxinfo
389 FROM APPS.ra_tax_exemptions_all
390 WHERE customer_id = cust.cust_account_id)
391 LOOP
392 write_to_char(tax.taxinfo,site_char);
393 END LOOP;
394 END LOOP;
395
396 FOR cp1 IN (
397 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
398 ' ' || EMAIL_ADDRESS || ' ' phone
399 FROM APPS.hz_contact_points
400 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
401 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
402 AND OWNER_TABLE_ID = l_party_id)
403 LOOP
404 write_to_char(cp1.phone,site_char);
405 END LOOP;
406
407 /*
408 FOR cont in (
409 SELECT rel.PARTY_ID rel_party_id,
410 p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
411 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
412 APPS.HZ_PARTY_RELATIONSHIPS rel
413 WHERE ar.cust_account_id = sites.cust_account_id
414 AND (ar.cust_acct_site_id = sites.cust_acct_site_id)
415 AND ar.ROLE_TYPE = 'CONTACT'
416 AND ar.party_id = rel.party_id
417 AND p.party_id = rel.subject_id )
418 LOOP
419 write_to_char(cont.name,site_char);
420
421 FOR cp1 IN (
422 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
423 ' ' || EMAIL_ADDRESS || ' ' phone
424 FROM APPS.hz_contact_points
425 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
426 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
427 AND OWNER_TABLE_ID = cont.rel_party_id)
428 LOOP
429 write_to_char(cp1.phone,site_char);
430 END LOOP;
431 END LOOP;
432 */
433
434 FOR cp1 IN (
435 SELECT PHONE_NUMBER||phone_area_code||phone_country_code|| ' ' phone
436 FROM APPS.hz_contact_points
437 WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
438 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'EMAIL', 'WEB')
439 AND OWNER_TABLE_ID = sites.party_site_id)
440 LOOP
441 write_to_char(cp1.phone,site_char);
442 END LOOP;
443 END IF;
444 END LOOP;
445 END;
446
447 END RA_CUSTOMER_TEXT_PKG;