1 PACKAGE BODY HZ_WF_SYNCH AS
2 /* $Header: ARHWFSNB.pls 120.16.12020000.2 2013/03/12 10:20:21 rgokavar ship $ */
3
4 /*===========================================================================+
5 | FUNCTION |
6 | propogate_user_role |
7 | |
8 | DESCRIPTION |
9 | Propogates user information to WF API's |
10 | |
11 | SCOPE - Public |
12 | |
13 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
14 | NONE |
15 | |
16 | ARGUMENTS : IN: |
17 | p_subscription_guid in raw, |
18 | |
19 | OUT: |
20 | |
21 | IN/ OUT: |
22 | p_event in out NOCOPY wf_event_t |
23 | |
24 | |
25 | |
26 | RETURNS : VARCHAR2 |
27 | |
28 | NOTES |
29 | The create or update relationship should call |
30 | SynchGroupWFUserRole procedure, which in turn synchs the |
31 | work flow tables. |
32 | |
33 | MODIFICATION HISTORY |
34 | |
35 | 03-Jan-2003 Porkodi Chinnandar Bug 2627161: Modified the code |
36 | to populate proper values before|
37 | calling WFSYNCH procedures |
38 | |
39 +===========================================================================*/
40
41 FUNCTION propagate_user_role(
42 p_subscription_guid IN RAW,
43 p_event IN OUT NOCOPY wf_event_t)
44 RETURN VARCHAR2
45 IS
46 l_key VARCHAR2(240) := p_event.GetEventKey();
47 l_user_orig_system VARCHAR2(100) := 'HZ_PARTY';
48 l_user_orig_system_id NUMBER;
49 l_role_orig_system VARCHAR2(100) := 'HZ_PARTY';
50 l_role_orig_system_id NUMBER;
51 l_start_date DATE DEFAULT NULL;
52 l_expiration_date DATE DEFAULT NULL;
53 l_relationship_id NUMBER;
54 l_match_string VARCHAR2(240) := NULL;
55 id NUMBER;
56 l_debug_prefix VARCHAR2(30) := '';
57 BEGIN
58 SAVEPOINT propagate_user_role;
59
60 -- Check if API is called in debug mode. If yes, enable debug.
61 --enable_debug;
62
63 --Checks for the event and synchs data with the workflow table
64 --by calling propagate_user_role
65 IF (l_key like 'oracle.apps.ar.hz.Relationship.create%') OR
66 (l_key like 'oracle.apps.ar.hz.Relationship.update%') THEN
67 id := p_event.getValueForParameter('RELATIONSHIP_ID');
68 SynchGroupWFUserRole(id);
69 -- Debug info.
70 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
71 hz_utility_v2pub.debug(
72 p_message=>'After calling the propagate_user_role for the relationship_id '||id,
73 p_prefix =>l_debug_prefix,
74 p_msg_level=>fnd_log.level_statement);
75 END IF;
76 END IF; -- chk for Relationship Create or Update Events
77 RETURN 'SUCCESS';
78 EXCEPTION
79 WHEN OTHERS THEN
80 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
81 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
82 FND_MSG_PUB.ADD;
83 WF_CORE.CONTEXT('HZ_WF_SYNCH',
84 'propagate_user_role',
85 p_event.getEventName(),
86 p_subscription_guid);
87 WF_EVENT.setErrorInfo(p_event, 'ERROR');
88 ROLLBACK TO propagate_user_role;
89 RETURN 'ERROR';
90 END; -- propagate_user_role()
91
92
93 /*===========================================================================+
94 | PROCEDURE |
95 | propogate_role |
96 | |
97 | DESCRIPTION |
98 | Propogates user information to WF API's |
99 | |
100 | SCOPE - Public |
101 | |
102 | EXETERNAL PROCEDURES/FUNCTIONS ACCESSED |
103 | WF_LOCAL_SYNCH |
104 | |
105 | ARGUMENTS : IN: |
106 | p_subscription_guid in raw, |
107 | |
108 | OUT: |
109 | |
110 | IN/ OUT: |
111 | p_event in out NOCOPY wf_event_t | |
112 | |
113 | |
114 | |
115 | RETURNS : NONE |
116 | |
117 | NOTES |
118 | The create or update of Person/PersonLanguage/OrgContact/ |
119 | Group/ContactPoint events should call any of the |
120 | SynchPersonWFRole/SynchContactWFRole/SynchGroupWFRole or all |
121 | of them to synch with workflow tables. |
122 | |
123 | MODIFICATION HISTORY |
124 | |
125 | 03-Jan-2003 Porkodi Chinnandar Bug 2627161: Modified the code |
126 | to populate proper values before|
127 | calling WFSYNCH procedures |
128 | |
129 +===========================================================================*/
130
131 FUNCTION propagate_role(
132 p_subscription_guid IN RAW,
133 p_event IN OUT NOCOPY wf_event_t)
134 RETURN VARCHAR2
135 IS
136 l_key VARCHAR2(240) := p_event.GetEventKey();
137 l_orig_system VARCHAR2(100) := 'HZ_PARTY';
138 l_orig_system_id NUMBER;
139 l_attributes wf_parameter_list_t;
140 l_start_date DATE DEFAULT NULL;
141 l_expiration_date DATE DEFAULT NULL;
142 l_match_string VARCHAR2(240) := null;
143 l_primary_lang_indicator VARCHAR2(10) := 'N';
144 l_temp_party_id NUMBER;
145 wf_party_id NUMBER;
146 wf_lang_user_ref_id NUMBER;
147 wf_party_type VARCHAR2(30);
148 wf_party_relationship_id NUMBER;
149 wf_owner_table_id NUMBER;
150 l_debug_prefix VARCHAR2(30) := '';
151
152 Cursor find_contacts is
153 select party_id
154 from hz_relationships rel,
155 hz_org_contacts org
156 where rel.relationship_id=org.party_relationship_id and
157 Subject_table_name = 'HZ_PARTIES' and
158 Object_table_name = 'HZ_PARTIES' and
159 Directional_flag = 'F' and
160 subject_id = wf_party_id;
161
162 Cursor org_update is
163 select party_id
164 from hz_relationships rel,
165 hz_org_contacts org
166 where rel.relationship_id=org.party_relationship_id and
167 Subject_table_name = 'HZ_PARTIES' and
168 Object_table_name = 'HZ_PARTIES' and
169 Directional_flag = 'F' and
170 object_id = l_match_string;
171
172 BEGIN
173 SAVEPOINT propagate_role;
174
175 -- Check if API is called in debug mode. If yes, enable debug.
176 --enable_debug;
177
178 IF (l_key LIKE 'oracle.apps.ar.hz.Person.create%') THEN
179
180 -- Debug info.
181 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
182 hz_utility_v2pub.debug(
183 p_message=>l_key,
184 p_prefix =>l_debug_prefix,
185 p_msg_level=>fnd_log.level_statement);
186 END IF;
187
188 wf_party_id := p_event.getValueForParameter('PARTY_ID');
189 SynchPersonWFRole(wf_party_id);
190
191 -- Debug info.
192 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
193 hz_utility_v2pub.debug(
194 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
195 p_prefix =>l_debug_prefix,
196 p_msg_level=>fnd_log.level_statement);
197 END IF;
198
199 ELSIF (l_key LIKE 'oracle.apps.ar.hz.Person.update%') THEN
200 -- Debug info.
201 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
202 hz_utility_v2pub.debug(
203 p_message=>l_key,
204 p_prefix =>l_debug_prefix,
205 p_msg_level=>fnd_log.level_statement);
206 END IF;
207
208 wf_party_id := p_event.getValueForParameter('PARTY_ID');
209 SynchPersonWFRole(wf_party_id,TRUE,TRUE);
210
211 -- Debug info.
212 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
213 hz_utility_v2pub.debug(
214 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
215 p_prefix =>l_debug_prefix,
216 p_msg_level=>fnd_log.level_statement);
217 END IF;
218
219 --While updating the person details, his details in contact should
220 --also be updated in workflow tables
221 For Contact in find_contacts Loop
222 SynchContactWFRole(Contact.party_id,TRUE,TRUE);
223 End Loop;
224
225 -- Debug info.
226 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
227 hz_utility_v2pub.debug(
228 p_message=>'After updating the contact details in WF for subject partyid: '||wf_party_id,
229 p_prefix =>l_debug_prefix,
230 p_msg_level=>fnd_log.level_statement);
231 END IF;
232
233 ELSIF (l_key LIKE 'oracle.apps.ar.hz.PersonLanguage.create%') THEN
234 -- Debug info.
235 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
236 hz_utility_v2pub.debug(
237 p_message=>l_key,
238 p_prefix =>l_debug_prefix,
239 p_msg_level=>fnd_log.level_statement);
240 END IF;
241
242 wf_lang_user_ref_id := p_event.getValueForParameter('LANGUAGE_USE_REFERENCE_ID');
243
244 -- anonymous block to find the person corresponding to language
245 BEGIN
246 select p.party_id, party_type
247 into wf_party_id, wf_party_type
248 from hz_parties p, hz_person_language l
249 where p.party_id= l.party_id and
250 primary_language_indicator='Y' and
251 l.status ='A' and
252 language_use_reference_id = wf_lang_user_ref_id;
253 EXCEPTION
254 WHEN NO_DATA_FOUND THEN
255 -- Debug info.
256 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
257 hz_utility_v2pub.debug(
258 p_message=>'No person found for language_use_reference_id: '||wf_lang_user_ref_id,
259 p_prefix =>l_debug_prefix,
260 p_msg_level=>fnd_log.level_statement);
261 hz_utility_v2pub.debug(
262 p_message=>'No person party_id found for event '||l_key,
263 p_prefix =>l_debug_prefix,
264 p_msg_level=>fnd_log.level_statement);
265 END IF;
266 END; -- end of anonymous block to find the person corresponding to language
267
268 --For personLanguage create event it tries to synch all the
269 --personLanguage related details in workflow table
270 IF (wf_party_type = 'PERSON') THEN
271 -- sync person
272 SynchPersonWFRole(wf_party_id);
273 -- Debug info.
274 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
275 hz_utility_v2pub.debug(
276 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
277 p_prefix =>l_debug_prefix,
278 p_msg_level=>fnd_log.level_statement);
279 END IF;
280
281 -- sync all the contacts
282 For Contact in find_contacts
283 Loop
284 SynchContactWFRole(Contact.party_id);
285 End Loop;
286 -- Debug info.
287 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
288 hz_utility_v2pub.debug(
289 p_message=>'After updating the contact details in WF for subject partyid: '||wf_party_id,
290 p_prefix =>l_debug_prefix,
291 p_msg_level=>fnd_log.level_statement);
292 END IF;
293
294 ELSIF (wf_party_type = 'GROUP') THEN
295 -- sync group
296 SynchGroupWFRole(wf_party_id);
297 -- Debug info.
298 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
299 hz_utility_v2pub.debug(
300 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
301 p_prefix =>l_debug_prefix,
302 p_msg_level=>fnd_log.level_statement);
303 END IF;
304 END IF;
305
306 --For personLanguage update event it tries to synch all the
307 --personLanguage related details in workflow table
308
309 ELSIF (l_key LIKE 'oracle.apps.ar.hz.PersonLanguage.update%') THEN
310 -- Debug info.
311 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
312 hz_utility_v2pub.debug(
313 p_message=>l_key,
314 p_prefix =>l_debug_prefix,
315 p_msg_level=>fnd_log.level_statement);
316 END IF;
317 -- read the lang ref id from param
318 wf_lang_user_ref_id := p_event.getValueForParameter('LANGUAGE_USE_REFERENCE_ID');
319 -- anonymous block to find the person corresponding to language
320 BEGIN
321 select p.party_id, party_type
322 into wf_party_id, wf_party_type
323 from hz_parties p, hz_person_language l
324 where p.party_id= l.party_id and
325 -- primary_language_indicator='Y' and
326 -- l.status ='A' and
327 language_use_reference_id = wf_lang_user_ref_id;
328 EXCEPTION
329 WHEN NO_DATA_FOUND THEN
330 -- Debug info.
331 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
332 hz_utility_v2pub.debug(
333 p_message=>'No person found for language_use_reference_id: '||wf_lang_user_ref_id,
334 p_prefix =>l_debug_prefix,
335 p_msg_level=>fnd_log.level_statement);
336 hz_utility_v2pub.debug(
337 p_message=>'No person party_id found for event '||l_key,
338 p_prefix =>l_debug_prefix,
339 p_msg_level=>fnd_log.level_statement);
340 END IF;
341 END; -- end of anonymous block to find the person corresponding to language
342
343 IF (wf_party_type = 'PERSON') THEN
344 -- sync person
345 SynchPersonWFRole(wf_party_id,TRUE,TRUE);
346 -- Debug info.
347 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
348 hz_utility_v2pub.debug(
349 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
350 p_prefix =>l_debug_prefix,
351 p_msg_level=>fnd_log.level_statement);
352 END IF;
353 -- sync all the contacts
354 For Contact in find_contacts Loop
355 SynchContactWFRole(Contact.party_id,TRUE,TRUE);
356 End Loop;
357 -- Debug info.
358 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
359 hz_utility_v2pub.debug(
360 p_message=>'After updating the contact details in WF for subject partyid: '||wf_party_id,
361 p_prefix =>l_debug_prefix,
362 p_msg_level=>fnd_log.level_statement);
363 END IF;
364 ELSIF (wf_party_type = 'GROUP') THEN
365 -- sync group
366 SynchGroupWFRole(wf_party_id,TRUE,TRUE);
367 -- Debug info.
368 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
369 hz_utility_v2pub.debug(
370 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
371 p_prefix =>l_debug_prefix,
372 p_msg_level=>fnd_log.level_statement);
373 END IF;
374 END IF; -- check for party type ends in personLang.update event
375
376 --On creation of OrgContact this code synchs this details with
377 --work flow table
378 ELSIF (l_key LIKE 'oracle.apps.ar.hz.OrgContact.create%') THEN
379 -- Debug info.
380 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
381 hz_utility_v2pub.debug(
382 p_message=>l_key,
383 p_prefix =>l_debug_prefix,
384 p_msg_level=>fnd_log.level_statement);
385 END IF;
386 l_match_string := p_event.getValueForParameter('ORG_CONTACT_ID');
387
388 -- given an orgContactId, get the relParty (Contact) that must be synced
389 -- anonymous block to find the orgContact
390 BEGIN
391 select party_id
392 into wf_party_id
393 from hz_org_contacts org, hz_relationships rel
394 where org.party_relationship_id=rel.relationship_id and
395 Subject_table_name = 'HZ_PARTIES' and
396 Object_table_name = 'HZ_PARTIES' and
397 Directional_flag = 'F' and
398 org.org_contact_id=l_match_string;
399
400 SynchContactWFRole(wf_party_id);
401 -- Debug info.
402 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
403 hz_utility_v2pub.debug(
404 p_message=>'After updating the contact details in WF for partyid: '||wf_party_id,
405 p_prefix =>l_debug_prefix,
406 p_msg_level=>fnd_log.level_statement);
407 END IF;
408 EXCEPTION
409 WHEN NO_DATA_FOUND THEN
410 -- Debug info.
411 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
412 hz_utility_v2pub.debug(
413 p_message=>'No orgContact found for orgContactId: '||l_match_string,
414 p_prefix =>l_debug_prefix,
415 p_msg_level=>fnd_log.level_statement);
416 hz_utility_v2pub.debug(
417 p_message=>'No rel party_id found for event '||l_key,
418 p_prefix =>l_debug_prefix,
419 p_msg_level=>fnd_log.level_statement);
420 END IF;
421 END; -- end of anonymous block to find the orgContact
422
423 --On updation of OrgContact this code synchs this details with
424 --work flow table
425 ELSIF (l_key like 'oracle.apps.ar.hz.OrgContact.update%') THEN
426 -- Debug info.
427 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
428 hz_utility_v2pub.debug(
429 p_message=>l_key,
430 p_prefix =>l_debug_prefix,
431 p_msg_level=>fnd_log.level_statement);
432 END IF;
433 l_match_string := p_event.getValueForParameter('ORG_CONTACT_ID');
434 -- given an orgContactId, get the relParty (Contact) that must be synced
435 -- anonymous block to find the orgContact
436 BEGIN
437 select party_id
438 into wf_party_id
439 from hz_org_contacts org, hz_relationships rel
440 where org.party_relationship_id=rel.relationship_id and
441 Subject_table_name = 'HZ_PARTIES' and
442 Object_table_name = 'HZ_PARTIES' and
443 Directional_flag = 'F' and
444 org.org_contact_id=l_match_string;
445 -- sync contact
446 SynchContactWFRole(wf_party_id,TRUE,TRUE);
447 -- Debug info.
448 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
449 hz_utility_v2pub.debug(
450 p_message=>'After updating the contact details in WF for partyid: '||wf_party_id,
451 p_prefix =>l_debug_prefix,
452 p_msg_level=>fnd_log.level_statement);
453 END IF;
454 EXCEPTION
455 WHEN NO_DATA_FOUND THEN
456 -- Debug info.
457 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
458 hz_utility_v2pub.debug(
459 p_message=>'No orgContact found for orgContactId: '||l_match_string,
460 p_prefix =>l_debug_prefix,
461 p_msg_level=>fnd_log.level_statement);
462 hz_utility_v2pub.debug(
463 p_message=>'No rel party_id found for event '||l_key,
464 p_prefix =>l_debug_prefix,
465 p_msg_level=>fnd_log.level_statement);
466 END IF;
467 END; -- end of anonymous block to find the orgContact
468
469 --On creation of Group party this code synchs this details with
470 --work flow table
471 ELSIF (l_key LIKE 'oracle.apps.ar.hz.Group.create%') THEN
472 -- Debug info.
473 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
474 hz_utility_v2pub.debug(
475 p_message=>l_key,
476 p_prefix =>l_debug_prefix,
477 p_msg_level=>fnd_log.level_statement);
478 END IF;
479 l_match_string := p_event.getValueForParameter('PARTY_ID');
480 SynchGroupWFRole(l_match_string);
481 -- Debug info.
482 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
483 hz_utility_v2pub.debug(
484 p_message=>'After SynchGroupWFRole() for party_id: '||l_match_string,
485 p_prefix =>l_debug_prefix,
486 p_msg_level=>fnd_log.level_statement);
487 END IF;
488 --On updation of Group party this code synchs this details with
489 --work flow table
490 ELSIF (l_key like 'oracle.apps.ar.hz.Group.update%') THEN
491 -- Debug info.
492 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
493 hz_utility_v2pub.debug(
494 p_message=>l_key,
495 p_prefix =>l_debug_prefix,
496 p_msg_level=>fnd_log.level_statement);
497 END IF;
498 l_match_string := p_event.getValueForParameter('PARTY_ID');
499 SynchGroupWFRole(l_match_string,TRUE,TRUE);
500 -- Debug info.
501 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
502 hz_utility_v2pub.debug(
503 p_message=>'After SynchGroupWFRole() for party_id: '||l_match_string,
504 p_prefix =>l_debug_prefix,
505 p_msg_level=>fnd_log.level_statement);
506 END IF;
507 --On creation of ContactPoint this code synchs this details with
508 --work flow table
509 ELSIF (l_key LIKE 'oracle.apps.ar.hz.ContactPoint.create%') THEN
510 -- Debug info.
511 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
512 hz_utility_v2pub.debug(
513 p_message=>l_key,
514 p_prefix =>l_debug_prefix,
515 p_msg_level=>fnd_log.level_statement);
516 END IF;
517 l_match_string := p_event.getValueForParameter('CONTACT_POINT_ID');
518 -- anonymous block to find the party corresponding to CP
519 BEGIN
520 select owner_table_id, party_type
521 into wf_party_id, wf_party_type
522 from hz_contact_points pt, hz_parties
523 where pt.owner_table_id = party_id and
524 pt.contact_point_type = 'EMAIL' and
525 pt.status ='A' and
526 pt.primary_flag='Y' and
527 pt.owner_table_name ='HZ_PARTIES' and
528 pt.contact_point_id = l_match_string;
529 EXCEPTION
530 WHEN NO_DATA_FOUND THEN
531 -- Debug info.
532 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
533 hz_utility_v2pub.debug(
534 p_message=>'No party_id found for contact point id: '||l_match_string,
535 p_prefix =>l_debug_prefix,
536 p_msg_level=>fnd_log.level_statement);
537 hz_utility_v2pub.debug(
538 p_message=>'No party_id found for event '||l_key,
539 p_prefix =>l_debug_prefix,
540 p_msg_level=>fnd_log.level_statement);
541 END IF;
542 END; -- end of anonymous block to find the party corresponding to CP
543
544 IF (wf_party_type = 'PERSON') THEN
545 -- sync person
546 SynchPersonWFRole(wf_party_id);
547 -- Debug info.
548 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
549 hz_utility_v2pub.debug(
550 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
551 p_prefix =>l_debug_prefix,
552 p_msg_level=>fnd_log.level_statement);
553 END IF;
554 ELSIF (wf_party_type = 'PARTY_RELATIONSHIP') then
555 -- sync Contact
556 SynchContactWFRole(wf_party_id);
557 -- Debug info.
558 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
559 hz_utility_v2pub.debug(
560 p_message=>'After updating the contact details in WF for subject partyid: '||wf_party_id,
561 p_prefix =>l_debug_prefix,
562 p_msg_level=>fnd_log.level_statement);
563 END IF;
564 ELSIF (wf_party_type = 'GROUP') THEN
565 -- sync Group
566 SynchGroupWFRole(wf_party_id);
567 -- Debug info.
568 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
569 hz_utility_v2pub.debug(
570 p_message=>'After SynchGroupWFRole() for party_id: '||wf_party_id,
571 p_prefix =>l_debug_prefix,
572 p_msg_level=>fnd_log.level_statement);
573 END IF;
574 END IF; -- check of partyType ends
575
576 --On updation of ContactPoint this code synchs this details with
577 --work flow table
578 ELSIF (l_key like 'oracle.apps.ar.hz.ContactPoint.update%') THEN
579 -- Debug info.
580 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
581 hz_utility_v2pub.debug(
582 p_message=>l_key,
583 p_prefix =>l_debug_prefix,
584 p_msg_level=>fnd_log.level_statement);
585 END IF;
586 l_match_string := p_event.getValueForParameter('CONTACT_POINT_ID');
587 -- anonymous block to find the party corresponding to CP
588 BEGIN
589 select owner_table_id, party_type
590 into wf_party_id, wf_party_type
591 from hz_contact_points pt, hz_parties
592 where pt.owner_table_id = party_id and
593 pt.contact_point_type = 'EMAIL' and
594 pt.owner_table_name ='HZ_PARTIES' and
595 pt.contact_point_id = l_match_string;
596 EXCEPTION
597 WHEN NO_DATA_FOUND THEN
598 -- Debug info.
599 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
600 hz_utility_v2pub.debug(
601 p_message=>'No party_id found for contact point id: '||l_match_string,
602 p_prefix =>l_debug_prefix,
603 p_msg_level=>fnd_log.level_statement);
604 hz_utility_v2pub.debug(
605 p_message=>'No party_id found for event '||l_key,
606 p_prefix =>l_debug_prefix,
607 p_msg_level=>fnd_log.level_statement);
608 END IF;
609 END; -- end of anonymous block to find the party corresponding to CP
610
611 IF (wf_party_type = 'PERSON') THEN
612 -- sync person
613 SynchPersonWFRole(wf_party_id,TRUE,TRUE);
614 -- Debug info.
615 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
616 hz_utility_v2pub.debug(
617 p_message=>'After calling the SynchPersonWFRole for party '||wf_party_id,
618 p_prefix =>l_debug_prefix,
619 p_msg_level=>fnd_log.level_statement);
620 END IF;
621 ELSIF (wf_party_type = 'PARTY_RELATIONSHIP') then
622 -- sync Contact
623 SynchContactWFRole(wf_party_id,TRUE,TRUE);
624 -- Debug info.
625 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
626 hz_utility_v2pub.debug(
627 p_message=>'After updating the contact details in WF for subject partyid: '||wf_party_id,
628 p_prefix =>l_debug_prefix,
629 p_msg_level=>fnd_log.level_statement);
630 END IF;
631 ELSIF (wf_party_type = 'GROUP') THEN
632 -- sync Group
633 SynchGroupWFRole(wf_party_id,TRUE,TRUE);
634 -- Debug info.
635 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
636 hz_utility_v2pub.debug(
637 p_message=>'After SynchGroupWFRole() for party_id: '||wf_party_id,
638 p_prefix =>l_debug_prefix,
639 p_msg_level=>fnd_log.level_statement);
640 END IF;
641 END IF; -- check of partyType ends
642
643 --On updation of Organization this code synchs this details with
644 --work flow table
645 ELSIF (l_key like 'oracle.apps.ar.hz.Organization.update%') THEN
646 -- Debug info.
647 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
648 hz_utility_v2pub.debug(
649 p_message=>l_key,
650 p_prefix =>l_debug_prefix,
651 p_msg_level=>fnd_log.level_statement);
652 END IF;
653 l_match_string := p_event.getValueForParameter('PARTY_ID');
654 -- sync all orgContacts for the Org
655 For orga_update in org_update Loop
656 SynchContactWFRole(orga_update.party_id,TRUE,TRUE);
657 End Loop;
658 -- Debug info.
659 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
660 hz_utility_v2pub.debug(p_message=>'After updating the Contact details in WF for Org party_id: '||l_match_string,
661 p_prefix =>l_debug_prefix,
662 p_msg_level=>fnd_log.level_statement);
663 END IF;
664
665 END IF; -- end of event key check
666
667 RETURN 'SUCCESS';
668
669 EXCEPTION
670 WHEN OTHERS THEN
671 FND_MESSAGE.SET_NAME('AR', 'HZ_API_OTHERS_EXCEP');
672 FND_MESSAGE.SET_TOKEN('ERROR' ,SQLERRM);
673 FND_MSG_PUB.ADD;
674 WF_CORE.CONTEXT('HZ_WF_SYNCH',
675 'propagate_user_role',
676 p_event.getEventName(),
677 p_subscription_guid);
678 WF_EVENT.setErrorInfo(p_event, 'ERROR');
679 ROLLBACK TO propagate_role;
680 RETURN 'ERROR';
681 END; -- end of propagate_role()
682
683 -------------------------------------------------------------------------
684 /**
685 * PROCEDURE SynchPersonWFRole
686 *
687 * DESCRIPTION
688 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_role method
689 *
690 *
691 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
692 * WF_LOCAL_SYNCH.propagate_role
693 *
694 * ARGUMENTS
695 * IN:
696 * PartyId party_id for which the synch has
697 * to be done
698 * p_update Update flag
699 * p_overwrite Overwrite flag
700 *
701 * IN/OUT:
702 *
703 * OUT:
704 *
705 *
706 * NOTES
707 *
708 * MODIFICATION HISTORY
709 *
710 * 03-Jan-2003 Porkodi Chinnandar o Created.
711 * 13-Mar-2003 Leena Sampemane o Person needs to be created as a
712 * wf_user.
713 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
714 * 12-Mar-2013 Sudhir Gokavarapu o Bug#15992479
715 * expiration_date value changed in Select statement.
716 * as Party does not have end date we will get
717 * sysdate as expiration date for Inactive parties.
718 *
719 */
720 -------------------------------------------------------------------------
721
722 PROCEDURE SYNCHPERSONWFROLE (
723 PartyId IN Number,
724 p_update IN Boolean Default False ,
725 p_overwrite IN Boolean Default False )
726 IS
727 List wf_parameter_list_t;
728 user_name VARCHAR2(25);
729 display_name VARCHAR2(360);
730 description VARCHAR2(360);
731 notification_pref VARCHAR2(30);
732 language VARCHAR2(30);
733 territory VARCHAR2(30);
734 email_address VARCHAR2(325);
735 fax VARCHAR2(80);
736 status VARCHAR2(8);
737 expiration_date DATE;
738 start_date DATE DEFAULT NULL;
739 system VARCHAR2(10) := 'HZ_PARTY';
740 system_id VARCHAR2(15);
741 l_debug_prefix VARCHAR2(30) := 'SYNCPERWFROLE';
742
743 BEGIN
744 -- debug info
745 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
746 hz_utility_v2pub.DEBUG
747 (p_message=>'SYNCHPERSONWFROLE()+',
748 p_prefix =>l_debug_prefix,
749 p_msg_level=>fnd_log.level_statement);
750 END IF;
751 SAVEPOINT SynchPersonWFRole;
752 /*
753 As part of Bug 4390816, following changes were done to this procedure:
754 Select stmt changes:
755 1. email_format is no longer hardcoded to MAILTEXT when null
756 2. email format is truncated to 8 char
757 3. email address is truncated to 320 char
758
759 BUG 4957312 changes
760 1. email_format a.k.a Notification Preference defaulting was re-inroduced.
761
762 Details-
763 If the email is present and has a notification preference, then it was used.
764 If the notification preference was not present and the email was present,
765 then MAILTEXT is deafulted. This is to be consistent with email defaulting
766 in Public API Bug4359226.
767 If the email itself was not available, notification preference was defaulted
768 to Query.
769 */
770
771 BEGIN -- anonymous block to select the records from TCA Registry
772 SELECT
773 'HZ_PARTY:'||p.party_id, -- Username
774 p.party_name, -- DisplayName
775 p.party_name, -- description
776 nvl2(p.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY'), -- notification_pref
777 fl.nls_language, -- preferredLanguage
778 fl.nls_territory, -- orclNLSTerritory
779 substrb(p.email_address,1,320), -- mail
780 NULL, -- fax
781 decode(p.status, 'A', 'ACTIVE', 'INACTIVE'), -- orclIsEnabled
782 -- NULL, -- ExpirationDate
783 decode(p.status, 'A', NULL, SYSDATE), -- ExpirationDate
784 p.party_id -- System Id
785 INTO
786 user_name,
787 display_name,
788 description,
789 notification_pref,
790 language,
791 territory,
792 email_address,
793 fax,
794 status,
795 expiration_date,
796 system_id
797 FROM
798 HZ_PARTIES p,
799 HZ_CONTACT_POINTS cp,
800 HZ_PERSON_LANGUAGE pl,
801 FND_LANGUAGES fl
802 WHERE
803 p.party_id = PartyId
804 AND p.party_type = 'PERSON'
805 AND cp.owner_table_name(+) = 'HZ_PARTIES'
806 AND cp.owner_table_id (+) = p.party_id
807 AND cp.contact_point_type(+) = 'EMAIL'
808 AND cp.primary_flag(+) = 'Y'
809 AND cp.status(+) = 'A'
810 AND pl.party_id (+) = p.party_id
811 AND pl.primary_language_indicator(+) = 'Y'
812 AND pl.status(+) = 'A'
813 AND pl.language_name = fl.language_code(+);
814 EXCEPTION
815 WHEN NO_DATA_FOUND THEN
816 -- Debug info.
817 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
818 hz_utility_v2pub.DEBUG
819 (p_message=>'no data found to sync',
820 p_prefix =>l_debug_prefix,
821 p_msg_level=>fnd_log.level_statement);
822 END IF;
823 END; -- anonymous block to fetch the TCA Registry records ends
824
825 IF (system_id IS NOT NULL) THEN
826 -- if there is a valid record from TCA Registry to Sync
827 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
828 hz_utility_v2pub.DEBUG
829 (p_message=>'Orig system ID:'||system_id,
830 p_prefix =>l_debug_prefix,
831 p_msg_level=>fnd_log.level_statement);
832 END IF;
833
834 -- Add the TCA registry Data as parameters to wf_event
835 -- This is needed as the WF API access the wf_event structure for
836 -- the information passed.
837
838 wf_event.AddParameterToList('User_Name', user_name, List);
839 wf_event.AddParameterToList('DisplayName', display_name, List);
840 wf_event.AddParameterToList('Description', description, List);
841 wf_event.AddParameterToList('orclWorkFlowNotificationPref', notification_pref, List);
842 wf_event.AddParameterToList('preferredLanguage', language, List);
843 wf_event.AddParameterToList('OrclNLSTerritory', territory, List);
844 wf_event.AddParameterToList('Mail', email_address, List);
845 wf_event.AddParameterToList('FacsimileTelephoneNumber', fax, List);
846 wf_event.AddParameterToList('orclisEnabled', status, List);
847 wf_event.AddParameterToList('ExpirationDate', expiration_date, List);
848 wf_event.AddParameterToList('orclWFOrigSystem',system, List);
849 wf_event.AddParameterToList('orclWFOrigSystemID',system_id, List);
850
851 if (p_update = TRUE) then
852 wf_event.AddParameterToList('UpdateOnly', 'TRUE' , List);
853 end if;
854
855 if (p_overwrite = TRUE) then
856 wf_event.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE', List);
857 end if;
858
859 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
860 hz_utility_v2pub.DEBUG
861 (p_message=>'bfr Calling WF_LOCAL_SYNCH.propagate_role API',
862 p_prefix =>l_debug_prefix,
863 p_msg_level=>fnd_log.level_statement);
864 END IF;
865
866 -- call WF API for Synchronization
867 WF_LOCAL_SYNCH.propagate_user(
868 system,
869 system_id,
870 List,
871 start_date,
872 expiration_date);
873 END IF; -- check for valid TCA id ends
874
875 -- debug info
876 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
877 hz_utility_v2pub.DEBUG
878 (p_message=>'SYNCHPERSONWFROLE()-',
879 p_prefix =>l_debug_prefix,
880 p_msg_level=>fnd_log.level_statement);
881 END IF;
882
883 END SYNCHPERSONWFROLE;
884 -------------------------------------------------------------------------
885 /**
886 * PROCEDURE SynchContactWFRole
887 *
888 * DESCRIPTION
889 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_role method
890 *
891 *
892 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
893 * WF_LOCAL_SYNCH.propagate_role
894 *
895 * ARGUMENTS
896 * IN:
897 * PartyId party_id for which the synch has
898 * to be done
899 * p_update Update flag
900 * p_overwrite overwrite flag
901 *
902 * IN/OUT:
903 *
904 * OUT:
905 *
906 *
907 * NOTES
908 *
909 * MODIFICATION HISTORY
910 *
911 * 03-Jan-2003 Porkodi Chinnandar o Created.
912 * 11-Mar-2003 Leena Sampemane - Contact needs to be created as a WF User
913 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
914 *
915 *
916 */
917 -------------------------------------------------------------------------
918 PROCEDURE SYNCHCONTACTWFROLE (
919 PartyId IN Number,
920 p_update IN Boolean Default False ,
921 p_overwrite IN Boolean Default False )
922 IS
923 List WF_PARAMETER_LIST_T;
924 system VARCHAR2(10) := 'HZ_PARTY';
925 l_tbl SYNC_TBL_TYPE;
926 l_rec_count NUMBER := 0;
927 l_debug_prefix VARCHAR2(30) := 'SYNCCNTWFROLE';
928
929 /*
930 As part of Bug 4390816, following changes were done to this procedure:
931
932 Select stmt changes:
933 1. Email_format is no longer hardcoded to MAILTEXT when null
934 2. Email format is truncated to 8 char
935 3. Email address is truncated to 320 char
936 4. StartDate, ExpirationDate are populated from relationship table
937 5. Unnecessary joining for 'object' to hz_parties removed
938 6. Removed directional flag filtering. This would mean that there
939 are relationship records that must sync up (unlike the previous
940 design). Hence need to re-write the SQL as a cursor.
941
942 Param changes:
943 1, Added wf_event.AddParameterToList('StartDate', start_date, List);
944
945 BUG 4957312 changes
946 1. email_format a.k.a Notification Preference defaulting was re-inroduced.
947
948 Details-
949 If the email is present and has a notification preference, then it was used.
950 If the notification preference was not present and the email was present,
951 then MAILTEXT is deafulted. This is to be consistent with email defaulting
952 in Public API Bug4359226.
953 If the email itself was not available, notification preference was defaulted
954 to Query.
955
956 2. Notification Preference selected must belong to the Email chosen.
957 In case of email of a contact, the notification preference must belong
958 to the same email (of that contact).
959
960 3. Incase of person to person rel, directional flag was re-intorduced.
961
962 Details-
963 This was to avoid selecting two rows in case of Person-Person relationships.
964
965 4. Person-Group relationships are no longer considered in SyncContactWFRole().
966 */
967
968 CURSOR c_rel (c_p_rel_party_id IN NUMBER) IS
969 SELECT
970 'HZ_PARTY:'||to_char(pr.party_id) -- Username
971 ,per.party_name -- DisplayName
972 ,prp.party_name -- description
973 ,nvl2(prp.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY') -- notification_pref
974 ,fl.nls_language -- language
975 ,fl.nls_territory -- territory
976 ,substrb(prp.email_address,1,320) -- email_address
977 ,NULL -- fax
978 ,DECODE(prp.status,'A','ACTIVE','INACTIVE') -- status
979 ,pr.start_date -- startDate
980 ,DECODE(pr.status, 'A', pr.end_date, 'I',
981 (CASE
982 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) < 0 THEN pr.end_date
983 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) > 0 THEN SYSDATE
984 ELSE SYSDATE
985 END), SYSDATE) -- ExpirationDate --Bug#5209709 fix
986 ,pr.party_id -- system_id
987 FROM
988 hz_relationships pr
989 ,hz_org_contacts oc
990 ,hz_parties prp -- party relationship party
991 ,hz_parties per
992 ,hz_contact_points cp
993 ,hz_person_language pl
994 ,fnd_languages fl
995 WHERE
996 pr.party_id = c_p_rel_party_id
997 AND pr.subject_table_name = 'HZ_PARTIES'
998 AND pr.object_table_name = 'HZ_PARTIES'
999 AND pr.subject_id = per.party_id
1000 AND per.party_type = 'PERSON'
1001 AND ((pr.object_type = 'PERSON' AND pr.directional_flag = 'F')
1002 OR pr.object_type = 'ORGANIZATION')
1003 AND pr.relationship_id = oc.party_relationship_id
1004 AND prp.party_id = pr.party_id
1005 AND prp.party_type = 'PARTY_RELATIONSHIP'
1006 AND cp.owner_table_name(+) = 'HZ_PARTIES'
1007 AND cp.owner_table_id (+) = prp.party_id
1008 AND cp.contact_point_type(+) = 'EMAIL'
1009 AND cp.email_address(+) = prp.email_address
1010 AND cp.primary_flag(+) = 'Y'
1011 AND cp.status(+) = 'A'
1012 AND per.party_id = pl.party_id(+)
1013 AND pl.primary_language_indicator(+) = 'Y'
1014 AND pl.status(+) = 'A'
1015 AND pl.language_name = fl.language_code(+);
1016
1017 BEGIN
1018 SAVEPOINT SynchContactWFRole;
1019 -- Debug info.
1020 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1021 hz_utility_v2pub.DEBUG
1022 (p_message=>'SynchContactWFRole()+',
1023 p_prefix =>l_debug_prefix,
1024 p_msg_level=>fnd_log.level_statement);
1025 END IF;
1026
1027 OPEN c_rel(PartyId);
1028 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1029 hz_utility_v2pub.DEBUG
1030 (p_message=>'OPENED THE CURSOR',
1031 p_prefix =>l_debug_prefix,
1032 p_msg_level=>fnd_log.level_statement);
1033 END IF;
1034 FETCH c_rel BULK COLLECT INTO l_tbl; -- select records from TCA Registry
1035 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1036 hz_utility_v2pub.DEBUG
1037 (p_message=>'Bulk collected the data',
1038 p_prefix =>l_debug_prefix,
1039 p_msg_level=>fnd_log.level_statement);
1040 END IF;
1041 CLOSE c_rel;
1042
1043 l_rec_count := l_tbl.COUNT;
1044 --Bug#8587352
1045 --When there are no records l_tbl.FIRST and l_tbl.LAST
1046 --returns NULL value and its raising an error message.
1047 --OTHERS is added at Exception block.
1048 IF l_rec_count > 0 THEN
1049 FOR i IN l_tbl.FIRST..l_tbl.LAST
1050 LOOP
1051 -- for each of the selected record from TCA Registry
1052 -- perform WF Synchronization.
1053 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1054 hz_utility_v2pub.DEBUG
1055 (p_message=>'At '||i||' Rec',
1056 p_prefix =>l_debug_prefix,
1057 p_msg_level=>fnd_log.level_statement);
1058 END IF;
1059
1060 IF (l_tbl(i).system_id IS NOT NULL) THEN
1061 -- for each of the valid record with a partyId
1062
1063 List := WF_PARAMETER_LIST_T();
1064 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1065 hz_utility_v2pub.DEBUG
1066 (p_message=>'Orig system ID: '||l_tbl(i).system_id,
1067 p_prefix =>l_debug_prefix,
1068 p_msg_level=>fnd_log.level_statement);
1069 END IF;
1070
1071 -- Add the TCA registry Data as parameters to wf_event
1072 -- This is needed as the WF API access the wf_event structure for
1073 -- the information passed.
1074
1075 wf_event.AddParameterToList('User_Name', l_tbl(i).user_name, List);
1076 wf_event.AddParameterToList('DisplayName', l_tbl(i).display_name, List);
1077 wf_event.AddParameterToList('Description', l_tbl(i).description, List);
1078 wf_event.AddParameterToList('orclWorkFlowNotificationPref', l_tbl(i).notification_pref, List);
1079 wf_event.AddParameterToList('preferredLanguage', l_tbl(i).language, List);
1080 wf_event.AddParameterToList('OrclNLSTerritory', l_tbl(i).territory, List);
1081 wf_event.AddParameterToList('Mail', l_tbl(i).email_address, List);
1082 wf_event.AddParameterToList('FacsimileTelephoneNumber', l_tbl(i).fax, List);
1083 wf_event.AddParameterToList('orclisEnabled', l_tbl(i).status, List);
1084 wf_event.AddParameterToList('StartDate', l_tbl(i).start_date, List);
1085 wf_event.AddParameterToList('ExpirationDate', l_tbl(i).expiration_date, List);
1086 wf_event.AddParameterToList('orclWFOrigSystem',system, List);
1087 wf_event.AddParameterToList('orclWFOrigSystemID',l_tbl(i).system_id, List);
1088
1089 IF (p_update = TRUE) then
1090 wf_event.AddParameterToList('UpdateOnly', 'TRUE', List);
1091 END IF;
1092
1093 IF (p_overwrite = TRUE) THEN
1094 wf_event.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE', List);
1095 END IF;
1096
1097 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1098 hz_utility_v2pub.DEBUG
1099 (p_message=>'Before WF API Call',
1100 p_prefix =>l_debug_prefix,
1101 p_msg_level=>fnd_log.level_statement);
1102 END IF;
1103
1104 -- call WF API for Synchronization
1105
1106 WF_LOCAL_SYNCH.propagate_user(
1107 system,
1108 l_tbl(i).system_id,
1109 List,
1110 l_tbl(i).start_date,
1111 l_tbl(i).expiration_date);
1112 END IF; -- check for valid TCA id ends
1113 END LOOP; -- looping through all selected record ends
1114 ELSE
1115 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1116 hz_utility_v2pub.DEBUG
1117 (p_message=>'No record found at c_rel cursor. ',
1118 p_prefix =>l_debug_prefix,
1119 p_msg_level=>fnd_log.level_statement);
1120 END IF;
1121
1122 END IF; -- For l_rec_count check.
1123 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1124 hz_utility_v2pub.DEBUG
1125 (p_message=>'SynchContactWFRole()-',
1126 p_prefix =>l_debug_prefix,
1127 p_msg_level=>fnd_log.level_statement);
1128 END IF;
1129
1130 EXCEPTION
1131 WHEN NO_DATA_FOUND THEN
1132 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1133 hz_utility_v2pub.DEBUG
1134 (p_message=>'No data found excep:'||sqlerrm,
1135 p_prefix =>l_debug_prefix,
1136 p_msg_level=>fnd_log.level_statement);
1137 END IF;
1138 -- no error is raised as not finding any record to sync is not an error.
1139 WHEN OTHERS THEN
1140 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1141 hz_utility_v2pub.DEBUG
1142 (p_message=>'Error raised :'||sqlerrm,
1143 p_prefix =>l_debug_prefix,
1144 p_msg_level=>fnd_log.level_statement);
1145 END IF;
1146 END SynchContactWFRole;
1147
1148 -------------------------------------------------------------------------
1149 /**
1150 * PROCEDURE SynchGroupWFRole
1151 *
1152 * DESCRIPTION
1153 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_role method
1154 * When the party type is group.
1155 *
1156 *
1157 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1158 * WF_LOCAL_SYNCH.propagate_role
1159 *
1160 * ARGUMENTS
1161 * IN:
1162 * PartyId party_id for which the synch has
1163 * to be done
1164 * p_update Update flag
1165 * p_overwrite Overwrite flag
1166 *
1167 * IN/OUT:
1168 *
1169 * OUT:
1170 *
1171 *
1172 * NOTES
1173 *
1174 * MODIFICATION HISTORY
1175 *
1176 * 03-Jan-2003 Porkodi Chinnandar o Created.
1177 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
1178 *
1179 */
1180
1181 -------------------------------------------------------------------------
1182 PROCEDURE SYNCHGROUPWFROLE (
1183 PartyId IN Number,
1184 p_update IN Boolean Default False ,
1185 p_overwrite IN Boolean Default False )
1186
1187 IS
1188 List wf_parameter_list_t;
1189 user_name varchar2(25);
1190 display_name varchar2(360);
1191 description varchar2(360);
1192 notification_pref varchar2(30);
1193 language varchar2(30);
1194 territory varchar2(30);
1195 email_address varchar2(325);
1196 fax varchar2(80);
1197 status varchar2(8);
1198 expiration_date Date;
1199 start_date Date DEFAULT NULL;
1200 system varchar2(10) := 'HZ_GROUP';
1201 system_id varchar2(15);
1202 l_debug_prefix VARCHAR2(30) := 'SYNCGRWFROLE';
1203
1204 BEGIN
1205 SAVEPOINT SynchGroupWFRole;
1206 -- Debug info.
1207 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1208 hz_utility_v2pub.DEBUG
1209 (p_message=>'SynchGroupWFRole()+',
1210 p_prefix =>l_debug_prefix,
1211 p_msg_level=>fnd_log.level_statement);
1212 END IF;
1213
1214 -- As part of Bug 4390816, following changes were done to this procedure:
1215 -- select stmt changes
1216 -- 1. email_format is no longer hardcoded to MAILTEXT when null
1217 -- 2. email format is truncated to 8 char
1218 -- 3. email address is truncated to 320 char
1219 -- 4. mission statement is truncated to 1000 chars
1220
1221 BEGIN -- anonymous block to select the TCA Registry
1222 -- Debug info.
1223 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1224 hz_utility_v2pub.DEBUG
1225 (p_message=>'Bfr Selecting the data',
1226 p_prefix =>l_debug_prefix,
1227 p_msg_level=>fnd_log.level_statement);
1228 END IF;
1229 /*
1230 BUG 4957312 changes
1231 1. email_format a.k.a Notification Preference defaulting was re-inroduced.
1232
1233 Details-
1234 If the email is present and has a notification preference, then it was used.
1235 If the notification preference was not present and the email was present,
1236 then MAILTEXT is deafulted. This is to be consistent with email defaulting
1237 in Public API Bug4359226.
1238 If the email itself was not available, notification preference was defaulted
1239 to Query.
1240
1241 2. Notification Preference selected must belong to the Email chosen.
1242 In case of email of a contact, the notification preference must belong
1243 to the same email (of that contact).
1244 */
1245 SELECT
1246 'HZ_GROUP:'||p.party_id, -- USER_NAME, note the :
1247 p.party_name, -- DisplayName
1248 substrb(p.mission_statement,1,1000), -- DESCRIPTION
1249 nvl2(p.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY'), -- notification_pref
1250 fl.nls_language, -- Language
1251 fl.nls_territory, -- Territory
1252 substrb(p.email_address,1,320), -- email mail
1253 NULL, -- fax
1254 decode(p.status, 'A', 'ACTIVE', 'INACTIVE'), -- status
1255 NULL, -- ExpirationDate
1256 p.party_id -- System Id
1257 INTO
1258 user_name,
1259 display_name,
1260 description,
1261 notification_pref,
1262 language,
1263 territory,
1264 email_address,
1265 fax,
1266 status,
1267 expiration_date,
1268 system_id
1269 FROM
1270 HZ_PARTIES p,
1271 HZ_CONTACT_POINTS cp,
1272 HZ_PERSON_LANGUAGE pl,
1273 FND_LANGUAGES fl
1274 WHERE
1275 p.party_id = PartyId
1276 AND p.party_type = 'GROUP'
1277 AND cp.owner_table_name(+) = 'HZ_PARTIES'
1278 AND cp.owner_table_id (+) = p.party_id
1279 AND cp.contact_point_type(+) = 'EMAIL'
1280 AND cp.primary_flag(+) = 'Y'
1281 AND cp.status(+) = 'A'
1282 AND pl.party_id (+) = p.party_id
1283 AND pl.primary_language_indicator(+) = 'Y'
1284 AND pl.status(+) = 'A'
1285 AND pl.language_name = fl.language_code(+);
1286 EXCEPTION
1287 WHEN NO_DATA_FOUND THEN
1288 -- Debug info.
1289 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1290 hz_utility_v2pub.DEBUG
1291 (p_message=>'no data found to sync',
1292 p_prefix =>l_debug_prefix,
1293 p_msg_level=>fnd_log.level_statement);
1294 END IF;
1295 -- no error is raised as not finding any record to sync is not an error.
1296 END; -- anonymous block to query the TCA Registry ends
1297
1298 IF (system_id IS NOT NULL) THEN
1299
1300 -- if there any record to be synchonized with WF
1301 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1302 hz_utility_v2pub.DEBUG
1303 (p_message=>'Orig system ID:'||system_id,
1304 p_prefix =>l_debug_prefix,
1305 p_msg_level=>fnd_log.level_statement);
1306 END IF;
1307
1308 -- Add the TCA registry Data as parameters to wf_event
1309 -- This is needed as the WF API access the wf_event structure for
1310 -- the information passed.
1311
1312 wf_event.AddParameterToList('User_Name', user_name, List);
1313 wf_event.AddParameterToList('DisplayName', display_name, List);
1314 wf_event.AddParameterToList('Description', description, List);
1315 wf_event.AddParameterToList('orclWorkFlowNotificationPref', notification_pref, List);
1316 wf_event.AddParameterToList('preferredLanguage', language, List);
1317 wf_event.AddParameterToList('OrclNLSTerritory', territory, List);
1318 wf_event.AddParameterToList('Mail', email_address, List);
1319 wf_event.AddParameterToList('FacsimileTelephoneNumber', fax, List);
1320 wf_event.AddParameterToList('orclisEnabled', status, List);
1321 wf_event.AddParameterToList('ExpirationDate', expiration_date, List);
1322 wf_event.AddParameterToList('orclWFOrigSystem',system, List);
1323 wf_event.AddParameterToList('orclWFOrigSystemID',system_id, List);
1324
1325 IF (p_update = TRUE) then
1326 wf_event.AddParameterToList('UpdateOnly', 'TRUE', List);
1327 END IF;
1328
1329 IF (p_overwrite = TRUE) then
1330 wf_event.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE', List);
1331 END IF;
1332
1333 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1334 hz_utility_v2pub.DEBUG
1335 (p_message=>'bfr Calling WF_LOCAL_SYNCH.propagate_role API',
1336 p_prefix =>l_debug_prefix,
1337 p_msg_level=>fnd_log.level_statement);
1338 END IF;
1339
1340 -- calling the WF API to propagate role
1341 WF_LOCAL_SYNCH.propagate_role(
1342 system,
1343 system_id,
1344 List,
1345 start_date,
1346 expiration_date);
1347 END IF; -- if there is any TCA Registry record to sync, check ends
1348
1349 -- Debug info.
1350 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1351 hz_utility_v2pub.DEBUG
1352 (p_message=>'SynchGroupWFRole()-',
1353 p_prefix =>l_debug_prefix,
1354 p_msg_level=>fnd_log.level_statement);
1355 END IF;
1356 END SYNCHGROUPWFROLE;
1357
1358 -------------------------------------------------------------------------
1359 /**
1360 * PROCEDURE SynchGroupWFUserRole
1361 *
1362 * DESCRIPTION
1363 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_user_role method
1364 *
1365 *
1366 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1367 * WF_LOCAL_SYNCH.propagate_user_role
1368 *
1369 * ARGUMENTS
1370 * IN:
1371 * PartyId party_id for which the synch has
1372 * to be done
1373 *
1374 * IN/OUT:
1375 *
1376 * OUT:
1377 *
1378 *
1379 * NOTES
1380 *
1381 * MODIFICATION HISTORY
1382 *
1383 * 03-Jan-2003 Porkodi Chinnandar o Created.
1384 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
1385 *
1386 */
1387 -------------------------------------------------------------------------
1388
1389 PROCEDURE SYNCHGROUPWFUSERROLE (RelationshipId IN NUMBER )
1390 IS
1391 expiration_date Date DEFAULT NULL;
1392 start_date Date DEFAULT NULL;
1393 user_system varchar2(10);
1394 user_system_id Number;
1395 role_system varchar2(10);
1396 role_system_id Number;
1397 l_debug_prefix VARCHAR2(30) := 'SYNCGRPWFUR';
1398
1399 BEGIN
1400 -- debug info
1401 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1402 hz_utility_v2pub.DEBUG
1403 (p_message=>'SYNCHGROUPWFUSERROLE()+',
1404 p_prefix =>l_debug_prefix,
1405 p_msg_level=>fnd_log.level_statement);
1406 END IF;
1407
1408 SAVEPOINT SynchGroupWFUserRole;
1409 -- changes done because of Bug#4390816 is
1410 -- removed the filtering based on directional flag from the select stmt
1411
1412 BEGIN
1413 -- Debug info.
1414 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1415 hz_utility_v2pub.DEBUG
1416 (p_message=>'Bfr Selecting the data',
1417 p_prefix =>l_debug_prefix,
1418 p_msg_level=>fnd_log.level_statement);
1419 END IF;
1420
1421 SELECT
1422 'HZ_PARTY' -- USER_ORIG_SYSTEM
1423 ,sp.party_id -- USER_ORIG_SYSTEM_ID
1424 ,'HZ_GROUP' -- ROLE_ORIG_SYSTEM
1425 ,op.party_id -- ROLE_ORIG_SYSTEM_ID
1426 ,pr.start_date -- START_DATE
1427 ,DECODE(pr.status, 'A', pr.end_date, 'I',
1428 (CASE
1429 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) < 0 THEN pr.end_date
1430 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) > 0 THEN SYSDATE
1431 ELSE SYSDATE
1432 END), SYSDATE) -- EndDate --Bug#5209709 fix
1433 INTO
1434 user_system,
1435 user_system_id,
1436 role_system,
1437 role_system_id,
1438 start_date,
1439 expiration_date
1440 FROM
1441 hz_relationships pr
1442 ,hz_parties sp
1443 ,hz_parties op
1444 WHERE
1445 pr.relationship_id = RelationshipId
1446 and pr.subject_table_name = 'HZ_PARTIES'
1447 and pr.object_table_name = 'HZ_PARTIES'
1448 and sp.party_id = pr.subject_id
1449 and sp.party_type = 'PERSON'
1450 and op.party_id = pr.object_id
1451 and op.party_type = 'GROUP';
1452
1453 EXCEPTION
1454 WHEN NO_DATA_FOUND THEN
1455 -- Debug info.
1456 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1457 hz_utility_v2pub.DEBUG
1458 (p_message=>'no data found to sync',
1459 p_prefix =>l_debug_prefix,
1460 p_msg_level=>fnd_log.level_statement);
1461 END IF;
1462 -- no error is raised as not finding any record to sync is not an error.
1463 -- return the control to the caller as no action is necessary.
1464 RETURN;
1465 END; -- anonymous block for selecting the person-group relationships end
1466
1467 BEGIN
1468 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1469 hz_utility_v2pub.DEBUG
1470 (p_message=>'User Sys:'||user_system||' USER sysID:'||user_system_id,
1471 p_prefix =>l_debug_prefix,
1472 p_msg_level=>fnd_log.level_statement);
1473 END IF;
1474 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1475 hz_utility_v2pub.DEBUG
1476 (p_message=>'Role Sys:'||role_system||' Role sysID:'||role_system_id,
1477 p_prefix =>l_debug_prefix,
1478 p_msg_level=>fnd_log.level_statement);
1479 END IF;
1480
1481 WF_LOCAL_SYNCH.propagate_user_role(
1482 user_system,
1483 user_system_id,
1484 role_system,
1485 role_system_id,
1486 start_date,
1487 expiration_date);
1488
1489 EXCEPTION
1490 WHEN NO_DATA_FOUND THEN
1491 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1492 hz_utility_v2pub.DEBUG
1493 (p_message=>'No data found to propagate_user_role',
1494 p_prefix =>l_debug_prefix,
1495 p_msg_level=>fnd_log.level_statement);
1496 END IF;
1497 -- as there is no data to sync as user role, first sync person and
1498 -- then group. After syncing person and group as roles, then sync
1499 -- person-Group as user role.
1500 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1501 hz_utility_v2pub.DEBUG
1502 (p_message=>'Bfr SynchPersonWFRole to propagate per:'||user_system_id,
1503 p_prefix =>l_debug_prefix,
1504 p_msg_level=>fnd_log.level_statement);
1505 END IF;
1506
1507 SynchPersonWFRole(user_system_id);
1508
1509 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1510 hz_utility_v2pub.DEBUG
1511 (p_message=>'Bfr SynchGroupWFRole to propagate grp:'||role_system_id,
1512 p_prefix =>l_debug_prefix,
1513 p_msg_level=>fnd_log.level_statement);
1514 END IF;
1515
1516 SynchGroupWFRole(role_system_id);
1517
1518 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1519 hz_utility_v2pub.DEBUG
1520 (p_message=>'Bfr WF_LOCAL_SYNCH.propagate_user_role()',
1521 p_prefix =>l_debug_prefix,
1522 p_msg_level=>fnd_log.level_statement);
1523 END IF;
1524
1525 WF_LOCAL_SYNCH.propagate_user_role(
1526 user_system,
1527 user_system_id,
1528 role_system,
1529 role_system_id,
1530 start_date,
1531 expiration_date);
1532 END; -- anonymous block to propagate the wf tables end.
1533 -- debug info
1534 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1535 hz_utility_v2pub.DEBUG
1536 (p_message=>'SYNCHGROUPWFUSERROLE()-',
1537 p_prefix =>l_debug_prefix,
1538 p_msg_level=>fnd_log.level_statement);
1539 END IF;
1540 END SYNCHGROUPWFUSERROLE;
1541 -------------------------------------------------------------------------
1542 -------------------------------------------------------------------------
1543 END HZ_WF_SYNCH;
[Home] [Help]