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;