摘要：
IssueDoc_IssueDocLines_LineNum.Value5)、fn_GetSegName参数说明：参数1、参数2固定值，参数3为扩展字段段数方式二：采用SQL用户自定义函数实现1)、创建SQL函数：函数名称[Fn_YM_IssueDetailDescFlex]--=================================================--获取生产领料单明细物料私有段6值集值名称--=================================================IFEXISTSDROPFUNCTIONFn_YM_IssueDetailDescFlexGOCREATEFUNCTIONdbo.Fn_YM_IssueDetailDescFlexRETURNSNVARCHARASBEGINDECLARE@DescFlexFieldStrNVARCHARselect@DescFlexFieldStr=castfromMO_IssueDocAinnerjoin[MO_IssueDocLine]Boninnerjoin[CBO_ItemMaster]ConinnerjoindonwhereA.[DocNo]=@DocNoandb.[LineNum]=@lineNoRETURN@DescFlexFieldStrEND函数2)、SQL验证函数可用性selectdbo.Fn_YM_IssueDetailDescFlex--参数1：生产领料单单号；参数2：生产领料单行号3)、U9中注册自定义函数注释：注册自定义函数后,需重启IIS方可在U9产品中使用;路径:D:yonyouU9V50Portalinscript.xmlD:yonyouU9V50PortalApplicationServerinscript.xml注册文件:script.xml注册函数:4)、调用自定义函数[Fn_YM_IssueDetailDescFlex]selectFn_YM_IssueDetailDescFlexfromUFIDA::U9::MO::Issue::IssueDocwhereUFIDA::U9::MO::Issue::IssueDoc.DocNo=Fields!
UBF打印模板中,单据自定义扩展字段显示均为扩展字段值集值编码，而在实际运用过程中打印时需要显示扩展字段名称，具体实现方法如下
方式一：采用SQL系统定义函数[dbo].[fn_GetSegName]实现<通用>
1)、检查[fn_GetSegName]函数是否存在(SQL位置：ERP数据库->可编译性->函数->标量值函数)
2)、在U9中注册函数[fn_GetSegName]；
文件路径: D:yonyouU9V50Portalinscript.xml D:yonyouU9V50PortalApplicationServerinscript.xml 注册文件: script.xml 注册函数: <sql name="fn_GetSegName" method="fn_GetSegName" owner="dbo" />
3)、注册自定义函数后,需重启IIS方可在U9产品中使用；
4)、打印模板自定义字段调用函数[fn_GetSegName]方法；
select fn_GetSegName(UFIDA::U9::MO::Issue::IssueDoc.IssueDocLines.Item.DescFlexField.CombineName ,'#@#',56)
from UFIDA::U9::MO::Issue::IssueDoc
where UFIDA::U9::MO::Issue::IssueDoc.DocNo=Fields!IssueDoc_DocNo.Value and UFIDA::U9::MO::Issue::IssueDoc.IssueDocLines.LineNum=Fields!IssueDoc_IssueDocLines_LineNum.Value
5)、fn_GetSegName(DescFlexField_CombineName ,'#@#',56) 参数说明：参数1、参数2固定值，参数3为扩展字段段数(公共段1-50,私有段从51段开始)
方式二：采用SQL用户自定义函数实现<定制>
1)、创建SQL函数：函数名称[Fn_YM_IssueDetailDescFlex]
--================================================= --获取生产领料单明细物料私有段6值集值名称 --================================================= IF EXISTS (SELECT * FROM sysobjects WHERE name = N'Fn_YM_IssueDetailDescFlex') DROP FUNCTION Fn_YM_IssueDetailDescFlex GO CREATE FUNCTION dbo.Fn_YM_IssueDetailDescFlex ( @DocNo NVARCHAR(50), --生产领料单单号 @lineNo NVARCHAR(50)--生产领料单行号 ) RETURNS NVARCHAR(255) AS BEGIN DECLARE @DescFlexFieldStr NVARCHAR(255) select @DescFlexFieldStr=cast( D.[name] as NVARCHAR(255) ) fromMO_IssueDoc A inner join [MO_IssueDocLine] B on (B.[issueDoc]=A.[ID]) inner join [CBO_ItemMaster] C on (B.[item]=C.[ID]) inner join (select[Base_DefineValue].[Code],[Name] from[Base_DefineValue_Trl] inner join [Base_DefineValue] on ([Base_DefineValue].[ID]=[Base_DefineValue_Trl].[ID]) inner join [Base_ValueSetDef] on ([Base_ValueSetDef].[ID]=[ValueSetDef]) where [Base_ValueSetDef].[Code]='034' ) d on (C.[DescFlexField_PrivateDescSeg6]=d.[code]) where A.[DocNo]=@DocNo and b.[LineNum]=@lineNo RETURN @DescFlexFieldStr END
2)、SQL验证函数可用性
select dbo.Fn_YM_IssueDetailDescFlex('GQLL-201908070034','10') -- 参数1：生产领料单单号；参数2：生产领料单行号
3)、U9中注册自定义函数
注释：注册自定义函数后,需重启IIS方可在U9产品中使用;
路径: D:yonyouU9V50Portalinscript.xml D:yonyouU9V50PortalApplicationServerinscript.xml 注册文件:script.xml 注册函数: <sql name="Fn_YM_IssueDetailDescFlex" method="Fn_YM_IssueDetailDescFlex" owner="dbo" />
4)、调用自定义函数[Fn_YM_IssueDetailDescFlex]
select Fn_YM_IssueDetailDescFlex(UFIDA::U9::MO::Issue::IssueDoc.DocNo,UFIDA::U9::MO::Issue::IssueDoc.IssueDocLines.LineNum) from UFIDA::U9::MO::Issue::IssueDoc where UFIDA::U9::MO::Issue::IssueDoc.DocNo=Fields!IssueDoc_DocNo.Value and UFIDA::U9::MO::Issue::IssueDoc.IssueDocLines.LineNum=Fields!IssueDoc_IssueDocLines_LineNum.Value
