MAKE ZERO STOCK INVENTORI

SQL QUERY CODE

DECLARE
@CON_COMP_CODE NVARCHAR(8)
,@CON_DIV_CODE NVARCHAR(8)
,@CON_COUNT_DATE NVARCHAR(10)
,@CON_WH_CODE NVARCHAR(8)
,@CON_TYPE NVARCHAR(2)

SET @CON_COMP_CODE = 'MASTER'
SET @CON_DIV_CODE = '02' --//01 : KGEO , 02: SGEO
SET @CON_COUNT_DATE = '20140531'
SET @CON_WH_CODE = 'H10' --//H01: KGEO WIP , H10: SGEO WIP
SET @CON_TYPE = 'Y' --//APPLY

IF @CON_TYPE = ''
BEGIN
SELECT A.ITEM_CODE
,A.GOOD_STOCK_BOOK_Q
,A.GOOD_STOCK_Q
,C.STOCK_Q
FROM BIV300T A (NOLOCK)
JOIN BPR100T R1 (NOLOCK) ON (R1.COMP_CODE=A.COMP_CODE AND R1.ITEM_CODE=A.ITEM_CODE)
JOIN BPR200T R2 (NOLOCK) ON (R2.COMP_CODE=A.COMP_CODE AND R2.DIV_CODE=A.DIV_CODE AND R2.ITEM_CODE=A.ITEM_CODE)
LEFT JOIN BPR000T R0 (NOLOCK) ON (R0.COMP_CODE=R1.COMP_CODE AND R0.LEVEL1=R1.ITEM_LEVEL1 AND R0.LEVEL2=R1.ITEM_LEVEL2 AND R0.LEVEL3=R1.ITEM_LEVEL3)
LEFT JOIN BIV100T C (NOLOCK) ON (C.COMP_CODE=A.COMP_CODE AND C.DIV_CODE=A.DIV_CODE AND C.WH_CODE=A.WH_CODE AND C.ITEM_CODE=A.ITEM_CODE)
WHERE A.COMP_CODE = @CON_COMP_CODE
AND A.DIV_CODE = @CON_DIV_CODE
AND A.COUNT_DATE = @CON_COUNT_DATE
AND A.WH_CODE = @CON_WH_CODE

END

IF @CON_TYPE = 'Y'
BEGIN

UPDATE A SET
A.GOOD_STOCK_Q = 0
,A.BAD_STOCK_Q = 0
FROM BIV300T A (NOLOCK)
WHERE A.COMP_CODE = @CON_COMP_CODE
AND A.DIV_CODE = @CON_DIV_CODE
AND A.COUNT_DATE = @CON_COUNT_DATE
AND A.WH_CODE = @CON_WH_CODE

END