[ERP/鼎捷E10][存货管理]库存呆滞料分析表SQL

最新推荐文章于 2023-04-07 11:45:05 发布
最新推荐文章于 2023-04-07 11:45:05 发布
阅读量1.9k 收藏 2
点赞数
分类专栏： ERP 鼎捷E10 存货管理
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/tao_yf1989/article/details/109491530
版权
ERP 同时被 3 个专栏收录
13 篇文章 4 订阅
订阅专栏
鼎捷E10
12 篇文章 9 订阅
订阅专栏
存货管理
2 篇文章 0 订阅
订阅专栏 
declare @INACTIVE_BASIC varchar(10),
@INFO_DATE date,@AnalyticalMdel varchar(10),
@PLANT_ID uniqueidentifier,
@PRINT_WAREHOUSE int,
@MONTH int,
@INACTIVE_CONDITION varchar(10)
select 
@PLANT_ID='1FE52FFD-6B71-45E1-1190-114F94B7B244',
@INFO_DATE='2016-10-31 00:00:00',
@MONTH=1,
@PRINT_WAREHOUSE=1,
@INACTIVE_CONDITION=N'1',
@INACTIVE_BASIC=N'3',
@AnalyticalMdel=N'2'
 -------------------------------------------  
SELECT [ITEM].[ITEM_CODE] AS [ITEM_CODE],
[ITEM].[ITEM_NAME] AS [ITEM_NAME],
[ITEM].[ITEM_SPECIFICATION] AS [ITEM_SPECIFICATION],
[ITEM_FEATURE].[ITEM_SPECIFICATION] AS [ITEM_SPECIFICATION_01],
[ITEM_FEATURE].[ITEM_FEATURE_CODE] AS [ITEM_FEATURE_CODE],
0.0 AS [INVENTORY_QTY],
[UNIT].[UNIT_CODE] AS [UNIT_CODE],
[UNIT].[UNIT_NAME] AS [UNIT_NAME],0.0 AS [SECOND_QTY],
[UNIT_02].[UNIT_CODE] AS [UNIT_CODE_01],[UNIT_02].[UNIT_NAME] AS [UNIT_NAME_01],
[PLANT].[PLANT_CODE] AS [PLANT_CODE],
[PLANT].[PLANT_NAME] AS [PLANT_NAME],
[WAREHOUSE].[WAREHOUSE_CODE] AS [WAREHOUSE_CODE],
[WAREHOUSE].[WAREHOUSE_NAME] AS [WAREHOUSE_NAME],[BIN].[BIN_CODE] AS [BIN_CODE],[BIN].[BIN_NAME] AS [BIN_NAME],
[ITEM_LOT].[LOT_CODE] AS [LOT_CODE],[ITEM_LOT].[LOT_DESCRIPTION] AS [LOT_DESCRIPTION],
[IM_FICATEGORY].[IM_FICATEGORY_NAME] AS [IM_FICATEGORY_NAME],
CASE [ITEM_WAREHOUSE_BIN].[BO_ID_RTK]
WHEN N'SUPPLIER' THEN [SUPPLIER].[SUPPLIER_CODE] 
WHEN N'CUSTOMER' THEN [CUSTOMER].[CUSTOMER_CODE] 
WHEN N'EMPLOYEE' THEN [EMPLOYEE].[EMPLOYEE_CODE] 
WHEN N'OTHER_BO' THEN [OTHER_BO].[OTHER_BO_CODE] ELSE N'''' END AS [BO_ID_CODE],
CASE [ITEM_WAREHOUSE_BIN].[BO_ID_RTK] 
WHEN N'SUPPLIER' THEN [SUPPLIER].[SUPPLIER_NAME] 
WHEN N'CUSTOMER' THEN [CUSTOMER].[CUSTOMER_NAME] 
WHEN N'EMPLOYEE' THEN [EMPLOYEE].[EMPLOYEE_NAME] 
WHEN N'OTHER_BO' THEN [OTHER_BO].[OTHER_BO_NAME] ELSE N'''' END AS [BO_ID_NAME],
[ITEM_WAREHOUSE_BIN].[WAREHOUSE_ID] AS [WAREHOUSE_ID],[ITEM].[FEATURE_GROUP_ID] AS [FEATURE_GROUP_ID],
[ITEM].[INVENTORY_CHECK_BY] AS [INVENTORY_CHECK_BY],0.0 AS [UNIT_COST],0.0 AS [COST_AMT],
CASE @INACTIVE_BASIC --呆滞依据
WHEN N'1' THEN [ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE] --最后出库日
WHEN N'2' THEN [ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE] --最后入库日
WHEN N'3' THEN 
		CASE  WHEN [ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE] >= [ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE] 
				THEN [ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE] 
				ELSE [ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE] 
		END 
