物料[OA_MATERIAL]视图

--物料[OA_MATERIAL] 过滤条件为组织 和 物料属性枚举值（标准采购申请单：物料属性值为外购【1】、自制【2】、委外【3】资产采购申请单：物料属性值为资产【10】）

--FMANUM ：物料编码

--FMANAME ：物料名称

--FORGNUM ：组织编码

--FSPECIFICATION ：规格型号

--FERPCLSID ：物料属性枚举值

--FCAPTION ：物料属性说明

--FUNITNAME ：采购单位名称

--FGYSISENABLE ：是否启用辅助属性供应商

--FGGISENABLE ：是否启用辅助属性规格

IF EXISTS (

SELECT *

FROM sysobjects

WHERE name = 'OA_MATERIAL'

)

DROP VIEW OA_MATERIAL

GO

CREATE VIEW OA_MATERIAL

AS

SELECT A.FNUMBER AS FMANUM, B.FNAME AS FMANAME, C.FNUMBER AS FORGNUM, B.FSPECIFICATION, D.FERPCLSID

, E.FCAPTION, G.FNAME AS FUNITNAME, ISNULL(H.FISENABLE, 0) AS FGYSISENABLE

, ISNULL(J.FISENABLE, 0) AS FGGISENABLE

FROM T_BD_MATERIAL A

LEFT JOIN T_BD_MATERIAL_L B

ON A.FMATERIALID = B.FMATERIALID

AND FLOCALEID = '2052'

LEFT JOIN T_ORG_Organizations C ON A.FUSEORGID = C.FORGID

LEFT JOIN t_BD_MaterialBase D ON A.FMATERIALID = D.FMATERIALID

LEFT JOIN (

SELECT b.FCAPTION, a.FVALUE

FROM T_META_FORMENUMITEM a

LEFT JOIN T_META_FORMENUMITEM_L b

ON a.FENUMID = b.FENUMID

AND b.FLOCALEID = 2052

WHERE a.FID = 'ac14913e-bd72-416d-a50b-2c7432bbff63' --BD_物料属性 枚举类型的 内码

) E

ON D.FERPCLSID = E.FVALUE

LEFT JOIN t_bd_MaterialPurchase F ON A.FMATERIALID = F.FMATERIALID

LEFT JOIN T_BD_UNIT_L G

ON F.FPURCHASEUNITID = G.FUNITID

AND G.FLOCALEID = '2052'

LEFT JOIN (

SELECT FMATERIALID, FISENABLE

FROM t_BD_MaterialAuxPty

WHERE FAUXPROPERTYID = 100002 --辅助属性供应商

) H

ON A.FMATERIALID = H.FMATERIALID

LEFT JOIN (

SELECT FMATERIALID, FISENABLE

FROM t_BD_MaterialAuxPty

WHERE FAUXPROPERTYID = 100004 --辅助属性规格

) J

ON A.FMATERIALID = J.FMATERIALID

WHERE A.FDOCUMENTSTATUS = 'C'

AND A.FFORBIDSTATUS = 'A'

AND D.FISPURCHASE = 1

GO

GRANT SELECT ON OA_MATERIAL TO db_oaview