[Home] [Help]
PACKAGE BODY: APPS.ARH_DQM_SRCH_PKG
Source
1 PACKAGE BODY arh_dqm_srch_pkg AS
2 /*$Header: ARHDSRHB.pls 120.5 2005/06/16 21:11:28 jhuang noship $*/
3
4
5 FUNCTION IsNull
6 -- Return TRUE if the dqm record type argument has all its attributes to NULL
7 ( p_party IN hz_party_search.party_search_rec_type)
8 RETURN BOOLEAN
9 IS
10 BEGIN
11 IF p_party.line_of_business IS NULL
12 AND p_party.local_activity_code IS NULL
13 AND p_party.local_activity_code_type IS NULL
14 AND p_party.local_bus_identifier IS NULL
15 AND p_party.local_bus_iden_type IS NULL
16 AND p_party.maximum_credit_currency_code IS NULL
17 AND p_party.maximum_credit_recommendation IS NULL
18 AND p_party.minority_owned_ind IS NULL
19 AND p_party.minority_owned_type IS NULL
20 AND p_party.next_fy_potential_revenue IS NULL
21 AND p_party.oob_ind IS NULL
22 AND p_party.organization_name IS NULL
23 AND p_party.organization_name_phonetic IS NULL
24 AND p_party.organization_type IS NULL
25 AND p_party.parent_sub_ind IS NULL
26 AND p_party.paydex_norm IS NULL
27 AND p_party.paydex_score IS NULL
28 AND p_party.paydex_three_months_ago IS NULL
29 AND p_party.pref_functional_currency IS NULL
30 AND p_party.principal_name IS NULL
31 AND p_party.principal_title IS NULL
32 AND p_party.public_private_ownership_flag IS NULL
33 AND p_party.registration_type IS NULL
34 AND p_party.rent_own_ind IS NULL
35 AND p_party.sic_code IS NULL
36 AND p_party.sic_code_type IS NULL
37 AND p_party.small_bus_ind IS NULL
38 AND p_party.tax_name IS NULL
39 AND p_party.tax_reference IS NULL
40 AND p_party.total_employees_text IS NULL
41 AND p_party.total_emp_est_ind IS NULL
42 AND p_party.total_emp_min_ind IS NULL
43 AND p_party.total_employees_ind IS NULL
44 AND p_party.total_payments IS NULL
45 AND p_party.woman_owned_ind IS NULL
46 AND p_party.year_established IS NULL
47 AND p_party.category_code IS NULL
48 AND p_party.competitor_flag IS NULL
49 AND p_party.do_not_mail_flag IS NULL
50 AND p_party.group_type IS NULL
51 AND p_party.language_name IS NULL
52 AND p_party.party_name IS NULL
53 AND p_party.party_number IS NULL
54 AND p_party.party_type IS NULL
55 AND p_party.reference_use_flag IS NULL
56 AND p_party.salutation IS NULL
57 AND p_party.status IS NULL
58 AND p_party.third_party_flag IS NULL
59 AND p_party.validated_flag IS NULL
60 AND p_party.date_of_birth IS NULL
61 AND p_party.date_of_death IS NULL
62 AND p_party.effective_start_date IS NULL
63 AND p_party.effective_end_date IS NULL
64 AND p_party.declared_ethnicity IS NULL
65 AND p_party.gender IS NULL
66 AND p_party.head_of_household_flag IS NULL
67 AND p_party.household_income IS NULL
68 AND p_party.household_size IS NULL
69 AND p_party.last_known_gps IS NULL
70 AND p_party.marital_status IS NULL
71 AND p_party.marital_status_effective_date IS NULL
72 AND p_party.middle_name_phonetic IS NULL
73 AND p_party.personal_income IS NULL
74 AND p_party.person_academic_title IS NULL
75 AND p_party.person_first_name IS NULL
76 AND p_party.person_first_name_phonetic IS NULL
77 AND p_party.person_identifier IS NULL
78 AND p_party.person_iden_type IS NULL
79 AND p_party.person_initials IS NULL
80 AND p_party.person_last_name IS NULL
81 AND p_party.person_last_name_phonetic IS NULL
82 AND p_party.person_middle_name IS NULL
83 AND p_party.person_name IS NULL
84 AND p_party.person_name_phonetic IS NULL
85 AND p_party.person_name_suffix IS NULL
86 AND p_party.person_previous_last_name IS NULL
87 AND p_party.person_pre_name_adjunct IS NULL
88 AND p_party.person_title IS NULL
89 AND p_party.place_of_birth IS NULL
90 AND p_party.all_account_names IS NULL
91 AND p_party.all_account_numbers IS NULL
92 AND p_party.custom_attribute1 IS NULL
93 AND p_party.custom_attribute10 IS NULL
94 AND p_party.custom_attribute11 IS NULL
95 AND p_party.custom_attribute12 IS NULL
96 AND p_party.custom_attribute13 IS NULL
97 AND p_party.custom_attribute14 IS NULL
98 AND p_party.custom_attribute15 IS NULL
99 AND p_party.custom_attribute16 IS NULL
100 AND p_party.custom_attribute17 IS NULL
101 AND p_party.custom_attribute18 IS NULL
102 AND p_party.custom_attribute19 IS NULL
103 AND p_party.custom_attribute2 IS NULL
104 AND p_party.custom_attribute20 IS NULL
105 AND p_party.custom_attribute21 IS NULL
106 AND p_party.custom_attribute22 IS NULL
107 AND p_party.custom_attribute23 IS NULL
108 AND p_party.custom_attribute24 IS NULL
109 AND p_party.custom_attribute25 IS NULL
110 AND p_party.custom_attribute26 IS NULL
111 AND p_party.custom_attribute27 IS NULL
112 AND p_party.custom_attribute28 IS NULL
113 AND p_party.custom_attribute29 IS NULL
114 AND p_party.custom_attribute3 IS NULL
115 AND p_party.custom_attribute30 IS NULL
116 AND p_party.custom_attribute4 IS NULL
117 AND p_party.custom_attribute5 IS NULL
118 AND p_party.custom_attribute6 IS NULL
119 AND p_party.custom_attribute7 IS NULL
120 AND p_party.custom_attribute8 IS NULL
121 AND p_party.custom_attribute9 IS NULL
122 AND p_party.analysis_fy IS NULL
123 AND p_party.avg_high_credit IS NULL
124 AND p_party.best_time_contact_begin IS NULL
125 AND p_party.best_time_contact_end IS NULL
126 AND p_party.branch_flag IS NULL
127 AND p_party.business_scope IS NULL
128 AND p_party.ceo_name IS NULL
129 AND p_party.ceo_title IS NULL
130 AND p_party.cong_dist_code IS NULL
131 AND p_party.content_source_number IS NULL
132 AND p_party.content_source_type IS NULL
133 AND p_party.control_yr IS NULL
134 AND p_party.corporation_class IS NULL
135 AND p_party.credit_score IS NULL
136 AND p_party.credit_score_age IS NULL
137 AND p_party.credit_score_class IS NULL
138 AND p_party.credit_score_commentary IS NULL
139 AND p_party.credit_score_commentary10 IS NULL
140 AND p_party.credit_score_commentary2 IS NULL
141 AND p_party.credit_score_commentary3 IS NULL
142 AND p_party.credit_score_commentary4 IS NULL
143 AND p_party.credit_score_commentary5 IS NULL
144 AND p_party.credit_score_commentary6 IS NULL
145 AND p_party.credit_score_commentary7 IS NULL
146 AND p_party.credit_score_commentary8 IS NULL
147 AND p_party.credit_score_commentary9 IS NULL
148 AND p_party.credit_score_date IS NULL
149 AND p_party.credit_score_incd_default IS NULL
150 AND p_party.credit_score_natl_percentile IS NULL
151 AND p_party.curr_fy_potential_revenue IS NULL
152 AND p_party.db_rating IS NULL
153 AND p_party.debarments_count IS NULL
154 AND p_party.debarments_date IS NULL
155 AND p_party.debarment_ind IS NULL
156 AND p_party.disadv_8a_ind IS NULL
157 AND p_party.duns_number_c IS NULL
158 AND p_party.employees_total IS NULL
159 AND p_party.emp_at_primary_adr IS NULL
160 AND p_party.emp_at_primary_adr_est_ind IS NULL
161 AND p_party.emp_at_primary_adr_min_ind IS NULL
162 AND p_party.emp_at_primary_adr_text IS NULL
163 AND p_party.enquiry_duns IS NULL
164 AND p_party.export_ind IS NULL
165 AND p_party.failure_score IS NULL
166 AND p_party.failure_score_age IS NULL
167 AND p_party.failure_score_class IS NULL
168 AND p_party.failure_score_commentary IS NULL
169 AND p_party.failure_score_commentary10 IS NULL
170 AND p_party.failure_score_commentary2 IS NULL
171 AND p_party.failure_score_commentary3 IS NULL
172 AND p_party.failure_score_commentary4 IS NULL
173 AND p_party.failure_score_commentary5 IS NULL
174 AND p_party.failure_score_commentary6 IS NULL
175 AND p_party.failure_score_commentary7 IS NULL
176 AND p_party.failure_score_commentary8 IS NULL
177 AND p_party.failure_score_commentary9 IS NULL
178 AND p_party.failure_score_date IS NULL
179 AND p_party.failure_score_incd_default IS NULL
180 AND p_party.failure_score_override_code IS NULL
181 AND p_party.fiscal_yearend_month IS NULL
182 AND p_party.global_failure_score IS NULL
183 AND p_party.gsa_indicator_flag IS NULL
184 AND p_party.high_credit IS NULL
185 AND p_party.hq_branch_ind IS NULL
186 AND p_party.import_ind IS NULL
187 AND p_party.incorp_year IS NULL
188 AND p_party.internal_flag IS NULL
189 AND p_party.jgzz_fiscal_code IS NULL
190 AND p_party.party_all_names IS NULL
191 AND p_party.known_as IS NULL
192 AND p_party.known_as2 IS NULL
193 AND p_party.known_as3 IS NULL
194 AND p_party.known_as4 IS NULL
195 AND p_party.known_as5 IS NULL
196 AND p_party.labor_surplus_ind IS NULL
197 AND p_party.legal_status IS NULL
198 THEN
199 RETURN TRUE;
200 ELSE
201 RETURN FALSE;
202 END IF;
203 END;
204
205 FUNCTION IsNull
206 ( p_party_site IN hz_party_search.party_site_search_rec_type)
207 RETURN BOOLEAN
208 IS
209 BEGIN
210 IF p_party_site.party_site_number IS NULL
211 AND p_party_site.custom_attribute16 IS NULL
212 AND p_party_site.custom_attribute17 IS NULL
213 AND p_party_site.custom_attribute18 IS NULL
214 AND p_party_site.custom_attribute19 IS NULL
215 AND p_party_site.custom_attribute2 IS NULL
216 AND p_party_site.custom_attribute20 IS NULL
217 AND p_party_site.custom_attribute21 IS NULL
218 AND p_party_site.custom_attribute22 IS NULL
219 AND p_party_site.custom_attribute23 IS NULL
220 AND p_party_site.custom_attribute24 IS NULL
221 AND p_party_site.custom_attribute25 IS NULL
222 AND p_party_site.custom_attribute26 IS NULL
223 AND p_party_site.custom_attribute27 IS NULL
224 AND p_party_site.custom_attribute28 IS NULL
225 AND p_party_site.custom_attribute29 IS NULL
226 AND p_party_site.custom_attribute3 IS NULL
227 AND p_party_site.custom_attribute30 IS NULL
228 AND p_party_site.custom_attribute4 IS NULL
229 AND p_party_site.custom_attribute5 IS NULL
230 AND p_party_site.custom_attribute6 IS NULL
231 AND p_party_site.custom_attribute7 IS NULL
232 AND p_party_site.custom_attribute8 IS NULL
233 AND p_party_site.custom_attribute9 IS NULL
234 AND p_party_site.address1 IS NULL
235 AND p_party_site.address2 IS NULL
236 AND p_party_site.address3 IS NULL
237 AND p_party_site.address4 IS NULL
238 AND p_party_site.floor IS NULL
239 AND p_party_site.house_number IS NULL
240 AND p_party_site.language IS NULL
241 AND p_party_site.clli_code IS NULL
242 AND p_party_site.content_source_type IS NULL
243 AND p_party_site.country IS NULL
244 AND p_party_site.county IS NULL
245 AND p_party_site.trailing_directory_code IS NULL
246 AND p_party_site.validated_flag IS NULL
247 AND p_party_site.identifying_address_flag IS NULL
248 AND p_party_site.mailstop IS NULL
249 AND p_party_site.party_site_name IS NULL
250 AND p_party_site.address IS NULL
251 AND p_party_site.custom_attribute1 IS NULL
252 AND p_party_site.custom_attribute10 IS NULL
253 AND p_party_site.custom_attribute11 IS NULL
254 AND p_party_site.custom_attribute12 IS NULL
255 AND p_party_site.custom_attribute13 IS NULL
256 AND p_party_site.custom_attribute14 IS NULL
257 AND p_party_site.custom_attribute15 IS NULL
258 AND p_party_site.city IS NULL
259 AND p_party_site.address_effective_date IS NULL
260 AND p_party_site.address_expiration_date IS NULL
261 AND p_party_site.address_lines_phonetic IS NULL
262 AND p_party_site.position IS NULL
263 AND p_party_site.postal_code IS NULL
264 AND p_party_site.postal_plus4_code IS NULL
265 AND p_party_site.po_box_number IS NULL
266 AND p_party_site.province IS NULL
267 AND p_party_site.sales_tax_geocode IS NULL
268 AND p_party_site.sales_tax_inside_city_limits IS NULL
269 AND p_party_site.state IS NULL
270 AND p_party_site.street IS NULL
271 AND p_party_site.street_number IS NULL
272 AND p_party_site.street_suffix IS NULL
273 AND p_party_site.suite IS NULL
274 THEN
275 RETURN TRUE;
276 ELSE
277 RETURN FALSE;
278 END IF;
279 END;
280
281 FUNCTION IsNull
282 ( p_contact IN hz_party_search.contact_search_rec_type)
283 RETURN BOOLEAN
284 IS
285 BEGIN
286 IF p_contact.directional_flag IS NULL
287 AND p_contact.native_language IS NULL
288 AND p_contact.other_language_1 IS NULL
289 AND p_contact.title IS NULL
290 AND p_contact.custom_attribute1 IS NULL
291 AND p_contact.custom_attribute10 IS NULL
292 AND p_contact.custom_attribute11 IS NULL
293 AND p_contact.custom_attribute12 IS NULL
294 AND p_contact.custom_attribute13 IS NULL
295 AND p_contact.custom_attribute14 IS NULL
296 AND p_contact.custom_attribute15 IS NULL
297 AND p_contact.custom_attribute16 IS NULL
298 AND p_contact.custom_attribute17 IS NULL
299 AND p_contact.custom_attribute18 IS NULL
300 AND p_contact.custom_attribute19 IS NULL
301 AND p_contact.custom_attribute2 IS NULL
302 AND p_contact.custom_attribute20 IS NULL
303 AND p_contact.custom_attribute21 IS NULL
304 AND p_contact.custom_attribute22 IS NULL
305 AND p_contact.custom_attribute23 IS NULL
306 AND p_contact.custom_attribute24 IS NULL
307 AND p_contact.custom_attribute25 IS NULL
308 AND p_contact.custom_attribute26 IS NULL
309 AND p_contact.custom_attribute27 IS NULL
310 AND p_contact.custom_attribute28 IS NULL
311 AND p_contact.custom_attribute29 IS NULL
315 AND p_contact.custom_attribute5 IS NULL
312 AND p_contact.custom_attribute3 IS NULL
313 AND p_contact.custom_attribute30 IS NULL
314 AND p_contact.custom_attribute4 IS NULL
316 AND p_contact.custom_attribute6 IS NULL
317 AND p_contact.mail_stop IS NULL
318 AND p_contact.best_time_contact_end IS NULL
319 AND p_contact.job_title_code IS NULL
320 AND p_contact.relationship_type IS NULL
321 AND p_contact.other_language_2 IS NULL
322 AND p_contact.rank IS NULL
323 AND p_contact.reference_use_flag IS NULL
324 AND p_contact.date_of_birth IS NULL
325 AND p_contact.date_of_death IS NULL
326 AND p_contact.jgzz_fiscal_code IS NULL
327 AND p_contact.known_as IS NULL
328 AND p_contact.person_academic_title IS NULL
329 AND p_contact.person_first_name IS NULL
330 AND p_contact.person_first_name_phonetic IS NULL
331 AND p_contact.person_identifier IS NULL
332 AND p_contact.person_iden_type IS NULL
333 AND p_contact.person_initials IS NULL
334 AND p_contact.person_last_name IS NULL
335 AND p_contact.person_last_name_phonetic IS NULL
336 AND p_contact.person_middle_name IS NULL
337 AND p_contact.person_name IS NULL
338 AND p_contact.person_name_phonetic IS NULL
339 AND p_contact.person_name_suffix IS NULL
340 AND p_contact.person_previous_last_name IS NULL
341 AND p_contact.person_title IS NULL
342 AND p_contact.place_of_birth IS NULL
343 AND p_contact.tax_name IS NULL
344 AND p_contact.tax_reference IS NULL
345 AND p_contact.content_source_type IS NULL
346 AND p_contact.job_title IS NULL
347 AND p_contact.custom_attribute7 IS NULL
348 AND p_contact.custom_attribute8 IS NULL
349 AND p_contact.custom_attribute9 IS NULL
350 AND p_contact.contact_number IS NULL
351 AND p_contact.contact_name IS NULL
352 AND p_contact.decision_maker_flag IS NULL
353 AND p_contact.best_time_contact_begin IS NULL
354 THEN
355 RETURN TRUE;
356 ELSE
357 RETURN FALSE;
358 END IF;
359 END;
360
361 FUNCTION IsNull
362 ( p_contact_point IN hz_party_search.contact_point_search_rec_type)
363 RETURN BOOLEAN
364 IS
365 BEGIN
366 IF p_contact_point.phone_line_type IS NULL
367 AND p_contact_point.phone_number IS NULL
368 AND p_contact_point.primary_flag IS NULL
369 AND p_contact_point.raw_phone_number IS NULL
370 AND p_contact_point.telephone_type IS NULL
371 AND p_contact_point.telex_number IS NULL
372 AND p_contact_point.time_zone IS NULL
373 AND p_contact_point.url IS NULL
374 AND p_contact_point.web_type IS NULL
375 AND p_contact_point.contact_point_type IS NULL
376 AND p_contact_point.custom_attribute1 IS NULL
377 AND p_contact_point.custom_attribute10 IS NULL
378 AND p_contact_point.edi_tp_header_id IS NULL
379 AND p_contact_point.custom_attribute5 IS NULL
380 AND p_contact_point.custom_attribute6 IS NULL
381 AND p_contact_point.custom_attribute4 IS NULL
382 AND p_contact_point.edi_remittance_instruction IS NULL
383 AND p_contact_point.edi_transaction_handling IS NULL
384 AND p_contact_point.custom_attribute8 IS NULL
385 AND p_contact_point.custom_attribute9 IS NULL
386 AND p_contact_point.content_source_type IS NULL
387 AND p_contact_point.edi_ece_tp_location_code IS NULL
388 AND p_contact_point.edi_id_number IS NULL
389 AND p_contact_point.edi_payment_format IS NULL
390 AND p_contact_point.custom_attribute12 IS NULL
391 AND p_contact_point.custom_attribute13 IS NULL
392 AND p_contact_point.custom_attribute14 IS NULL
393 AND p_contact_point.custom_attribute15 IS NULL
394 AND p_contact_point.custom_attribute16 IS NULL
395 AND p_contact_point.custom_attribute17 IS NULL
396 AND p_contact_point.custom_attribute18 IS NULL
397 AND p_contact_point.custom_attribute19 IS NULL
398 AND p_contact_point.custom_attribute2 IS NULL
399 AND p_contact_point.custom_attribute20 IS NULL
400 AND p_contact_point.custom_attribute21 IS NULL
401 AND p_contact_point.custom_attribute22 IS NULL
402 AND p_contact_point.custom_attribute23 IS NULL
403 AND p_contact_point.custom_attribute24 IS NULL
404 AND p_contact_point.custom_attribute25 IS NULL
405 AND p_contact_point.custom_attribute26 IS NULL
406 AND p_contact_point.custom_attribute27 IS NULL
407 AND p_contact_point.custom_attribute28 IS NULL
408 AND p_contact_point.custom_attribute29 IS NULL
412 AND p_contact_point.email_address IS NULL
409 AND p_contact_point.custom_attribute3 IS NULL
410 AND p_contact_point.custom_attribute30 IS NULL
411 AND p_contact_point.custom_attribute11 IS NULL
413 AND p_contact_point.email_format IS NULL
414 AND p_contact_point.flex_format_phone_number IS NULL
415 AND p_contact_point.last_contact_dt_time IS NULL
416 AND p_contact_point.phone_area_code IS NULL
417 AND p_contact_point.phone_calling_calendar IS NULL
418 AND p_contact_point.phone_country_code IS NULL
419 AND p_contact_point.phone_extension IS NULL
420 AND p_contact_point.edi_remittance_method IS NULL
421 AND p_contact_point.custom_attribute7 IS NULL
422 AND p_contact_point.edi_payment_method IS NULL
423 THEN
424 RETURN TRUE;
425 ELSE
426 RETURN FALSE;
427 END IF;
428 END;
429
430 FUNCTION Contact_point_list
431 ----------------------------------------------
432 -- Return a list of contact_point_search_rec by defaulting the contact_type
433 -- split 1 contact_point_search_rec into a list of record for different contact_type
434 -- Return an empty list if the in record type argument is null
435 ----------------------------------------------
436 ( p_contact_point IN hz_party_search.contact_point_search_rec_type,
437 p_default_type IN VARCHAR2)
438 RETURN hz_party_search.contact_point_list
439 IS
440 l_list hz_party_search.contact_point_list;
441 l_reset_rec hz_party_search.contact_point_search_rec_type;
442 l_rec hz_party_search.contact_point_search_rec_type;
443 l_init_rec hz_party_search.contact_point_search_rec_type;
444 i NUMBER := 0;
445 BEGIN
446 IF NOT IsNull(p_contact_point) THEN
447 l_init_rec.primary_flag := p_contact_point.primary_flag;
448 l_init_rec.time_zone := p_contact_point.time_zone;
449 l_init_rec.custom_attribute1 := p_contact_point.custom_attribute1;
450 l_init_rec.custom_attribute10 := p_contact_point.custom_attribute10;
451 l_init_rec.custom_attribute5 := p_contact_point.custom_attribute5;
452 l_init_rec.custom_attribute6 := p_contact_point.custom_attribute6;
453 l_init_rec.custom_attribute4 := p_contact_point.custom_attribute4;
454 l_init_rec.custom_attribute8 := p_contact_point.custom_attribute8;
455 l_init_rec.custom_attribute9 := p_contact_point.custom_attribute9;
456 l_init_rec.content_source_type := p_contact_point.content_source_type;
457 l_init_rec.custom_attribute12 := p_contact_point.custom_attribute12;
458 l_init_rec.custom_attribute13 := p_contact_point.custom_attribute13;
459 l_init_rec.custom_attribute14 := p_contact_point.custom_attribute14;
460 l_init_rec.custom_attribute15 := p_contact_point.custom_attribute15;
461 l_init_rec.custom_attribute16 := p_contact_point.custom_attribute16;
462 l_init_rec.custom_attribute17 := p_contact_point.custom_attribute17;
463 l_init_rec.custom_attribute18 := p_contact_point.custom_attribute18;
464 l_init_rec.custom_attribute19 := p_contact_point.custom_attribute19;
465 l_init_rec.custom_attribute2 := p_contact_point.custom_attribute2;
466 l_init_rec.custom_attribute20 := p_contact_point.custom_attribute20;
467 l_init_rec.custom_attribute21 := p_contact_point.custom_attribute21;
468 l_init_rec.custom_attribute22 := p_contact_point.custom_attribute22;
469 l_init_rec.custom_attribute23 := p_contact_point.custom_attribute23;
470 l_init_rec.custom_attribute24 := p_contact_point.custom_attribute24;
471 l_init_rec.custom_attribute25 := p_contact_point.custom_attribute25;
472 l_init_rec.custom_attribute26 := p_contact_point.custom_attribute26;
473 l_init_rec.custom_attribute27 := p_contact_point.custom_attribute27;
474 l_init_rec.custom_attribute28 := p_contact_point.custom_attribute28;
475 l_init_rec.custom_attribute29 := p_contact_point.custom_attribute29;
476 l_init_rec.custom_attribute3 := p_contact_point.custom_attribute3;
477 l_init_rec.custom_attribute30 := p_contact_point.custom_attribute30;
478 l_init_rec.custom_attribute11 := p_contact_point.custom_attribute11;
479 l_init_rec.last_contact_dt_time := p_contact_point.last_contact_dt_time;
480 l_init_rec.custom_attribute7 := p_contact_point.custom_attribute7;
481
482 -- Phone type
483 IF p_contact_point.phone_line_type IS NOT NULL
484 OR p_contact_point.phone_number IS NOT NULL
485 OR p_contact_point.raw_phone_number IS NOT NULL
486 OR p_contact_point.telephone_type IS NOT NULL
487 OR p_contact_point.flex_format_phone_number IS NOT NULL
488 OR p_contact_point.phone_area_code IS NOT NULL
489 OR p_contact_point.phone_calling_calendar IS NOT NULL
490 OR p_contact_point.phone_country_code IS NOT NULL
491 OR p_contact_point.phone_extension IS NOT NULL
492 THEN
493 l_rec := l_reset_rec;
494 l_rec := l_init_rec;
495 l_rec.contact_point_type := 'PHONE';
496 l_rec.phone_line_type := p_contact_point.phone_line_type;
500 l_rec.flex_format_phone_number := p_contact_point.flex_format_phone_number;
497 l_rec.phone_number := p_contact_point.phone_number;
498 l_rec.raw_phone_number := p_contact_point.raw_phone_number;
499 l_rec.telephone_type := p_contact_point.telephone_type;
501 l_rec.phone_area_code := p_contact_point.phone_area_code;
502 l_rec.phone_calling_calendar := p_contact_point.phone_calling_calendar;
503 l_rec.phone_country_code := p_contact_point.phone_country_code;
504 l_rec.phone_extension := p_contact_point.phone_extension;
505 i := i + 1;
506 l_list(i) := l_rec;
507 END IF;
508
509 -- Telex type
510 IF p_contact_point.telex_number IS NOT NULL THEN
511 l_rec := l_reset_rec;
512 l_rec := l_init_rec;
513 l_rec.contact_point_type := 'TLX';
514 l_rec.telex_number := p_contact_point.telex_number;
515 i := i + 1;
516 l_list(i) := l_rec;
517 END IF;
518
519 -- Web type
520 IF p_contact_point.url IS NOT NULL
521 OR p_contact_point.web_type IS NOT NULL
522 THEN
523 l_rec := l_reset_rec;
524 l_rec := l_init_rec;
525 l_rec.contact_point_type := 'WEB';
526 l_rec.url := p_contact_point.url;
527 l_rec.web_type := p_contact_point.web_type;
528 i := i + 1;
529 l_list(i) := l_rec;
530 END IF;
531
532 -- Edi type
533 IF p_contact_point.edi_tp_header_id IS NOT NULL
534 OR p_contact_point.edi_remittance_instruction IS NOT NULL
535 OR p_contact_point.edi_transaction_handling IS NOT NULL
536 OR p_contact_point.edi_ece_tp_location_code IS NOT NULL
537 OR p_contact_point.edi_id_number IS NOT NULL
538 OR p_contact_point.edi_payment_format IS NOT NULL
539 OR p_contact_point.edi_remittance_method IS NOT NULL
540 OR p_contact_point.edi_payment_method IS NOT NULL
541 THEN
542 l_rec := l_reset_rec;
543 l_rec := l_init_rec;
544 l_rec.contact_point_type := 'EDI';
545 l_rec.edi_tp_header_id := p_contact_point.edi_tp_header_id;
546 l_rec.edi_remittance_instruction := p_contact_point.edi_remittance_instruction;
547 l_rec.edi_transaction_handling := p_contact_point.edi_transaction_handling;
548 l_rec.edi_ece_tp_location_code := p_contact_point.edi_ece_tp_location_code;
549 l_rec.edi_id_number := p_contact_point.edi_id_number;
550 l_rec.edi_payment_format := p_contact_point.edi_payment_format;
551 l_rec.edi_remittance_method := p_contact_point.edi_remittance_method;
552 l_rec.edi_payment_method := p_contact_point.edi_payment_method;
553 i := i + 1;
554 l_list(i) := l_rec;
555 END IF;
556
557 -- Email type
558 IF p_contact_point.email_address IS NOT NULL
559 OR p_contact_point.email_format IS NOT NULL
560 THEN
561 l_rec := l_reset_rec;
562 l_rec := l_init_rec;
563 l_rec.contact_point_type := 'EMAIL';
564 l_rec.email_address := p_contact_point.email_address;
565 l_rec.email_format := p_contact_point.email_format;
566 i := i + 1;
567 l_list(i) := l_rec;
568 END IF;
569
570 IF l_list.COUNT = 0 THEN
571 l_rec := l_reset_rec;
572 l_rec := l_init_rec;
573 l_rec.contact_point_type := p_default_type;
574 i := i + 1;
575 l_list(i) := l_rec;
576 END IF;
577 END IF;
578 RETURN l_list;
579 END Contact_point_list;
580
581 PROCEDURE Exec_Dqm_Api
582 ( p_line_of_business VARCHAR2,
583 p_local_activity_code VARCHAR2,
584 p_local_activity_code_type VARCHAR2,
585 p_local_bus_identifier VARCHAR2,
586 p_local_bus_iden_type VARCHAR2,
587 p_max_credit_currency_code VARCHAR2,
588 p_max_credit_recommendation NUMBER,
589 p_minority_owned_ind VARCHAR2,
590 p_minority_owned_type VARCHAR2,
591 p_next_fy_potential_revenue NUMBER,
592 p_oob_ind VARCHAR2,
593 p_organization_name VARCHAR2,
594 p_organization_name_phonetic VARCHAR2,
595 p_organization_type VARCHAR2,
596 p_parent_sub_ind VARCHAR2,
597 p_paydex_norm VARCHAR2,
598 p_paydex_score VARCHAR2,
599 p_paydex_three_months_ago VARCHAR2,
600 p_pref_functional_currency VARCHAR2,
601 p_principal_name VARCHAR2,
602 p_principal_title VARCHAR2,
603 p_public_private_ownership_fg VARCHAR2,
604 p_registration_type VARCHAR2,
605 p_rent_own_ind VARCHAR2,
606 p_sic_code VARCHAR2,
607 p_sic_code_type VARCHAR2,
608 p_small_bus_ind VARCHAR2,
609 p_tax_name VARCHAR2,
610 p_tax_reference VARCHAR2,
614 p_total_employees_ind VARCHAR2,
611 p_total_employees_text VARCHAR2,
612 p_total_emp_est_ind VARCHAR2,
613 p_total_emp_min_ind VARCHAR2,
615 p_total_payments NUMBER,
616 p_woman_owned_ind VARCHAR2,
617 p_year_established NUMBER,
618 p_category_code VARCHAR2,
619 p_competitor_fg VARCHAR2,
620 p_do_not_mail_fg VARCHAR2,
621 p_group_type VARCHAR2,
622 p_language_name VARCHAR2,
623 p_party_name VARCHAR2,
624 p_party_number VARCHAR2,
625 p_party_type VARCHAR2,
626 p_reference_use_fg VARCHAR2,
627 p_salutation VARCHAR2,
628 p_status VARCHAR2,
629 p_third_party_fg VARCHAR2,
630 p_validated_fg VARCHAR2,
631 p_date_of_birth DATE,
632 p_date_of_death DATE,
633 p_effective_start_date DATE,
634 p_effective_end_date DATE,
635 p_declared_ethnicity VARCHAR2,
636 p_gender VARCHAR2,
637 p_head_of_household_fg VARCHAR2,
638 p_household_income NUMBER,
639 p_household_size NUMBER,
640 p_last_known_gps VARCHAR2,
641 p_mar_status VARCHAR2,
642 p_mar_status_effective_date DATE,
643 p_middle_name_phonetic VARCHAR2,
644 p_personal_income NUMBER,
645 p_person_academic_title VARCHAR2,
646 p_person_first_name VARCHAR2,
647 p_person_first_name_phonetic VARCHAR2,
648 p_person_identifier VARCHAR2,
649 p_person_iden_type VARCHAR2,
650 p_person_initials VARCHAR2,
651 p_person_last_name VARCHAR2,
652 p_person_last_name_phonetic VARCHAR2,
653 p_person_middle_name VARCHAR2,
654 p_person_name VARCHAR2,
655 p_person_name_phonetic VARCHAR2,
656 p_person_name_suffix VARCHAR2,
657 p_person_previous_last_name VARCHAR2,
658 p_person_pre_name_adjunct VARCHAR2,
659 p_person_title VARCHAR2,
660 p_place_of_birth VARCHAR2,
661 p_all_account_names VARCHAR2,
662 p_all_account_numbers VARCHAR2,
663 p_custom_attribute1 VARCHAR2,
664 p_custom_attribute10 VARCHAR2,
665 p_custom_attribute11 VARCHAR2,
666 p_custom_attribute12 VARCHAR2,
667 p_custom_attribute13 VARCHAR2,
668 p_custom_attribute14 VARCHAR2,
669 p_custom_attribute15 VARCHAR2,
670 p_custom_attribute16 VARCHAR2,
671 p_custom_attribute17 VARCHAR2,
672 p_custom_attribute18 VARCHAR2,
673 p_custom_attribute19 VARCHAR2,
674 p_custom_attribute2 VARCHAR2,
675 p_custom_attribute20 VARCHAR2,
676 p_custom_attribute21 VARCHAR2,
677 p_custom_attribute22 VARCHAR2,
678 p_custom_attribute23 VARCHAR2,
679 p_custom_attribute24 VARCHAR2,
680 p_custom_attribute25 VARCHAR2,
681 p_custom_attribute26 VARCHAR2,
682 p_custom_attribute27 VARCHAR2,
683 p_custom_attribute28 VARCHAR2,
684 p_custom_attribute29 VARCHAR2,
685 p_custom_attribute3 VARCHAR2,
686 p_custom_attribute30 VARCHAR2,
687 p_custom_attribute4 VARCHAR2,
688 p_custom_attribute5 VARCHAR2,
689 p_custom_attribute6 VARCHAR2,
690 p_custom_attribute7 VARCHAR2,
691 p_custom_attribute8 VARCHAR2,
692 p_custom_attribute9 VARCHAR2,
693 p_analysis_fy VARCHAR2,
694 p_avg_high_credit NUMBER,
695 p_best_time_contact_begin DATE,
696 p_best_time_contact_end DATE,
697 p_branch_fg VARCHAR2,
698 p_business_scope VARCHAR2,
699 p_ceo_name VARCHAR2,
700 p_ceo_title VARCHAR2,
701 p_cong_dist_code VARCHAR2,
702 p_content_source_number VARCHAR2,
703 p_content_source_type VARCHAR2,
704 p_control_yr NUMBER,
705 p_corporation_class VARCHAR2,
706 p_credit_score VARCHAR2,
707 p_credit_score_age NUMBER,
708 p_credit_score_class NUMBER,
709 p_credit_score_commentary VARCHAR2,
710 p_credit_score_commentary10 VARCHAR2,
711 p_credit_score_commentary2 VARCHAR2,
712 p_credit_score_commentary3 VARCHAR2,
713 p_credit_score_commentary4 VARCHAR2,
714 p_credit_score_commentary5 VARCHAR2,
715 p_credit_score_commentary6 VARCHAR2,
716 p_credit_score_commentary7 VARCHAR2,
717 p_credit_score_commentary8 VARCHAR2,
718 p_credit_score_commentary9 VARCHAR2,
719 p_credit_score_date DATE,
720 p_credit_score_incd_default NUMBER,
721 p_credit_score_natl_percentile NUMBER,
722 p_curr_fy_potential_revenue NUMBER,
723 p_db_rating VARCHAR2,
724 p_debarments_count NUMBER,
725 p_debarments_date DATE,
726 p_debarment_ind VARCHAR2,
730 p_emp_at_primary_adr VARCHAR2,
727 p_disadv_8a_ind VARCHAR2,
728 p_duns_number_c VARCHAR2,
729 p_employees_total NUMBER,
731 p_emp_at_primary_adr_est_ind VARCHAR2,
732 p_emp_at_primary_adr_min_ind VARCHAR2,
733 p_emp_at_primary_adr_text VARCHAR2,
734 p_enquiry_duns VARCHAR2,
735 p_export_ind VARCHAR2,
736 p_failure_score VARCHAR2,
737 p_failure_score_age NUMBER,
738 p_failure_score_class NUMBER,
739 p_failure_score_commentary VARCHAR2,
740 p_failure_score_commentary10 VARCHAR2,
741 p_failure_score_commentary2 VARCHAR2,
742 p_failure_score_commentary3 VARCHAR2,
743 p_failure_score_commentary4 VARCHAR2,
744 p_failure_score_commentary5 VARCHAR2,
745 p_failure_score_commentary6 VARCHAR2,
746 p_failure_score_commentary7 VARCHAR2,
747 p_failure_score_commentary8 VARCHAR2,
748 p_failure_score_commentary9 VARCHAR2,
749 p_failure_score_date DATE,
750 p_failure_score_incd_default NUMBER,
751 p_failure_score_override_code VARCHAR2,
752 p_fiscal_yearend_month VARCHAR2,
753 p_global_failure_score VARCHAR2,
754 p_gsa_indicator_fg VARCHAR2,
755 p_high_credit NUMBER,
756 p_hq_branch_ind VARCHAR2,
757 p_import_ind VARCHAR2,
758 p_incorp_year NUMBER,
759 p_internal_fg VARCHAR2,
760 p_jgzz_fiscal_code VARCHAR2,
761 p_party_all_names VARCHAR2,
762 p_known_as VARCHAR2,
763 p_known_as2 VARCHAR2,
764 p_known_as3 VARCHAR2,
765 p_known_as4 VARCHAR2,
766 p_known_as5 VARCHAR2,
767 p_labor_surplus_ind VARCHAR2,
768 p_legal_status VARCHAR2,
769 --PS
770 s_party_site_number VARCHAR2,
771 s_custom_attribute16 VARCHAR2,
772 s_custom_attribute17 VARCHAR2,
773 s_custom_attribute18 VARCHAR2,
774 s_custom_attribute19 VARCHAR2,
775 s_custom_attribute2 VARCHAR2,
776 s_custom_attribute20 VARCHAR2,
777 s_custom_attribute21 VARCHAR2,
778 s_custom_attribute22 VARCHAR2,
779 s_custom_attribute23 VARCHAR2,
780 s_custom_attribute24 VARCHAR2,
781 s_custom_attribute25 VARCHAR2,
782 s_custom_attribute26 VARCHAR2,
783 s_custom_attribute27 VARCHAR2,
784 s_custom_attribute28 VARCHAR2,
785 s_custom_attribute29 VARCHAR2,
786 s_custom_attribute3 VARCHAR2,
787 s_custom_attribute30 VARCHAR2,
788 s_custom_attribute4 VARCHAR2,
789 s_custom_attribute5 VARCHAR2,
790 s_custom_attribute6 VARCHAR2,
791 s_custom_attribute7 VARCHAR2,
792 s_custom_attribute8 VARCHAR2,
793 s_custom_attribute9 VARCHAR2,
794 s_address1 VARCHAR2,
795 s_address2 VARCHAR2,
796 s_address3 VARCHAR2,
797 s_address4 VARCHAR2,
798 s_floor VARCHAR2,
799 s_house_number VARCHAR2,
800 s_language VARCHAR2,
801 s_clli_code VARCHAR2,
802 s_content_source_type VARCHAR2,
803 s_country VARCHAR2,
804 s_county VARCHAR2,
805 s_trailing_directory_code VARCHAR2,
806 s_validated_fg VARCHAR2,
807 s_identifying_address_fg VARCHAR2,
808 s_mailstop VARCHAR2,
809 s_party_site_name VARCHAR2,
810 s_address VARCHAR2,
811 s_custom_attribute1 VARCHAR2,
812 s_custom_attribute10 VARCHAR2,
813 s_custom_attribute11 VARCHAR2,
814 s_custom_attribute12 VARCHAR2,
815 s_custom_attribute13 VARCHAR2,
816 s_custom_attribute14 VARCHAR2,
817 s_custom_attribute15 VARCHAR2,
818 s_city VARCHAR2,
819 s_address_effective_date DATE,
820 s_address_expiration_date DATE,
821 s_address_lines_phonetic VARCHAR2,
822 s_position VARCHAR2,
823 s_postal_code VARCHAR2,
824 s_postal_plus4_code VARCHAR2,
825 s_po_box_number VARCHAR2,
826 s_province VARCHAR2,
827 s_sales_tax_geocode VARCHAR2,
828 s_sales_tax_inside_city_limits VARCHAR2,
829 s_state VARCHAR2,
830 s_street VARCHAR2,
831 s_street_number VARCHAR2,
832 s_street_suffix VARCHAR2,
833 s_suite VARCHAR2,
834 --CT
835 c_directional_fg VARCHAR2,
836 c_native_language VARCHAR2,
837 c_other_language_1 VARCHAR2,
838 c_title VARCHAR2,
839 c_custom_attribute1 VARCHAR2,
840 c_custom_attribute10 VARCHAR2,
841 c_custom_attribute11 VARCHAR2,
842 c_custom_attribute12 VARCHAR2,
843 c_custom_attribute13 VARCHAR2,
847 c_custom_attribute17 VARCHAR2,
844 c_custom_attribute14 VARCHAR2,
845 c_custom_attribute15 VARCHAR2,
846 c_custom_attribute16 VARCHAR2,
848 c_custom_attribute18 VARCHAR2,
849 c_custom_attribute19 VARCHAR2,
850 c_custom_attribute2 VARCHAR2,
851 c_custom_attribute20 VARCHAR2,
852 c_custom_attribute21 VARCHAR2,
853 c_custom_attribute22 VARCHAR2,
854 c_custom_attribute23 VARCHAR2,
855 c_custom_attribute24 VARCHAR2,
856 c_custom_attribute25 VARCHAR2,
857 c_custom_attribute26 VARCHAR2,
858 c_custom_attribute27 VARCHAR2,
859 c_custom_attribute28 VARCHAR2,
860 c_custom_attribute29 VARCHAR2,
861 c_custom_attribute3 VARCHAR2,
862 c_custom_attribute30 VARCHAR2,
863 c_custom_attribute4 VARCHAR2,
864 c_custom_attribute5 VARCHAR2,
865 c_custom_attribute6 VARCHAR2,
866 c_mail_stop VARCHAR2,
867 c_best_time_contact_end DATE,
868 c_job_title_code VARCHAR2,
869 c_relationship_type VARCHAR2,
870 c_other_language_2 VARCHAR2,
871 c_rank VARCHAR2,
872 c_reference_use_fg VARCHAR2,
873 c_date_of_birth DATE,
874 c_date_of_death DATE,
875 c_jgzz_fiscal_code VARCHAR2,
876 c_known_as VARCHAR2,
877 c_person_academic_title VARCHAR2,
878 c_person_first_name VARCHAR2,
879 c_person_first_name_phonetic VARCHAR2,
880 c_person_identifier VARCHAR2,
881 c_person_iden_type VARCHAR2,
882 c_person_initials VARCHAR2,
883 c_person_last_name VARCHAR2,
884 c_person_last_name_phonetic VARCHAR2,
885 c_person_middle_name VARCHAR2,
886 c_person_name VARCHAR2,
887 c_person_name_phonetic VARCHAR2,
888 c_person_name_suffix VARCHAR2,
889 c_person_previous_last_name VARCHAR2,
890 c_person_title VARCHAR2,
891 c_place_of_birth VARCHAR2,
892 c_tax_name VARCHAR2,
893 c_tax_reference VARCHAR2,
894 c_content_source_type VARCHAR2,
895 c_job_title VARCHAR2,
896 c_custom_attribute7 VARCHAR2,
897 c_custom_attribute8 VARCHAR2,
898 c_custom_attribute9 VARCHAR2,
899 c_contact_number VARCHAR2,
900 c_contact_name VARCHAR2,
901 c_decision_maker_fg VARCHAR2,
902 c_best_time_contact_begin DATE,
903 --CPT
904 t_phone_line_type VARCHAR2,
905 t_phone_number VARCHAR2,
906 t_primary_fg VARCHAR2,
907 t_raw_phone_number VARCHAR2,
908 t_telephone_type VARCHAR2,
909 t_telex_number VARCHAR2,
910 t_time_zone NUMBER,
911 t_url VARCHAR2,
912 t_web_type VARCHAR2,
913 t_contact_point_type VARCHAR2,
914 t_custom_attribute1 VARCHAR2,
915 t_custom_attribute10 VARCHAR2,
916 t_edi_tp_header_id NUMBER,
917 t_custom_attribute5 VARCHAR2,
918 t_custom_attribute6 VARCHAR2,
919 t_custom_attribute4 VARCHAR2,
920 t_edi_remittance_instruction VARCHAR2,
921 t_edi_transaction_handling VARCHAR2,
922 t_custom_attribute8 VARCHAR2,
923 t_custom_attribute9 VARCHAR2,
924 t_content_source_type VARCHAR2,
925 t_edi_ece_tp_location_code VARCHAR2,
926 t_edi_id_number VARCHAR2,
927 t_edi_payment_format VARCHAR2,
928 t_custom_attribute12 VARCHAR2,
929 t_custom_attribute13 VARCHAR2,
930 t_custom_attribute14 VARCHAR2,
931 t_custom_attribute15 VARCHAR2,
932 t_custom_attribute16 VARCHAR2,
933 t_custom_attribute17 VARCHAR2,
934 t_custom_attribute18 VARCHAR2,
935 t_custom_attribute19 VARCHAR2,
936 t_custom_attribute2 VARCHAR2,
937 t_custom_attribute20 VARCHAR2,
938 t_custom_attribute21 VARCHAR2,
939 t_custom_attribute22 VARCHAR2,
940 t_custom_attribute23 VARCHAR2,
941 t_custom_attribute24 VARCHAR2,
942 t_custom_attribute25 VARCHAR2,
943 t_custom_attribute26 VARCHAR2,
944 t_custom_attribute27 VARCHAR2,
945 t_custom_attribute28 VARCHAR2,
946 t_custom_attribute29 VARCHAR2,
947 t_custom_attribute3 VARCHAR2,
948 t_custom_attribute30 VARCHAR2,
949 t_custom_attribute11 VARCHAR2,
950 t_email_address VARCHAR2,
951 t_email_format VARCHAR2,
952 t_flex_format_phone_number VARCHAR2,
953 t_last_contact_dt_time DATE,
954 t_phone_area_code VARCHAR2,
955 t_phone_calling_calendar VARCHAR2,
956 t_phone_country_code VARCHAR2,
957 t_phone_extension VARCHAR2,
958 t_edi_remittance_method VARCHAR2,
959 t_custom_attribute7 VARCHAR2,
963 p_cur_all VARCHAR2,
960 t_edi_payment_method VARCHAR2,
961 --other parameter
962 p_restrict_sql VARCHAR2,
964 x_status VARCHAR2,
965 x_rule_id IN OUT NOCOPY NUMBER,
966 x_search_ctx_id IN OUT NOCOPY NUMBER,
967 x_num_matches IN OUT NOCOPY NUMBER,
968 x_msg_count IN OUT NOCOPY NUMBER,
969 x_msg_data IN OUT NOCOPY varchar2,
970 x_return_status IN OUT NOCOPY VARCHAR2)
971 IS
972 -- DQM record types
973 l_party hz_party_search.party_search_rec_type;
974 l_party_site hz_party_search.party_site_search_rec_type;
975 l_contact hz_party_search.contact_search_rec_type;
976 l_contact_point hz_party_search.contact_point_search_rec_type;
977 -- DQM list type
978 l_party_site_list hz_party_search.party_site_list;
979 l_contact_list hz_party_search.contact_list;
980 l_contact_point_list hz_party_search.contact_point_list;
981 -- for error usage
982 tmp_var VARCHAR2(2000);
983 tmp_var1 VARCHAR2(2000);
984
985 BEGIN
986 l_party.line_of_business := p_line_of_business;
987 l_party.local_activity_code := p_local_activity_code;
988 l_party.local_activity_code_type := p_local_activity_code_type;
989 l_party.local_bus_identifier := p_local_bus_identifier;
990 l_party.local_bus_iden_type := p_local_bus_iden_type;
991 l_party.maximum_credit_currency_code := p_max_credit_currency_code;
992 l_party.maximum_credit_recommendation := p_max_credit_recommendation;
993 l_party.minority_owned_ind := p_minority_owned_ind;
994 l_party.minority_owned_type := p_minority_owned_type;
995 l_party.next_fy_potential_revenue := p_next_fy_potential_revenue;
996 l_party.oob_ind := p_oob_ind;
997 l_party.organization_name := p_organization_name;
998 l_party.organization_name_phonetic := p_organization_name_phonetic;
999 l_party.organization_type := p_organization_type;
1000 l_party.parent_sub_ind := p_parent_sub_ind;
1001 l_party.paydex_norm := p_paydex_norm;
1002 l_party.paydex_score := p_paydex_score;
1003 l_party.paydex_three_months_ago := p_paydex_three_months_ago;
1004 l_party.pref_functional_currency := p_pref_functional_currency;
1005 l_party.principal_name := p_principal_name;
1006 l_party.principal_title := p_principal_title;
1007 l_party.public_private_ownership_flag := p_public_private_ownership_fg;
1008 l_party.registration_type := p_registration_type;
1009 l_party.rent_own_ind := p_rent_own_ind;
1010 l_party.sic_code := p_sic_code;
1011 l_party.sic_code_type := p_sic_code_type;
1012 l_party.small_bus_ind := p_small_bus_ind;
1013 l_party.tax_name := p_tax_name;
1014 l_party.tax_reference := p_tax_reference;
1015 l_party.total_employees_text := p_total_employees_text;
1016 l_party.total_emp_est_ind := p_total_emp_est_ind;
1017 l_party.total_emp_min_ind := p_total_emp_min_ind;
1018 l_party.total_employees_ind := p_total_employees_ind;
1019 l_party.total_payments := p_total_payments;
1020 l_party.woman_owned_ind := p_woman_owned_ind;
1021 l_party.year_established := p_year_established;
1022 l_party.category_code := p_category_code;
1023 l_party.competitor_flag := p_competitor_fg;
1024 l_party.do_not_mail_flag := p_do_not_mail_fg;
1025 l_party.group_type := p_group_type;
1026 l_party.language_name := p_language_name;
1027 l_party.party_name := p_party_name;
1028 l_party.party_number := p_party_number;
1029 l_party.party_type := p_party_type;
1030 l_party.reference_use_flag := p_reference_use_fg;
1031 l_party.salutation := p_salutation;
1032 l_party.status := p_status;
1033 l_party.third_party_flag := p_third_party_fg;
1034 l_party.validated_flag := p_validated_fg;
1035 l_party.date_of_birth := p_date_of_birth;
1036 l_party.date_of_death := p_date_of_death;
1037 l_party.effective_start_date := p_effective_start_date;
1038 l_party.effective_end_date := p_effective_end_date;
1039 l_party.declared_ethnicity := p_declared_ethnicity;
1040 l_party.gender := p_gender;
1041 l_party.head_of_household_flag := p_head_of_household_fg;
1042 l_party.household_income := p_household_income;
1043 l_party.household_size := p_household_size;
1044 l_party.last_known_gps := p_last_known_gps;
1045 l_party.marital_status := p_mar_status;
1046 l_party.marital_status_effective_date := p_mar_status_effective_date;
1047 l_party.middle_name_phonetic := p_middle_name_phonetic;
1048 l_party.personal_income := p_personal_income;
1049 l_party.person_academic_title := p_person_academic_title;
1050 l_party.person_first_name := p_person_first_name;
1051 l_party.person_first_name_phonetic := p_person_first_name_phonetic;
1052 l_party.person_identifier := p_person_identifier;
1056 l_party.person_last_name_phonetic := p_person_last_name_phonetic;
1053 l_party.person_iden_type := p_person_iden_type;
1054 l_party.person_initials := p_person_initials;
1055 l_party.person_last_name := p_person_last_name;
1057 l_party.person_middle_name := p_person_middle_name;
1058 l_party.person_name := p_person_name;
1059 l_party.person_name_phonetic := p_person_name_phonetic;
1060 l_party.person_name_suffix := p_person_name_suffix;
1061 l_party.person_previous_last_name := p_person_previous_last_name;
1062 l_party.person_pre_name_adjunct := p_person_pre_name_adjunct;
1063 l_party.person_title := p_person_title;
1064 l_party.place_of_birth := p_place_of_birth;
1065 l_party.all_account_names := p_all_account_names;
1066 l_party.all_account_numbers := p_all_account_numbers;
1067 l_party.custom_attribute1 := p_custom_attribute1;
1068 l_party.custom_attribute10 := p_custom_attribute10;
1069 l_party.custom_attribute11 := p_custom_attribute11;
1070 l_party.custom_attribute12 := p_custom_attribute12;
1071 l_party.custom_attribute13 := p_custom_attribute13;
1072 l_party.custom_attribute14 := p_custom_attribute14;
1073 l_party.custom_attribute15 := p_custom_attribute15;
1074 l_party.custom_attribute16 := p_custom_attribute16;
1075 l_party.custom_attribute17 := p_custom_attribute17;
1076 l_party.custom_attribute18 := p_custom_attribute18;
1077 l_party.custom_attribute19 := p_custom_attribute19;
1078 l_party.custom_attribute2 := p_custom_attribute2;
1079 l_party.custom_attribute20 := p_custom_attribute20;
1080 l_party.custom_attribute21 := p_custom_attribute21;
1081 l_party.custom_attribute22 := p_custom_attribute22;
1082 l_party.custom_attribute23 := p_custom_attribute23;
1083 l_party.custom_attribute24 := p_custom_attribute24;
1084 l_party.custom_attribute25 := p_custom_attribute25;
1085 l_party.custom_attribute26 := p_custom_attribute26;
1086 l_party.custom_attribute27 := p_custom_attribute27;
1087 l_party.custom_attribute28 := p_custom_attribute28;
1088 l_party.custom_attribute29 := p_custom_attribute29;
1089 l_party.custom_attribute3 := p_custom_attribute3;
1090 l_party.custom_attribute30 := p_custom_attribute30;
1091 l_party.custom_attribute4 := p_custom_attribute4;
1092 l_party.custom_attribute5 := p_custom_attribute5;
1093 l_party.custom_attribute6 := p_custom_attribute6;
1094 l_party.custom_attribute7 := p_custom_attribute7;
1095 l_party.custom_attribute8 := p_custom_attribute8;
1096 l_party.custom_attribute9 := p_custom_attribute9;
1097 l_party.analysis_fy := p_analysis_fy;
1098 l_party.avg_high_credit := p_avg_high_credit;
1099 l_party.best_time_contact_begin := p_best_time_contact_begin;
1100 l_party.best_time_contact_end := p_best_time_contact_end;
1101 l_party.branch_flag := p_branch_fg;
1102 l_party.business_scope := p_business_scope;
1103 l_party.ceo_name := p_ceo_name;
1104 l_party.ceo_title := p_ceo_title;
1105 l_party.cong_dist_code := p_cong_dist_code;
1106 l_party.content_source_number := p_content_source_number;
1107 l_party.content_source_type := p_content_source_type;
1108 l_party.control_yr := p_control_yr;
1109 l_party.corporation_class := p_corporation_class;
1110 l_party.credit_score := p_credit_score;
1111 l_party.credit_score_age := p_credit_score_age;
1112 l_party.credit_score_class := p_credit_score_class;
1113 l_party.credit_score_commentary := p_credit_score_commentary;
1114 l_party.credit_score_commentary10 := p_credit_score_commentary10;
1115 l_party.credit_score_commentary2 := p_credit_score_commentary2;
1116 l_party.credit_score_commentary3 := p_credit_score_commentary3;
1117 l_party.credit_score_commentary4 := p_credit_score_commentary4;
1118 l_party.credit_score_commentary5 := p_credit_score_commentary5;
1119 l_party.credit_score_commentary6 := p_credit_score_commentary6;
1120 l_party.credit_score_commentary7 := p_credit_score_commentary7;
1121 l_party.credit_score_commentary8 := p_credit_score_commentary8;
1122 l_party.credit_score_commentary9 := p_credit_score_commentary9;
1123 l_party.credit_score_date := p_credit_score_date;
1124 l_party.credit_score_incd_default := p_credit_score_incd_default;
1125 l_party.credit_score_natl_percentile := p_credit_score_natl_percentile;
1126 l_party.curr_fy_potential_revenue := p_curr_fy_potential_revenue;
1127 l_party.db_rating := p_db_rating;
1128 l_party.debarments_count := p_debarments_count;
1129 l_party.debarments_date := p_debarments_date;
1130 l_party.debarment_ind := p_debarment_ind;
1131 l_party.disadv_8a_ind := p_disadv_8a_ind;
1132 l_party.duns_number_c := p_duns_number_c;
1133 l_party.employees_total := p_employees_total;
1134 l_party.emp_at_primary_adr := p_emp_at_primary_adr;
1135 l_party.emp_at_primary_adr_est_ind := p_emp_at_primary_adr_est_ind;
1139 l_party.export_ind := p_export_ind;
1136 l_party.emp_at_primary_adr_min_ind := p_emp_at_primary_adr_min_ind;
1137 l_party.emp_at_primary_adr_text := p_emp_at_primary_adr_text;
1138 l_party.enquiry_duns := p_enquiry_duns;
1140 l_party.failure_score := p_failure_score;
1141 l_party.failure_score_age := p_failure_score_age;
1142 l_party.failure_score_class := p_failure_score_class;
1143 l_party.failure_score_commentary := p_failure_score_commentary;
1144 l_party.failure_score_commentary10 := p_failure_score_commentary10;
1145 l_party.failure_score_commentary2 := p_failure_score_commentary2;
1146 l_party.failure_score_commentary3 := p_failure_score_commentary3;
1147 l_party.failure_score_commentary4 := p_failure_score_commentary4;
1148 l_party.failure_score_commentary5 := p_failure_score_commentary5;
1149 l_party.failure_score_commentary6 := p_failure_score_commentary6;
1150 l_party.failure_score_commentary7 := p_failure_score_commentary7;
1151 l_party.failure_score_commentary8 := p_failure_score_commentary8;
1152 l_party.failure_score_commentary9 := p_failure_score_commentary9;
1153 l_party.failure_score_date := p_failure_score_date;
1154 l_party.failure_score_incd_default := p_failure_score_incd_default;
1155 l_party.failure_score_override_code := p_failure_score_override_code;
1156 l_party.fiscal_yearend_month := p_fiscal_yearend_month;
1157 l_party.global_failure_score := p_global_failure_score;
1158 l_party.gsa_indicator_flag := p_gsa_indicator_fg;
1159 l_party.high_credit := p_high_credit;
1160 l_party.hq_branch_ind := p_hq_branch_ind;
1161 l_party.import_ind := p_import_ind;
1162 l_party.incorp_year := p_incorp_year;
1163 l_party.internal_flag := p_internal_fg;
1164 l_party.jgzz_fiscal_code := p_jgzz_fiscal_code;
1165 l_party.party_all_names := p_party_all_names;
1166 l_party.known_as := p_known_as;
1167 l_party.known_as2 := p_known_as2;
1168 l_party.known_as3 := p_known_as3;
1169 l_party.known_as4 := p_known_as4;
1170 l_party.known_as5 := p_known_as5;
1171 l_party.labor_surplus_ind := p_labor_surplus_ind;
1172 l_party.legal_status := p_legal_status;
1173
1174 l_party_site.party_site_number := s_party_site_number;
1175 l_party_site.custom_attribute16 := s_custom_attribute16;
1176 l_party_site.custom_attribute17 := s_custom_attribute17;
1177 l_party_site.custom_attribute18 := s_custom_attribute18;
1178 l_party_site.custom_attribute19 := s_custom_attribute19;
1179 l_party_site.custom_attribute2 := s_custom_attribute2;
1180 l_party_site.custom_attribute20 := s_custom_attribute20;
1181 l_party_site.custom_attribute21 := s_custom_attribute21;
1182 l_party_site.custom_attribute22 := s_custom_attribute22;
1183 l_party_site.custom_attribute23 := s_custom_attribute23;
1184 l_party_site.custom_attribute24 := s_custom_attribute24;
1185 l_party_site.custom_attribute25 := s_custom_attribute25;
1186 l_party_site.custom_attribute26 := s_custom_attribute26;
1187 l_party_site.custom_attribute27 := s_custom_attribute27;
1188 l_party_site.custom_attribute28 := s_custom_attribute28;
1189 l_party_site.custom_attribute29 := s_custom_attribute29;
1190 l_party_site.custom_attribute3 := s_custom_attribute3;
1191 l_party_site.custom_attribute30 := s_custom_attribute30;
1192 l_party_site.custom_attribute4 := s_custom_attribute4;
1193 l_party_site.custom_attribute5 := s_custom_attribute5;
1194 l_party_site.custom_attribute6 := s_custom_attribute6;
1195 l_party_site.custom_attribute7 := s_custom_attribute7;
1196 l_party_site.custom_attribute8 := s_custom_attribute8;
1197 l_party_site.custom_attribute9 := s_custom_attribute9;
1198 l_party_site.address1 := s_address1;
1199 l_party_site.address2 := s_address2;
1200 l_party_site.address3 := s_address3;
1201 l_party_site.address4 := s_address4;
1202 l_party_site.floor := s_floor;
1203 l_party_site.house_number := s_house_number;
1204 l_party_site.language := s_language;
1205 l_party_site.clli_code := s_clli_code;
1206 l_party_site.content_source_type := s_content_source_type;
1207 l_party_site.country := s_country;
1208 l_party_site.county := s_county;
1209 l_party_site.trailing_directory_code := s_trailing_directory_code;
1210 l_party_site.validated_flag := s_validated_fg;
1211 l_party_site.identifying_address_flag := s_identifying_address_fg;
1212 l_party_site.mailstop := s_mailstop;
1213 l_party_site.party_site_name := s_party_site_name;
1214 l_party_site.address := s_address;
1215 l_party_site.custom_attribute1 := s_custom_attribute1;
1216 l_party_site.custom_attribute10 := s_custom_attribute10;
1217 l_party_site.custom_attribute11 := s_custom_attribute11;
1218 l_party_site.custom_attribute12 := s_custom_attribute12;
1222 l_party_site.city := s_city;
1219 l_party_site.custom_attribute13 := s_custom_attribute13;
1220 l_party_site.custom_attribute14 := s_custom_attribute14;
1221 l_party_site.custom_attribute15 := s_custom_attribute15;
1223 l_party_site.address_effective_date := s_address_effective_date;
1224 l_party_site.address_expiration_date := s_address_expiration_date;
1225 l_party_site.address_lines_phonetic := s_address_lines_phonetic;
1226 l_party_site.position := s_position;
1227 l_party_site.postal_code := s_postal_code;
1228 l_party_site.postal_plus4_code := s_postal_plus4_code;
1229 l_party_site.po_box_number := s_po_box_number;
1230 l_party_site.province := s_province;
1231 l_party_site.sales_tax_geocode := s_sales_tax_geocode;
1232 l_party_site.sales_tax_inside_city_limits := s_sales_tax_inside_city_limits;
1233 l_party_site.state := s_state;
1234 l_party_site.street := s_street;
1235 l_party_site.street_number := s_street_number;
1236 l_party_site.street_suffix := s_street_suffix;
1237 l_party_site.suite := s_suite;
1238
1239 l_contact.directional_flag := c_directional_fg;
1240 l_contact.native_language := c_native_language;
1241 l_contact.other_language_1 := c_other_language_1;
1242 l_contact.title := c_title;
1243 l_contact.custom_attribute1 := c_custom_attribute1;
1244 l_contact.custom_attribute10 := c_custom_attribute10;
1245 l_contact.custom_attribute11 := c_custom_attribute11;
1246 l_contact.custom_attribute12 := c_custom_attribute12;
1247 l_contact.custom_attribute13 := c_custom_attribute13;
1248 l_contact.custom_attribute14 := c_custom_attribute14;
1249 l_contact.custom_attribute15 := c_custom_attribute15;
1250 l_contact.custom_attribute16 := c_custom_attribute16;
1251 l_contact.custom_attribute17 := c_custom_attribute17;
1252 l_contact.custom_attribute18 := c_custom_attribute18;
1253 l_contact.custom_attribute19 := c_custom_attribute19;
1254 l_contact.custom_attribute2 := c_custom_attribute2;
1255 l_contact.custom_attribute20 := c_custom_attribute20;
1256 l_contact.custom_attribute21 := c_custom_attribute21;
1257 l_contact.custom_attribute22 := c_custom_attribute22;
1258 l_contact.custom_attribute23 := c_custom_attribute23;
1259 l_contact.custom_attribute24 := c_custom_attribute24;
1260 l_contact.custom_attribute25 := c_custom_attribute25;
1261 l_contact.custom_attribute26 := c_custom_attribute26;
1262 l_contact.custom_attribute27 := c_custom_attribute27;
1263 l_contact.custom_attribute28 := c_custom_attribute28;
1264 l_contact.custom_attribute29 := c_custom_attribute29;
1265 l_contact.custom_attribute3 := c_custom_attribute3;
1266 l_contact.custom_attribute30 := c_custom_attribute30;
1267 l_contact.custom_attribute4 := c_custom_attribute4;
1268 l_contact.custom_attribute5 := c_custom_attribute5;
1269 l_contact.custom_attribute6 := c_custom_attribute6;
1270 l_contact.mail_stop := c_mail_stop;
1271 l_contact.best_time_contact_end := c_best_time_contact_end;
1272 l_contact.job_title_code := c_job_title_code;
1273 l_contact.relationship_type := c_relationship_type;
1274 l_contact.other_language_2 := c_other_language_2;
1275 l_contact.rank := c_rank;
1276 l_contact.reference_use_flag := c_reference_use_fg;
1277 l_contact.date_of_birth := c_date_of_birth;
1278 l_contact.date_of_death := c_date_of_death;
1279 l_contact.jgzz_fiscal_code := c_jgzz_fiscal_code;
1280 l_contact.known_as := c_known_as;
1281 l_contact.person_academic_title := c_person_academic_title;
1282 l_contact.person_first_name := c_person_first_name;
1283 l_contact.person_first_name_phonetic := c_person_first_name_phonetic;
1284 l_contact.person_identifier := c_person_identifier;
1285 l_contact.person_iden_type := c_person_iden_type;
1286 l_contact.person_initials := c_person_initials;
1287 l_contact.person_last_name := c_person_last_name;
1288 l_contact.person_last_name_phonetic := c_person_last_name_phonetic;
1289 l_contact.person_middle_name := c_person_middle_name;
1290 l_contact.person_name := c_person_name;
1291 l_contact.person_name_phonetic := c_person_name_phonetic;
1292 l_contact.person_name_suffix := c_person_name_suffix;
1293 l_contact.person_previous_last_name := c_person_previous_last_name;
1294 l_contact.person_title := c_person_title;
1295 l_contact.place_of_birth := c_place_of_birth;
1296 l_contact.tax_name := c_tax_name;
1297 l_contact.tax_reference := c_tax_reference;
1298 l_contact.content_source_type := c_content_source_type;
1299 l_contact.job_title := c_job_title;
1300 l_contact.custom_attribute7 := c_custom_attribute7;
1301 l_contact.custom_attribute8 := c_custom_attribute8;
1302 l_contact.custom_attribute9 := c_custom_attribute9;
1306 l_contact.best_time_contact_begin := c_best_time_contact_begin;
1303 l_contact.contact_number := c_contact_number;
1304 l_contact.contact_name := c_contact_name;
1305 l_contact.decision_maker_flag := c_decision_maker_fg;
1307
1308 l_contact_point.phone_line_type := t_phone_line_type;
1309 l_contact_point.phone_number := t_phone_number;
1310 l_contact_point.primary_flag := t_primary_fg;
1311 l_contact_point.raw_phone_number := t_raw_phone_number;
1312 l_contact_point.telephone_type := t_telephone_type;
1313 l_contact_point.telex_number := t_telex_number;
1314 l_contact_point.time_zone := t_time_zone;
1315 l_contact_point.url := t_url;
1316 l_contact_point.web_type := t_web_type;
1317 l_contact_point.contact_point_type := t_contact_point_type;
1318 l_contact_point.custom_attribute1 := t_custom_attribute1;
1319 l_contact_point.custom_attribute10 := t_custom_attribute10;
1320 l_contact_point.edi_tp_header_id := t_edi_tp_header_id;
1321 l_contact_point.custom_attribute5 := t_custom_attribute5;
1322 l_contact_point.custom_attribute6 := t_custom_attribute6;
1323 l_contact_point.custom_attribute4 := t_custom_attribute4;
1324 l_contact_point.edi_remittance_instruction := t_edi_remittance_instruction;
1325 l_contact_point.edi_transaction_handling := t_edi_transaction_handling;
1326 l_contact_point.custom_attribute8 := t_custom_attribute8;
1327 l_contact_point.custom_attribute9 := t_custom_attribute9;
1328 l_contact_point.content_source_type := t_content_source_type;
1329 l_contact_point.edi_ece_tp_location_code := t_edi_ece_tp_location_code;
1330 l_contact_point.edi_id_number := t_edi_id_number;
1331 l_contact_point.edi_payment_format := t_edi_payment_format;
1332 l_contact_point.custom_attribute12 := t_custom_attribute12;
1333 l_contact_point.custom_attribute13 := t_custom_attribute13;
1334 l_contact_point.custom_attribute14 := t_custom_attribute14;
1335 l_contact_point.custom_attribute15 := t_custom_attribute15;
1336 l_contact_point.custom_attribute16 := t_custom_attribute16;
1337 l_contact_point.custom_attribute17 := t_custom_attribute17;
1338 l_contact_point.custom_attribute18 := t_custom_attribute18;
1339 l_contact_point.custom_attribute19 := t_custom_attribute19;
1340 l_contact_point.custom_attribute2 := t_custom_attribute2;
1341 l_contact_point.custom_attribute20 := t_custom_attribute20;
1342 l_contact_point.custom_attribute21 := t_custom_attribute21;
1343 l_contact_point.custom_attribute22 := t_custom_attribute22;
1344 l_contact_point.custom_attribute23 := t_custom_attribute23;
1345 l_contact_point.custom_attribute24 := t_custom_attribute24;
1346 l_contact_point.custom_attribute25 := t_custom_attribute25;
1347 l_contact_point.custom_attribute26 := t_custom_attribute26;
1348 l_contact_point.custom_attribute27 := t_custom_attribute27;
1349 l_contact_point.custom_attribute28 := t_custom_attribute28;
1350 l_contact_point.custom_attribute29 := t_custom_attribute29;
1351 l_contact_point.custom_attribute3 := t_custom_attribute3;
1352 l_contact_point.custom_attribute30 := t_custom_attribute30;
1353 l_contact_point.custom_attribute11 := t_custom_attribute11;
1354 l_contact_point.email_address := t_email_address;
1355 l_contact_point.email_format := t_email_format;
1356 l_contact_point.flex_format_phone_number := t_flex_format_phone_number;
1357 l_contact_point.last_contact_dt_time := t_last_contact_dt_time;
1358 l_contact_point.phone_area_code := t_phone_area_code;
1359 l_contact_point.phone_calling_calendar := t_phone_calling_calendar;
1360 l_contact_point.phone_country_code := t_phone_country_code;
1361 l_contact_point.phone_extension := t_phone_extension;
1362 l_contact_point.edi_remittance_method := t_edi_remittance_method;
1363 l_contact_point.custom_attribute7 := t_custom_attribute7;
1364 l_contact_point.edi_payment_method := t_edi_payment_method;
1365
1366
1367 --fnd_client_info.set_org_context('458');
1368
1369 -------------------------------------
1370 -- Put Record type variables in lists
1371 -------------------------------------
1372 IF NOT arh_dqm_srch_pkg.IsNull(l_party_site) THEN
1373 l_party_site_list(1) := l_party_site;
1374 END IF;
1375
1376 IF NOT arh_dqm_srch_pkg.IsNull(l_contact) THEN
1377 l_contact_list(1) := l_contact;
1378 END IF;
1379
1380 l_contact_point_list := arh_dqm_srch_pkg.Contact_point_list( p_contact_point =>l_contact_point);
1381
1382 hz_party_search.find_party_details (
1383 p_rule_id => x_rule_id,
1384 p_party_search_rec => l_party,
1385 p_party_site_list => l_party_site_list,
1386 p_contact_list => l_contact_list,
1387 p_contact_point_list => l_contact_point_list,
1388 p_restrict_sql => p_restrict_sql,
1389 p_search_merged => NULL,
1390 p_match_type => NULL,
1391 x_search_ctx_id => x_search_ctx_id,
1392 x_num_matches => x_num_matches,
1393 x_return_status => x_return_status,
1397 IF x_msg_count > 1 THEN
1394 x_msg_count => x_msg_count,
1395 x_msg_data => x_msg_data);
1396
1398 FOR i IN 1..x_msg_count LOOP
1399 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1400 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1401 END LOOP;
1402 x_msg_data := tmp_var1;
1403 END IF;
1404
1405 END Exec_Dqm_Api;
1406
1407 PROCEDURE Exec_Get_Matching_Sites
1408 (
1409 --PS
1410 s_party_site_number VARCHAR2,
1411 s_custom_attribute16 VARCHAR2,
1412 s_custom_attribute17 VARCHAR2,
1413 s_custom_attribute18 VARCHAR2,
1414 s_custom_attribute19 VARCHAR2,
1415 s_custom_attribute2 VARCHAR2,
1416 s_custom_attribute20 VARCHAR2,
1417 s_custom_attribute21 VARCHAR2,
1418 s_custom_attribute22 VARCHAR2,
1419 s_custom_attribute23 VARCHAR2,
1420 s_custom_attribute24 VARCHAR2,
1421 s_custom_attribute25 VARCHAR2,
1422 s_custom_attribute26 VARCHAR2,
1423 s_custom_attribute27 VARCHAR2,
1424 s_custom_attribute28 VARCHAR2,
1425 s_custom_attribute29 VARCHAR2,
1426 s_custom_attribute3 VARCHAR2,
1427 s_custom_attribute30 VARCHAR2,
1428 s_custom_attribute4 VARCHAR2,
1429 s_custom_attribute5 VARCHAR2,
1430 s_custom_attribute6 VARCHAR2,
1431 s_custom_attribute7 VARCHAR2,
1432 s_custom_attribute8 VARCHAR2,
1433 s_custom_attribute9 VARCHAR2,
1434 s_address1 VARCHAR2,
1435 s_address2 VARCHAR2,
1436 s_address3 VARCHAR2,
1437 s_address4 VARCHAR2,
1438 s_floor VARCHAR2,
1439 s_house_number VARCHAR2,
1440 s_language VARCHAR2,
1441 s_clli_code VARCHAR2,
1442 s_content_source_type VARCHAR2,
1443 s_country VARCHAR2,
1444 s_county VARCHAR2,
1445 s_trailing_directory_code VARCHAR2,
1446 s_validated_fg VARCHAR2,
1447 s_identifying_address_fg VARCHAR2,
1448 s_mailstop VARCHAR2,
1449 s_party_site_name VARCHAR2,
1450 s_address VARCHAR2,
1451 s_custom_attribute1 VARCHAR2,
1452 s_custom_attribute10 VARCHAR2,
1453 s_custom_attribute11 VARCHAR2,
1454 s_custom_attribute12 VARCHAR2,
1455 s_custom_attribute13 VARCHAR2,
1456 s_custom_attribute14 VARCHAR2,
1457 s_custom_attribute15 VARCHAR2,
1458 s_city VARCHAR2,
1459 s_address_effective_date DATE,
1460 s_address_expiration_date DATE,
1461 s_address_lines_phonetic VARCHAR2,
1462 s_position VARCHAR2,
1463 s_postal_code VARCHAR2,
1464 s_postal_plus4_code VARCHAR2,
1465 s_po_box_number VARCHAR2,
1466 s_province VARCHAR2,
1467 s_sales_tax_geocode VARCHAR2,
1468 s_sales_tax_inside_city_limits VARCHAR2,
1469 s_state VARCHAR2,
1470 s_street VARCHAR2,
1471 s_street_number VARCHAR2,
1472 s_street_suffix VARCHAR2,
1473 s_suite VARCHAR2,
1474 --CT
1475 c_directional_fg VARCHAR2,
1476 c_native_language VARCHAR2,
1477 c_other_language_1 VARCHAR2,
1478 c_title VARCHAR2,
1479 c_custom_attribute1 VARCHAR2,
1480 c_custom_attribute10 VARCHAR2,
1481 c_custom_attribute11 VARCHAR2,
1482 c_custom_attribute12 VARCHAR2,
1483 c_custom_attribute13 VARCHAR2,
1484 c_custom_attribute14 VARCHAR2,
1485 c_custom_attribute15 VARCHAR2,
1486 c_custom_attribute16 VARCHAR2,
1487 c_custom_attribute17 VARCHAR2,
1488 c_custom_attribute18 VARCHAR2,
1489 c_custom_attribute19 VARCHAR2,
1490 c_custom_attribute2 VARCHAR2,
1491 c_custom_attribute20 VARCHAR2,
1492 c_custom_attribute21 VARCHAR2,
1493 c_custom_attribute22 VARCHAR2,
1494 c_custom_attribute23 VARCHAR2,
1495 c_custom_attribute24 VARCHAR2,
1496 c_custom_attribute25 VARCHAR2,
1497 c_custom_attribute26 VARCHAR2,
1498 c_custom_attribute27 VARCHAR2,
1499 c_custom_attribute28 VARCHAR2,
1500 c_custom_attribute29 VARCHAR2,
1501 c_custom_attribute3 VARCHAR2,
1502 c_custom_attribute30 VARCHAR2,
1503 c_custom_attribute4 VARCHAR2,
1504 c_custom_attribute5 VARCHAR2,
1505 c_custom_attribute6 VARCHAR2,
1506 c_mail_stop VARCHAR2,
1507 c_best_time_contact_end DATE,
1508 c_job_title_code VARCHAR2,
1509 c_relationship_type VARCHAR2,
1510 c_other_language_2 VARCHAR2,
1511 c_rank VARCHAR2,
1512 c_reference_use_fg VARCHAR2,
1513 c_date_of_birth DATE,
1517 c_person_academic_title VARCHAR2,
1514 c_date_of_death DATE,
1515 c_jgzz_fiscal_code VARCHAR2,
1516 c_known_as VARCHAR2,
1518 c_person_first_name VARCHAR2,
1519 c_person_first_name_phonetic VARCHAR2,
1520 c_person_identifier VARCHAR2,
1521 c_person_iden_type VARCHAR2,
1522 c_person_initials VARCHAR2,
1523 c_person_last_name VARCHAR2,
1524 c_person_last_name_phonetic VARCHAR2,
1525 c_person_middle_name VARCHAR2,
1526 c_person_name VARCHAR2,
1527 c_person_name_phonetic VARCHAR2,
1528 c_person_name_suffix VARCHAR2,
1529 c_person_previous_last_name VARCHAR2,
1530 c_person_title VARCHAR2,
1531 c_place_of_birth VARCHAR2,
1532 c_tax_name VARCHAR2,
1533 c_tax_reference VARCHAR2,
1534 c_content_source_type VARCHAR2,
1535 c_job_title VARCHAR2,
1536 c_custom_attribute7 VARCHAR2,
1537 c_custom_attribute8 VARCHAR2,
1538 c_custom_attribute9 VARCHAR2,
1539 c_contact_number VARCHAR2,
1540 c_contact_name VARCHAR2,
1541 c_decision_maker_fg VARCHAR2,
1542 c_best_time_contact_begin DATE,
1543 --CPT
1544 t_phone_line_type VARCHAR2,
1545 t_phone_number VARCHAR2,
1546 t_primary_fg VARCHAR2,
1547 t_raw_phone_number VARCHAR2,
1548 t_telephone_type VARCHAR2,
1549 t_telex_number VARCHAR2,
1550 t_time_zone NUMBER,
1551 t_url VARCHAR2,
1552 t_web_type VARCHAR2,
1553 t_contact_point_type VARCHAR2,
1554 t_custom_attribute1 VARCHAR2,
1555 t_custom_attribute10 VARCHAR2,
1556 t_edi_tp_header_id NUMBER,
1557 t_custom_attribute5 VARCHAR2,
1558 t_custom_attribute6 VARCHAR2,
1559 t_custom_attribute4 VARCHAR2,
1560 t_edi_remittance_instruction VARCHAR2,
1561 t_edi_transaction_handling VARCHAR2,
1562 t_custom_attribute8 VARCHAR2,
1563 t_custom_attribute9 VARCHAR2,
1564 t_content_source_type VARCHAR2,
1565 t_edi_ece_tp_location_code VARCHAR2,
1566 t_edi_id_number VARCHAR2,
1567 t_edi_payment_format VARCHAR2,
1568 t_custom_attribute12 VARCHAR2,
1569 t_custom_attribute13 VARCHAR2,
1570 t_custom_attribute14 VARCHAR2,
1571 t_custom_attribute15 VARCHAR2,
1572 t_custom_attribute16 VARCHAR2,
1573 t_custom_attribute17 VARCHAR2,
1574 t_custom_attribute18 VARCHAR2,
1575 t_custom_attribute19 VARCHAR2,
1576 t_custom_attribute2 VARCHAR2,
1577 t_custom_attribute20 VARCHAR2,
1578 t_custom_attribute21 VARCHAR2,
1579 t_custom_attribute22 VARCHAR2,
1580 t_custom_attribute23 VARCHAR2,
1581 t_custom_attribute24 VARCHAR2,
1582 t_custom_attribute25 VARCHAR2,
1583 t_custom_attribute26 VARCHAR2,
1584 t_custom_attribute27 VARCHAR2,
1585 t_custom_attribute28 VARCHAR2,
1586 t_custom_attribute29 VARCHAR2,
1587 t_custom_attribute3 VARCHAR2,
1588 t_custom_attribute30 VARCHAR2,
1589 t_custom_attribute11 VARCHAR2,
1590 t_email_address VARCHAR2,
1591 t_email_format VARCHAR2,
1592 t_flex_format_phone_number VARCHAR2,
1593 t_last_contact_dt_time DATE,
1594 t_phone_area_code VARCHAR2,
1595 t_phone_calling_calendar VARCHAR2,
1596 t_phone_country_code VARCHAR2,
1597 t_phone_extension VARCHAR2,
1598 t_edi_remittance_method VARCHAR2,
1599 t_custom_attribute7 VARCHAR2,
1600 t_edi_payment_method VARCHAR2,
1601 --other parameter
1602 p_restrict_sql VARCHAR2,
1603 p_party_id VARCHAR2,
1604 p_cur_all VARCHAR2,
1605 x_status VARCHAR2,
1606 x_rule_id IN OUT NOCOPY NUMBER,
1607 x_search_ctx_id IN OUT NOCOPY NUMBER,
1608 x_num_matches IN OUT NOCOPY NUMBER,
1609 x_msg_count IN OUT NOCOPY NUMBER,
1610 x_msg_data IN OUT NOCOPY varchar2,
1611 x_return_status IN OUT NOCOPY VARCHAR2)
1612 IS
1613 -- DQM record types
1614 l_party_site hz_party_search.party_site_search_rec_type;
1615 l_contact hz_party_search.contact_search_rec_type;
1616 l_contact_point hz_party_search.contact_point_search_rec_type;
1617 -- DQM list type
1618 l_party_site_list hz_party_search.party_site_list;
1619 l_contact_list hz_party_search.contact_list;
1620 l_contact_point_list hz_party_search.contact_point_list;
1621 -- for error usage
1622 tmp_var VARCHAR2(2000);
1623 tmp_var1 VARCHAR2(2000);
1624
1625 /*
1626 -- Cursor for details
1627 CURSOR matched_parties(i_ctx_id IN NUMBER) IS
1628 SELECT party_id,
1629 score
1630 FROM hz_matched_parties_gt
1631 WHERE search_context_id = i_ctx_id
1635 CURSOR matched_cpts(i_ctx_id IN NUMBER) IS
1632 ORDER BY SCORE;
1633
1634 -- Cursor for details
1636 SELECT party_id,
1637 contact_point_id,
1638 score
1639 FROM hz_matched_cpts_gt
1640 WHERE search_context_id = i_ctx_id
1641 ORDER BY SCORE;
1642
1643 l_cpt_rec matched_cpts%ROWTYPE;
1644
1645 --
1646 CURSOR matched_contacts(i_ctx_id IN NUMBER) IS
1647 SELECT party_id,
1648 org_contact_id,
1649 score
1650 FROM hz_matched_contacts_gt
1651 WHERE search_context_id = i_ctx_id
1652 ORDER BY SCORE;
1653
1654 l_contact_rec matched_contacts%ROWTYPE;
1655
1656 l_cur_all VARCHAR2(3);
1657 */
1658 BEGIN
1659
1660 l_party_site.party_site_number := s_party_site_number;
1661 l_party_site.custom_attribute16 := s_custom_attribute16;
1662 l_party_site.custom_attribute17 := s_custom_attribute17;
1663 l_party_site.custom_attribute18 := s_custom_attribute18;
1664 l_party_site.custom_attribute19 := s_custom_attribute19;
1665 l_party_site.custom_attribute2 := s_custom_attribute2;
1666 l_party_site.custom_attribute20 := s_custom_attribute20;
1667 l_party_site.custom_attribute21 := s_custom_attribute21;
1668 l_party_site.custom_attribute22 := s_custom_attribute22;
1669 l_party_site.custom_attribute23 := s_custom_attribute23;
1670 l_party_site.custom_attribute24 := s_custom_attribute24;
1671 l_party_site.custom_attribute25 := s_custom_attribute25;
1672 l_party_site.custom_attribute26 := s_custom_attribute26;
1673 l_party_site.custom_attribute27 := s_custom_attribute27;
1674 l_party_site.custom_attribute28 := s_custom_attribute28;
1675 l_party_site.custom_attribute29 := s_custom_attribute29;
1676 l_party_site.custom_attribute3 := s_custom_attribute3;
1677 l_party_site.custom_attribute30 := s_custom_attribute30;
1678 l_party_site.custom_attribute4 := s_custom_attribute4;
1679 l_party_site.custom_attribute5 := s_custom_attribute5;
1680 l_party_site.custom_attribute6 := s_custom_attribute6;
1681 l_party_site.custom_attribute7 := s_custom_attribute7;
1682 l_party_site.custom_attribute8 := s_custom_attribute8;
1683 l_party_site.custom_attribute9 := s_custom_attribute9;
1684 l_party_site.address1 := s_address1;
1685 l_party_site.address2 := s_address2;
1686 l_party_site.address3 := s_address3;
1687 l_party_site.address4 := s_address4;
1688 l_party_site.floor := s_floor;
1689 l_party_site.house_number := s_house_number;
1690 l_party_site.language := s_language;
1691 l_party_site.clli_code := s_clli_code;
1692 l_party_site.content_source_type := s_content_source_type;
1693 l_party_site.country := s_country;
1694 l_party_site.county := s_county;
1695 l_party_site.trailing_directory_code := s_trailing_directory_code;
1696 l_party_site.validated_flag := s_validated_fg;
1697 l_party_site.identifying_address_flag := s_identifying_address_fg;
1698 l_party_site.mailstop := s_mailstop;
1699 l_party_site.party_site_name := s_party_site_name;
1700 l_party_site.address := s_address;
1701 l_party_site.custom_attribute1 := s_custom_attribute1;
1702 l_party_site.custom_attribute10 := s_custom_attribute10;
1703 l_party_site.custom_attribute11 := s_custom_attribute11;
1704 l_party_site.custom_attribute12 := s_custom_attribute12;
1705 l_party_site.custom_attribute13 := s_custom_attribute13;
1706 l_party_site.custom_attribute14 := s_custom_attribute14;
1707 l_party_site.custom_attribute15 := s_custom_attribute15;
1708 l_party_site.city := s_city;
1709 l_party_site.address_effective_date := s_address_effective_date;
1710 l_party_site.address_expiration_date := s_address_expiration_date;
1711 l_party_site.address_lines_phonetic := s_address_lines_phonetic;
1712 l_party_site.position := s_position;
1713 l_party_site.postal_code := s_postal_code;
1714 l_party_site.postal_plus4_code := s_postal_plus4_code;
1715 l_party_site.po_box_number := s_po_box_number;
1716 l_party_site.province := s_province;
1717 l_party_site.sales_tax_geocode := s_sales_tax_geocode;
1718 l_party_site.sales_tax_inside_city_limits := s_sales_tax_inside_city_limits;
1719 l_party_site.state := s_state;
1720 l_party_site.street := s_street;
1721 l_party_site.street_number := s_street_number;
1722 l_party_site.street_suffix := s_street_suffix;
1723 l_party_site.suite := s_suite;
1724
1725 l_contact.directional_flag := c_directional_fg;
1726 l_contact.native_language := c_native_language;
1727 l_contact.other_language_1 := c_other_language_1;
1728 l_contact.title := c_title;
1729 l_contact.custom_attribute1 := c_custom_attribute1;
1730 l_contact.custom_attribute10 := c_custom_attribute10;
1734 l_contact.custom_attribute14 := c_custom_attribute14;
1731 l_contact.custom_attribute11 := c_custom_attribute11;
1732 l_contact.custom_attribute12 := c_custom_attribute12;
1733 l_contact.custom_attribute13 := c_custom_attribute13;
1735 l_contact.custom_attribute15 := c_custom_attribute15;
1736 l_contact.custom_attribute16 := c_custom_attribute16;
1737 l_contact.custom_attribute17 := c_custom_attribute17;
1738 l_contact.custom_attribute18 := c_custom_attribute18;
1739 l_contact.custom_attribute19 := c_custom_attribute19;
1740 l_contact.custom_attribute2 := c_custom_attribute2;
1741 l_contact.custom_attribute20 := c_custom_attribute20;
1742 l_contact.custom_attribute21 := c_custom_attribute21;
1743 l_contact.custom_attribute22 := c_custom_attribute22;
1744 l_contact.custom_attribute23 := c_custom_attribute23;
1745 l_contact.custom_attribute24 := c_custom_attribute24;
1746 l_contact.custom_attribute25 := c_custom_attribute25;
1747 l_contact.custom_attribute26 := c_custom_attribute26;
1748 l_contact.custom_attribute27 := c_custom_attribute27;
1749 l_contact.custom_attribute28 := c_custom_attribute28;
1750 l_contact.custom_attribute29 := c_custom_attribute29;
1751 l_contact.custom_attribute3 := c_custom_attribute3;
1752 l_contact.custom_attribute30 := c_custom_attribute30;
1753 l_contact.custom_attribute4 := c_custom_attribute4;
1754 l_contact.custom_attribute5 := c_custom_attribute5;
1755 l_contact.custom_attribute6 := c_custom_attribute6;
1756 l_contact.mail_stop := c_mail_stop;
1757 l_contact.best_time_contact_end := c_best_time_contact_end;
1758 l_contact.job_title_code := c_job_title_code;
1759 l_contact.relationship_type := c_relationship_type;
1760 l_contact.other_language_2 := c_other_language_2;
1761 l_contact.rank := c_rank;
1762 l_contact.reference_use_flag := c_reference_use_fg;
1763 l_contact.date_of_birth := c_date_of_birth;
1764 l_contact.date_of_death := c_date_of_death;
1765 l_contact.jgzz_fiscal_code := c_jgzz_fiscal_code;
1766 l_contact.known_as := c_known_as;
1767 l_contact.person_academic_title := c_person_academic_title;
1768 l_contact.person_first_name := c_person_first_name;
1769 l_contact.person_first_name_phonetic := c_person_first_name_phonetic;
1770 l_contact.person_identifier := c_person_identifier;
1771 l_contact.person_iden_type := c_person_iden_type;
1772 l_contact.person_initials := c_person_initials;
1773 l_contact.person_last_name := c_person_last_name;
1774 l_contact.person_last_name_phonetic := c_person_last_name_phonetic;
1775 l_contact.person_middle_name := c_person_middle_name;
1776 l_contact.person_name := c_person_name;
1777 l_contact.person_name_phonetic := c_person_name_phonetic;
1778 l_contact.person_name_suffix := c_person_name_suffix;
1779 l_contact.person_previous_last_name := c_person_previous_last_name;
1780 l_contact.person_title := c_person_title;
1781 l_contact.place_of_birth := c_place_of_birth;
1782 l_contact.tax_name := c_tax_name;
1783 l_contact.tax_reference := c_tax_reference;
1784 l_contact.content_source_type := c_content_source_type;
1785 l_contact.job_title := c_job_title;
1786 l_contact.custom_attribute7 := c_custom_attribute7;
1787 l_contact.custom_attribute8 := c_custom_attribute8;
1788 l_contact.custom_attribute9 := c_custom_attribute9;
1789 l_contact.contact_number := c_contact_number;
1790 l_contact.contact_name := c_contact_name;
1791 l_contact.decision_maker_flag := c_decision_maker_fg;
1792 l_contact.best_time_contact_begin := c_best_time_contact_begin;
1793
1794 l_contact_point.phone_line_type := t_phone_line_type;
1795 l_contact_point.phone_number := t_phone_number;
1796 l_contact_point.primary_flag := t_primary_fg;
1797 l_contact_point.raw_phone_number := t_raw_phone_number;
1798 l_contact_point.telephone_type := t_telephone_type;
1799 l_contact_point.telex_number := t_telex_number;
1800 l_contact_point.time_zone := t_time_zone;
1801 l_contact_point.url := t_url;
1802 l_contact_point.web_type := t_web_type;
1803 l_contact_point.contact_point_type := t_contact_point_type;
1804 l_contact_point.custom_attribute1 := t_custom_attribute1;
1805 l_contact_point.custom_attribute10 := t_custom_attribute10;
1806 l_contact_point.edi_tp_header_id := t_edi_tp_header_id;
1807 l_contact_point.custom_attribute5 := t_custom_attribute5;
1808 l_contact_point.custom_attribute6 := t_custom_attribute6;
1809 l_contact_point.custom_attribute4 := t_custom_attribute4;
1810 l_contact_point.edi_remittance_instruction := t_edi_remittance_instruction;
1811 l_contact_point.edi_transaction_handling := t_edi_transaction_handling;
1812 l_contact_point.custom_attribute8 := t_custom_attribute8;
1813 l_contact_point.custom_attribute9 := t_custom_attribute9;
1817 l_contact_point.edi_payment_format := t_edi_payment_format;
1814 l_contact_point.content_source_type := t_content_source_type;
1815 l_contact_point.edi_ece_tp_location_code := t_edi_ece_tp_location_code;
1816 l_contact_point.edi_id_number := t_edi_id_number;
1818 l_contact_point.custom_attribute12 := t_custom_attribute12;
1819 l_contact_point.custom_attribute13 := t_custom_attribute13;
1820 l_contact_point.custom_attribute14 := t_custom_attribute14;
1821 l_contact_point.custom_attribute15 := t_custom_attribute15;
1822 l_contact_point.custom_attribute16 := t_custom_attribute16;
1823 l_contact_point.custom_attribute17 := t_custom_attribute17;
1824 l_contact_point.custom_attribute18 := t_custom_attribute18;
1825 l_contact_point.custom_attribute19 := t_custom_attribute19;
1826 l_contact_point.custom_attribute2 := t_custom_attribute2;
1827 l_contact_point.custom_attribute20 := t_custom_attribute20;
1828 l_contact_point.custom_attribute21 := t_custom_attribute21;
1829 l_contact_point.custom_attribute22 := t_custom_attribute22;
1830 l_contact_point.custom_attribute23 := t_custom_attribute23;
1831 l_contact_point.custom_attribute24 := t_custom_attribute24;
1832 l_contact_point.custom_attribute25 := t_custom_attribute25;
1833 l_contact_point.custom_attribute26 := t_custom_attribute26;
1834 l_contact_point.custom_attribute27 := t_custom_attribute27;
1835 l_contact_point.custom_attribute28 := t_custom_attribute28;
1836 l_contact_point.custom_attribute29 := t_custom_attribute29;
1837 l_contact_point.custom_attribute3 := t_custom_attribute3;
1838 l_contact_point.custom_attribute30 := t_custom_attribute30;
1839 l_contact_point.custom_attribute11 := t_custom_attribute11;
1840 l_contact_point.email_address := t_email_address;
1841 l_contact_point.email_format := t_email_format;
1842 l_contact_point.flex_format_phone_number := t_flex_format_phone_number;
1843 l_contact_point.last_contact_dt_time := t_last_contact_dt_time;
1844 l_contact_point.phone_area_code := t_phone_area_code;
1845 l_contact_point.phone_calling_calendar := t_phone_calling_calendar;
1849 l_contact_point.custom_attribute7 := t_custom_attribute7;
1846 l_contact_point.phone_country_code := t_phone_country_code;
1847 l_contact_point.phone_extension := t_phone_extension;
1848 l_contact_point.edi_remittance_method := t_edi_remittance_method;
1850 l_contact_point.edi_payment_method := t_edi_payment_method;
1851
1852
1853 --fnd_client_info.set_org_context('458');
1854
1855 -------------------------------------
1856 -- Put Record type variables in lists
1857 -------------------------------------
1858 IF NOT arh_dqm_srch_pkg.IsNull(l_party_site) THEN
1859 l_party_site_list(1) := l_party_site;
1860 END IF;
1861
1862 IF NOT arh_dqm_srch_pkg.IsNull(l_contact) THEN
1863 l_contact_list(1) := l_contact;
1864 END IF;
1865
1866 l_contact_point_list := arh_dqm_srch_pkg.Contact_point_list( p_contact_point =>l_contact_point);
1867
1868
1869 hz_party_search.get_matching_party_sites (
1870 p_init_msg_list => FND_API.G_TRUE,
1871 p_rule_id => x_rule_id,
1872 p_party_id => p_party_id,
1873 p_party_site_list => l_party_site_list,
1874 p_contact_point_list => l_contact_point_list,
1875 p_restrict_sql => p_restrict_sql,
1876 p_match_type => NULL,
1877 x_search_ctx_id => x_search_ctx_id,
1878 x_num_matches => x_num_matches,
1879 x_return_status => x_return_status,
1880 x_msg_count => x_msg_count,
1881 x_msg_data => x_msg_data
1882 );
1883
1884
1885 IF x_msg_count > 1 THEN
1886 FOR i IN 1..x_msg_count LOOP
1887 tmp_var := fnd_msg_pub.get(p_encoded => fnd_api.g_false);
1888 tmp_var1 := tmp_var1 || ' '|| tmp_var;
1889 END LOOP;
1890 x_msg_data := tmp_var1;
1891 END IF;
1892
1893 END Exec_Get_Matching_Sites;
1894
1895 END;