SQL & PL/SQL

Hi all

I am uising with clause in SQL and getting this error. Please help ORA-24374: define not done before fetch or execute and fetch

with dea as
(SELECT MAX (idnt_value_cd) dea_num_cd,
MAX (xtl_bpa_idnt_value_eff_dt)dea_eff_dt,
MAX (idnt_value_cd_term_dt)dea_exp_dt,bpa_id
FROM xtl_bpa_idnt_value
WHERE xtl_bpa_idnt_id = 1
AND merci_util.is_date_range_active
(xtl_bpa_idnt_value_eff_dt,
idnt_value_cd_term_dt
) = 0
GROUP BY bpa_id)
SELECT 'IPLAN' src_sys_name, bp.bp_id src_bp_id, 'INDV' bp_type_cd,
CAST (NULL AS VARCHAR2 (10)) src_bp_clsfn_cd,
CAST (NULL AS VARCHAR2 (10)) src_bp_sub_clsfn_cd,
CAST (NULL AS VARCHAR2 (100)) bp_name, hcp.first_nm first_nm,
hcp.middle_nm midl_nm, hcp.last_nm last_nm,
hcp.name_pfx_dcd salu_txt, hcp.name_sufx_dcd sfx_txt,
birth_yr_cd || birth_mth_cd || birth_day_cd birth_dt,
gender_dcd gndr_cd, mpro_type.mpro_cd src_pfsnl_dgntn_cd,
(SELECT spty_nm
FROM specialty
WHERE spty_id =
(SELECT spty_id
FROM bp_specialty
WHERE bp_id = bp.bp_id
AND bp_specialty_id =
(SELECT MAX (bp_specialty_id)
FROM bp_specialty
WHERE bp_id = bp.bp_id)))
src_prim_mdcl_spty_cd,
(SELECT spty_nm
FROM specialty
WHERE spty_id =
(SELECT spty_id
FROM bp_specialty
WHERE bp_id = bp.bp_id
AND bp_specialty_id =
(SELECT MAX (bp_specialty_id) - 1
FROM bp_specialty
WHERE bp_id = bp.bp_id)))
src_sec_mdcl_spty_cd,
(SELECT spty_nm
FROM specialty
WHERE spty_id =
(SELECT spty_id
FROM bp_specialty
WHERE bp_id = bp.bp_id
AND bp_specialty_id =
(SELECT MAX (bp_specialty_id) - 2
FROM bp_specialty
WHERE bp_id = bp.bp_id)))
src_tert_mdcl_spty_cd,
lic.lic_num_cd src_st_lic_num, vhcp.state_cd src_sln_state_cd,
lic.lic_eff_dt src_st_lic_eff_dt,
lic.lic_expr_dt src_st_lic_exprn_dt,
bp_status.bp_status_type_dcd src_bp_actv_ind,
bp_status.bp_status_eff_dt src_bp_sta_chg_dt,
mpro_type.mpro_type_dcd titl_txt,
CAST (NULL AS VARCHAR2 (10)) src_cmeh_bp_id,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 6)
callmax_cust_id,
CAST (NULL AS VARCHAR2 (10)) wk_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 26) ims_psbr_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 22) ama_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 3) aoa_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 21) ada_num,
CAST (NULL AS VARCHAR2 (10)) vet_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 23) np_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 20) pa_num,
CAST (NULL AS VARCHAR2 (10)) pod_num,
CAST (NULL AS VARCHAR2 (10)) opt_num,
(SELECT xtl_bp_idnt_value_cd
FROM xtl_bp_idnt_value
WHERE bp_id = vhcp.hcp_id AND xtl_bp_idnt_id = 7) tax_id,
CAST (NULL AS VARCHAR2 (10)) hin_num,
CAST (NULL AS VARCHAR2 (10)) npi_num,
vhcp.phone_num_cd bp_off_phn_num, vhcp.fax_num_cd bp_fax_num,
CAST (NULL AS VARCHAR2 (10)) bp_cell_phn_num,
CAST (NULL AS VARCHAR2 (10)) bp_pager_num,
CAST (NULL AS VARCHAR2 (10)) bp_home_phn_num,
CAST (NULL AS VARCHAR2 (10)) bp_vmail_num,
vhcp.e_mail_cd bp_email_addr_txt,
CAST (NULL AS VARCHAR2 (10)) bp_url, vhcp.bpa_id src_bpa_id,
vhcp.addr_1_ds addr_ln_1_txt, addr_2_ds addr_ln_2_txt,
CAST (NULL AS VARCHAR2 (10)) addr_ln_3_txt,
CAST (NULL AS VARCHAR2 (10)) addr_ln_4_txt, city_nm,
state_nm state_cd, postal_cd zip_5,
CAST (NULL AS VARCHAR2 (10)) zip_4,
vhcp.pfrd_ctac_loc_ind pfr_locn_ind,
DECODE
(merci_util.is_date_range_active ((SELECT bp_address.eff_dt
FROM bp_address
WHERE bpa_id = vhcp.bpa_id),
(SELECT bp_address.end_dt
FROM bp_address
WHERE bpa_id = vhcp.bpa_id)
),
0, 'ACTIVE',
'INACTIVE'
) src_bpa_actv_ind,
TO_CHAR (vhcp.bpa_updt_dtm, ' YYYYMMDD') src_bpa_sta_chg_dt,
vhcp.prac_loc_ind bpa_off_addr_ind,
vhcp.ship_to_loc_ind bpa_ship_addr_ind,
vhcp.pfrd_fncl_loc_ind bpa_bill_addr_ind,
(SELECT bp_address.mail_loc_ind
FROM bp_address
WHERE bpa_id = vhcp.bpa_id) bpa_mail_addr_ind,
CAST (NULL AS VARCHAR2 (10)) bpa_sm_addr_ind,
(SELECT bp_address.home_loc_ind
FROM bp_address
WHERE bpa_id = vhcp.bpa_id) bpa_home_addr_ind,
CAST (NULL AS VARCHAR2 (10)) bpa_hdqtr_addr_ind,
vhcp.affiliation_ind bpa_affl_addr_ind,
CAST (NULL AS VARCHAR2 (10)) bpa_prov_addr_ind,
CAST (NULL AS VARCHAR2 (10)) bpa_other_addr_ind,
dea.dea_num_cd,
dea.dea_eff_dt,
dea.dea_exp_dt,
CAST (NULL AS VARCHAR2 (10)) schd_clas_cd,
(SELECT MAX (idnt_value_cd) affl_id_cd
FROM xtl_bpa_idnt_value
WHERE 1 = 1
AND xtl_bpa_idnt_id = 3
AND merci_util.is_date_range_active
(xtl_bpa_idnt_value_eff_dt,
idnt_value_cd_term_dt
) = 0
AND xtl_bpa_idnt_value.bpa_id = vhcp.bpa_id
GROUP BY bpa_id) ims_outlet_num,
(SELECT MAX (idnt_value_cd) affl_id_cd
FROM xtl_bpa_idnt_value
WHERE 1 = 1
AND xtl_bpa_idnt_id = 6
AND merci_util.is_date_range_active
(xtl_bpa_idnt_value_eff_dt,
idnt_value_cd_term_dt
) = 0
AND xtl_bpa_idnt_value.bpa_id = vhcp.bpa_id
GROUP BY bpa_id) callmax_afln_id,
CAST (NULL AS VARCHAR2 (10)) src_cmeh_bpa_id,
vhcp.bpa_id src_addr_id, vhcp.phone_num_cd bpa_off_num,
vhcp.fax_num_cd bpa_fax_num,
CAST (NULL AS VARCHAR2 (10)) bpa_cell_phn_num,
CAST (NULL AS VARCHAR2 (10)) bpa_pager_num,
CAST (NULL AS VARCHAR2 (10)) bpa_home_phn_num,
CAST (NULL AS VARCHAR2 (10)) bpa_vmail_num,
vhcp.e_mail_cd bpa_email_addr_txt,
CAST (NULL AS VARCHAR2 (10)) bpa_url,
CAST (NULL AS VARCHAR2 (10)) bp_filler_1,
CAST (NULL AS VARCHAR2 (10)) bp_filler_2,
CAST (NULL AS VARCHAR2 (10)) bp_filler_3,
CAST (NULL AS VARCHAR2 (10)) bp_filler_4,
CAST (NULL AS VARCHAR2 (10)) bp_filler_5,
CAST (NULL AS VARCHAR2 (10)) bpa_filler_1,
CAST (NULL AS VARCHAR2 (10)) bpa_filler_2,
CAST (NULL AS VARCHAR2 (10)) bpa_filler_3,
CAST (NULL AS VARCHAR2 (10)) bpa_filler_4,
CAST (NULL AS VARCHAR2 (10)) bpa_filler_5,
max_date (max_date (vhcp.bpa_updt_dtm, vhcp.hcp_updt_dtm),
bp_status.updt_dtm
) updt_dtm,
SYSDATE refresh_dtm
FROM business_party bp,
hcp,
(SELECT hcp_id, lic_num_cd, MIN (lic_eff_dt) lic_eff_dt,
MAX (lic_expr_dt) lic_expr_dt
FROM mpro_govt_org_license
WHERE 1 = 1
AND merci_util.is_date_range_active (lic_eff_dt, lic_expr_dt) =
0
GROUP BY hcp_id, mpro_type_dcd, geoa_id, lic_num_cd) lic,
(SELECT code_type_nm, code_value_cd mpro_type_dcd,
xtl_src_obj_cd mpro_cd
FROM xtl_src_code_value
WHERE 1 = 1 AND code_type_nm = 'MPRO_TYPE') mpro_type,
vm_hcp_address vhcp,
bp_status,
dea
WHERE bp.bp_id = hcp.hcp_id
AND hcp.hcp_id = lic.hcp_id(+)
AND dea.bpa_id(+) = vhcp.bpa_id
AND bp.hcp_ind = 'Y'
AND hcp.mpro_type_1_dcd = mpro_type.mpro_type_dcd(+)
AND hcp.hcp_id = vhcp.hcp_id
AND hcp.srch_ctac_bpa_id = vhcp.bpa_id
AND hcp.hcp_id = bp_status.bp_id
and hcp.hcp_id=2200970