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]