CREATEVIEWV_STK_WARNPURZAITUAS(SELECTPURO.FSTOCKORGID,PURO.FMATERIALID,SUM(PURO.FBASEQTY)FBASEQTYFROM(SELECTTSES.FRECEIVEORGIDFSTOCKORGID,TM.FMATERIALID,SUM(TSE.FBASEUNITQTY-TSER.FORDERJNBASEQTY)ASFBASEQTYFROMT_PUR_REQUISITIONTSINNERJOINT_PUR_REQENTRYTSEONTSE.FID=TS.FIDINNERJOINT_PUR_REQENTRY_STSESONTSES.FENTRYID=TSE.FENTRYIDANDTSES.FID=TS.FIDINNERJOINT_PUR_REQENTRY_RTSERONTSER.FENTRYID=TSE.FENTRYIDANDTSER.FID=TS.FIDINNERJOINT_BD_MATERIALTM_CURORGONTM_CURORG.FMATERIALID=TSE.FMATERIALIDINNERJOINT_BD_MATERIALTMONTM.FMASTERID=TM_CURORG.FMASTERIDANDTM.FUSEORGID=TSES.FRECEIVEORGIDINNERJOINT_BD_MATERIALBASETMBONTMB.FMATERIALID=TM.FMATERIALIDWHERETS.FDOCUMENTSTATUSIN('A','B','C','D')ANDTS.FCANCELSTATUS='A'ANDTS.FCLOSESTATUS='A'ANDTSE.FMRPTERMINATESTATUS='A'ANDTSE.FMRPCLOSESTATUS='A'ANDTMB.FISINVENTORY='1'ANDTSE.FARRIVALDATE>=CONVERT(VARCHAR(50),DATEADD(YEAR,-1,GETDATE()),23)GROUPBYTSES.FRECEIVEORGID,TM.FMATERIALIDUNIONALLSELECTTSED.FRECEIVEORGIDFSTOCKORGID,TM.FMATERIALID,SUM(TSE.FSTOCKBASEQTY-TSER.FSTOCKBASESTOCKINQTY-TSER.FBASECHECKCUTPAYQTY)FBASEQTYFROMT_PUR_POORDERTSINNERJOINT_PUR_POORDERENTRYTSEONTSE.FID=TS.FIDINNERJOINT_PUR_POORDERENTRY_DTSEDONTSED.FENTRYID=TSE.FENTRYIDANDTSED.FID=TS.FIDINNERJOINT_PUR_POORDERENTRY_RTSERONTSER.FENTRYID=TSE.FENTRYIDANDTSER.FID=TS.FIDINNERJOINT_BD_MATERIALTM_CURORGONTM_CURORG.FMATERIALID=TSE.FMATERIALIDINNERJOINT_BD_MATERIALTMONTM.FMASTERID=TM_CURORG.FMASTERIDANDTM.FUSEORGID=TSED.FRECEIVEORGIDINNERJOINT_BD_MATERIALBASETMBONTMB.FMATERIALID=TM.FMATERIALIDWHERETS.FDOCUMENTSTATUSIN('A','B','C','D')ANDTS.FCANCELSTATUS='A'ANDTS.FCLOSESTATUS='A'ANDTSE.FMRPCLOSESTATUS='A'ANDTSE.FMRPTERMINATESTATUS='A'ANDTSE.FMRPFREEZESTATUS='A'ANDTMB.FISINVENTORY='1'ANDTSED.FDELIVERYDATE>=CONVERT(VARCHAR(50),DATEADD(YEAR,-1,GETDATE()),23)GROUPBYTSED.FRECEIVEORGID,TM.FMATERIALID)PUROGROUPBYPURO.FSTOCKORGID,PURO.FMATERIALID)GOCREATEVIEWV_STK_WARNSTOCK_PURASSELECT((((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,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.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.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID)tvON((tv.FMATERIALID=TM.FMASTERIDANDtv.FSTOCKORGID=TM.FUSEORGID)ANDtv.FSTOCKID=SA.FSTOCKID)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,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.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.FSTOCKUNITID,T.FBASEUNITID,T.FSECUNITID)tvON((tv.FMATERIALID=TM.FMASTERIDANDtv.FSTOCKORGID=SA.FSTOCKORG)ANDtv.FSTOCKID=SA.FSTOCKID)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,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)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMAXSTOCK)fmaxdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMAXSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxdifferecestunitqty,CASEWHENTS.FMAXSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMAXSTOCK)/TS.FMAXSTOCK)ASNUMERIC(19,6))*100.0)ENDfmaxdifferecerate,(ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMINSTOCK)fmindiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMINSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmindifferecestunitqty,CASEWHENTS.FMINSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMINSTOCK)/TS.FMINSTOCK)ASNUMERIC(19,6))*100.0)ENDfmindifferecerate,(ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FSAFESTOCK)fsavediffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FSAFESTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsavedifferecestunitqty,CASEWHENTS.FSAFESTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.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)LEFTOUTERJOINV_STK_WARNPURZAITUPURONPUR.FSTOCKORGID=TM.FUSEORGIDANDPUR.FMATERIALID=TM.FMATERIALIDWHERENOTEXISTS(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,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)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMAXSTOCK)fmaxdiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMAXSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmaxdifferecestunitqty,CASEWHENTS.FMAXSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMAXSTOCK)/TS.FMAXSTOCK)ASNUMERIC(19,6))*100.0)ENDfmaxdifferecerate,(ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMINSTOCK)fmindiffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMINSTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfmindifferecestunitqty,CASEWHENTS.FMINSTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FMINSTOCK)/TS.FMINSTOCK)ASNUMERIC(19,6))*100.0)ENDfmindifferecerate,(ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FSAFESTOCK)fsavediffereceqty,CASEWHENTS.FSTOREURNUM=0.0THEN0.00ELSECAST((((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.FBASEQTY,0.00)-TS.FSAFESTOCK)*TS.FSTOREURNOM)/TS.FSTOREURNUM)ASNUMERIC(23,10))ENDfsavedifferecestunitqty,CASEWHENTS.FSAFESTOCK=0.0THEN0.0ELSE(CAST(((ISNULL(tv.fbaseqty,0.00)+ISNULL(PUR.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.FMASTERIDLEFTOUTERJOINV_STK_WARNPURZAITUPURONPUR.FSTOCKORGID=TM.FUSEORGIDANDPUR.FMATERIALID=TM.FMATERIALIDWHERE(EXISTS(SELECT1FROMT_META_BASEDATATYPEBTWHERE(BT.FBASEDATATYPEID='BD_MATERIAL'ANDBT.FSTRATEGYTYPE=1))ANDNOTEXISTS(SELECT1FROMT_BD_STOCKALERTTAWHERETA.FMATERIALID=TM.FMATERIALID));GOCREATEVIEWV_STK_WARNSTOCK_PUR_LASSELECTml.FPKID,a.fid,a.fmaterialid,ml.FLOCALEID,ml.FNAME,ml.FSPECIFICATION,ml.FDESCRIPTION,ISNULL(sl.FNAME,N'')fstocknameFROMV_STK_WARNSTOCK_PURaINNERJOINT_BD_MATERIAL_LmlONml.FMATERIALID=a.fmaterialidLEFTOUTERJOINT_BD_STOCK_LslON(sl.FSTOCKID=a.fstockidANDsl.FLOCALEID=ml.FLOCALEID)
