ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 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 create, update, copy and delete Blitz Report assignments, which make reports available at site, application, request group, responsibility, user, form or function level. Upload modes: - Create with Create Empty File = Yes opens an empty sheet to enter new assignments. - Create with Create Empty File blank lists the reports selected by Report Name starts with, Report Category or Report Type, one row per report, to enter new assignments for them. - Create, Update downloads the existing assignments. An assignment is identified by report, assignment level and assignment value, and by Description at Request Group, Responsibility and Form level, where it names the application. A row matching an existing assignment updates its Include/Exclude, Disabled, Form Block, Users and parameter defaulting; any other row creates a new assignment. Changing the level or value of a downloaded row therefore copies the assignment, for example to copy the seeded Enginatics application level assignments to custom applications. Delete = Yes deletes the assignment, or only the parameter defaulting of the row's Parameter Name. Users limits an Include assignment at Application, Request Group, Responsibility, Form or Function level to the listed users, entered as comma separated user names. At Form level, Form Block limits the assignment to one block of the form, and Parameter Name with either Block Name and Item Name or a Constant Value defaults a report parameter from a form field or a constant when the report is opened from the form. Id or Value tells whether the form field holds the parameter's id or its displayed value. Assignments of uploads can only be maintained with Blitz Report Access System. Enginatics R12 only Upload 1=1 :p_create_empty_file :p_upload_mode 1 10 :p_upload_mode LOV Blitz Upload Mode E811F942597A2075E0530100007FF4F2 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 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 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 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 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 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 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 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 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 select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description US Disabled 0A1615E03B4E4F5B8DD5353FDDF300D7 Default All columns. ENGINATICS 5 ASSIGNMENT_LEVEL 6 ASSIGNMENT_VALUE 11 BLOCK_NAME 3 CATEGORY 14 CONSTANT_VALUE 16 CREATED_BY 17 CREATION_DATE 20 DELETE_ 7 DESCRIPTION 15 DISABLED 9 FORM_BLOCK 4 INCLUDE_EXCLUDE Y 12 ITEM_NAME 13 Id or Value 18 LAST_UPDATED_BY 19 LAST_UPDATE_DATE 10 PARAMETER_NAME 1 REPORT_NAME 2 TYPE 8 USERS S Site 0A1615E03B4E4F5B8DD5353FDDF300D7 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 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 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 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 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 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 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 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 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 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'