declare @FMATERIALID int,@FSTOCKORGID int
set @FMATERIALID=108998
set @FSTOCKORGID=100189
SELECT 0 flockorder ,
— TI.FSTOCKORGID ,
— TI.FKEEPERTYPEID ,
— TK.fitemid fkeeperid ,
— TI.FOWNERTYPEID ,
— TW.fitemid fownerid ,
— TS.FSTOCKID ,
— TI.FSTOCKLOCID ,
— TM.FMATERIALID ,
— TI.FBASEUNITID ,
— TI.FSTOCKUNITID ,
— TI.FSECUNITID ,
— TI.FID finvdetailid ,
— TI.FAUXPROPID ,
— TI.FSTOCKSTATUSID ,
— TI.FLOT ,
— TB.FID fbomid ,
— TI.FMTONO ,
— TI.FPROJECTNO ,
— CASE WHEN TMS.FISEXPPARTOFLOT = ‘1’ THEN TL.FPRODUCEDATE
— ELSE TI.FPRODUCEDATE
— END fproducedate ,
— CASE WHEN TMS.FISEXPPARTOFLOT = ‘1’ THEN TL.FEXPIRYDATE
— ELSE TI.FEXPIRYDATE
— END fexpirydate ,
— TI.FBASEQTY ,
— TI.FSECQTY ,
— TI.FQTY ,
— TK.fmasterid fkeeperid_md ,
— TK.fnumber fkeeperid_nu ,
— TK_L.fname fkeeperid_na ,
— TW.fmasterid fownerid_md ,
— TW.fnumber fownerid_nu ,
— TW_L.fname fownerid_na ,
— TS.FMASTERID fstockid_md ,
— TS.FNUMBER fstockid_nu ,
— TSL.FNAME fstockid_na ,
— TT.FSTOCKSTATUSID fstockstatusid_md ,
— TT.FNUMBER fstockstatusid_nu ,
— TTL.FNAME fstockstatusid_na ,
— TT.FTYPE fstkstatustype ,
— TT.FAVAILABLE fstkstatusavailable ,
— TT.FAVAILABLELOCK fstkstatusavailablelock ,
— TT.FAVAILABLEMRP fstkstatusavailablemrp ,
— TT.FAVAILABLEATP fstkstatusavailableatp ,
— TT.FNOTSALE fstkstatusnotsale ,
— TT.FNOTGET fstkstatusnotget ,
TL.FLOTID ,
TL.FMASTERID flot_md ,
TL.FNUMBER flot_nu ,
TL.FNUMBER flotnumber ,
TB.FMASTERID fbomid_md ,
TB.FNUMBER fbomid_nu ,
TL.FINSTOCKDATE finstockdate ,
TS.FSORTINGPRIORITY fsortingpriority ,
TS.FAVAILABLEPICKING favailablepicking ,
TAXP.FF100001 fauxp_ff100001 ,
TAXP.FF100002 fauxp_ff100002
–into #TEMO
FROM T_STK_INVENTORY TI
INNER JOIN T_BD_MATERIAL TM ON TI.FMATERIALID = TM.FMASTERID
AND TI.FSTOCKORGID = TM.FUSEORGID
)
INNER JOIN T_BD_STOCK TS ON TI.FSTOCKID = TS.FMASTERID
AND TI.FSTOCKORGID = TS.FUSEORGID
)
–INNER JOIN T_BD_MATERIALSTOCK TMS ON TM.FMATERIALID = TMS.FMATERIALID
–INNER JOIN V_ITEMCLASS_KEEPER TK ON TK.fmasterid = TI.FKEEPERID
— AND TK.fformid = TI.FKEEPERTYPEID
— )
— AND TK.fuseorgid = TI.FSTOCKORGID
— OR TK.fuseorgid = 0
— OR EXISTS SELECT
— 1
— FROM
— T_META_BASEDATATYPE BT
— WHERE
— BT.FBASEDATATYPEID = TK.fformid
— AND BT.FSTRATEGYTYPE = 1
— ) )
— )
— )
–LEFT OUTER JOIN V_ITEMCLASS_KEEPER_L TK_L ON TK.fitemid = TK_L.fitemid
— AND TK_L.FLOCALEID = 2052
— )
–INNER JOIN V_ITEMCLASS_OWNER TW ON TW.fmasterid = TI.FOWNERID
— AND TW.fformid = TI.FOWNERTYPEID
— )
— AND TW.fuseorgid = TI.FSTOCKORGID
— OR TW.fuseorgid = 0
— OR EXISTS SELECT
— 1
— FROM
— T_META_BASEDATATYPE BT
— WHERE
— BT.FBASEDATATYPEID = TW.fformid
— AND BT.FSTRATEGYTYPE = 1
— ) )
— )
— )
–LEFT OUTER JOIN V_ITEMCLASS_OWNER_L TW_L ON TW.fitemid = TW_L.fitemid
— AND TW_L.FLOCALEID = 2052
— )
–LEFT OUTER JOIN T_BD_STOCK_L TSL ON TS.FSTOCKID = TSL.FSTOCKID
— AND TSL.FLOCALEID = 2052
— )
–INNER JOIN T_BD_STOCKSTATUS TT ON TI.FSTOCKSTATUSID = TT.FSTOCKSTATUSID
–LEFT OUTER JOIN T_BD_STOCKSTATUS_L TTL ON TT.FSTOCKSTATUSID = TTL.FSTOCKSTATUSID
— AND TTL.FLOCALEID = 2052
— )
LEFT OUTER JOIN T_BD_LOTMASTER TL ON TI.FLOT = TL.FMASTERID
AND TL.FUSEORGID = TI.FSTOCKORGID
)
AND TL.FBIZTYPE = ‘1’
)
LEFT OUTER JOIN T_ENG_BOM TB ON TI.FBOMID = TB.FMASTERID
AND TI.FSTOCKORGID = TB.FUSEORGID
)
LEFT OUTER JOIN T_BD_FLEXSITEMDETAILV TAXP ON TI.FAUXPROPID = TAXP.FID
WHERE TI.FBASEQTY > 0 )
–AND TT.FTYPE NOT IN ‘1’, ‘5’ )
)
AND TM.FMATERIALID IN @FMATERIALID )
AND TI.FSTOCKORGID IN @FSTOCKORGID )
AND TI.FSTOCKSTATUSID IN 10000, 115132 )
)
)
)
ORDER BY TM.FMATERIALID ASC;