UPDATE RootCauseAnalysis set rca_created_date = IF (SubmissionDate < IFNULL(LastUpdated,NOW()), SubmissionDate, LastUpdated) WHERE rca_created_date = '0000-00-00';
UPDATE RootCauseAnalysis set rca_created_by_per_id = SubmittedBy WHERE rca_created_by_per_id = 0;


INSERT INTO comment_table(cmt_id, cmt_name, cmt_description, cmt_created_date, cmt_created_by_per_id, cmt_enable) SELECT 16 AS cmt_id, 'Lineup Module' AS cmt_name, 'lineup_attachment' AS cmt_description, NOW() AS cmt_created_date, 1 AS cmt_created_by_per_id, 1 AS cmt_enable FROM dual WHERE NOT EXISTS (SELECT 1 FROM comment_table WHERE cmt_id = 16 AND cmt_name = 'Lineup Module' AND cmt_description = 'lineup_attachment' AND cmt_enable = 1);

INSERT INTO Reports (ReportName, ReportDescription, ReportCategory,ReportURL, 
SSRSReportName, rpt_output_type, rpt_enable, rpt_allow_future_dates)

SELECT 9639 AS ReportName, 9638 AS ReportDescription, 'Report' AS ReportCategory, 
'employee_sign_in_history' AS ReportURL, 'employeeSignInHistory' AS SSRSReportName, 
'pageable/pdf' AS rpt_output_type, 1 AS rpt_enable, 0 AS rpt_allow_future_dates 
FROM dual WHERE NOT EXISTS
(SELECT 1 FROM Reports WHERE ReportName = 9639 AND ReportDescription = 9638 
AND ReportCategory = 'Report' AND ReportURL = 'employee_sign_in_history' 
AND SSRSReportName = 'employeeSignInHistory' AND rpt_output_type = 'pageable/pdf' 
AND rpt_enable = 1 AND rpt_allow_future_dates = 0);

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,
rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 116 AS rfm_rpt_id, 4 AS rfm_rft_id, 0 AS rfm_is_mandatory, 
NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual 
WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 116 AND rfm_rft_id = 4 
AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);



INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,
rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 116 AS rfm_rpt_id, 15 AS rfm_rft_id, 0 AS rfm_is_mandatory, 
NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual 
WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 116 AND rfm_rft_id = 15 
AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);



INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,
rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 116 AS rfm_rpt_id, 21 AS rfm_rft_id, 1 AS rfm_is_mandatory, 
NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual 
WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 116 AND rfm_rft_id = 21 
AND rfm_is_mandatory = 1 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);



INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,
rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 116 AS rfm_rpt_id, 22 AS rfm_rft_id, 0 AS rfm_is_mandatory, 
NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual 
WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 116 AND rfm_rft_id = 22 
AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);



INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,
rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 116 AS rfm_rpt_id, 25 AS rfm_rft_id, 0 AS rfm_is_mandatory, 
NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual 
WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 116 AND rfm_rft_id = 25
AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);



INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,
rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 116 AS rfm_rpt_id, 11 AS rfm_rft_id, 0 AS rfm_is_mandatory, 
NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual 
WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 116 AND rfm_rft_id = 11
AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);


INSERT INTO form_field_visibility (ffv_ffd_id, ffv_is_visible, ffv_created_date, ffv_created_by_per_id, ffv_enable, ffv_enote)
SELECT 16907 AS ffv_ffd_id, 1 AS ffv_is_visible, '2023-09-01' AS ffv_created_date, 1 AS ffv_created_by_per_id, 1 AS ffv_enable, 'SOF-15001' AS ffv_enote from dual WHERE NOT EXISTS
(SELECT 1 FROM form_field_visibility WHERE ffv_ffd_id = 16907 AND ffv_created_date = '2023-09-01' AND ffv_created_by_per_id = 1 AND ffv_enable = 1 AND ffv_enote = 'SOF-15001');

INSERT INTO form_field_visibility (ffv_ffd_id, ffv_is_visible, ffv_created_date, ffv_created_by_per_id, ffv_enable, ffv_enote)
SELECT 16909 AS ffv_ffd_id, 1 AS ffv_is_visible, '2023-09-01' AS ffv_created_date, 1 AS ffv_created_by_per_id, 1 AS ffv_enable, 'SOF-15001' AS ffv_enote from dual WHERE NOT EXISTS
(SELECT 1 FROM form_field_visibility WHERE ffv_ffd_id = 16909 AND ffv_created_date = '2023-09-01' AND ffv_created_by_per_id = 1 AND ffv_enable = 1 AND ffv_enote = 'SOF-15001');

