CREATE OR REPLACE PROCEDURE cm_find_claim_coverage (
  i_insured_fk        number,
  i_loss_date         varchar2,
  i_report_date       varchar2,
  i_inc_start_date    varchar2,
  i_inc_end_date      varchar2,
  i_loss_state        varchar2,
  i_loss_county       varchar2,
  i_lob               varchar2,
  i_coverage_type     varchar2,
  o_ref_curs     IN OUT Oasis_Utility.t_ref_cursor) IS

  v_sql               varchar2( 32767 )  := '';
BEGIN
v_sql := cm_find_claim_coverage_sql(
 i_insured_fk,
 i_loss_date,
 i_report_date,
 i_inc_start_date,
 i_inc_end_date,
 i_loss_state,
 i_loss_county,
 i_lob,
 i_coverage_type);
  -- Output it all
  OPEN o_ref_curs FOR v_sql;
END cm_find_claim_coverage; 

CREATE OR REPLACE FUNCTION cm_find_claim_coverage_sql (
  i_insured_fk        number,
  i_loss_date         varchar2,
  i_report_date       varchar2,
  i_inc_start_date    varchar2,
  i_inc_end_date      varchar2,
  i_loss_state        varchar2,
  i_loss_county       varchar2,
  i_lob               varchar2,
  i_coverage_type     varchar2)
  return varchar2 IS

  search_days         number;
  verify_func         varchar2( 200 );
  cm_verify_func      varchar2( 200 );
  eff_from_dt         varchar2( 100 );
  eff_to_dt           varchar2( 100 );
  addl_sql            varchar2( 10000 );
  pm_installed_b      varchar2( 10 );
  pm_used_b           varchar2( 10 );
  user_profile        varchar2( 10 );
  sysparm_value       varchar2( 10 );
  pol_sysparm_value   varchar2( 10 );
  incl_invalid_b      varchar2( 1 );
  incl_invalid_pol_b  varchar2( 1 );
  v_pm_covg_type      system_parameter_util.sysparm_value%TYPE;

  v_sql               varchar2( 32767 )  := '';
