declare
strval varchar(10);
str1 varchar(1000) := "and CLL.CHARTLOC_CODE in ('MIDC','CAPCO','AUCK','GREEN','NATW','STAR','MMORE','CHCH','BURW','CHCHW','GISB','MER','NELS','TIM','OAM','NLAND','DUNED','SOUTH','WAIR',
'HAWK','HUTT','WAKE','NSHOR','ROTO','TAUR','LAKE','TAUM','TARA','KENE','HAW','WHAK','WAIK','POR','HBEN','MASON','MIDC','LAKE','TARA','HORO','HAST',
'PMAR','NMDHB','AORA','ASHB','WAIT','ABURN','GREY','PORMH','WAI')";
str2 varchar(1000) := "and CLL.CHARTLOC_CODE not in ('MIDC','CAPCO','AUCK','GREEN','NATW','STAR','MMORE','CHCH','BURW','CHCHW','GISB','MER','NELS','TIM','OAM','NLAND','DUNED','SOUTH','WAIR',
'HAWK','HUTT','WAKE','NSHOR','ROTO','TAUR','LAKE','TAUM','TARA','KENE','HAW','WHAK','WAIK','POR','HBEN','MASON','MIDC','LAKE','TARA','HORO','HAST',
'PMAR','NMDHB','AORA','ASHB','WAIT','ABURN','GREY','PORMH','WAI')";
begin
select mr.pt_code,mr.mvmt_date,MR.MVMT_TYPE ,MR.DATE_REQUIRED,MR.EXP_RTN_DATE, MR.CHARTTYPE_CODE,MR.CHARTVOL_NO, MR.HOSP_CODE, CLL.CHARTLOC_TITLE
from k_mvmtreg mr, k_chartloclist cll
where MR.CHARTLOC_CODE = CLL.CHARTLOC_CODE
and MR.DEL_DATE is null
and MR.CHARTLOC_CODE = CLL.CHARTLOC_CODE
and CLL.CHARTLOC_CODE =case when:strval = 'Internal' then str2 else str1 end
and MR.EXP_RTN_DATE > sysdate -180
order by 1