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 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 E7A3F1C920B84D5E8F6A1C3B4D2E5F70Y select null action_, null status_, null message_, null modified_columns_, xrtv.report_name, xrtv.template_name, xrtv.owner, xrtsv.sharing_level_desc sharing_level, xrtsv.level_value shared_with, fav.application_name description, xxen_util.user_name(xrtsv.created_by) created_by, xxen_util.client_time(xrtsv.creation_date) creation_date, null delete_, null upload_row from xxen_report_templates_v xrtv, (select xrtsv.* from xxen_report_template_sharing_v xrtsv where 2=2) xrtsv, fnd_application_vl fav where 1=1 and nvl(:p_create_empty_file,'x')<>'Y' and xrtv.template_id=xrtsv.template_id(+) and xrtsv.id2=fav.application_id(+) Initial version - upload to maintain Blitz Report template sharing (Site / Responsibility / User). U A xxen_report_upload.create_update_template_sharing US Blitz Report Template Sharing Upload Upload to maintain Blitz Report template sharing - which users, responsibilities, or the whole site can see and use a report template (Excel layout). The upload can be run in the following modes: -Create with 'Create Empty File'=Yes: Opens an empty Excel file to enter new template shares. -Create with 'Create Empty File'=blank: Query the templates you want to share, filtered by report name, category or sharing level. -Create, Update: Query existing template shares, which can be deleted or copied to a different sharing level. Sharing levels: -Site shares the template with the whole system (all responsibilities and users). -Responsibility shares it with one responsibility - enter the responsibility name in Shared With and its owning application in Description. -User shares it with one named user. Note: Site sharing is mutually exclusive with specific shares. Sharing a template at Site level removes any existing Responsibility or User shares for that template, and adding a Responsibility or User share removes any existing Site share - mirroring the Blitz Report template setup form. Enginatics Upload 1=1 2=2 :p_create_empty_file 1 10 2=2 :p_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 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(xrtv.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 xrtv.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 xrtsv.sharing_level_desc=:sharing_level LOV custom Y select 'Site' value, null description from dual union all select 'Responsibility' value, null description from dual union all select 'User' value, null description from dual US Sharing Level 6 60 1=1 xrtsv.creation_date>=:creation_date_from Date US Sharing Creation Date From 7 70 1=1 xrtsv.creation_date<:creation_date_to+1 Date US Sharing Creation Date To :$flex$.report_name_starts_with Report Name starts with Create Empty File :$flex$.upload_mode Upload Mode Create Empty File 1 ACTION_ Char varchar2 2 STATUS_ Char varchar2 3 MESSAGE_ Char varchar2 4 MODIFIED_COLUMNS_ Char varchar2 Y 5 REPORT_NAME LOV custom varchar2 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 exists (select null from xxen_report_templates xrt where xrt.report_id=xrv.report_id) order by xrv.report_name Y 6 TEMPLATE_NAME LOV custom varchar2 Y select xrtv.template_name value, xrtv.description from xxen_report_templates_v xrtv where xrtv.report_name=:$column$.report_name order by xrtv.template_name select xrtv.template_id id, xrtv.template_name value from xxen_report_templates_v xrtv where xrtv.report_name=:$column$.report_name Y 7 OWNER Char varchar2 select xrtv.owner from xxen_report_templates_v xrtv where xrtv.report_name=:$column$.report_name and xrtv.template_name=:$column$.template_name Y 8 SHARING_LEVEL LOV custom varchar2 Y select 'Site' value, null description from dual union all select 'Responsibility' value, null description from dual union all select 'User' value, null description from dual Y 9 SHARED_WITH LOV custom varchar2 Y select xralv.level_value value, xralv.description from xxen_report_assignments_lov_v xralv where xralv.assignment_level=:$column$.sharing_level order by xralv.report_count desc nulls last, xralv.level_value select xralv.id1 id, xralv.level_value value from xxen_report_assignments_lov_v xralv where xralv.assignment_level=:$column$.sharing_level and (:$column$.sharing_level in ('Site','User') or xralv.description=:$column$.description) Y 10 DESCRIPTION LOV custom varchar2 Y select xralv.description value, null description from xxen_report_assignments_lov_v xralv where :$column$.sharing_level='Responsibility' and xralv.assignment_level=:$column$.sharing_level and xralv.level_value=:$column$.shared_with order by xralv.report_count desc nulls last, xralv.description select xralv.id2 id, xralv.description value from xxen_report_assignments_lov_v xralv where xralv.assignment_level=:$column$.sharing_level and xralv.level_value=:$column$.shared_with select xralv.description from xxen_report_assignments_lov_v xralv where :$column$.sharing_level='Responsibility' and xralv.assignment_level='Responsibility' and xralv.level_value=:$column$.shared_with 11 CREATED_BY Char varchar2 12 CREATION_DATE Date date 13 DELETE_ LOV varchar2 Yes 8E2FF36EDEA679D2E0530100007F1FF2 Y select 'Y' id, xxen_util.meaning('Y','YES_NO',0) value, null description from dual 14 UPLOAD_ROW Char varchar2 Y U 1 status_ out Char STATUS_CODE_ U 2 message_ out Char MESSAGE_ U 3 p_template_id in Number TEMPLATE_NAME U 4 p_sharing_level in Char SHARING_LEVEL U 5 p_id1 in Number SHARED_WITH U 6 p_id2 in Number DESCRIPTION U 7 p_delete in Char DELETE_ Error SQL Order By upload_row, report_name, template_name, sharing_level, shared_with Success SQL select xu.action_, xu.status_, xu.message_, xu.modified_columns_, nvl(xrtv.report_name,xu.report_name) report_name, nvl(xrtsv.template_name,xu.template_name) template_name, nvl(xrtv.owner,xu.owner) owner, nvl(xrtsv.sharing_level_desc,xu.sharing_level) sharing_level, nvl(xrtsv.level_value,xu.shared_with) shared_with, nvl(fav.application_name,xu.description) description, nvl(xxen_util.user_name(xrtsv.created_by),xu.created_by) created_by, nvl(xxen_util.client_time(xrtsv.creation_date),xu.creation_date) creation_date, xu.delete_, xu.upload_row from xxen_blitz_report_templ_0944_u xu, xxen_report_templates_v xrtv, xxen_report_template_sharing_v xrtsv, fnd_application_vl fav where xu.status_code_=xxen_upload.status_success and xu.report_name=xrtv.report_name(+) and xu.template_name=xrtv.template_name(+) and xrtv.template_id=xrtsv.template_id(+) and xu.sharing_level=xrtsv.sharing_level_desc(+) and xu.shared_with=xrtsv.level_value(+) and xrtsv.id2=fav.application_id(+) LOV query :$column$.shared_with SHARED_WITH DESCRIPTION Default value :$column$.shared_with SHARED_WITH DESCRIPTION Default value :$column$.sharing_level SHARING_LEVEL DESCRIPTION LOV query :$column$.sharing_level SHARING_LEVEL DESCRIPTION Default value :$column$.report_name REPORT_NAME OWNER Default value :$column$.template_name TEMPLATE_NAME OWNER LOV query :$column$.sharing_level SHARING_LEVEL SHARED_WITH LOV query :$column$.report_name REPORT_NAME TEMPLATE_NAME Value to id query :$column$.description DESCRIPTION p_id1 Value to id query :$column$.report_name REPORT_NAME p_template_id Value to id query :$column$.shared_with SHARED_WITH p_id2 Value to id query :$column$.sharing_level SHARING_LEVEL p_id1 Value to id query :$column$.sharing_level SHARING_LEVEL p_id2