select DISTINCT cli_rid,cli_prefix, cli_first_name, cli_last_name, cli_middle_initial, cli_maiden_name, cli_suffix, mad_address, mad_city, mad_state, mad_zip, mad_zip_plus, mad_city||', '||mad_state||' '||mad_zip||DECODE(mad_zip_plus,NULL,'','-')||mad_zip_plus mad_address2, cli_area_code_1||decode(cli_area_code_1,'','','-')||substr(cli_main_phone,1,3)||decode(cli_main_phone,'','','-')|| substr(cli_main_phone,4,4)||decode(cli_extension_1,'','',' Ext.')||cli_extension_1 cli_Business_Phone, cli_fax_area_code||decode(cli_fax_area_code,'','','-')||substr(cli_fax,1,3)||decode(cli_main_phone,'','','-')||substr(cli_fax,4,4) cli_Fax_Number, cli_area_code_2||decode(cli_area_code_2,'','','-')||substr(cli_second_phone,1,3)||decode(cli_main_phone,'','','-')||substr(cli_second_phone,4,4) cli_Home_Phone, age_agency_company, age_agency_name,age_newagency_name, age_po_address,age_agency_address,age_agency_city ,age_agency_state,age_agency_zip,age_main_phone,age_po_city,age_po_state, age_po_zip,age_zip_plus,age_no_of_employees, age_website, cli_email_address, FLOOR((months_between(sysdate,cli_birthday)/12)) CLI_AGE, cli_birthday,cli_comments, CLI_FIRST_NAME ||' '||DECODE(CLI_MIDDLE_INITIAL,NULL,'',CLI_MIDDLE_INITIAL||' ')||CLI_LAST_NAME CLI_NAME,cli_username,cli_password, substr(cli_ssn,1,3)||'-'||substr(cli_ssn,4,2)||'-'||substr(cli_ssn,6,4) cli_ssn, CLI_PREFIX||DECODE(CLI_PREFIX,NULL,'',' ')||CLI_FIRST_NAME||DECODE(CLI_MIDDLE_INITIAL,NULL,'',' '||CLI_MIDDLE_INITIAL)||' '||CLI_LAST_NAME CLI_PNAME, CLI_PREFIX||DECODE(CLI_PREFIX,NULL,'',' ')||CLI_FIRST_NAME||DECODE(CLI_MIDDLE_INITIAL,NULL,'',' '||CLI_MIDDLE_INITIAL)||' '||CLI_LAST_NAME||DECODE(CLI_SUFFIX,NULL,'',' ')||CLI_SUFFIX CLI_PNAMES, CLI_PREFIX||DECODE(CLI_PREFIX,NULL,'',' ')||CLI_FIRST_NAME||DECODE(CLI_MIDDLE_INITIAL,NULL,'',' '||CLI_MIDDLE_INITIAL)||' '||CLI_LAST_NAME||DECODE(CLI_SUFFIX,NULL,'',' ')||CLI_SUFFIX||DECODE(CLI_DESIGNATIONS,NULL,'',', ')||CLI_DESIGNATIONS CLI_PNAMESDES, cli_nickname,cli_designations, cli_title,decode(cli_inactive,'YES','YES','NO','NO',Null,'NO') cli_inactive, decode(instr(cli_cic_status||','||cli_cisr_status||','||cli_crm_status||','||cli_other_status,'DELETE'),0,'NO','YES') CLI_DELETED, nvl(cli_cic_status,' ') cli_cic_status , cli_cic_status_date,nvl(cli_cisr_status,' ') cli_cisr_status,cli_cisr_status_date, nvl(cli_crm_status,' ') cli_crm_status,cli_crm_status_date ,cli_other_status, f_dea_date(1,cli_rid,3) cli_cic_date, f_dea_date(2,cli_rid,3) cli_cisr_date, f_dea_date(3,cli_rid,3) cli_crm_date,f_dea_date(21,cli_rid,3) cli_csrm_date, f_dea_date(1,cli_rid,1) cli_cic_npdate, f_dea_date(2,cli_rid,1) cli_cisr_npdate, f_dea_date(3,cli_rid,1) cli_crm_npdate, f_dea_date(5,cli_rid,1) cli_ncim_npdate, f_dea_date(6,cli_rid,1) cli_dsr_npdate, f_dea_date(24,cli_rid,1) cli_srma_npdate, f_dea_date(21,cli_rid,1) cli_csrm_npdate, f_dea_date(16,cli_rid,1) cli_aces_npdate, f_dea_date(17,cli_rid,1) cli_prodsc_npdate, f_dea_date(19,cli_rid,1) cli_ethics_npdate, f_dea_date(1,cli_rid,2) cli_cic_1pdate, f_dea_date(2,cli_rid,2) cli_cisr_1pdate, f_dea_date(3,cli_rid,2) cli_crm_1pdate, f_dea_date(5,cli_rid,2) cli_ncim_1pdate, f_dea_date(6,cli_rid,2) cli_dsr_1pdate, f_dea_date(24,cli_rid,2) cli_srma_1pdate, f_dea_date(21,cli_rid,2) cli_csrm_1pdate, cli_cic_dues, f_post_date(CLI_RID,'CIC') cic_dues_post_date, cli_cisr_dues, f_post_date(CLI_RID,'CIS') cisr_dues_post_date, cli_crm_dues, f_post_date(CLI_RID,'CRM') crm_dues_post_date, cli_acad_dues, f_post_date(CLI_RID,'ACA') aca_dues_post_date, nvl(cli_cic_update,'01-JAN-2090') cli_cic_update, nvl(cli_cisr_update,'01-JAN-2090') cli_cisr_update, nvl(cli_crm_update,'01-JAN-2090') cli_crm_update, CPD_AGE_OWNER, cpd_age_sale_mgr, cpd_age_producer, cpd_age_csr, cpd_age_staff_cler, cpd_comp_underwriting, cpd_comp_sale_mktg, cpd_comp_claims, cpd_comp_management, cpd_number_emp, decode(F_POSITIONS(CLI_RID,14),14,'YES','NO') CIC_BOARD, decode(F_POSITIONS(CLI_RID,15),15,'YES','NO') CISR_BOARD, decode(F_POSITIONS(CLI_RID,16),16,'YES','NO') ACAD_BOARD, decode(F_POSITIONS(CLI_RID,18),18,'YES','NO') PAST_CIC_BOARD, decode(F_POSITIONS(CLI_RID,19),19,'YES','NO') RES_ASSOC, decode(F_POSITIONS(CLI_RID,20),20,'YES','NO') FAC_MEMBERS, decode(F_POSITIONS(CLI_RID,21),21,'YES','NO') LIC_EXEC, decode(F_POSITIONS(CLI_RID,22),22,'YES','NO') LIC_CONT, decode(F_POSITIONS(CLI_RID,23),23,'YES','NO') ED_CONST, decode(F_POSITIONS(CLI_RID,26),26,'YES','NO') RA_EXEC_COUN, decode(F_POSITIONS(CLI_RID,27),27,'YES','NO') RESOURCES, decode(F_POSITIONS(CLI_RID,28),28,'YES','NO') ACAD_FOUND, decode(F_POSITIONS(CLI_RID,30),30,'YES','NO') SOC_SPON_ASSOC, decode(f_flags(cli_rid,14),14,'YES','NO') CRM_MAILINGS, decode(f_flags(cli_rid,28),28,'YES','NO') REMAIL_DUES, decode(f_flags(cli_rid,29),29,'YES','NO') NO_FAX, decode(f_flags(cli_rid,60),60,'YES','NO') NO_DUES_INVOICE, decode(f_flags(cli_rid,61),61,'YES','NO') CISR_COURSE_CERT, decode(f_flags(cli_rid,62),62,'YES','NO') NO_MAIL_CISR, decode(f_flags(cli_rid,63),63,'YES','NO') NO_MAIL_CIC, decode(f_flags(cli_rid,64),64,'YES','NO') NO_MAIL_CRM, decode(f_flags(cli_rid,65),65,'YES','NO') NO_MAIL_ACAD, decode(f_flags(cli_rid,66),66,'YES','NO') NO_MAIL_NCIM, decode(f_flags(cli_rid,67),67,'YES','NO') NO_email, 0 PASS_CC, 0 PASS_CP, 0 PASS_LH, 0 PASS_PL, 0 PASS_AM, 0 PASS_PR, 0 PASS_PA, 0 PASS_IP, 0 PASS_IC, 0 PASS_AO, 0 PASS_RME, 0 PASS_RA, 0 PASS_RC, 0 PASS_RF, 0 PASS_AD, cpd_time_spent, cpd_ass_aff_iia IIA, cpd_ass_aff_pia PIA, cpd_ass_aff_aicpa AICPA, cpd_ass_aff_naiw NAIW, cpd_ass_aff_other Other_aff, cpd_ass_aff_nalu NALU, cpd_ass_aff_rims RIMS, decode(cpd_ass_aff_desc,null,'NONE',cpd_ass_aff_desc) affiliate_desc, F_any_2ND_EVE_DATE(cli_rid,1) SECOND_CIC_COURSE, F_any_2ND_EVE_DATE(cli_rid,2) SECOND_CISR_COURSE, F_any_2ND_EVE_DATE(cli_rid,3) SECOND_CRM_COURSE, f_LAST_date(cli_rid,1) LAST_CIC_COURSE, f_LAST_date(cli_rid,2) LAST_CISR_COURSE, f_last_date(cli_rid,4) LAST_ATTENDANCE, F_LAST_DATE(CLI_RID,3) LAST_CRM_COURSE, F_LAST_DATE(CLI_RID,5) LAST_NCIM_COURSE, F_LAST_CIC_UPDATE_EVT(CLI_RID) LAST_CIC_UPDATE_EVENT, F_LAST_CISR_UPDATE_EVT(CLI_RID) LAST_CISR_UPDATE_EVENT, F_LAST_CRM_UPDATE_EVT(CLI_RID) LAST_CRM_UPDATE_EVENT, F_FAC_STATUS(CLI_RID) CLI_FAC_STATUS, F_GET_DUES(cli_rid,'CIC') CIC_DUES, F_GET_DUES(cli_rid,'CRM') CRM_DUES, cli_rid cli_rid_v, f_other_designations(cli_designations) cli_other_designations from clients_and_facilitators,client_mailing_address,agency_companies, CLIENT_PERSONAL_DATA where mad_cli_rid=cli_rid and cli_agency_id=age_rid(+) and CLI_RID = CPD_CLI_RID(+) /