ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 8E2FF36EDEFA79D2E0530100007F1FF2 Blitz Report Category Y select xrcv.category value, (select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description from xxen_report_categories_v xrcv order by xrcv.category US 8E2FF36EDEA879D2E0530100007F1FF2 Blitz Report Name Y select xrv.report_name value, xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description from xxen_reports_v xrv where nvl(xrv.type,'x')<>'S' and (fnd_profile.value('XXEN_REPORT_ACCESS')='S' or nvl(xrv.type,'x')<>'U') order by xrv.report_name US B04763D730F050D7E0530100007F06B3 Blitz Report Name (wildcard) select xrv.report_name value, xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description from xxen_reports_v xrv order by xrv.report_name US 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 8E2FF36EDEA679D2E0530100007F1FF2 Yes Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual US 3D4261D9251B5590E0630100007F92CBY select null action_, null status_, null message_, null modified_columns_, xrv.report_name, xrv.type_dsp type, xrv.category, xxen_util.meaning(nvl(xrav.include_exclude,'I'),'INCLUDE_EXCLUDE',0) include_exclude, xrav.assignment_level, xrav.level_value assignment_value, xrav.description, (select listagg(fu.user_name,', ') within group (order by fu.user_name) from xxen_report_assignment_users xrau, fnd_user fu where xrav.assignment_id=xrau.assignment_id and xrau.user_id=fu.user_id) users, xrav.block_name form_block, xrzpvv.parameter_name, xrzpvv.block_name, xrzpvv.item_name, initcap(xrzpvv.id_or_value_dsp) "Id or Value", xrzpvv.parameter_value_dsp constant_value, xxen_util.yes(xrav.disabled) disabled, xxen_util.user_name(xrav.created_by) created_by, xxen_util.client_time(xrav.creation_date) creation_date, xxen_util.user_name(xrav.last_updated_by) last_updated_by, xxen_util.client_time(xrav.last_update_date) last_update_date, null delete_, null upload_row from xxen_reports_v xrv, (select xrav.* from xxen_report_assignments_v xrav where :p_upload_mode<>'C') xrav, xxen_report_zoom_param_vals_v xrzpvv where 1=1 and nvl(:p_create_empty_file,'x')<>'Y' and xrv.report_id=xrav.report_id(+) and xrav.assignment_id=xrzpvv.assignment_id(+) Add missing users column to the Success SQL; ANSI join rewrite for DB 11.2 compatibility U A xxen_report_upload.create_update_assignments xxen_report_upload.validate_assignment( p_assignment_level=>:$column$.assignment_level, p_description=>:$column$.description, p_form_block=>:$column$.form_block, p_parameter_name=>:$column$.parameter_name, p_block_name=>:$column$.block_name, p_item_name=>:$column$.item_name, p_parameter_value=>:$column$.constant_value, p_users=>:$column$.users ) US Blitz Report Assignment Upload Upload to maintain Blitz Report assignments. The upload support creating, deleting or copying assignment and can be run with the following modes: -Create with 'Create Empty File'=Yes: Opens an empty Excel file to enter new assignments. -Create with 'Create Empty File'=blank: You can query a list of reports that you want create assignments for by report name, category or report type. -Create, Update: Allows querying existing assignments, which can be deleted, or copied to a different assignment level. A frequent use case for customers having custom responsibilities linked to custom applications would be to copy seeded application level Enginatics assignments to custom applications instead. For this, you can query all Enginatics application level assignments, replace the application names with the corresponding custom applications, and upload the file again. Note that in this scenario, the Action column would show 'Update', but it would create new records, not update when uploading the file. Enginatics R12 only Upload 1=1 :p_create_empty_file :p_upload_mode 1 10 :p_upload_mode 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 Y US Upload Mode 2 20 :p_create_empty_file LOV custom Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual where :$flex$.upload_mode='C' case when :$flex$.upload_mode='C' and :$flex$.report_name_starts_with is null then 'Y' end US Create Empty File 3 30 1=1 lower(xrv.report_name) like lower(:report_name_like)||'%' LOV Blitz Report Name (wildcard) B04763D730F050D7E0530100007F06B3 select xrv.report_name value, xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description from xxen_reports_v xrv order by xrv.report_name US Report Name starts with 4 40 1=1 xrv.report_id in ( select xrca.report_id from xxen_report_category_assigns xrca, xxen_report_categories_v xrcv where xrcv.category=:category and xrcv.category_id=xrca.category_id ) LOV Blitz Report Category 8E2FF36EDEFA79D2E0530100007F1FF2 Y select xrcv.category value, (select count(*) from xxen_report_category_assigns xrca where xrcv.category_id=xrca.category_id) description from xxen_report_categories_v xrcv order by xrcv.category US Report Category 5 50 1=1 xrv.type_dsp=:type LOV custom Y select xrv.type_dsp value, count(*) description from xxen_reports_v xrv where :$flex$.create_empty_file is null group by xrv.type_dsp order by count(*) desc US Report Type 6 60 1=1 xrav.assignment_level=:assignment_level LOV custom Y select xrav.assignment_level value, count(*) description from xxen_report_assignments_v xrav where :$flex$.upload_mode<>'C' and :$flex$.create_empty_file is null group by xrav.assignment_level order by count(*) desc US Assignment Level 7 70 1=1 xrav.level_value=:level_value LOV custom Y select xrav.level_value value, count(*) description from xxen_report_assignments_v xrav where :$flex$.upload_mode<>'C' and :$flex$.create_empty_file is null and (:$flex$.assignment_level is null or xxen_util.contains(:$flex$.assignment_level,xrav.assignment_level)='Y') group by xrav.level_value order by count(*) desc US Level Value 8 80 1=1 xrav.created_by=:created_by LOV custom Y Y select fu.user_id id, 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 :$flex$.upload_mode<>'C' and :$flex$.create_empty_file is null and 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 US Assignment Created By 9 90 1=1 xrav.creation_date>=:creation_date_from Date US Assignment Creation Date From 10 100 1=1 xrav.creation_date<:creation_date_to+1 Date US Assignment Creation Date To 11 110 1=1 xrav.assignment_id is not null and nvl(xrav.disabled,'N')=:disabled LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description US Disabled 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 5 REPORT_NAME LOV varchar2 240 Blitz Report Name 8E2FF36EDEA879D2E0530100007F1FF2 Y select xrv.report_name value, xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null)||substrb(xrv.description,1,4000-lengthb(xrv.type_dsp||nvl2(xrv.type,' ',null)||nvl2(xrv.category,'('||xrv.category||') ',null))) description from xxen_reports_v xrv where nvl(xrv.type,'x')<>'S' and (fnd_profile.value('XXEN_REPORT_ACCESS')='S' or nvl(xrv.type,'x')<>'U') order by xrv.report_name select xrv.report_id id, xrv.report_name value from xxen_reports_v xrv Y 6 TYPE Char varchar2 9 select xrv.type_dsp from xxen_reports_v xrv where xrv.report_name=:$column$.report_name 7 CATEGORY Char varchar2 select xrv.category from xxen_reports_v xrv where xrv.report_name=:$column$.report_name 8 INCLUDE_EXCLUDE LOV custom varchar2 Y select flvv.lookup_code id, flvv.meaning value, null description from fnd_lookup_values_vl flvv where flvv.lookup_type='INCLUDE_EXCLUDE' and flvv.view_application_id=0 and flvv.security_group_id=0 and flvv.enabled_flag='Y' order by flvv.lookup_code desc xxen_util.meaning('I','INCLUDE_EXCLUDE',0) Y 9 ASSIGNMENT_LEVEL LOV custom varchar2 14 Y select 'Site' id, 'Site' value, null description from dual union all select 'Application' id, 'Application' value, null description from dual union all select 'Request Group' id, 'Request Group' value, null description from dual union all select 'Responsibility' id, 'Responsibility' value, null description from dual union all select 'User' id, 'User' value, null description from dual union all select 'Form' id, 'Form' value, null description from dual union all select 'Function' id, 'Function' value, null description from dual Y 10 ASSIGNMENT_VALUE LOV custom varchar2 480 Y select xralv.level_value value, xralv.description from xxen_report_assignments_lov_v xralv where xralv.assignment_level=nvl(:$column$.assignment_level,xralv.assignment_level) order by xralv.report_count desc nulls last, decode(xralv.assignment_level,'Site',1,'Application',2,'Request Group',3,'Responsibility',4,'User',5,'Form',6,'Function',7,'Report',8), xralv.level_value select xralv.id1 id, xralv.level_value value from xxen_report_assignments_lov_v xralv where xralv.assignment_level=:$column$.assignment_level and (:$column$.assignment_level in ('Site','Application','User','Form','Function') or xralv.description=:$column$.description) Y 11 DESCRIPTION LOV custom varchar2 960 Y select xralv.id2 id, xralv.description value, null description from xxen_report_assignments_lov_v xralv where xralv.assignment_level=:$column$.assignment_level and xralv.level_value=:$column$.assignment_value order by xralv.report_count desc nulls last, decode(xralv.assignment_level,'Site',1,'Application',2,'Request Group',3,'Responsibility',4,'User',5,'Form',6,'Function',7,'Report',8), xralv.level_value, xralv.description select xralv.description from xxen_report_assignments_lov_v xralv where xralv.assignment_level=:$column$.assignment_level and xralv.level_value=:$column$.assignment_value and xralv.assignment_level<>'Site' 12 USERS Char varchar2 13 FORM_BLOCK Char varchar2 250 14 PARAMETER_NAME LOV custom varchar2 80 Y select xrpv.parameter_id id, xrpv.parameter_name value, null description from xxen_report_parameters_v xrpv where xrpv.report_name=:$column$.report_name and :$column$.assignment_level='Form' and xrpv.display_sequence is not null order by xrpv.display_sequence 15 BLOCK_NAME Char varchar2 30 16 ITEM_NAME Char varchar2 30 17 Id or Value LOV custom varchar2 5 Y select x.* from ( select 'id' id, 'Id' value, null description from dual union all select 'value' id, 'Value' value, null description from dual ) x where exists ( select null from xxen_report_parameters_v xrpv where :$column$.block_name is not null and xrpv.report_name=:$column$.report_name and xrpv.parameter_name=:$column$.parameter_name and xrpv.display_sequence is not null and xxen_api.lov_value_equal_to_id(xrpv.parameter_id) is null ) 18 CONSTANT_VALUE Char varchar2 19 DISABLED LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 20 CREATED_BY Char varchar2 21 CREATION_DATE Date date 22 LAST_UPDATED_BY Char varchar2 23 LAST_UPDATE_DATE Date date 24 DELETE_ LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 25 UPLOAD_ROW Char varchar2 Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_report_id in Number REPORT_NAME U 4 p_include_exclude in Char INCLUDE_EXCLUDE U 5 p_disabled in Char DISABLED U 6 p_assignment_level in Char ASSIGNMENT_LEVEL U 7 p_id1 in Number ASSIGNMENT_VALUE U 8 p_id2 in Number DESCRIPTION U 9 p_form_block in Char FORM_BLOCK U 10 p_parameter_id in Number PARAMETER_NAME U 11 p_block_name in Char BLOCK_NAME U 12 p_item_name in Char ITEM_NAME U 13 p_id_or_value in Char Id or Value U 14 p_parameter_value in Char CONSTANT_VALUE U 15 p_users in Char USERS U 16 p_delete in Char DELETE_ Error SQL Order By upload_row, report_name, description, assignment_value, block_name, parameter_name Success SQL select null action_, xu.status_, xu.message_, xu.modified_columns_, xu.report_name, xu.type, xu.category, nvl(xxen_util.meaning(xrav.include_exclude,'INCLUDE_EXCLUDE',0),xu.include_exclude) include_exclude, nvl(xrav.assignment_level,xu.assignment_level) assignment_level, nvl(xrav.level_value,xu.assignment_value) assignment_value, nvl(xrav.description,xu.description) description, nvl((select listagg(fu.user_name,', ') within group (order by fu.user_name) from xxen_report_assignment_users xrau, fnd_user fu where xrav.assignment_id=xrau.assignment_id and xrau.user_id=fu.user_id),xu.users) users, nvl(xrav.block_name,xu.form_block) form_block, nvl(xrzpvv.parameter_name,xu.parameter_name) parameter_name, nvl(xrzpvv.block_name,xu.block_name) block_name, nvl(xrzpvv.item_name,xu.item_name) item_name, nvl(xrzpvv.id_or_value,xu."Id or Value") "Id or Value", nvl(xrzpvv.parameter_value_dsp,xu.constant_value) constant_value, xxen_util.yes(xrav.disabled) disabled, xxen_util.user_name(xrav.created_by) created_by, xxen_util.client_time(xrav.creation_date) creation_date, xxen_util.user_name(xrav.last_updated_by) last_updated_by, xxen_util.client_time(xrav.last_update_date) last_update_date, xu.delete_, xu.upload_row from xxen_blitz_report_assig_6217_u xu left join xxen_report_assignments_v xrav on xu.report_name=xrav.report_name and xu.assignment_level=xrav.assignment_level and xu.assignment_value=xrav.level_value and nvl(xu.description,'x')=nvl(xrav.description,'x') and nvl(xu.form_block,'x')=nvl(xrav.block_name,'x') left join xxen_report_zoom_param_vals_v xrzpvv on xrav.assignment_id=xrzpvv.assignment_id and xu.parameter_name=xrzpvv.parameter_name where xu.status_code_='S'