Sample SQL Queries for the UM_USER_RECON and UM_USER_SYNC SQL Query Names

This appendix lists sample SQL queries that can be used to update the UM_USER_RECON and UM_USER_SYNC queries in the search.properties file.

This appendix contains the following sections:

• Sample SQL Queries Updated to Include Single-Valued Attributes

• Sample SQL Queries Updated to Include Multivalued Attributes

Sample SQL Queries Updated to Include Single-Valued Attributes

Use this SQL query to update the UM_USER_RECON and UM_USER_SYNC queries.

If you have added a single-valued attribute (Customer Id) as part of performing the procedure described Adding New Attributes for Reconciliation and Provisioning:

with roledata as ( \
               select fa.application_id,fa.application_short_name,wlr.name,ura.user_name user_name ,ura.start_date active_from,ura.end_date active_to from wf_local_roles wlr,wf_user_role_assignments ura,fnd_application fa where ura.role_name like 'UMX%' AND wlr.parent_orig_system = 'UMX' and  wlr.name=ura.role_name and fa.application_short_name = wlr.owner_tag and  ( (ura.start_date < nvl(ura.end_date, TO_DATE('31-DEC-4712','dd-mon-yyyy')) and ura.start_date > sysdate) or sysdate between ura.start_date and nvl(ura.end_date, TO_DATE('31-DEC-4712','dd-mon-yyyy')) ) \
               ) , party as ( \
               select USER_ID AS user_id,USER_GUID AS user_guid,sysdate  as system_date, LAST_UPDATE_DATE DATE_UPDATED, case when password_lifespan_days > 0 then 'Days' when password_lifespan_accesses > 0 then 'Accesses' else 'None' end as PASSWORD_EXP_TYPE, case when password_lifespan_days > 0 then password_lifespan_days when password_lifespan_accesses > 0 then password_lifespan_accesses else null end as PASSWORD_LIFESPAN, EMPLOYEE_ID as EMPLOYEE_ID, USER_NAME AS user_name,  TO_NUMBER(SUPPLIER_ID) as SUPPLIER_ID, session_number as session_number,CUSTOMER_ID as CUSTOMER_ID, DESCRIPTION as description, EMAIL_ADDRESS as EMAIL_ADDRESS, FAX as FAX, START_DATE  AS START_DATE, END_DATE AS END_DATE,'Supplier' as PARTY_TYPE,b.person_first_name as party_first_name,b.person_last_name as party_last_name,b.party_id as party_id ,b.party_name as supplier_name,b.sup_party_id as supplier_party_id,null as security_group_id, null as responsibility_ID,null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE,null as ROLE_ID ,null as role_start_date ,null as expiration_date ,null as RESP_DESCRIPTION,null as ROLE_APP_ID  from fnd_user a,(   select hp.party_id,hp.person_first_name,hp.person_last_name,hp1.party_name,hp1.party_id as sup_party_id FROM hz_relationships hr , hz_parties hp,hz_parties hp1 where hr.subject_ID = hp1.party_id and hr.object_ID=hp.party_id and hr.subject_type='ORGANIZATION' and hr.object_type='PERSON') b where a.person_party_id= b.party_id \
               union all \
               select USER_ID AS user_id,USER_GUID AS user_guid,sysdate  as system_date, LAST_UPDATE_DATE  DATE_UPDATED, case when password_lifespan_days > 0 then 'Days' when password_lifespan_accesses > 0 then 'Accesses' else 'None' end as PASSWORD_EXP_TYPE, case when password_lifespan_days > 0 then password_lifespan_days when password_lifespan_accesses > 0 then password_lifespan_accesses else null end as PASSWORD_LIFESPAN, EMPLOYEE_ID as EMPLOYEE_ID, USER_NAME AS user_name,  TO_NUMBER(SUPPLIER_ID) as SUPPLIER_ID, session_number as session_number,CUSTOMER_ID as CUSTOMER_ID, DESCRIPTION as description, EMAIL_ADDRESS as EMAIL_ADDRESS, FAX as FAX, START_DATE AS START_DATE,END_DATE AS END_DATE,'Party' as PARTY_TYPE,b.person_first_name as party_first_name,b.person_last_name as party_last_name,b.party_id as party_id,null as supplier_name,null as supplier_party_id,null as security_group_id, null as responsibility_ID,null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE ,null as ROLE_ID ,null as role_start_date ,null as expiration_date ,null as RESP_DESCRIPTION,null as ROLE_APP_ID from fnd_user a,(   select hp.party_id,hp.person_first_name,hp.person_last_name  FROM hz_parties hp) b where a.person_party_id not in (select hr.object_ID FROM hz_relationships hr where hr.subject_type='ORGANIZATION' and hr.object_type='PERSON') and a.person_party_id= b.party_id \
               union all \
               select USER_ID AS user_id,USER_GUID AS user_guid,sysdate as system_date, LAST_UPDATE_DATE  DATE_UPDATED, case when password_lifespan_days > 0 then 'Days' when password_lifespan_accesses > 0 then 'Accesses' else 'None' end as PASSWORD_EXP_TYPE, case when password_lifespan_days > 0 then password_lifespan_days when password_lifespan_accesses > 0 then password_lifespan_accesses else null end as PASSWORD_LIFESPAN, EMPLOYEE_ID as EMPLOYEE_ID, USER_NAME AS user_name,  TO_NUMBER(SUPPLIER_ID) as SUPPLIER_ID, session_number as session_number,CUSTOMER_ID as CUSTOMER_ID, DESCRIPTION as description, EMAIL_ADDRESS as EMAIL_ADDRESS, FAX as FAX, START_DATE AS START_DATE,END_DATE  AS END_DATE,null as PARTY_TYPE,null as party_first_name,null as party_last_name,null as party_id,null as supplier_name,null as supplier_party_id,null as security_group_id, null as responsibility_ID,null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE ,null as ROLE_ID ,null as role_start_date ,null as expiration_date ,null as RESP_DESCRIPTION,null as ROLE_APP_ID from fnd_user a where person_party_id IS NULL \
               ) \
               select * from ( \
               select RESULTTABLE.*,ROW_NUMBER() OVER (ORDER BY user_id) AS Row_Num from \
               ( \
               select * from party \
               union all \
               select f.USER_ID AS user_id,f.user_guid as user_guid,f.system_date,f.DATE_UPDATED,f.PASSWORD_EXP_TYPE,f.PASSWORD_LIFESPAN,f.EMPLOYEE_ID, f.USER_NAME,f.SUPPLIER_ID, f.session_number,f.CUSTOMER_ID, f.DESCRIPTION , f.EMAIL_ADDRESS , f.FAX , f.START_DATE,f.END_DATE,f.PARTY_TYPE, f.party_first_name,f.party_last_name,f.party_id,f.supplier_name,f.supplier_party_id,s.security_group_id as security_group_id, (CONCAT(a.application_ID || '~', r.responsibility_id)) as responsibility_id,ur.RESPONSIBILITY_APPLICATION_ID as RESPONSIBILITY_APP_ID,ur.END_DATE  AS RESP_END_DATE,ur.START_DATE  AS RESP_START_DATE, null as ROLE_ID ,null as role_start_date ,null as expiration_date ,ur.DESCRIPTION as RESP_DESCRIPTION,null as ROLE_APP_ID from party f,FND_USER_RESP_GROUPS_DIRECT ur, fnd_application_vl a, fnd_responsibility_vl r, fnd_security_groups_vl s where f.user_id = ur.user_id and ur.responsibility_ID = r.responsibility_ID and r.application_ID = a.application_ID and ur.security_group_id = s.security_group_id and  ( (ur.START_DATE < nvl(ur.END_DATE, TO_DATE('31-DEC-4712','dd-mon-yyyy')) and ur.START_DATE > sysdate) or sysdate between ur.START_DATE and nvl(ur.END_DATE, TO_DATE('31-DEC-4712','dd-mon-yyyy')) ) \
               union all \
               select f.USER_ID AS user_id,f.user_guid as user_guid,f.system_date,f.DATE_UPDATED,f.PASSWORD_EXP_TYPE,f.PASSWORD_LIFESPAN,f.EMPLOYEE_ID, f.USER_NAME,f.SUPPLIER_ID, f.session_number,f.CUSTOMER_ID, f.DESCRIPTION , f.EMAIL_ADDRESS , f.FAX , f.START_DATE,f.END_DATE,f.PARTY_TYPE, f.party_first_name,f.party_last_name,f.party_id,f.supplier_name,f.supplier_party_id, null as security_group_id, null as responsibility_id, null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE,  (CONCAT(r.application_id || '~',   r.name)) AS ROLE_ID ,r.active_from  AS role_start_date,r.active_to  AS expiration_date,null as RESP_DESCRIPTION,r.application_id as ROLE_APP_ID  from party f , roledata r where  f.user_name = r.user_name \
               ) RESULTTABLE  \
               --<FILTER> \
               )  WHERE Row_Num BETWEEN <START_ROW_NUMBER> and <END_ROW_NUMBER>

