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