END AS [INACTIVE_BASIC_TIME],--呆滞月数
[PLANT].[COMPANY_ID] AS [COMPANY_ID],[ITEM_WAREHOUSE_BIN].[ITEM_ID] AS [ITEM_ID],
[ITEM_WAREHOUSE_BIN].[ITEM_FEATURE_ID] AS [ITEM_FEATURE_ID],[ITEM_WAREHOUSE_BIN].[ITEM_LOT_ID] AS [ITEM_LOT_ID],
CASE @AnalyticalMdel --分析方式
WHEN N'1' THEN --1仓库
	CASE @INACTIVE_BASIC 
	WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
	WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
	WHEN N'3' THEN 
		CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
		THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
		ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
		END 
	END 
WHEN N'2' THEN --2仓库+库位
	CASE @INACTIVE_BASIC WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
						WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
						WHEN N'3' THEN 
							CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
							THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
							ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
							END 
	END 
END AS [INACTIVE_TOTAL_TIME],--呆滞总时间
(CASE @AnalyticalMdel --分析模式
WHEN N'1' THEN 
	CASE @INACTIVE_BASIC 
	WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
	WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
	WHEN N'3' THEN 
		CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
		THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
		ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
		END 
	END 
WHEN N'2' THEN 
	CASE @INACTIVE_BASIC 
	WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
	WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
	WHEN N'3' THEN 
		CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE)
		 THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
		ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
		END 
	END 
END / 30) AS [INACTIVE_MONTH],--呆滞月数
[PARA_COMPANY].[INVENTORY_VALUATION_LEVEL] AS [INVENTORY_VALUATION_LEVEL],
[WAREHOUSE].[COST_DOMAIN_ID] AS [COST_DOMAIN_ID],
[PLANT].[COST_DOMAIN_ID] AS [COST_DOMAIN_ID_01],
CASE [PARA_COMPANY].[INVENTORY_VALUATION_LEVEL] 
	WHEN 1 THEN [PLANT].[COMPANY_ID]
	WHEN 2 THEN [PLANT].[COST_DOMAIN_ID] 
	ELSE [WAREHOUSE].[COST_DOMAIN_ID] 
	END 
AS [CDID],--成本域
[PARA_COMPANY].[FUNCTION_CURRENCY_ID] AS [FUNCTION_CURRENCY_ID],--记账本位币
CASE [ITEM].[DRAWING_NO_FROM] 
	WHEN N'1' THEN [ITEM].[DRAWING_NO] 
	WHEN N'2' THEN [ITEM_FEATURE].[DRAWING_NO] 
	END AS [DRAWING_NO],
[FEATURE_GROUP].[FEATURE_GROUP_CODE] AS [FEATURE_GROUP_CODE],
[ITEM_WAREHOUSE_BIN].[BO_ID_RTK] AS [BO_ID_RTK],[ITEM_WAREHOUSE_BIN].[BO_ID_ROid] AS [BO_ID_ROid],
[ITEM_WAREHOUSE_BIN].[BIN_ID] AS [BIN_ID],
[WAREHOUSE].[WAREHOUSE_PROPERTY] AS [WAREHOUSE_PROPERTY],--仓库性质
[IM_FICATEGORY].[IM_FICATEGORY_CODE] AS [IM_FICATEGORY_CODE],
CASE @AnalyticalMdel 
	WHEN N'1' THEN [ITEM_WAREHOUSE].[LAST_ISSUE_DATE] --取品号仓库最后入库日
	WHEN N'2' THEN [ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE] --取存货余额最后入库日
	END AS [LAST_ISSUE_DATE],--最后入库日