INSERT INTO form_field_visibility (ffv_ffd_id, ffv_is_visible, ffv_created_date, ffv_created_by_per_id, ffv_enable, ffv_enote)
SELECT 16911 AS ffv_ffd_id, 1 AS ffv_is_visible, '2023-09-01' AS ffv_created_date, 1 AS ffv_created_by_per_id, 1 AS ffv_enable, 'SOF-15001' AS ffv_enote from dual WHERE NOT EXISTS
(SELECT 1 FROM form_field_visibility WHERE ffv_ffd_id = 16911 AND ffv_created_date = '2023-09-01' AND ffv_created_by_per_id = 1 AND ffv_enable = 1 AND ffv_enote = 'SOF-15001');

INSERT INTO form_field_visibility (ffv_ffd_id, ffv_is_visible, ffv_created_date, ffv_created_by_per_id, ffv_enable, ffv_enote)
SELECT 16913 AS ffv_ffd_id, 1 AS ffv_is_visible, '2023-09-01' AS ffv_created_date, 1 AS ffv_created_by_per_id, 1 AS ffv_enable, 'SOF-15001' AS ffv_enote from dual WHERE NOT EXISTS
(SELECT 1 FROM form_field_visibility WHERE ffv_ffd_id = 16913 AND ffv_created_date = '2023-09-01' AND ffv_created_by_per_id = 1 AND ffv_enable = 1 AND ffv_enote = 'SOF-15001');


update LineupWorkersPPE lw set lw.lpp_enable = 1 WHERE lw.lpp_enable = 0;
update LineupEquipment le set leq_enable = 1;
update LineupWorkers lw set lwo_enable = 1;
update LineupWorkplaces lw set lw.lwp_enable = 1;
update LineupFormHeader set lfh_enable = 1;


UPDATE SubmissionHAP sh set sh.sha_is_group_action = 0, sh.sha_enote =CONCAT('SOF-14788 - Set sha_is_group_action to 0 or 1.', IFNULL(sh.sha_enote, '')) WHERE ISNULL(sh.sha_is_group_action);

INSERT INTO Reports (ReportName, ReportDescription, ReportCategory,       ReportURL, SSRSReportName, rpt_output_type, rpt_enable, rpt_allow_future_dates)
SELECT 9615 AS ReportName, 9627 AS ReportDescription, 'Report' AS ReportCategory, 'archived_items' AS ReportURL, 'archiveditems' AS SSRSReportName, 'pageable/pdf' AS rpt_output_type, 1 AS rpt_enable, 0 AS rpt_allow_future_dates FROM dual WHERE NOT EXISTS
(SELECT 1 FROM Reports WHERE ReportName = 9615 AND ReportDescription = 9627 AND ReportCategory = 'Report' AND ReportURL = 'archived_items' AND SSRSReportName = 'archiveditems' AND rpt_output_type = 'pageable/pdf' AND rpt_enable = 1 AND rpt_allow_future_dates = 0);


INSERT INTO ref_list_header (rlh_rlt_id, rlh_name, rlh_display_name, rlh_tag_type, rlh_is_code_required, rlh_is_score_required, rlh_is_name_required, rlh_is_description_required, rlh_is_active_required, rlh_created_date, rlh_created_by_per_id, rlh_enable, rlh_is_core_list) 
SELECT 1 AS rlh_rlt_id, 'ref_module_list' AS rlh_name, 3501 AS rlh_display_name, 1 AS rlh_tag_type, 0 AS rlh_is_code_required, 0 AS rlh_is_score_required, 1 AS rlh_is_name_required, 0 AS rlh_is_description_required, 1 AS rlh_is_active_required, NOW() AS rlh_created_date, 1 AS rlh_created_by_per_id, 1 AS rlh_enable, 0 AS rlh_is_core_list FROM dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_header WHERE rlh_rlt_id = 1 AND rlh_name = 'ref_module_list' AND rlh_display_name = 3501 AND rlh_tag_type = 1 AND rlh_is_code_required = 0 AND rlh_is_score_required = 0 AND rlh_is_name_required = 1 AND rlh_is_description_required = 0 AND rlh_is_active_required = 1 AND  rlh_created_by_per_id = 1 AND rlh_enable = 1 AND rlh_is_core_list = 0);

