云星空 按组织查询即时库存

SELECT t0.FMATERIALID fmaterialid_id,
       st04.FMASTERID fmasterid_id,
	   st04.FNUMBER fmaterialid_no,
       t0.FSTOCKID fstockid_id,
       t0.FLOT flot_id,
       t0.FSTOCKUNITID fstockunitid_id,
       t0.FQTY fqty,
       t0.FAVBQTY favbqty,
       t0.FSTOCKSTATUSID fstockstatusid_id,
       t0.FSTOCKORGID fstockorgid_id,
       t0.FOWNERTYPEID fownertypeid,
       t0.FOWNERID fownerid_id,
       t0.FKEEPERTYPEID fkeepertypeid,
       t0.FKEEPERID fkeeperid_id,
       t0.FBASEQTY fbaseqty,
       t0.FBASELOCKQTY fbaselockqty,
       t0.FBASEAVBQTY fbaseavbqty,
       t0.FSTOCKORGID fstockorgid,
       t0.FOBJECTTYPEID fobjecttypeid,
       t0.FID fid
       ,ROW_NUMBER() OVER (ORDER BY t0.FID ASC) fidentityid
	   ,CASE st05.FSTOCKID WHEN t0.FSTOCKID THEN '1' ELSE '0' END FISDEFAULTSTOCK
FROM T_STK_INVENTORY t0
    LEFT OUTER JOIN T_BD_MATERIAL st04
        ON   st04.FMATERIALID = t0.FMATERIALID
	LEFT JOIN t_BD_MaterialStock st05
	    ON st05.FMATERIALID = t0.FMATERIALID

WHERE 1=1 AND FSTOCKSTATUSID!=10006 /*不含报废品*/
AND (
          (
              (
                  (
                      (
                          (
                              (
                               --AND t0.FSTOCKORGID IN ( @FSTOCKORGID ) AND 
								  t0.FSTOCKORGID IN ( 1, 100436, 105552, 105553 )
                              )
                             --AND (st04.FNUMBER LIKE N'%'+@FMATERIALID+'%')
                          )
                        /* AND
                          (
                              (t0.FBASEQTY <> 0)
                              OR (t0.FSECQTY <> 0)
                          )*/
                      )
                      AND FISEFFECTIVED = '1'
                  )
                --  AND (st04.FNUMBER LIKE N'%0101-00000927%')
              )
              AND EXISTS
(
    SELECT FID
    FROM
    (
        SELECT t0.FID
        FROM T_STK_INVENTORY t0
        WHERE t0.FSTOCKORGID IN ( 1, 100436, 105552, 105553 )
    ) txs
    WHERE txs.FID = t0.FID
)
          )
          AND
          (
              t0.FSTOCKORGID IN ( 100436, 1, 0 )
              AND t0.FOBJECTTYPEID = 'STK_Inventory'
          )
      )