Function GetCountPriceSum(VFLAG VARCHAR2, VPLANNO VARCHAR2) RETURN VARCHAR2 IS
/*************************************
功能: 取执行值
**************************************/
VCount varchar2(50) := '';
BEGIN
IF(UPPER(VFLAG)='SP004001')THEN
select sum(DealTotalPrice)/10000 into VCount
from FM_RealtyInf c,
OM_PLANFORSALER a,
OM_DETAILOFPLANFORSALES b
where c.FloorId=a.EXCUTEFLOOR
and a.planid=b.planid
and c.SucessDate>=a.PLANSTARTTIME
and c.SucessDate<=a.FINISHDATE
and b.paramid=VFLAG and a.PLANid =VPLANNO; ELSIF(UPPER(VFLAG)='SP004002')THEN
select count(*) into VCount
from FM_RealtyInf c,
OM_PLANFORSALER a,
OM_DETAILOFPLANFORSALES b
where c.FloorId=a.EXCUTEFLOOR and a.planid=b.planid
and c.SucessDate>=a.PLANSTARTTIME
and c.SucessDate<=a.FINISHDATE
and b.paramid=VFLAG and a.PLANid =VPLANNO; ELSIF(UPPER(VFLAG)='SP004003')THEN
select sum(BuildArea) into VCount
from FM_RealtyInf c
OM_PLANFORSALER a ,
OM_DETAILOFPLANFORSALES b
where c.FloorId=a.EXCUTEFLOOR and a.planid=b.planid
and b.paramid=VFLAG and a.PLANid =VPLANNO; ELSIF(UPPER(VFLAG)='SP004004')THEN
select decode(sum(FeeTotal),'','0',sum(FeeTotal)) into VCount
from CPM_ContractFee c,
OM_PLANFORSALER a,
OM_DETAILOFPLANFORSALES
where c.FeeFloorID=a.EXCUTEFLOOR and a.planid=b.planid
and c.FeeDate>=a.PLANSTARTTIME
and c.FeeDate<=a.FINISHDATE and c.DELETEFLAG='0'
and b.paramid=VFLAG and a.PLANid =VPLANNO; END IF;
IF(VCount='')THEN
VCount:=0;
END IF; RETURN VCount; EXCEPTION
WHEN OTHERS THEN
RETURN '0';
END;
/*************************************
功能: 取执行值
**************************************/
VCount varchar2(50) := '';
BEGIN
IF(UPPER(VFLAG)='SP004001')THEN
select sum(DealTotalPrice)/10000 into VCount
from FM_RealtyInf c,
OM_PLANFORSALER a,
OM_DETAILOFPLANFORSALES b
where c.FloorId=a.EXCUTEFLOOR
and a.planid=b.planid
and c.SucessDate>=a.PLANSTARTTIME
and c.SucessDate<=a.FINISHDATE
and b.paramid=VFLAG and a.PLANid =VPLANNO; ELSIF(UPPER(VFLAG)='SP004002')THEN
select count(*) into VCount
from FM_RealtyInf c,
OM_PLANFORSALER a,
OM_DETAILOFPLANFORSALES b
where c.FloorId=a.EXCUTEFLOOR and a.planid=b.planid
and c.SucessDate>=a.PLANSTARTTIME
and c.SucessDate<=a.FINISHDATE
and b.paramid=VFLAG and a.PLANid =VPLANNO; ELSIF(UPPER(VFLAG)='SP004003')THEN
select sum(BuildArea) into VCount
from FM_RealtyInf c
OM_PLANFORSALER a ,
OM_DETAILOFPLANFORSALES b
where c.FloorId=a.EXCUTEFLOOR and a.planid=b.planid
and b.paramid=VFLAG and a.PLANid =VPLANNO; ELSIF(UPPER(VFLAG)='SP004004')THEN
select decode(sum(FeeTotal),'','0',sum(FeeTotal)) into VCount
from CPM_ContractFee c,
OM_PLANFORSALER a,
OM_DETAILOFPLANFORSALES
where c.FeeFloorID=a.EXCUTEFLOOR and a.planid=b.planid
and c.FeeDate>=a.PLANSTARTTIME
and c.FeeDate<=a.FINISHDATE and c.DELETEFLAG='0'
and b.paramid=VFLAG and a.PLANid =VPLANNO; END IF;
IF(VCount='')THEN
VCount:=0;
END IF; RETURN VCount; EXCEPTION
WHEN OTHERS THEN
RETURN '0';
END;
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货