-- B1 DEPENDS: AFTER:PT:PROCESS_END

CREATE PROCEDURE TmSp_ICRA_GetWarehouseQty ( IN CountDate NVARCHAR(20)) 
LANGUAGE SQLSCRIPT 
SQL SECURITY INVOKER
AS 
ConvertedCountDate DATE;
BEGIN
ConvertedCountDate := TO_DATE(:CountDate, 'MM/DD/YYYY');
SELECT * FROM (SELECT TOP 10 '#' || T0."WhsCode" AS "code", "BinActivat", IFNULL("qtycounted", 0) AS "qtycounted", (IFNULL("qtytotal", 0) - IFNULL("qtycounted", 0)) AS "qtyuncounted", IFNULL("qtycountedbin", 0) AS "qtycountedbin", CASE WHEN "BinActivat" = 'Y' THEN (IFNULL("qtytotalbin", 0) - IFNULL("qtycountedbin", 0) - IFNULL("qtycounted", 0)) ELSE (IFNULL("qtytotalbin", 0) - IFNULL("qtycountedbin", 0)) END AS "qtyuncountedbin", CASE WHEN "BinActivat" = 'Y' THEN IFNULL("qtytotalbin", 0) ELSE IFNULL("qtytotal", 0) END AS "totalQty" FROM (SELECT "WhsCode", "BinActivat" FROM OWHS TOWHS INNER JOIN (SELECT DISTINCT "LocCode" FROM OIVL) AS TOIVL ON TOWHS."WhsCode" = TOIVL."LocCode") AS T0 LEFT OUTER JOIN (SELECT "WhsCode", SUM(T0."OnHand") AS "qtycounted" FROM OITW T0 LEFT OUTER JOIN OITM T3 ON T3."ItemCode" = T0."ItemCode" WHERE T0."OnHand" > 0 AND T0."ItemCode" IN (SELECT "ItemCode" FROM (SELECT "ItemCode", "WhsCode", "BinEntry", "CountDate" FROM INC1 UNION SELECT "ItemCode", "WhsCode", "BinEntry", "CountDate" FROM IQR1 WHERE "BaseEntry" IS NULL) AS T1 WHERE T0."WhsCode" = T1."WhsCode" AND (T1."BinEntry" IS NULL) AND T1."CountDate" >= :ConvertedCountDate) GROUP BY "WhsCode") AS TCounted ON TCounted."WhsCode" = T0."WhsCode" LEFT OUTER JOIN (SELECT "WhsCode", SUM(T0."OnHand") AS "qtytotal" FROM OITW T0 LEFT OUTER JOIN OITM T3 ON T3."ItemCode" = T0."ItemCode" GROUP BY "WhsCode") AS TUncounted ON T0."WhsCode" = TUncounted."WhsCode" LEFT OUTER JOIN (SELECT T1."WhsCode", SUM(T1."OnHandQty") AS "qtycountedbin" FROM OIBQ T1 INNER JOIN OITM T3 ON T3."ItemCode" = T1."ItemCode" WHERE t1."OnHandQty" > 0 AND T1."ItemCode" IN (SELECT "ItemCode" FROM (SELECT "ItemCode", "WhsCode", "BinEntry", "CountDate" FROM INC1 UNION SELECT "ItemCode", "WhsCode", "BinEntry", "CountDate" FROM IQR1 WHERE "BaseEntry" IS NULL) AS T2 WHERE T1."WhsCode" = T2."WhsCode" AND T2."BinEntry" = T1."BinAbs" AND T2."CountDate" >= :ConvertedCountDate) AND T1."ItemCode" NOT IN (SELECT "ItemCode" FROM (SELECT "ItemCode", "WhsCode", "BinEntry", "CountDate" FROM INC1 UNION SELECT "ItemCode", "WhsCode", "BinEntry", "CountDate" FROM IQR1 WHERE "BaseEntry" IS NULL) AS T3 WHERE T3."WhsCode" = T1."WhsCode" AND (T3."BinEntry" IS NULL) AND T3."CountDate" >= :ConvertedCountDate) GROUP BY T1."WhsCode") AS T4 ON T0."WhsCode" = T4."WhsCode" LEFT OUTER JOIN (SELECT T5."WhsCode", SUM(T5."OnHandQty") AS "qtytotalbin" FROM OIBQ T5 INNER JOIN OITM T3 ON T3."ItemCode" = T5."ItemCode" GROUP BY T5."WhsCode") AS T7 ON T0."WhsCode" = T7."WhsCode" ORDER BY "totalQty" DESC) T ORDER BY T."code";
END;