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 3D4261D9251B5590E0630100007F92CB 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_desc assignment_level, xrav.level_value assignment_value, xrav.description, 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.meaning(nvl(xrav.enabled,'N'),'YES_NO',0) enabled, 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(+) Y 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 ) 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 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_desc=:assignment_level LOV custom Y select xrav.assignment_level_desc 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_desc 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_desc)='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.enabled,'N')=:enabled 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 Enabled :$flex$.assignment_level Assignment Level Level Value :$flex$.create_empty_file Create Empty File Assignment Created By :$flex$.create_empty_file Create Empty File Assignment Level :$flex$.create_empty_file Create Empty File Level Value :$flex$.create_empty_file Create Empty File Report Category :$flex$.create_empty_file Create Empty File Report Type :$flex$.report_name_starts_with Report Name starts with Create Empty File :$flex$.upload_mode Upload Mode Assignment Created By :$flex$.upload_mode Upload Mode Assignment Level :$flex$.upload_mode Upload Mode Create Empty File :$flex$.upload_mode Upload Mode Level Value 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 5 REPORT_NAME LOV varchar2 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 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 Y select 'S' id, 'Site' value, null description from dual union all select 'A' id, 'Application' value, null description from dual union all select 'G' id, 'Request Group' value, null description from dual union all select 'R' id, 'Responsibility' value, null description from dual union all select 'U' id, 'User' value, null description from dual union all select 'F' id, 'Form' value, null description from dual Y 10 ASSIGNMENT_VALUE LOV custom varchar2 Y select xralv.level_value value, xralv.description from xxen_report_assignments_lov_v xralv where xralv.assignment_level_desc=nvl(:$column$.assignment_level,xralv.assignment_level_desc) order by xralv.report_count desc nulls last, decode(xralv.assignment_level,'S',1,'A',2,'G',3,'R',4,'U',5,'F',6), xralv.level_value select xralv.id1 id, xralv.level_value value from xxen_report_assignments_lov_v xralv where xralv.assignment_level_desc=:$column$.assignment_level and (:$column$.assignment_level in ('Site','Application','User','Form') or xralv.description=:$column$.description) Y 11 DESCRIPTION LOV custom varchar2 Y select xralv.id1 id, xralv.description value, null description from xxen_report_assignments_lov_v xralv where xralv.assignment_level_desc=:$column$.assignment_level and xralv.level_value=:$column$.assignment_value order by xralv.report_count desc nulls last, decode(xralv.assignment_level,'S',1,'A',2,'G',3,'R',4,'U',5,'F',6), xralv.level_value, xralv.description select xralv.description from xxen_report_assignments_lov_v xralv where xralv.assignment_level_desc=:$column$.assignment_level and xralv.level_value=:$column$.assignment_value and xralv.assignment_level<>'S' 12 FORM_BLOCK Char varchar2 13 PARAMETER_NAME LOV custom varchar2 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 14 BLOCK_NAME Char varchar2 15 ITEM_NAME Char varchar2 16 Id or Value LOV custom varchar2 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 ) 17 CONSTANT_VALUE Char varchar2 18 ENABLED LOV Oracle varchar2 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 xxen_util.meaning('Y','YES_NO',0) Y 19 CREATED_BY Char varchar2 20 CREATION_DATE Date date 21 LAST_UPDATED_BY Char varchar2 22 LAST_UPDATE_DATE Date date 23 DELETE_ LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 24 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_enabled in Char ENABLED 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_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_desc,xu.assignment_level) assignment_level, nvl(xrav.level_value,xu.assignment_value) assignment_value, nvl(xrav.description,xu.description) description, 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.meaning(nvl(xrav.enabled,'N'),'YES_NO',0) enabled, 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, xxen_report_assignments_v xrav, xxen_report_zoom_param_vals_v xrzpvv where xu.status_code_='S' and xu.report_name=xrav.report_name(+) and xu.assignment_level=xrav.assignment_level_desc(+) 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') and xrav.assignment_id=xrzpvv.assignment_id(+) and xu.parameter_name=xrzpvv.parameter_name(+) LOV query :$column$.assignment_level ASSIGNMENT_LEVEL ASSIGNMENT_VALUE Default value :$column$.report_name REPORT_NAME CATEGORY Default value :$column$.assignment_level ASSIGNMENT_LEVEL DESCRIPTION LOV query :$column$.assignment_level ASSIGNMENT_LEVEL DESCRIPTION Default value :$column$.assignment_value ASSIGNMENT_VALUE DESCRIPTION LOV query :$column$.assignment_value ASSIGNMENT_VALUE DESCRIPTION LOV query :$column$.block_name BLOCK_NAME Id or Value LOV query :$column$.parameter_name PARAMETER_NAME Id or Value LOV query :$column$.report_name REPORT_NAME Id or Value LOV query :$column$.assignment_level ASSIGNMENT_LEVEL PARAMETER_NAME LOV query :$column$.report_name REPORT_NAME PARAMETER_NAME Default value :$column$.report_name REPORT_NAME TYPE Value to id query :$column$.assignment_level ASSIGNMENT_LEVEL p_id1 Value to id query :$column$.assignment_level ASSIGNMENT_LEVEL p_id2 Value to id query :$column$.assignment_level ASSIGNMENT_LEVEL p_parameter_id Excel validation :$column$.assignment_level ASSIGNMENT_LEVEL Value to id query :$column$.assignment_value ASSIGNMENT_VALUE p_id2 Value to id query :$column$.block_name BLOCK_NAME p_id_or_value Excel validation :$column$.block_name BLOCK_NAME Excel validation :$column$.constant_value CONSTANT_VALUE Value to id query :$column$.description DESCRIPTION p_id1 Excel validation :$column$.description DESCRIPTION Excel validation :$column$.form_block FORM_BLOCK Excel validation :$column$.item_name ITEM_NAME Value to id query :$column$.parameter_name PARAMETER_NAME p_id_or_value Excel validation :$column$.parameter_name PARAMETER_NAME Value to id query :$column$.report_name REPORT_NAME p_id_or_value Value to id query :$column$.report_name REPORT_NAME p_parameter_id