CREATE OR REPLACE PROCEDURE ilm_reports.ilm_investigation_no_subquery_dwh_report_procedure() LANGUAGE plpgsql AS $procedure$ declare l_min_inv varchar; declare l_max_inv varchar; declare l_min_pol_no varchar; declare l_max_pol_no varchar; declare l_min_clm_typ varchar(100); declare l_max_clm_typ varchar(100); declare l_status_name_min varchar(100); declare l_status_name_max varchar(100); declare l_line_of_business varchar(100); declare l_s1_name varchar(100); declare l_s2_name varchar(100); declare l_case_owner_min varchar(100); declare l_case_owner_max varchar(100); declare l_v1_name varchar(100); declare l_v2_name varchar(100); declare l_qc_status_min varchar(100); declare l_qc_status_max varchar(100); declare l_vendor_allocation_date_min timestamp; declare l_vendor_allocation_date_max timestamp; declare l_vd_report_submitted_date_min timestamp; declare l_vd_report_submitted_date_max timestamp; declare l_rpt_submission_date_qc1_min timestamp; declare l_rpt_submission_date_qc1_max timestamp; declare l_rpt_submission_date_qc2_min timestamp; declare l_rpt_submission_date_qc2_max timestamp; declare l_rpt_submission_date_qc3_min timestamp; declare l_rpt_submission_date_qc3_max timestamp; declare l_findings_min varchar(5000); declare l_findings_max varchar(5000); declare l_ground_rep_min varchar(5000); declare l_ground_rep_max varchar(5000); declare l_savings_min varchar(5000); declare l_savings_max varchar(5000); declare l_fraud_min varchar(5000); declare l_fraud_max varchar(5000); declare l_evidence_min varchar(5000); declare l_evidence_max varchar(5000); declare l_paid_amount_min INTEGER; declare l_paid_amount_max INTEGER; declare l_payment_date_min timestamp; declare l_payment_date_max timestamp; declare l_Vendor_remarks_min varchar(5000); declare l_Vendor_remarks_max varchar(5000); declare l_referral_date_min timestamp; declare l_referral_date_max timestamp; declare l_regional_manager_name varchar(5000); declare l_qc_remarks_min varchar(5000); declare l_qc_remarks_max varchar(5000); declare l_qc2_remarks_min varchar(5000); declare l_qc2_remarks_max varchar(5000); declare l_qc3_remarks_min varchar(5000); declare l_qc3_remarks_max varchar(5000); declare l_invqc_remarks_min varchar(5000); declare l_invqc_remarks_max varchar(5000); declare l_is_waived_off varchar(100); declare l_is_waived_off_date_min timestamp; declare l_waive_off_date_max timestamp; declare l_is_closed varchar(100); declare l_is_closed_date_min timestamp; declare l_is_closed_date_max timestamp; declare l_is_withdrawn varchar(100); declare l_withdrawn_remarks varchar(5000); declare l_in_out_tat varchar(100); declare l_open_tat_min varchar(100); declare l_closing_tat_min varchar(100); declare x record; declare seq INTEGER = '0'; declare l_claim_number varchar(100); begin truncate table ilm_reports.ilm_investigations_dwh_report3; FOR x IN ( SELECT distinct cd.claim_number, cd.investigation_id FROM ilm_investigations.claim_details cd where cd.claim_number is not null limit 10000 ) LOOP begin SELECT min(i.inv_no) INTO l_min_inv FROM ilm_investigations.investigations i WHERE i.ID in (x.investigation_id); exception when others then null; end; begin select i.inv_no INTO l_max_inv from ilm_investigations.investigations i where i.id in ( select distinct case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end from ilm_investigations.investigations i ); -- exception -- when others then -- null; end; begin select min(pd.policy_no), max(pd.policy_no) INTO l_min_pol_no, l_max_pol_no from ilm_investigations.policy_details pd where pd.investigation_id in (x.investigation_id); exception when others then null; end; begin SELECT min(i.claim_type), max(i.claim_type) INTO l_min_clm_typ, l_max_clm_typ FROM ilm_investigations.investigations i WHERE i.id in (x.investigation_id); exception when others then null; end; begin select min(sm.name) into l_status_name_min from ilm_masters.status_master sm where sm.id in ( select it.status_id from ilm_investigations.inv_tasks it where it.investigation_id in (x.investigation_id) ); exception when others then null; end; begin select sm.name into l_status_name_max from ilm_masters.status_master sm where sm.id in ( select distinct case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( select it.status_id from ilm_investigations.inv_tasks it where it.investigation_id in ( ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) ) else null end from ilm_investigations.investigations i ); exception when others then null; end; begin select MAX(line_of_business) INTO l_line_of_business from ilm_masters.claim_types where id IN ( select i.claim_type_id from ilm_investigations.investigations i where i.id IN(x.investigation_id) ); exception when others then null; end; begin select (u.first_name) || ' ' || (u.last_name) as so_name into l_s2_name from ilm_masters.users u where u.id in ( select it.state_owner from ilm_investigations.inv_tasks it where it.investigation_id in ( max (x.investigation_id) ) ); exception when others then null; end; begin select min(it.assigned_to_name), max(it.assigned_to_name) into l_case_owner_min, l_case_owner_max from ilm_investigations.inv_tasks it where it.investigation_id in (x.investigation_id); exception when others then null; end; begin select ia.investigator_name into l_v2_name from ilm_investigations.investigator_assignments ia where ia.investigation_id in ( select distinct case when ( select count(cdb.investigation_id)>= 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end from ilm_investigations.investigations i ); -- exception -- when others then -- null; end; begin select (hlinv.qc_inv_status) as qc_status_max into l_qc_status_max from ilm_investigations_health.hl_investigation_details hlinv where hlinv.section_code = 'qc_section' and hlinv.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( max(x.investigation_id) ) ); exception when others then null; end; begin select min(activity.created_date) as vendor_allocation_date_min into l_vendor_allocation_date_min from ilm_investigations.inv_activities activity where activity.activity_type = 'Assigned' and activity.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select cdb.investigation_id from ilm_investigations.claim_details cdb join ilm_investigations.investigator_assignments ia on ia.investigation_id = cdb.investigation_id where cdb.claim_number = x.claim_number and ia.is_active = 'TRUE' and ia.org_type = 'EXTERNAL' ) ); exception when others then null; end; begin select into l_vendor_allocation_date_max ( select case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( select (ia.created_date) as vendor_allocation_date_max from ilm_investigations.inv_activities ia where ia.activity_type = 'Assigned' and ia.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) ) else null end ); exception when others then null; end; begin select min(ia.created_date) as vd_report_submitted_date_min into l_vd_report_submitted_date_min from ilm_investigations.inv_activities ia where ia.current_value in ( 'Pending for review by QC1', 'Report Submitted', 'Report Submitted by External Inv(AH)', 'Report Submitted by Agency/Investigator' ) and ia.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select min(cdb.investigation_id) from ilm_investigations.claim_details cdb join ilm_investigations.investigator_assignments ia on ia.investigation_id = cdb.investigation_id where cdb.claim_number = x.claim_number ) ); exception when others then null; end; begin select into l_vd_report_submitted_date_max ( select case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( select (ia.created_date) as vd_report_submitted_date_max from ilm_investigations.inv_activities ia where ia.current_value in ( 'Pending for review by QC1', 'Report Submitted', 'Report Submitted by External Inv(AH)', 'Report Submitted by Agency/Investigator' ) and ia.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) ) else null end ); exception when others then null; end; begin select min(act.created_date) as rpt_submission_date_qc1_min into l_rpt_submission_date_qc1_min from ilm_investigations.inv_activities act where ( act.activity_type = 'Pending for review by QC2' or act.activity_type = 'Closed' or act.activity_type = 'CLOSED' ) and act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( min (x.investigation_id) ) ); exception when others then null; end; begin select into l_rpt_submission_date_qc1_max ( select case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( select (act.created_date) as rpt_submission_date_qc1_max from ilm_investigations.inv_activities act where ( act.activity_type = 'Pending for review by QC2' or act.activity_type = 'Closed' or act.activity_type = 'CLOSED' ) and act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) ) else null end ); exception when others then null; end; begin select (act.created_date) as rpt_submission_date_qc2_min into l_rpt_submission_date_qc2_min from ilm_investigations.inv_activities act where ( act.activity_type = 'Pending for review by QC3' or act.activity_type = 'Closed' or act.activity_type = 'CLOSED' ) and act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select min(cdb.investigation_id) from ilm_investigations.claim_details cdb join ilm_investigations.investigator_assignments ia on ia.investigation_id = cdb.investigation_id where cdb.claim_number = x.claim_number ) ); exception when others then null; end; begin select into l_rpt_submission_date_qc2_max ( select case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = '210031240A02' ) then ( select (act.created_date) as rpt_submission_date_qc2_max from ilm_investigations.inv_activities act where ( act.activity_type = 'Pending for review by QC3' or act.activity_type = 'Closed' or act.activity_type = 'CLOSED' ) and act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) ) else null end ); exception when others then null; end; begin select min(act.created_date) as rpt_submission_date_qc3_min, max(act.created_date) as rpt_submission_date_qc3_max into l_rpt_submission_date_qc3_min, l_rpt_submission_date_qc3_max from ilm_investigations.inv_activities act where ( act.activity_type = 'Closed' or act.activity_type = 'CLOSED' ) and act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in (x.investigation_id) ); exception when others then null; end; begin select (hlinv.investigation_findings) as findings_max into l_findings_max from ilm_investigations_health.hl_investigation_details hlinv where hlinv.section_code = 'qc_section' and hlinv.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( max (x.investigation_id) ) ); exception when others then null; end; begin select (hlinv.ground_rejection) as ground_rep_max into l_ground_rep_max from ilm_investigations_health.hl_investigation_details hlinv where hlinv.section_code = 'qc_section' and hlinv.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select max (x.investigation_id) ) ); exception when others then null; end; begin select (hlinv.saving) as savings_max into ----l_savings_min, l_savings_max from ilm_investigations_health.hl_investigation_details hlinv where hlinv.section_code = 'qc_section' and hlinv.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( max (x.investigation_id) ) ); exception when others then null; end; begin select (hlinv.fraud_severity) as fraud_max into l_fraud_max from ilm_investigations_health.hl_investigation_details hlinv where hlinv.section_code = 'qc_section' and hlinv.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( select max(x.investigation_id) ) ); exception when others then null; end; begin select (hlinv.evidence) as evidence_max into l_evidence_max from ilm_investigations_health.hl_investigation_details hlinv where hlinv.section_code = 'qc_section' and hlinv.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in ( max (x.investigation_id) ) ); exception when others then null; end; begin select (invoice.approved_grand_total) into l_paid_amount_max from ilm_investigations.invoice_details invoice where invoice.is_active = true and invoice.investigation_id in ( max (x.investigation_id) ); exception when others then null; end; begin select (invoice.modifieddate) as payment_date_min into l_payment_date_min from ilm_investigations.invoice_details invoice where invoice.is_active = true and invoice.investigation_id in ( select min(x.investigation_id) ); exception when others then null; end; begin select into l_payment_date_max ( select case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( select (invoice.modifieddate) as payment_date_max from ilm_investigations.invoice_details invoice where invoice.is_active = true and invoice.investigation_id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end ); exception when others then null; end; begin select (hid.investigation_findings) as Vendor_remarks_max into l_Vendor_remarks_max from ilm_investigations_health.hl_investigation_details hid where hid.section_code = 'invqc_section' and hid.investigation_id in ( select max(x.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ); exception when others then null; end; begin select (i.created_date) as referral_date_min into l_referral_date_min from ilm_investigations.investigations i where i.id in ( min(x.investigation_id) ); exception when others then null; end; begin select (i.created_date) as referral_date_max into l_referral_date_max from ilm_investigations.investigations i where i.id in( select distinct case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( ( select max (cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end from ilm_investigations.investigations i ); exception when others then null; end; begin select into l_payment_date_max ( select case when ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) then ( select (i.created_date) as referral_date_min from ilm_investigations.investigations i where i.id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end ); exception when others then null; end; -- final end begin select (u.first_name || ' ' || u.last_name) as regional_manager_name into l_regional_manager_name from ilm_investigations.inv_tasks it3 inner join ilm_masters.parties pa on pa.user_id = it3.state_owner inner join ilm_masters.party_reporting_to prt on prt.party_id = pa.id inner join ilm_masters.user_claim_types uct on uct.user_id = pa.user_id inner join ilm_masters.users u on u.id = prt.reporting_to_user_id inner join ilm_investigations.investigations i on i.claim_type_id = uct.claim_type_id where pa.user_id in ( select it.state_owner from ilm_investigations.inv_tasks it where it.investigation_id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ); exception when others then null; end; begin select min(hid.lob_qc_remarks) as qc_remarks_min, max(hid.lob_qc_remarks) as qc_remarks_max into l_qc_remarks_min, l_qc_remarks_max from ilm_investigations_health.hl_investigation_details hid where hid.section_code = 'qc_section' and hid.investigation_id in (x.investigation_id); exception when others then null; end; begin select min(hid.lob_qc_remarks) as qc2_remarks_min, max(hid.lob_qc_remarks) as qc2_remarks_max into l_qc2_remarks_min, l_qc2_remarks_max from ilm_investigations_health.hl_investigation_details hid where hid.section_code = 'qc2_section' and hid.investigation_id in (x.investigation_id); exception when others then null; end; -- final edit end --Qc3_remarks begin select min(hid.lob_qc_remarks) as qc3_remarks_min, max(hid.lob_qc_remarks) as qc3_remarks_max into l_qc3_remarks_min, l_qc3_remarks_max from ilm_investigations_health.hl_investigation_details hid where hid.section_code = 'qc3_section' and hid.investigation_id in (x.investigation_id); exception when others then null; end; --INVQC_remarks begin select min(hid.lob_qc_remarks) as invqc_remarks_min, max(hid.lob_qc_remarks) as invqc_remarks_max into l_invqc_remarks_min, l_invqc_remarks_max from ilm_investigations_health.hl_investigation_details hid where hid.section_code = 'invqc_section' and hid.investigation_id in (x.investigation_id); exception when others then null; end; begin select
into l_is_waived_off, l_is_waived_off_date_min, l_waive_off_date_max from ( select case when it.status_id = 86 then 'YES' else 'NO' end as is_waived_off, case when it.status_id = 86 then ( select it.modified_date from ilm_investigations.inv_tasks it where it.investigation_id in ( select min(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end as is_waived_off_date_min, case when ( (it.status_id = 86) and ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) then ( select (it.modified_date) from ilm_investigations.inv_tasks it where it.investigation_id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end as is_waived_off_date_max from ilm_investigations.inv_tasks it where it.investigation_id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) a; exception when others then null; end; begin select into l_is_closed, l_is_closed_date_min, l_is_closed_date_max from ( select case when it.status_id in (5, 86) then 'YES' else 'NO' end as is_closed, case when it.status_id in (5, 86) then ( select it.modified_date from ilm_investigations.inv_tasks it where it.investigation_id in ( select min(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end as is_closed_date_min, case when ( ( it.status_id in (5, 86) ) and ( select count(cdb.investigation_id)> 1 from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) then ( select (it.modified_date) from ilm_investigations.inv_tasks it where it.investigation_id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) else null end as is_closed_date_max from ilm_investigations.inv_tasks it where it.investigation_id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) a; exception when others then null; end; ---withdrawn begin select into l_is_withdrawn, l_withdrawn_remarks from ( select case when ( select count() from ilm_investigations.inv_activities act where act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in (x.investigation_id) ) and act.activity_type = 'Withdraw' )> 0 then 'Yes' else 'No' end as is_withdrawn, case when ( select count() from ilm_investigations.inv_activities act where act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in (x.investigation_id) ) and act.activity_type = 'Withdraw' )> 0 then ( select act.remarks from ilm_investigations.inv_activities act where act.inv_task_id in ( select it.id from ilm_investigations.inv_tasks it where it.investigation_id in (x.investigation_id) ) and act.activity_type = 'Withdraw' order by act.created_date desc limit 1 ) else null end as withdrawn_remarks ) a; exception when others then null; end; --in_out_tat begin select case when ( select extract ( epoch from ( now() :: timestamp - ( select i3.created_date from ilm_investigations.investigations i3 where i3.id in ( select max(cd.investigation_id) from ilm_investigations.claim_details cd where cd.claim_number = x.claim_number ) ) ) ):: integer / 60 )> ( select tc.tat_in_min from ilm_masters.tat_configs tc where tc.claim_type_id in ( select i2.claim_type_id from ilm_investigations.investigations i2 where i2.id in ( select max(cdb.investigation_id) from ilm_investigations.claim_details cdb where cdb.claim_number = x.claim_number ) ) ) then 'OUT TAT' else 'IN TAT' end as in_out_tat into l_in_out_tat; exception when others then null; end; --closing_tat_min begin select into l_open_tat_min, l_closing_tat_min from ( select 0 as open_tat_min, case when ( ( select it2.status_id from ilm_investigations.inv_tasks it2 where it2.investigation_id in ( select max(cd.investigation_id) from ilm_investigations.claim_details cd where cd.claim_number = x.claim_number ) )= 5 ) then ( select extract ( epoch from ( ( ( select it2.modified_date from ilm_investigations.inv_tasks it2 where it2.investigation_id in ( select max(cd.investigation_id) from ilm_investigations.claim_details cd where cd.claim_number = x.claim_number ) ) ) -( select i3.created_date from ilm_investigations.investigations i3 where i3.id in ( select max(cd.investigation_id) from ilm_investigations.claim_details cd where cd.claim_number = x.claim_number ) ) ) ) :: integer / 60 ) else null end as closing_tat_min ) a; exception when others then null; end; begin select cd.claim_number into l_claim_number from ilm_investigations.claim_details cd where cd.claim_number = x.claim_number; exception when others then null; end; seq := seq + 1; INSERT INTO ilm_reports.ilm_investigations_dwh_report3( id, min_inv_no, max_inv_no, min_policy_number, max_policy_number, min_claim_type, max_claim_type, status_name_min, status_name_max, line_of_business, so_name_min, so_name_max, case_owner_min, case_owner_max, v1_name, v2_name, qc_status_min, qc_status_max, vendor_allocation_date_min, vendor_allocation_date_max, vendor_report_submitted_date_min, vendor_report_submitted_date_max, rpt_submission_date_qc1_min, rpt_submission_date_qc1_max, rpt_submission_date_qc2_min, rpt_submission_date_qc2_max, rpt_submission_date_qc3_min, rpt_submission_date_qc3_max, findings_min, findings_max, ground_rep_min, ground_rep_max, savings_min, savings_max, fraud_min, fraud_max, evidence_min, evidence_max, paid_amount_min, paid_amount_max, payment_date_min, payment_date_max, vendor_remarks_min, vendor_remarks_max, referradate_min, referradate_max, regionamanager_name, qc_remarks_min, qc_remarks_max, qc2_remarks_min, qc2_remarks_max, qc3_remarks_min, qc3_remarks_max, invqc_remarks_min, invqc_remarks_max, is_waived_off, is_waived_off_date_min, waive_off_date_max, is_closed, is_closed_date_min, is_closed_date_max, is_withdrawn, withdrawn_remarks, in_out_tat, open_tat_min, closing_tat_min, process_date, claim_number ) VALUES ( seq, l_min_inv, l_max_inv, l_min_pol_no, l_max_pol_no, l_min_clm_typ, l_max_clm_typ, l_status_name_min, l_status_name_max, l_line_of_business, l_s1_name, l_s2_name, l_case_owner_min, l_case_owner_max, l_v1_name, l_v2_name, l_qc_status_min, l_qc_status_max, l_vendor_allocation_date_min, l_vendor_allocation_date_max, l_vd_report_submitted_date_min, l_vd_report_submitted_date_max, l_rpt_submission_date_qc1_min, l_rpt_submission_date_qc1_max, l_rpt_submission_date_qc2_min, l_rpt_submission_date_qc2_max, l_rpt_submission_date_qc3_min, l_rpt_submission_date_qc3_max, l_findings_min, l_findings_max, l_ground_rep_min, l_ground_rep_max, l_savings_min, l_savings_max, l_fraud_min, l_fraud_max, l_evidence_min, l_evidence_max, l_paid_amount_min, l_paid_amount_max, l_payment_date_min, l_payment_date_max, l_Vendor_remarks_min, l_Vendor_remarks_max, l_referral_date_min, l_referral_date_max, l_regional_manager_name, l_qc_remarks_min, l_qc_remarks_max, l_qc2_remarks_min, l_qc2_remarks_max, l_qc3_remarks_min, l_qc3_remarks_max, l_invqc_remarks_min, l_invqc_remarks_max, l_is_waived_off, l_is_waived_off_date_min, l_waive_off_date_max, l_is_closed, l_is_closed_date_min, l_is_closed_date_max, l_is_withdrawn, l_withdrawn_remarks, l_in_out_tat, l_open_tat_min, l_closing_tat_min, current_timestamp, l_claim_number ); commit; END LOOP; END; $procedure$; -- Permissions ALTER PROCEDURE ilm_reports.ilm_investigation_no_subquery_dwh_report_procedure() OWNER TO app_ilm; GRANT ALL ON PROCEDURE ilm_reports.ilm_investigation_no_subquery_dwh_report_procedure() TO public; GRANT ALL ON PROCEDURE ilm_reports.ilm_investigation_no_subquery_dwh_report_procedure() TO app_ilm;
1.this procedure will get the data from various table's and the values will be initialized into declared variables.then the variables will get inserted into another table name (ilm_investigations_dwh_report3).
2.by running this procedure in production database which will occupies high database utilization.
3.I redused the subquery call's in it
4.this query will trriger on daily basis for daily wise hourly report.the report must be send with in a day but the procedure will run more then a day
the question above is pretty much unreadable, please check out how to format code-blocks and then copy your code in one of those. I tried to put it through a SQL Beautifier but it failed to fully understand it too.
What I did learn from trying to read it is that you have a main loop that runs 10k times and for each loop you fetch some fields from a table, then go to another table to fetch some other fields based on the ones you just found etc etc... and then in the end store 1 result-record in a table
It probably is A LOT more efficient to use a temp-table to store the 10k 'starting elements' and then JOIN that temp-table with the next table and maybe store that result into a new table etc etc...
In fact, I did not check every step, but I think you can merge quite a few steps by JOIN-ing them together too.
In short: in SQL always try to work set-based and don't try to process things 'record by record'