/*-----------------------------------------------------------------------------
* DESCRIPTION：采购订单的“品质要求”字段可随基础资料带出并可修改的实现方式
* BY：Suntime
* DATE：2006-12
* 功能：采购订单的“品质要求”字段可随基础资料带出，并可修改
* 客户要求：应客户要求，设置此字段的目的
1、向其供应商和加工商在业务单据上说明相关物料的生产、加工要求
2、在一段时间内，某一物料的外购或委外品质要求是不变的
3、要减少输单工作量，订单上的“品质要求”字段可随基础资料带出，并可修改。
4、新增或选单生成业务单据时，最好能直接在新单据上直接显示此字段相关信息
* 实现方式：
1、在物料基础资料上新增“品质要求”字段，
2、在采购订单上新增“品质要求”字段，新增或选单生成业务单据时,数据值来源于物料的“品质要求”，并可修改。
-----------------------------------------------------------------------------*/
/*在物料基础资料上新增"品质要求"字段*/
if not exists (SELECT * FROM sysobjects t1 ,syscolumns t2 where t1.id=t2.id and t1.name='t_ICItemCustom' and t2.Name='QualityRequest')
Alter Table t_ICItemCustom Add QualityRequest varchar(800)
go
DECLARE @FPropID INT
SELECT @FPropID = ISNull(MAX(FPropID)+1,102) FROM t_ItemPropDesc WHERE FItemClassID=4 And (FPropID>=102 And FPropID<500)
PRINT @FPropID
INSERT INTO t_ItemPropDesc (FItemClassID,FPropID,FName,FName_en,FName_cht,FSQLColumnName,FDataType,FPrecision,FScale,FActualType,FActualSize,FBehavior,FSearch,FSrcTable,FSrcField,FDisplayField,FDefaultValue,FPageName,FPageName_en,FPageName_cht,FComCall,FIsShownList,FViewMask)
VALUES (4,@FPropID, '品质要求', '品质要求', '品质要求', 'QualityRequest', 200, 800, 0, 200, 800, NULL, 0, NULL, NULL, NULL, NULL, '1.基本资料', '1. Basic Data', '1.基本資料', NULL, 0, 0)
go
If Not Exists(Select 1 from t_FieldDescription where FTableID =60 And FFieldName='QualityRequest')
Insert into t_FieldDescription(FTableID,FFieldName,FFieldType,FDescription,FDescription_CHT,FDescription_EN,FSefDefSign) values(60,'QualityRequest','STRING','品质要求','品质要求','品质要求',1)
go
UPdate t_ItemPropDesc set FBehavior=1 Where FItemClassID = 4 and Fname='品质要求'
Go
exec SP_CREATE_ICItem_VIEW
go
exec SP_CREATE_ICItem_TRIGGER
go
/*在采购订单上新增"品质要求"字段*/
--向单据模板记录表（ICTemplateEntry）中插入 QualityRequest 字段
DECLARE @FCtlIndex INT,
@FCtlOrder INT
SELECT @FCtlIndex = MAX(FCtlIndex)+1 FROM ICTemplateEntry WHERE FID='P02'
SELECT @FCtlOrder = MAX(FCtlOrder)+1 FROM ICTemplateEntry WHERE FID='P02'
PRINT @FCtlIndex
PRINT @FCtlOrder
Insert Into ICTemplateEntry(FID,FCtlIndex,FCtlOrder,FHeadCaption,FHeadCaption_CHT,FHeadCaption_EN,FCtlType,FLookUpCls,FNeedSave,FValueType,FSaveValue,FFieldName,FWidth,FEnable,FRelateOutTbl,FPrint,FMustInput,FNeedCount,FFilter,FRelationID,FAction,FDefaultCtl,FVisForBillType, FStatCount,FFormat,FMaxValue,FMinValue)
Values('P02',@FCtlIndex,@FCtlOrder,'品质要求','品质要求','品质要求',30,-1,-1,0,0,'QualityRequest',1500,48,0,-1,0,0,'','FItemID','.,QualityRequest',1,31,0,'','','')
go
--向采购订单分录表(POOrderEntry)中添加 QualityRequest 字段
if not exists (SELECT * FROM sysobjects t1 ,syscolumns t2 where t1.id=t2.id and t1.name='POOrderEntry' and t2.Name='QualityRequest')
Alter Table POOrderEntry Add QualityRequest VARCHAR(800) NULL
go
--向出套打格式字段表（GLNoteCitation）\单据序时薄表（ICChatBillTitle）中添加 QualityRequest 字段
DELETE FROM GLNoteCitation WHERE FCode='QualityRequest' AND FTemplateID='P02' AND FRelationID IN (1,2,3,20)
DECLARE @FID INT,@FInterID INT
SELECT @FID = MAX(FID)+1 FROM GLNoteCitation WHERE FTemplateID='P02'
SELECT @FInterID = MAX(FInterID)+1 FROM ICChatBillTitle Where FTypeID=81
PRINT @FID
PRINT @FInterID
Insert Into GLNoteCitation(FTemplateID,FID,FNoteTypeID,FCitationName,FInEntry,FIsMoney,FCode,FCtlIndex,FIsSum,FKeyFieldName,FExtFieldName,FTableName,FRelationID,FCitationName_CHT,FCitationName_EN)
Values ('P02',@FID ,50,'品质要求$',1,0 ,'QualityRequest',41,0,'QualityRequest','', '', 2,'品质要求$','品质要求$')
Insert Into ICChatBillTitle(FInterID,FTypeID,FColCaption,FColCaption_CHT,FColCaption_EN,FMergeable,FColName,FName,FTableName,FTableAlias,FColType,FItemClassID,FReturnDataType,FCtlIndex,FStatistical,FNeedCount,FCountPriceType, FVisForQuest,FVisForOrder,FFormat)
Values(@FInterID,81,'品质要求$','品质要求$','品质要求$',0,'QualityRequest','QualityRequest','POOrderEntry','u1',1,-1,0,41,1,0,0,1,1,'')
go
--整理单据求和统计公式计算表（ICVoucherTField）中先前添加 QualityRequest 字段记录
Delete FROM ICVoucherTField WHERE FBillType=71 AND FIsUserDefine=1 AND FUserDefineField='QualityRequest'
go
--向表描述表（t_TableDescription）和字段描述表（t_FieldDescription）添加QualityRequest 字段
IF Exists(Select t2.* From t_TableDescription t1, t_FieldDescription t2 Where t1.FTableID=t2.FTableID And t1.FTableName ='POOrderEntry' And t2.FFieldName ='QualityRequest')
Begin
UPDATE t2 SET FFieldType ='STRING'
,FDescription ='品质要求',FDescription_CHT ='品质要求',FDescription_EN ='品质要求'
,FFieldNote = '品质要求',FFieldNote_CHT = '品质要求',FFieldNote_EN = '品质要求'
From t_TableDescription t1, t_FieldDescription t2
Where t1.FTableID=t2.FTableID And t1.FTableName ='POOrderEntry' And t2.FFieldName ='QualityRequest'
End
Else
Begin
INSERT INTO t_FieldDescription(FTableID,FFieldName,FFieldType
,FDescription,FDescription_CHT,FDescription_EN
,FFieldNote,FFieldNote_CHT,FFieldNote_EN
) Select t1.FTableID,'QualityRequest','STRING'
,'品质要求','品质要求','品质要求'
,'品质要求','品质要求','品质要求'
From t_TableDescription t1
Where t1.FTableName ='POOrderEntry'
End
Go
--删除并重整选单流转表（ICSelBills）中的 QualityRequest 字段记录
Delete ICSelBills Where FID ='P02' and FFieldName='1007100' and FDstCtlField='QualityRequest' and FSelType=0
Delete ICSelBills Where FID ='P02' and FFieldName='32' and FDstCtlField='QualityRequest' and FSelType=0
Delete ICSelBills Where FID ='P02' and FFieldName='FContractID' and FDstCtlField='QualityRequest' and FSelType=0
Delete ICSelBills Where FID ='P02' and FFieldName='FContractID' and FDstCtlField='QualityRequest' and FSelType=0
Delete ICSelBills Where FID ='P02' and FFieldName='FRequestInterID' and FDstCtlField='QualityRequest' and FSelType=0
Insert Into ICSelBills(FID,FFieldName,FDstCtlField,FDK,FSelType,FColName,FName,FTableName,FTableAlias,FAction)
Values('P02','FRequestInterID','QualityRequest',0,0,'QualityRequest','QualityRequest','t_ICItem','t4','')
go
--设置采购订单 品质要求 字段来源于物料的 品质要求 字段
UPdate ICTemplateEntry set FRelationID='FItemID',FAction='.,QualityRequest',FDefaultctl=1 where FFieldName='QualityRequest'
go
评论
实际上工业老单的单据自定义是可以实现的，
只是要在物料和单据上新增相关字段之后
要注意修改单据模板和选单表的 Action
这段是我改过的
Insert Into ICTemplateEntry(FID,FCtlIndex,FCtlOrder,FHeadCaption,FHeadCaption_CHT,FHeadCaption_EN,FCtlType,FLookUpCls,FNeedSave,FValueType,FSaveValue,FFieldName,FWidth,FEnable,FRelateOutTbl,FPrint,FMustInput,FNeedCount,FFilter,FRelationID,FAction,FDefaultCtl,FVisForBillType, FStatCount,FFormat,FMaxValue,FMinValue)
Values('P02',@FCtlIndex,@FCtlOrder,'品质要求','品质要求','品质要求',30,-1,-1,0,0,'QualityRequest',1500,48,0,-1,0,0,'','FItemID','.,QualityRequest',1,31,0,'','','')
go
你同单据自定义产生的脚本比较一下
实际关键是这几个字段：FRelationID、FAction、FFilter
即便是单据自定义，在最后更新
UPdate ICTemplateEntry set FRelationID='FItemID',FAction='.,QualityRequest',FDefaultctl=1 where FFieldName='QualityRequest'
就行了
这段脚本主要解决，手工新增单据时，“品质要求”字段可随基础资料带出并可修改
119，你再同单据自定义的“来源于已有的基础资料属性”比较看看
Insert Into ICSelBills(FID,FFieldName,FDstCtlField,FDK,FSelType,FColName,FName,FTableName,FTableAlias,FAction)
Values('P02','FRequestInterID','QualityRequest',0,0,'QualityRequest','QualityRequest','t_ICItem','t4','')
go
主要是解决选单时 采购申请单->采购订单，“品质要求”字段可随基础资料带出并可修改