#SQL Statements for OracleSQL
#ReportLoader.java
load.custom.report.xml = SELECT cr.report_xml FROM cr_report cr WHERE rep_id=?
# need to copy to oracle
load.remoteDB.schema = SELECT 'local' SCHEMA_ID, 'local' SCHEMA_DESC, null DATASOURCE_TYPE FROM DUAL union SELECT a.SCHEMA_ID, a.SCHEMA_DESC, DATASOURCE_TYPE FROM SCHEMA_INFO a where schema_id <> 'local' order by schema_id
load.remoteDB.schema.where = SELECT a.SCHEMA_ID, a.SCHEMA_DESC, DATASOURCE_TYPE FROM SCHEMA_INFO a where schema_id = '[schema_id]'
#formfield rendering
formfield.id.name.sql.prefix=SELECT id, name FROM (
formfield.id.name.sql=SELECT id, name
formfield.id.name.sql.suffix=
report.security.create= SELECT nvl(cr.owner_id, cr.create_id) AS owner_id, cr.create_id, TO_CHAR(cr.create_date, 'MM/dd/YYYY') create_date, maint_id, TO_CHAR(cr.maint_date, 'MM/dd/YYYY') update_date, cr.public_yn FROM cr_report cr WHERE cr.rep_id = [rw.getReportID()]
db.update.report.xml = SELECT cr.rep_id, cr.report_xml FROM cr_report cr WHERE rep_id=? FOR UPDATE
update.custom.report.rec = UPDATE cr_report SET title='[Utils.oracleSafe(rw.getReportName())]', descr='[Utils.oracleSafe(rw.getReportDescr())]', public_yn='[(rw.isPublic()]', menu_id='[rw.getMenuID()]', menu_approved_yn='[(rw.isMenuApproved()]', owner_id=[rw.getOwnerID()], maint_id=[rw.getUpdateID()], maint_date=TO_DATE('[rw.getUpdateDate()]', '[Globals.getTimeFormat()]'), dashboard_type_yn='[(rw.isDashboardType()]', dashboard_yn= '[(rw.getReportType().equals(AppConstants.RT_DASHBOARD)]' WHERE rep_id = [rw.getReportID()]
is.report.already.scheduled = select rep_id from cr_report_schedule where rep_id = ?
create.custom.report.rec = INSERT INTO cr_report(rep_id, title, descr, public_yn, menu_id, menu_approved_yn, report_xml, owner_id, create_id, create_date, maint_id, maint_date, dashboard_type_yn, dashboard_yn, folder_id) VALUES([rw.getReportID()], '[Utils.oracleSafe(rw.getReportName())]', '[Utils.oracleSafe(rw.getReportDescr())]', '[rw.isPublic()]', '[rw.getMenuID()]', '[rw.isMenuApproved()]', '', [rw.getOwnerID()], [rw.getCreateID()], TO_DATE('[rw.getCreateDate()]', '[Globals.getTimeFormat()]'), [rw.getUpdateID()], TO_DATE('[rw.getUpdateDate()]', '[Globals.getTimeFormat()]'), '[rw.isDashboardType()]', '[rw.getReportType().equals(AppConstants.RT_DASHBOARD)]',[rw.getFolderId()])
get.user.report.names = SELECT cr.rep_id, cr.title FROM cr_report cr WHERE nvl(cr.owner_id, cr.create_id) = [userID]
get.report.owner.id = SELECT nvl(cr.owner_id, cr.create_id) owner FROM cr_report cr WHERE rep_id = ?
delete.report.record.log = DELETE FROM cr_report_log WHERE rep_id = [reportID]
delete.report.record.users = DELETE FROM cr_report_schedule_users WHERE rep_id = [reportID]
delete.report.record.schedule = DELETE FROM cr_report_schedule WHERE rep_id = [reportID]
delete.report.record.access = DELETE FROM cr_report_access WHERE rep_id = [reportID]
delete.report.record.email = DELETE FROM cr_report_email_sent_log WHERE rep_id = [reportID]
delete.report.record.favorite = DELETE FROM cr_favorite_reports WHERE rep_id = [reportID]
delete.report.record.report = DELETE FROM cr_report WHERE rep_id = [reportID]
load.quick.links = select finalcr.rep_id, finalcr.title, finalcr.descr from (SELECT cr.rep_id, cr.title, cr.descr FROM (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra LEFT JOIN cr_report cr ON cr.rep_id = ra.rep_id WHERE cr.menu_id LIKE '%[nvls(menuId)]%' AND cr.menu_approved_yn = 'Y' AND (nvl(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) UNION SELECT cr.rep_id, cr.title, cr.descr FROM cr_report cr WHERE cr.menu_id LIKE '%[nvls(menuId)]%' AND (nvl(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' or EXISTS (select * from fn_user_role where user_id=[userID] and role_id in (1)))) finalcr ORDER BY finalcr.title
load.folder.reports = SELECT cr.rep_id, cr.rep_id report_id, ([rep_title_sql] || (CASE WHEN cr.public_yn = 'Y' THEN '' ELSE '[PRIVATE_ICON]' END)||cr.title||'') title, cr.descr, (au.first_name||' '||au.last_name) owner_name, TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, (CASE WHEN nvl(cr.owner_id, cr.create_id) = [userID] THEN 'N' ELSE nvl(ra.read_only_yn, 'Y') END) read_only_yn, DECODE(NVL(cr.owner_id, cr.create_id), [userID], 'Y', 'N') user_is_owner_yn FROM cr_report cr JOIN app_user au ON nvl(cr.owner_id, cr.create_id) = au.user_id AND cr.folder_id= '[folderId]' LEFT JOIN (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL(SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON cr.rep_id = ra.rep_id
load.folder.reports.user = AND nvl(cr.owner_id, cr.create_id) = [userID]
load.folder.reports.publicsql = AND (nvl(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)
load.quick.download.links = SELECT a.file_name, b.title,to_char(a.dwnld_start_time, 'Dy DD-Mon-YYYY HH24:MI:SS') as time, a.dwnld_start_time FROM cr_report_dwnld_log a, cr_report b where a.user_id = [userID] and a.rep_id = b.rep_id and (a.dwnld_start_time) >= to_date(to_char(sysdate-24/24, 'mm/dd/yyyy'), 'mm/dd/yyyy') and a.record_ready_time is not null order by a.dwnld_start_time desc
load.reports.to.schedule = SELECT cr.rep_id, Initcap(cr.title), cr.descr FROM cr_report cr, (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra WHERE cr.rep_id = ra.rep_id (+) AND (cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL or cr.owner_id = [userID] ) ORDER BY Initcap(cr.title)
load.reports.to.add.in.dashboard = SELECT cr.rep_id, cr.title, cr.descr FROM cr_report cr, (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra WHERE cr.rep_id = ra.rep_id (+) AND (nvl(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL) AND (cr.dashboard_yn = 'N' or cr.dashboard_yn is null) ORDER BY cr.title
load.my.recent.links = select rep_id, title, descr, form_fields from ( select rownum, rep_id, title, descr, form_fields from (select cr.rep_id, cr.title, a.form_fields, cr.descr, a.log_time, a.user_id, a.action, a.action_value from cr_report_log a, cr_report cr where user_id = [userID] and action = 'Report Execution Time' and a.rep_id = cr.rep_id order by log_time desc) x where rownum <= 6 ) y where rownum >= 1
create.report.log.entry = INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, form_fields) VALUES([reportID], SYSDATE, [userID], '[action]' , '[executionTime]', '[form_fields]')
create.report.log.entry.exec.time = INSERT INTO cr_report_log (rep_id, log_time, user_id, action, action_value, form_fields) VALUES([reportID], sysdate+1/(24*60*60), [userID], '[action]' , '[executionTime]', '[formFields]')
clear.report.log.entries = DELETE from cr_report_log WHERE rep_id = ? and user_id = ?
load.report.log.entries = SELECT x.log_time, x.user_id, (CASE WHEN x.action = 'Report Execution Time' THEN ''||x.action||'' ELSE x.action END) action, (CASE WHEN x.action = 'Report Execution Time' THEN action_value ELSE 'N/A' END) time_taken, (CASE WHEN x.action = 'Report Execution Time' THEN '![\"Run](\"[AppUtils.getImgFolderURL()]test_run.gif\")
' ELSE 'N/A' END) run_image, x.name FROM (SELECT rl.rep_id, TO_CHAR(rl.log_time, 'Month DD, YYYY HH:MI:SS AM') log_time, rl.action_value, fuser.last_name ||', '||fuser.first_name name, rl.user_id, rl.action, rl.form_fields FROM cr_report_log rl, fn_user fuser WHERE rl.rep_id = [nvls(reportId)] and rl.action != 'Report Run' and fuser.user_id = rl.user_id ORDER BY rl.log_time DESC) x WHERE ROWNUM <= 100
does.user.can.schedule.report = select crs.sched_user_id, count(*) from cr_report_schedule crs where sched_user_id = [userId] group by crs.sched_user_id having count(*) >= [Globals.getScheduleLimit()]
does.user.can.schedule = select crs.schedule_id from cr_report_schedule crs where schedule_id = [scheduleId]
get.system.date.time = select to_char(sysdate,'MM/dd/yyyy HH24:mi:ss') from dual
get.next.day.date.time = select to_char(sysdate+1,'MM/dd/yyyy HH24:mi:ss') from dual
get.next.fifteen.minutes.date.time = select to_char(sysdate+15/(24*60),'MM/dd/yyyy HH24:mi:ss') from dual
get.next.thirty.minutes.date.time = select to_char(sysdate+30/(24*60),'MM/dd/yyyy HH24:mi:ss') from dual
get.template.file = select template_file from cr_report_template_map where report_id = [reportId]
load.pdf.img.lookup = select image_id, image_loc from cr_raptor_pdf_img
load.action.img.lookup = select image_id, image_loc from cr_raptor_action_img
#ActionHandler.java
report.values.map.def.a = SELECT x FROM (SELECT DISTINCT
report.values.map.def.b = TO_CHAR([colName], '[nvl(displayFormat, AppConstants.DEFAULT_DATE_FORMAT)]')
report.values.map.def.c = [colName]
report.values.map.def.d = x FROM [rdef.getTableById(tableId).getTableName()] WHERE [colName] IS NOT NULL ORDER BY 1) xx WHERE <= [Globals.getDefaultPageSize()]
test.sched.cond.popup = SELECT 1 FROM DUAL WHERE EXISTS ([sql])
download.all.email.sent = Select user_id, rep_id from CR_REPORT_EMAIL_SENT_LOG where rownum = 1 and gen_key='[pdfAttachmentKey.trim()]' and log_id =[report_email_sent_log_id.trim()] and (sysdate - sent_date) < 1
download.all.gen.key = select schedule_id from cr_report_email_sent_log u where U.GEN_KEY = '[pdfAttachmentKey]'
download.all.retrieve = SELECT au.user_id FROM (SELECT rs.schedule_id, rs.rep_id FROM cr_report_schedule rs WHERE rs.enabled_yn='Y' AND rs.run_date IS NOT NULL AND rs.schedule_id = [scheduleId]) x, cr_report r, app_user au WHERE x.rep_id = r.rep_id AND au.user_id IN (SELECT rsu.user_id FROM cr_report_schedule_users rsu WHERE rsu.schedule_id = x.schedule_id and rsu.schedule_id = [scheduleId] UNION SELECT ur.user_id FROM fn_user_role ur WHERE ur.role_id IN (SELECT rsu2.role_id FROM cr_report_schedule_users rsu2 WHERE rsu2.schedule_id = x.schedule_id and rsu2.schedule_id = [scheduleId]))
download.all.insert = insert into cr_report_dwnld_log (user_id,rep_id,file_name,dwnld_start_time,filter_params) values (?,?,?,?,?)
#ReportWrapper.java
report.wrapper.format = SELECT NVL(cr.owner_id, cr.create_id) owner_id, cr.create_id, TO_CHAR(cr.create_date, '[Globals.getTimeFormat()]') create_date, maint_id, TO_CHAR(cr.maint_date, '[Globals.getTimeFormat()]') update_date, cr.menu_id, cr.menu_approved_yn FROM cr_report cr WHERE cr.rep_id=[reportID]
generate.subset.sql = SELECT [colNames.toString()] FROM (SELECT ROWNUM rnum, [colNames.toString()] FROM ([reportSQL]) x ) y
report.sql.only.first.part = SELECT rnum,[colNames.toString()] FROM (SELECT ROWNUM rnum, [colNames.toString()] FROM (
report.sql.only.second.part.a = WHERE ROWNUM <= [endRow]
report.sql.only.second.part.b = ) y WHERE rnum >= [startRow] and rownum <= ([startRow]+[pageSize]) ORDER BY rnum
report.sql.only.second.part.b.noorderby = AND rnum >= [startRow]
generate.sql.visual.select = SELECT
generate.sql.visual.count = COUNT(*) cnt
generate.sql.visual.dual = FROM DUAL
#ReportRuntime.java
load.crosstab.report.data = SELECT ROWNUM rnum, [colNames.toString()] FROM ([reportSQL])
#RaptorRunHandler.java
generate.sql.handler = select ROWNUM rnum, x.* from ([sql]) x where rownum <= 2
generate.sql.select = SELECT [colNames.toString()] FROM (SELECT ROWNUM rnum, [colNames.toString()] FROM ([sql]) x
#ReportSchedule.java
load.schedule.data = SELECT rs.enabled_yn, TO_CHAR(rs.start_date, 'MM/DD/YYYY') start_date, TO_CHAR(rs.end_date, 'MM/DD/YYYY') end_date, TO_CHAR(rs.run_date, 'MM/DD/YYYY') run_date, NVL(TO_CHAR(rs.run_date, 'HH'), '12') run_hour, NVL(TO_CHAR(rs.run_date, 'MI'), '00') run_min, NVL(TO_CHAR(rs.run_date, 'AM'), 'AM') run_ampm, rs.recurrence, rs.conditional_yn, rs.notify_type, rs.max_row, rs.initial_formfields, rs.schedule_id, NVL(TO_CHAR(rs.end_date, 'HH'), '11') end_hour, NVL(TO_CHAR(rs.end_date, 'MI'), '45') end_min, NVL(TO_CHAR(rs.end_date, 'AM'), 'PM') end_ampm, encrypt_yn, attachment_yn FROM cr_report_schedule rs WHERE rs.rep_id = [reportID]
load.schedule.getid = SELECT rsu.user_id, fuser.last_name||', '||fuser.first_name, fuser.login_id FROM cr_report_schedule_users rsu, fn_user fuser WHERE rsu.rep_id = [reportID] AND rsu.schedule_id = [getScheduleID()] and rsu.user_id IS NOT NULL and rsu.user_id = fuser.user_id
load.schedule.users = SELECT rsu.role_id FROM cr_report_schedule_users rsu WHERE rsu.rep_id = [reportID] AND rsu.schedule_id = [getScheduleID()] AND rsu.role_id IS NOT NULL
new.schedule.data = select SEQ_CR_REPORT_SCHEDULE.nextval from dual
new.report.data = select SEQ_CR_REPORT.nextval from dual
execute.update = DELETE cr_report_schedule_users WHERE rep_id = [reportID] and schedule_id = [getScheduleID()]
execute.update.users = INSERT INTO cr_report_schedule_users (schedule_id, rep_id, user_id, role_id, order_no) VALUES([getScheduleID()], [reportID], [emailToUsers.get(i)).getId()], NULL, [(i + 1)])
execute.update.roles = INSERT INTO cr_report_schedule_users (schedule_id, rep_id, user_id, role_id, order_no) VALUES([getScheduleID()], [reportID], NULL, [emailToRoles.get(i)).getId()], [((emailToUsers.size() + i + 1)])
execute.update.activity = INSERT into cr_schedule_activity_log (schedule_id, notes, run_time) values ([getScheduleID()],'Submitted:Schedule',TO_DATE('[getRunDate()] [getRunHour()]:[getRunMin()] [getRunAMPM()]', 'MM/DD/YYYY HH:MI AM'))
delete.schedule.data = SELECT 1 FROM cr_report_schedule WHERE rep_id = [reportID] and sched_user_id = [getScheduleUserID()] and schedule_id = [getScheduleID()]
delete.schedule.data.users = DELETE from cr_report_schedule_users WHERE rep_id = [reportID] and schedule_id = [getScheduleID()]
delete.schedule.data.id = DELETE FROM cr_report_schedule where rep_id = [reportID] and sched_user_id = [getScheduleUserID()] and schedule_id = [getScheduleID()]
load.cond.sql = SELECT condition_large_sql FROM cr_report_schedule WHERE schedule_id=?
load.cond.sql.select = SELECT condition_sql FROM cr_report_schedule WHERE schedule_id = [scheduleId]
persist.cond.sql.update = update cr_report_schedule set condition_large_sql = EMPTY_CLOB() where schedule_id = [scheduleId]
persist.cond.sql.large = SELECT condition_large_sql FROM cr_report_schedule cr WHERE schedule_id=? FOR UPDATE
persist.cond.sql.set = update cr_report_schedule set condition_sql = ? where schedule_id = [scheduleId]
#DataCache.java
get.data.view.actions = SELECT ts.web_view_action FROM cr_table_source ts WHERE ts.web_view_action IS NOT NULL
get.public.report.id.names = SELECT rep_id, title FROM cr_report WHERE public_yn = 'Y' ORDER BY title
get.private.accessible.names.a = SELECT cr.rep_id, cr.title FROM cr_report cr WHERE cr.rep_id not in (select rep_id from cr_report_access cra where user_id = '[user_id]'
get.private.accessible.names.if = OR role_id in (
get.private.accessible.names.b = ) AND public_yn = 'N' and cr.owner_id = '[user_id]' order by 2
get.group.accessible.names.a = SELECT cr.rep_id, cr.title FROM cr_report cr WHERE cr.rep_id in (select rep_id from cr_report_access cra where user_id = '[user_id]'
get.group.accessible.names.b = ) AND public_yn = 'N' order by 2
get.report.table.sources.a = SELECT table_name, display_name, pk_fields, web_view_action, large_data_source_yn, filter_sql FROM cr_table_source
get.report.table.sources.where = where SOURCE_DB= '[dBInfo]'
get.report.table.sources.if = where SOURCE_DB is null or SOURCE_DB = '[AppConstants.DB_LOCAL]'
get.report.table.sources.else = ORDER BY table_name
grab.report.table.a = SELECT ts.table_name, ts.display_name, ts.pk_fields, ts.web_view_action, ts.large_data_source_yn, ts.filter_sql FROM cr_table_source ts WHERE
grab.report.table.if = ts.SOURCE_DB= '[dBInfo]'
grab.report.table.else = (ts.SOURCE_DB is null or ts.SOURCE_DB = '[AppConstants.DB_LOCAL]')
grab.report.table.b = minus SELECT ts.table_name, ts.display_name, ts.pk_fields, ts.web_view_action, ts.large_data_source_yn, ts.filter_sql from cr_table_source ts where table_name in (select table_name from cr_table_role where role_id not IN [sb.toString()]) and
grab.report.table.c = ORDER BY 1
get.report.table.crjoin = SELECT src_table_name, dest_table_name, join_expr FROM cr_table_join
get.report.table.joins = SELECT tj.src_table_name, tj.dest_table_name, tj.join_expr FROM cr_table_join tj WHERE ((EXISTS (SELECT 1 FROM cr_table_role trs WHERE trs.table_name=tj.src_table_name AND trs.role_id IN [sb.toString()])) OR (NOT EXISTS (SELECT 1 FROM cr_table_role trs WHERE trs.table_name=tj.src_table_name))) AND ((EXISTS (SELECT 1 FROM cr_table_role trd WHERE trd.table_name=tj.dest_table_name AND trd.role_id IN [sb.toString()])) OR (NOT EXISTS (SELECT 1 FROM cr_table_role trd WHERE trd.table_name=tj.dest_table_name)))
generate.report.table.col = SELECT a.table_name, a.column_name, a.data_type, a.label FROM user_column_def a WHERE a.table_name = '[tableName.toUpperCase()]' ORDER BY a.column_id
generate.db.user.sql.a = SELECT utc.table_name, utc.column_name, utc.data_type,
generate.db.user.sql.if = utc.column_name label FROM user_tab_columns utc
generate.db.user.sql.else = nvl(x.label, utc.column_name) label FROM user_tab_columns utc
generate.db.user.sql.b = WHERE utc.table_name = '[tableName.toUpperCase()]'
generate.db.user.sql.c = AND utc.table_name = x.table_name AND utc.column_name = x.column_name
generate.db.user.sql.d = ORDER BY utc.column_id
#SearchHandler.java
load.report.search.result = SELECT cr.rep_id, cr.rep_id report_id, [rep_title_sql] title, cr.descr, (au.first_name||' '||au.last_name) owner_name, TO_CHAR(cr.create_date, 'MM/DD/YYYY'), (CASE WHEN nvl(cr.owner_id, cr.create_id) = [userID] THEN 'N' ELSE nvl(ra.read_only_yn, 'Y') END) read_only_yn, (CASE WHEN nvl(cr.owner_id, cr.create_id) = [userID] THEN 'Y' ELSE 'N' END) user_is_owner_yn, (case when report_xml like '%N%' then 'N' when report_xml like '%Y%' or 1 = (select distinct 1 from cr_report_schedule where rep_id = cr.rep_id) then 'Y' else 'N' end) FROM cr_report cr JOIN fn_user au ON nvl (cr.owner_id, cr.create_id) = au.user_id [fReportID] [fReportName] LEFT JOIN(SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra ON ra.rep_id = cr.rep_id
load.report.search.rep_id_sql = AND ROUND(cr.rep_id, 0) like nvl('%%', ROUND(cr.rep_id, 0))
load.report.search.instr = AND INSTR('|'||cr.menu_id||'|', '|'||'[menuId]'||'|') > 0
load.report.search.result.user = AND nvl(cr.owner_id, cr.create_id) = [userID]
load.report.search.result.public = AND (nvl(cr.owner_id, cr.create_id) = [userID] OR cr.public_yn = 'Y' OR ra.read_only_yn IS NOT NULL)
load.report.search.result.fav = AND cr.rep_id in (select rep_id from cr_favorite_reports where user_id = [userID]
load.report.search.result.sort = ORDER BY DECODE(nvl(cr.owner_id, cr.create_id), [userID], ' ', upper(au.first_name||' '||au.last_name)), upper(cr.title)
load.folder.report.result = SELECT cr.rep_id, cr.rep_id report_id, [rep_title_sql]||DECODE(cr.public_yn, 'Y', '', '[PRIVATE_ICON]')||cr.title||'' title, cr.descr, au.first_name||' '||au.last_name owner_name, TO_CHAR(cr.create_date, 'MM/DD/YYYY') create_date, DECODE(NVL(cr.owner_id, cr.create_id), [userID], 'N', NVL(ra.read_only_yn, 'Y')) read_only_yn, DECODE(NVL(cr.owner_id, cr.create_id), [userID], 'Y', 'N') user_is_owner_yn FROM cr_report cr, app_user au, (SELECT rep_id, MIN(read_only_yn) read_only_yn FROM ((SELECT ua.rep_id, ua.read_only_yn FROM cr_report_access ua WHERE ua.user_id = [userID]) UNION ALL (SELECT ra.rep_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.role_id IN ([roleList.toString()]))) report_access GROUP BY rep_id) ra WHERE TO_CHAR(cr.rep_id) = nvl('[fReportID]', TO_CHAR(cr.rep_id)) AND UPPER(cr.title) LIKE UPPER('%[fReportName]%') AND nvl(cr.owner_id, cr.create_id) = au.user_id AND cr.rep_id = ra.rep_id (+)
load.folder.report.result.sort = ORDER BY DECODE(nvl(cr.owner_id, cr.create_id), [userID], ' ', au.first_name||' '||au.last_name), cr.title
#WizardProcessor.java
process.filter.add.edit = '[argValue]' FROM dual
#ReportDefinition.java
persist.report.adhoc = SELECT [Globals.getAdhocReportSequence()].nextval FROM dual
#Globals.java
initialize.roles = SELECT 1 FROM dual WHERE EXISTS (SELECT 1 FROM cr_table_role)
initialize.version = SELECT cr_raptor.get_version FROM dual
# scheduler
scheduler.available.schedules = SELECT x.rep_id, x.schedule_id, x.conditional_yn, x.condition_large_sql, x.notify_type, x.max_row, x.initial_formfields, x.processed_formfields, r.title, x.user_id FROM ( SELECT rs.rep_id, rs.schedule_id, rs.sched_user_id user_id, rs.conditional_yn, rs.condition_large_sql, rs.notify_type, rs.max_row, rs.initial_formfields, rs.processed_formfields FROM cr_report_schedule rs WHERE rs.enabled_yn='Y' AND rs.start_date <= [currentDate] AND (rs.end_date >= [currentDate] or rs.end_date is null ) AND rs.run_date IS NOT NULL ) x, cr_report r WHERE x.rep_id = r.rep_id
random.string = select DBMS_RANDOM.STRING('X',DBMS_RANDOM.VALUE(20,25)) random from dual
scheduler.user.emails = SELECT au.user_id FROM (SELECT rs.schedule_id, rs.rep_id FROM cr_report_schedule rs WHERE rs.enabled_yn='Y' AND rs.start_date <= sysdate AND trunc(rs.end_date+1)-1/86400 >= sysdate AND rs.run_date IS NOT NULL AND rs.schedule_id = [p_schedule_id] ) x, cr_report r, fn_user au WHERE x.rep_id = r.rep_id AND au.user_id IN (SELECT rsu.user_id FROM cr_report_schedule_users rsu WHERE rsu.schedule_id = x.schedule_id and rsu.schedule_id = [p_schedule_id] UNION SELECT ur.user_id FROM fn_user_role ur WHERE ur.role_id IN (SELECT rsu2.role_id FROM cr_report_schedule_users rsu2 WHERE rsu2.schedule_id = x.schedule_id and rsu2.schedule_id = [p_schedule_id]))
# basic sql
convert.to.raw = utl_raw.cast_to_raw
seq.next.val = SELECT [sequenceName].nextval as ID FROM dual
current.date.string = sysdate
# report security
report.user.access = SELECT ra.role_id, ra.user_id, ra.read_only_yn FROM cr_report_access ra WHERE ra.rep_id = [reportID]
add.user.access = INSERT INTO cr_report_access (rep_id, order_no, role_id, user_id, read_only_yn) VALUES([reportID], IFNULL((select order_no from (SELECT MAX(order_no) AS order_no FROM cr_report_access WHERE rep_id=[reportID]) AS temp), 0)+1, NULL, [userID], '[readOnlyAccess]')
update.user.access = UPDATE cr_report_access SET read_only_yn='[readOnlyAccess]' WHERE rep_id=[reportID] AND user_id=[userID]
remove.user.access = DELETE FROM cr_report_access WHERE rep_id=[reportID] AND user_id=[userID]
add.role.access = INSERT INTO cr_report_access (rep_id, order_no, role_id, user_id, read_only_yn) VALUES([reportID], IFNULL((select order_no from (SELECT MAX(order_no) AS order_no FROM cr_report_access WHERE rep_id=[reportID]) AS temp), 0)+1, [roleID], NULL, '[readOnlyAccess]')
update.role.access = UPDATE cr_report_access SET read_only_yn='[readOnlyAccess]' WHERE rep_id=[reportID] AND role_id=[roleID]
remove.role.access = DELETE FROM cr_report_access WHERE rep_id=[reportID] AND role_id=[roleID]