createorreplace package CARD_SERVICE_DAY_VIEW_PARAM is function set_param(num varchar) returnvarchar; function get_param returnvarchar; end CARD_SERVICE_DAY_VIEW_PARAM; commit; createorreplace package body CARD_SERVICE_DAY_VIEW_PARAM is paramValue varchar(10); function set_param(num varchar) returnvarcharis begin paramValue:=num; return num; end; function get_param returnvarcharis begin return paramValue; end; end CARD_SERVICE_DAY_VIEW_PARAM; commit; createorreplaceview CARD_SERVICE_DAY_VIEW as select cs.id, cs.card_id, cs.lock_money, to_char(cs.check_time,'yyyy-MM-dd hh24:mi') as check_time, cs.name,cs.phone,( months_between(to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd'), to_date(to_char(cs.CHECK_TIME,'yyyyMMdd'),'yyyyMMdd')) ) as curr_batch, (selectcount(1) fromtransactionwhere card_id=cs.card_id and tran_time between cs.check_time and to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd') and (curr_bal-money)<cs.lock_money) as condition, (selectcount(1) from service_transaction where batch=(months_between(to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd'),to_date(to_char(cs.CHECK_TIME,'yyyyMMdd'),'yyyyMMdd'))) and s_id=cs.id) as hasAudit from card_service cs where cs.state='T' and months_between(to_date(CARD_SERVICE_DAY_VIEW_PARAM.get_param(),'yyyyMMdd'),to_date(to_char(cs.CHECK_TIME,'yyyyMMdd'),'yyyyMMdd')) in (1,2,3,4,5,6) orderby cs.card_id commit;