INSERT INTO report_filter_type ( rft_name, rft_tag, rft_tag_type, rft_type, rft_rlh_id, rft_sp, rft_show_filter, rft_created_date, rft_created_by_per_id, rft_enable) 
SELECT 'module_type' AS rft_name, 3501 AS rft_tag, 1 AS rft_tag_type, 'multiselect' AS rft_type, 110 AS rft_rlh_id, 'rpt_get_ref_list_items' AS rft_sp, 1 AS rft_show_filter,NOW() AS rft_created_date, 1 AS rft_created_by_per_id, 1 AS rft_enable FROM dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type WHERE rft_name='module_type' AND rft_tag = 3501 AND rft_tag_type = 1 AND rft_type = 'multiselect' AND rft_rlh_id = 110 AND rft_sp = 'rpt_get_ref_list_items' AND rft_show_filter = 1 AND rft_created_by_per_id = 1 AND rft_enable = 1);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'lessons_learned' AS rld_code, 
1036 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'lessons_learned' 
AND rld_name = 1036
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'lockout_tagout' AS rld_code, 
2697 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'lockout_tagout' 
AND rld_name = 2697
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'decision_log' AS rld_code, 
1334 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'decision_log' 
AND rld_name = 1334
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'documents' AS rld_code, 
1344 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'documents' 
AND rld_name = 1344
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'ora' AS rld_code, 
2103 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'organization_risk_assessment' 
AND rld_name = 2103
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'pra' AS rld_code, 
2259 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'projectprocess_risk_assessment' 
AND rld_name = 2259
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'jra' AS rld_code, 
2253 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'job_risk_assessment' 
AND rld_name = 2253
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'bowtie' AS rld_code, 
2291 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'bowtie_risk_assessment' 
AND rld_name = 2291
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'custom_forms' AS rld_code, 
9133 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'custom_forms' 
AND rld_name = 9133
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'custom_lists' AS rld_code, 
9180 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'custom_lists' 
AND rld_name = 9180
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'equipment_preops' AS rld_code, 
3500 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'equipment_preops' 
AND rld_name = 3500
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'training_record' AS rld_code, 
8658 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'training_record' 
AND rld_name = 8658
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'targets' AS rld_code, 
1033 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'targets' 
AND rld_name = 1033
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

INSERT INTO ref_list_detail 
(rld_rlh_id,rld_code,rld_name, rld_tag_type, 
rld_is_active,rld_created_date,rld_created_by_per_id,rld_enable,
rld_deleted)
SELECT 110 AS rld_rlh_id, 'hours_log' AS rld_code, 
2723 AS rld_name, 
1 AS rld_tag_type, 
1 AS rld_is_active, NOW() AS rld_created_date, 1 AS rld_created_by_per_id,
1 AS rld_enable, 0 AS rld_deleted
from dual WHERE NOT EXISTS
(SELECT 1 FROM ref_list_detail 
WHERE rld_rlh_id = 110 AND rld_code = 'hours_log' 
AND rld_name = 2723
AND rld_tag_type = 1 AND rld_is_active = 1 AND rld_created_by_per_id = 1
AND rld_enable = 1 AND rld_deleted = 0);

Report scheduler -

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 114 AS rfm_rpt_id, 15 AS rfm_rft_id, 0 AS rfm_is_mandatory, NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 114 AND rfm_rft_id = 15 AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 114 AS rfm_rpt_id, 4 AS rfm_rft_id, 0 AS rfm_is_mandatory, NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 114 AND rfm_rft_id = 4 AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 114 AS rfm_rpt_id, 21 AS rfm_rft_id, 1 AS rfm_is_mandatory, NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 114 AND rfm_rft_id = 21 AND rfm_is_mandatory = 1 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 114 AS rfm_rpt_id, 22 AS rfm_rft_id, 0 AS rfm_is_mandatory, NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 114 AND rfm_rft_id = 22 AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 114 AS rfm_rpt_id, 25 AS rfm_rft_id, 0 AS rfm_is_mandatory, NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 114 AND rfm_rft_id = 25 AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);