Sample SQL Queries Updated to Include Multivalued Attributes

Use this SQL query to update the UM_USER_RECON and UM_USER_SYNC queries.

The following SQL query can be used to update the UM_USER_RECON and UM_USER_SYNC queries if you have added new security attributes as part of performing the procedure described in Updating the Connector Bundle:

with roledata as ( \
               select fa.application_id,fa.application_short_name,wlr.name,ura.user_name user_name ,ura.start_date active_from,ura.end_date active_to from wf_local_roles wlr,wf_user_role_assignments ura,fnd_application fa where   ura.role_name like 'UMX%' AND wlr.parent_orig_system = 'UMX' and  wlr.name=ura.role_name and fa.application_short_name = wlr.owner_tag and  ( (ura.start_date < nvl(ura.end_date, TO_DATE('31-DEC-4712','dd-mon-yyyy')) and ura.start_date > sysdate) or sysdate between ura.start_date and nvl(ura.end_date, TO_DATE('31-DEC-4712','dd-mon-yyyy')) ) \
               ) ,  securitydata as ( \
               select userak.web_user_id as user_id,userak.attribute_code as SECURITY_ATTR_NAME, userak.attribute_application_id as SECURITY_APP_ID,NVL(userak.VARCHAR2_VALUE,NVL(to_char(userak.DATE_VALUE),userak.NUMBER_VALUE)) as SECURITY_ATTR_VALUE,ak.DATA_TYPE as SECURITY_ATTR_TYPE from ak_web_user_sec_attr_values userak,AK_ATTRIBUTES ak where ak.attribute_code=userak.attribute_code and ak.attribute_application_id= userak.attribute_application_id \
               ), party as ( \
               select null as SECURITY_ATTR_NAME, null as SECURITY_APP_ID, null as SECURITY_ATTR_VALUE,null as SECURITY_ATTR_TYPE,USER_ID AS user_id,USER_GUID AS user_guid,sysdate  as system_date,    LAST_UPDATE_DATE  DATE_UPDATED, case when password_lifespan_days > 0 then 'Days' when password_lifespan_accesses > 0 then 'Accesses' else 'None' end as PASSWORD_EXP_TYPE, case when password_lifespan_days > 0 then password_lifespan_days when password_lifespan_accesses > 0 then password_lifespan_accesses else null end as PASSWORD_LIFESPAN, EMPLOYEE_ID as  EMPLOYEE_ID, USER_NAME AS user_name,  TO_NUMBER(SUPPLIER_ID) as   SUPPLIER_ID, session_number as session_number,CUSTOMER_ID as CUSTOMER_ID, DESCRIPTION as description, EMAIL_ADDRESS as EMAIL_ADDRESS, FAX as FAX, START_DATE  AS START_DATE, END_DATE  AS END_DATE,'Supplier' as PARTY_TYPE,b.person_first_name as party_first_name,b.person_last_name as party_last_name,b.party_id as party_id ,b.party_name as supplier_name,b.sup_party_id as supplier_party_id,null as security_group_id, null as responsibility_ID,null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE,null as ROLE_ID ,null as role_start_date ,null as expiration_date ,null as RESP_DESCRIPTION,null as ROLE_APP_ID  from fnd_user a,(   select hp.party_id,hp.person_first_name,hp.person_last_name,hp1.party_name,hp1.party_id as sup_party_id FROM hz_relationships hr , hz_parties hp,hz_parties hp1 where hr.subject_ID = hp1.party_id and hr.object_ID=hp.party_id
 and hr.subject_type='ORGANIZATION' and hr.object_type='PERSON') b where a.person_party_id= b.party_id \
               union all \
               select null as SECURITY_ATTR_NAME, null as SECURITY_APP_ID, null as SECURITY_ATTR_VALUE,null as SECURITY_ATTR_TYPE,USER_ID AS user_id,USER_GUID AS user_guid,sysdate  as system_date,    LAST_UPDATE_DATE  DATE_UPDATED, case when password_lifespan_days > 0 then 'Days' when password_lifespan_accesses > 0 then 'Accesses' else 'None' end as PASSWORD_EXP_TYPE, case when password_lifespan_days > 0 then password_lifespan_days when password_lifespan_accesses > 0 then password_lifespan_accesses else null end as PASSWORD_LIFESPAN, EMPLOYEE_ID as  EMPLOYEE_ID, USER_NAME AS user_name,  TO_NUMBER(SUPPLIER_ID) as   SUPPLIER_ID, session_number as session_number,CUSTOMER_ID as CUSTOMER_ID,    DESCRIPTION as description, EMAIL_ADDRESS as EMAIL_ADDRESS, FAX as FAX, START_DATE  AS START_DATE,END_DATE  AS END_DATE,'Party' as PARTY_TYPE,b.person_first_name as party_first_name,b.person_last_name as party_last_name,b.party_id as party_id,null as supplier_name,null as supplier_party_id,null as security_group_id, null as responsibility_ID,null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE ,null as ROLE_ID ,null as role_start_date ,null as expiration_date ,null as RESP_DESCRIPTION,null as ROLE_APP_ID from fnd_user a,(   select hp.party_id,hp.person_first_name,hp.person_last_name  FROM hz_parties hp) b where a.person_party_id not in (select hr.object_ID FROM hz_relationships hr where hr.subject_type='ORGANIZATION' and hr.object_type='PERSON') and a.person_party_id= b.party_id \
               union all \
               select null as SECURITY_ATTR_NAME, null as SECURITY_APP_ID, null as SECURITY_ATTR_VALUE,null as SECURITY_ATTR_TYPE,USER_ID AS user_id,USER_GUID AS user_guid,sysdate  as system_date,    LAST_UPDATE_DATE  DATE_UPDATED, case when password_lifespan_days > 0 then 'Days' when password_lifespan_accesses > 0 then 'Accesses' else 'None' end as PASSWORD_EXP_TYPE, case when password_lifespan_days > 0 then password_lifespan_days when password_lifespan_accesses > 0 then password_lifespan_accesses else null end as PASSWORD_LIFESPAN, EMPLOYEE_ID as  EMPLOYEE_ID, USER_NAME AS user_name,  TO_NUMBER(SUPPLIER_ID) as   SUPPLIER_ID, session_number as session_number,CUSTOMER_ID as CUSTOMER_ID,    DESCRIPTION as description, EMAIL_ADDRESS as EMAIL_ADDRESS, FAX as FAX, START_DATE  AS START_DATE,END_DATE  AS END_DATE,null as PARTY_TYPE,null as party_first_name,null as party_last_name,null as party_id,null as supplier_name,null as supplier_party_id,null as security_group_id, null as responsibility_ID,null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE ,null as ROLE_ID ,null as role_start_date ,null as expiration_date ,null as RESP_DESCRIPTION,null as ROLE_APP_ID from fnd_user a where person_party_id IS NULL \
               ) \
               select * from ( \
               select RESULTTABLE.*,ROW_NUMBER() OVER (ORDER BY user_id) AS Row_Num from \
               ( \
               select * from party \
               union all \
               select f.SECURITY_ATTR_NAME, f.SECURITY_APP_ID, f.SECURITY_ATTR_VALUE,f.SECURITY_ATTR_TYPE,f.USER_ID AS user_id,f.user_guid as user_guid,f.system_date,f.DATE_UPDATED,f.PASSWORD_EXP_TYPE,f.PASSWORD_LIFESPAN,f.EMPLOYEE_ID, f.USER_NAME,f.SUPPLIER_ID, f.session_number,f.CUSTOMER_ID, f.DESCRIPTION , f.EMAIL_ADDRESS , f.FAX , f.START_DATE,f.END_DATE,f.PARTY_TYPE, f.party_first_name,f.party_last_name,f.party_id,f.supplier_name,f.supplier_party_id,s.security_group_id as security_group_id, (CONCAT(a.application_ID || '~', r.responsibility_id)) as responsibility_id,ur.RESPONSIBILITY_APPLICATION_ID as RESPONSIBILITY_APP_ID,ur.END_DATE  AS RESP_END_DATE,ur.START_DATE  AS RESP_START_DATE, null as ROLE_ID ,null as role_start_date ,null as expiration_date ,ur.DESCRIPTION as RESP_DESCRIPTION,null as ROLE_APP_ID from party f,FND_USER_RESP_GROUPS_DIRECT ur, fnd_application_vl a, fnd_responsibility_vl r, fnd_security_groups_vl s where f.user_id = ur.user_id and ur.responsibility_ID = r.responsibility_ID and r.application_ID = a.application_ID and ur.security_group_id = s.security_group_id and  ( (ur.START_DATE < nvl(ur.END_DATE, TO_DATE('31-DEC-4712','dd-mon-yyyy')) and ur.START_DATE > sysdate) or sysdate between ur.START_DATE and nvl(ur.END_DATE, TO_DATE('31-DEC-4712','dd-mon-yyyy')) ) \
               union all \
               select f.SECURITY_ATTR_NAME, f.SECURITY_APP_ID, f.SECURITY_ATTR_VALUE,f.SECURITY_ATTR_TYPE,f.USER_ID AS user_id,f.user_guid as user_guid,f.system_date,f.DATE_UPDATED,f.PASSWORD_EXP_TYPE,f.PASSWORD_LIFESPAN,f.EMPLOYEE_ID, f.USER_NAME,f.SUPPLIER_ID, f.session_number,f.CUSTOMER_ID, f.DESCRIPTION , f.EMAIL_ADDRESS , f.FAX , f.START_DATE,f.END_DATE,f.PARTY_TYPE, f.party_first_name,f.party_last_name,f.party_id,f.supplier_name,f.supplier_party_id, null as security_group_id, null as responsibility_id, null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE,  (CONCAT(r.application_id || '~',   r.name)) AS ROLE_ID ,r.active_from  AS role_start_date,r.active_to  AS expiration_date,null as RESP_DESCRIPTION,r.application_id as ROLE_APP_ID  from party f , roledata r where  f.user_name = r.user_name \
               union all \
               select sa.SECURITY_ATTR_NAME as SECURITY_ATTR_NAME, sa.SECURITY_APP_ID as SECURITY_APP_ID, sa.SECURITY_ATTR_VALUE as SECURITY_ATTR_VALUE,sa.SECURITY_ATTR_TYPE as SECURITY_ATTR_TYPE,f.USER_ID AS user_id,f.user_guid as user_guid,f.system_date,f.DATE_UPDATED,f.PASSWORD_EXP_TYPE,f.PASSWORD_LIFESPAN,f.EMPLOYEE_ID, f.USER_NAME,f.SUPPLIER_ID, f.session_number,f.CUSTOMER_ID, f.DESCRIPTION , f.EMAIL_ADDRESS , f.FAX , f.START_DATE,f.END_DATE,f.PARTY_TYPE, f.party_first_name,f.party_last_name,f.party_id,f.supplier_name,f.supplier_party_id, null as security_group_id, null as responsibility_id, null as RESPONSIBILITY_APP_ID, null AS RESP_END_DATE,null AS RESP_START_DATE, null AS ROLE_ID ,null  AS role_start_date,null  AS expiration_date,null as RESP_DESCRIPTION,null as ROLE_APP_ID  from party f , securitydata sa where  f.user_id = sa.user_id \
               ) RESULTTABLE  \
               --<FILTER> \
               )  WHERE Row_Num BETWEEN <START_ROW_NUMBER> and <END_ROW_NUMBER>