-- 自动获得查询K3各种单据的SQL语句,自动得到相关字段,自动得到表关联关系!!!非常的方便,谁用谁知道.
-- 注意,
-- 1.将自动得到的SQL语句 Form 前面的逗号去掉
-- 2.可能有一条CASE语句,有点问题,将其屏蔽掉即可.(不知为何K3就那一条CASE语句有问题,弄清楚了)
--此问题今天终于弄清楚了,原来在查询分析工具中 工具/选项/结果 将每列最多字符数 设置为8000 即可!
--每列最多字符数 默认值是256 ,所以自动获得的CASE语句会截断256 字符,当然不全了,呵呵,终于找到原因.
-- 作者:诗欢 QQ:46942064 K3资料共享群TOP500:21011867
-- select FTemplateID,* From ICListTemplate Where FName like '%销售订单%' --得到FTemplateID
--
-- select FID,* from ICTransActionType where FName like '%销售订单%' --单据事物类型表 得到FName
-- --调用实例
-- --exec CreateSql 61,'LAS-04002/07','销售订单'
-- select FTemplateID,* From ICListTemplate Where FName like '%销售订单%' --得到FTemplateID
--
-- select FID,* from ICTransActionType where FName like '%销售订单%' --单据事物类型表 得到FName
--
-- select FTemplateID,* From ICListTemplate Where FName like '%发票%' --得到FTemplateID
--
-- select FID,* from ICTransActionType where FName like '%发票%' --单据事物类型表
--
--
-- exec CreateSql 62,'PSEFP000249','销售发票普通'
-- --单据类型
-- select top 10 Finterid,FtranType,FbillNo,t2.Fid,Fname,Ftype,FtemplateID,FvchTemplateID
-- From ICStockBill t1 inner join ICTransActionType t2 on t1.FTranType=t2.FID
-- order by t1.FTranType
--
-- --单据类型
CREATE PROCEDURE CreateSql
--Alter Procedure CreateSql
@FTemplateID int = 61,
@FBillNo varchar(20) = 'LAS-04002/07',
@OrderName varchar(20) = '销售订单'
AS
--ICChatBillTitle查询字段
select * from
(
select -100 AS HeadFInterID,'' AS BodyFInterID,'select ' as sql
union all
select TOP 1000 FInterID AS HeadFInterID,'' AS BodyFInterID,
case when Faction <> '' then Faction + ' AS [' + substring(FColCaption,1,len(FColCaption)-1) + '],'
else
case when FColCaption='$' and FColName='FInterID' then FTableAlias + '.' + FName + ' AS [单据内码],'
when FColCaption='$' and FColName='FEntryID' then FTableAlias + '.' + FName + ' AS [单据分录号],'
when FColCaption='$' and (FColName<>'FEntryID' and FColName<>'FInterID') then FTableAlias + '.' + FName + ' AS [' + FName + '],'
else FTableAlias + '.' + FName + ' AS [' + substring(FColCaption,1,len(FColCaption)-1) + '],'
end
end
as SQL
from ICChatBillTitle -- 叙事簿的显示字段
where FTypeID IN (@FTemplateID) and FName<>''
--order by FInterID
UNION ALL
select 9999 AS HeadFInterID,'' AS BodyFInterID,'from ' as sql
union all
select TOP 1000 10000 as HeadFInterID , FInterID AS BodyFInterID,
case when FInterID < 0 then FTableName + ' ' + FTableNameAlias + ' '
else ''
end +
case when FLogic='=' then 'Inner Join'
when FLogic='*=' then 'left outer Join'
when FLogic='=*' then 'right outer Join'
when FLogic='*=*' then 'full outer Join'
else ' '
end
+ ' ' +
FTableName11 + ' ' + FTableNameAlias11
+ ' on ' + FTableNameAlias + '.'+ FFieldName + '=' + FTableNameAlias11 + '.' + FFieldName11 as SQL
from ICTableRelation --叙事簿的过滤条件 表关联关系
where Ftypeid in (@FTemplateID) and FTableNameAlias11<>'##BASE##'
--order by Finterid
union
select 10001 AS HeadFInterID,'' AS BodyFInterID,'Where ' as sql
union
select 10002 as HeadFInterID , FInterID AS BodyFInterID,
' ' + FTableNameAlias11+'.FStandard=1' as sql from ICTableRelation where Ftypeid in (@FTemplateID) and FTableNameAlias11<>'##BASE##'
and FTableName11='t_MeasureUnit' and FFieldName11='FUnitGroupID'
union
select 10003 as HeadFInterID , 10002 AS BodyFInterID,
'and ' + 'v1.FTranType= ' + cast((select FID from ICTransActionType where FName like @OrderName) as char(4)) as sql
union
select 10004 as HeadFInterID , 10003 AS BodyFInterID,
'and ' + 'v1.FBillNo= ''' + @FBillNo + ''' ' as sql
) as TT order by HeadFInterID,BodyFInterID
--*************************************************
-- --调用实例
-- --exec CreateSql 61,'LAS-04002/07','销售订单'
--得到的SQL语句 如下: 表关系清析,字段信息完整!!!
select
v1.FBillNo+'['+t16.FNumber+']' AS [单据编号、物料代码],
v1.FBillNo+'['+CONVERT(VARCHAR(255),u1.FEntryID)+']' AS [单据编号、分录内码],
v1.FTranType AS [事务类型],
v1.FInterID AS [单据内码],
u1.FEntryID AS [单据分录号],
case when v1.FCheckerID>0 then '※' when v1.FCheckerID<0 then '※' else '' end AS [审核标志],
t4.FNumber AS [购货单位代码],
case when v1.FStatus = 3 then '※' else '' end AS [关闭标志],
v1.Fdate AS [日期],
t4.FName AS [购货单位],
t5.FName AS [销售方式],
t7.FName AS [交货方式],
v1.FBillNo AS [单据编号],
t8.FName AS [币别],
v1.FFetchAdd AS [交货地点],
t10.FName AS [主管],
t11.FName AS [部门],
t12.FName AS [业务员],
t13.FName AS [制单],
t14.FName AS [结算方式],
v1.FExchangeRate AS [汇率],
t16.FShortNumber AS [产品短代码],
t16.Fname AS [产品名称],
t16.Fmodel AS [规格型号],
t19.FName AS [单位],
u1.Fauxqty AS [数量],
u1.Fauxprice AS [单价],
u1.Famount AS [金额],
u1.FTaxRate AS [折扣率%],
u1.FUniDiscount AS [单位折扣额],
u1.FTaxAmount AS [折扣额],
u1.FFinalAmount AS [折后金额],
t25.FName AS [基本单位],
u1.FQty AS [基本单位数量],
CASE WHEN u1.FLockFlag=1 THEN '※' ELSE '' END AS [锁库标志],
t24.FName AS [审核人],
u1.FDate AS [交货日期],
u1.FNote AS [备注],
t16.FQtyDecimal AS [FQtyDecimal],
t16.FPriceDecimal AS [FPriceDecimal],
t16.FItemID AS [产品内码],
t16.FNumber AS [产品长代码],
case when v1.FCancellation=1 then '※' else '' end AS [作废标志],
CASE WHEN EXISTS(SELECT TOP 1 * FROM ICMO WHERE FOrderInterID = v1.FInterID AND FTranType = 85) THEN '※' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM SEOutStock WHERE FCnnInterID = v1.FInterID AND FTranType = 83) THEN '※' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM POrequest WHERE FOrderInterID = v1.FInterID AND FTranType = 70) THEN '※' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICSale WHERE FOrderInterID = v1.FInterID AND FTranType = 86) THEN '※' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICSale WHERE FOrderInterID = v1.FInterID AND FTranType = 80) THEN '※' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FOrderInterID = v1.FInterID AND FTranType = 21) THEN '※' ELSE CASE WHEN EXISTS(SELECT TOP 1 * FROM ICStockBill WHERE FOrderInterID = v1.FInterID AND FTranType = 2) THEN '※' ELSE '' END END END END END END END AS [关联标志],
CASE WHEN v1.FPORFQInterID<>0 THEN v1.FRelationBillNo ELSE '' END AS [销售报价单号],
t30.FContractNo AS [合同号],
(Select Case When v1.FMrpClosed = 1 Then '※' Else '' End) AS [业务关闭状态],
t11.FNumber AS [部门代码],
v1.FContractID AS [合同号ID],
CASE WHEN v1.FOrderAffirm=1 THEN '※' ELSE '' END AS [确认标志],
u1.FCess AS [税率(%)],
v1.FTransitAheadTime AS [运输提前期],
u1.FAdviceConsignDate AS [建议交货日期],
u1.FMapNumber AS [对应代码],
u1.FMapName AS [对应名称],
u1.FBomInterID AS [客户BOM内码],
(Select FBOMNumber From ICBOM Where FInterID =u1.FBomInterID) AS [客户BOM],
t65.FName AS [订货机构],
v1.FPOOrdBillNo AS [分销订单号],
u1.FStockQty AS [基本单位出库数量],
u1.FCommitQty AS [基本单位关联数量],
v1.FCustID AS [FCustID],
CASE WHEN u1.FATPDeduct=1 THEN '※' ELSE '' END AS [冲减标志],
Case WHEN t16.FCUUnitID=0 THEN '' Else t500.FName end AS [常用单位],
CASE WHEN t16.FCUUnitID=0 THEN '' Else u1.FQty/t500.FCoefficient end AS [常用单位数量],
Case WHEN t16.FCUUnitID=0 THEN '' Else u1.FStockQty/t500.FCoefficient end AS [常用单位出库数量],
Case WHEN t16.FCUUnitID=0 THEN '' Else u1.FCommitQty/t500.FCoefficient end AS [常用单位关联数量],
t1634.FName AS [生产车间:],
v1.FHeadSelfS0130 AS [客方合同:],
t1636.FName AS [类别],
t1637.FNUMBER AS [购货单位代码],
v1.FHeadSelfS0133 AS [客户分代码],
v1.FHeadSelfS0134 AS [出货日期],
t1640.FName AS [电镀要求],
u1.FEntrySelfS0132 AS [客方货号],
u1.FEntrySelfS0133 AS [完整客方货号],
u1.FEntrySelfS0134 AS [套装号],
u1.FEntrySelfS0135 AS [赠送标志],
u1.FEntrySelfS0136 AS [出厂单价],
u1.FEntrySelfS0137 AS [出厂金额],
u1.FEntrySelfS0138 AS [Desc_HK],
u1.FEntrySelfS0139 AS [是否取消],
u1.FEntrySelfS0140 AS [接单货号],
u1.FEntrySelfS0141 AS [重开单标志],
u1.FEntrySelfS0142 AS [电镀厚度],
u1.FEntrySelfS0143 AS [id_number]
from
SEOrder v1 Inner Join SEOrderEntry u1 on v1.FInterID=u1.FInterID
left outer Join t_RpContract t30 on v1.FContractID=t30.FContractID
left outer Join t_Organization t4 on v1.FCustID=t4.FItemID
left outer Join t_SubMessage t5 on v1.FSaleStyle=t5.FInterID
left outer Join t_SubMessage t7 on v1.FFetchStyle=t7.FInterID
Inner Join t_Currency t8 on v1.FCurrencyID=t8.FCurrencyID
left outer Join t_Emp t10 on v1.FMangerID=t10.FItemID
left outer Join t_Department t11 on v1.FDeptID=t11.FItemID
left outer Join t_Emp t12 on v1.FEmpID=t12.FItemID
left outer Join t_User t13 on v1.FBillerID=t13.FUserID
left outer Join t_Settle t14 on v1.FSettleID=t14.FItemID
Inner Join t_ICItem t16 on u1.FItemID=t16.FItemID
Inner Join t_MeasureUnit t19 on u1.FUnitID=t19.FItemID
Inner Join t_MeasureUnit t25 on t16.FUnitGroupID=t25.FUnitGroupID
left outer Join t_user t24 on v1.FCheckerID=t24.FUserID
left outer Join PORFQ t20 on v1.FPORFQInterID=t20.FInterID
left outer Join t_SonCompany t65 on v1.FRelateBrID=t65.FItemID
left outer Join t_MeasureUnit t500 on t16.FCUUnitID=t500.FItemID
left outer Join t_Currency t504 on v1.FCurrencyID=t504.FCurrencyID
left outer Join t_Item t1634 on v1.FHeadSelfS0129=t1634.FItemID
left outer Join t_Item t1636 on v1.FHeadSelfS0131=t1636.FItemID
left outer Join t_Organization t1637 on v1.FCustID=t1637.FItemID
left outer Join t_Item t1640 on u1.FEntrySelfS0131=t1640.FItemID
Where
t25.FStandard=1
and v1.FTranType= 81
and v1.FBillNo= 'LAS-04002/07'
v
