[Home] [Help]
PACKAGE BODY: APPS.CAC_SYNC_CONTACTS_PVT
Source
4 /* Modified cursor for bug 6147530 */
1 PACKAGE BODY CAC_SYNC_CONTACTS_PVT as
2 /* $Header: cacvsctb.pls 120.3.12000000.2 2007/06/29 05:49:24 vsood ship $ */
3
5 CURSOR c_new_contacts
6 /*
7 Getthe new contacts information from TCA HZ tables that have been newly added to the
8 party preference table HZ_PARTY_PREFERENCES since last sync */
9
10 (b_principal_id NUMBER,
11 b_person_party_id NUMBER,
12 b_bookmark_module VARCHAR2,
13 b_bookmark_category VARCHAR2,
14 b_bookmark_preference VARCHAR2)
15
16 IS
17 SELECT CAC_SYNC_CONTACT_TEMPS_S.NEXTVAL SYNC_CONTACT_TEMP_ID,
18 HPP.VALUE_NUMBER REL_CONTACT_PARTY_ID
19 , ORG.PARTY_ID ORG_PARTY_ID
20 , ORG.PARTY_NAME ORG_NAME
21 , PERSON.PARTY_ID PERSON_PARTY_ID
22 , PERSON.PARTY_NAME PERSON_FULL_NAME
23 , PERSON.PERSON_LAST_NAME||';'||
24 PERSON.PERSON_FIRST_NAME||';'||
25 PERSON.PERSON_MIDDLE_NAME||';'||
26 ARLK.MEANING||';'||
27 PERSON.PERSON_NAME_SUFFIX PERSON_NAME_DELIMITED
28 , HOC.JOB_TITLE JOB_TITLE
29 , HOC.DEPARTMENT DEPARTMENT
30 , LOC.PO_BOX_NUMBER||';'||
31 LOC.ADDRESS1||';'||
32 LOC.ADDRESS2||';'||
33 LOC.CITY||';'||
34 LOC.STATE||';'||
35 LOC.POSTAL_CODE||';'||
36 LOC.COUNTRY ADDRESS_DELIMITED
37 , CPT_WP.PHONE_NUMBER WORK_PHONE_NUMBER
38 , CPT_HP.PHONE_NUMBER HOME_PHONE_NUMBER
39 , CPT_WF.PHONE_NUMBER WORK_FAX_NUMBER
40 , CPT_CELL.PHONE_NUMBER CELL_NUMBER
41 , CPT_PAGER.PHONE_NUMBER PAGER_NUMBER
42 , CPT_EMAIL.EMAIL_FORMAT EMAIL_FORMAT
43 , CPT_EMAIL.EMAIL_ADDRESS EMAIL_ADDRESS
44 , SITE.PARTY_SITE_ID PARTY_SITE_ID
45 , CPT_WP.CONTACT_POINT_ID WORK_PHONE_CONTACT_POINT_ID
46 , CPT_HP.CONTACT_POINT_ID HOME_PHONE_CONTACT_POINT_ID
47 , CPT_WF.CONTACT_POINT_ID WORK_FAX_CONTACT_POINT_ID
48 , CPT_CELL.CONTACT_POINT_ID CELL_PHONE_CONTACT_POINT_ID
49 , CPT_PAGER.CONTACT_POINT_ID PAGER_CONTACT_POINT_ID
50 , CPT_EMAIL.CONTACT_POINT_ID EMAIL_CONTACT_POINT_ID
51 , 'N' STATUS
52 FROM HZ_PARTY_PREFERENCES HPP
53 , HZ_PARTIES REL_CONTACT
54 , HZ_PARTIES ORG
55 , HZ_PARTIES PERSON
56 , AR_LOOKUPS ARLK
57 , HZ_ORG_CONTACTS HOC
58 , HZ_RELATIONSHIPS REL
59 , HZ_PARTY_SITES SITE
60 , HZ_LOCATIONS LOC
61 , (SELECT CP.OWNER_TABLE_ID
62 , CP.CONTACT_POINT_ID
63 , 'WORK;VOICE' CONTACT_TYPE
64 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
65 CP.PHONE_AREA_CODE,
66 CP.PHONE_NUMBER,
67 CP.PHONE_EXTENSION) PHONE_NUMBER
68 FROM HZ_CONTACT_POINTS CP
69 , HZ_PARTY_PREFERENCES PREF
70 WHERE PREF.PARTY_ID = b_person_party_id
71 AND PREF.CATEGORY = b_bookmark_category
72 AND PREF.PREFERENCE_CODE = b_bookmark_preference
73 AND PREF.MODULE = b_bookmark_module
74 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
75 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
76 FROM HZ_CONTACT_POINTS C
77 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
78 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
79 AND C.CONTACT_POINT_TYPE = 'PHONE'
80 AND C.PHONE_LINE_TYPE = 'GEN'
81 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
82 AND C.STATUS = 'A')
83 ) CPT_WP
84 , (SELECT CP.OWNER_TABLE_ID
85 , CP.CONTACT_POINT_ID
86 , 'HOME;VOICE' CONTACT_TYPE
87 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
88 CP.PHONE_AREA_CODE,
89 CP.PHONE_NUMBER,
90 CP.PHONE_EXTENSION) PHONE_NUMBER
91
92 FROM HZ_CONTACT_POINTS CP
93 , HZ_PARTY_PREFERENCES PREF
94 WHERE PREF.PARTY_ID = b_person_party_id
95 AND PREF.CATEGORY = b_bookmark_category
96 AND PREF.PREFERENCE_CODE = b_bookmark_preference
97 AND PREF.MODULE = b_bookmark_module
98 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
99 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
100 FROM HZ_CONTACT_POINTS C
101 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
102 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
103 AND C.CONTACT_POINT_TYPE = 'PHONE'
104 AND C.PHONE_LINE_TYPE = 'GEN'
105 AND C.CONTACT_POINT_PURPOSE = 'PERSONAL'
106 AND C.STATUS = 'A')
107 ) CPT_HP
108 , (SELECT CP.OWNER_TABLE_ID
109 , CP.CONTACT_POINT_ID
110 , 'WORK;FAX' CONTACT_TYPE
111 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
115
112 CP.PHONE_AREA_CODE,
113 CP.PHONE_NUMBER,
114 CP.PHONE_EXTENSION) PHONE_NUMBER
116 FROM HZ_CONTACT_POINTS CP
117 , HZ_PARTY_PREFERENCES PREF
118 WHERE PREF.PARTY_ID = b_person_party_id
119 AND PREF.CATEGORY = b_bookmark_category
120 AND PREF.PREFERENCE_CODE = b_bookmark_preference
121 AND PREF.MODULE = b_bookmark_module
122 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
123 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
124 FROM HZ_CONTACT_POINTS C
125 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
126 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
127 AND C.CONTACT_POINT_TYPE = 'PHONE'
128 AND C.PHONE_LINE_TYPE = 'FAX'
129 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
130 AND C.STATUS = 'A')
131 ) CPT_WF
132 , (SELECT CP.OWNER_TABLE_ID
133 , CP.CONTACT_POINT_ID
134 , 'CELL;VOICE' CONTACT_TYPE
135 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
136 CP.PHONE_AREA_CODE,
137 CP.PHONE_NUMBER,
138 CP.PHONE_EXTENSION) PHONE_NUMBER
139
140 FROM HZ_CONTACT_POINTS CP
141 , HZ_PARTY_PREFERENCES PREF
142 WHERE PREF.PARTY_ID = b_person_party_id
143 AND PREF.CATEGORY = b_bookmark_category
144 AND PREF.PREFERENCE_CODE = b_bookmark_preference
145 AND PREF.MODULE = b_bookmark_module
146 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
147 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
148 FROM HZ_CONTACT_POINTS C
149 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
150 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
151 AND C.CONTACT_POINT_TYPE = 'PHONE'
152 AND C.PHONE_LINE_TYPE = 'MOBILE'
153 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
154 AND C.STATUS = 'A')
155 ) CPT_CELL
156 , (SELECT CP.OWNER_TABLE_ID
157 , CP.CONTACT_POINT_ID
158 , 'PAGER' CONTACT_TYPE
159 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
160 CP.PHONE_AREA_CODE,
161 CP.PHONE_NUMBER,
162 CP.PHONE_EXTENSION) PHONE_NUMBER
163
164 FROM HZ_CONTACT_POINTS CP
165 , HZ_PARTY_PREFERENCES PREF
166 WHERE PREF.PARTY_ID = b_person_party_id
167 AND PREF.CATEGORY = b_bookmark_category
168 AND PREF.PREFERENCE_CODE = b_bookmark_preference
169 AND PREF.MODULE = b_bookmark_module
170 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
171 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
172 FROM HZ_CONTACT_POINTS C
173 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
174 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
175 AND C.CONTACT_POINT_TYPE = 'PHONE'
176 AND C.PHONE_LINE_TYPE = 'PAGER'
177 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
178 AND C.STATUS = 'A')
179 ) CPT_PAGER
180 , (SELECT CP.OWNER_TABLE_ID
181 , CP.CONTACT_POINT_ID
182 , 'EMAIL' CONTACT_TYPE
183 , CP.EMAIL_FORMAT
184 , CP.EMAIL_ADDRESS
185 FROM HZ_CONTACT_POINTS CP
186 , HZ_PARTY_PREFERENCES PREF
187 WHERE PREF.PARTY_ID = b_person_party_id
188 AND PREF.CATEGORY = b_bookmark_category
189 AND PREF.PREFERENCE_CODE = b_bookmark_preference
190 AND PREF.MODULE = b_bookmark_module
191 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
192 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
193 FROM HZ_CONTACT_POINTS C
194 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
195 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
196 AND C.CONTACT_POINT_TYPE = 'EMAIL'
197 AND C.STATUS = 'A')
198 ) CPT_EMAIL
199 WHERE NOT EXISTS (SELECT NULL
200 FROM CAC_SYNC_MAPPINGS
201 WHERE GUID = HPP.VALUE_NUMBER
202 AND PRINCIPAL_ID = b_principal_id
203 AND SERVER_URI = SERVER_URI_CONST)
204 AND HPP.CATEGORY = b_bookmark_category
205 AND HPP.PREFERENCE_CODE = b_bookmark_preference
206 AND HPP.PARTY_ID = b_person_party_id
207 AND HPP.MODULE = b_bookmark_module
208 AND REL_CONTACT.PARTY_ID = HPP.VALUE_NUMBER
212 AND ARLK.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
209 AND REL.PARTY_ID = REL_CONTACT.PARTY_ID
210 AND REL.DIRECTIONAL_FLAG = 'F'
211 AND PERSON.PARTY_ID = REL.SUBJECT_ID
213 AND ARLK.LOOKUP_CODE(+) = PERSON.PERSON_PRE_NAME_ADJUNCT
214 AND ORG.PARTY_ID = REL.OBJECT_ID
215 AND HOC.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
216 AND SITE.PARTY_ID (+)= REL_CONTACT.PARTY_ID
217 AND SITE.STATUS (+)= 'A'
218 AND SITE.IDENTIFYING_ADDRESS_FLAG (+)= 'Y'
219 AND LOC.LOCATION_ID(+) = SITE.LOCATION_ID
220 AND CPT_WP.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
221 AND CPT_HP.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
222 AND CPT_WF.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
223 AND CPT_CELL.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
224 AND CPT_PAGER.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
225 AND CPT_EMAIL.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID;
226
227 /* Modified cursor for bug 6147530 */
228 CURSOR c_updated_contacts
229 /* Getthe updated contacts information from TCA HZ tables that have been updated
230 since last sync */
231 (b_principal_id NUMBER,
232 b_person_party_id NUMBER,
233 b_sync_anchor DATE,
234 b_bookmark_module VARCHAR2,
235 b_bookmark_category VARCHAR2,
236 b_bookmark_preference VARCHAR2)
237 IS
238 SELECT CAC_SYNC_CONTACT_TEMPS_S.NEXTVAL SYNC_CONTACT_TEMP_ID
239 , REL_CONTACT_PARTY_ID
240 , ORG_PARTY_ID
241 , ORG_NAME
242 , PERSON_PARTY_ID
243 , PERSON_FULL_NAME
244 , PERSON_NAME_DELIMITED
245 , JOB_TITLE
246 , DEPARTMENT
247 , ADDRESS_DELIMITED
248 , WORK_PHONE_NUMBER
249 , HOME_PHONE_NUMBER
250 , WORK_FAX_NUMBER
251 , CELL_NUMBER
252 , PAGER_NUMBER
253 , EMAIL_FORMAT
254 , EMAIL_ADDRESS
255 , PARTY_SITE_ID
256 , WORK_PHONE_CONTACT_POINT_ID
257 , HOME_PHONE_CONTACT_POINT_ID
258 , WORK_FAX_CONTACT_POINT_ID
259 , CELL_PHONE_CONTACT_POINT_ID
260 , PAGER_CONTACT_POINT_ID
261 , EMAIL_CONTACT_POINT_ID
262 , STATUS
263 FROM (
264 SELECT HPP.VALUE_NUMBER REL_CONTACT_PARTY_ID
265 , ORG.PARTY_ID ORG_PARTY_ID
266 , ORG.PARTY_NAME ORG_NAME
267 , PERSON.PARTY_ID PERSON_PARTY_ID
268 , PERSON.PARTY_NAME PERSON_FULL_NAME
269 , PERSON.PERSON_LAST_NAME||';'||
270 PERSON.PERSON_FIRST_NAME||';'||
271 PERSON.PERSON_MIDDLE_NAME||';'||
272 ARLK.MEANING||';'||
273 PERSON.PERSON_NAME_SUFFIX PERSON_NAME_DELIMITED
274 , HOC.JOB_TITLE JOB_TITLE
275 , HOC.DEPARTMENT DEPARTMENT
276 , LOC.PO_BOX_NUMBER||';'||
277 LOC.ADDRESS1||';'||
278 LOC.ADDRESS2||';'||
279 LOC.CITY||';'||
280 LOC.STATE||';'||
281 LOC.POSTAL_CODE||';'||
282 LOC.COUNTRY ADDRESS_DELIMITED
283 , CPT_WP.PHONE_NUMBER WORK_PHONE_NUMBER
284 , CPT_HP.PHONE_NUMBER HOME_PHONE_NUMBER
285 , CPT_WF.PHONE_NUMBER WORK_FAX_NUMBER
286 , CPT_CELL.PHONE_NUMBER CELL_NUMBER
287 , CPT_PAGER.PHONE_NUMBER PAGER_NUMBER
288 , CPT_EMAIL.EMAIL_FORMAT EMAIL_FORMAT
289 , CPT_EMAIL.EMAIL_ADDRESS EMAIL_ADDRESS
290 , SITE.PARTY_SITE_ID PARTY_SITE_ID
291 , CPT_WP.CONTACT_POINT_ID WORK_PHONE_CONTACT_POINT_ID
292 , CPT_HP.CONTACT_POINT_ID HOME_PHONE_CONTACT_POINT_ID
293 , CPT_WF.CONTACT_POINT_ID WORK_FAX_CONTACT_POINT_ID
294 , CPT_CELL.CONTACT_POINT_ID CELL_PHONE_CONTACT_POINT_ID
295 , CPT_PAGER.CONTACT_POINT_ID PAGER_CONTACT_POINT_ID
296 , CPT_EMAIL.CONTACT_POINT_ID EMAIL_CONTACT_POINT_ID
297 , 'U' STATUS
298 , DECODE(SIGN(REL_CONTACT.LAST_UPDATE_DATE - b_sync_anchor),
299 1, 1,
300 0, 1,
301 0)
302 + DECODE(SIGN(PERSON.LAST_UPDATE_DATE - b_sync_anchor),
303 1, 1,
304 0, 1,
305 0)
306 + DECODE(SIGN(HOC.LAST_UPDATE_DATE - b_sync_anchor),
307 1, 1,
308 0, 1,
309 0)
310 + DECODE(CSPM.PARTY_SITE_ID,
311 NULL, DECODE(SITE.PARTY_SITE_ID, NULL, 0, 1),
312 DECODE(SITE.PARTY_SITE_ID,
313 NULL, 1,
314 DECODE(CSPM.PARTY_SITE_ID,
315 SITE.PARTY_SITE_ID,
316 DECODE(SIGN(SITE.LAST_UPDATE_DATE - b_sync_anchor),
317 1, 1,
318 0, 1,
319 0),
320 1)
321 )
322 )
323 + DECODE(SIGN(LOC.LAST_UPDATE_DATE - b_sync_anchor),
324 1, 1,
325 0, 1,
326 0)
327 + DECODE(CPT_WP.CONTACT_POINT_ID,
328 NULL, DECODE(CSPM.WORK_CONTACT_POINT_ID, NULL, 0, 1),
329 DECODE(CSPM.WORK_CONTACT_POINT_ID,
330 NULL, 1,
331 DECODE(CPT_WP.CONTACT_POINT_ID, CSPM.WORK_CONTACT_POINT_ID,
332 DECODE(SIGN(CPT_WP.LAST_UPDATE_DATE - b_sync_anchor),
333 1, 1,
334 0, 1,
338 NULL, DECODE(CSPM.HOME_CONTACT_POINT_ID, NULL, 0, 1),
335 0),
336 1)))
337 + DECODE(CPT_HP.CONTACT_POINT_ID,
339 DECODE(CSPM.HOME_CONTACT_POINT_ID,
340 NULL, 1,
341 DECODE(CPT_HP.CONTACT_POINT_ID, CSPM.HOME_CONTACT_POINT_ID,
342 DECODE(SIGN(CPT_HP.LAST_UPDATE_DATE - b_sync_anchor),
343 1, 1,
344 0, 1,
345 0),
346 1)))
347 + DECODE(CPT_WF.CONTACT_POINT_ID,
348 NULL, DECODE(CSPM.FAX_CONTACT_POINT_ID, NULL, 0, 1),
349 DECODE(CSPM.FAX_CONTACT_POINT_ID,
350 NULL, 1,
351 DECODE(CPT_WF.CONTACT_POINT_ID, CSPM.FAX_CONTACT_POINT_ID,
352 DECODE(SIGN(CPT_WF.LAST_UPDATE_DATE - b_sync_anchor),
353 1, 1,
354 0, 1,
355 0),
356 1)))
357 + DECODE(CPT_CELL.CONTACT_POINT_ID,
358 NULL, DECODE(CSPM.CELL_CONTACT_POINT_ID, NULL, 0, 1),
359 DECODE(CSPM.CELL_CONTACT_POINT_ID,
360 NULL, 1,
361 DECODE(CPT_CELL.CONTACT_POINT_ID, CSPM.CELL_CONTACT_POINT_ID,
362 DECODE(SIGN(CPT_CELL.LAST_UPDATE_DATE - b_sync_anchor),
363 1, 1,
364 0, 1,
365 0),
366 1)))
367 + DECODE(CPT_PAGER.CONTACT_POINT_ID,
368 NULL, DECODE(CSPM.PAGER_CONTACT_POINT_ID, NULL, 0, 1),
369 DECODE(CSPM.PAGER_CONTACT_POINT_ID,
370 NULL, 1,
371 DECODE(CPT_PAGER.CONTACT_POINT_ID, CSPM.PAGER_CONTACT_POINT_ID,
372 DECODE(SIGN(CPT_PAGER.LAST_UPDATE_DATE - b_sync_anchor),
373 1, 1,
374 0, 1,
375 0),
376 1)))
377 + DECODE(CPT_EMAIL.CONTACT_POINT_ID,
378 NULL, DECODE(CSPM.EMAIL_CONTACT_POINT_ID, NULL, 0, 1),
379 DECODE(CSPM.EMAIL_CONTACT_POINT_ID,
380 NULL, 1,
381 DECODE(CPT_EMAIL.CONTACT_POINT_ID, CSPM.EMAIL_CONTACT_POINT_ID,
382 DECODE(SIGN(CPT_EMAIL.LAST_UPDATE_DATE - b_sync_anchor),
383 1, 1,
384 0, 1,
385 0),
386 1))) UPDATED_FLAG
387 FROM HZ_PARTIES REL_CONTACT
388 , HZ_PARTIES ORG
389 , HZ_PARTIES PERSON
390 , AR_LOOKUPS ARLK
391 , HZ_ORG_CONTACTS HOC
392 , HZ_RELATIONSHIPS REL
393 , HZ_PARTY_SITES SITE
394 , HZ_LOCATIONS LOC
395 , HZ_PARTY_PREFERENCES HPP
396 , CAC_SYNC_CONTACT_MAPPINGS CSPM
397 , (SELECT CP.OWNER_TABLE_ID
398 , CP.CONTACT_POINT_ID
399 , 'WORK;VOICE' CONTACT_TYPE
400 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
401 CP.PHONE_AREA_CODE,
402 CP.PHONE_NUMBER,
403 CP.PHONE_EXTENSION) PHONE_NUMBER
404 , CP.LAST_UPDATE_DATE
405 FROM HZ_CONTACT_POINTS CP
406 , HZ_PARTY_PREFERENCES PREF
407 WHERE PREF.PARTY_ID = b_person_party_id
408 AND PREF.CATEGORY = b_bookmark_category
409 AND PREF.PREFERENCE_CODE = b_bookmark_preference
410 AND PREF.MODULE = b_bookmark_module
411 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
412 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
413 FROM HZ_CONTACT_POINTS C
414 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
415 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
416 AND C.CONTACT_POINT_TYPE = 'PHONE'
417 AND C.PHONE_LINE_TYPE = 'GEN'
418 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
419 AND C.STATUS = 'A')
420 ) CPT_WP
421 , (SELECT CP.OWNER_TABLE_ID
422 , CP.CONTACT_POINT_ID
423 , 'HOME;VOICE' CONTACT_TYPE
424 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
425 CP.PHONE_AREA_CODE,
426 CP.PHONE_NUMBER,
427 CP.PHONE_EXTENSION) PHONE_NUMBER
428
429 , CP.LAST_UPDATE_DATE
430 FROM HZ_CONTACT_POINTS CP
431 , HZ_PARTY_PREFERENCES PREF
432 WHERE PREF.PARTY_ID = b_person_party_id
433 AND PREF.CATEGORY = b_bookmark_category
434 AND PREF.PREFERENCE_CODE = b_bookmark_preference
435 AND PREF.MODULE = b_bookmark_module
439 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
436 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
437 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
438 FROM HZ_CONTACT_POINTS C
440 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
441 AND C.CONTACT_POINT_TYPE = 'PHONE'
442 AND C.PHONE_LINE_TYPE = 'GEN'
443 AND C.CONTACT_POINT_PURPOSE = 'PERSONAL'
444 AND C.STATUS = 'A')
445 ) CPT_HP
446 , (SELECT CP.OWNER_TABLE_ID
447 , CP.CONTACT_POINT_ID
448 , 'WORK;FAX' CONTACT_TYPE
449 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
450 CP.PHONE_AREA_CODE,
451 CP.PHONE_NUMBER,
452 CP.PHONE_EXTENSION) PHONE_NUMBER
453
454 , CP.LAST_UPDATE_DATE
455 FROM HZ_CONTACT_POINTS CP
456 , HZ_PARTY_PREFERENCES PREF
457 WHERE PREF.PARTY_ID = b_person_party_id
458 AND PREF.CATEGORY = b_bookmark_category
459 AND PREF.PREFERENCE_CODE = b_bookmark_preference
460 AND PREF.MODULE = b_bookmark_module
461 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
462 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
463 FROM HZ_CONTACT_POINTS C
464 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
465 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
466 AND C.CONTACT_POINT_TYPE = 'PHONE'
467 AND C.PHONE_LINE_TYPE = 'FAX'
468 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
469 AND C.STATUS = 'A')
470 ) CPT_WF
471 , (SELECT CP.OWNER_TABLE_ID
472 , CP.CONTACT_POINT_ID
473 , 'CELL;VOICE' CONTACT_TYPE
474 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
475 CP.PHONE_AREA_CODE,
476 CP.PHONE_NUMBER,
477 CP.PHONE_EXTENSION) PHONE_NUMBER
478
479 , CP.LAST_UPDATE_DATE
480 FROM HZ_CONTACT_POINTS CP
481 , HZ_PARTY_PREFERENCES PREF
482 WHERE PREF.PARTY_ID = b_person_party_id
483 AND PREF.CATEGORY = b_bookmark_category
484 AND PREF.PREFERENCE_CODE = b_bookmark_preference
485 AND PREF.MODULE = b_bookmark_module
486 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
487 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
488 FROM HZ_CONTACT_POINTS C
489 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
490 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
491 AND C.CONTACT_POINT_TYPE = 'PHONE'
492 AND C.PHONE_LINE_TYPE = 'MOBILE'
493 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
494 AND C.STATUS = 'A')
495 ) CPT_CELL
496 , (SELECT CP.OWNER_TABLE_ID
497 , CP.CONTACT_POINT_ID
498 , 'PAGER' CONTACT_TYPE
499 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
500 CP.PHONE_AREA_CODE,
501 CP.PHONE_NUMBER,
502 CP.PHONE_EXTENSION) PHONE_NUMBER
503
504 , CP.LAST_UPDATE_DATE
505 FROM HZ_CONTACT_POINTS CP
506 , HZ_PARTY_PREFERENCES PREF
507 WHERE PREF.PARTY_ID = b_person_party_id
508 AND PREF.CATEGORY = b_bookmark_category
509 AND PREF.PREFERENCE_CODE = b_bookmark_preference
510 AND PREF.MODULE = b_bookmark_module
511 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
512 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
513 FROM HZ_CONTACT_POINTS C
514 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
515 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
516 AND C.CONTACT_POINT_TYPE = 'PHONE'
517 AND C.PHONE_LINE_TYPE = 'PAGER'
518 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
519 AND C.STATUS = 'A')
520 ) CPT_PAGER
521 , (SELECT CP.OWNER_TABLE_ID
522 , CP.CONTACT_POINT_ID
523 , 'EMAIL' CONTACT_TYPE
524 , CP.EMAIL_FORMAT
525 , CP.EMAIL_ADDRESS
526 , CP.LAST_UPDATE_DATE
530 AND PREF.CATEGORY = b_bookmark_category
527 FROM HZ_CONTACT_POINTS CP
528 , HZ_PARTY_PREFERENCES PREF
529 WHERE PREF.PARTY_ID = b_person_party_id
531 AND PREF.PREFERENCE_CODE = b_bookmark_preference
532 AND PREF.MODULE = b_bookmark_module
533 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
534 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
535 FROM HZ_CONTACT_POINTS C
536 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
537 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
538 AND C.CONTACT_POINT_TYPE = 'EMAIL'
539 AND C.STATUS = 'A')
540 ) CPT_EMAIL
541 WHERE HPP.PARTY_ID = b_person_party_id
542 AND HPP.CATEGORY = b_bookmark_category
543 AND HPP.PREFERENCE_CODE = b_bookmark_preference
544 AND HPP.MODULE = b_bookmark_module
545 AND EXISTS (SELECT NULL
546 FROM CAC_SYNC_MAPPINGS
547 WHERE GUID = HPP.VALUE_NUMBER
548 AND PRINCIPAL_ID = b_principal_id
549 AND SERVER_URI = SERVER_URI_CONST)
550 AND REL_CONTACT.PARTY_ID = HPP.VALUE_NUMBER
551 AND ORG.PARTY_TYPE = 'ORGANIZATION'
552 AND PERSON.PARTY_TYPE = 'PERSON'
553 AND ARLK.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
554 AND ARLK.LOOKUP_CODE(+) = PERSON.PERSON_PRE_NAME_ADJUNCT
555 AND REL.PARTY_ID = REL_CONTACT.PARTY_ID
556 AND REL.DIRECTIONAL_FLAG = 'F'
557 AND PERSON.PARTY_ID = REL.SUBJECT_ID
558 AND ORG.PARTY_ID = REL.OBJECT_ID
559 AND HOC.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
560 AND SITE.PARTY_ID (+)= REL_CONTACT.PARTY_ID
561 AND SITE.STATUS (+)= 'A'
562 AND SITE.IDENTIFYING_ADDRESS_FLAG (+)= 'Y'
563 AND LOC.LOCATION_ID(+) = SITE.LOCATION_ID
564 AND CPT_WP.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
565 AND CPT_HP.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
566 AND CPT_WF.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
567 AND CPT_CELL.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
568 AND CPT_PAGER.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
569 AND CPT_EMAIL.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
570 AND CSPM.CONTACT_PARTY_ID(+) = REL_CONTACT.PARTY_ID
571 )
572 WHERE UPDATED_FLAG > 0;
573
574 CURSOR c_deleted_contacts
575 /* Getthe deleted contacts information from TCA HZ tables that have been deleted
576 since last sync */
577 (b_principal_id NUMBER,
578 b_person_party_id NUMBER,
579 b_bookmark_module VARCHAR2,
580 b_bookmark_category VARCHAR2,
581 b_bookmark_preference VARCHAR2)
582
583 IS
584 SELECT
585 CAC_SYNC_CONTACT_TEMPS_S.NEXTVAL SYNC_CONTACT_TEMP_ID,
586 CSUM.GUID REL_CONTACT_PARTY_ID,
587 -1 ORG_PARTY_ID,
588 NULL ORG_NAME,
589 -1 PERSON_PARTY_ID,
590 NULL PERSON_FULL_NAME,
591 NULL PERSON_NAME_DELIMITED,
592 NULL JOB_TITLE,
593 NULL DEPARTMENT,
594 NULL ADDRESS_DELIMITED,
595 NULL WORK_PHONE_NUMBER,
596 NULL HOME_PHONE_NUMBER,
597 NULL WORK_FAX_NUMBER,
598 NULL CELL_NUMBER,
599 NULL PAGER_NUMBER,
600 NULL EMAIL_FORMAT,
601 NULL EMAIL_ADDRESS,
602 NULL PARTY_SITE_ID,
603 NULL WORK_PHONE_CONTACT_POINT_ID,
604 NULL HOME_PHONE_CONTACT_POINT_ID,
605 NULL WORK_FAX_CONTACT_POINT_ID,
606 NULL CELL_PHONE_CONTACT_POINT_ID,
607 NULL PAGER_CONTACT_POINT_ID,
608 NULL EMAIL_CONTACT_POINT_ID,
609 'D' STATUS
610 FROM CAC_SYNC_MAPPINGS CSUM
611 WHERE NOT EXISTS (SELECT NULL
612 FROM HZ_PARTY_PREFERENCES
613 WHERE VALUE_NUMBER = CSUM.GUID
614 AND CATEGORY = b_bookmark_category
615 AND PREFERENCE_CODE = b_bookmark_preference
616 AND MODULE = b_bookmark_module
617 AND PARTY_ID = b_person_party_id)
618 AND CSUM.PRINCIPAL_ID = b_principal_id
619 AND CSUM.SERVER_URI = SERVER_URI_CONST;
620
621 /* Modified cursor for bug 6147530 */
622 CURSOR c_all_contacts
623 /* Getthe ALL contacts information from TCA HZ tables */
624 (b_person_party_id NUMBER,
625 b_bookmark_module VARCHAR2,
626 b_bookmark_category VARCHAR2,
627 b_bookmark_preference VARCHAR2
628 )
629
630 IS SELECT CAC_SYNC_CONTACT_TEMPS_S.NEXTVAL SYNC_CONTACT_TEMP_ID
631 , HPP.VALUE_NUMBER REL_CONTACT_PARTY_ID
632 , ORG.PARTY_ID ORG_PARTY_ID
633 , ORG.PARTY_NAME ORG_NAME
634 , PERSON.PARTY_ID PERSON_PARTY_ID
635 , PERSON.PARTY_NAME PERSON_FULL_NAME
636 , PERSON.PERSON_LAST_NAME||';'||
637 PERSON.PERSON_FIRST_NAME||';'||
638 PERSON.PERSON_MIDDLE_NAME||';'||
639 ARLK.MEANING||';'||
640 PERSON.PERSON_NAME_SUFFIX PERSON_NAME_DELIMITED
641 , HOC.JOB_TITLE JOB_TITLE
642 , HOC.DEPARTMENT DEPARTMENT
643 , LOC.PO_BOX_NUMBER||';'||
644 LOC.ADDRESS1||';'||
645 LOC.ADDRESS2||';'||
646 LOC.CITY||';'||
647 LOC.STATE||';'||
648 LOC.POSTAL_CODE||';'||
649 LOC.COUNTRY ADDRESS_DELIMITED
653 , CPT_CELL.PHONE_NUMBER CELL_NUMBER
650 , CPT_WP.PHONE_NUMBER WORK_PHONE_NUMBER
651 , CPT_HP.PHONE_NUMBER HOME_PHONE_NUMBER
652 , CPT_WF.PHONE_NUMBER WORK_FAX_NUMBER
654 , CPT_PAGER.PHONE_NUMBER PAGER_NUMBER
655 , CPT_EMAIL.EMAIL_FORMAT EMAIL_FORMAT
656 , CPT_EMAIL.EMAIL_ADDRESS EMAIL_ADDRESS
657 , SITE.PARTY_SITE_ID PARTY_SITE_ID
658 , CPT_WP.CONTACT_POINT_ID WORK_PHONE_CONTACT_POINT_ID
659 , CPT_HP.CONTACT_POINT_ID HOME_PHONE_CONTACT_POINT_ID
660 , CPT_WF.CONTACT_POINT_ID WORK_FAX_CONTACT_POINT_ID
661 , CPT_CELL.CONTACT_POINT_ID CELL_PHONE_CONTACT_POINT_ID
662 , CPT_PAGER.CONTACT_POINT_ID PAGER_CONTACT_POINT_ID
663 , CPT_EMAIL.CONTACT_POINT_ID EMAIL_CONTACT_POINT_ID
664 , 'N' STATUS
665 FROM HZ_PARTIES REL_CONTACT
666 , HZ_PARTIES ORG
667 , HZ_PARTIES PERSON
668 , AR_LOOKUPS ARLK
669 , HZ_ORG_CONTACTS HOC
670 , HZ_RELATIONSHIPS REL
671 , HZ_PARTY_SITES SITE
672 , HZ_LOCATIONS LOC
673 , HZ_PARTY_PREFERENCES HPP
674 , CAC_SYNC_CONTACT_MAPPINGS CSPM
675 , (SELECT CP.OWNER_TABLE_ID
676 , CP.CONTACT_POINT_ID
677 , 'WORK;VOICE' CONTACT_TYPE
678 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
679 CP.PHONE_AREA_CODE,
680 CP.PHONE_NUMBER,
681 CP.PHONE_EXTENSION) PHONE_NUMBER
682
683 FROM HZ_CONTACT_POINTS CP
684 , HZ_PARTY_PREFERENCES PREF
685 WHERE PREF.PARTY_ID = b_person_party_id
686 AND PREF.CATEGORY = b_bookmark_category
687 AND PREF.PREFERENCE_CODE = b_bookmark_preference
688 AND PREF.MODULE = b_bookmark_module
689 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
690 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
691 FROM HZ_CONTACT_POINTS C
692 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
693 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
694 AND C.CONTACT_POINT_TYPE = 'PHONE'
695 AND C.PHONE_LINE_TYPE = 'GEN'
696 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
697 AND C.STATUS = 'A')
698 ) CPT_WP
699 , (SELECT CP.OWNER_TABLE_ID
700 , CP.CONTACT_POINT_ID
701 , 'HOME;VOICE' CONTACT_TYPE
702 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
703 CP.PHONE_AREA_CODE,
704 CP.PHONE_NUMBER,
705 CP.PHONE_EXTENSION) PHONE_NUMBER
706
707 FROM HZ_CONTACT_POINTS CP
708 , HZ_PARTY_PREFERENCES PREF
709 WHERE PREF.PARTY_ID = b_person_party_id
710 AND PREF.CATEGORY = b_bookmark_category
711 AND PREF.PREFERENCE_CODE = b_bookmark_preference
712 AND PREF.MODULE = b_bookmark_module
713 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
714 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
715 FROM HZ_CONTACT_POINTS C
716 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
717 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
718 AND C.CONTACT_POINT_TYPE = 'PHONE'
719 AND C.PHONE_LINE_TYPE = 'GEN'
720 AND C.CONTACT_POINT_PURPOSE = 'PERSONAL'
721 AND C.STATUS = 'A')
722 ) CPT_HP
723 , (SELECT CP.OWNER_TABLE_ID
724 , CP.CONTACT_POINT_ID
725 , 'WORK;FAX' CONTACT_TYPE
726 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
727 CP.PHONE_AREA_CODE,
728 CP.PHONE_NUMBER,
729 CP.PHONE_EXTENSION) PHONE_NUMBER
730
731 FROM HZ_CONTACT_POINTS CP
732 , HZ_PARTY_PREFERENCES PREF
733 WHERE PREF.PARTY_ID = b_person_party_id
734 AND PREF.CATEGORY = b_bookmark_category
735 AND PREF.PREFERENCE_CODE = b_bookmark_preference
736 AND PREF.MODULE = b_bookmark_module
737 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
738 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
739 FROM HZ_CONTACT_POINTS C
740 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
741 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
742 AND C.CONTACT_POINT_TYPE = 'PHONE'
743 AND C.PHONE_LINE_TYPE = 'FAX'
744 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
745 AND C.STATUS = 'A')
746 ) CPT_WF
747 , (SELECT CP.OWNER_TABLE_ID
748 , CP.CONTACT_POINT_ID
752 CP.PHONE_NUMBER,
749 , 'CELL;VOICE' CONTACT_TYPE
750 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
751 CP.PHONE_AREA_CODE,
753 CP.PHONE_EXTENSION) PHONE_NUMBER
754
755 FROM HZ_CONTACT_POINTS CP
756 , HZ_PARTY_PREFERENCES PREF
757 WHERE PREF.PARTY_ID = b_person_party_id
758 AND PREF.CATEGORY = b_bookmark_category
762 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
759 AND PREF.PREFERENCE_CODE = b_bookmark_preference
760 AND PREF.MODULE = b_bookmark_module
761 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
763 FROM HZ_CONTACT_POINTS C
764 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
765 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
766 AND C.CONTACT_POINT_TYPE = 'PHONE'
767 AND C.PHONE_LINE_TYPE = 'MOBILE'
768 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
769 AND C.STATUS = 'A')
770 ) CPT_CELL
771 , (SELECT CP.OWNER_TABLE_ID
772 , CP.CONTACT_POINT_ID
773 , 'PAGER' CONTACT_TYPE
774 , CAC_SYNC_CONTACTS_PVT.FORMAT_PHONE(CP.PHONE_COUNTRY_CODE,
775 CP.PHONE_AREA_CODE,
776 CP.PHONE_NUMBER,
777 CP.PHONE_EXTENSION) PHONE_NUMBER
778
779 FROM HZ_CONTACT_POINTS CP
780 , HZ_PARTY_PREFERENCES PREF
781 WHERE PREF.PARTY_ID = b_person_party_id
782 AND PREF.CATEGORY = b_bookmark_category
783 AND PREF.PREFERENCE_CODE = b_bookmark_preference
784 AND PREF.MODULE = b_bookmark_module
785 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
786 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
787 FROM HZ_CONTACT_POINTS C
788 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
789 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
790 AND C.CONTACT_POINT_TYPE = 'PHONE'
791 AND C.PHONE_LINE_TYPE = 'PAGER'
792 AND NVL(C.CONTACT_POINT_PURPOSE,'BUSINESS') = 'BUSINESS'
793 AND C.STATUS = 'A')
794 ) CPT_PAGER
795 , (SELECT CP.OWNER_TABLE_ID
796 , CP.CONTACT_POINT_ID
797 , 'EMAIL' CONTACT_TYPE
798 , CP.EMAIL_FORMAT
799 , CP.EMAIL_ADDRESS
800 FROM HZ_CONTACT_POINTS CP
801 , HZ_PARTY_PREFERENCES PREF
802 WHERE PREF.PARTY_ID = b_person_party_id
803 AND PREF.CATEGORY = b_bookmark_category
804 AND PREF.PREFERENCE_CODE = b_bookmark_preference
805 AND PREF.MODULE = b_bookmark_module
806 AND CP.OWNER_TABLE_ID = PREF.VALUE_NUMBER
807 AND CP.CONTACT_POINT_ID = (SELECT MIN(CONTACT_POINT_ID)
808 FROM HZ_CONTACT_POINTS C
809 WHERE C.OWNER_TABLE_ID = PREF.VALUE_NUMBER
810 AND C.OWNER_TABLE_NAME = 'HZ_PARTIES'
811 AND C.CONTACT_POINT_TYPE = 'EMAIL'
812 AND C.STATUS = 'A')
813 ) CPT_EMAIL
814 WHERE HPP.PARTY_ID = b_person_party_id
815 AND HPP.CATEGORY = b_bookmark_category
816 AND HPP.PREFERENCE_CODE = b_bookmark_preference
817 AND HPP.MODULE = b_bookmark_module
818 AND REL_CONTACT.PARTY_ID = HPP.VALUE_NUMBER
819 AND ORG.PARTY_TYPE = 'ORGANIZATION'
820 AND PERSON.PARTY_TYPE = 'PERSON'
821 AND ARLK.LOOKUP_TYPE(+) = 'CONTACT_TITLE'
822 AND ARLK.LOOKUP_CODE(+) = PERSON.PERSON_PRE_NAME_ADJUNCT
823 AND REL.PARTY_ID = REL_CONTACT.PARTY_ID
824 AND REL.DIRECTIONAL_FLAG = 'F'
825 AND PERSON.PARTY_ID = REL.SUBJECT_ID
826 AND ORG.PARTY_ID = REL.OBJECT_ID
827 AND HOC.PARTY_RELATIONSHIP_ID = REL.RELATIONSHIP_ID
828 AND SITE.PARTY_ID (+)= REL_CONTACT.PARTY_ID
829 AND SITE.STATUS (+)= 'A'
830 AND SITE.IDENTIFYING_ADDRESS_FLAG (+)= 'Y'
831 AND LOC.LOCATION_ID(+) = SITE.LOCATION_ID
832 AND CPT_WP.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
836 AND CPT_PAGER.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
833 AND CPT_HP.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
834 AND CPT_WF.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
835 AND CPT_CELL.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
837 AND CPT_EMAIL.OWNER_TABLE_ID(+) = REL_CONTACT.PARTY_ID
838 AND CSPM.CONTACT_PARTY_ID(+) = REL_CONTACT.PARTY_ID;
839
840
841 PROCEDURE PREPARE_FASTSYNC
842 /*******************************************************************************
843 **
844 ** PREPARE_FASTSYNC
845 **
846 ** Performs a fetch from HZ tables based on the timestamp
847 ** and populates the CAC_SYNC_CONTACT_TEMPS table
848 **
849 *******************************************************************************/
850 (
851 p_api_version IN NUMBER -- Standard version parameter
852 , p_init_msg_list IN VARCHAR2 -- Standard message initialization flag
853 , p_principal_id IN NUMBER -- Principal ID
854 , p_person_party_id IN NUMBER -- Person Party ID
855 , p_sync_anchor IN DATE -- Timestamp for sync anchor
856 , x_return_status OUT NOCOPY VARCHAR2 -- Standard API return status parameter
857 , x_msg_count OUT NOCOPY NUMBER -- Standard return parameter for the no of msgs in the stack
858 , x_msg_data OUT NOCOPY VARCHAR2 -- Standard return parameter for the msgs in the stack
859 )
860 IS
861 rec_new_contacts c_new_contacts%ROWTYPE;
862 rec_updated_contacts c_updated_contacts%ROWTYPE;
863 rec_deleted_contacts c_deleted_contacts%ROWTYPE;
864 l_bkm_module VARCHAR2(50);
865 l_bkm_category VARCHAR2(30);
866 l_bkm_preference_code VARCHAR2(30);
867 l_count NUMBER;
868 BEGIN
869 l_bkm_module := fnd_profile.value('CAC_SYNC_CONT_BKM_MODULE');
870 l_bkm_category := fnd_profile.value('CAC_SYNC_CONT_BKM_CATEGORY');
871 l_bkm_preference_code := fnd_profile.value('CAC_SYNC_CONT_BKM_PREFERENCE_CODE');
872
873 SAVEPOINT prepare_fastsync_sp;
874
875 cac_sync_contact_util_pvt.log(p_message=>'Entering PREPARE_FASTSYNC...',
876 p_msg_level=>fnd_log.level_procedure,
877 p_module_prefix=>'PREPARE_FASTSYNC');
878
879 IF p_init_msg_list IS NULL OR
880 fnd_api.to_boolean (p_init_msg_list)
881 THEN
882 fnd_msg_pub.initialize;
883 END IF;
884
885 -- Emptying the CAC_SYNC_CONTACT_TEMPS table not reqd as using ON COMMIT DELETE ROWS clause
886 cac_sync_contact_util_pvt.log(p_message=>'Querying the new contacts...',
887 p_msg_level=>fnd_log.level_procedure,
888 p_module_prefix=>'PREPARE_FASTSYNC');
889
890 l_count := 0;
891 FOR rec_new_contacts IN c_new_contacts (p_principal_id,
892 p_person_party_id,
893 l_bkm_module,
894 l_bkm_category,
895 l_bkm_preference_code)
896 LOOP
897 INSERT INTO CAC_SYNC_CONTACT_TEMPS
898 (sync_contact_temp_id
899 ,rel_contact_party_id
900 ,org_party_id
901 ,org_name
902 ,person_party_id
903 ,person_full_name
904 ,person_name_delimited
905 ,job_title
906 ,department
907 ,address_delimited
908 ,work_phone_number
909 ,home_phone_number
910 ,work_fax_number
911 ,cell_number
912 ,pager_number
913 ,email_format
914 ,email_address
915 ,party_site_id
916 ,work_phone_contact_point_id
917 ,home_phone_contact_point_id
918 ,work_fax_contact_point_id
919 ,cell_phone_contact_point_id
920 ,pager_contact_point_id
921 ,email_contact_point_id
922 ,status
923 )
924 VALUES
925 (rec_new_contacts.sync_contact_temp_id
926 ,rec_new_contacts.rel_contact_party_id
927 ,rec_new_contacts.org_party_id
928 ,rec_new_contacts.org_name
929 ,rec_new_contacts.person_party_id
930 ,rec_new_contacts.person_full_name
931 ,rec_new_contacts.person_name_delimited
932 ,rec_new_contacts.job_title
933 ,rec_new_contacts.department
934 ,rec_new_contacts.address_delimited
935 ,rec_new_contacts.work_phone_number
936 ,rec_new_contacts.home_phone_number
937 ,rec_new_contacts.work_fax_number
938 ,rec_new_contacts.cell_number
939 ,rec_new_contacts.pager_number
940 ,rec_new_contacts.email_format
941 ,rec_new_contacts.email_address
942 ,rec_new_contacts.party_site_id
943 ,rec_new_contacts.work_phone_contact_point_id
944 ,rec_new_contacts.home_phone_contact_point_id
945 ,rec_new_contacts.work_fax_contact_point_id
946 ,rec_new_contacts.cell_phone_contact_point_id
947 ,rec_new_contacts.pager_contact_point_id
948 ,rec_new_contacts.email_contact_point_id
949 ,rec_new_contacts.status
950 );
951 l_count := l_count + 1;
952 END LOOP;
953
954 cac_sync_contact_util_pvt.log(p_message=>'The number of new contacts queried: '||l_count,
955 p_msg_level=>fnd_log.level_procedure,
956 p_module_prefix=>'PREPARE_FASTSYNC');
957
958 cac_sync_contact_util_pvt.log(p_message=>'Querying the updated contacts...',
959 p_msg_level=>fnd_log.level_procedure,
960 p_module_prefix=>'PREPARE_FASTSYNC');
961
962 l_count := 0;
966 l_bkm_module,
963 FOR rec_updated_contacts IN c_updated_contacts (p_principal_id,
964 p_person_party_id,
965 p_sync_anchor,
967 l_bkm_category,
968 l_bkm_preference_code)
969 LOOP
970 INSERT INTO CAC_SYNC_CONTACT_TEMPS
971 (sync_contact_temp_id
972 ,rel_contact_party_id
973 ,org_party_id
974 ,org_name
975 ,person_party_id
976 ,person_full_name
977 ,person_name_delimited
978 ,job_title
979 ,department
980 ,address_delimited
981 ,work_phone_number
982 ,home_phone_number
983 ,work_fax_number
984 ,cell_number
985 ,pager_number
986 ,email_format
987 ,email_address
988 ,party_site_id
989 ,work_phone_contact_point_id
990 ,home_phone_contact_point_id
991 ,work_fax_contact_point_id
992 ,cell_phone_contact_point_id
993 ,pager_contact_point_id
994 ,email_contact_point_id
995 ,status
996 )
997 VALUES
998 (rec_updated_contacts.sync_contact_temp_id
999 ,rec_updated_contacts.rel_contact_party_id
1000 ,rec_updated_contacts.org_party_id
1001 ,rec_updated_contacts.org_name
1002 ,rec_updated_contacts.person_party_id
1003 ,rec_updated_contacts.person_full_name
1004 ,rec_updated_contacts.person_name_delimited
1005 ,rec_updated_contacts.job_title
1006 ,rec_updated_contacts.department
1007 ,rec_updated_contacts.address_delimited
1008 ,rec_updated_contacts.work_phone_number
1009 ,rec_updated_contacts.home_phone_number
1010 ,rec_updated_contacts.work_fax_number
1011 ,rec_updated_contacts.cell_number
1012 ,rec_updated_contacts.pager_number
1013 ,rec_updated_contacts.email_format
1014 ,rec_updated_contacts.email_address
1015 ,rec_updated_contacts.party_site_id
1016 ,rec_updated_contacts.work_phone_contact_point_id
1017 ,rec_updated_contacts.home_phone_contact_point_id
1018 ,rec_updated_contacts.work_fax_contact_point_id
1019 ,rec_updated_contacts.cell_phone_contact_point_id
1020 ,rec_updated_contacts.pager_contact_point_id
1021 ,rec_updated_contacts.email_contact_point_id
1022 ,rec_updated_contacts.status
1023 );
1024 l_count := l_count + 1;
1025 END LOOP;
1026
1027 cac_sync_contact_util_pvt.log(p_message=>'The number of updated contacts queried: '||l_count,
1028 p_msg_level=>fnd_log.level_procedure,
1029 p_module_prefix=>'PREPARE_FASTSYNC');
1030
1031 cac_sync_contact_util_pvt.log(p_message=>'Querying the deleted contacts...',
1032 p_msg_level=>fnd_log.level_procedure,
1033 p_module_prefix=>'PREPARE_FASTSYNC');
1034
1035 l_count := 0;
1036 FOR rec_deleted_contacts IN c_deleted_contacts (p_principal_id,
1037 p_person_party_id,
1038 l_bkm_module,
1039 l_bkm_category,
1040 l_bkm_preference_code)
1041 LOOP
1042 INSERT INTO CAC_SYNC_CONTACT_TEMPS
1043 (sync_contact_temp_id
1044 ,rel_contact_party_id
1045 ,org_party_id
1046 ,org_name
1047 ,person_party_id
1048 ,person_full_name
1049 ,person_name_delimited
1050 ,job_title
1051 ,department
1052 ,address_delimited
1053 ,work_phone_number
1054 ,home_phone_number
1055 ,work_fax_number
1056 ,cell_number
1057 ,pager_number
1058 ,email_format
1059 ,email_address
1060 ,party_site_id
1061 ,work_phone_contact_point_id
1062 ,home_phone_contact_point_id
1063 ,work_fax_contact_point_id
1064 ,cell_phone_contact_point_id
1065 ,pager_contact_point_id
1066 ,email_contact_point_id
1067 ,status
1068 )
1069 VALUES
1070 (rec_deleted_contacts.sync_contact_temp_id
1071 ,rec_deleted_contacts.rel_contact_party_id
1072 ,rec_deleted_contacts.org_party_id
1073 ,rec_deleted_contacts.org_name
1074 ,rec_deleted_contacts.person_party_id
1075 ,rec_deleted_contacts.person_full_name
1076 ,rec_deleted_contacts.person_name_delimited
1077 ,rec_deleted_contacts.job_title
1078 ,rec_deleted_contacts.department
1079 ,rec_deleted_contacts.address_delimited
1080 ,rec_deleted_contacts.work_phone_number
1081 ,rec_deleted_contacts.home_phone_number
1082 ,rec_deleted_contacts.work_fax_number
1083 ,rec_deleted_contacts.cell_number
1084 ,rec_deleted_contacts.pager_number
1085 ,rec_deleted_contacts.email_format
1086 ,rec_deleted_contacts.email_address
1087 ,rec_deleted_contacts.party_site_id
1088 ,rec_deleted_contacts.work_phone_contact_point_id
1089 ,rec_deleted_contacts.home_phone_contact_point_id
1090 ,rec_deleted_contacts.work_fax_contact_point_id
1091 ,rec_deleted_contacts.cell_phone_contact_point_id
1092 ,rec_deleted_contacts.pager_contact_point_id
1093 ,rec_deleted_contacts.email_contact_point_id
1094 ,rec_deleted_contacts.status
1095 );
1099 cac_sync_contact_util_pvt.log(p_message => 'The number of deleted contacts queried: '||l_count,
1096 l_count := l_count + 1;
1097 END LOOP;
1098
1100 p_msg_level => fnd_log.level_procedure,
1101 p_module_prefix=>'PREPARE_FASTSYNC');
1102
1103 x_return_status := fnd_api.g_ret_sts_success;
1104
1105 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1106
1107 cac_sync_contact_util_pvt.log(p_message => 'Leaving PREPARE_FASTSYNC...: '||x_return_status,
1108 p_msg_level => fnd_log.level_procedure,
1109 p_module_prefix=>'PREPARE_FASTSYNC');
1110 EXCEPTION
1111 WHEN fnd_api.g_exc_unexpected_error
1112 THEN
1113 ROLLBACK TO prepare_fastsync_sp;
1114 x_return_status := fnd_api.g_ret_sts_unexp_error;
1115 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1116 cac_sync_contact_util_pvt.log(p_message => x_msg_data,
1117 p_msg_level => fnd_log.level_exception,
1118 p_module_prefix=>'PREPARE_FASTSYNC');
1119 WHEN OTHERS
1120 THEN
1121 ROLLBACK TO prepare_fastsync_sp;
1122 fnd_message.set_name ('CAC', 'CAC_CONTACT_UNKNOWN_ERROR');
1123 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1124 fnd_msg_pub.add;
1125 x_return_status := fnd_api.g_ret_sts_unexp_error;
1126 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1127 cac_sync_contact_util_pvt.log(p_message => x_msg_data,
1128 p_msg_level => fnd_log.level_exception,
1129 p_module_prefix=>'PREPARE_FASTSYNC');
1130 END PREPARE_FASTSYNC;
1131
1132 PROCEDURE PREPARE_SLOWSYNC
1133 /*******************************************************************************
1134 **
1135 ** PREPARE_SLOWSYNC
1136 **
1137 ** Performs a fetch from HZ tables for ALL synchronizable contact records
1138 ** and populates the CAC_SYNC_CONTACT_TEMPS table
1139 **
1140 *******************************************************************************/
1141 (
1142 p_api_version IN NUMBER -- Standard version parameter
1143 , p_init_msg_list IN VARCHAR2 -- Standard message initialization flag
1144 , p_person_party_id IN NUMBER -- Person Party ID
1145 , x_return_status OUT NOCOPY VARCHAR2 -- Standard API return status parameter
1146 , x_msg_count OUT NOCOPY NUMBER -- Standard return parameter for the no of msgs in the stack
1147 , x_msg_data OUT NOCOPY VARCHAR2 -- Standard return parameter for the msgs in the stack
1148 )
1149 IS
1150 rec_all_contacts c_all_contacts%ROWTYPE;
1151 l_bkm_module VARCHAR2(50);
1152 l_bkm_category VARCHAR2(30);
1153 l_bkm_preference_code VARCHAR2(30);
1154 l_count NUMBER;
1155 BEGIN
1156 l_bkm_module := fnd_profile.value('CAC_SYNC_CONT_BKM_MODULE');
1157 l_bkm_category := fnd_profile.value('CAC_SYNC_CONT_BKM_CATEGORY');
1158 l_bkm_preference_code := fnd_profile.value('CAC_SYNC_CONT_BKM_PREFERENCE_CODE');
1159
1160 SAVEPOINT prepare_slowsync_sp;
1161
1162 cac_sync_contact_util_pvt.log(p_message => 'Entering PREPARE_SLOWSYNC...: ',
1163 p_msg_level => fnd_log.level_procedure,
1164 p_module_prefix=>'PREPARE_SLOWSYNC');
1165
1166 IF p_init_msg_list IS NULL OR
1167 fnd_api.to_boolean (p_init_msg_list)
1168 THEN
1169 fnd_msg_pub.initialize;
1170 END IF;
1171
1172 -- Emptying the CAC_SYNC_CONTACT_TEMPS table not reqd as using ON COMMIT DELETE ROWS clause
1173
1174 cac_sync_contact_util_pvt.log(p_message=>'Retrieving all the contacts...',
1175 p_msg_level=>fnd_log.level_procedure,
1176 p_module_prefix=>'PREPARE_SLOWSYNC');
1177
1178 l_count := 0;
1179 FOR rec_all_contacts IN c_all_contacts (p_person_party_id,
1180 l_bkm_module,
1181 l_bkm_category,
1182 l_bkm_preference_code)
1183 LOOP
1184 INSERT INTO CAC_SYNC_CONTACT_TEMPS
1185 (sync_contact_temp_id
1186 ,rel_contact_party_id
1187 ,org_party_id
1188 ,org_name
1189 ,person_party_id
1190 ,person_full_name
1191 ,person_name_delimited
1192 ,job_title
1193 ,department
1194 ,address_delimited
1195 ,work_phone_number
1196 ,home_phone_number
1197 ,work_fax_number
1198 ,cell_number
1199 ,pager_number
1200 ,email_format
1201 ,email_address
1202 ,party_site_id
1203 ,work_phone_contact_point_id
1204 ,home_phone_contact_point_id
1205 ,work_fax_contact_point_id
1206 ,cell_phone_contact_point_id
1207 ,pager_contact_point_id
1208 ,email_contact_point_id
1209 ,status
1210 )
1211 VALUES
1212 (rec_all_contacts.sync_contact_temp_id
1213 ,rec_all_contacts.rel_contact_party_id
1214 ,rec_all_contacts.org_party_id
1215 ,rec_all_contacts.org_name
1216 ,rec_all_contacts.person_party_id
1217 ,rec_all_contacts.person_full_name
1218 ,rec_all_contacts.person_name_delimited
1219 ,rec_all_contacts.job_title
1220 ,rec_all_contacts.department
1221 ,rec_all_contacts.address_delimited
1222 ,rec_all_contacts.work_phone_number
1223 ,rec_all_contacts.home_phone_number
1227 ,rec_all_contacts.email_format
1224 ,rec_all_contacts.work_fax_number
1225 ,rec_all_contacts.cell_number
1226 ,rec_all_contacts.pager_number
1228 ,rec_all_contacts.email_address
1229 ,rec_all_contacts.party_site_id
1230 ,rec_all_contacts.work_phone_contact_point_id
1231 ,rec_all_contacts.home_phone_contact_point_id
1232 ,rec_all_contacts.work_fax_contact_point_id
1233 ,rec_all_contacts.cell_phone_contact_point_id
1234 ,rec_all_contacts.pager_contact_point_id
1235 ,rec_all_contacts.email_contact_point_id
1236 ,rec_all_contacts.status
1237 );
1238 l_count := l_count + 1;
1239 END LOOP;
1240
1241 cac_sync_contact_util_pvt.log(p_message => 'The number of contacts queried: '||l_count,
1242 p_msg_level => fnd_log.level_procedure,
1243 p_module_prefix=>'PREPARE_SLOWSYNC');
1244
1245 x_return_status := fnd_api.g_ret_sts_success;
1246
1247 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1248
1249 cac_sync_contact_util_pvt.log(p_message => 'Leaving PREPARE_SLOWSYNC...:'||x_return_status,
1250 p_msg_level => fnd_log.level_procedure,
1251 p_module_prefix=>'PREPARE_SLOWSYNC');
1252 EXCEPTION
1253 WHEN fnd_api.g_exc_unexpected_error
1254 THEN
1255 ROLLBACK TO prepare_slowsync_sp;
1256 x_return_status := fnd_api.g_ret_sts_unexp_error;
1257 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1258 cac_sync_contact_util_pvt.log(p_message => x_msg_data,
1259 p_msg_level => fnd_log.level_exception,
1260 p_module_prefix=>'PREPARE_SLOWSYNC');
1261
1262 WHEN OTHERS
1263 THEN
1264 ROLLBACK TO prepare_slowsync_sp;
1265 fnd_message.set_name ('CAC', 'CAC_CONTACT_UNKNOWN_ERROR');
1266 fnd_message.set_token ('P_TEXT', SQLCODE || SQLERRM);
1267 fnd_msg_pub.add;
1268 x_return_status := fnd_api.g_ret_sts_unexp_error;
1269 fnd_msg_pub.count_and_get (p_count => x_msg_count, p_data => x_msg_data);
1270 cac_sync_contact_util_pvt.log(p_message => x_msg_data,
1271 p_msg_level => fnd_log.level_exception,
1272 p_module_prefix=>'PREPARE_SLOWSYNC');
1273
1274 END PREPARE_SLOWSYNC;
1275
1276 FUNCTION FORMAT_PHONE
1277 /*******************************************************************************
1278 **
1279 ** FORMAT_PHONE
1280 **
1281 ** Format phone fucntion calling CAC_SYNC_CONTACT_UTIL_PVT.format_phone()
1282 **
1283 *******************************************************************************/
1284 ( p_country_code IN VARCHAR2
1285 , p_area_code IN VARCHAR2
1286 , p_phone_number IN VARCHAR2
1287 , p_phone_extension IN VARCHAR2
1288 ) RETURN VARCHAR2
1289 IS
1290 BEGIN
1291 cac_sync_contact_util_pvt.log(p_message => 'Entering FORMAT_PHONE...',
1292 p_msg_level => fnd_log.level_procedure,
1293 p_module_prefix=>'FORMAT_PHONE');
1294
1295 cac_sync_contact_util_pvt.log(p_message => 'Calling CAC_SYNC_CONTACT_UTIL_PVT.FORMAT_PHONE...',
1296 p_msg_level => fnd_log.level_procedure,
1297 p_module_prefix=>'FORMAT_PHONE');
1298
1299 RETURN CAC_SYNC_CONTACT_UTIL_PVT.FORMAT_PHONE(
1300 p_country_code => p_country_code,
1301 p_area_code => p_area_code,
1302 p_phone_number => p_phone_number,
1303 p_phone_extension => p_phone_extension,
1304 p_delimit_country => '+',
1305 p_delimit_area_code => '( )',
1306 p_delimit_phone_number => ' ',
1307 p_delimit_extension => ' x'
1308 );
1309
1310 END FORMAT_PHONE;
1311
1312
1313 END CAC_SYNC_CONTACTS_PVT;