--添加辅助属性字段altertableT_BD_STOCKALERTADDF_CUS_FAUXPROPIDBIGINTNOTNULLDEFAULT(0);altertableT_BD_STOCKALERTADDF_CUS_FAUXPROPNUmbervarchar(200)NOTNULLDEFAULT('');GOCREATEVIEWV_STK_WARNSTOCKCUSASSELECT((((CONVERT(VARCHAR(8000),TM.FMATERIALID)+'_')+CONVERT(VARCHAR(8000),TM.FUSEORGID))+'_')+CONVERT(VARCHAR(8000),SA.FSTOCKID))fid,CASEWHEN(tv.FMATERIALIDISNULL)THEN'0'ELSE'1'ENDfisbiztype,SA.FSTOCKIDfstockid,SA.F_CUS_FAUXPROPIDFAUXPROPID,SA.F_CUS_FAUXPROPNUmberFAUXPROPNUmber,TM.FMATERIALIDfmaterialid,TM.FNUMBERfnumber,TM.FUSEORGIDfstockorgid,ISNULL(tv.FSTOCKUNITID,TS.FSTOREUNITID)fstockunitid,ISNULL(tv.FBASEUNITID,TB.FBASEUNITID)fbaseunitid,ISNULL(tv.FSECUNITID,TS.FAUXUNITID)fsecunitid,ISNULL(tv.fbaseqty,0.00)fbaseqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(tv.fbaseqty,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfqty,ISNULL(tv.fsecqty,0.00)fsecqty,ISNULL(tv.fbaseavbqty,0.00)fbaseavbqty,SA.FSAFESTOCKfsafestock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FSAFESTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsafestunitqty,SA.FMINSTOCKfminstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FMINSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfminstunitqty,SA.FMAXSTOCKfmaxstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FMAXSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxstunitqty,SA.FREORDERGOODfreordergood,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FREORDERGOOD,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreordergoodstunitqty,SA.FECONREORDERQTYfeconreorderqty,(ISNULL(tv.fbaseqty,0.00)-SA.FMAXSTOCK)fmaxdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FMAXSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxdifferecestunitqty,CASEWHENSA.FMAXSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FMAXSTOCK)/SA.FMAXSTOCK)ASNUMERIC(19,6))*100.0)ENDfmaxdifferecerate,(ISNULL(tv.fbaseqty,0.00)-SA.FMINSTOCK)fmindiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FMINSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmindifferecestunitqty,CASEWHENSA.FMINSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FMINSTOCK)/SA.FMINSTOCK)ASNUMERIC(19,6))*100.0)ENDfmindifferecerate,(ISNULL(tv.fbaseqty,0.00)-SA.FSAFESTOCK)fsavediffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FSAFESTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsavedifferecestunitqty,CASEWHENSA.FSAFESTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FSAFESTOCK)/SA.FSAFESTOCK)ASNUMERIC(19,6))*100.0)ENDfsavedifferecerate,(ISNULL(tv.fbaseqty,0.00)-SA.FREORDERGOOD)freorderdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FREORDERGOOD)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreorderdifferecestunitqty,CASEWHENSA.FREORDERGOOD=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FREORDERGOOD)/SA.FREORDERGOOD)ASNUMERIC(19,6))*100.0)ENDfreorderdifferecerate,TS.FISENABLEMINSTOCK,TS.FISENABLESAFESTOCK,TS.FISENABLEREORDER,TS.FISENABLEMAXSTOCK,TM.FFORBIDSTATUSFROMT_BD_MATERIALTMINNERJOINT_BD_MATERIALBASETBON(TB.FMATERIALID=TM.FMATERIALIDANDTB.FISINVENTORY='1')INNERJOINT_BD_MATERIALSTOCKTSONTS.FMATERIALID=TM.FMATERIALIDINNERJOINT_BD_STOCKALERTSAONSA.FMATERIALID=TM.FMATERIALIDLEFTOUTERJOIN(SELECTT.FMATERIALID,T.FSTOCKID,T.FAUXPROPID,T.FSTOCKORGID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID,SUM(T.FBASEQTY)fbaseqty,SUM(FSECQTY)fsecqty,SUM(T.FBASEAVBQTY)fbaseavbqtyFROMT_STK_INVENTORYTINNERJOINT_BD_STOCKSTATUSSSONT.FSTOCKSTATUSID=SS.FSTOCKSTATUSIDINNERJOINT_BD_STOCKSTOONT.FSTOCKID=STO.FSTOCKIDWHERE((SS.FAVAILABLEALERT='1'ANDSTO.FAVAILABLEALERT='1')ANDT.FISEFFECTIVED='1')GROUPBYT.FMATERIALID,T.FSTOCKORGID,T.FSTOCKID,T.FAUXPROPID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID)tvON((tv.FMATERIALID=TM.FMASTERIDANDtv.FSTOCKORGID=TM.FUSEORGID)ANDtv.FSTOCKID=SA.FSTOCKIDANDtv.FAUXPROPID=SA.F_CUS_FAUXPROPID)UNIONALLSELECT((((CONVERT(VARCHAR(8000),TM.FMATERIALID)+'_')+CONVERT(VARCHAR(8000),SA.FSTOCKORG))+'_')+CONVERT(VARCHAR(8000),SA.FSTOCKID))fid,CASEWHEN(tv.FMATERIALIDISNULL)THEN'0'ELSE'1'ENDfisbiztype,SA.FSTOCKIDfstockid,SA.F_CUS_FAUXPROPIDFAUXPROPID,SA.F_CUS_FAUXPROPNUmberFAUXPROPNUmber,TM.FMATERIALIDfmaterialid,TM.FNUMBERfnumber,SA.FSTOCKORGfstockorgid,ISNULL(tv.FSTOCKUNITID,TS.FSTOREUNITID)fstockunitid,ISNULL(tv.FBASEUNITID,TB.FBASEUNITID)fbaseunitid,ISNULL(tv.FSECUNITID,TS.FAUXUNITID)fsecunitid,ISNULL(tv.fbaseqty,0.00)fbaseqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(tv.fbaseqty,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfqty,ISNULL(tv.fsecqty,0.00)fsecqty,ISNULL(tv.fbaseavbqty,0.00)fbaseavbqty,SA.FSAFESTOCKfsafestock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FSAFESTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsafestunitqty,SA.FMINSTOCKfminstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FMINSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfminstunitqty,SA.FMAXSTOCKfmaxstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FMAXSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxstunitqty,SA.FREORDERGOODfreordergood,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(SA.FREORDERGOOD,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreordergoodstunitqty,SA.FECONREORDERQTYfeconreorderqty,(ISNULL(tv.fbaseqty,0.00)-SA.FMAXSTOCK)fmaxdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FMAXSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxdifferecestunitqty,CASEWHENSA.FMAXSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FMAXSTOCK)/SA.FMAXSTOCK)ASNUMERIC(19,6))*100.0)ENDfmaxdifferecerate,(ISNULL(tv.fbaseqty,0.00)-SA.FMINSTOCK)fmindiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FMINSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmindifferecestunitqty,CASEWHENSA.FMINSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FMINSTOCK)/SA.FMINSTOCK)ASNUMERIC(19,6))*100.0)ENDfmindifferecerate,(ISNULL(tv.fbaseqty,0.00)-SA.FSAFESTOCK)fsavediffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FSAFESTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsavedifferecestunitqty,CASEWHENSA.FSAFESTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FSAFESTOCK)/SA.FSAFESTOCK)ASNUMERIC(19,6))*100.0)ENDfsavedifferecerate,(ISNULL(tv.fbaseqty,0.00)-SA.FREORDERGOOD)freorderdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-SA.FREORDERGOOD)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreorderdifferecestunitqty,CASEWHENSA.FREORDERGOOD=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-SA.FREORDERGOOD)/SA.FREORDERGOOD)ASNUMERIC(19,6))*100.0)ENDfreorderdifferecerate,TS.FISENABLEMINSTOCK,TS.FISENABLESAFESTOCK,TS.FISENABLEREORDER,TS.FISENABLEMAXSTOCK,TM.FFORBIDSTATUSFROMT_BD_MATERIALTMINNERJOINT_BD_MATERIALBASETBON(TB.FMATERIALID=TM.FMATERIALIDANDTB.FISINVENTORY='1')INNERJOINT_BD_MATERIALSTOCKTSONTS.FMATERIALID=TM.FMATERIALIDINNERJOINT_BD_STOCKALERTSAONSA.FMATERIALID=TM.FMATERIALIDLEFTOUTERJOIN(SELECTT.FMATERIALID,T.FSTOCKID,T.FAUXPROPID,T.FSTOCKORGID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID,SUM(T.FBASEQTY)fbaseqty,SUM(FSECQTY)fsecqty,SUM(T.FBASEAVBQTY)fbaseavbqtyFROMT_STK_INVENTORYTINNERJOINT_BD_STOCKSTATUSSSONT.FSTOCKSTATUSID=SS.FSTOCKSTATUSIDINNERJOINT_BD_STOCKSTOONT.FSTOCKID=STO.FSTOCKIDWHERE((SS.FAVAILABLEALERT='1'ANDSTO.FAVAILABLEALERT='1')ANDT.FISEFFECTIVED='1')GROUPBYT.FMATERIALID,T.FSTOCKORGID,T.FSTOCKID,T.FAUXPROPID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID)tvON((tv.FMATERIALID=TM.FMASTERIDANDtv.FSTOCKORGID=SA.FSTOCKORG)ANDtv.FSTOCKID=SA.FSTOCKIDANDtv.FAUXPROPID=SA.F_CUS_FAUXPROPID)WHEREEXISTS(SELECT1FROMT_META_BASEDATATYPEBTWHERE(BT.FBASEDATATYPEID='BD_MATERIAL'ANDBT.FSTRATEGYTYPE=1))UNIONALLSELECT((CONVERT(VARCHAR(8000),TM.FMATERIALID)+'_')+CONVERT(VARCHAR(8000),TM.FUSEORGID))fid,CASEWHEN(tv.FMATERIALIDISNULL)THEN'0'ELSE'1'ENDfisbiztype,0fstockid,0FAUXPROPID,''FAUXPROPNUmber,TM.FMATERIALIDfmaterialid,TM.FNUMBERfnumber,TM.FUSEORGIDfstockorgid,ISNULL(tv.FSTOCKUNITID,TS.FSTOREUNITID)fstockunitid,ISNULL(tv.FBASEUNITID,TB.FBASEUNITID)fbaseunitid,ISNULL(tv.FSECUNITID,TS.FAUXUNITID)fsecunitid,ISNULL(tv.fbaseqty,0.00)fbaseqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(tv.fbaseqty,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfqty,ISNULL(tv.fsecqty,0.00)fsecqty,ISNULL(tv.fbaseavbqty,0.00)fbaseavbqty,TS.FSAFESTOCKfsafestock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FSAFESTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsafestunitqty,TS.FMINSTOCKfminstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FMINSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfminstunitqty,TS.FMAXSTOCKfmaxstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FMAXSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxstunitqty,TS.FREORDERGOODfreordergood,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FREORDERGOOD,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreordergoodstunitqty,TS.FECONREORDERQTYfeconreorderqty,(ISNULL(tv.fbaseqty,0.00)-TS.FMAXSTOCK)fmaxdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FMAXSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxdifferecestunitqty,CASEWHENTS.FMAXSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FMAXSTOCK)/TS.FMAXSTOCK)ASNUMERIC(19,6))*100.0)ENDfmaxdifferecerate,(ISNULL(tv.fbaseqty,0.00)-TS.FMINSTOCK)fmindiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FMINSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmindifferecestunitqty,CASEWHENTS.FMINSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FMINSTOCK)/TS.FMINSTOCK)ASNUMERIC(19,6))*100.0)ENDfmindifferecerate,(ISNULL(tv.fbaseqty,0.00)-TS.FSAFESTOCK)fsavediffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FSAFESTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsavedifferecestunitqty,CASEWHENTS.FSAFESTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FSAFESTOCK)/TS.FSAFESTOCK)ASNUMERIC(19,6))*100.0)ENDfsavedifferecerate,(ISNULL(tv.fbaseqty,0.00)-TS.FREORDERGOOD)freorderdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FREORDERGOOD)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreorderdifferecestunitqty,CASEWHENTS.FREORDERGOOD=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FREORDERGOOD)/TS.FREORDERGOOD)ASNUMERIC(19,6))*100.0)ENDfreorderdifferecerate,TS.FISENABLEMINSTOCK,TS.FISENABLESAFESTOCK,TS.FISENABLEREORDER,TS.FISENABLEMAXSTOCK,TM.FFORBIDSTATUSFROMT_BD_MATERIALTMINNERJOINT_BD_MATERIALBASETBON(TB.FMATERIALID=TM.FMATERIALIDANDTB.FISINVENTORY='1')INNERJOINT_BD_MATERIALSTOCKTSONTS.FMATERIALID=TM.FMATERIALIDLEFTOUTERJOIN(SELECTT.FMATERIALID,T.FSTOCKORGID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID,SUM(T.FBASEQTY)fbaseqty,SUM(FSECQTY)fsecqty,SUM(T.FBASEAVBQTY)fbaseavbqtyFROMT_STK_INVENTORYTINNERJOINT_BD_STOCKSTATUSSSONT.FSTOCKSTATUSID=SS.FSTOCKSTATUSIDINNERJOINT_BD_STOCKSTOONT.FSTOCKID=STO.FSTOCKIDWHERE((SS.FAVAILABLEALERT='1'ANDSTO.FAVAILABLEALERT='1')ANDT.FISEFFECTIVED='1')GROUPBYT.FMATERIALID,T.FSTOCKORGID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID)tvON(tv.FMATERIALID=TM.FMASTERIDANDtv.FSTOCKORGID=TM.FUSEORGID)WHERENOTEXISTS(SELECT1FROMT_BD_STOCKALERTTAWHERETA.FMATERIALID=TM.FMATERIALID)UNIONALLSELECT((CONVERT(VARCHAR(8000),TM.FMATERIALID)+'_')+CONVERT(VARCHAR(8000),TM.FUSEORGID))fid,CASEWHEN(tv.FMATERIALIDISNULL)THEN'0'ELSE'1'ENDfisbiztype,0fstockid,0FAUXPROPID,''FAUXPROPNUmber,TM.FMATERIALIDfmaterialid,TM.FNUMBERfnumber,TM.FUSEORGIDfstockorgid,ISNULL(tv.FSTOCKUNITID,TS.FSTOREUNITID)fstockunitid,ISNULL(tv.FBASEUNITID,TB.FBASEUNITID)fbaseunitid,ISNULL(tv.FSECUNITID,TS.FAUXUNITID)fsecunitid,ISNULL(tv.fbaseqty,0.00)fbaseqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(tv.fbaseqty,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfqty,ISNULL(tv.fsecqty,0.00)fsecqty,ISNULL(tv.fbaseavbqty,0.00)fbaseavbqty,TS.FSAFESTOCKfsafestock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FSAFESTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsafestunitqty,TS.FMINSTOCKfminstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FMINSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfminstunitqty,TS.FMAXSTOCKfmaxstock,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FMAXSTOCK,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxstunitqty,TS.FREORDERGOODfreordergood,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST(((ISNULL(TS.FREORDERGOOD,0.00)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreordergoodstunitqty,TS.FECONREORDERQTYfeconreorderqty,(ISNULL(tv.fbaseqty,0.00)-TS.FMAXSTOCK)fmaxdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FMAXSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxdifferecestunitqty,CASEWHENTS.FMAXSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FMAXSTOCK)/TS.FMAXSTOCK)ASNUMERIC(19,6))*100.0)ENDfmaxdifferecerate,(ISNULL(tv.fbaseqty,0.00)-TS.FMINSTOCK)fmindiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FMINSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmindifferecestunitqty,CASEWHENTS.FMINSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FMINSTOCK)/TS.FMINSTOCK)ASNUMERIC(19,6))*100.0)ENDfmindifferecerate,(ISNULL(tv.fbaseqty,0.00)-TS.FSAFESTOCK)fsavediffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FSAFESTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsavedifferecestunitqty,CASEWHENTS.FSAFESTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FSAFESTOCK)/TS.FSAFESTOCK)ASNUMERIC(19,6))*100.0)ENDfsavedifferecerate,(ISNULL(tv.fbaseqty,0.00)-TS.FREORDERGOOD)freorderdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)-TS.FREORDERGOOD)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfreorderdifferecestunitqty,CASEWHENTS.FREORDERGOOD=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)-TS.FREORDERGOOD)/TS.FREORDERGOOD)ASNUMERIC(19,6))*100.0)ENDfreorderdifferecerate,TS.FISENABLEMINSTOCK,TS.FISENABLESAFESTOCK,TS.FISENABLEREORDER,TS.FISENABLEMAXSTOCK,TM.FFORBIDSTATUSFROMT_BD_MATERIALTMINNERJOINT_BD_MATERIALBASETBON(TB.FMATERIALID=TM.FMATERIALIDANDTB.FISINVENTORY='1')INNERJOINT_BD_MATERIALSTOCKTSONTS.FMATERIALID=TM.FMATERIALIDLEFTOUTERJOIN(SELECTT.FMATERIALID,T.FSTOCKORGID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID,SUM(T.FBASEQTY)fbaseqty,SUM(FSECQTY)fsecqty,SUM(T.FBASEAVBQTY)fbaseavbqtyFROMT_STK_INVENTORYTINNERJOINT_BD_STOCKSTATUSSSONT.FSTOCKSTATUSID=SS.FSTOCKSTATUSIDINNERJOINT_BD_STOCKSTOONT.FSTOCKID=STO.FSTOCKIDWHERE((SS.FAVAILABLEALERT='1'ANDSTO.FAVAILABLEALERT='1')ANDT.FISEFFECTIVED='1')GROUPBYT.FMATERIALID,T.FSTOCKORGID,T.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID)tvONtv.FMATERIALID=TM.FMASTERIDWHERE(EXISTS(SELECT1FROMT_META_BASEDATATYPEBTWHERE(BT.FBASEDATATYPEID='BD_MATERIAL'ANDBT.FSTRATEGYTYPE=1))ANDNOTEXISTS(SELECT1FROMT_BD_STOCKALERTTAWHERETA.FMATERIALID=TM.FMATERIALID));GOCREATEVIEWV_STK_WARNSTOCKCUS_LASSELECTml.FPKID,a.fid,a.fmaterialid,ml.FLOCALEID,ml.FNAME,ml.FSPECIFICATION,ml.FDESCRIPTION,ISNULL(sl.FNAME,N'')fstocknameFROMV_STK_WARNSTOCKCUSaINNERJOINT_BD_MATERIAL_LmlONml.FMATERIALID=a.fmaterialidLEFTOUTERJOINT_BD_STOCK_LslON(sl.FSTOCKID=a.fstockidANDsl.FLOCALEID=ml.FLOCALEID)
