Store procedure in Oracle
FUNCTION GET_BAND_UNITCOST
(
SUB_REQID NUMBER,
TBL VARCHAR
)
RETURN NUMBER IS
UNIT_COST NUMBER;
--only used in cabin space
acNonAc_val number;
BEGIN
IF(TBL='accAnt') THEN
--bandwise pricing method cost
select min(SectAntPricing.price) INTO UNIT_COST
from AccessAntRequest INNER JOIN
RequestMaster ON AccessAntRequest.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID INNER JOIN
GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band INNER JOIN
SectAntPricing ON GenItemPricing.genitmepriceid = SectAntPricing.genitmepriceid
WHERE AccessAntRequest.accantreqid = SUB_REQID and
GenItemPricing.tenantid = RequestMaster.tenantid and
SectAntPricing.antlenheight=AccessAntRequest.antennalength and
SectAntPricing.installheight >= AccessAntRequest.requestingheight ;
if UNIT_COST is null then --no pricing data
UNIT_COST := -1;
end if;
END IF;
------------------------------------------------------
IF(TBL='txnAnt') THEN
select min(ParabolicAntPricing.price) INTO UNIT_COST
from TxnAntRequest INNER JOIN
RequestMaster ON TxnAntRequest.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID INNER JOIN
GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band INNER JOIN
PARABOLICANTPRICING ON GenItemPricing.genitmepriceid = PARABOLICANTPRICING.genitmepriceid
WHERE TxnAntRequest.txnAntreqid = SUB_REQID and
GenItemPricing.tenantid = RequestMaster.tenantid and
ParabolicAntPricing.antdiameter=TxnAntRequest.antennalength and
ParabolicAntPricing.installheight >= TxnAntRequest.requestingheight ;
if UNIT_COST is null then --no pricing data
UNIT_COST := -1;
end if;
END IF;
-------------------------------------------------
IF(TBL='pwrSup') THEN
select PowerPricing.price INTO UNIT_COST
from PwrSupRequest INNER JOIN
RequestMaster ON PwrSupRequest.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID INNER JOIN
GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band INNER JOIN
PowerPricing ON PowerPricing.genitmepriceid = GenItemPricing.genitmepriceid
WHERE PwrSupRequest.pwrsupreqid = SUB_REQID and
GenItemPricing.tenantid = RequestMaster.tenantid and
PowerPricing.configid= PwrSupRequest.powertypesdet and
PowerPricing.con_phase= PwrSupRequest.powerphase ;
if UNIT_COST is null then --no pricing data
UNIT_COST := -1;
end if;
END IF;
----------------------------------------------------------------
IF(TBL='cabSpc') THEN
select cabinSpaceReq.acnonac into acNonAc_val
from cabinSpaceReq INNER JOIN
RequestMaster ON cabinSpaceReq.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID INNER JOIN
GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band
where cabinSpaceReq.cabspcreqid = SUB_REQID and
GenItemPricing.tenantid = RequestMaster.tenantid ;
if acNonAc_val=1 then --take ac pricing
select GenItemPricing.acindspace into UNIT_COST
from cabinSpaceReq INNER JOIN
RequestMaster ON cabinSpaceReq.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID INNER JOIN
GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band
where cabinSpaceReq.cabspcreqid = SUB_REQID and
GenItemPricing.tenantid = RequestMaster.tenantid ;
else --take nonAC pricing
select GenItemPricing.nonacindspace into UNIT_COST
from cabinSpaceReq INNER JOIN
RequestMaster ON cabinSpaceReq.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID INNER JOIN
GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band
where cabinSpaceReq.cabspcreqid = SUB_REQID and
GenItemPricing.tenantid = RequestMaster.tenantid ;
end if;
if UNIT_COST is null then --no pricing data
UNIT_COST := -1;
end if;
END IF;
----------------------------------------------------------------
IF(TBL='rckSpc') THEN
select GenItemPricing.ninteeninchrackspace into UNIT_COST
from GenItemPricing INNER JOIN
SiteGeneralDetail ON GenItemPricing.Band = SiteGeneralDetail.Band INNER JOIN
RequestMaster ON SiteGeneralDetail.SiteID = RequestMaster.SiteID AND SiteGeneralDetail.SiteID = RequestMaster.SiteID INNER JOIN
RackSpaceReq ON RequestMaster.RequestID = RackSpaceReq.RequestID
where RackSpaceReq.rakspcreqid=SUB_REQID AND GenItemPricing.tenantid=RequestMaster.tenantid;
if UNIT_COST is null then --no pricing data
UNIT_COST := -1;
end if;
END IF;
----------------------------------------------------------------
IF(TBL='grndSpc') THEN
select GenItemPricing.outgndspace into UNIT_COST
from GroundSpaceReq INNER JOIN
RequestMaster ON GroundSpaceReq.RequestID = RequestMaster.RequestID INNER JOIN
SiteGeneralDetail ON RequestMaster.SiteID = SiteGeneralDetail.SiteID
INNER JOIN GenItemPricing ON SiteGeneralDetail.Band = GenItemPricing.Band
where GroundSpaceReq.grndspareqid=SUB_REQID and GenItemPricing.tenantid=RequestMaster.tenantid;
if UNIT_COST is null then --no pricing data
UNIT_COST := -1;
end if;
END IF;
----------------------------------------------------------------
RETURN UNIT_COST ;
--EXCEPTION
-- WHEN OTHERS THEN
-- statements ;
END;
Comments
Post a Comment