BEGIN
  dbms_output.enable( 500000 );
  -- we will use (slightly) different SQL depending on whether the OASIS PM system is in use or not.  so
  -- determine if it is in use or note
  oasis_installation.get_installed_used_b('PM', pm_installed_b, pm_used_b);

  -- See if we ignore all dates in the retrieval
  -- Are we using the dates when verifying cov from mini policy
  sysparm_value := Upper(nvl(cs_get_system_parameter('CM_INC_CVI_INVLD_CVG'), 'N')) ;
  -- Are we using the dates when verifying cov from policy system
  pol_sysparm_value := Upper(nvl(cs_get_system_parameter('CM_INC_POL_INVLD_CVG'), 'N')) ;
  -- If one of these system paramenters is set to PROFILE then the user has to have that
  -- profile to be able to see all of the  coverages reguardless of the dates
  -- so see if the user has that profile
  user_profile := oasis_security.user_has_profile('CREATE INVALID COVERAGE CLAIM');

  IF sysparm_value = 'PROFILE' THEN
     IF user_profile = 'N' THEN
        incl_invalid_b := 'N';
     ELSE
        incl_invalid_b := 'Y';
     END IF;
  ELSIF sysparm_value in ('Y','YES','T','TRUE') THEN
     incl_invalid_b := 'Y';
  ELSE
     incl_invalid_b := 'N';
  END IF;

  IF pol_sysparm_value = 'PROFILE' THEN
     IF user_profile = 'N' THEN
        incl_invalid_pol_b := 'N';
     ELSE
        incl_invalid_pol_b := 'Y';
     END IF;
  ELSIF pol_sysparm_value in ('Y','YES','T','TRUE') THEN
     incl_invalid_pol_b := 'Y';
  ELSE
     incl_invalid_pol_b := 'N';
  END IF;

  -- We will call the verification function several times so piece together its call
  verify_func := ' pm_coverage_verification(coverage_pk, entity_insured_fk, role_type_code, ' ||
                   '''' || i_loss_date      || ''', ' ||
                   '''' || i_inc_start_date || ''', ' ||
                   '''' || i_inc_end_date   || ''', ' ||
                   '''' || i_report_date    || ''', ' ||
                   '''' || i_loss_state     || ''', ' ||
                   'term_base_record_fk' || ')';
  cm_verify_func := ' cm_coverage_verification(cm_coverage_pk, entity_insured_fk,  ' ||
                   '''' || i_loss_date      || ''', ' ||
                   '''' || i_inc_start_date || ''', ' ||
                   '''' || i_inc_end_date   || ''', ' ||
                   '''' || i_report_date    || ''', ' ||
                   '''' || i_loss_state     || '''' || ')';

  v_sql :=          'SELECT /*+ rule */  policy_fk, policy_no, coverage_pk, product_coverage_code, policy_form_code, ';
  v_sql := v_sql ||       ' risk_base_record_fk, coverage_base_record_fk, limit_descr coverage_limit_code, role_type_code, ';
  v_sql := v_sql ||       ' pth_effective_from_date effective_from_date, pth_effective_to_date effective_to_date, accounting_from_date, ';
  v_sql := v_sql ||       ' accounting_to_date, covg_descr coverage_desc, prod_descr product_coverage_desc, policy_term_history_pk, risk_pk risk_fk, ';
  v_sql := v_sql ||       ' form_descr, retroactive_date retro_date, policy_source, risk_descr, ';
  v_sql := v_sql ||       ' substr(' || verify_func || ',';
  v_sql := v_sql ||              ' 1, instr(' || verify_func || ',''^'') - 1) coverage_valid, ';
  v_sql := v_sql ||       ' substr(' || verify_func || ',';
  v_sql := v_sql ||              ' instr(' || verify_func || ',''^'') + 1) invalid_reason, ';
  v_sql := v_sql ||       ' '''' composite_view, specialty_code, county_code_used_to_rate, county_state_used_to_rate, state_code_used_to_rate, ';
  v_sql := v_sql ||       ' policyholder_name, manuscript_endorsement_b,policy_cycle_code, policy_type_code policy_type, covg_type, product_cov_verify_pk, ''N'' selected_b, ''N'' flag_b, to_number('''') exp_res, to_number('''') ind_res, to_number('''') temp_covg_xref_pk, ';
  v_sql := v_sql ||       ' addl_info1, addl_info2, addl_info3, addl_info4, addl_info5, addl_info6, addl_info7, addl_info8, ';
  v_sql := v_sql ||       ' addl_info9, addl_info10, addl_info11, addl_info12, addl_info13, addl_info14, addl_info15, addl_info16, ';
  v_sql := v_sql ||       ' addl_info17, addl_info18, addl_info19, addl_info20, addl_info21, addl_info22, addl_info23, addl_info24, addl_info25 ';
  v_sql := v_sql ||  ' FROM cm_pm_interface ';
  v_sql := v_sql || ' WHERE entity_insured_fk = ' || to_char(i_insured_fk);

  -- Call our coverage verification function
  --IF Upper(nvl(cs_get_system_parameter('CM_INC_INVALID_CVG'), 'N')) not in ('Y', 'YES', 'T', 'TRUE') THEN
  --  v_sql := v_sql ||    ' AND substr(' || verify_func || ',';
  --  v_sql := v_sql ||                 ' 1, instr(' || verify_func || ',''^'') - 1) = ''TRUE'' ';
  --END IF;

  -- Do we include quotes or not ( not by default )
  IF Upper(nvl(cs_get_system_parameter('CM_INCLUDE_QUOTES'), 'N')) in ('N','NO','F','FALSE') THEN
     v_sql := v_sql ||    ' AND policy_cycle_code = ''POLICY'' ';
  END IF;

  -- this code takes care of any restrictions on coverage based upon the claim's loss type
  v_sql := v_sql ||   ' AND ( EXISTS ';
  v_sql := v_sql ||        ' (SELECT 0 ';
  v_sql := v_sql ||           ' FROM cm_lob_filter ';
  v_sql := v_sql ||          ' WHERE filter_table_name = ''PM_POL_LOB_CODE'' ';
  v_sql := v_sql ||            ' AND cm_pm_interface.pol_lob = filter_code ';
  v_sql := v_sql ||            ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||        ' OR NOT EXISTS ';
  v_sql := v_sql ||           ' (SELECT 0 ';
  v_sql := v_sql ||              ' FROM cm_lob_filter ';
  v_sql := v_sql ||             ' WHERE filter_table_name = ''PM_POL_LOB_CODE'' ';
  v_sql := v_sql ||               ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||       ' )';
  v_sql := v_sql ||   ' AND ( EXISTS ';
  v_sql := v_sql ||        ' (SELECT 0 ';
  v_sql := v_sql ||           ' FROM cm_lob_filter ';
  v_sql := v_sql ||          ' WHERE filter_table_name = ''COVERAGE_CODE'' ';
  v_sql := v_sql ||            ' AND (   cm_pm_interface.cvg_lob = filter_code ';
  v_sql := v_sql ||                 ' OR cm_pm_interface.cvg_lob IN ';
  v_sql := v_sql ||                    ' (SELECT code ';
  v_sql := v_sql ||                       ' FROM product_cov_verify ';
  v_sql := v_sql ||                      ' WHERE coverage_code = filter_code)) ';
  v_sql := v_sql ||            ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||        ' OR NOT EXISTS ';
  v_sql := v_sql ||           ' (SELECT 0 ';
  v_sql := v_sql ||              ' FROM cm_lob_filter ';
  v_sql := v_sql ||             ' WHERE filter_table_name = ''COVERAGE_CODE'' ';
  v_sql := v_sql ||               ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||       ' )';

  IF Upper(cs_get_system_parameter('CM_CGVR_INC_SBLCVG')) in ('Y','YES','T','TRUE') THEN
    IF length(i_coverage_type) > 0 THEN
      v_sql := v_sql ||   ' AND INSTR(NVL(cm_pm_interface.sub_lob_code_list,'',' || i_coverage_type || ',''),'',' || i_coverage_type || ','')>0';
    END IF;
  END IF;



  IF Upper(nvl(cs_get_system_parameter('CM_CVGVER_ALL_RANGE'),'N')) in ('Y', 'YES', 'T', 'TRUE') THEN
      v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), covg_effective_from_date) <= ';
      v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), covg_effective_to_date - 1/86400) ';
      v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), role_effective_from_date) <= ';
      v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), role_effective_to_date- 1/86400) ';
      v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), risk_effective_from_date) <= ';
      v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), risk_effective_to_date- 1/86400) ';
      v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), pth_effective_from_date) <= ';
      v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), pth_effective_to_date- 1/86400) ';
  END IF;

  -- Determine type of coverages to display - parent coverage only, coverage class
  -- or everything
  v_pm_covg_type := NVL(Cs_Get_System_Parameter('CM_CVGVER_COVG_TYPE'),'ALL');
  IF v_pm_covg_type = 'COVG' THEN
      v_sql := v_sql ||   ' AND covg_type IN (''COVG'',''COVGWCLASS'') ';
  ELSIF v_pm_covg_type = 'CLASS' THEN
      v_sql := v_sql ||   ' AND covg_type IN (''COVG'',''COVGCLASS'') ';
  END IF;

  IF incl_invalid_pol_b = 'N' THEN
     v_sql := v_sql ||    ' AND substr(' || verify_func || ',';
     v_sql := v_sql ||                 ' 1, instr(' || verify_func || ',''^'') - 1) = ''TRUE'' ';
     IF  Upper(nvl(cs_get_system_parameter('CM_CVGVER_INC_RANGE'),'N')) in ('N','NO','F','FALSE') THEN
        -- Only use the loss date to find coverages
          v_sql := v_sql ||    ' AND ((policy_form_code = ''OCCURRENCE'' ';
          v_sql := v_sql ||         ' AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN greatest(covg_effective_from_date, role_effective_from_date, risk_effective_from_date, pth_effective_from_date) ';
          v_sql := v_sql ||                   ' AND least(covg_effective_to_date, role_effective_to_date, risk_effective_to_date, pth_effective_to_date) - 1/86400) ';
          v_sql := v_sql ||     ' OR (policy_form_code = ''CM'' ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN greatest(covg_effective_from_date, role_effective_from_date, risk_effective_from_date, pth_effective_from_date) ';
          v_sql := v_sql ||                   ' AND least(covg_effective_to_date, role_effective_to_date, risk_effective_to_date, pth_effective_to_date) - 1/86400 ';
          v_sql := v_sql ||         ' AND retroactive_date <= to_date(''' || i_loss_date || ''',''mm/dd/yyyy'')) ';
          v_sql := v_sql ||     ' OR (coverage_segment_code = ''TAIL'' ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN covg_effective_from_date AND covg_effective_to_date - 1/86400 ';
          v_sql := v_sql ||                   ' AND retroactive_date <= to_date(''' || i_loss_date || ''',''mm/dd/yyyy'')))';
     ELSE
           -- use incident range to find coverages
          v_sql := v_sql ||    ' AND ((policy_form_code = ''OCCURRENCE'' ';
          v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), covg_effective_from_date) <= ';
          v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), covg_effective_to_date - 1/86400) ';
          v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), role_effective_from_date) <= ';
          v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), role_effective_to_date- 1/86400) ';
          v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), risk_effective_from_date) <= ';
          v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), risk_effective_to_date- 1/86400) ';
          v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), pth_effective_from_date) <= ';
          v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), pth_effective_to_date- 1/86400) )';
          v_sql := v_sql ||     ' OR (policy_form_code = ''CM'' ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN covg_effective_from_date AND covg_effective_to_date - 1/86400 ';
          v_sql := v_sql ||         ' AND retroactive_date <= to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN role_effective_from_date AND role_effective_to_date - 1/86400 ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN risk_effective_from_date AND risk_effective_to_date - 1/86400 ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN pth_effective_from_date AND pth_effective_to_date - 1/86400 )';
          v_sql := v_sql ||     ' OR (coverage_segment_code = ''TAIL'' ';
          v_sql := v_sql ||         ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN covg_effective_from_date AND covg_effective_to_date - 1/86400 ';
          v_sql := v_sql ||         ' AND retroactive_date <= to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy'') ))';
     END IF;

     -- extra condition for TAIL coverage
     IF pm_used_b = 'Y' THEN
          v_sql := v_sql ||    ' AND (nvl(coverage_segment_code, ''x'') <> ''TAIL'' ';
          v_sql := v_sql ||         ' OR (to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') >= retroactive_date ';
          v_sql := v_sql ||             ' AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') < pm_report.get_covg_exp_date(coverage_pk))) ';
     ELSE
          v_sql := v_sql ||    ' AND (nvl(coverage_segment_code, ''x'') <> ''TAIL'' ';
          v_sql := v_sql ||         ' OR (to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') >= retroactive_date ';
          v_sql := v_sql ||             ' AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') < pth_effective_to_date)) ';
     END IF;

     -- extra condition for report date and its relation to policy term history effective from and to dates
     IF Upper(cs_get_system_parameter('CM_RPT_PTH_DATES')) in ('Y','YES','T','TRUE') THEN
          v_sql := v_sql ||    ' AND (to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||           ' BETWEEN pth_effective_from_date AND pth_effective_to_date ';
          v_sql := v_sql ||         ' OR (NOT EXISTS ';
          v_sql := v_sql ||             ' (SELECT 0 ';
          v_sql := v_sql ||                ' FROM cm_pm_interface cpi2 ';
          v_sql := v_sql ||               ' WHERE cpi2.entity_insured_fk = ' || to_char(i_insured_fk);
          v_sql := v_sql ||                 ' AND cpi2.policy_pk = cm_pm_interface.policy_pk ';
          v_sql := v_sql ||                 ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||                       ' BETWEEN cpi2.pth_effective_from_date AND cpi2.pth_effective_to_date) ';
          v_sql := v_sql ||             ' AND NOT EXISTS ';
          v_sql := v_sql ||                 ' (SELECT 0 ';
          v_sql := v_sql ||                    ' FROM cm_pm_interface cpi2 ';
          v_sql := v_sql ||                   ' WHERE cpi2.entity_insured_fk = ' || to_char(i_insured_fk);
          v_sql := v_sql ||                     ' AND cpi2.policy_pk = cm_pm_interface.policy_pk ';
          v_sql := v_sql ||                     ' AND ABS(to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||                             ' - cpi2.pth_effective_from_date + (cpi2.pth_effective_from_date - cpi2.pth_effective_to_date)/2) < ';
          v_sql := v_sql ||                     ' ABS(to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||                             ' - cm_pm_interface.pth_effective_from_date + ';
          v_sql := v_sql ||                             ' (cm_pm_interface.pth_effective_from_date - cm_pm_interface.pth_effective_to_date)/2)))) ';
     END IF;
  END IF;

  -- special logic to deal with unique slot/occupant relationships
  IF Upper(cs_get_system_parameter('CM_CVGVER_SLOT_OCCUP')) IN ('Y','YES','T','TRUE') THEN
    v_sql := v_sql || ' AND ( (occupant_coverage_pk IS NULL) ';
    v_sql := v_sql || '  OR   (occupant_coverage_pk IS NOT NULL ';
    v_sql := v_sql || '        AND EXISTS (SELECT 1 FROM Coverage c_occup1 ';
    v_sql := v_sql || '                     WHERE c_occup1.coverage_pk = occupant_coverage_pk ';
    v_sql := v_sql || '                       AND ( (to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') ';
    v_sql := v_sql || '                           BETWEEN c_occup1.effective_from_date AND c_occup1.effective_to_date - 1/86400) ';
    v_sql := v_sql || '                        OR   (to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') < c_occup1.effective_from_date ';
    v_sql := v_sql || '                              AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') >= retroactive_date) ) ';
    v_sql := v_sql || '                       AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') >= retroactive_date) ) ) ';
  END IF;

  -- append any additional SQL for coverages from cm_pm_interface
  addl_sql := cs_get_system_parameter('CM_ADDLSQL4PMCVGVER1') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4PMCVGVER2') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4PMCVGVER3') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4PMCVGVER4') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4PMCVGVER5');

  v_sql := v_sql || addl_sql;

  v_sql := v_sql || ' UNION ALL ';
  v_sql := v_sql || 'SELECT /*+ rule */  p.cm_policy_pk,p.POLICY_NO,c.cm_coverage_pk,'' '',c.POLICY_FORM,0,0,';
  v_sql := v_sql ||       ' ltrim(to_char(c.inc_limit,''$999,999,990'')) || ''/'' || ltrim(to_char(c.agg_limit,''$999,999,990'')) , '' '',';

  -- we may show policy dates or coverages dates.
  IF cs_char2bool(cs_get_system_parameter('CM_CVGLST_USE_CVGDTS')) = 'Y' THEN
    v_sql := v_sql ||       ' c.effective_from_date,c.effective_to_date,sysdate,sysdate,';
  ELSE
    v_sql := v_sql ||       ' p.policy_start_date,p.policy_end_date,sysdate,sysdate,';
  END IF;

  -- there are a couple different things we can show for coverage description
  IF cs_char2bool(cs_get_system_parameter('CM_CVGLST_USE_CVGDSC')) = 'Y' THEN
    v_sql := v_sql ||       ' c.coverage_description,';
  ELSE
    v_sql := v_sql ||       ' cvg.long_description,';
  END IF;

  v_sql := v_sql ||       ' lob.long_description,0,r.cm_risk_pk,lc.short_description,c.retro_date,''CLAIM_POLICY'', ';
  v_sql := v_sql ||       ' nvl(r.risk_description,cs_get_client_name(r.entity_fk)) risk_descr,  ' ;
  v_sql := v_sql ||       ' substr(' || cm_verify_func || ',';
  v_sql := v_sql ||              ' 1, instr(' || cm_verify_func || ',''^'') - 1) coverage_valid, ';
  v_sql := v_sql ||       ' substr(' || cm_verify_func || ',';
  v_sql := v_sql ||              ' instr(' || cm_verify_func || ',''^'') + 1) invalid_reason, ';
  IF Upper(nvl(cs_get_system_parameter('CM_USE_COMPST_VIEW'),'N')) in ('Y','YES','T','TRUE') THEN
     v_sql := v_sql ||       ' cm_coverage_composite_view( ' || to_char(i_insured_fk) || ', ' || '''' || i_loss_date || '''' || ', ' || '''STR'') , ';
  ELSE
      v_sql := v_sql ||              ' '''', ';
  END IF;
  v_sql := v_sql ||       ' r.risk_spec_code, '''', '''', '''', cs_get_client_name(p.entity_insured_fk), '''', '''', p.policy_type policy_type, cvg.long_description, to_number(''''), ''N'' selected_b, ''N'' flag_b, to_number('''') exp_res, to_number('''') ind_res, to_number('''') temp_covg_xref_pk, ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL1''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL1''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL2''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL2''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL3''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL3''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL4''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL4''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL5''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL5''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL6''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL6''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL7''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL7''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL8''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL8''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL9''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL9''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL10''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL10''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL11''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL11''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL12''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL12''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL13''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL13''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL14''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL14''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL15''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL15''))), ';
  v_sql := v_sql ||		  ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL16''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL16''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL17''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL17''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL18''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL18''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL19''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL19''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL20''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL20''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL21''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL21''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL22''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL22''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL23''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL23''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL24''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL24''))), ';
  v_sql := v_sql ||       ' decode(cs_get_system_parameter(''CM_CLMCVG_ADDL25''), '''' , '''', cm_addl_info.coverage_info(c.cm_coverage_pk, cs_get_system_parameter(''CM_CLMCVG_ADDL25''))) ';

  v_sql := v_sql ||  ' FROM cm_policy p, cm_risk r, cm_coverage c, lookup_code lc, cm_pol_lob_code lob, cm_pol_coverage_type cvg ';
  v_sql := v_sql || ' WHERE c.cm_lob_code = lob.cm_pol_lob_code(+) AND p.cm_policy_pk = r.cm_policy_fk ';
  v_sql := v_sql ||   ' AND r.cm_risk_pk = c.cm_risk_fk AND c.cm_coverage_type_code = cvg.cm_pol_coverage_type_code(+) ';
  v_sql := v_sql ||   ' AND (instr('','' || p.state_code || '','', '',' || i_loss_state || ','') > 0 ';
  v_sql := v_sql ||        ' OR nvl(cvg.state_verification_b, nvl(lob.state_verification_b, ''N'')) = ''N'') ';

   -- when we compare dates there are two system parameters that we have to be concerned with
  --    1) CM_CVGVER_INC_RANGE indicates if we should use the incident range rather than the loss date
  --    2) CM_CVGVER_RSKCVG_DT indicates if the effective dates on risk and coverage should be used
  -- populate a couple variables depending on sysparm so we don't have to keep repeating the same phrase.
  IF Upper(cs_get_system_parameter('CM_CVGVER_RSKCVG_DT')) in ('N','NO','F','FALSE') THEN
     eff_from_dt := ' p.policy_start_date ';
     eff_to_dt := ' p.policy_end_date ';
  ELSE
     eff_from_dt := ' nvl(c.effective_from_date, nvl(r.effective_from_date, p.policy_start_date)) ';
     eff_to_dt := ' nvl(c.effective_to_date, nvl(r.effective_to_date, p.policy_end_date)) ';
  END IF;

   IF cs_char2bool(cs_get_system_parameter('CM_CVGVER_RISK_ONLY')) = 'Y' THEN
   -- we only look in the CM_RISK to find our policies for the insured pass into here.
     v_sql := v_sql ||   ' AND r.cm_risk_pk IN ';
     v_sql := v_sql ||       ' (SELECT cm_risk_pk FROM cm_risk ';
     v_sql := v_sql ||         ' WHERE entity_fk =' || to_char(i_insured_fk);
  ELSE
  -- we have to look both in the CM_RISK and CM_POLICY tables to find our policies for the insured pass into here.
     v_sql := v_sql ||   ' AND r.cm_risk_pk IN ';
     v_sql := v_sql ||       ' (SELECT cm_risk_pk FROM cm_risk ';
     v_sql := v_sql ||         ' WHERE entity_fk =' || to_char(i_insured_fk);
     v_sql := v_sql ||         ' UNION ALL ';
     v_sql := v_sql ||        ' SELECT cm_risk_pk FROM cm_risk, cm_policy ';
     v_sql := v_sql ||         ' WHERE cm_policy_fk = cm_policy_pk ';
     v_sql := v_sql ||           ' AND entity_insured_fk = ' || to_char(i_insured_fk);
  END IF;

  -- add an additional select if we have to find historical policy holders.  This is pretty client specific stuff.
  -- use ENTITY_ROLE to select historical policyholder information - HPOLHOLDER
  IF cs_get_system_parameter('CM_HPOLHLDR_POL_POS') is not null THEN
     v_sql := v_sql ||         ' UNION ALL ';
     v_sql := v_sql ||        ' SELECT cm_risk_pk FROM cm_risk, cm_policy, entity_role er ';
     v_sql := v_sql ||         ' WHERE er.role_type_code = ''HPOLHOLDER'' ';
     v_sql := v_sql ||           ' AND er.accounting_to_date = to_date(''01/01/3000'',''mm/dd/yyyy'') ';
     v_sql := v_sql ||           ' AND substr(policy_no, 1, length(policy_no) - to_number(nvl(cs_get_system_parameter(''CM_HPOLHLDR_POL_POS''), ''0''))) = er.external_id ';
     v_sql := v_sql ||           ' AND cm_policy_fk = cm_policy_pk ';
     v_sql := v_sql ||           ' AND er.entity_fk =' || to_char(i_insured_fk);
  END IF;

  -- add an additional select if we have to find entity haveng relation of type ADDNMINS to the insured.  This is pretty client specific stuff.
  IF cs_char2bool(cs_get_system_parameter('CM_CVGVER_CIS_ADDRSK')) = 'Y' THEN
     v_sql := v_sql ||         ' UNION ALL ';
     v_sql := v_sql ||        ' SELECT r.cm_risk_pk FROM entity_relation er, cm_risk r ';
     v_sql := v_sql ||         ' WHERE er.entity_parent_fk = r.entity_fk ';
     v_sql := v_sql ||           ' AND er.relation_type_code = ''ADDNMINS'' ';
     v_sql := v_sql ||           ' AND (er.accounting_to_date is null or er.accounting_to_date = to_date(''01/01/3000'',''mm/dd/yyyy'')) ';
     IF  Upper(nvl(cs_get_system_parameter('CM_CVGVER_INC_RANGE'),'N')) in ('N','NO','F','FALSE') THEN
         v_sql := v_sql ||           ' AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'')';
         v_sql := v_sql ||           '     BETWEEN TRUNC(er.effective_from_date) and TRUNC(er.effective_to_date)';
     ELSE
         v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), TRUNC(er.effective_from_date) ) <= ';
         v_sql := v_sql ||            ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), TRUNC(er.effective_to_date) - 1/86400) ';
     END IF;
     v_sql := v_sql ||         ' AND greatest( '||eff_from_dt ||', TRUNC(er.effective_from_date) ) <= ';
     v_sql := v_sql ||            ' least('||eff_to_dt ||', TRUNC(er.effective_to_date) - 1/86400) ';
     v_sql := v_sql ||           ' AND er.entity_child_fk =' || to_char(i_insured_fk);
  END IF;

  -- close out the sub-query with a right paren.
  v_sql := v_sql ||        ' ) ';

  -- if we are doing entity via entity_relation of ADDNMINS we did a correlated sub-query above, but that is really
  -- slow.  So here we do pretty much the same query, but uncorredlated which Oracle will do first to whittle down
  -- the number of rows that go through the painful coorelated sub-query above.  This is basically a cut and paste of
  -- the code above.
  IF cs_char2bool(cs_get_system_parameter('CM_CVGVER_CIS_ADDRSK')) = 'Y' THEN
      IF cs_char2bool(cs_get_system_parameter('CM_CVGVER_RISK_ONLY')) = 'Y' THEN
      -- we only look in the CM_RISK to find our policies for the insured pass into here.
        v_sql := v_sql ||   ' AND r.cm_risk_pk IN ';
        v_sql := v_sql ||       ' (SELECT cm_risk_pk FROM cm_risk ';
        v_sql := v_sql ||         ' WHERE entity_fk =' || to_char(i_insured_fk);
     ELSE
     -- we have to look both in the CM_RISK and CM_POLICY tables to find our policies for the insured pass into here.
        v_sql := v_sql ||   ' AND r.cm_risk_pk IN ';
        v_sql := v_sql ||       ' (SELECT cm_risk_pk FROM cm_risk ';
        v_sql := v_sql ||         ' WHERE entity_fk =' || to_char(i_insured_fk);
        v_sql := v_sql ||         ' UNION ALL ';
        v_sql := v_sql ||        ' SELECT cm_risk_pk FROM cm_risk, cm_policy ';
        v_sql := v_sql ||         ' WHERE cm_policy_fk = cm_policy_pk ';
        v_sql := v_sql ||           ' AND entity_insured_fk = ' || to_char(i_insured_fk);
     END IF;

     -- add an additional select if we have to find historical policy holders.  This is pretty client specific stuff.
     -- use ENTITY_ROLE to select historical policyholder information - HPOLHOLDER
     IF cs_get_system_parameter('CM_HPOLHLDR_POL_POS') is not null THEN
        v_sql := v_sql ||         ' UNION ALL ';
        v_sql := v_sql ||        ' SELECT cm_risk_pk FROM cm_risk, cm_policy, entity_role er ';
        v_sql := v_sql ||         ' WHERE er.role_type_code = ''HPOLHOLDER'' ';
        v_sql := v_sql ||           ' AND er.accounting_to_date = to_date(''01/01/3000'',''mm/dd/yyyy'') ';
        v_sql := v_sql ||           ' AND substr(policy_no, 1, length(policy_no) - to_number(nvl(cs_get_system_parameter(''CM_HPOLHLDR_POL_POS''), ''0''))) = er.external_id ';
        v_sql := v_sql ||           ' AND cm_policy_fk = cm_policy_pk ';
        v_sql := v_sql ||           ' AND er.entity_fk =' || to_char(i_insured_fk);
     END IF;

     -- add an additional select if we have to find entity haveng relation of type ADDNMINS to the insured.  This is pretty client specific stuff.
     IF cs_char2bool(cs_get_system_parameter('CM_CVGVER_CIS_ADDRSK')) = 'Y' THEN
        v_sql := v_sql ||         ' UNION ALL ';
        v_sql := v_sql ||        ' SELECT r.cm_risk_pk FROM entity_relation er, cm_risk r ';
        v_sql := v_sql ||         ' WHERE er.entity_parent_fk = r.entity_fk ';
        v_sql := v_sql ||           ' AND er.relation_type_code = ''ADDNMINS'' ';
        v_sql := v_sql ||           ' AND (er.accounting_to_date is null or er.accounting_to_date = to_date(''01/01/3000'',''mm/dd/yyyy'')) ';
        IF  Upper(nvl(cs_get_system_parameter('CM_CVGVER_INC_RANGE'),'N')) in ('N','NO','F','FALSE') THEN
            v_sql := v_sql ||           ' AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'')';
            v_sql := v_sql ||           '     BETWEEN TRUNC(er.effective_from_date) and TRUNC(er.effective_to_date)';
        ELSE
            v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), TRUNC(er.effective_from_date) ) <= ';
            v_sql := v_sql ||            ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), TRUNC(er.effective_to_date) - 1/86400) ';
        END IF;
        v_sql := v_sql ||           ' AND er.entity_child_fk =' || to_char(i_insured_fk);
     END IF;

     -- close out the sub-query with a right paren.
     v_sql := v_sql ||        ' ) ';

  END IF;

  v_sql := v_sql ||   ' AND c.policy_form = lc.code(+) ';
  v_sql := v_sql ||   ' AND lc.lookup_type_code(+) = ''POLICY_FORM_CODE''';

  -- Compare LOB if system is set up to do so
  -- this probably makes no more sense since we have split the claim LOB (renamed loss type)
  -- and the policy LOB, but keep it around for awhile  CJM 8/31/98
  IF Upper(cs_get_system_parameter('CM_CVGVER_INC_LOB')) in ('Y','YES','T','TRUE') THEN
    IF length(i_lob) >  0  THEN
      v_sql := v_sql ||   ' AND c.cm_lob_code = ''' || i_lob || ''' ';
    END IF;
  END IF;

  -- Compare coverage if system is set up to do so
  -- this probably makes no more sense since we have split the claim LOB (renamed loss type)
  -- and the policy LOB, but keep it around for awhile  CJM 8/31/98
  IF Upper(cs_get_system_parameter('CM_CVGVER_INC_CVG')) in ('Y','YES','T','TRUE') THEN
    IF length(i_coverage_type) >  0  THEN
      v_sql := v_sql ||   ' AND c.cm_coverage_type_code = ''' || i_coverage_type || ''' ';
    END IF;
  END IF;

  -- this code takes care of any restrictions on coverage based upon the claim's loss type
  v_sql := v_sql ||   ' AND ( EXISTS ';
  v_sql := v_sql ||        ' (SELECT 0 ';
  v_sql := v_sql ||           ' FROM cm_lob_filter ';
  v_sql := v_sql ||          ' WHERE filter_table_name = ''CM_POL_COVERAGE_TYPE'' ';
  v_sql := v_sql ||            ' AND c.cm_coverage_type_code = filter_code ';
  v_sql := v_sql ||            ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||        ' OR NOT EXISTS ';
  v_sql := v_sql ||           ' (SELECT 0 ';
  v_sql := v_sql ||              ' FROM cm_lob_filter ';
  v_sql := v_sql ||             ' WHERE filter_table_name = ''CM_POL_COVERAGE_TYPE'' ';
  v_sql := v_sql ||               ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||       ' )';

-- this code takes care of any restrictions on policy based upon the claim's loss type
   v_sql := v_sql ||   ' AND ( EXISTS ';
  v_sql := v_sql ||        ' (SELECT 0 ';
  v_sql := v_sql ||           ' FROM cm_lob_filter ';
  v_sql := v_sql ||          ' WHERE filter_table_name = ''CM_POL_LOB_CODE'' ';
  v_sql := v_sql ||            ' AND p.lob_code = filter_code ';
  v_sql := v_sql ||            ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||        ' OR NOT EXISTS ';
  v_sql := v_sql ||           ' (SELECT 0 ';
  v_sql := v_sql ||              ' FROM cm_lob_filter ';
  v_sql := v_sql ||             ' WHERE filter_table_name = ''CM_POL_LOB_CODE'' ';
  v_sql := v_sql ||               ' AND cm_lob_code = ''' || i_lob || ''')';
  v_sql := v_sql ||       ' )';

   -- If the user wants to see all coverages that have effective_from/to dates
  -- between a range of dates then the CM_CVGVER_ALL_RANGE system parameter
  -- has to be set to Y
  IF Upper(nvl(cs_get_system_parameter('CM_CVGVER_ALL_RANGE'),'N')) in ('Y', 'YES', 'T', 'TRUE') THEN
     v_sql := v_sql ||   ' AND (';
     v_sql := v_sql ||         ' (greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), ' || eff_from_dt || ' ) <= ';
     v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), ' || eff_to_dt || ' - 1/86400)) ';
     v_sql := v_sql ||             ' OR ( to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
     v_sql := v_sql ||                   ' BETWEEN ' || eff_from_dt || ' AND ' || eff_to_dt || ' - 1/86400 ';
     v_sql := v_sql ||                   ' AND c.retro_date <= to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''))) ';
  END IF;

  IF incl_invalid_b = 'N' THEN
     v_sql := v_sql ||    ' AND substr(' || cm_verify_func || ',';
     v_sql := v_sql ||                 ' 1, instr(' || cm_verify_func || ',''^'') - 1) = ''TRUE'' ';
     v_sql := v_sql ||   ' AND (';
     IF  Upper(nvl(cs_get_system_parameter('CM_CVGVER_INC_RANGE'),'N')) in ('N','NO','F','FALSE') THEN
          v_sql := v_sql ||        ' (c.policy_form = ''OCCURRENCE'' ';
          v_sql := v_sql ||         ' AND to_date(''' || i_loss_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||               ' BETWEEN ' || eff_from_dt || ' AND ' || eff_to_dt || ' - 1/86400 )';
          v_sql := v_sql ||        ' OR (c.policy_form = ''CM'' ';
          v_sql := v_sql ||            ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||                  ' BETWEEN ' || eff_from_dt || ' AND' || eff_to_dt || ' - 1/86400 ';
          v_sql := v_sql ||            ' AND c.retro_date <= to_date(''' || i_loss_date || ''',''mm/dd/yyyy'')) ) ';
     ELSE
          v_sql := v_sql ||        ' (c.policy_form = ''OCCURRENCE'' ';
          v_sql := v_sql ||         ' AND greatest(to_date(''' || i_inc_start_date || ''',''mm/dd/yyyy''), ' || eff_from_dt || ' ) <= ';
          v_sql := v_sql ||             ' least(to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy''), ' || eff_to_dt || ' - 1/86400)) ';
          v_sql := v_sql ||        ' OR (c.policy_form = ''CM'' ';
          v_sql := v_sql ||            ' AND to_date(''' || i_report_date || ''',''mm/dd/yyyy'') ';
          v_sql := v_sql ||                  ' BETWEEN ' || eff_from_dt || ' AND ' || eff_to_dt || ' - 1/86400 ';
          v_sql := v_sql ||            ' AND c.retro_date <= to_date(''' || i_inc_end_date || ''',''mm/dd/yyyy'')) ) ';
     END IF;
     -- Add tail logic
     v_sql := v_sql ||   ' AND (nvl(c.tail_b,''N'') = ''N'' ';
     v_sql := v_sql ||        ' OR ' || eff_from_dt || ' > to_date(''' || i_loss_date || ''',''mm/dd/yyyy'')) ';
  END IF;

-- append any additional SQL for claim's proprietary coverages
  addl_sql := cs_get_system_parameter('CM_ADDLSQL4CVGVER1') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4CVGVER2') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4CVGVER3') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4CVGVER4') || ' ' ||
              cs_get_system_parameter('CM_ADDLSQL4CVGVER5');

  v_sql := v_sql || addl_sql;
  return v_sql;
END cm_find_claim_coverage_sql;