ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN E811F942597A2075E0530100007FF4F2 Blitz Upload Mode Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual US D7815153ACE3306DE0530100007FD65A FND Business Groups Y select pbg.name value, pbg.short_name description from per_business_groups pbg US 8E2FF36EDE9E79D2E0530100007F1FF2 FND Responsibility Name Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name US 15B2E2DBB73EE8CCE0630100007F9602 User Name Y select fu.user_name value, coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address ) description from fnd_user fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf where fu.employee_id=papf.person_id(+) and :$flex$.upload_mode like '%' || xxen_upload.action_update order by fu.user_name US 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US F869CD383AFA5087E0530100007F3D0C select y.* from ( select null action_, null status_, null message_, null modified_columns_, fu.user_name, null unencrypted_password, fu.description, papf.business_group, papf.full_name person, papf.employee_number, papf.first_name, papf.last_name, papf.sex gender, papf.date_of_birth date_of_birth, fu.email_address, fu.fax, fu.start_date user_start_date, fu.end_date user_end_date, fu.last_logon_date last_logon_date, xxen_util.user_name(fu.created_by) created_by, xxen_util.client_time(fu.creation_date) user_creation_date, xxen_util.user_name(fu.last_updated_by) user_last_updated_by, xxen_util.client_time(fu.last_update_date) user_last_update_date, frv.responsibility_name, frv.application_name responsiblity_application, frv.start_date responsiblity_start, frv.end_date responsiblity_end, frv.description assignment_description, 0 upload_row from fnd_user fu, (select pbg.name business_group, papf.* from per_all_people_f papf, per_business_groups pbg where papf.business_group_id=pbg.business_group_id and nvl(papf.effective_end_date,sysdate)>=trunc(sysdate)) papf, ( select furgd.user_id, frv.responsibility_name, fa.application_short_name application_name, furgd.start_date, furgd.end_date, furgd.description from fnd_responsibility_vl frv, fnd_application fa, fnd_user_resp_groups_direct furgd where 2=2 and frv.application_id=fa.application_id and frv.application_id=furgd.responsibility_application_id and frv.responsibility_id=furgd.responsibility_id ) frv where 1=1 and fu.employee_id=papf.person_id(+) and fu.user_id=frv.user_id(+) ) y Y U A xxen_fnd_upload.create_update_user F FND User Upload Liste et mise à jour de tous les utilisateurs EBS et de leurs responsabilités US FND User Upload Listing and updating all EBS users and their responsiblities Enginatics Upload 1=1 2=2 1 10 1=1 :upload_mode like '%' || xxen_upload.action_update LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 Y select xxen_upload.action_create id, xxen_upload.action_meaning(xxen_upload.action_create) value, null description from dual union all select xxen_upload.action_create || ',' || xxen_upload.action_update id, xxen_upload.action_meaning(xxen_upload.action_create)||', '||xxen_upload.action_meaning(xxen_upload.action_update) value, null description from dual C,U Y US Upload Mode 2 20 1=1 fu.user_name=:user_name LOV User Name 15B2E2DBB73EE8CCE0630100007F9602 Y select fu.user_name value, coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address ) description from fnd_user fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf where fu.employee_id=papf.person_id(+) and :$flex$.upload_mode like '%' || xxen_upload.action_update order by fu.user_name US User Name 3 2=2 furgd.user_id in (select fu.user_id from fnd_user fu where fu.user_name=:user_name) US User Name 4 40 1=1 fu.user_id in ( select furg.user_id from fnd_responsibility_vl frv, fnd_user_resp_groups furg where frv.responsibility_name=:responsibility and frv.responsibility_id=furg.responsibility_id and frv.application_id=furg.responsibility_application_id ) LOV custom Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv where :$flex$.upload_mode like '%' || xxen_upload.action_update order by frv.responsibility_name US Has Access to Responsibility 5 50 1=1 fu.user_id in ( select furg.user_id from fnd_application_vl fav, fnd_user_resp_groups furg where fav.application_name=:application and fav.application_id=furg.responsibility_application_id ) LOV custom Y select fav.application_name value, fav.application_short_name description from fnd_application_vl fav where :$flex$.upload_mode like '%' || xxen_upload.action_update order by fav.application_name US Has Access to Application 6 60 1=1 sysdate between fu.start_date and nvl(fu.end_date,sysdate) LOV Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual Y US Active only 7 2=2 nvl(furgd.end_date,sysdate)>=trunc(sysdate) US Active only 8 70 1=1 fu.creation_date>=:creation_date_from Date US Creation Date From 9 80 1=1 fu.creation_date<:creation_date_to+1 Date last_day(:$flex$.creation_date_from) US Creation Date To 10 90 1=1 fu.creation_date>=sysdate-:days Number US Created within days :$flex$.creation_date_from Creation Date From Y Creation Date To :$flex$.upload_mode Upload Mode Has Access to Application :$flex$.upload_mode Upload Mode Has Access to Responsibility :$flex$.upload_mode Upload Mode User Name 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 USER_NAME LOV custom varchar2 Y select fu.user_name value, trim(coalesce( trim(papf.first_name||' '||papf.last_name), fu.description, fu.email_address, papf.email_address )||fu.inactive) description from (select case when sysdate between fu.start_date and nvl(fu.end_date,sysdate) then null else ' (inactive)' end inactive, fu.* from fnd_user fu) fu, (select papf.* from per_all_people_f papf where sysdate between papf.effective_start_date and papf.effective_end_date) papf, ( select distinct furg.user_id, count(*) over (partition by furg.user_id) resp_count, max(fr.responsibility_key) over (partition by furg.user_id) max_responsibility_key from fnd_responsibility fr, fnd_user_resp_groups_direct furg where fr.responsibility_id=furg.responsibility_id and fr.application_id=furg.responsibility_application_id ) furg where fu.employee_id=papf.person_id(+) and fu.user_id=furg.user_id(+) and not (furg.resp_count=1 and furg.max_responsibility_key='IRC_EXT_CANDIDATE') order by fu.inactive desc, fu.user_name Y 6 UNENCRYPTED_PASSWORD Char varchar2 7 DESCRIPTION Char varchar2 select fu.description from fnd_user fu where fu.user_name=:$column$.user_name 8 BUSINESS_GROUP LOV varchar2 FND Business Groups D7815153ACE3306DE0530100007FD65A Y select pbg.name value, pbg.short_name description from per_business_groups pbg select pbg.business_group_id id, pbg.name value from per_business_groups pbg select pbg.name from fnd_user fu, per_business_groups pbg, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and papf.business_group_id=pbg.business_group_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 9 PERSON LOV custom varchar2 Y select papf.full_name value, null description from per_all_people_f papf, per_business_groups pbg where papf.business_group_id=pbg.business_group_id and pbg.name=:$column$.business_group and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) select papf.full_name from fnd_user fu, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 10 EMPLOYEE_NUMBER LOV custom varchar2 Y select papf.employee_number value, papf.email_address description from per_all_people_f papf, per_business_groups pbg where papf.business_group_id=pbg.business_group_id and papf.full_name=:$column$.person and pbg.name=:$column$.business_group and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) select papf.employee_number from fnd_user fu, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 11 FIRST_NAME Char varchar2 select papf.first_name from fnd_user fu, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 12 LAST_NAME Char varchar2 select papf.last_name from fnd_user fu, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 13 GENDER LOV custom varchar2 Y select hl.lookup_code value, hl.meaning description from hr_lookups hl where hl.lookup_type='SEX' select papf.sex from fnd_user fu, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 14 DATE_OF_BIRTH Date date select papf.date_of_birth from fnd_user fu, per_all_people_f papf where fu.user_name=:$column$.user_name and papf.person_id=fu.employee_id and ( trunc(sysdate) between papf.effective_start_date and nvl(papf.effective_end_date, trunc(sysdate)) or papf.effective_start_date > sysdate ) 15 EMAIL_ADDRESS Char varchar2 select fu.email_address from fnd_user fu where fu.user_name=:$column$.user_name 16 FAX Char varchar2 select fu.fax from fnd_user fu where fu.user_name=:$column$.user_name 17 USER_START_DATE Date date select fu.start_date from fnd_user fu where fu.user_name=:$column$.user_name Y 18 USER_END_DATE Date date select fu.end_date from fnd_user fu where fu.user_name=:$column$.user_name 19 LAST_LOGON_DATE Date date select fu.last_logon_date from fnd_user fu where fu.user_name=:$column$.user_name 20 CREATED_BY Char varchar2 select xxen_util.user_name(fu.created_by) created_by from fnd_user fu where fu.user_name=:$column$.user_name 21 USER_CREATION_DATE DateTime date select fu.creation_date from fnd_user fu where fu.user_name=:$column$.user_name 22 USER_LAST_UPDATED_BY Char varchar2 select xxen_util.user_name(fu.last_updated_by) last_updated_by from fnd_user fu where fu.user_name=:$column$.user_name 23 USER_LAST_UPDATE_DATE DateTime date select fu.last_update_date from fnd_user fu where fu.user_name=:$column$.user_name 24 RESPONSIBILITY_NAME LOV varchar2 FND Responsibility Name 8E2FF36EDE9E79D2E0530100007F1FF2 Y select frv.responsibility_name value, frv.description from fnd_responsibility_vl frv order by frv.responsibility_name Y 25 RESPONSIBLITY_APPLICATION LOV custom varchar2 Y select fav.application_short_name value, fav.application_name description from fnd_application_vl fav where fav.application_id in (select frv.application_id from fnd_responsibility_vl frv where frv.responsibility_name=:$column$.responsibility_name) order by fav.application_short_name Y 26 RESPONSIBLITY_START Date date sysdate Y 27 RESPONSIBLITY_END Date date 28 ASSIGNMENT_DESCRIPTION Char varchar2 29 UPLOAD_ROW Number number U 3 action_ in Char Y ACTION_CODE_ U 4 status_ out Char STATUS_CODE_ U 5 message_ out Char MESSAGE_ U 6 p_user_name in Char Y USER_NAME U 7 p_owner in Char Y U 8 p_unencrypted_password in Char Y UNENCRYPTED_PASSWORD U 9 p_session_number in Number Y U 10 p_start_date in Date Y USER_START_DATE U 11 p_end_date in Date Y USER_END_DATE U 12 p_last_logon_date in Date Y U 13 p_description in Char Y DESCRIPTION U 14 p_password_date in Date Y U 15 p_password_accesses_left in Number Y U 16 p_password_lifespan_accesses in Number Y U 17 p_password_lifespan_days in Number Y U 18 p_email_address in Char Y EMAIL_ADDRESS U 19 p_fax in Char Y FAX U 20 p_customer_id in Number Y U 21 p_supplier_id in Number Y U 22 p_old_password in Char Y U 24 p_emp_full_name in Char Y PERSON U 25 p_emp_last_name in Char Y LAST_NAME U 26 p_emp_first_name in Char Y FIRST_NAME U 27 p_emp_sex in Char Y GENDER U 28 p_emp_business_group_id in Number Y BUSINESS_GROUP U 29 p_emp_number in Char Y EMPLOYEE_NUMBER U 30 p_emp_date_of_birth in Date Y DATE_OF_BIRTH U 31 p_emp_email_address in Char Y EMAIL_ADDRESS U 32 p_resp_application in Char Y RESPONSIBLITY_APPLICATION U 33 p_resp_name in Char Y RESPONSIBILITY_NAME U 34 p_resp_description in Char Y ASSIGNMENT_DESCRIPTION U 35 p_resp_start_date in Date Y RESPONSIBLITY_START U 36 p_resp_end_date in Date Y RESPONSIBLITY_END Order By upload_row, case when nvl(user_end_date,sysdate)>=trunc(sysdate) then 1 else 2 end, to_date(user_creation_date) desc, user_name Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(x.user_name,xu.user_name) user_name, null unencrypted_password, nvl(x.description,xu.description) description, nvl(x.business_group,xu.business_group) business_group, nvl(x.person,xu.person) person, nvl(x.employee_number,xu.employee_number) employee_number, nvl(x.first_name,xu.first_name) first_name, nvl(x.last_name,xu.last_name) last_name, nvl(x.gender,xu.gender) gender, nvl(x.emp_date_of_birth,xu.date_of_birth) date_of_birth, nvl(x.email_address,xu.email_address) email_address, nvl(x.fax,xu.fax) fax, nvl(x.user_start_date,xu.user_start_date) user_start_date, nvl(x.user_end_date,xu.user_end_date) user_end_date, x.last_logon_date, x.created_by, x.user_creation_date, x.user_last_updated_by, x.user_last_update_date, nvl(x.responsibility_name,xu.responsibility_name) responsibility_name, nvl(x.responsiblity_application,xu.responsiblity_application) responsiblity_application, nvl(x.responsiblity_start,xu.responsiblity_start) responsiblity_start, nvl(x.responsiblity_end,xu.responsiblity_end) responsiblity_end, nvl(x.assignment_description,xu.assignment_description) assignment_description, xu.upload_row from ( select fu.user_name, null unencrypted_password, fu.description, papf.business_group, papf.full_name person, papf.employee_number, papf.first_name, papf.last_name, papf.sex gender, papf.date_of_birth emp_date_of_birth, papf.email_address hr_email_address, fu.email_address, fu.fax, fu.start_date user_start_date, fu.end_date user_end_date, fu.last_logon_date last_logon_date, xxen_util.user_name(fu.created_by) created_by, xxen_util.client_time(fu.creation_date) user_creation_date, xxen_util.user_name(fu.last_updated_by) user_last_updated_by, xxen_util.client_time(fu.last_update_date) user_last_update_date, frv.responsibility_name, frv.application_name responsiblity_application, frv.start_date responsiblity_start, frv.end_date responsiblity_end, frv.description assignment_description from fnd_user fu, (select pbg.name business_group,pp.* from per_all_people_f pp, per_business_groups pbg where pbg.business_group_id=pp.business_group_id and nvl(pp.effective_end_date,sysdate)>=trunc(sysdate)) papf, ( select furgd.user_id, frv.responsibility_name, fa.application_short_name application_name, furgd.start_date, furgd.end_date, furgd.description from fnd_responsibility_vl frv, fnd_application fa, fnd_user_resp_groups_direct furgd where frv.application_id=fa.application_id and frv.application_id=furgd.responsibility_application_id and frv.responsibility_id=furgd.responsibility_id and nvl(furgd.end_date,sysdate)>=trunc(sysdate) ) frv where fu.employee_id=papf.person_id(+) and fu.user_id=frv.user_id(+) ) x right join xxen_fnd_user_upload_1732_u xu on upper(x.user_name)=upper(xu.user_name) and nvl(x.responsiblity_application,'!')=nvl(xu.responsiblity_application,'!') and nvl(x.responsibility_name,'!')=nvl(xu.responsibility_name,'!') and trunc(nvl(x.responsiblity_start,to_date('1','j')))=trunc(nvl(xu.responsiblity_start,to_date('1','j'))) and trunc(nvl(x.responsiblity_end,to_date('1','j')))=trunc(nvl(xu.responsiblity_end,to_date('1','j'))) Default value :$column$.user_name USER_NAME BUSINESS_GROUP Default value :$column$.user_name USER_NAME CREATED_BY Default value :$column$.user_name USER_NAME DATE_OF_BIRTH Default value :$column$.user_name USER_NAME DESCRIPTION Default value :$column$.user_name USER_NAME EMAIL_ADDRESS LOV query :$column$.business_group BUSINESS_GROUP EMPLOYEE_NUMBER LOV query :$column$.person PERSON EMPLOYEE_NUMBER Default value :$column$.user_name USER_NAME EMPLOYEE_NUMBER Default value :$column$.user_name USER_NAME FAX Default value :$column$.user_name USER_NAME FIRST_NAME Default value :$column$.user_name USER_NAME GENDER Default value :$column$.user_name USER_NAME LAST_LOGON_DATE Default value :$column$.user_name USER_NAME LAST_NAME LOV query :$column$.business_group BUSINESS_GROUP PERSON Default value :$column$.user_name USER_NAME PERSON LOV query :$column$.responsibility_name RESPONSIBILITY_NAME RESPONSIBLITY_APPLICATION Default value :$column$.user_name USER_NAME USER_CREATION_DATE Default value :$column$.user_name USER_NAME USER_END_DATE Default value :$column$.user_name USER_NAME USER_LAST_UPDATED_BY Default value :$column$.user_name USER_NAME USER_LAST_UPDATE_DATE Default value :$column$.user_name USER_NAME USER_START_DATE