[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
| The create or update relationship should call |
| SynchGroupWFUserRole procedure, which in turn synchs the |
| work flow tables. |
| |
| MODIFICATION HISTORY |
| |
| 03-Jan-2003 Porkodi Chinnandar Bug 2627161: Modified the code |
| to populate proper values before|
| calling WFSYNCH procedures |
| |
+===========================================================================*/
FUNCTION propagate_user_role(
p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2
IS
l_key VARCHAR2(240) := p_event.GetEventKey();
(l_key like 'oracle.apps.ar.hz.Relationship.update%') THEN
id := p_event.getValueForParameter('RELATIONSHIP_ID');
END IF; -- chk for Relationship Create or Update Events
| The create or update of Person/PersonLanguage/OrgContact/ |
| Group/ContactPoint events should call any of the |
| SynchPersonWFRole/SynchContactWFRole/SynchGroupWFRole or all |
| of them to synch with workflow tables. |
| |
| MODIFICATION HISTORY |
| |
| 03-Jan-2003 Porkodi Chinnandar Bug 2627161: Modified the code |
| to populate proper values before|
| calling WFSYNCH procedures |
| |
+===========================================================================*/
FUNCTION propagate_role(
p_subscription_guid IN RAW,
p_event IN OUT NOCOPY wf_event_t)
RETURN VARCHAR2
IS
l_key VARCHAR2(240) := p_event.GetEventKey();
select party_id
from hz_relationships rel,
hz_org_contacts org
where rel.relationship_id=org.party_relationship_id and
Subject_table_name = 'HZ_PARTIES' and
Object_table_name = 'HZ_PARTIES' and
Directional_flag = 'F' and
subject_id = wf_party_id;
Cursor org_update is
select party_id
from hz_relationships rel,
hz_org_contacts org
where rel.relationship_id=org.party_relationship_id and
Subject_table_name = 'HZ_PARTIES' and
Object_table_name = 'HZ_PARTIES' and
Directional_flag = 'F' and
object_id = l_match_string;
ELSIF (l_key LIKE 'oracle.apps.ar.hz.Person.update%') THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>l_key,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
select p.party_id, party_type
into wf_party_id, wf_party_type
from hz_parties p, hz_person_language l
where p.party_id= l.party_id and
primary_language_indicator='Y' and
l.status ='A' and
language_use_reference_id = wf_lang_user_ref_id;
ELSIF (l_key LIKE 'oracle.apps.ar.hz.PersonLanguage.update%') THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>l_key,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
select p.party_id, party_type
into wf_party_id, wf_party_type
from hz_parties p, hz_person_language l
where p.party_id= l.party_id and
-- primary_language_indicator='Y' and
-- l.status ='A' and
language_use_reference_id = wf_lang_user_ref_id;
END IF; -- check for party type ends in personLang.update event
select party_id
into wf_party_id
from hz_org_contacts org, hz_relationships rel
where org.party_relationship_id=rel.relationship_id and
Subject_table_name = 'HZ_PARTIES' and
Object_table_name = 'HZ_PARTIES' and
Directional_flag = 'F' and
org.org_contact_id=l_match_string;
ELSIF (l_key like 'oracle.apps.ar.hz.OrgContact.update%') THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>l_key,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
select party_id
into wf_party_id
from hz_org_contacts org, hz_relationships rel
where org.party_relationship_id=rel.relationship_id and
Subject_table_name = 'HZ_PARTIES' and
Object_table_name = 'HZ_PARTIES' and
Directional_flag = 'F' and
org.org_contact_id=l_match_string;
ELSIF (l_key like 'oracle.apps.ar.hz.Group.update%') THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>l_key,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
select owner_table_id, party_type
into wf_party_id, wf_party_type
from hz_contact_points pt, hz_parties
where pt.owner_table_id = party_id and
pt.contact_point_type = 'EMAIL' and
pt.status ='A' and
pt.primary_flag='Y' and
pt.owner_table_name ='HZ_PARTIES' and
pt.contact_point_id = l_match_string;
ELSIF (l_key like 'oracle.apps.ar.hz.ContactPoint.update%') THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>l_key,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
select owner_table_id, party_type
into wf_party_id, wf_party_type
from hz_contact_points pt, hz_parties
where pt.owner_table_id = party_id and
pt.contact_point_type = 'EMAIL' and
pt.owner_table_name ='HZ_PARTIES' and
pt.contact_point_id = l_match_string;
ELSIF (l_key like 'oracle.apps.ar.hz.Organization.update%') THEN
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.debug(
p_message=>l_key,
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
For orga_update in org_update Loop
SynchContactWFRole(orga_update.party_id,TRUE,TRUE);
* p_update Update flag
* p_overwrite Overwrite flag
*
* IN/OUT:
*
* OUT:
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 03-Jan-2003 Porkodi Chinnandar o Created.
* 13-Mar-2003 Leena Sampemane o Person needs to be created as a
* wf_user.
* 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
* 12-Mar-2013 Sudhir Gokavarapu o Bug#15992479
* expiration_date value changed in Select statement.
* as Party does not have end date we will get
* sysdate as expiration date for Inactive parties.
*
*/
-------------------------------------------------------------------------
PROCEDURE SYNCHPERSONWFROLE (
PartyId IN Number,
p_update IN Boolean Default False ,
p_overwrite IN Boolean Default False )
IS
List wf_parameter_list_t;
Select stmt changes:
1. email_format is no longer hardcoded to MAILTEXT when null
2. email format is truncated to 8 char
3. email address is truncated to 320 char
BUG 4957312 changes
1. email_format a.k.a Notification Preference defaulting was re-inroduced.
Details-
If the email is present and has a notification preference, then it was used.
If the notification preference was not present and the email was present,
then MAILTEXT is deafulted. This is to be consistent with email defaulting
in Public API Bug4359226.
If the email itself was not available, notification preference was defaulted
to Query.
*/
BEGIN -- anonymous block to select the records from TCA Registry
SELECT
'HZ_PARTY:'||p.party_id, -- Username
p.party_name, -- DisplayName
p.party_name, -- description
nvl2(p.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY'), -- notification_pref
fl.nls_language, -- preferredLanguage
fl.nls_territory, -- orclNLSTerritory
substrb(p.email_address,1,320), -- mail
NULL, -- fax
decode(p.status, 'A', 'ACTIVE', 'INACTIVE'), -- orclIsEnabled
-- NULL, -- ExpirationDate
decode(p.status, 'A', NULL, SYSDATE), -- ExpirationDate
p.party_id -- System Id
INTO
user_name,
display_name,
description,
notification_pref,
language,
territory,
email_address,
fax,
status,
expiration_date,
system_id
FROM
HZ_PARTIES p,
HZ_CONTACT_POINTS cp,
HZ_PERSON_LANGUAGE pl,
FND_LANGUAGES fl
WHERE
p.party_id = PartyId
AND p.party_type = 'PERSON'
AND cp.owner_table_name(+) = 'HZ_PARTIES'
AND cp.owner_table_id (+) = p.party_id
AND cp.contact_point_type(+) = 'EMAIL'
AND cp.primary_flag(+) = 'Y'
AND cp.status(+) = 'A'
AND pl.party_id (+) = p.party_id
AND pl.primary_language_indicator(+) = 'Y'
AND pl.status(+) = 'A'
AND pl.language_name = fl.language_code(+);
if (p_update = TRUE) then
wf_event.AddParameterToList('UpdateOnly', 'TRUE' , List);
* p_update Update flag
* p_overwrite overwrite flag
*
* IN/OUT:
*
* OUT:
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 03-Jan-2003 Porkodi Chinnandar o Created.
* 11-Mar-2003 Leena Sampemane - Contact needs to be created as a WF User
* 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
*
*
*/
-------------------------------------------------------------------------
PROCEDURE SYNCHCONTACTWFROLE (
PartyId IN Number,
p_update IN Boolean Default False ,
p_overwrite IN Boolean Default False )
IS
List WF_PARAMETER_LIST_T;
Select stmt changes:
1. Email_format is no longer hardcoded to MAILTEXT when null
2. Email format is truncated to 8 char
3. Email address is truncated to 320 char
4. StartDate, ExpirationDate are populated from relationship table
5. Unnecessary joining for 'object' to hz_parties removed
6. Removed directional flag filtering. This would mean that there
are relationship records that must sync up (unlike the previous
design). Hence need to re-write the SQL as a cursor.
Param changes:
1, Added wf_event.AddParameterToList('StartDate', start_date, List);
2. Notification Preference selected must belong to the Email chosen.
In case of email of a contact, the notification preference must belong
to the same email (of that contact).
3. Incase of person to person rel, directional flag was re-intorduced.
Details-
This was to avoid selecting two rows in case of Person-Person relationships.
4. Person-Group relationships are no longer considered in SyncContactWFRole().
*/
CURSOR c_rel (c_p_rel_party_id IN NUMBER) IS
SELECT
'HZ_PARTY:'||to_char(pr.party_id) -- Username
,per.party_name -- DisplayName
,prp.party_name -- description
,nvl2(prp.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY') -- notification_pref
,fl.nls_language -- language
,fl.nls_territory -- territory
,substrb(prp.email_address,1,320) -- email_address
,NULL -- fax
,DECODE(prp.status,'A','ACTIVE','INACTIVE') -- status
,pr.start_date -- startDate
,DECODE(pr.status, 'A', pr.end_date, 'I',
(CASE
WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) < 0 THEN pr.end_date
WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) > 0 THEN SYSDATE
ELSE SYSDATE
END), SYSDATE) -- ExpirationDate --Bug#5209709 fix
,pr.party_id -- system_id
FROM
hz_relationships pr
,hz_org_contacts oc
,hz_parties prp -- party relationship party
,hz_parties per
,hz_contact_points cp
,hz_person_language pl
,fnd_languages fl
WHERE
pr.party_id = c_p_rel_party_id
AND pr.subject_table_name = 'HZ_PARTIES'
AND pr.object_table_name = 'HZ_PARTIES'
AND pr.subject_id = per.party_id
AND per.party_type = 'PERSON'
AND ((pr.object_type = 'PERSON' AND pr.directional_flag = 'F')
OR pr.object_type = 'ORGANIZATION')
AND pr.relationship_id = oc.party_relationship_id
AND prp.party_id = pr.party_id
AND prp.party_type = 'PARTY_RELATIONSHIP'
AND cp.owner_table_name(+) = 'HZ_PARTIES'
AND cp.owner_table_id (+) = prp.party_id
AND cp.contact_point_type(+) = 'EMAIL'
AND cp.email_address(+) = prp.email_address
AND cp.primary_flag(+) = 'Y'
AND cp.status(+) = 'A'
AND per.party_id = pl.party_id(+)
AND pl.primary_language_indicator(+) = 'Y'
AND pl.status(+) = 'A'
AND pl.language_name = fl.language_code(+);
FETCH c_rel BULK COLLECT INTO l_tbl; -- select records from TCA Registry
IF (p_update = TRUE) then
wf_event.AddParameterToList('UpdateOnly', 'TRUE', List);
END LOOP; -- looping through all selected record ends
* p_update Update flag
* p_overwrite Overwrite flag
*
* IN/OUT:
*
* OUT:
*
*
* NOTES
*
* MODIFICATION HISTORY
*
* 03-Jan-2003 Porkodi Chinnandar o Created.
* 07-JUN-2005 Srikanth o fixed bug4390816 for release 12
*
*/
-------------------------------------------------------------------------
PROCEDURE SYNCHGROUPWFROLE (
PartyId IN Number,
p_update IN Boolean Default False ,
p_overwrite IN Boolean Default False )
IS
List wf_parameter_list_t;
BEGIN -- anonymous block to select the TCA Registry
-- Debug info.
IF fnd_log.level_statement>=fnd_log.g_current_runtime_level THEN
hz_utility_v2pub.DEBUG
(p_message=>'Bfr Selecting the data',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
2. Notification Preference selected must belong to the Email chosen.
In case of email of a contact, the notification preference must belong
to the same email (of that contact).
*/
SELECT
'HZ_GROUP:'||p.party_id, -- USER_NAME, note the :
p.party_name, -- DisplayName
substrb(p.mission_statement,1,1000), -- DESCRIPTION
nvl2(p.email_address, nvl(substrb(cp.email_format,1,8),'MAILTEXT'),'QUERY'), -- notification_pref
fl.nls_language, -- Language
fl.nls_territory, -- Territory
substrb(p.email_address,1,320), -- email mail
NULL, -- fax
decode(p.status, 'A', 'ACTIVE', 'INACTIVE'), -- status
NULL, -- ExpirationDate
p.party_id -- System Id
INTO
user_name,
display_name,
description,
notification_pref,
language,
territory,
email_address,
fax,
status,
expiration_date,
system_id
FROM
HZ_PARTIES p,
HZ_CONTACT_POINTS cp,
HZ_PERSON_LANGUAGE pl,
FND_LANGUAGES fl
WHERE
p.party_id = PartyId
AND p.party_type = 'GROUP'
AND cp.owner_table_name(+) = 'HZ_PARTIES'
AND cp.owner_table_id (+) = p.party_id
AND cp.contact_point_type(+) = 'EMAIL'
AND cp.primary_flag(+) = 'Y'
AND cp.status(+) = 'A'
AND pl.party_id (+) = p.party_id
AND pl.primary_language_indicator(+) = 'Y'
AND pl.status(+) = 'A'
AND pl.language_name = fl.language_code(+);
IF (p_update = TRUE) then
wf_event.AddParameterToList('UpdateOnly', 'TRUE', List);
(p_message=>'Bfr Selecting the data',
p_prefix =>l_debug_prefix,
p_msg_level=>fnd_log.level_statement);
SELECT
'HZ_PARTY' -- USER_ORIG_SYSTEM
,sp.party_id -- USER_ORIG_SYSTEM_ID
,'HZ_GROUP' -- ROLE_ORIG_SYSTEM
,op.party_id -- ROLE_ORIG_SYSTEM_ID
,pr.start_date -- START_DATE
,DECODE(pr.status, 'A', pr.end_date, 'I',
(CASE
WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) < 0 THEN pr.end_date
WHEN MONTHS_BETWEEN(NVL(pr.end_date, SYSDATE), SYSDATE) > 0 THEN SYSDATE
ELSE SYSDATE
END), SYSDATE) -- EndDate --Bug#5209709 fix
INTO
user_system,
user_system_id,
role_system,
role_system_id,
start_date,
expiration_date
FROM
hz_relationships pr
,hz_parties sp
,hz_parties op
WHERE
pr.relationship_id = RelationshipId
and pr.subject_table_name = 'HZ_PARTIES'
and pr.object_table_name = 'HZ_PARTIES'
and sp.party_id = pr.subject_id
and sp.party_type = 'PERSON'
and op.party_id = pr.object_id
and op.party_type = 'GROUP';
END; -- anonymous block for selecting the person-group relationships end