CREATE OR REPLACE FORCE VIEW EMRVWINSURANCELIST (INSURANCE_NAME_ID, INSURANCE_NAME, INSURANCE_ADDRESS, INSURANCE_CITY, STATE_ID, STATE_CODE, STATE_NAME, INSURANCE_ZIP, INSURANCE_PHONE, INSURANCE_PAYER_ID, HEALTH_PLAN_ID) AS 
		SELECT INSURANCE_NAME_ID, INSURANCE_NAME, INSURANCE_ADDRESS,
			INSURANCE_CITY, EMRSTATESLKUP.STATE_ID, EMRSTATESLKUP.STATE_CODE, EMRSTATESLKUP.STATE_NAME,
			INSURANCE_ZIP, INSURANCE_PHONE, INSURANCE_PAYER_ID, NULL AS HEALTH_PLAN_ID
	FROM EMRINSURANCENAMESLKUP
	LEFT OUTER JOIN EMRSTATESLKUP ON EMRINSURANCENAMESLKUP.STATE_ID = EMRSTATESLKUP.STATE_ID
		WHERE (EMRINSURANCENAMESLKUP.STATUS = 1 OR EMRINSURANCENAMESLKUP.STATUS IS NULL)
	UNION ALL
		SELECT EZEMRX_HEALTH_PLAN_ID AS INSURANCE_NAME_ID, HEALTH_PLAN_NAME AS INSURANCE_NAME, 
			CASE WHEN NVL(HEALTH_PLAN_ADDRESS1,HEALTH_PLAN_ADDRESS2) AS INSURANCE_ADDRESS, 	
			HEALTH_PLAN_CITY AS INSURANCE_CITY, 
			EMRSTATESLKUP.STATE_ID,EMRSTATESLKUP.STATE_CODE,EMRSTATESLKUP.STATE_NAME,
			NVL(HEALTH_PLAN_ZIP,HEALTH_PLAN_ZIP4) AS INSURANCE_ZIP,
			HEALTH_PLAN_PHONE AS INSURANCE_PHONE,
			ORGANIZATION_ID	AS INSURANCE_PAYER_ID,
			EMRHEALTHPLANSTPLKUP.HEALTH_PLAN_ID
	FROM EMRHEALTHPLANSTPLKUP 
	LEFT OUTER JOIN EMRSTATESLKUP ON EMRHEALTHPLANSTPLKUP.HEALTH_PLAN_STATE_CODE = EMRSTATESLKUP.STATE_CODE
		WHERE STATUS = 'A';
/