[Home] [Help]
PACKAGE BODY: APPS.HZ_PARTY_ACQUIRE
Source
4 TYPE vTable IS TABLE OF VARCHAR2(255) index by binary_integer;
1 package BODY HZ_PARTY_ACQUIRE AS
2 /* $Header: ARHDQAQB.pls 120.7.12010000.2 2010/03/29 11:19:05 amstephe ship $ */
3
5
6 g_party_custom_attrs vTable;
7 g_party_custom_procs vTable;
8 g_party_custom_valid vTable;
9
10 g_ps_custom_attrs vTable;
11 g_ps_custom_procs vTable;
12 g_ps_custom_valid vTable;
13
14 g_cpt_custom_attrs vTable;
15 g_cpt_custom_procs vTable;
16 g_cpt_custom_valid vTable;
17
18 g_cont_custom_attrs vTable;
19 g_cont_custom_procs vTable;
20 g_cont_custom_valid vTable;
21
22 g_party_custom_queried VARCHAR2(1) := 'N';
23 g_ps_custom_queried VARCHAR2(1) := 'N';
24 g_cpt_custom_queried VARCHAR2(1) := 'N';
25 g_cont_custom_queried VARCHAR2(1) := 'N';
26
27 FUNCTION filter_ph_num(
28 p_inval IN VARCHAR2)
29 RETURN VARCHAR2 IS
30 BEGIN
31 IF p_inval IS NULL THEN
32 RETURN NULL;
33 END IF;
34 RETURN translate(
35 p_inval,
36 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZabcdefghijklmnopqrstuvwxyz!"#$%&()''*+,-./:;<=>?@[\]^_`{|}~ ',
37 '0123456789ABCDEFGHIJKLMNOPQRSTUVWXYZABCDEFGHIJKLMNOPQRSTUVWXYZ');
38 END;
39
40 FUNCTION get_party_rec (
41 p_party_id IN NUMBER,
42 p_party_type IN VARCHAR2
43 ) RETURN HZ_PARTY_SEARCH.party_search_rec_type IS
44 CURSOR org IS
45 SELECT o.ANALYSIS_FY
46 ,o.AVG_HIGH_CREDIT
47 ,o.BEST_TIME_CONTACT_BEGIN
48 ,o.BEST_TIME_CONTACT_END
49 ,o.BRANCH_FLAG
50 ,o.BUSINESS_SCOPE
51 ,o.CEO_NAME
52 ,o.CEO_TITLE
53 ,o.CONG_DIST_CODE
54 ,o.CONTENT_SOURCE_NUMBER
55 ,o.CONTENT_SOURCE_TYPE
56 ,o.CONTROL_YR
57 ,o.CORPORATION_CLASS
58 ,o.CREDIT_SCORE
59 ,o.CREDIT_SCORE_AGE
60 ,o.CREDIT_SCORE_CLASS
61 ,o.CREDIT_SCORE_COMMENTARY
62 ,o.CREDIT_SCORE_COMMENTARY10
63 ,o.CREDIT_SCORE_COMMENTARY2
64 ,o.CREDIT_SCORE_COMMENTARY3
65 ,o.CREDIT_SCORE_COMMENTARY4
66 ,o.CREDIT_SCORE_COMMENTARY5
67 ,o.CREDIT_SCORE_COMMENTARY6
68 ,o.CREDIT_SCORE_COMMENTARY7
69 ,o.CREDIT_SCORE_COMMENTARY8
70 ,o.CREDIT_SCORE_COMMENTARY9
71 ,o.CREDIT_SCORE_DATE
72 ,o.CREDIT_SCORE_INCD_DEFAULT
73 ,o.CREDIT_SCORE_NATL_PERCENTILE
74 ,o.CURR_FY_POTENTIAL_REVENUE
75 ,o.DB_RATING
76 ,o.DEBARMENTS_COUNT
77 ,o.DEBARMENTS_DATE
78 ,o.DEBARMENT_IND
79 ,o.DISADV_8A_IND
80 ,o.DUNS_NUMBER_C
81 ,o.EMPLOYEES_TOTAL
82 ,o.EMP_AT_PRIMARY_ADR
86 ,o.ENQUIRY_DUNS
83 ,o.EMP_AT_PRIMARY_ADR_EST_IND
84 ,o.EMP_AT_PRIMARY_ADR_MIN_IND
85 ,o.EMP_AT_PRIMARY_ADR_TEXT
87 ,o.EXPORT_IND
88 ,o.FAILURE_SCORE
89 ,o.FAILURE_SCORE_AGE
90 ,o.FAILURE_SCORE_CLASS
91 ,o.FAILURE_SCORE_COMMENTARY
92 ,o.FAILURE_SCORE_COMMENTARY10
93 ,o.FAILURE_SCORE_COMMENTARY2
94 ,o.FAILURE_SCORE_COMMENTARY3
95 ,o.FAILURE_SCORE_COMMENTARY4
96 ,o.FAILURE_SCORE_COMMENTARY5
97 ,o.FAILURE_SCORE_COMMENTARY6
98 ,o.FAILURE_SCORE_COMMENTARY7
99 ,o.FAILURE_SCORE_COMMENTARY8
100 ,o.FAILURE_SCORE_COMMENTARY9
101 ,o.FAILURE_SCORE_DATE
102 ,o.FAILURE_SCORE_INCD_DEFAULT
103 ,o.FAILURE_SCORE_OVERRIDE_CODE
104 ,o.FISCAL_YEAREND_MONTH
105 ,o.GLOBAL_FAILURE_SCORE
106 ,o.GSA_INDICATOR_FLAG
107 ,o.HIGH_CREDIT
108 ,o.HQ_BRANCH_IND
109 ,o.IMPORT_IND
110 ,o.INCORP_YEAR
111 ,o.INTERNAL_FLAG
112 ,o.JGZZ_FISCAL_CODE
113 ,o.LABOR_SURPLUS_IND
114 ,o.LEGAL_STATUS
115 ,o.LINE_OF_BUSINESS
116 ,o.LOCAL_ACTIVITY_CODE
117 ,o.LOCAL_ACTIVITY_CODE_TYPE
118 ,o.LOCAL_BUS_IDENTIFIER
119 ,o.LOCAL_BUS_IDEN_TYPE
120 ,o.MAXIMUM_CREDIT_CURRENCY_CODE
121 ,o.MAXIMUM_CREDIT_RECOMMENDATION
122 ,o.MINORITY_OWNED_IND
123 ,o.MINORITY_OWNED_TYPE
124 ,o.NEXT_FY_POTENTIAL_REVENUE
125 ,o.OOB_IND
126 ,o.ORGANIZATION_NAME
127 ,o.ORGANIZATION_NAME_PHONETIC
128 ,o.ORGANIZATION_TYPE
129 ,o.PARENT_SUB_IND
130 ,o.PAYDEX_NORM
131 ,o.PAYDEX_SCORE
132 ,o.PAYDEX_THREE_MONTHS_AGO
133 ,o.PREF_FUNCTIONAL_CURRENCY
134 ,o.PRINCIPAL_NAME
135 ,o.PRINCIPAL_TITLE
136 ,o.PUBLIC_PRIVATE_OWNERSHIP_FLAG
137 ,o.REGISTRATION_TYPE
138 ,o.RENT_OWN_IND
139 ,o.SIC_CODE
140 ,o.SIC_CODE_TYPE
141 ,o.SMALL_BUS_IND
142 ,o.TAX_NAME
143 ,o.TAX_REFERENCE
144 ,o.TOTAL_EMPLOYEES_TEXT
145 ,o.TOTAL_EMP_EST_IND
146 ,o.TOTAL_EMP_MIN_IND
147 ,o.TOTAL_EMPLOYEES_IND
148 ,o.TOTAL_PAYMENTS
149 ,o.WOMAN_OWNED_IND
150 ,o.YEAR_ESTABLISHED
151 ,p.CATEGORY_CODE
152 ,p.COMPETITOR_FLAG
153 ,p.DO_NOT_MAIL_FLAG
154 ,p.GROUP_TYPE
155 ,p.LANGUAGE_NAME
156 ,p.PARTY_NAME
157 ,p.PARTY_NUMBER
158 ,p.PARTY_TYPE
159 ,p.REFERENCE_USE_FLAG
160 ,p.SALUTATION
161 ,nvl(p.STATUS,'A')
162 ,p.THIRD_PARTY_FLAG
163 ,p.VALIDATED_FLAG
164 ,o.EFFECTIVE_START_DATE
165 ,o.EFFECTIVE_END_DATE
166 ,p.KNOWN_AS
167 ,p.KNOWN_AS2
168 ,p.KNOWN_AS3
169 ,p.KNOWN_AS4
170 ,p.KNOWN_AS5
171 FROM HZ_PARTIES p, HZ_ORGANIZATION_PROFILES o
172 WHERE p.party_id = p_party_id
173 AND o.effective_end_date is NULL
174 AND p.party_id = o.party_id;
175
176 CURSOR pers IS
177 SELECT p.CATEGORY_CODE
178 ,p.COMPETITOR_FLAG
179 ,p.DO_NOT_MAIL_FLAG
180 ,p.GROUP_TYPE
181 ,p.LANGUAGE_NAME
182 ,p.PARTY_NAME
183 ,p.PARTY_NUMBER
184 ,p.PARTY_TYPE
185 ,p.REFERENCE_USE_FLAG
186 ,p.SALUTATION
187 ,nvl(p.STATUS,'A')
188 ,p.THIRD_PARTY_FLAG
189 ,p.VALIDATED_FLAG
190 ,pe.DATE_OF_BIRTH
191 ,pe.DATE_OF_DEATH
192 ,pe.DECLARED_ETHNICITY
193 ,pe.GENDER
194 ,pe.HEAD_OF_HOUSEHOLD_FLAG
195 ,pe.HOUSEHOLD_INCOME
196 ,pe.HOUSEHOLD_SIZE
197 ,pe.LAST_KNOWN_GPS
198 ,pe.MARITAL_STATUS
199 ,pe.MARITAL_STATUS_EFFECTIVE_DATE
200 ,pe.MIDDLE_NAME_PHONETIC
201 ,pe.PERSONAL_INCOME
202 ,pe.PERSON_ACADEMIC_TITLE
203 ,pe.PERSON_FIRST_NAME
204 ,pe.PERSON_FIRST_NAME_PHONETIC
205 ,pe.PERSON_IDENTIFIER
206 ,pe.PERSON_IDEN_TYPE
207 ,pe.PERSON_INITIALS
208 ,pe.PERSON_LAST_NAME
209 ,pe.PERSON_LAST_NAME_PHONETIC
210 ,pe.PERSON_MIDDLE_NAME
211 ,pe.PERSON_NAME
212 ,pe.PERSON_NAME_PHONETIC
213 ,pe.PERSON_NAME_SUFFIX
214 ,pe.PERSON_PREVIOUS_LAST_NAME
215 ,pe.PERSON_PRE_NAME_ADJUNCT
216 ,pe.PERSON_TITLE
217 ,pe.PLACE_OF_BIRTH
218 ,pe.BEST_TIME_CONTACT_BEGIN
219 ,pe.BEST_TIME_CONTACT_END
220 ,pe.CONTENT_SOURCE_NUMBER
221 ,pe.CONTENT_SOURCE_TYPE
222 ,pe.INTERNAL_FLAG
223 ,pe.JGZZ_FISCAL_CODE
224 ,pe.RENT_OWN_IND
225 ,pe.TAX_NAME
226 ,pe.TAX_REFERENCE
227 ,pe.EFFECTIVE_START_DATE
228 ,pe.EFFECTIVE_END_DATE
229 ,p.KNOWN_AS
230 ,p.KNOWN_AS2
231 ,p.KNOWN_AS3
232 ,p.KNOWN_AS4
233 ,p.KNOWN_AS5
234 FROM HZ_PARTIES p, HZ_PERSON_PROFILES pe
235 WHERE p.party_id = p_party_id
236 AND pe.effective_end_date is NULL
237 AND p.party_id = pe.party_id;
238 CURSOR custom_attribs IS
239 SELECT attribute_name, custom_attribute_procedure
240 FROM HZ_TRANS_ATTRIBUTES_VL
241 WHERE entity_name='PARTY'
242 AND (source_table = 'CUSTOM'
243 OR custom_attribute_procedure is not null);
244
245 l_attr_name VARCHAR2(255);
246
247 l_proc_name VARCHAR2(255);
248 plsql_block VARCHAR2(32000);
252
249 l_val VARCHAR2(2000);
250
251 l_return_status VARCHAR2(1);
253 l_party_search_rec HZ_PARTY_SEARCH.PARTY_SEARCH_REC_TYPE;
254 NUM NUMBER;
255 c NUMBER;
256
257 l_sql VARCHAR2(255);
258
259 BEGIN
260 l_party_search_rec.party_type := p_party_type;
261 IF p_party_type = 'ORGANIZATION' THEN
262 OPEN org;
263 FETCH org INTO l_party_search_rec.ANALYSIS_FY
264 ,l_party_search_rec.AVG_HIGH_CREDIT
265 ,l_party_search_rec.BEST_TIME_CONTACT_BEGIN
266 ,l_party_search_rec.BEST_TIME_CONTACT_END
267 ,l_party_search_rec.BRANCH_FLAG
268 ,l_party_search_rec.BUSINESS_SCOPE
269 ,l_party_search_rec.CEO_NAME
270 ,l_party_search_rec.CEO_TITLE
271 ,l_party_search_rec.CONG_DIST_CODE
272 ,l_party_search_rec.CONTENT_SOURCE_NUMBER
273 ,l_party_search_rec.CONTENT_SOURCE_TYPE
274 ,l_party_search_rec.CONTROL_YR
275 ,l_party_search_rec.CORPORATION_CLASS
276 ,l_party_search_rec.CREDIT_SCORE
277 ,l_party_search_rec.CREDIT_SCORE_AGE
278 ,l_party_search_rec.CREDIT_SCORE_CLASS
279 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY
280 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY10
281 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY2
282 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY3
283 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY4
284 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY5
285 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY6
286 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY7
287 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY8
288 ,l_party_search_rec.CREDIT_SCORE_COMMENTARY9
289 ,l_party_search_rec.CREDIT_SCORE_DATE
290 ,l_party_search_rec.CREDIT_SCORE_INCD_DEFAULT
291 ,l_party_search_rec.CREDIT_SCORE_NATL_PERCENTILE
292 ,l_party_search_rec.CURR_FY_POTENTIAL_REVENUE
293 ,l_party_search_rec.DB_RATING
294 ,l_party_search_rec.DEBARMENTS_COUNT
295 ,l_party_search_rec.DEBARMENTS_DATE
296 ,l_party_search_rec.DEBARMENT_IND
297 ,l_party_search_rec.DISADV_8A_IND
298 ,l_party_search_rec.DUNS_NUMBER_C
299 ,l_party_search_rec.EMPLOYEES_TOTAL
300 ,l_party_search_rec.EMP_AT_PRIMARY_ADR
301 ,l_party_search_rec.EMP_AT_PRIMARY_ADR_EST_IND
302 ,l_party_search_rec.EMP_AT_PRIMARY_ADR_MIN_IND
303 ,l_party_search_rec.EMP_AT_PRIMARY_ADR_TEXT
304 ,l_party_search_rec.ENQUIRY_DUNS
305 ,l_party_search_rec.EXPORT_IND
306 ,l_party_search_rec.FAILURE_SCORE
307 ,l_party_search_rec.FAILURE_SCORE_AGE
308 ,l_party_search_rec.FAILURE_SCORE_CLASS
309 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY
310 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY10
311 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY2
312 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY3
313 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY4
314 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY5
315 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY6
316 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY7
317 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY8
318 ,l_party_search_rec.FAILURE_SCORE_COMMENTARY9
319 ,l_party_search_rec.FAILURE_SCORE_DATE
320 ,l_party_search_rec.FAILURE_SCORE_INCD_DEFAULT
321 ,l_party_search_rec.FAILURE_SCORE_OVERRIDE_CODE
322 ,l_party_search_rec.FISCAL_YEAREND_MONTH
323 ,l_party_search_rec.GLOBAL_FAILURE_SCORE
324 ,l_party_search_rec.GSA_INDICATOR_FLAG
325 ,l_party_search_rec.HIGH_CREDIT
326 ,l_party_search_rec.HQ_BRANCH_IND
327 ,l_party_search_rec.IMPORT_IND
328 ,l_party_search_rec.INCORP_YEAR
329 ,l_party_search_rec.INTERNAL_FLAG
330 ,l_party_search_rec.JGZZ_FISCAL_CODE
331 ,l_party_search_rec.LABOR_SURPLUS_IND
332 ,l_party_search_rec.LEGAL_STATUS
333 ,l_party_search_rec.LINE_OF_BUSINESS
334 ,l_party_search_rec.LOCAL_ACTIVITY_CODE
335 ,l_party_search_rec.LOCAL_ACTIVITY_CODE_TYPE
336 ,l_party_search_rec.LOCAL_BUS_IDENTIFIER
337 ,l_party_search_rec.LOCAL_BUS_IDEN_TYPE
338 ,l_party_search_rec.MAXIMUM_CREDIT_CURRENCY_CODE
339 ,l_party_search_rec.MAXIMUM_CREDIT_RECOMMENDATION
340 ,l_party_search_rec.MINORITY_OWNED_IND
341 ,l_party_search_rec.MINORITY_OWNED_TYPE
342 ,l_party_search_rec.NEXT_FY_POTENTIAL_REVENUE
343 ,l_party_search_rec.OOB_IND
344 ,l_party_search_rec.ORGANIZATION_NAME
345 ,l_party_search_rec.ORGANIZATION_NAME_PHONETIC
346 ,l_party_search_rec.ORGANIZATION_TYPE
347 ,l_party_search_rec.PARENT_SUB_IND
348 ,l_party_search_rec.PAYDEX_NORM
349 ,l_party_search_rec.PAYDEX_SCORE
350 ,l_party_search_rec.PAYDEX_THREE_MONTHS_AGO
351 ,l_party_search_rec.PREF_FUNCTIONAL_CURRENCY
352 ,l_party_search_rec.PRINCIPAL_NAME
356 ,l_party_search_rec.RENT_OWN_IND
353 ,l_party_search_rec.PRINCIPAL_TITLE
354 ,l_party_search_rec.PUBLIC_PRIVATE_OWNERSHIP_FLAG
355 ,l_party_search_rec.REGISTRATION_TYPE
357 ,l_party_search_rec.SIC_CODE
358 ,l_party_search_rec.SIC_CODE_TYPE
359 ,l_party_search_rec.SMALL_BUS_IND
360 ,l_party_search_rec.TAX_NAME
361 ,l_party_search_rec.TAX_REFERENCE
362 ,l_party_search_rec.TOTAL_EMPLOYEES_TEXT
363 ,l_party_search_rec.TOTAL_EMP_EST_IND
364 ,l_party_search_rec.TOTAL_EMP_MIN_IND
365 ,l_party_search_rec.TOTAL_EMPLOYEES_IND
366 ,l_party_search_rec.TOTAL_PAYMENTS
367 ,l_party_search_rec.WOMAN_OWNED_IND
368 ,l_party_search_rec.YEAR_ESTABLISHED
369 ,l_party_search_rec.CATEGORY_CODE
370 ,l_party_search_rec.COMPETITOR_FLAG
371 ,l_party_search_rec.DO_NOT_MAIL_FLAG
372 ,l_party_search_rec.GROUP_TYPE
373 ,l_party_search_rec.LANGUAGE_NAME
374 ,l_party_search_rec.PARTY_NAME
375 ,l_party_search_rec.PARTY_NUMBER
376 ,l_party_search_rec.PARTY_TYPE
377 ,l_party_search_rec.REFERENCE_USE_FLAG
378 ,l_party_search_rec.SALUTATION
379 ,l_party_search_rec.STATUS
380 ,l_party_search_rec.THIRD_PARTY_FLAG
381 ,l_party_search_rec.VALIDATED_FLAG
382 ,l_party_search_rec.EFFECTIVE_START_DATE
383 ,l_party_search_rec.EFFECTIVE_END_DATE
384 ,l_party_search_rec.KNOWN_AS
385 ,l_party_search_rec.KNOWN_AS2
386 ,l_party_search_rec.KNOWN_AS3
387 ,l_party_search_rec.KNOWN_AS4
388 ,l_party_search_rec.KNOWN_AS5;
389 CLOSE org;
390 ELSIF p_party_type = 'PERSON' THEN
391 OPEN pers;
392 FETCH pers INTO l_party_search_rec.CATEGORY_CODE
393 ,l_party_search_rec.COMPETITOR_FLAG
394 ,l_party_search_rec.DO_NOT_MAIL_FLAG
395 ,l_party_search_rec.GROUP_TYPE
396 ,l_party_search_rec.LANGUAGE_NAME
397 ,l_party_search_rec.PARTY_NAME
398 ,l_party_search_rec.PARTY_NUMBER
399 ,l_party_search_rec.PARTY_TYPE
400 ,l_party_search_rec.REFERENCE_USE_FLAG
401 ,l_party_search_rec.SALUTATION
402 ,l_party_search_rec.STATUS
403 ,l_party_search_rec.THIRD_PARTY_FLAG
404 ,l_party_search_rec.VALIDATED_FLAG
405 ,l_party_search_rec.DATE_OF_BIRTH
406 ,l_party_search_rec.DATE_OF_DEATH
407 ,l_party_search_rec.DECLARED_ETHNICITY
408 ,l_party_search_rec.GENDER
409 ,l_party_search_rec.HEAD_OF_HOUSEHOLD_FLAG
410 ,l_party_search_rec.HOUSEHOLD_INCOME
411 ,l_party_search_rec.HOUSEHOLD_SIZE
412 ,l_party_search_rec.LAST_KNOWN_GPS
413 ,l_party_search_rec.MARITAL_STATUS
414 ,l_party_search_rec.MARITAL_STATUS_EFFECTIVE_DATE
415 ,l_party_search_rec.MIDDLE_NAME_PHONETIC
416 ,l_party_search_rec.PERSONAL_INCOME
417 ,l_party_search_rec.PERSON_ACADEMIC_TITLE
418 ,l_party_search_rec.PERSON_FIRST_NAME
419 ,l_party_search_rec.PERSON_FIRST_NAME_PHONETIC
420 ,l_party_search_rec.PERSON_IDENTIFIER
421 ,l_party_search_rec.PERSON_IDEN_TYPE
422 ,l_party_search_rec.PERSON_INITIALS
423 ,l_party_search_rec.PERSON_LAST_NAME
424 ,l_party_search_rec.PERSON_LAST_NAME_PHONETIC
425 ,l_party_search_rec.PERSON_MIDDLE_NAME
426 ,l_party_search_rec.PERSON_NAME
427 ,l_party_search_rec.PERSON_NAME_PHONETIC
428 ,l_party_search_rec.PERSON_NAME_SUFFIX
429 ,l_party_search_rec.PERSON_PREVIOUS_LAST_NAME
430 ,l_party_search_rec.PERSON_PRE_NAME_ADJUNCT
431 ,l_party_search_rec.PERSON_TITLE
432 ,l_party_search_rec.PLACE_OF_BIRTH
433 ,l_party_search_rec.BEST_TIME_CONTACT_BEGIN
434 ,l_party_search_rec.BEST_TIME_CONTACT_END
435 ,l_party_search_rec.CONTENT_SOURCE_NUMBER
436 ,l_party_search_rec.CONTENT_SOURCE_TYPE
437 ,l_party_search_rec.INTERNAL_FLAG
438 ,l_party_search_rec.JGZZ_FISCAL_CODE
439 ,l_party_search_rec.RENT_OWN_IND
440 ,l_party_search_rec.TAX_NAME
441 ,l_party_search_rec.TAX_REFERENCE
442 ,l_party_search_rec.EFFECTIVE_START_DATE
443 ,l_party_search_rec.EFFECTIVE_END_DATE
444 ,l_party_search_rec.KNOWN_AS
445 ,l_party_search_rec.KNOWN_AS2
446 ,l_party_search_rec.KNOWN_AS3
447 ,l_party_search_rec.KNOWN_AS4
448 ,l_party_search_rec.KNOWN_AS5;
449 CLOSE pers;
450 END IF;
451
452 IF g_party_custom_queried = 'N' THEN
453 g_party_custom_queried := 'Y';
454
455 NUM := 1;
456 OPEN custom_attribs;
457 LOOP
458
459 FETCH custom_attribs INTO g_party_custom_attrs(NUM), g_party_custom_procs(NUM);
460 EXIT WHEN custom_attribs%NOTFOUND;
461
462 c := dbms_sql.open_cursor;
466 g_party_custom_valid(NUM) := 'Y';
463 l_sql := 'select '|| g_party_custom_procs(NUM) || '(:record_id, :entity_name, :attribute_name) from dual';
464 BEGIN
465 dbms_sql.parse(c,l_sql,2);
467 EXCEPTION
468 WHEN OTHERS THEN
469 g_party_custom_valid(NUM) := 'N';
470 FND_FILE.put_line(fnd_file.log,'Error parsing custom attribute procedure ' || g_party_custom_procs(NUM) ||
471 ' for attribute PARTY.'||g_party_custom_attrs(NUM));
472 END;
473 dbms_sql.close_cursor(c);
474
475 NUM := NUM+1;
476 END LOOP;
477 CLOSE custom_attribs;
478 END IF;
479
480 l_return_status:=FND_API.G_RET_STS_SUCCESS;
481 FOR I IN 1..g_party_custom_procs.COUNT LOOP
482 l_proc_name := g_party_custom_procs(I);
483 l_attr_name := g_party_custom_attrs(I);
484
485 IF g_party_custom_valid(I) = 'Y' THEN
486 BEGIN
487
488 IF l_proc_name = 'HZ_PARTY_ACQUIRE.get_known_as' THEN
489 l_val := HZ_PARTY_ACQUIRE.get_known_as(p_party_id,'PARTY', l_attr_name, 'STAGE');
490 ELSIF l_proc_name = 'HZ_PARTY_ACQUIRE.get_account_info' THEN
491 l_val := HZ_PARTY_ACQUIRE.get_account_info(p_party_id,'PARTY', l_attr_name, 'STAGE');
492 ELSE
493 -- Create a dynamic SQL block to execute the merge procedure
494 plsql_block := 'BEGIN '||
495 ' :retval := '||l_proc_name||'(:record_id, :entity_name, :attribute_name);'||
496 'END;';
497
498 -- Execute the dynamic PLSQL block
499 EXECUTE IMMEDIATE plsql_block USING
500 OUT l_val, p_party_id, 'PARTY', l_attr_name;
501 END IF;
502
503 IF l_attr_name = 'ALL_ACCOUNT_NAMES' THEN
504 l_party_search_rec.ALL_ACCOUNT_NAMES := l_val;
505 ELSIF l_attr_name = 'ALL_ACCOUNT_NUMBERS' THEN
506 l_party_search_rec.ALL_ACCOUNT_NUMBERS := l_val;
507 ELSIF l_attr_name = 'PARTY_ALL_NAMES' THEN
508 l_party_search_rec.PARTY_ALL_NAMES := l_val;
509 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE1' THEN
510 l_party_search_rec.CUSTOM_ATTRIBUTE1 := l_val;
511 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE2' THEN
512 l_party_search_rec.CUSTOM_ATTRIBUTE2 := l_val;
513 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE3' THEN
514 l_party_search_rec.CUSTOM_ATTRIBUTE3 := l_val;
515 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE4' THEN
516 l_party_search_rec.CUSTOM_ATTRIBUTE4 := l_val;
517 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE5' THEN
518 l_party_search_rec.CUSTOM_ATTRIBUTE5 := l_val;
519 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE6' THEN
520 l_party_search_rec.CUSTOM_ATTRIBUTE6 := l_val;
521 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE7' THEN
522 l_party_search_rec.CUSTOM_ATTRIBUTE7 := l_val;
523 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE8' THEN
524 l_party_search_rec.CUSTOM_ATTRIBUTE8 := l_val;
525 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE9' THEN
526 l_party_search_rec.CUSTOM_ATTRIBUTE9 := l_val;
527 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE10' THEN
528 l_party_search_rec.CUSTOM_ATTRIBUTE10:= l_val;
529 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE11' THEN
530 l_party_search_rec.CUSTOM_ATTRIBUTE11 := l_val;
531 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE12' THEN
532 l_party_search_rec.CUSTOM_ATTRIBUTE12 := l_val;
533 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE13' THEN
534 l_party_search_rec.CUSTOM_ATTRIBUTE13 := l_val;
535 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE14' THEN
536 l_party_search_rec.CUSTOM_ATTRIBUTE14 := l_val;
537 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE15' THEN
538 l_party_search_rec.CUSTOM_ATTRIBUTE15 := l_val;
539 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE16' THEN
540 l_party_search_rec.CUSTOM_ATTRIBUTE16 := l_val;
541 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE17' THEN
542 l_party_search_rec.CUSTOM_ATTRIBUTE17 := l_val;
543 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE18' THEN
544 l_party_search_rec.CUSTOM_ATTRIBUTE18 := l_val;
545 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE19' THEN
546 l_party_search_rec.CUSTOM_ATTRIBUTE19 := l_val;
547 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE20' THEN
548 l_party_search_rec.CUSTOM_ATTRIBUTE20:= l_val;
549 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE21' THEN
550 l_party_search_rec.CUSTOM_ATTRIBUTE21 := l_val;
551 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE22' THEN
552 l_party_search_rec.CUSTOM_ATTRIBUTE22 := l_val;
553 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE23' THEN
554 l_party_search_rec.CUSTOM_ATTRIBUTE23 := l_val;
555 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE24' THEN
556 l_party_search_rec.CUSTOM_ATTRIBUTE24 := l_val;
557 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE25' THEN
558 l_party_search_rec.CUSTOM_ATTRIBUTE25 := l_val;
559 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE26' THEN
560 l_party_search_rec.CUSTOM_ATTRIBUTE26 := l_val;
561 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE27' THEN
562 l_party_search_rec.CUSTOM_ATTRIBUTE27 := l_val;
563 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE28' THEN
564 l_party_search_rec.CUSTOM_ATTRIBUTE28 := l_val;
565 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE29' THEN
566 l_party_search_rec.CUSTOM_ATTRIBUTE29 := l_val;
567 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE30' THEN
568 l_party_search_rec.CUSTOM_ATTRIBUTE30:= l_val;
569 END IF;
570 EXCEPTION
571 WHEN OTHERS THEN
572 FND_FILE.put_line(FND_FILE.log,'Error executing custom procedure for attribute ' || l_attr_name || ' (Party ID: ' || p_party_id || '). Continuing ... ');
576 FND_MESSAGE.SET_TOKEN('ENTITY' ,'PARTY');
573 FND_FILE.put_line(FND_FILE.log,SQLERRM);
574 /*
575 FND_MESSAGE.SET_NAME('AR', 'HZ_CUSTOM_PROC_ERROR');
577 FND_MESSAGE.SET_TOKEN('ATTRIBUTE' ,l_attr_name);
578 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
579 FND_MSG_PUB.ADD;
580 RAISE FND_API.G_EXC_ERROR;
581 */
582 END;
583 END IF;
584 END LOOP;
585
586 RETURN l_party_search_rec;
587 EXCEPTION
588 WHEN FND_API.G_EXC_ERROR THEN
589 RAISE FND_API.G_EXC_ERROR;
590 WHEN OTHERS THEN
591 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_SQL_ERROR');
592 FND_MESSAGE.SET_TOKEN('PROC' ,'get_party_rec');
593 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
594 FND_MSG_PUB.ADD;
595 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
596 END;
597
598
599 FUNCTION get_party_site_rec (
600 p_party_site_id IN NUMBER
601 ) RETURN HZ_PARTY_SEARCH.party_site_search_rec_type IS
602
603 CURSOR c_party_site IS
604 SELECT l.ADDRESS1
605 ,l.ADDRESS2
606 ,l.ADDRESS3
607 ,l.ADDRESS4
608 ,l.ADDRESS_EFFECTIVE_DATE
609 ,l.ADDRESS_EXPIRATION_DATE
610 ,l.ADDRESS_LINES_PHONETIC
611 ,l.CITY
612 ,l.CLLI_CODE
613 ,l.CONTENT_SOURCE_TYPE
614 ,l.COUNTRY
615 ,l.COUNTY
616 ,l.FLOOR
617 ,l.HOUSE_NUMBER
618 ,l.LANGUAGE
619 ,l.POSITION
620 ,l.POSTAL_CODE
621 ,l.POSTAL_PLUS4_CODE
622 ,l.PO_BOX_NUMBER
623 ,l.PROVINCE
624 ,l.SALES_TAX_GEOCODE
625 ,l.SALES_TAX_INSIDE_CITY_LIMITS
626 ,l.STATE
627 ,l.STREET
628 ,l.STREET_NUMBER
629 ,l.STREET_SUFFIX
630 ,l.SUITE
631 ,l.TRAILING_DIRECTORY_CODE
632 ,l.VALIDATED_FLAG
633 ,ps.IDENTIFYING_ADDRESS_FLAG
634 ,ps.MAILSTOP
635 ,ps.PARTY_SITE_NAME
636 ,ps.PARTY_SITE_NUMBER
637 FROM HZ_PARTY_SITES ps, HZ_LOCATIONS l
638 WHERE ps.party_site_id = p_party_site_id
639 AND ps.location_id = l.location_id
640 AND (ps.status is null OR ps.status = 'A' or ps.status = 'I');
641
642 CURSOR custom_attribs IS
643 SELECT attribute_name, custom_attribute_procedure
644 FROM HZ_TRANS_ATTRIBUTES_VL
645 WHERE entity_name='PARTY_SITES'
646 AND (source_table = 'CUSTOM'
647 OR custom_attribute_procedure is not null);
648
649 l_attr_name VARCHAR2(255);
650 l_proc_name VARCHAR2(255);
651 plsql_block VARCHAR2(32000);
652 l_val VARCHAR2(2000);
653
654 l_party_site_search_rec HZ_PARTY_SEARCH.PARTY_SITE_SEARCH_REC_TYPE;
655 l_return_status VARCHAR2(1);
656
657 NUM NUMBER;
658 c NUMBER;
659
660 l_sql VARCHAR2(255);
661
662 BEGIN
663 l_return_status:=FND_API.G_RET_STS_SUCCESS;
664 OPEN c_party_site;
665 FETCH c_party_site INTO l_party_site_search_rec.ADDRESS1
666 ,l_party_site_search_rec.ADDRESS2
667 ,l_party_site_search_rec.ADDRESS3
668 ,l_party_site_search_rec.ADDRESS4
669 ,l_party_site_search_rec.ADDRESS_EFFECTIVE_DATE
670 ,l_party_site_search_rec.ADDRESS_EXPIRATION_DATE
671 ,l_party_site_search_rec.ADDRESS_LINES_PHONETIC
672 ,l_party_site_search_rec.CITY
673 ,l_party_site_search_rec.CLLI_CODE
674 ,l_party_site_search_rec.CONTENT_SOURCE_TYPE
675 ,l_party_site_search_rec.COUNTRY
676 ,l_party_site_search_rec.COUNTY
677 ,l_party_site_search_rec.FLOOR
678 ,l_party_site_search_rec.HOUSE_NUMBER
679 ,l_party_site_search_rec.LANGUAGE
680 ,l_party_site_search_rec.POSITION
681 ,l_party_site_search_rec.POSTAL_CODE
682 ,l_party_site_search_rec.POSTAL_PLUS4_CODE
683 ,l_party_site_search_rec.PO_BOX_NUMBER
684 ,l_party_site_search_rec.PROVINCE
685 ,l_party_site_search_rec.SALES_TAX_GEOCODE
686 ,l_party_site_search_rec.SALES_TAX_INSIDE_CITY_LIMITS
687 ,l_party_site_search_rec.STATE
688 ,l_party_site_search_rec.STREET
689 ,l_party_site_search_rec.STREET_NUMBER
690 ,l_party_site_search_rec.STREET_SUFFIX
691 ,l_party_site_search_rec.SUITE
692 ,l_party_site_search_rec.TRAILING_DIRECTORY_CODE
693 ,l_party_site_search_rec.VALIDATED_FLAG
694 ,l_party_site_search_rec.IDENTIFYING_ADDRESS_FLAG
695 ,l_party_site_search_rec.MAILSTOP
696 ,l_party_site_search_rec.PARTY_SITE_NAME
697 ,l_party_site_search_rec.PARTY_SITE_NUMBER;
698
699 CLOSE c_party_site;
700
701
702 IF g_ps_custom_queried = 'N' THEN
703 g_ps_custom_queried := 'Y';
704
705 NUM := 1;
706 OPEN custom_attribs;
707 LOOP
708
709 FETCH custom_attribs INTO g_ps_custom_attrs(NUM), g_ps_custom_procs(NUM);
710 EXIT WHEN custom_attribs%NOTFOUND;
711
712 c := dbms_sql.open_cursor;
713 l_sql := 'select '|| g_ps_custom_procs(NUM) || '(:record_id, :entity_name, :attribute_name) from dual';
714 BEGIN
715 dbms_sql.parse(c,l_sql,2);
716 g_ps_custom_valid(NUM) := 'Y';
717 EXCEPTION
718 WHEN OTHERS THEN
719 g_ps_custom_valid(NUM) := 'N';
720 FND_FILE.put_line(fnd_file.log,'Error parsing custom attribute procedure ' || g_ps_custom_procs(NUM) ||
724
721 ' for attribute PARTY.'||g_ps_custom_attrs(NUM));
722 END;
723 dbms_sql.close_cursor(c);
725 NUM := NUM+1;
726 END LOOP;
727 CLOSE custom_attribs;
728 END IF;
729
730 l_return_status:=FND_API.G_RET_STS_SUCCESS;
731 FOR I IN 1..g_ps_custom_procs.COUNT LOOP
732 l_proc_name := g_ps_custom_procs(I);
733 l_attr_name := g_ps_custom_attrs(I);
734
735 IF g_ps_custom_valid(I) = 'Y' THEN
736
737 BEGIN
738 IF l_proc_name = 'HZ_PARTY_ACQUIRE.get_address' THEN
739 l_val := HZ_PARTY_ACQUIRE.get_address(p_party_site_id, 'PARTY_SITES', l_attr_name, 'STAGE');
740 ELSE
741 -- Create a dynamic SQL block to execute the merge procedure
742 plsql_block := 'BEGIN '||
743 ' :retval := '||l_proc_name||'(:record_id, :entity_name, :attribute_name);'||
744 'END;';
745
746 -- Execute the dynamic PLSQL block
747 EXECUTE IMMEDIATE plsql_block USING
748 OUT l_val, p_party_site_id, 'PARTY_SITES', l_attr_name;
749 END IF;
750
751 IF l_attr_name = 'ADDRESS' THEN
752 l_party_site_search_rec.ADDRESS := l_val;
753 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE1' THEN
754 l_party_site_search_rec.CUSTOM_ATTRIBUTE1 := l_val;
755 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE2' THEN
756 l_party_site_search_rec.CUSTOM_ATTRIBUTE2 := l_val;
757 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE3' THEN
758 l_party_site_search_rec.CUSTOM_ATTRIBUTE3 := l_val;
759 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE4' THEN
760 l_party_site_search_rec.CUSTOM_ATTRIBUTE4 := l_val;
761 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE5' THEN
762 l_party_site_search_rec.CUSTOM_ATTRIBUTE5 := l_val;
763 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE6' THEN
764 l_party_site_search_rec.CUSTOM_ATTRIBUTE6 := l_val;
765 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE7' THEN
766 l_party_site_search_rec.CUSTOM_ATTRIBUTE7 := l_val;
767 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE8' THEN
768 l_party_site_search_rec.CUSTOM_ATTRIBUTE8 := l_val;
769 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE9' THEN
770 l_party_site_search_rec.CUSTOM_ATTRIBUTE9 := l_val;
771 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE10' THEN
772 l_party_site_search_rec.CUSTOM_ATTRIBUTE10:= l_val;
773 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE11' THEN
774 l_party_site_search_rec.CUSTOM_ATTRIBUTE11 := l_val;
775 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE12' THEN
776 l_party_site_search_rec.CUSTOM_ATTRIBUTE12 := l_val;
777 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE13' THEN
778 l_party_site_search_rec.CUSTOM_ATTRIBUTE13 := l_val;
779 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE14' THEN
780 l_party_site_search_rec.CUSTOM_ATTRIBUTE14 := l_val;
781 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE15' THEN
782 l_party_site_search_rec.CUSTOM_ATTRIBUTE15 := l_val;
783 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE16' THEN
784 l_party_site_search_rec.CUSTOM_ATTRIBUTE16 := l_val;
785 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE17' THEN
786 l_party_site_search_rec.CUSTOM_ATTRIBUTE17 := l_val;
787 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE18' THEN
788 l_party_site_search_rec.CUSTOM_ATTRIBUTE18 := l_val;
789 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE19' THEN
790 l_party_site_search_rec.CUSTOM_ATTRIBUTE19 := l_val;
791 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE20' THEN
792 l_party_site_search_rec.CUSTOM_ATTRIBUTE20:= l_val;
793 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE21' THEN
794 l_party_site_search_rec.CUSTOM_ATTRIBUTE21 := l_val;
795 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE22' THEN
796 l_party_site_search_rec.CUSTOM_ATTRIBUTE22 := l_val;
797 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE23' THEN
798 l_party_site_search_rec.CUSTOM_ATTRIBUTE23 := l_val;
799 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE24' THEN
800 l_party_site_search_rec.CUSTOM_ATTRIBUTE24 := l_val;
801 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE25' THEN
802 l_party_site_search_rec.CUSTOM_ATTRIBUTE25 := l_val;
803 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE26' THEN
804 l_party_site_search_rec.CUSTOM_ATTRIBUTE26 := l_val;
805 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE27' THEN
806 l_party_site_search_rec.CUSTOM_ATTRIBUTE27 := l_val;
807 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE28' THEN
808 l_party_site_search_rec.CUSTOM_ATTRIBUTE28 := l_val;
809 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE29' THEN
810 l_party_site_search_rec.CUSTOM_ATTRIBUTE29 := l_val;
811 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE30' THEN
812 l_party_site_search_rec.CUSTOM_ATTRIBUTE30:= l_val;
813 END IF;
814 EXCEPTION
815 WHEN OTHERS THEN
816 FND_FILE.put_line(FND_FILE.log,'Error executing custom procedure for attribute ' || l_attr_name || ' (Party Site ID: ' || p_party_site_id || '). Continuing ... ');
817 FND_FILE.put_line(FND_FILE.log,SQLERRM);
818 /*
819 FND_MESSAGE.SET_NAME('AR', 'HZ_CUSTOM_PROC_ERROR');
820 FND_MESSAGE.SET_TOKEN('ENTITY' ,'PARTY_SITES');
821 FND_MESSAGE.SET_TOKEN('ATTRIBUTE' ,l_attr_name);
822 FND_MSG_PUB.ADD;
823 RAISE FND_API.G_EXC_ERROR;
824 */
825 END;
826 END IF;
827 END LOOP;
828
829 RETURN l_party_site_search_rec;
830 EXCEPTION
831 WHEN FND_API.G_EXC_ERROR THEN
832 RAISE FND_API.G_EXC_ERROR;
833 WHEN OTHERS THEN
834 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_SQL_ERROR');
835 FND_MESSAGE.SET_TOKEN('PROC' ,'get_party_site_rec');
836 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
837 FND_MSG_PUB.ADD;
838 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
842
839
840 END;
841
843 FUNCTION get_contact_rec (
844 p_org_contact_id IN NUMBER
845 ) RETURN HZ_PARTY_SEARCH.contact_search_rec_type IS
846
847 CURSOR c_org_contact IS
848 SELECT o.CONTACT_NUMBER
849 ,o.DECISION_MAKER_FLAG
850 ,o.JOB_TITLE
851 ,o.JOB_TITLE_CODE
852 ,o.MAIL_STOP
853 ,o.NATIVE_LANGUAGE
854 ,o.OTHER_LANGUAGE_1
855 ,o.OTHER_LANGUAGE_2
856 ,o.RANK
857 ,o.REFERENCE_USE_FLAG
858 ,o.TITLE
859 ,pr.RELATIONSHIP_TYPE
860 ,ps.BEST_TIME_CONTACT_BEGIN
861 ,ps.BEST_TIME_CONTACT_END
862 ,ps.DATE_OF_BIRTH
863 ,ps.DATE_OF_DEATH
864 ,ps.JGZZ_FISCAL_CODE
865 ,ps.KNOWN_AS
866 ,ps.PERSON_ACADEMIC_TITLE
867 ,ps.PERSON_FIRST_NAME
868 ,ps.PERSON_FIRST_NAME_PHONETIC
869 ,ps.PERSON_IDENTIFIER
870 ,ps.PERSON_IDEN_TYPE
871 ,ps.PERSON_INITIALS
872 ,ps.PERSON_LAST_NAME
873 ,ps.PERSON_LAST_NAME_PHONETIC
874 ,ps.PERSON_MIDDLE_NAME
875 ,ps.PERSON_NAME
876 ,ps.PERSON_NAME_PHONETIC
877 ,ps.PERSON_NAME_SUFFIX
878 ,ps.PERSON_PREVIOUS_LAST_NAME
879 ,ps.PERSON_TITLE
880 ,ps.PLACE_OF_BIRTH
881 ,ps.TAX_NAME
882 ,ps.TAX_REFERENCE
883 FROM HZ_ORG_CONTACTS o, HZ_RELATIONSHIPS pr, HZ_PERSON_PROFILES ps
884 WHERE o.party_relationship_id = pr.relationship_id
885 AND pr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
886 AND pr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
887 AND pr.DIRECTIONAL_FLAG = 'F'
888 AND pr.subject_id = ps.party_id
889 AND ps.effective_end_date IS NULL
890 AND o.org_contact_id = p_org_contact_id
891 AND (o.status is null OR o.status = 'A' or o.status = 'I')
892 AND (pr.status is null OR pr.status = 'A' or pr.status = 'I');
893
894 CURSOR custom_attribs IS
895 SELECT attribute_name, custom_attribute_procedure
896 FROM HZ_TRANS_ATTRIBUTES_VL
897 WHERE entity_name='CONTACTS'
898 AND (source_table = 'CUSTOM'
899 OR custom_attribute_procedure is not null);
900
901 l_attr_name VARCHAR2(255);
902 l_proc_name VARCHAR2(255);
903 plsql_block VARCHAR2(32000);
904 l_val VARCHAR2(2000);
905
906 l_contact_search_rec HZ_PARTY_SEARCH.contact_search_rec_type;
907 l_subject_id NUMBER;
908 l_return_status VARCHAR2(1);
909
910 NUM NUMBER;
911 c NUMBER;
912
913 l_sql VARCHAR2(255);
914
915 BEGIN
916 l_return_status:=FND_API.G_RET_STS_SUCCESS;
917 OPEN c_org_contact;
918 FETCH c_org_contact INTO l_contact_search_rec.CONTACT_NUMBER
919 ,l_contact_search_rec.DECISION_MAKER_FLAG
920 ,l_contact_search_rec.JOB_TITLE
921 ,l_contact_search_rec.JOB_TITLE_CODE
922 ,l_contact_search_rec.MAIL_STOP
923 ,l_contact_search_rec.NATIVE_LANGUAGE
924 ,l_contact_search_rec.OTHER_LANGUAGE_1
925 ,l_contact_search_rec.OTHER_LANGUAGE_2
926 ,l_contact_search_rec.RANK
927 ,l_contact_search_rec.REFERENCE_USE_FLAG
928 ,l_contact_search_rec.TITLE
929 ,l_contact_search_rec.RELATIONSHIP_TYPE
930 ,l_contact_search_rec.BEST_TIME_CONTACT_BEGIN
931 ,l_contact_search_rec.BEST_TIME_CONTACT_END
932 ,l_contact_search_rec.DATE_OF_BIRTH
933 ,l_contact_search_rec.DATE_OF_DEATH
934 ,l_contact_search_rec.JGZZ_FISCAL_CODE
935 ,l_contact_search_rec.KNOWN_AS
936 ,l_contact_search_rec.PERSON_ACADEMIC_TITLE
937 ,l_contact_search_rec.PERSON_FIRST_NAME
938 ,l_contact_search_rec.PERSON_FIRST_NAME_PHONETIC
939 ,l_contact_search_rec.PERSON_IDENTIFIER
940 ,l_contact_search_rec.PERSON_IDEN_TYPE
941 ,l_contact_search_rec.PERSON_INITIALS
942 ,l_contact_search_rec.PERSON_LAST_NAME
943 ,l_contact_search_rec.PERSON_LAST_NAME_PHONETIC
944 ,l_contact_search_rec.PERSON_MIDDLE_NAME
945 ,l_contact_search_rec.PERSON_NAME
946 ,l_contact_search_rec.PERSON_NAME_PHONETIC
947 ,l_contact_search_rec.PERSON_NAME_SUFFIX
948 ,l_contact_search_rec.PERSON_PREVIOUS_LAST_NAME
949 ,l_contact_search_rec.PERSON_TITLE
950 ,l_contact_search_rec.PLACE_OF_BIRTH
951 ,l_contact_search_rec.TAX_NAME
952 ,l_contact_search_rec.TAX_REFERENCE;
953 CLOSE c_org_contact;
954
955 IF g_cont_custom_queried = 'N' THEN
956 g_cont_custom_queried := 'Y';
957
958 NUM := 1;
959 OPEN custom_attribs;
960 LOOP
961
962 FETCH custom_attribs INTO g_cont_custom_attrs(NUM), g_cont_custom_procs(NUM);
963 EXIT WHEN custom_attribs%NOTFOUND;
964
965 c := dbms_sql.open_cursor;
966 l_sql := 'select '|| g_cont_custom_procs(NUM) || '(:record_id, :entity_name, :attribute_name) from dual';
967 BEGIN
968 dbms_sql.parse(c,l_sql,2);
969 g_cont_custom_valid(NUM) := 'Y';
970 EXCEPTION
971 WHEN OTHERS THEN
972 g_cont_custom_valid(NUM) := 'N';
973 FND_FILE.put_line(fnd_file.log,'Error parsing custom attribute procedure ' || g_cont_custom_procs(NUM) ||
974 ' for attribute PARTY.'||g_cont_custom_attrs(NUM));
975 END;
976 dbms_sql.close_cursor(c);
977
981 END IF;
978 NUM := NUM+1;
979 END LOOP;
980 CLOSE custom_attribs;
982
983 l_return_status:=FND_API.G_RET_STS_SUCCESS;
984 FOR I IN 1..g_cont_custom_procs.COUNT LOOP
985 l_proc_name := g_cont_custom_procs(I);
986 l_attr_name := g_cont_custom_attrs(I);
987
988 IF g_cont_custom_valid(I) = 'Y' THEN
989
990 BEGIN
991 IF l_proc_name='HZ_PARTY_ACQUIRE.get_contact_name' THEN
992 l_val := HZ_PARTY_ACQUIRE.get_contact_name(p_org_contact_id, 'CONTACTS', l_attr_name, 'STAGE');
993 ELSE
994 -- Create a dynamic SQL block to execute the merge procedure
995 plsql_block := 'BEGIN '||
996 ' :retval := '||l_proc_name||'(:record_id, :entity_name, :attribute_name);'||
997 'END;';
998
999 -- Execute the dynamic PLSQL block
1000 EXECUTE IMMEDIATE plsql_block USING
1001 OUT l_val, p_org_contact_id, 'CONTACTS', l_attr_name;
1002 END IF;
1003
1004 IF l_attr_name = 'CONTACT_NAME' THEN
1005 l_contact_search_rec.CONTACT_NAME := l_val;
1006 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE1' THEN
1007 l_contact_search_rec.CUSTOM_ATTRIBUTE1 := l_val;
1008 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE2' THEN
1009 l_contact_search_rec.CUSTOM_ATTRIBUTE2 := l_val;
1010 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE3' THEN
1011 l_contact_search_rec.CUSTOM_ATTRIBUTE3 := l_val;
1012 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE4' THEN
1013 l_contact_search_rec.CUSTOM_ATTRIBUTE4 := l_val;
1014 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE5' THEN
1015 l_contact_search_rec.CUSTOM_ATTRIBUTE5 := l_val;
1016 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE6' THEN
1017 l_contact_search_rec.CUSTOM_ATTRIBUTE6 := l_val;
1018 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE7' THEN
1019 l_contact_search_rec.CUSTOM_ATTRIBUTE7 := l_val;
1020 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE8' THEN
1021 l_contact_search_rec.CUSTOM_ATTRIBUTE8 := l_val;
1022 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE9' THEN
1023 l_contact_search_rec.CUSTOM_ATTRIBUTE9 := l_val;
1024 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE10' THEN
1025 l_contact_search_rec.CUSTOM_ATTRIBUTE10:= l_val;
1026 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE11' THEN
1027 l_contact_search_rec.CUSTOM_ATTRIBUTE11 := l_val;
1028 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE12' THEN
1029 l_contact_search_rec.CUSTOM_ATTRIBUTE12 := l_val;
1030 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE13' THEN
1031 l_contact_search_rec.CUSTOM_ATTRIBUTE13 := l_val;
1032 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE14' THEN
1033 l_contact_search_rec.CUSTOM_ATTRIBUTE14 := l_val;
1034 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE15' THEN
1035 l_contact_search_rec.CUSTOM_ATTRIBUTE15 := l_val;
1036 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE16' THEN
1037 l_contact_search_rec.CUSTOM_ATTRIBUTE16 := l_val;
1038 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE17' THEN
1039 l_contact_search_rec.CUSTOM_ATTRIBUTE17 := l_val;
1040 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE18' THEN
1041 l_contact_search_rec.CUSTOM_ATTRIBUTE18 := l_val;
1042 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE19' THEN
1043 l_contact_search_rec.CUSTOM_ATTRIBUTE19 := l_val;
1044 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE20' THEN
1045 l_contact_search_rec.CUSTOM_ATTRIBUTE20:= l_val;
1046 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE21' THEN
1047 l_contact_search_rec.CUSTOM_ATTRIBUTE21 := l_val;
1048 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE22' THEN
1049 l_contact_search_rec.CUSTOM_ATTRIBUTE22 := l_val;
1050 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE23' THEN
1051 l_contact_search_rec.CUSTOM_ATTRIBUTE23 := l_val;
1052 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE24' THEN
1053 l_contact_search_rec.CUSTOM_ATTRIBUTE24 := l_val;
1054 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE25' THEN
1055 l_contact_search_rec.CUSTOM_ATTRIBUTE25 := l_val;
1056 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE26' THEN
1057 l_contact_search_rec.CUSTOM_ATTRIBUTE26 := l_val;
1058 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE27' THEN
1059 l_contact_search_rec.CUSTOM_ATTRIBUTE27 := l_val;
1060 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE28' THEN
1061 l_contact_search_rec.CUSTOM_ATTRIBUTE28 := l_val;
1062 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE29' THEN
1063 l_contact_search_rec.CUSTOM_ATTRIBUTE29 := l_val;
1064 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE30' THEN
1065 l_contact_search_rec.CUSTOM_ATTRIBUTE30:= l_val;
1066 END IF;
1067 EXCEPTION
1068 WHEN OTHERS THEN
1069 FND_FILE.put_line(FND_FILE.log,'Error executing custom procedure for attribute ' || l_attr_name || ' (Org Contact ID: ' || p_org_contact_id || '). Continuing ... ');
1070 FND_FILE.put_line(FND_FILE.log,SQLERRM);
1071 /*
1072 FND_MESSAGE.SET_NAME('AR', 'HZ_CUSTOM_PROC_ERROR');
1073 FND_MESSAGE.SET_TOKEN('ENTITY' ,'CONTACT');
1074 FND_MESSAGE.SET_TOKEN('ATTRIBUTE' ,l_attr_name);
1075 FND_MSG_PUB.ADD;
1076 RAISE FND_API.G_EXC_ERROR;
1077 */
1078 END;
1079 END IF;
1080 END LOOP;
1081
1082 RETURN l_contact_search_rec;
1083 EXCEPTION
1084 WHEN FND_API.G_EXC_ERROR THEN
1085 RAISE FND_API.G_EXC_ERROR;
1086 WHEN OTHERS THEN
1087 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_SQL_ERROR');
1088 FND_MESSAGE.SET_TOKEN('PROC' ,'get_contact_Rec');
1089 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1090 FND_MSG_PUB.ADD;
1091 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1092
1093 END;
1094
1095
1096 FUNCTION get_contact_point_rec (
1097 p_contact_point_id IN NUMBER
1098 ) RETURN HZ_PARTY_SEARCH.contact_point_search_rec_type IS
1099
1103 ,EDI_ECE_TP_LOCATION_CODE
1100 CURSOR cpt IS
1101 SELECT CONTACT_POINT_TYPE
1102 ,CONTENT_SOURCE_TYPE
1104 ,EDI_ID_NUMBER
1105 ,EDI_PAYMENT_FORMAT
1106 ,EDI_PAYMENT_METHOD
1107 ,EDI_REMITTANCE_INSTRUCTION
1108 ,EDI_REMITTANCE_METHOD
1109 ,EDI_TP_HEADER_ID
1110 ,EDI_TRANSACTION_HANDLING
1111 ,EMAIL_ADDRESS
1112 ,EMAIL_FORMAT
1113 ,LAST_CONTACT_DT_TIME
1114 ,PHONE_AREA_CODE
1115 ,PHONE_CALLING_CALENDAR
1116 ,PHONE_COUNTRY_CODE
1117 ,PHONE_EXTENSION
1118 ,PHONE_LINE_TYPE
1119 ,PHONE_NUMBER
1120 ,PRIMARY_FLAG
1121 ,TELEPHONE_TYPE
1122 ,TELEX_NUMBER
1123 ,TIME_ZONE
1124 ,URL
1125 ,WEB_TYPE
1126 FROM HZ_CONTACT_POINTS
1127 WHERE contact_point_id = p_contact_point_id
1128 AND (status is null OR status = 'A' or status = 'I');
1129
1130 CURSOR custom_attribs IS
1131 SELECT attribute_name, custom_attribute_procedure
1132 FROM HZ_TRANS_ATTRIBUTES_VL
1133 WHERE entity_name='CONTACT_POINTS'
1134 AND (source_table = 'CUSTOM'
1135 OR custom_attribute_procedure IS NOT NULL);
1136
1137 l_attr_name VARCHAR2(255);
1138 l_proc_name VARCHAR2(255);
1139 plsql_block VARCHAR2(32000);
1140 l_val VARCHAR2(2000);
1141
1142 l_contact_pt_search_rec HZ_PARTY_SEARCH.contact_point_search_rec_type;
1143 l_return_status VARCHAR2(1);
1144
1145 NUM NUMBER;
1146 c NUMBER;
1147
1148 l_sql VARCHAR2(255);
1149
1150 BEGIN
1151 l_return_status:=FND_API.G_RET_STS_SUCCESS;
1152
1153 OPEN cpt;
1154 FETCH cpt INTO l_contact_pt_search_rec.CONTACT_POINT_TYPE
1155 ,l_contact_pt_search_rec.CONTENT_SOURCE_TYPE
1156 ,l_contact_pt_search_rec.EDI_ECE_TP_LOCATION_CODE
1157 ,l_contact_pt_search_rec.EDI_ID_NUMBER
1158 ,l_contact_pt_search_rec.EDI_PAYMENT_FORMAT
1159 ,l_contact_pt_search_rec.EDI_PAYMENT_METHOD
1160 ,l_contact_pt_search_rec.EDI_REMITTANCE_INSTRUCTION
1161 ,l_contact_pt_search_rec.EDI_REMITTANCE_METHOD
1162 ,l_contact_pt_search_rec.EDI_TP_HEADER_ID
1163 ,l_contact_pt_search_rec.EDI_TRANSACTION_HANDLING
1164 ,l_contact_pt_search_rec.EMAIL_ADDRESS
1165 ,l_contact_pt_search_rec.EMAIL_FORMAT
1166 ,l_contact_pt_search_rec.LAST_CONTACT_DT_TIME
1167 ,l_contact_pt_search_rec.PHONE_AREA_CODE
1168 ,l_contact_pt_search_rec.PHONE_CALLING_CALENDAR
1169 ,l_contact_pt_search_rec.PHONE_COUNTRY_CODE
1170 ,l_contact_pt_search_rec.PHONE_EXTENSION
1171 ,l_contact_pt_search_rec.PHONE_LINE_TYPE
1172 ,l_contact_pt_search_rec.PHONE_NUMBER
1173 ,l_contact_pt_search_rec.PRIMARY_FLAG
1174 ,l_contact_pt_search_rec.TELEPHONE_TYPE
1175 ,l_contact_pt_search_rec.TELEX_NUMBER
1176 ,l_contact_pt_search_rec.TIME_ZONE
1177 ,l_contact_pt_search_rec.URL
1178 ,l_contact_pt_search_rec.WEB_TYPE;
1179 CLOSE cpt;
1180
1181 IF g_cpt_custom_queried = 'N' THEN
1182 g_cpt_custom_queried := 'Y';
1183
1184 NUM := 1;
1185 OPEN custom_attribs;
1186 LOOP
1187
1188 FETCH custom_attribs INTO g_cpt_custom_attrs(NUM), g_cpt_custom_procs(NUM);
1189 EXIT WHEN custom_attribs%NOTFOUND;
1190
1191 c := dbms_sql.open_cursor;
1192
1193 l_sql := 'select '|| g_cpt_custom_procs(NUM) || '(:record_id, :entity_name, :attribute_name) from dual';
1194 BEGIN
1195 dbms_sql.parse(c,l_sql,2);
1196 g_cpt_custom_valid(NUM) := 'Y';
1197 EXCEPTION
1198 WHEN OTHERS THEN
1199 g_cpt_custom_valid(NUM) := 'N';
1200 FND_FILE.put_line(fnd_file.log,'Error parsing custom attribute procedure '
1201 || g_cpt_custom_procs(NUM) ||
1202 ' for attribute PARTY.'||g_cpt_custom_attrs(NUM));
1203 END;
1204 dbms_sql.close_cursor(c);
1205
1206
1207 NUM := NUM+1;
1208 END LOOP;
1209 CLOSE custom_attribs;
1210 END IF;
1211
1212 l_return_status:=FND_API.G_RET_STS_SUCCESS;
1213 FOR I IN 1..g_cpt_custom_procs.COUNT LOOP
1214 l_proc_name := g_cpt_custom_procs(I);
1215 l_attr_name := g_cpt_custom_attrs(I);
1216
1217 IF g_cpt_custom_valid(I) = 'Y' THEN
1218
1219 BEGIN
1220 IF l_proc_name='HZ_PARTY_ACQUIRE.get_phone_number' THEN
1221 l_val := HZ_PARTY_ACQUIRE.get_phone_number(p_contact_point_id, 'CONTACT_POINTS', l_attr_name, 'STAGE');
1222 ELSE
1223 -- Create a dynamic SQL block to execute the merge procedure
1224 plsql_block := 'BEGIN '||
1225 ' :retval := '||l_proc_name||'(:record_id, :entity_name, :attribute_name);'||
1226 'END;';
1227
1228 -- Execute the dynamic PLSQL block
1229 EXECUTE IMMEDIATE plsql_block USING
1230 OUT l_val, p_contact_point_id, 'CONTACT_POINTS', l_attr_name;
1231 END IF;
1232
1233 IF l_attr_name = 'FLEX_FORMAT_PHONE_NUMBER' THEN
1234 l_contact_pt_search_rec.FLEX_FORMAT_PHONE_NUMBER := l_val;
1235 ELSIF l_attr_name = 'RAW_PHONE_NUMBER' THEN
1236 l_contact_pt_search_rec.RAW_PHONE_NUMBER := l_val;
1237 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE1' THEN
1238 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE1 := l_val;
1239 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE2' THEN
1240 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE2 := l_val;
1241 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE3' THEN
1242 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE3 := l_val;
1243 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE4' THEN
1247 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE6' THEN
1244 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE4 := l_val;
1245 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE5' THEN
1246 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE5 := l_val;
1248 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE6 := l_val;
1249 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE7' THEN
1250 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE7 := l_val;
1251 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE8' THEN
1252 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE8 := l_val;
1253 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE9' THEN
1254 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE9 := l_val;
1255 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE10' THEN
1256 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE10:= l_val;
1257 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE11' THEN
1258 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE11 := l_val;
1259 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE12' THEN
1260 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE12 := l_val;
1261 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE13' THEN
1262 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE13 := l_val;
1263 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE14' THEN
1264 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE14 := l_val;
1265 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE15' THEN
1266 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE15 := l_val;
1267 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE16' THEN
1268 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE16 := l_val;
1269 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE17' THEN
1270 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE17 := l_val;
1271 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE18' THEN
1272 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE18 := l_val;
1273 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE19' THEN
1274 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE19 := l_val;
1275 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE20' THEN
1276 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE20:= l_val;
1277 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE21' THEN
1278 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE21 := l_val;
1279 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE22' THEN
1280 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE22 := l_val;
1281 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE23' THEN
1282 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE23 := l_val;
1283 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE24' THEN
1284 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE24 := l_val;
1285 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE25' THEN
1286 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE25 := l_val;
1287 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE26' THEN
1288 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE26 := l_val;
1289 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE27' THEN
1290 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE27 := l_val;
1291 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE28' THEN
1292 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE28 := l_val;
1293 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE29' THEN
1294 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE29 := l_val;
1295 ELSIF l_attr_name = 'CUSTOM_ATTRIBUTE30' THEN
1296 l_contact_pt_search_rec.CUSTOM_ATTRIBUTE30:= l_val;
1297 END IF;
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 FND_FILE.put_line(FND_FILE.log,'Error executing custom procedure for attribute ' || l_attr_name || ' (Contact Point ID: ' || p_contact_point_id || '). Continuing ... ');
1301 FND_FILE.put_line(FND_FILE.log,SQLERRM);
1302 /*
1303 FND_MESSAGE.SET_NAME('AR', 'HZ_CUSTOM_PROC_ERROR');
1304 FND_MESSAGE.SET_TOKEN('ENTITY' ,'CONTACT POINTS');
1305 FND_MESSAGE.SET_TOKEN('ATTRIBUTE' ,l_attr_name);
1306 FND_MSG_PUB.ADD;
1307 RAISE FND_API.G_EXC_ERROR;
1308 */
1309 END;
1310 END IF;
1311 END LOOP;
1312
1313 RETURN l_contact_pt_search_rec;
1314 EXCEPTION
1315 WHEN FND_API.G_EXC_ERROR THEN
1316 RAISE FND_API.G_EXC_ERROR;
1317 WHEN OTHERS THEN
1318 RAISE;
1319 FND_MESSAGE.SET_NAME('AR', 'HZ_STAGE_SQL_ERROR');
1320 FND_MESSAGE.SET_TOKEN('PROC' ,'get_contact_point_rec');
1321 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1322 FND_MSG_PUB.ADD;
1323 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1324
1325 END;
1326
1327 FUNCTION get_account_info(
1328 p_party_id IN NUMBER,
1329 p_entity IN VARCHAR2,
1330 p_attribute IN VARCHAR2,
1331 p_context IN VARCHAR2 )
1332 RETURN VARCHAR2 IS
1333
1334 CURSOR cust_acct_info IS
1335 SELECT ACCOUNT_NAME, ACCOUNT_NUMBER
1336 FROM hz_cust_accounts
1337 WHERE PARTY_ID = p_party_id
1338 ORDER BY STATUS,CREATION_DATE;--Bug 9155543
1339
1340 l_acct_name VARCHAR2(240);
1341 l_acct_number VARCHAR2(30);
1342
1343 l_acct_ret VARCHAR2(1999); --Bug No: 4304921
1344 BEGIN
1345
1346 OPEN cust_acct_info;
1347 LOOP
1348 FETCH cust_acct_info INTO l_acct_name, l_acct_number;
1349 EXIT WHEN cust_acct_info%NOTFOUND;
1350
1351 IF p_attribute = 'ALL_ACCOUNT_NAMES' THEN
1352 l_acct_ret :=
1353 l_acct_ret || ' ' || l_acct_name;
1354 ELSIF p_attribute = 'ALL_ACCOUNT_NUMBERS' THEN
1355 l_acct_ret :=
1356 l_acct_ret || ' ' ||l_acct_number;
1357 END IF;
1358 END LOOP;
1359 CLOSE cust_acct_info;
1360 RETURN l_acct_ret;
1361
1362 EXCEPTION
1363 WHEN OTHERS THEN
1364 IF (sqlcode=-6502) THEN
1365 RETURN 'SYNC';--Bug 9204273
1366 END IF;
1367 FND_MESSAGE.SET_NAME('AR', 'HZ_ACQUIRE_PROC_ERROR');
1368 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_PARTY_ACQUIRE.get_account_info');
1369 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1370 FND_MSG_PUB.ADD;
1374 FUNCTION get_known_as (
1371 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1372 END get_account_info;
1373
1375 p_party_id IN NUMBER,
1376 p_entity IN VARCHAR2,
1377 p_attribute IN VARCHAR2,
1378 p_context IN VARCHAR2 )
1379 RETURN VARCHAR2 IS
1380
1381 CURSOR known_as IS
1382 SELECT PARTY_NAME || ' ' || KNOWN_AS || ' ' || KNOWN_AS2 || ' ' || KNOWN_AS3
1383 || ' '|| KNOWN_AS4 || ' '|| KNOWN_AS5, PARTY_NAME
1384 FROM HZ_PARTIES
1385 WHERE party_id = p_party_id;
1386
1387 l_known_as VARCHAR2(4000);
1388 l_party_name VARCHAR2(4000);
1389
1390 BEGIN
1391 OPEN known_as;
1392 FETCH known_as INTO l_known_as, l_party_name;
1393 CLOSE known_as;
1394
1395 IF p_context IS NOT NULL THEN
1396 RETURN l_known_as;
1397 ELSE
1398 RETURN l_party_name;
1399 END IF;
1400 EXCEPTION
1401 WHEN OTHERS THEN
1402 FND_MESSAGE.SET_NAME('AR', 'HZ_ACQUIRE_PROC_ERROR');
1403 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_PARTY_ACQUIRE.get_known_as');
1404 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1405 FND_MSG_PUB.ADD;
1406 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1407 END get_known_as;
1408
1409 FUNCTION get_address (
1410 p_party_site_id IN NUMBER,
1411 p_entity IN VARCHAR2,
1412 p_attribute IN VARCHAR2,
1413 p_context IN VARCHAR2 )
1414 RETURN VARCHAR2 IS
1415
1416 CURSOR c_party_site IS
1417 SELECT rtrim(l.address1 || ' ' || l.address2 || ' ' ||
1418 l.address3 || ' ' || l.address4), rtrim(l.address1)
1419 FROM HZ_PARTY_SITES ps, HZ_LOCATIONS l
1420 WHERE ps.party_site_id = p_party_site_id
1421 AND ps.location_id = l.location_id;
1422
1423 l_address VARCHAR2(4000);
1424 l_address1 VARCHAR2(4000);
1425
1426 BEGIN
1427 OPEN c_party_site;
1428 FETCH c_party_site INTO l_address, l_address1;
1429 CLOSE c_party_site;
1430
1431 IF p_context IS NOT NULL THEN
1432 RETURN l_address;
1433 ELSE
1434 RETURN l_address1;
1435 END IF;
1436
1437 EXCEPTION
1438 WHEN OTHERS THEN
1439 FND_MESSAGE.SET_NAME('AR', 'HZ_ACQUIRE_PROC_ERROR');
1440 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_PARTY_ACQUIRE.get_address');
1441 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1442 FND_MSG_PUB.ADD;
1443 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1444 END get_address;
1445
1446 FUNCTION get_contact_name (
1447 p_org_contact_id IN NUMBER,
1448 p_entity IN VARCHAR2,
1449 p_attribute IN VARCHAR2,
1450 p_context IN VARCHAR2 )
1451 RETURN VARCHAR2 IS
1452
1453 CURSOR cname IS
1454 select party_name
1455 from HZ_ORG_CONTACTS o, HZ_RELATIONSHIPS pr, HZ_parties p
1456 WHERE o.party_relationship_id = pr.relationship_id
1457 AND pr.SUBJECT_TABLE_NAME = 'HZ_PARTIES'
1458 AND pr.OBJECT_TABLE_NAME = 'HZ_PARTIES'
1459 AND pr.DIRECTIONAL_FLAG = 'F'
1460 AND pr.subject_id = p.party_id
1461 AND o.org_contact_id = p_org_contact_id
1462 AND (o.status is null OR o.status = 'A' or o.status = 'I')
1463 AND (pr.status is null OR pr.status = 'A' or pr.status = 'I');
1464
1465 l_cname VARCHAR2(2000);
1466 BEGIN
1467 OPEN cname;
1468 FETCH cname INTO l_cname;
1469 CLOSE cname;
1470
1471 RETURN l_cname;
1472
1473 EXCEPTION
1474 WHEN OTHERS THEN
1475 FND_MESSAGE.SET_NAME('AR', 'HZ_ACQUIRE_PROC_ERROR');
1476 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_PARTY_ACQUIRE.get_address');
1477 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1478 FND_MSG_PUB.ADD;
1479 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1480 END get_contact_name;
1481
1482 FUNCTION get_phone_number (
1483 p_contact_pt_id IN NUMBER,
1484 p_entity IN VARCHAR2,
1485 p_attribute IN VARCHAR2,
1486 p_context IN VARCHAR2 )
1487 RETURN VARCHAR2 IS
1488
1489 CURSOR cpt IS
1490 SELECT PHONE_NUMBER, PHONE_AREA_CODE, PHONE_COUNTRY_CODE
1491 FROM HZ_CONTACT_POINTS
1492 WHERE contact_point_id = p_contact_pt_id;
1493
1494 l_phone_number VARCHAR2(255);
1495 l_phone_area_code VARCHAR2(255);
1496 l_phone_country_code VARCHAR2(255);
1497
1498 BEGIN
1499 OPEN cpt;
1500 FETCH cpt INTO l_phone_number, l_phone_area_code, l_phone_country_code;
1501 CLOSE cpt;
1502
1503 IF p_attribute = 'FLEX_FORMAT_PHONE_NUMBER' THEN
1504 IF p_context IS NOT NULL THEN
1505 RETURN
1506 filter_ph_num(l_phone_number) || ' '||
1507 filter_ph_num(l_phone_area_code||
1508 l_phone_number) || ' '||
1509 filter_ph_num(l_phone_country_code ||
1510 l_phone_area_code||
1511 l_phone_number);
1512 ELSE
1513 RETURN filter_ph_num(
1514 l_phone_country_code ||
1515 l_phone_area_code||
1516 l_phone_number);
1517 END IF;
1518 ELSIF p_attribute = 'RAW_PHONE_NUMBER' THEN
1519 RETURN filter_ph_num(
1520 l_phone_country_code ||
1521 l_phone_area_code||
1522 l_phone_number);
1523 END IF;
1524 RETURN null;
1525 EXCEPTION
1526 WHEN OTHERS THEN
1527 FND_MESSAGE.SET_NAME('AR', 'HZ_ACQUIRE_PROC_ERROR');
1528 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_PARTY_ACQUIRE.get_phone_number');
1529 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1530 FND_MSG_PUB.ADD;
1531 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1532 END get_phone_number;
1533
1534 -- this will return the concaternation of the
1538 p_record_id IN NUMBER,
1535 -- orig sytem and the orig system reference
1536 -- delimited by a space
1537 FUNCTION get_ssm_mappings (
1539 p_entity IN VARCHAR2,
1540 p_attribute IN VARCHAR2,
1541 p_context IN VARCHAR2 )
1542 RETURN VARCHAR2 IS
1543 retstr VARCHAR2(2000) ;
1544 BEGIN
1545
1546 -- record_id will map to owner_table id
1547 -- decoded entity will map to owner_table_name
1548 -- return concatenation of all that you can find
1549 IF p_context = 'STAGE'
1550 THEN
1551 FOR t_cur in
1552 ( select orig_system, orig_system_reference
1553 from
1554 hz_orig_sys_references
1555 where owner_table_name = decode(p_entity,
1556 'PARTY','HZ_PARTIES',
1557 'PARTY_SITES', 'HZ_PARTY_SITES',
1558 'CONTACTS','HZ_ORG_CONTACTS',
1559 'CONTACT_POINTS', 'HZ_CONTACT_POINTS')
1560 and owner_table_id = p_record_id
1561 and nvl(STATUS,'A')='A' )
1562 LOOP
1563 retstr := retstr || t_cur.orig_system ||' '|| t_cur.orig_system_reference || ' ';
1564 END LOOP;
1565
1566 -- return the primary one
1567 ELSE
1568
1569 IF p_entity = 'PARTY'
1570 THEN
1571 select b.orig_system || ' '||a.orig_system_reference
1572 into retstr
1573 from hz_parties a, hz_orig_sys_references b
1574 where b.owner_table_id = p_record_id
1575 and b.owner_table_name = 'HZ_PARTIES'
1576 and b.orig_system_reference = a.orig_system_reference
1577 and nvl(b.STATUS,'A')='A';
1578 END IF;
1579
1580 IF p_entity = 'PARTY_SITES'
1581 THEN
1582 select b.orig_system || ' '||a.orig_system_reference into retstr
1583 from hz_party_sites a, hz_orig_sys_references b
1584 where b.owner_table_id = p_record_id
1585 and b.owner_table_name = 'HZ_PARTY_SITES'
1586 and b.orig_system_reference = a.orig_system_reference
1587 and nvl(b.STATUS,'A')='A';
1588 END IF;
1589
1590 IF p_entity = 'CONTACT'
1591 THEN
1592 select b.orig_system || ' '||a.orig_system_reference into retstr
1593 from hz_org_contacts a, hz_orig_sys_references b
1594 where b.owner_table_id = p_record_id
1595 and b.owner_table_name = 'HZ_ORG_CONTACTS'
1596 and b.orig_system_reference = a.orig_system_reference
1597 and nvl(b.STATUS,'A')='A';
1598
1599 END IF;
1600
1601 IF p_entity = 'CONTACT_POINTS'
1602 THEN
1603 select b.orig_system || ' '||a.orig_system_reference into retstr
1604 from hz_contact_points a, hz_orig_sys_references b
1605 where b.owner_table_id = p_record_id
1606 and b.owner_table_name = 'HZ_CONTACT_POINTS'
1607 and b.orig_system_reference = a.orig_system_reference
1608 and nvl(b.STATUS,'A')='A';
1609
1610 END IF;
1611
1612 END IF ;
1613
1614 return retstr;
1615
1616
1617
1618 EXCEPTION
1619 WHEN NO_DATA_FOUND THEN
1620 RETURN NULL;
1621 WHEN OTHERS THEN
1622 IF (sqlcode=-6502) THEN
1623 return retstr;
1624 END IF;
1625 FND_MESSAGE.SET_NAME('AR', 'HZ_ACQUIRE_PROC_ERROR');
1626 FND_MESSAGE.SET_TOKEN('PROC' ,'HZ_PARTY_ACQUIRE.GET_SSM_MAPPINGS');
1627 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
1628 FND_MSG_PUB.ADD;
1629 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1630 END get_ssm_mappings ;
1631
1632 END;