CASE @AnalyticalMdel 
	WHEN N'1' THEN [ITEM_WAREHOUSE].[LAST_RECEIPT_DATE] --
	WHEN N'2' THEN [ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE] 
	END AS [LAST_RECEIPT_DATE] --最后出库日
FROM [ITEM_WAREHOUSE_BIN] AS [ITEM_WAREHOUSE_BIN] 
LEFT JOIN [ITEM] AS [ITEM] ON [ITEM_WAREHOUSE_BIN].[ITEM_ID] = [ITEM].[ITEM_BUSINESS_ID] 
LEFT JOIN [ITEM_FEATURE] AS [ITEM_FEATURE] ON [ITEM_WAREHOUSE_BIN].[ITEM_FEATURE_ID] = [ITEM_FEATURE].[ITEM_FEATURE_ID] 
LEFT JOIN [UNIT] AS [UNIT] ON [ITEM].[STOCK_UNIT_ID] = [UNIT].[UNIT_ID] 
LEFT JOIN [UNIT] AS [UNIT_02] ON [ITEM].[SECOND_UNIT_ID] = [UNIT_02].[UNIT_ID] 
LEFT JOIN [PLANT] AS [PLANT] ON ([ITEM_WAREHOUSE_BIN].[Owner_Org_RTK] = 'PLANT' And [ITEM_WAREHOUSE_BIN].[Owner_Org_ROid] = [PLANT].[PLANT_ID]) 
LEFT JOIN [WAREHOUSE] AS [WAREHOUSE] ON [ITEM_WAREHOUSE_BIN].[WAREHOUSE_ID] = [WAREHOUSE].[WAREHOUSE_ID] 
LEFT JOIN [BIN] AS [BIN] ON [ITEM_WAREHOUSE_BIN].[BIN_ID] = [BIN].[BIN_ID] 
LEFT JOIN [ITEM_LOT] AS [ITEM_LOT] ON [ITEM_LOT].[ITEM_LOT_ID] = [ITEM_WAREHOUSE_BIN].[ITEM_LOT_ID] 
LEFT JOIN [ITEM_FINANCIAL] AS [ITEM_FINANCIAL] ON ([ITEM_WAREHOUSE_BIN].[ITEM_ID] = [ITEM_FINANCIAL].[ITEM_ID]  And [PLANT].[COMPANY_ID] = [ITEM_FINANCIAL].[Owner_Org_ROid]) 
LEFT JOIN [IM_FICATEGORY] AS [IM_FICATEGORY] ON [ITEM_FINANCIAL].[IM_FICATEGORY_ID] = [IM_FICATEGORY].[IM_FICATEGORY_ID] 
LEFT JOIN [SUPPLIER] AS [SUPPLIER] ON ([ITEM_WAREHOUSE_BIN].[BO_ID_RTK] = 'SUPPLIER' And [ITEM_WAREHOUSE_BIN].[BO_ID_ROid] = [SUPPLIER].[SUPPLIER_BUSINESS_ID]) 
LEFT JOIN [CUSTOMER] AS [CUSTOMER] ON ([ITEM_WAREHOUSE_BIN].[BO_ID_ROid] = [CUSTOMER].[CUSTOMER_BUSINESS_ID] And [ITEM_WAREHOUSE_BIN].[BO_ID_RTK] = 'CUSTOMER') 
LEFT JOIN [EMPLOYEE] AS [EMPLOYEE] ON ([ITEM_WAREHOUSE_BIN].[BO_ID_RTK] = 'EMPLOYEE' And [ITEM_WAREHOUSE_BIN].[BO_ID_ROid] = [EMPLOYEE].[EMPLOYEE_ID]) 
LEFT JOIN [OTHER_BO] AS [OTHER_BO] ON ([ITEM_WAREHOUSE_BIN].[BO_ID_RTK] = 'OTHER_BO' And [ITEM_WAREHOUSE_BIN].[BO_ID_ROid] = [OTHER_BO].[OTHER_BO_ID]) 
LEFT JOIN [ITEM_WAREHOUSE] AS [ITEM_WAREHOUSE] ON ([ITEM_WAREHOUSE_BIN].[ITEM_ID] = [ITEM_WAREHOUSE].[ITEM_ID] And [ITEM_WAREHOUSE_BIN].[ITEM_FEATURE_ID] = [ITEM_WAREHOUSE].[ITEM_FEATURE_ID] 
And [ITEM_WAREHOUSE_BIN].[WAREHOUSE_ID] = [ITEM_WAREHOUSE].[WAREHOUSE_ID]) 
LEFT JOIN [PARA_COMPANY] AS [PARA_COMPANY] ON [PARA_COMPANY].[Owner_Org_ROid] = [PLANT].[COMPANY_ID] 
LEFT JOIN [FEATURE_GROUP] AS [FEATURE_GROUP] ON [ITEM].[FEATURE_GROUP_ID] = [FEATURE_GROUP].[FEATURE_GROUP_ID] 
WHERE	
(--1	
	(--2
	 @PLANT_ID = [ITEM_WAREHOUSE_BIN].[Owner_Org_ROid] And	
			((@PRINT_WAREHOUSE = 0 And [WAREHOUSE].[WAREHOUSE_PROPERTY] = '1') Or (@PRINT_WAREHOUSE = 1 And  @PRINT_WAREHOUSE IS NOT NULL)) 
		And		
			(--3
			 (@MONTH = 0 And 1 = 1) Or (@MONTH <> 0 And	
				(--4	
					(--5.1
					 @INACTIVE_CONDITION = '1' And	--呆滞条件1.大于等于
						(
							CASE @AnalyticalMdel 
								WHEN N'1' THEN 
									CASE @INACTIVE_BASIC 
									WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
									WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
									WHEN N'3' THEN 
										CASE WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
										THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
										ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) END END 
								WHEN N'2' THEN 
									CASE @INACTIVE_BASIC 
									WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
									WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
									WHEN N'3' THEN 
										CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
										THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
										ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) END END END / 30
						) >= @MONTH  --条件,依据呆滞依据和分析方式作不同的条件
					)--5.1 
					Or	
					(--5.2
					@INACTIVE_CONDITION = '2' And	--呆滞条件2.小于等于
						(	(
							CASE @AnalyticalMdel 
							WHEN N'1' THEN 
								CASE @INACTIVE_BASIC 
								WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
								WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
								WHEN N'3' THEN 
									CASE WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE)
									THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
									ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
									END 
								END 
							WHEN N'2' THEN 
								CASE @INACTIVE_BASIC 
								WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
								WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
								WHEN N'3' THEN 
									CASE WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
									THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
									ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
									END 
								END 
							END / 30
							) <= @MONTH --小于输入月数
						And		
							(
							CASE @AnalyticalMdel 
								WHEN N'1' THEN 
								CASE @INACTIVE_BASIC 
									WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
									WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
									WHEN N'3' THEN 
										CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
										THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
										ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
										END
								 END 
								WHEN N'2' THEN 
								CASE @INACTIVE_BASIC 
									WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
									WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
									WHEN N'3' THEN 
									CASE WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
									THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
									ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) END END END / 30
							) > 0	--大于当前输入信息日期
						)
					)--5.2 
					Or 
					(--5.3等于
					@INACTIVE_CONDITION = '3' 
					And (CASE @AnalyticalMdel WHEN N'1' THEN CASE @INACTIVE_BASIC 
					WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
					WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					WHEN N'3' THEN CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= 
					DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) END END 
					WHEN N'2' THEN CASE @INACTIVE_BASIC WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
					WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) WHEN N'3' THEN CASE  
					WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= 
					DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) END END END / 30) = @MONTH) 
					Or 
					(--5.4大于
					@INACTIVE_CONDITION = '4' 
					And (CASE @AnalyticalMdel WHEN N'1' THEN CASE @INACTIVE_BASIC 
					WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) WHEN N'2' 
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) WHEN N'3' THEN
					CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],
					@INFO_DATE) THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) END END WHEN N'2' THEN 
					CASE @INACTIVE_BASIC WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
					WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) WHEN N'3' 
					THEN CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= 
					DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) END END END / 30) > 
					@MONTH) 
					Or 
					(--5.5小于
					@INACTIVE_CONDITION = '5' And ((CASE @AnalyticalMdel WHEN N'1' THEN 
					CASE @INACTIVE_BASIC WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
					WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) WHEN N'3' THEN 
					CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >= 
					DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) END END 
					WHEN N'2' THEN CASE @INACTIVE_BASIC WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) 
					WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					WHEN N'3' THEN CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >= 
					DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) END END END / 30) < @MONTH 
					And (CASE @AnalyticalMdel WHEN N'1' THEN CASE @INACTIVE_BASIC 
					WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) 
					WHEN N'2' THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) 
					WHEN N'3' THEN CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) >=
					DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE) THEN DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_RECEIPT_DATE],@INFO_DATE)
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE].[LAST_ISSUE_DATE],@INFO_DATE) END END WHEN N'2' THEN CASE @INACTIVE_BASIC
					WHEN N'1' THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) WHEN N'2'
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) WHEN N'3' THEN
					CASE  WHEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) >=
					DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					THEN DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_RECEIPT_DATE],@INFO_DATE) 
					ELSE DATEDIFF(dd,[ITEM_WAREHOUSE_BIN].[LAST_ISSUE_DATE],@INFO_DATE) END END END / 30) > 0)
					)
				)--4
			)
		)--3
	)--2
    And ([ITEM_WAREHOUSE_BIN].[WAREHOUSE_ID] = 'E8FEAD8A-9BE2-40CC-13C6-1281E0D321CC')
)--1 
      ORDER BY [ITEM_CODE],[ITEM_FEATURE_CODE],[WAREHOUSE_CODE]

