1 PACKAGE BODY HZ_WF_SYNCH AS
2 /* $Header: ARHWFSNB.pls 120.15 2006/06/27 10:15:28 smattegu noship $ */
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 *
715 */
716 -------------------------------------------------------------------------
717
718 PROCEDURE SYNCHPERSONWFROLE (
719 PartyId IN Number,
720 p_update IN Boolean Default False ,
721 p_overwrite IN Boolean Default False )
722 IS
723 List wf_parameter_list_t;
724 user_name VARCHAR2(25);
725 display_name VARCHAR2(360);
726 description VARCHAR2(360);
727 notification_pref VARCHAR2(30);
728 language VARCHAR2(30);
729 territory VARCHAR2(30);
730 email_address VARCHAR2(325);
731 fax VARCHAR2(80);
732 status VARCHAR2(8);
733 expiration_date DATE;
734 start_date DATE DEFAULT NULL;
735 system VARCHAR2(10) := 'HZ_PARTY';
736 system_id VARCHAR2(15);
737 l_debug_prefix VARCHAR2(30) := 'SYNCPERWFROLE';
738
739 BEGIN
740 -- debug info
741 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
742 hz_utility_v2pub.DEBUG
743 (p_message=>'SYNCHPERSONWFROLE()+',
744 p_prefix =>l_debug_prefix,
745 p_msg_level=>fnd_log.level_statement);
746 END IF;
747 SAVEPOINT SynchPersonWFRole;
748 /*
749 As part of Bug 4390816, following changes were done to this procedure:
750 Select stmt changes:
751 1. email_format is no longer hardcoded to MAILTEXT when null
752 2. email format is truncated to 8 char
753 3. email address is truncated to 320 char
754
755 BUG 4957312 changes
756 1. email_format a.k.a Notification Preference defaulting was re-inroduced.
757
758 Details-
759 If the email is present and has a notification preference, then it was used.
760 If the notification preference was not present and the email was present,
761 then MAILTEXT is deafulted. This is to be consistent with email defaulting
762 in Public API Bug4359226.
763 If the email itself was not available, notification preference was defaulted
764 to Query.
765 */
766
767 BEGIN -- anonymous block to select the records from TCA Registry
768 SELECT
769 'HZ_PARTY:'||p.party_id, -- Username
770 p.party_name, -- DisplayName
771 p.party_name, -- description
772 nvl2(p.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY'), -- notification_pref
773 fl.nls_language, -- preferredLanguage
774 fl.nls_territory, -- orclNLSTerritory
775 substrb(p.email_address,1,320), -- mail
776 NULL, -- fax
777 decode(p.status, 'A', 'ACTIVE', 'INACTIVE'), -- orclIsEnabled
778 NULL, -- ExpirationDate
779 p.party_id -- System Id
780 INTO
781 user_name,
782 display_name,
783 description,
784 notification_pref,
785 language,
786 territory,
787 email_address,
788 fax,
789 status,
790 expiration_date,
791 system_id
792 FROM
793 HZ_PARTIES p,
794 HZ_CONTACT_POINTS cp,
795 HZ_PERSON_LANGUAGE pl,
796 FND_LANGUAGES fl
797 WHERE
798 p.party_id = PartyId
799 AND p.party_type = 'PERSON'
800 AND cp.owner_table_name(+) = 'HZ_PARTIES'
801 AND cp.owner_table_id (+) = p.party_id
802 AND cp.contact_point_type(+) = 'EMAIL'
803 AND cp.primary_flag(+) = 'Y'
804 AND cp.status(+) = 'A'
805 AND pl.party_id (+) = p.party_id
806 AND pl.primary_language_indicator(+) = 'Y'
807 AND pl.status(+) = 'A'
808 AND pl.language_name = fl.language_code(+);
809 EXCEPTION
810 WHEN NO_DATA_FOUND THEN
811 -- Debug info.
812 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
813 hz_utility_v2pub.DEBUG
814 (p_message=>'no data found to sync',
815 p_prefix =>l_debug_prefix,
816 p_msg_level=>fnd_log.level_statement);
817 END IF;
818 END; -- anonymous block to fetch the TCA Registry records ends
819
820 IF (system_id IS NOT NULL) THEN
821 -- if there is a valid record from TCA Registry to Sync
822 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
823 hz_utility_v2pub.DEBUG
824 (p_message=>'Orig system ID:'||system_id,
825 p_prefix =>l_debug_prefix,
826 p_msg_level=>fnd_log.level_statement);
827 END IF;
828
829 -- Add the TCA registry Data as parameters to wf_event
830 -- This is needed as the WF API access the wf_event structure for
831 -- the information passed.
832
833 wf_event.AddParameterToList('User_Name', user_name, List);
834 wf_event.AddParameterToList('DisplayName', display_name, List);
835 wf_event.AddParameterToList('Description', description, List);
836 wf_event.AddParameterToList('orclWorkFlowNotificationPref', notification_pref, List);
837 wf_event.AddParameterToList('preferredLanguage', language, List);
838 wf_event.AddParameterToList('OrclNLSTerritory', territory, List);
839 wf_event.AddParameterToList('Mail', email_address, List);
840 wf_event.AddParameterToList('FacsimileTelephoneNumber', fax, List);
841 wf_event.AddParameterToList('orclisEnabled', status, List);
842 wf_event.AddParameterToList('ExpirationDate', expiration_date, List);
843 wf_event.AddParameterToList('orclWFOrigSystem',system, List);
844 wf_event.AddParameterToList('orclWFOrigSystemID',system_id, List);
845
846 if (p_update = TRUE) then
847 wf_event.AddParameterToList('UpdateOnly', 'TRUE' , List);
848 end if;
849
850 if (p_overwrite = TRUE) then
851 wf_event.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE', List);
852 end if;
853
854 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
855 hz_utility_v2pub.DEBUG
856 (p_message=>'bfr Calling WF_LOCAL_SYNCH.propagate_role API',
857 p_prefix =>l_debug_prefix,
858 p_msg_level=>fnd_log.level_statement);
859 END IF;
860
861 -- call WF API for Synchronization
862 WF_LOCAL_SYNCH.propagate_user(
863 system,
864 system_id,
865 List,
866 start_date,
867 expiration_date);
868 END IF; -- check for valid TCA id ends
869
870 -- debug info
871 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
872 hz_utility_v2pub.DEBUG
873 (p_message=>'SYNCHPERSONWFROLE()-',
874 p_prefix =>l_debug_prefix,
875 p_msg_level=>fnd_log.level_statement);
876 END IF;
877
878 END SYNCHPERSONWFROLE;
879 -------------------------------------------------------------------------
880 /**
881 * PROCEDURE SynchContactWFRole
882 *
883 * DESCRIPTION
884 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_role method
885 *
886 *
887 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
888 * WF_LOCAL_SYNCH.propagate_role
889 *
890 * ARGUMENTS
891 * IN:
892 * PartyId party_id for which the synch has
893 * to be done
894 * p_update Update flag
895 * p_overwrite overwrite flag
896 *
897 * IN/OUT:
898 *
899 * OUT:
900 *
901 *
902 * NOTES
903 *
904 * MODIFICATION HISTORY
905 *
906 * 03-Jan-2003 Porkodi Chinnandar o Created.
907 * 11-Mar-2003 Leena Sampemane - Contact needs to be created as a WF User
908 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
909 *
910 *
911 */
912 -------------------------------------------------------------------------
913 PROCEDURE SYNCHCONTACTWFROLE (
914 PartyId IN Number,
915 p_update IN Boolean Default False ,
916 p_overwrite IN Boolean Default False )
917 IS
918 List WF_PARAMETER_LIST_T;
919 system VARCHAR2(10) := 'HZ_PARTY';
920 l_tbl SYNC_TBL_TYPE;
921 l_rec_count NUMBER := 0;
922 l_debug_prefix VARCHAR2(30) := 'SYNCCNTWFROLE';
923
924 /*
925 As part of Bug 4390816, following changes were done to this procedure:
926
927 Select stmt changes:
928 1. Email_format is no longer hardcoded to MAILTEXT when null
929 2. Email format is truncated to 8 char
930 3. Email address is truncated to 320 char
931 4. StartDate, ExpirationDate are populated from relationship table
932 5. Unnecessary joining for 'object' to hz_parties removed
933 6. Removed directional flag filtering. This would mean that there
934 are relationship records that must sync up (unlike the previous
935 design). Hence need to re-write the SQL as a cursor.
936
937 Param changes:
938 1, Added wf_event.AddParameterToList('StartDate', start_date, List);
939
940 BUG 4957312 changes
941 1. email_format a.k.a Notification Preference defaulting was re-inroduced.
942
943 Details-
944 If the email is present and has a notification preference, then it was used.
945 If the notification preference was not present and the email was present,
946 then MAILTEXT is deafulted. This is to be consistent with email defaulting
947 in Public API Bug4359226.
948 If the email itself was not available, notification preference was defaulted
949 to Query.
950
951 2. Notification Preference selected must belong to the Email chosen.
952 In case of email of a contact, the notification preference must belong
953 to the same email (of that contact).
954
955 3. Incase of person to person rel, directional flag was re-intorduced.
956
957 Details-
958 This was to avoid selecting two rows in case of Person-Person relationships.
959
960 4. Person-Group relationships are no longer considered in SyncContactWFRole().
961 */
962
963 CURSOR c_rel (c_p_rel_party_id IN NUMBER) IS
964 SELECT
965 'HZ_PARTY:'||to_char(pr.party_id) -- Username
966 ,per.party_name -- DisplayName
967 ,prp.party_name -- description
968 ,nvl2(prp.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY') -- notification_pref
969 ,fl.nls_language -- language
970 ,fl.nls_territory -- territory
971 ,substrb(prp.email_address,1,320) -- email_address
972 ,NULL -- fax
973 ,DECODE(prp.status,'A','ACTIVE','INACTIVE') -- status
974 ,pr.start_date -- startDate
975 ,DECODE(pr.status, 'A', pr.end_date, 'I',
976 (CASE
977 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) < 0 THEN pr.end_date
978 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) > 0 THEN SYSDATE
979 ELSE SYSDATE
980 END), SYSDATE) -- ExpirationDate --Bug#5209709 fix
981 ,pr.party_id -- system_id
982 FROM
983 hz_relationships pr
984 ,hz_org_contacts oc
985 ,hz_parties prp -- party relationship party
986 ,hz_parties per
987 ,hz_contact_points cp
988 ,hz_person_language pl
989 ,fnd_languages fl
990 WHERE
991 pr.party_id = c_p_rel_party_id
992 AND pr.subject_table_name = 'HZ_PARTIES'
993 AND pr.object_table_name = 'HZ_PARTIES'
994 AND pr.subject_id = per.party_id
995 AND per.party_type = 'PERSON'
996 AND ((pr.object_type = 'PERSON' AND pr.directional_flag = 'F')
997 OR pr.object_type = 'ORGANIZATION')
998 AND pr.relationship_id = oc.party_relationship_id
999 AND prp.party_id = pr.party_id
1000 AND prp.party_type = 'PARTY_RELATIONSHIP'
1001 AND cp.owner_table_name(+) = 'HZ_PARTIES'
1002 AND cp.owner_table_id (+) = prp.party_id
1003 AND cp.contact_point_type(+) = 'EMAIL'
1004 AND cp.email_address(+) = prp.email_address
1005 AND cp.primary_flag(+) = 'Y'
1006 AND cp.status(+) = 'A'
1007 AND per.party_id = pl.party_id(+)
1008 AND pl.primary_language_indicator(+) = 'Y'
1009 AND pl.status(+) = 'A'
1010 AND pl.language_name = fl.language_code(+);
1011
1012 BEGIN
1013 SAVEPOINT SynchContactWFRole;
1014 -- Debug info.
1015 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1016 hz_utility_v2pub.DEBUG
1017 (p_message=>'SynchContactWFRole()+',
1018 p_prefix =>l_debug_prefix,
1019 p_msg_level=>fnd_log.level_statement);
1020 END IF;
1021
1022 OPEN c_rel(PartyId);
1023 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1024 hz_utility_v2pub.DEBUG
1025 (p_message=>'OPENED THE CURSOR',
1026 p_prefix =>l_debug_prefix,
1027 p_msg_level=>fnd_log.level_statement);
1028 END IF;
1029 FETCH c_rel BULK COLLECT INTO l_tbl; -- select records from TCA Registry
1030 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1031 hz_utility_v2pub.DEBUG
1032 (p_message=>'Bulk collected the data',
1033 p_prefix =>l_debug_prefix,
1034 p_msg_level=>fnd_log.level_statement);
1035 END IF;
1036 CLOSE c_rel;
1037
1038 l_rec_count := l_tbl.COUNT;
1039 FOR i IN l_tbl.FIRST..l_tbl.LAST
1040 LOOP
1041 -- for each of the selected record from TCA Registry
1042 -- perform WF Synchronization.
1043 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1044 hz_utility_v2pub.DEBUG
1045 (p_message=>'At '||i||' Rec',
1046 p_prefix =>l_debug_prefix,
1047 p_msg_level=>fnd_log.level_statement);
1048 END IF;
1049
1050 IF (l_tbl(i).system_id IS NOT NULL) THEN
1051 -- for each of the valid record with a partyId
1052
1053 List := WF_PARAMETER_LIST_T();
1054 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1055 hz_utility_v2pub.DEBUG
1056 (p_message=>'Orig system ID: '||l_tbl(i).system_id,
1057 p_prefix =>l_debug_prefix,
1058 p_msg_level=>fnd_log.level_statement);
1059 END IF;
1060
1061 -- Add the TCA registry Data as parameters to wf_event
1062 -- This is needed as the WF API access the wf_event structure for
1063 -- the information passed.
1064
1065 wf_event.AddParameterToList('User_Name', l_tbl(i).user_name, List);
1066 wf_event.AddParameterToList('DisplayName', l_tbl(i).display_name, List);
1067 wf_event.AddParameterToList('Description', l_tbl(i).description, List);
1068 wf_event.AddParameterToList('orclWorkFlowNotificationPref', l_tbl(i).notification_pref, List);
1069 wf_event.AddParameterToList('preferredLanguage', l_tbl(i).language, List);
1070 wf_event.AddParameterToList('OrclNLSTerritory', l_tbl(i).territory, List);
1071 wf_event.AddParameterToList('Mail', l_tbl(i).email_address, List);
1072 wf_event.AddParameterToList('FacsimileTelephoneNumber', l_tbl(i).fax, List);
1073 wf_event.AddParameterToList('orclisEnabled', l_tbl(i).status, List);
1074 wf_event.AddParameterToList('StartDate', l_tbl(i).start_date, List);
1075 wf_event.AddParameterToList('ExpirationDate', l_tbl(i).expiration_date, List);
1076 wf_event.AddParameterToList('orclWFOrigSystem',system, List);
1077 wf_event.AddParameterToList('orclWFOrigSystemID',l_tbl(i).system_id, List);
1078
1079 IF (p_update = TRUE) then
1080 wf_event.AddParameterToList('UpdateOnly', 'TRUE', List);
1081 END IF;
1082
1083 IF (p_overwrite = TRUE) THEN
1084 wf_event.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE', List);
1085 END IF;
1086
1087 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1088 hz_utility_v2pub.DEBUG
1089 (p_message=>'Before WF API Call',
1090 p_prefix =>l_debug_prefix,
1091 p_msg_level=>fnd_log.level_statement);
1092 END IF;
1093
1094 -- call WF API for Synchronization
1095
1096 WF_LOCAL_SYNCH.propagate_user(
1097 system,
1098 l_tbl(i).system_id,
1099 List,
1100 l_tbl(i).start_date,
1101 l_tbl(i).expiration_date);
1102 END IF; -- check for valid TCA id ends
1103 END LOOP; -- looping through all selected record ends
1104
1105 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1106 hz_utility_v2pub.DEBUG
1107 (p_message=>'SynchContactWFRole()-',
1108 p_prefix =>l_debug_prefix,
1109 p_msg_level=>fnd_log.level_statement);
1110 END IF;
1111
1112 EXCEPTION
1113 WHEN NO_DATA_FOUND THEN
1114 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1115 hz_utility_v2pub.DEBUG
1116 (p_message=>'No data found excep:'||sqlerrm,
1117 p_prefix =>l_debug_prefix,
1118 p_msg_level=>fnd_log.level_statement);
1119 END IF;
1120 -- no error is raised as not finding any record to sync is not an error.
1121 END SynchContactWFRole;
1122
1123 -------------------------------------------------------------------------
1124 /**
1125 * PROCEDURE SynchGroupWFRole
1126 *
1127 * DESCRIPTION
1128 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_role method
1129 * When the party type is group.
1130 *
1131 *
1132 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1133 * WF_LOCAL_SYNCH.propagate_role
1134 *
1135 * ARGUMENTS
1136 * IN:
1137 * PartyId party_id for which the synch has
1138 * to be done
1139 * p_update Update flag
1140 * p_overwrite Overwrite flag
1141 *
1142 * IN/OUT:
1143 *
1144 * OUT:
1145 *
1146 *
1147 * NOTES
1148 *
1149 * MODIFICATION HISTORY
1150 *
1151 * 03-Jan-2003 Porkodi Chinnandar o Created.
1152 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
1153 *
1154 */
1155
1156 -------------------------------------------------------------------------
1157 PROCEDURE SYNCHGROUPWFROLE (
1158 PartyId IN Number,
1159 p_update IN Boolean Default False ,
1160 p_overwrite IN Boolean Default False )
1161
1162 IS
1163 List wf_parameter_list_t;
1164 user_name varchar2(25);
1165 display_name varchar2(360);
1166 description varchar2(360);
1167 notification_pref varchar2(30);
1168 language varchar2(30);
1169 territory varchar2(30);
1170 email_address varchar2(325);
1171 fax varchar2(80);
1172 status varchar2(8);
1173 expiration_date Date;
1174 start_date Date DEFAULT NULL;
1175 system varchar2(10) := 'HZ_GROUP';
1176 system_id varchar2(15);
1177 l_debug_prefix VARCHAR2(30) := 'SYNCGRWFROLE';
1178
1179 BEGIN
1180 SAVEPOINT SynchGroupWFRole;
1181 -- Debug info.
1182 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1183 hz_utility_v2pub.DEBUG
1184 (p_message=>'SynchGroupWFRole()+',
1185 p_prefix =>l_debug_prefix,
1186 p_msg_level=>fnd_log.level_statement);
1187 END IF;
1188
1189 -- As part of Bug 4390816, following changes were done to this procedure:
1190 -- select stmt changes
1191 -- 1. email_format is no longer hardcoded to MAILTEXT when null
1192 -- 2. email format is truncated to 8 char
1193 -- 3. email address is truncated to 320 char
1194 -- 4. mission statement is truncated to 1000 chars
1195
1196 BEGIN -- anonymous block to select the TCA Registry
1197 -- Debug info.
1198 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1199 hz_utility_v2pub.DEBUG
1200 (p_message=>'Bfr Selecting the data',
1201 p_prefix =>l_debug_prefix,
1202 p_msg_level=>fnd_log.level_statement);
1203 END IF;
1204 /*
1205 BUG 4957312 changes
1206 1. email_format a.k.a Notification Preference defaulting was re-inroduced.
1207
1208 Details-
1209 If the email is present and has a notification preference, then it was used.
1210 If the notification preference was not present and the email was present,
1211 then MAILTEXT is deafulted. This is to be consistent with email defaulting
1212 in Public API Bug4359226.
1213 If the email itself was not available, notification preference was defaulted
1214 to Query.
1215
1216 2. Notification Preference selected must belong to the Email chosen.
1217 In case of email of a contact, the notification preference must belong
1218 to the same email (of that contact).
1219 */
1220 SELECT
1221 'HZ_GROUP:'||p.party_id, -- USER_NAME, note the :
1222 p.party_name, -- DisplayName
1223 substrb(p.mission_statement,1,1000), -- DESCRIPTION
1224 nvl2(p.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY'), -- notification_pref
1225 fl.nls_language, -- Language
1226 fl.nls_territory, -- Territory
1227 substrb(p.email_address,1,320), -- email mail
1228 NULL, -- fax
1229 decode(p.status, 'A', 'ACTIVE', 'INACTIVE'), -- status
1230 NULL, -- ExpirationDate
1231 p.party_id -- System Id
1232 INTO
1233 user_name,
1234 display_name,
1235 description,
1236 notification_pref,
1237 language,
1238 territory,
1239 email_address,
1240 fax,
1241 status,
1242 expiration_date,
1243 system_id
1244 FROM
1245 HZ_PARTIES p,
1246 HZ_CONTACT_POINTS cp,
1247 HZ_PERSON_LANGUAGE pl,
1248 FND_LANGUAGES fl
1249 WHERE
1250 p.party_id = PartyId
1251 AND p.party_type = 'GROUP'
1252 AND cp.owner_table_name(+) = 'HZ_PARTIES'
1253 AND cp.owner_table_id (+) = p.party_id
1254 AND cp.contact_point_type(+) = 'EMAIL'
1255 AND cp.primary_flag(+) = 'Y'
1256 AND cp.status(+) = 'A'
1257 AND pl.party_id (+) = p.party_id
1258 AND pl.primary_language_indicator(+) = 'Y'
1259 AND pl.status(+) = 'A'
1260 AND pl.language_name = fl.language_code(+);
1261 EXCEPTION
1262 WHEN NO_DATA_FOUND THEN
1263 -- Debug info.
1264 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1265 hz_utility_v2pub.DEBUG
1266 (p_message=>'no data found to sync',
1267 p_prefix =>l_debug_prefix,
1268 p_msg_level=>fnd_log.level_statement);
1269 END IF;
1270 -- no error is raised as not finding any record to sync is not an error.
1271 END; -- anonymous block to query the TCA Registry ends
1272
1273 IF (system_id IS NOT NULL) THEN
1274
1275 -- if there any record to be synchonized with WF
1276 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1277 hz_utility_v2pub.DEBUG
1278 (p_message=>'Orig system ID:'||system_id,
1279 p_prefix =>l_debug_prefix,
1280 p_msg_level=>fnd_log.level_statement);
1281 END IF;
1282
1283 -- Add the TCA registry Data as parameters to wf_event
1284 -- This is needed as the WF API access the wf_event structure for
1285 -- the information passed.
1286
1287 wf_event.AddParameterToList('User_Name', user_name, List);
1288 wf_event.AddParameterToList('DisplayName', display_name, List);
1289 wf_event.AddParameterToList('Description', description, List);
1290 wf_event.AddParameterToList('orclWorkFlowNotificationPref', notification_pref, List);
1291 wf_event.AddParameterToList('preferredLanguage', language, List);
1292 wf_event.AddParameterToList('OrclNLSTerritory', territory, List);
1293 wf_event.AddParameterToList('Mail', email_address, List);
1294 wf_event.AddParameterToList('FacsimileTelephoneNumber', fax, List);
1295 wf_event.AddParameterToList('orclisEnabled', status, List);
1296 wf_event.AddParameterToList('ExpirationDate', expiration_date, List);
1297 wf_event.AddParameterToList('orclWFOrigSystem',system, List);
1298 wf_event.AddParameterToList('orclWFOrigSystemID',system_id, List);
1299
1300 IF (p_update = TRUE) then
1301 wf_event.AddParameterToList('UpdateOnly', 'TRUE', List);
1302 END IF;
1303
1304 IF (p_overwrite = TRUE) then
1305 wf_event.AddParameterToList('WFSYNCH_OVERWRITE', 'TRUE', List);
1306 END IF;
1307
1308 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1309 hz_utility_v2pub.DEBUG
1310 (p_message=>'bfr Calling WF_LOCAL_SYNCH.propagate_role API',
1311 p_prefix =>l_debug_prefix,
1312 p_msg_level=>fnd_log.level_statement);
1313 END IF;
1314
1315 -- calling the WF API to propagate role
1316 WF_LOCAL_SYNCH.propagate_role(
1317 system,
1318 system_id,
1319 List,
1320 start_date,
1321 expiration_date);
1322 END IF; -- if there is any TCA Registry record to sync, check ends
1323
1324 -- Debug info.
1325 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1326 hz_utility_v2pub.DEBUG
1327 (p_message=>'SynchGroupWFRole()-',
1328 p_prefix =>l_debug_prefix,
1329 p_msg_level=>fnd_log.level_statement);
1330 END IF;
1331 END SYNCHGROUPWFROLE;
1332
1333 -------------------------------------------------------------------------
1334 /**
1335 * PROCEDURE SynchGroupWFUserRole
1336 *
1337 * DESCRIPTION
1338 * This is a wrapper to call the WF_LOCAL_SYNCH.propagate_user_role method
1339 *
1340 *
1341 * EXTERNAL PROCEDURES/FUNCTIONS ACCESSED
1342 * WF_LOCAL_SYNCH.propagate_user_role
1343 *
1344 * ARGUMENTS
1345 * IN:
1346 * PartyId party_id for which the synch has
1347 * to be done
1348 *
1349 * IN/OUT:
1350 *
1351 * OUT:
1352 *
1353 *
1354 * NOTES
1355 *
1356 * MODIFICATION HISTORY
1357 *
1358 * 03-Jan-2003 Porkodi Chinnandar o Created.
1359 * 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
1360 *
1361 */
1362 -------------------------------------------------------------------------
1363
1364 PROCEDURE SYNCHGROUPWFUSERROLE (RelationshipId IN NUMBER )
1365 IS
1366 expiration_date Date DEFAULT NULL;
1367 start_date Date DEFAULT NULL;
1368 user_system varchar2(10);
1369 user_system_id Number;
1370 role_system varchar2(10);
1371 role_system_id Number;
1372 l_debug_prefix VARCHAR2(30) := 'SYNCGRPWFUR';
1373
1374 BEGIN
1375 -- debug info
1376 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1377 hz_utility_v2pub.DEBUG
1378 (p_message=>'SYNCHGROUPWFUSERROLE()+',
1379 p_prefix =>l_debug_prefix,
1380 p_msg_level=>fnd_log.level_statement);
1381 END IF;
1382
1383 SAVEPOINT SynchGroupWFUserRole;
1384 -- changes done because of Bug#4390816 is
1385 -- removed the filtering based on directional flag from the select stmt
1386
1387 BEGIN
1388 -- Debug info.
1389 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1390 hz_utility_v2pub.DEBUG
1391 (p_message=>'Bfr Selecting the data',
1392 p_prefix =>l_debug_prefix,
1393 p_msg_level=>fnd_log.level_statement);
1394 END IF;
1395
1396 SELECT
1397 'HZ_PARTY' -- USER_ORIG_SYSTEM
1398 ,sp.party_id -- USER_ORIG_SYSTEM_ID
1399 ,'HZ_GROUP' -- ROLE_ORIG_SYSTEM
1400 ,op.party_id -- ROLE_ORIG_SYSTEM_ID
1401 ,pr.start_date -- START_DATE
1402 ,DECODE(pr.status, 'A', pr.end_date, 'I',
1403 (CASE
1404 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) < 0 THEN pr.end_date
1405 WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) > 0 THEN SYSDATE
1406 ELSE SYSDATE
1407 END), SYSDATE) -- EndDate --Bug#5209709 fix
1408 INTO
1409 user_system,
1410 user_system_id,
1411 role_system,
1412 role_system_id,
1413 start_date,
1414 expiration_date
1415 FROM
1416 hz_relationships pr
1417 ,hz_parties sp
1418 ,hz_parties op
1419 WHERE
1420 pr.relationship_id = RelationshipId
1421 and pr.subject_table_name = 'HZ_PARTIES'
1422 and pr.object_table_name = 'HZ_PARTIES'
1423 and sp.party_id = pr.subject_id
1424 and sp.party_type = 'PERSON'
1425 and op.party_id = pr.object_id
1426 and op.party_type = 'GROUP';
1427
1428 EXCEPTION
1429 WHEN NO_DATA_FOUND THEN
1430 -- Debug info.
1431 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1432 hz_utility_v2pub.DEBUG
1433 (p_message=>'no data found to sync',
1434 p_prefix =>l_debug_prefix,
1435 p_msg_level=>fnd_log.level_statement);
1436 END IF;
1437 -- no error is raised as not finding any record to sync is not an error.
1438 -- return the control to the caller as no action is necessary.
1439 RETURN;
1440 END; -- anonymous block for selecting the person-group relationships end
1441
1442 BEGIN
1443 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1444 hz_utility_v2pub.DEBUG
1445 (p_message=>'User Sys:'||user_system||' USER sysID:'||user_system_id,
1446 p_prefix =>l_debug_prefix,
1447 p_msg_level=>fnd_log.level_statement);
1448 END IF;
1449 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1450 hz_utility_v2pub.DEBUG
1451 (p_message=>'Role Sys:'||role_system||' Role sysID:'||role_system_id,
1452 p_prefix =>l_debug_prefix,
1453 p_msg_level=>fnd_log.level_statement);
1454 END IF;
1455
1456 WF_LOCAL_SYNCH.propagate_user_role(
1457 user_system,
1458 user_system_id,
1459 role_system,
1460 role_system_id,
1461 start_date,
1462 expiration_date);
1463
1464 EXCEPTION
1465 WHEN NO_DATA_FOUND THEN
1466 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1467 hz_utility_v2pub.DEBUG
1468 (p_message=>'No data found to propagate_user_role',
1469 p_prefix =>l_debug_prefix,
1470 p_msg_level=>fnd_log.level_statement);
1471 END IF;
1472 -- as there is no data to sync as user role, first sync person and
1473 -- then group. After syncing person and group as roles, then sync
1474 -- person-Group as user role.
1475 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1476 hz_utility_v2pub.DEBUG
1477 (p_message=>'Bfr SynchPersonWFRole to propagate per:'||user_system_id,
1478 p_prefix =>l_debug_prefix,
1479 p_msg_level=>fnd_log.level_statement);
1480 END IF;
1481
1482 SynchPersonWFRole(user_system_id);
1483
1484 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1485 hz_utility_v2pub.DEBUG
1486 (p_message=>'Bfr SynchGroupWFRole to propagate grp:'||role_system_id,
1487 p_prefix =>l_debug_prefix,
1488 p_msg_level=>fnd_log.level_statement);
1489 END IF;
1490
1491 SynchGroupWFRole(role_system_id);
1492
1493 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1494 hz_utility_v2pub.DEBUG
1495 (p_message=>'Bfr WF_LOCAL_SYNCH.propagate_user_role()',
1496 p_prefix =>l_debug_prefix,
1497 p_msg_level=>fnd_log.level_statement);
1498 END IF;
1499
1500 WF_LOCAL_SYNCH.propagate_user_role(
1501 user_system,
1502 user_system_id,
1503 role_system,
1504 role_system_id,
1505 start_date,
1506 expiration_date);
1507 END; -- anonymous block to propagate the wf tables end.
1508 -- debug info
1509 IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
1510 hz_utility_v2pub.DEBUG
1511 (p_message=>'SYNCHGROUPWFUSERROLE()-',
1512 p_prefix =>l_debug_prefix,
1513 p_msg_level=>fnd_log.level_statement);
1514 END IF;
1515 END SYNCHGROUPWFUSERROLE;
1516 -------------------------------------------------------------------------
1517 -------------------------------------------------------------------------
1518 END HZ_WF_SYNCH;
[Home] [Help]