INSERT INTO report_filter_type_mapping (rfm_rpt_id,rfm_rft_id,rfm_is_mandatory,rfm_created_date,rfm_created_by_per_id, rfm_enable)
SELECT 114 AS rfm_rpt_id, 42 AS rfm_rft_id, 0 AS rfm_is_mandatory, NOW() AS rfm_created_date, 1 AS rfm_created_by_per_id, 1 AS rfm_enable from dual WHERE NOT EXISTS
(SELECT 1 FROM report_filter_type_mapping WHERE rfm_rpt_id = 114 AND rfm_rft_id = 42 AND rfm_is_mandatory = 0 AND rfm_created_by_per_id = 1 AND rfm_enable = 1);

-- ACTION SCHEDULER -  INSERT STATEMENTS FOR system_settings TABLE 

INSERT into system_settings (sys_setting_type, sys_setting_name, sys_setting_value, sys_created_date, sys_created_by_per_id, sys_enable)
SELECT 8 as sys_setting_type,  'ACTION_SCHEDULER_RUN_STATUS' as sys_setting_name, 1 as sys_setting_value, NOW() as sys_created_date, 1 as sys_created_by_per_id, 1 as sys_enable from dual where not exists(SELECT 1 from system_settings where sys_setting_type = 8 and sys_setting_name='ACTION_SCHEDULER_RUN_STATUS' and sys_setting_value = 1 and sys_created_by_per_id = 1 and sys_enable = 1); 

INSERT into system_settings (sys_setting_type, sys_setting_name, sys_setting_value, sys_created_date, sys_created_by_per_id, sys_enable)
SELECT 8 as sys_setting_type,  'ACTION_SCHEDULER_RUN_HOUR' as sys_setting_name, 3 as sys_setting_value, NOW() as sys_created_date, 1 as sys_created_by_per_id, 1 as sys_enable from dual where not exists(SELECT 1 from system_settings where sys_setting_type = 8 and sys_setting_name='ACTION_SCHEDULER_RUN_HOUR' and sys_setting_value = 3 and sys_created_by_per_id = 1 and sys_enable = 1);

INSERT into system_settings (sys_setting_type, sys_setting_name, sys_setting_value, sys_created_date, sys_created_by_per_id, sys_enable)
SELECT 8 as sys_setting_type,  'ACTION_SCHEDULER_RUN_MINUTE' as sys_setting_name, 0 as sys_setting_value, NOW() as sys_created_date, 1 as sys_created_by_per_id, 1 as sys_enable from dual where not exists(SELECT 1 from system_settings where sys_setting_type = 8 and sys_setting_name='ACTION_SCHEDULER_RUN_MINUTE' and sys_setting_value =0 and sys_created_by_per_id = 1 and sys_enable = 1);

-- PERMISSIONS - INSERT STATEMENTS FOR auth_permission_sofvie table

INSERT INTO auth_permission_sofvie (ape_name, ape_display_name, ape_parent_id, ape_type_option, ape_created_date, ape_created_by_per_id, ape_enable)
SELECT 'Can Manage Recurring Actions' as ape_name, 9650 as ape_display_name, null as ape_parent_id, 3 as ape_type_option, NOW() as ape_created_date, 1 as ape_created_by_per_id, 1 as ape_enable from dual where not EXISTS ( SELECT 1 from auth_permission_sofvie where ape_name = 'Can Manage Recurring Actions' and ape_display_name = 9650 and ape_parent_id is NULL and ape_type_option = 3 and ape_created_by_per_id =1 and ape_enable = 1);

-- ADD NEW PERMISSION TO ADMIN in auth_role_permission_sofvie
INSERT into auth_role_permission_sofvie (arp_aro_id, arp_ape_id, arp_created_date, arp_created_by_per_id, arp_enable)
SELECT 8 as arp_aro_id, (SELECT ape_id from auth_permission_sofvie where ape_display_name = 9650) as arp_ape_id, NOW() as arp_created_date, 1 as arp_created_by_per_id, 1 as arp_enable from dual where not EXISTS (SELECT 1 from auth_role_permission_sofvie where arp_aro_id = 8 and arp_ape_id = (SELECT ape_id from auth_permission_sofvie where ape_display_name = 9650) and arp_created_by_per_id = 1 and arp_enable = 1);