--------------------------------------------------------------------------------------------------------------------------

欢迎指正、探讨！

ERP/E10/SQL交流群： 81837123

theon.tao
关注
  • 0
    点赞
  • 踩
  • 2
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
鼎捷E10数据表结构
05-26
鼎捷E10数据表结构
鼎捷 E10 ERP IM_存货管理系统概要 第1节
08-09
鼎捷 ERP E10
DTS 总结
wangjun7121的专栏
03-04 3807
文章目录前言概念及示例一.什么是DTS？为什么要引入DTS？二.DTS基本知识1.DTS 的加载过程2.DTS 的描述信息3.DTS 的组成结构4. DTB 整体结构5. dts 引起 BSP 和 driver 的变更6. 常见的 DTS 函数7. DTC(device tree complier)8. DTS 的用户接口位置：基于 MTK 平台分析【非常好】引脚 Dts 相关 clock 时钟示...
Linux内核启动过程学习
热门推荐
yqj1123的专栏
02-26 4万+
过完年来，敲着键盘都有点生疏了，很多东西都有点忘记了，还有刚上班还有点不习惯，状态还没有转换过来，没有办法趁这个机会就复习一下Linux的启动过程吧。     asmlinkage void __init start_kernel(void) { char * command_line; extern const struct kernel_param __start__
PX4编译又出错了：Caused by: java.lang.IllegalStateException: Canvas3D: Non-recoverable graphics configurati
移步换景的博客
12-26 382
咋整啊？？？？ 试试吧： 方法一：https://github.com/PX4/Firmware/issues/9283 2019.12.26 尝试中 方法二：待测试
PaddleDetect图像目标检测模型训练之数据标注——使用labelme进行标注
loutengyuan的专栏
03-27 7818
在做PaddleDetect图像检测模型训练时，需要对数据集进行人工标注，下面将已货车检测为例，使用labelme进行标注的详细过程记录一下，以防日后忘记。
ERP开发过程中常用的SQL查询脚本
月下狼~图腾~
07-05 574
--查找运行请求时间，参数等（可以是某用户的，某个报表） SELECT C.USER_NAME, PAPF.FULL_NAME, B.USER_CONCURRENT_PROGRAM_NAME, A.REQUEST_DATE, A.ARGUMENT_TEXT, (A.ACTUAL_COMPLETION_DATE -...
利用先进先出原则统计易飞ERP呆滞库存
东枫落定的专栏
04-07 371
易飞ERP自定义报表，先进先出方式实现呆滞库龄及呆滞库存金额的计算。
[ERP/鼎捷E10]VMI业务在E10中的处理
云丶峰的博客
09-25 1466
1.VMI概述 VMI全称Vendor Managed Inventory，即供应商管理库存。 VMI（Vendor Managed Inventory）是一种以用户和供应商双方都获得最低成本为目的，在一个共同的协议下由供应商管理库存， 并不断监督协议执行情况和修正协议内容，使库存管理得到持续地改进的合作性策略。这种库存管理策略打破了传统的各自为 政的库存管理模式。体现了供应链的集成化管理思想，适应市场变化的要求，是一种新的、有代表性的库存管理思想。目前VMI在 分销链中的作用十分重要，因此便被.
鼎捷E10操作手册(含财务模块)
05-31
鼎捷E10操作手册(含财务模块)
鼎捷E10教程-在线建档教程
11-01
鼎捷E10教程-在线建档
鼎捷E10模块手册
02-14
鼎捷E10模块手册。描述鼎捷E10－ERP软件的模 块主要功能。
[ERP/鼎捷E10][生产制造]取代料/替代料
云丶峰的博客
08-10 3481
1. 优点：物料优化、有效控制保料率及存货水平 2. 供给时，料件同时有取、替代料之交互关系，则供给量之消耗顺序为： 1) 取代>替代； 2) 依维护取替代料作业，取替代优先顺序预设。 3. E10工单展料取替代料的问题 1) 展料时若未勾选自动取代，则不会带出“取代料”，即旧料，默认勾选可在工厂参数信息中设置，从而达到优先耗用旧料库存的目的；“替代料”是辅料，可在工单单身的材料品号字段通过【F3.查询取替代料】开窗进行人为干预，系统不提供默认展料。(E10 6.0版本才有此开窗)
[ERP/鼎捷E10][生产管理]指定完工
云丶峰的博客
10-21 2371
1.概述 指定完工相较于正常的入库完工比较特殊，此文旨在对指定完工做一个汇总总结。 2.指定完工对后续的单据的影响 领料单、退料单、入库单 1) 工单有未审核的领料单 1.1) 工单指定完工时，会弹出警告： 1.2) 对此未审核的领料单审核会报错： 1.3) 此未审核的领料单可正常删除； 2) 工单有未审核的退料单 2.1) 工单指定完工时，会弹出警告： 2.2) 此未审核的退料单可以正常审核，且会回写数据； 3) 工单有未审核的入库单 3.1) 工单指定完工时，会弹出警
[ERP/鼎捷E10][生产制造]生产齐套分析
云丶峰的博客
08-10 2022
不管什么样的计划，都会有偏差； 关注计划的偏差并予以调整，比努力制定一个完美的计划有效的多 -------------------------------------------------------------------------------------------------------------------------------------------------------------------- 生产齐套分析 1.业务场景 主要针对企业在生产安排上线前分析物料的齐套情况，.
[ERP/鼎捷E10][存货管理]存货周转率/库存周转率
云丶峰的博客
08-18 1730
库存周转率
鼎捷e10 erp培训教材
最新发布
07-06
鼎捷e10 ERP培训教材是一套专门为鼎捷e10 ERP软件使用者设计的培训材料。该教材包含了全面系统的介绍鼎捷e10 ERP软件的功能和使用方法。 首先，该教材首先会介绍鼎捷e10 ERP软件的基本概念和系统结构。学员可以通过...

“相关推荐”对你有帮助么？

  • 非常没帮助
  • 没帮助
  • 一般
  • 有帮助
  • 非常有帮助
提交
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值