[ERP/鼎捷E10][存貨管理]庫存呆滯料分析表SQL

雲丶峰發表於2020-11-04
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] 

 

相關文章