## 20170720工作内容 ## 物流业务逻辑处理 select DB_ID() #### 客户表，商品表：   #### 仓储查询   SELECT [FItemID] ,[FItemClassID]：1：商户，4：商品 ,[FExternID] ,[FNumber] ,[FParentID] ,[FLevel] ,[FDetail] ,[FName] ,[FUnUsed] ,[FBrNo] ,[FFullNumber] ,[FDiff] ,[FDeleted] ,[FShortNumber] ,[FFullName] ,[UUID] ,[FGRCommonID] ,[FSystemType] ,[FUseSign] ,[FChkUserID] ,[FAccessory] ,[FGrControl] ,[FModifyTime] ,[FHavePicture] FROM [stock].[dbo].[t_Item] GO 商户组织表： SELECT TOP 1000 [FItemID] ,[FAddress] ,[FCity] ,[FProvince] ,[FCountry] ,[FPostalCode] ,[FPhone] ,[FFax] ,[FEmail] ,[FHomePage] ,[FCreditLimit] ,[FTaxID] ,[FBank] ,[FAccount] ,[FBankNumber] ,[FBrNo] ,[FBoundAttr] ,[FErpClsID] ,[FShortName] ,[FPriorityID] ,[FPOGroupID] ,[FStatus] ,[FLanguageID] ,[FRegionID] ,[FTrade] ,[FMinPOValue] ,[FMaxDebitDate] ,[FLegalPerson] ,[FContact] ,[FContactAcct] ,[FPhoneAcct] ,[FFaxAcct] ,[FZipAcct] ,[FEmailAcct] ,[FAddrAcct] ,[FTax] ,[FCyID] ,[FSetID] ,[FSetDLineID] ,[FTaxNum] ,[FPriceClsID] ,[FOperID] ,[FCIQNumber] ,[FDeleted] ,[FSaleMode] ,[FName] ,[FNumber] ,[FParentID] ,[FShortNumber] ,[FARAccountID] ,[FAPAccountID] ,[FpreAcctID] ,[FlastTradeAmount] ,[FlastRPAmount] ,[FfavorPolicy] ,[Fdepartment] ,[Femployee] ,[Fcorperate] ,[FbeginTradeDate] ,[FendTradeDate] ,[FlastTradeDate] ,[FlastReceiveDate] ,[FcashDiscount] ,[FcurrencyID] ,[FmaxDealAmount] ,[FminForeReceiveRate] ,[FminReserverate] ,[FdebtLevel] ,[FCarryingAOS] ,[FIsCreditMgr] ,[FCreditPeriod] ,[FCreditLevel] ,[FPayTaxAcctID] ,[FValueAddRate] ,[FTypeID] ,[FCreditDays] ,[FCreditAmount] ,[FStockIDAssign] ,[FStockIDInst] ,[FStockIDKeep] ,[FPaperPeriod] ,[FAlarmPeriod] ,[FLicAndPermit] ,[FOtherARAcctID] ,[FOtherAPAcctID] ,[FPreAPAcctID] ,[FSaleID] ,[FHelpCode] ,[FModifyTime] ,[FNameEN] ,[FAddrEn] ,[FCIQCode] ,[FRegion] ,[FMobilePhone] ,[FPayCondition] ,[FManageType] ,[FClass] ,[FValue] ,[FRegUserID] ,[FLastModifyDate] ,[FRecentContactDate] ,[FRegDate] ,[FFlat] ,[FClassTypeID] ,[FCoSupplierID] ,[FShareStatus] ,[FRelateUserID] ,[FBusinessUserID] ,[FNote] ,[FContactGUID] ,[FOpenID] ,[FVerifyCode] ,[FVerifyCodeTime] ,[FSource] ,[FDistrict] ,[FSex] ,[FIsSign] ,[FLatitude] ,[FLongitude] ,[F_102] FROM [AIS20170612094709].[dbo].[t_Organization] order by [FItemID] DESC ## 创建属性： SELECT TOP 1000 [FSeqID] ,[FTypeID] ,[FItemID] ,[FCreateDate] ,[FCreateUser] ,[FLastModDate] ,[FLastModUser] ,[FDeleteDate] ,[FDeleteUser] FROM [AIS20170612094709].[dbo].[t_BaseProperty] order by [FItemID] DESC /****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [FItemID] ,[FErpClsID] ,[FUnitID] ,[FUnitGroupID] ,[FDefaultLoc] ,[FSPID] ,[FSource] ,[FQtyDecimal] ,[FLowLimit] ,[FHighLimit] ,[FSecInv] ,[FUseState] ,[FIsEquipment] ,[FEquipmentNum] ,[FIsSparePart] ,[FFullName] ,[FSecUnitID] ,[FSecCoefficient] ,[FSecUnitDecimal] ,[FAlias] ,[FOrderUnitID] ,[FSaleUnitID] ,[FStoreUnitID] ,[FProductUnitID] ,[FApproveNo] ,[FAuxClassID] ,[FTypeID] ,[FPreDeadLine] ,[FSerialClassID] ,[FComCategoryID] ,[FComTypeID] ,[FSellMethod] ,[FProducingArea] ,[FComBrandID] ,[FPointsRate] ,[FComponents] ,[FGoodsNumber] ,[FPackTypeID] ,[FVIPPrice] ,[FSilverVIPPrice] ,[FGoldVIPPrice] ,[FGoodsBarCode] ,[FOperator] ,[FOperatingTime] ,[FBusinessType] ,[FBarCodeUnitID] ,[FKCCUnitID] ,[FLastUpdateTime] FROM [AIS20170612094709].[dbo].[t_ICItemBase] order by [FItemID] DESC SELECT TOP 1000 [FItemID] ,[FModel] ,[FName] ,[FHelpCode] ,[FDeleted] ,[FShortNumber] ,[FNumber] ,[FModifyTime] ,[FParentID] ,[FBrNo] ,[FTopID] ,[FRP] ,[FOmortize] ,[FOmortizeScale] ,[FForSale] ,[FStaCost] ,[FOrderPrice] ,[FOrderMethod] ,[FPriceFixingType] ,[FSalePriceFixingType] ,[FPerWastage] ,[FARAcctID] ,[FPlanPriceMethod] ,[FPlanClass] ,[FPY] ,[FPinYin] ,[FRefCost] FROM [AIS20170612094709].[dbo].[t_ICItemCore] order by [FItemID] DESC Set Nocount on IF OBJECT_ID('tempdb..#TempInventory','U') IS NOT NULL DROP TABLE #TempInventory Create Table #TempInventory( [FBrNo] [varchar] (10) NOT NULL , [FItemID] [int] NOT NULL , [FBatchNo] [varchar] (200) NOT NULL , [FMTONo] [varchar] (200) NOT NULL , [FStockID] [int] NOT NULL , [FQty] [decimal](28, 10) NOT NULL , [FBal] [decimal](20, 2) NOT NULL , [FStockPlaceID] [int] NULL , [FKFPeriod] [int] NOT NULL Default(0), [FKFDate] [varchar] (255) NOT NULL , [FMyKFDate] [varchar] (255), [FStockTypeID] [Int] NOT NULL, [FQtyLock] [decimal](28, 10) NOT NULL, [FAuxPropID] [int] NOT NULL, [FSecQty] [decimal](28, 10) NOT NULL ) Insert Into #TempInventory Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FMTONo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID, u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),500,u1.FQtyLock,u1.FAuxPropID,u1.FSecQty From ICInventory u1 where u1.FQty<>0 Insert Into #TempInventory Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FMTONo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID, u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),u1.FStockTypeID,0,u1.FAuxPropID,u1.FSecQty From POInventory u1 where u1.FQty<>0 Select u1.FAuxPropID,case when u1.FSecQty=0 then 0 else ROUND(u1.FQty/u1.FSecQty,t1.FQtyDecimal) end as FConvRate,u1.FStockTypeID,t1.FName as FMaterialName,t1.FModel as FMaterialModel,t1.FHelpCode,t1.FNote,t19.FName as FSecUnitName,t19.FNumber as FSecUnitNumber, u1.FBatchNo,u1.FMTONo,t2.FName as FStockName ,u1.FQtyLock as FBUQtyLock,u1.FQtyLock/t4.FCoefficient as FCUUQtyLock, t5.FName as FSPName,ISNULL(u1.FKFPeriod,0) AS FKFPeriod,case when isdate(u1.FKFDate)=1 then Convert(datetime,u1.FKFDate) else null end as FKFDate, case when isdate(u1.FMyKFDate)=1 then Convert(datetime,u1.FMyKFDate) else null end as FMyKFDate, t3.FName as FBUUnitName,t3.FNumber as FBUUnitNumber,ROUND(u1.FQty,t1.FQtydecimal) as FBUQty, t4.FName as FCUUnitName ,ROUND(u1.FQty/t4.FCoefficient,t1.FQtyDecimal) as FCUUQty,t1.FQtyDecimal, t1.FPriceDecimal,0 as FSumSort, Case when isdate(u1.FKFDate)=0 then '' else Convert(datetime,u1.FKFDate) + u1.FKFPeriod END AS FMaturityDate, t2.FNumber AS FStockNumber, t1.FNumber AS FMaterialNumber,t1.FNumber AS FLongNumber,t5.FNumber as FSPNumber,t4.FNumber as FCUUnitCode,t4.FMeasureunitID as FCUUnitID ,t1.FitemID ,T2.FitemID FStockID,T5.FSPID FSPID,t9.FName as FAuxPropName,t9.FNumber as FAuxPropNumber,ROUND(u1.FSecQty,t1.FQtyDecimal) AS FSecQty,t1.FSecCoefficient AS FItemSecCoefficient , t1.FGoodsBarCode AS FBarCode, t1.FOrderPrice,ROUND(t1.FOrderPrice * u1.FQty,t1.FPricedecimal) AS FStockBal From #TempInventory u1 left join t_ICItem t1 on u1.FItemID = t1.FItemID left join t_Stock t2 on u1.FStockID=t2.FItemID left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0) and t1.FDeleted=0 AND t2.FTypeID in (500,20291,20293) AND ( (t1.FHelpCode = 'CSXY') ) Order By t1.FNumber,u1.FBatchNo ,u1.FMTONo Drop Table #TempInventory Set Nocount on IF OBJECT_ID('tempdb..#TempInventory','U') IS NOT NULL DROP TABLE #TempInventory Create Table #TempInventory( [FBrNo] [varchar] (10) NOT NULL , [FItemID] [int] NOT NULL , [FBatchNo] [varchar] (200) NOT NULL , [FMTONo] [varchar] (200) NOT NULL , [FStockID] [int] NOT NULL , [FQty] [decimal](28, 10) NOT NULL , [FBal] [decimal](20, 2) NOT NULL , [FStockPlaceID] [int] NULL , [FKFPeriod] [int] NOT NULL Default(0), [FKFDate] [varchar] (255) NOT NULL , [FMyKFDate] [varchar] (255), [FStockTypeID] [Int] NOT NULL, [FQtyLock] [decimal](28, 10) NOT NULL, [FAuxPropID] [int] NOT NULL, [FSecQty] [decimal](28, 10) NOT NULL ) Insert Into #TempInventory Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FMTONo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID, u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),500,u1.FQtyLock,u1.FAuxPropID,u1.FSecQty From ICInventory u1 where u1.FQty<>0 Insert Into #TempInventory Select u1.FBrNo,u1.FItemID,u1.FBatchNo,u1.FMTONo,u1.FStockID,u1.FQty,u1.FBal,u1.FStockPlaceID, u1.FKFPeriod,ISNULL(u1.FKFDate,''),ISNULL(u1.FKFDate,''),u1.FStockTypeID,0,u1.FAuxPropID,u1.FSecQty From POInventory u1 where u1.FQty<>0 Select u1.FAuxPropID,case when u1.FSecQty=0 then 0 else ROUND(u1.FQty/u1.FSecQty,t1.FQtyDecimal) end as FConvRate,u1.FStockTypeID,t1.FName as FMaterialName,t1.FModel as FMaterialModel,t1.FHelpCode,t1.FNote,t19.FName as FSecUnitName,t19.FNumber as FSecUnitNumber, u1.FBatchNo,u1.FMTONo,t2.FName as FStockName ,u1.FQtyLock as FBUQtyLock,u1.FQtyLock/t4.FCoefficient as FCUUQtyLock, t5.FName as FSPName,ISNULL(u1.FKFPeriod,0) AS FKFPeriod,case when isdate(u1.FKFDate)=1 then Convert(datetime,u1.FKFDate) else null end as FKFDate, case when isdate(u1.FMyKFDate)=1 then Convert(datetime,u1.FMyKFDate) else null end as FMyKFDate, t3.FName as FBUUnitName,t3.FNumber as FBUUnitNumber,ROUND(u1.FQty,t1.FQtydecimal) as FBUQty, t4.FName as FCUUnitName ,ROUND(u1.FQty/t4.FCoefficient,t1.FQtyDecimal) as FCUUQty,t1.FQtyDecimal, t1.FPriceDecimal,0 as FSumSort, Case when isdate(u1.FKFDate)=0 then '' else Convert(datetime,u1.FKFDate) + u1.FKFPeriod END AS FMaturityDate, t2.FNumber AS FStockNumber, t1.FNumber AS FMaterialNumber,t1.FNumber AS FLongNumber,t5.FNumber as FSPNumber,t4.FNumber as FCUUnitCode,t4.FMeasureunitID as FCUUnitID ,t1.FitemID ,T2.FitemID FStockID,T5.FSPID FSPID,t9.FName as FAuxPropName,t9.FNumber as FAuxPropNumber,ROUND(u1.FSecQty,t1.FQtyDecimal) AS FSecQty,t1.FSecCoefficient AS FItemSecCoefficient , t1.FGoodsBarCode AS FBarCode, t1.FOrderPrice,ROUND(t1.FOrderPrice * u1.FQty,t1.FPricedecimal) AS FStockBal From #TempInventory u1 left join t_ICItem t1 on u1.FItemID = t1.FItemID left join t_Stock t2 on u1.FStockID=t2.FItemID left join t_MeasureUnit t3 on t1.FUnitID=t3.FMeasureUnitID left join t_MeasureUnit t4 on t1.FStoreUnitID=t4.FMeasureUnitID left join t_StockPlace t5 on u1.FStockPlaceID=t5.FSPID left join t_AuxItem t9 on u1.FAuxPropID=t9.FItemID left join t_Measureunit t19 on t1.FSecUnitID=t19.FMeasureunitID where (Round(u1.FQty,t1.FQtyDecimal)<>0 OR Round(u1.FQty/t4.FCoefficient,t1.FQtyDecimal)<>0) and t1.FDeleted=0 AND t1.FItemID=11253 AND t2.FTypeID in (500,20291,20293) Order By t1.FNumber,u1.FBatchNo ,u1.FMTONo Drop Table #TempInventory /****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [FBrNo] ,[FItemID] ,[FBatchNo] ,[FStockID] ,[FQty] ,[FBal] ,[FStockPlaceID] ,[FKFPeriod] ,[FKFDate] ,[FQtyLock] ,[FAuxPropID] ,[FSecQty] ,[FMTONo] ,[FLastUpdateTime] FROM [AIS20170612094709].[dbo].[ICInventory] order by [FItemID] DESC /****** Script for SelectTopNRows command from SSMS ******/ SELECT TOP 1000 [FMeasureUnitID] ,[FUnitGroupID] ,[FNumber] ,[FAuxClass] ,[FName] ,[FConversation] ,[FCoefficient] ,[FPrecision] ,[FBrNo] ,[FItemID] ,[FParentID] ,[FDeleted] ,[FShortNumber] ,[FOperDate] ,[FScale] ,[FStandard] ,[FControl] ,[FModifyTime] ,[FSystemType] ,[UUID] ,[FNameEN] ,[FNameEnPlu] FROM [stock].[dbo].[t_MeasureUnit] SELECT distinct u1.FItemID,u1.FSupID,u1.FEntryID,v1.FName, v1.FNumber, t2.FName AS FUnitName,u1.FStartQty,u1.FEndQty,(CASE u1.FPType WHEN 1 THEN '采购单价' ELSE '订单委外单价' END) AS FPType, u1.FPrice,t3.FName AS FCyName,u1.FDiscount,u1.FLeadTime, u2.FCurrencyID AS FPOHCyID,u3.FName AS FPOHCyName, (CASE u2.FPOHighPrice WHEN 0 THEN (CASE t1.FPriceFixingType WHEN 1 THEN (CASE u1.FPType WHEN 1 THEN t1.FPOHighPrice ELSE t1.FWWHghPrc END) ELSE 0 END) ELSE u2.FPOHighPrice END) AS FPOHighPrice, u1.FQuoteTime,u1.FDisableDate,u1.FRemark,(CASE u1.Fused WHEN 1 THEN 'Y' ELSE '' END) AS FUsed,t1.FNumber AS FItemNumber,v1.FNumber AS FSupplyNumber, U.FName AS FLastModifiedBy, u1.FLastModifiedDate, t1.FNumber as FItemNumber,t1.FName AS FItemName FROM t_SupplyEntry u1 INNER JOIN t_Supplier v1 ON u1.FSupID=v1.FItemID INNER JOIN t_Supply u2 ON u1.FSupID = u2.FSupID AND u1.FItemID=u2.FItemID AND u1.FPType=u2.FPType INNER JOIN t_ICItem t1 ON u2.FItemID = t1.FItemID INNER JOIN t_MeasureUnit t2 ON u1.FUnitID = t2.FItemID INNER JOIN t_Currency t3 ON u1.FCyID = t3.FCurrencyID INNER JOIN t_Currency u3 ON u3.FCurrencyID = u2.FCurrencyID LEFT JOIN t_item t5 ON t1.FParentID=t5.FItemID LEFT JOIN t_User U ON u1.FLastModifiedBy = U.FUserID WHERE u1.FItemID=7508 ORDER BY v1.FNumber