金蝶K/3 固定置产相关SQL语句

 

金蝶K/3 固定置产相关SQL语句

 

 

  select *  from vw_fa_card --固定置产打印原始数据

select FAssetID,FAssetNumber,FAssetName,FGroupName,FUnit,FNum,FLocationName,FGuid  from vw_fa_card --序号，资产编码，资产名称，类别，单位，数量，使用部门，Guid


  select * from vw_AssetInvent_FACardGroup

    select *  from vw_fa_card where FGuid = '261d78be-d938-401a-9243-2a7dac6ba389'

  select FValue,* from t_SystemProfile where FKey = 'FPrintCodeClass' and FCategory = 'FA' --获取打印条码设置，条形码还是二维码

  --固定置产打印原始数据,整理版本
SELECT FAlterID AS FDeptName, CONVERT(INT, FNum) AS FPrintQty
    , CONVERT(INT, FNum) AS FNum, FAssetNumber
    , CONVERT(varchar(100), V.FDate, 23) AS FDate
    , FWorkBookID, FGroupName, FAssetName, FUnit, FLocationName
    , FEconomyUseName, FStatusName, FAlterModeName, FWorkCenter = twc.FName
    , FCostCenter = tcc.FName, FModel, FProductingArea
    , FVender, FManufacturer, V.FAssetID, FOrgVal
    , convert(int, FLifePeriods) AS FLifePeriods
    , CONVERT(varchar(100), V.FBeginUseDate, 23) AS FBeginUseDate
    , FExplanation
    , (
        SELECT FValue
        FROM t_SystemProfile
        WHERE FKey = 'CompanyName'
    ) AS FAccountName
FROM vw_fa_card V
    LEFT JOIN t_WorkCenter twc ON twc.FItemID = v.FWorkCenterID
    LEFT JOIN t_BASE_CostCenter tcc ON tcc.FItemID = v.FCostCenterID
    LEFT JOIN (
        SELECT FAssetID, COUNT(1) AS FDetailNum
        FROM t_FaCardDetail
        WHERE FState = 1
        GROUP BY FAssetID
    ) tfc
    ON v.FAssetID = tfc.FAssetID
    INNER JOIN (
        SELECT FAssetID, MAX(fdate) AS fdate
        FROM vw_fa_card
        GROUP BY FAssetID
    ) t2
    ON v.FAssetID = t2.FAssetID
        AND v.fdate = t2.fdate
WHERE FWorkBookID = '01'
    AND FNum != 0
ORDER BY FAssetNumber

select t1.*,t2.FName_CHS,t2.FName_CHT,t2.FName_EN from (SELECT FProjectID,FEntryID,FBarCodeKey,FKey,FKey as FFieldName,FCtlType,FBarCodeName,FTableName,FListTableAlias,FListColName,FMustField,FRelTableName,FRelPrimaryField,FRelDisplayField  
                                                FROM ICBarCodeFieldMapping 
                                                WHERE FProjectID=20 )t1
                                                left join t_AssetBarCodeFields  t2 on t1.FKey=t2.FKey 
                                                where t2.FBarCodeClass = '二维码'
                                                ORDER BY FEntryID


SELECT [FProjectID],[FProjectName],[FUserID],[FClassTypeID],[FIsSystem],[FDelete],[FLogo],[FShowText]  FROM ICBarCodeProject WHERE FDelete=0 AND FProjectID=20And FBarCodeClass = '二维码'

select * from t_AssetBarCodeFields where FMustField=0 and FBarCodeClass = '二维码' and FName_CHS not in('流水号','分隔符','条码规则') and FKey <>'FRuleAssetNumber'

select t1.*,t2.FName_CHS,t2.FName_CHT,t2.FName_EN from (SELECT FProjectID,FEntryID,FBarCodeKey,FKey,FKey as FFieldName,FCtlType,FBarCodeName,FTableName,FListTableAlias,FListColName,FMustField,FRelTableName,FRelPrimaryField,FRelDisplayField  
                                                FROM ICBarCodeFieldMapping 
                                                WHERE FProjectID=20 )t1
                                                left join t_AssetBarCodeFields  t2 on t1.FKey=t2.FKey 
                                                where t2.FBarCodeClass = '二维码'
                                                ORDER BY FEntryID