[Home] [Help]
PACKAGE BODY: APPS.RA_CUSTOMER_TEXT_PKG
Source
1 PACKAGE BODY RA_CUSTOMER_TEXT_PKG as
2 /* $Header: ARXCUTXB.pls 120.21.12000000.5 2007/06/12 19:27:10 nsinghai 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
109 /* Bug Fix: 4095863 */
110 l_pos := instr(x_parameters,'siteud1');
111 IF ((l_pos <> 0 AND l_datastore = 'APPS.siteud') OR
112 (l_pos = 0 AND l_datastore = 'APPS.siteud1'))
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 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 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
257 WHERE p.party_id = c.party_id
258 AND c.cust_account_id = sites.cust_account_id)
259 LOOP
260 l_party_id := cust.party_id;
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 dbms_lob.writeappend(site_lob,lengthb(stvar),stvar);
349 END;
350
351 Procedure site_info (
352 rid IN ROWID,
353 site_char IN OUT NOCOPY VARCHAR2) IS
354
355 stvar VARCHAR2(32000);
356 l_party_id NUMBER;
357 BEGIN
358 site_char := ' ';
359 /* Bug Fix: 4006266 */
360 FOR sites in (
361 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||' '||
362 loc.province||' '||loc.postal_code || ' ' cust_address
363 FROM APPS.HZ_LOCATIONS loc, APPS.HZ_PARTY_SITES ps,
364 APPS.HZ_CUST_ACCT_SITES_ALL ac
365 WHERE ac.PARTY_SITE_ID=ps.PARTY_SITE_ID
366 AND loc.LOCATION_ID = ps.LOCATION_ID
367 AND ac.ROWID = rid)
368 LOOP
369 IF sites.status is null or sites.status = 'A' THEN
370 write_to_char(sites.cust_address,site_char);
371
372 FOR cust in (
373 SELECT cust_account_id, p.party_id,
374 party_name||' '||ACCOUNT_NUMBER||' '||tax_reference || ' ' data
375 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNTS c
376 WHERE p.party_id = c.party_id
377 AND c.cust_account_id = sites.cust_account_id)
378 LOOP
379 l_party_id := cust.party_id;
380
381 write_to_char(cust.data,site_char);
382 FOR tax IN (
383 SELECT distinct customer_exemption_number || ' ' taxinfo
384 FROM APPS.ra_tax_exemptions_all
385 WHERE customer_id = cust.cust_account_id)
386 LOOP
387 write_to_char(tax.taxinfo,site_char);
388 END LOOP;
389 END LOOP;
390
391 FOR cp1 IN (
392 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
393 ' ' || EMAIL_ADDRESS || ' ' phone
394 FROM APPS.hz_contact_points
395 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
396 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
397 AND OWNER_TABLE_ID = l_party_id)
398 LOOP
399 write_to_char(cp1.phone,site_char);
400 END LOOP;
401
402 /*
403 FOR cont in (
404 SELECT rel.PARTY_ID rel_party_id,
405 p.PERSON_FIRST_NAME || ' ' || p.PERSON_LAST_NAME|| ' ' name
406 FROM APPS.HZ_PARTIES p, APPS.HZ_CUST_ACCOUNT_ROLES ar,
407 APPS.HZ_PARTY_RELATIONSHIPS rel
408 WHERE ar.cust_account_id = sites.cust_account_id
409 AND (ar.cust_acct_site_id = sites.cust_acct_site_id)
410 AND ar.ROLE_TYPE = 'CONTACT'
411 AND ar.party_id = rel.party_id
412 AND p.party_id = rel.subject_id )
413 LOOP
414 write_to_char(cont.name,site_char);
415
416 FOR cp1 IN (
417 SELECT PHONE_NUMBER||phone_area_code||phone_country_code||
418 ' ' || EMAIL_ADDRESS || ' ' phone
419 FROM APPS.hz_contact_points
420 WHERE OWNER_TABLE_NAME = 'HZ_PARTIES'
421 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'WEB')
422 AND OWNER_TABLE_ID = cont.rel_party_id)
423 LOOP
424 write_to_char(cp1.phone,site_char);
425 END LOOP;
426 END LOOP;
427 */
428
429 FOR cp1 IN (
430 SELECT PHONE_NUMBER||phone_area_code||phone_country_code|| ' ' phone
431 FROM APPS.hz_contact_points
432 WHERE OWNER_TABLE_NAME = 'HZ_PARTY_SITES'
433 AND CONTACT_POINT_TYPE NOT IN ( 'EDI', 'EMAIL', 'WEB')
434 AND OWNER_TABLE_ID = sites.party_site_id)
435 LOOP
436 write_to_char(cp1.phone,site_char);
437 END LOOP;
438 END IF;
439 END LOOP;
440 END;
441
442 END RA_CUSTOMER_TEXT_PKG;