Notifications
Clear all
Mar 21, 2022 8:48 pm
Để thay đổi thành công Valuation Method (Phương pháp đánh giá giá trị hàng tồn kho), mã hàng hóa cần phải đáp ứng các điều kiện sau:
1. Số lượng tồn kho phải = 0 ở tất cả các kho
Để kiểm tra, người dùng có thể chạy câu truy vấn sau, thay thế XXXX bằng mã hàng hóa cần kiểm tra:
select "WhsCode", "OnHand" "Quantity" from OITW where "ItemCode" = 'XXXX' and "OnHand" <> 0;
2. Không còn bất cứ giao dịch có liên quan đến mã hàng ở trạng thái Open
select 'GRPO', T2."DocNum" from PDN1 T1 Inner Join OPDN T2 on T1."DocEntry"=T2."DocEntry" where T1."TargetType"<>'21' and T1."ItemCode"='XXXX' and T1."LineStatus"='O'; select 'Goods Return', T2."DocNum" from RPD1 T1 Inner Join ORPD T2 on T1."DocEntry"=T2."DocEntry" where T1."TrgetEntry" is null and T1."BaseType"<>'20' and T1."ItemCode"='XXXX' and T1."LineStatus"='O'; select 'A/P Invoice', T2."DocNum" from PCH1 T1 Inner Join OPCH T2 on T1."DocEntry"=T2."DocEntry" where T1."ItemCode"='XXXX' and T1."LineStatus"='O' and T1."BaseEntry" is null; select 'A/P Credit Memo', T2."DocNum" from RPC1 T1 Inner Join ORPC T2 on T1."DocEntry"=T2."DocEntry" where T1."ItemCode"='XXXX' and T1."LineStatus"='O'; select 'Sales Delivery', T2."DocNum" from DLN1 T1 Inner Join ODLN T2 on T1."DocEntry"=T2."DocEntry" where T1."TargetType"<>'16' and T1."ItemCode"='XXXX' and T1."LineStatus"='O'; select 'Sales Return', T2."DocNum" from RDN1 T1 Inner Join ORDN T2 on T1."DocEntry"=T2."DocEntry" where T1."TrgetEntry" is null and T1."BaseType"<>'15' and T1."ItemCode"='XXXX' and T1."LineStatus"='O'; select 'A/R Invoice', T2."DocNum" from INV1 T1 Inner Join OINV T2 on T1."DocEntry"=T2."DocEntry" where T1."ItemCode"='XXXX' and T1."LineStatus"='O' and T1."BaseEntry" is null; select 'A/R Credit Memo', T2."DocNum" from RIN1 T1 Inner Join ORIN T2 on T1."DocEntry"=T2."DocEntry" where T1."ItemCode"='XXXX' and T1."LineStatus"='O';
3. Không còn bất cứ lệnh sản xuất có liên quan đến mã hàng ở trạng thái Open
SELECT * FROM OWOR T0 INNER JOIN IGN1 T1 ON T1."BaseEntry" = T0."DocEntry" AND T1."BaseType" = 202 WHERE T0."ItemCode" = 'XXXX' AND T0."Status"= ('R');
4. Không có bất kỳ chứng từ Goods Receipt PO có sự không nhất quán về thông tin số lượng
select * from PDN1 where "ItemCode" = 'XXXX' and "OpenCreQty"<> 0 and "LineStatus" = 'C';
5. Không còn bất kỳ thông tin số lượng không nhất quán của các Item
Non Managed Items
select t0."ItemCode" ItemCode, t0."ItemName" ItemName from OITM t0 where (t0."ManSerNum" <> 'Y' and t0."ItemCode" in (select t1."ItemCode" from OSRQ t1 where t1."ItemCode" = t0."ItemCode" and (t1."Quantity" <> 0 or t1."CommitQty" <> 0))) or (t0."ManBtchNum" <> 'Y' and t0."ItemCode" in (select t2."ItemCode" from OBTQ t2 where t2."ItemCode" = t0."ItemCode" and (t2."Quantity" <> 0 or t2."CommitQty" <> 0)));
Batch Item
SELECT t0."ItemCode" AS "ItemCode",
t1."ItemName" AS "ItemName",
t0."WhsCode" AS "Warehouse",
t0."OnHand" AS "OnHand",
(SELECT coalesce(SUM("Quantity"), 0) FROM OBTQ WHERE "ItemCode" = t0."ItemCode" AND "WhsCode" = T0."WhsCode") AS "OnBatch",
(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode" and
/*
The document which can be canceled and not to need to assign batch/serial when add ITEM of On release Only method
-- GRPO, AP Invoice
If documents of doctype of any of the two above and it is canceled, it should not be counted during the batch quantity calculation.
*/
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)
)
+
ABS(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode" and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)) -- Select
) -- ABS
) / 2 AS "ForComplete",
t0."OnHand" -
(SELECT coalesce(SUM("Quantity"), 0)
FROM OBTQ
WHERE "ItemCode" = t0."ItemCode" AND "WhsCode" = T0."WhsCode") - ((
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode" and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)) +
ABS(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode" and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)))) / 2) AS "Difference",
CASE
(SELECT COUNT(1) FROM AITM T5 WHERE T5."ItemCode" = T0."ItemCode" AND T5."ManBtchNum" <> 'Y')
WHEN 0 THEN 'N'
ELSE 'Y'
END AS "StatusChanged",
T1."ManOutOnly",
CASE
WHEN t1."EvalSystem" = 'B' THEN 'Actual Costing'
ELSE t1."EvalSystem"
END AS "Valuation Method",
T1."MngMethod"
FROM OITW t0
INNER JOIN OITM t1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OWHS t3 ON t0."WhsCode" = t3."WhsCode"
WHERE t1."ManBtchNum" = 'Y' and t3."DropShip" <> 'Y'
AND (t0."OnHand"
-
(SELECT coalesce(SUM("Quantity"), 0) FROM OBTQ WHERE "ItemCode" = t0."ItemCode" AND "WhsCode" = T0."WhsCode")
- ((
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode" and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)) +
ABS(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode" and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)))) / 2) <> 0 OR t0."OnHand" < 0)
ORDER BY t0."ItemCode", t0."WhsCode";";
Select * from OBTQ where "Quantity" < 0;
Serial Items
SELECT t0."ItemCode" AS "ItemCode", t1."ItemName" AS "ItemName", t0."WhsCode" AS "Warehouse", t0."OnHand" AS "OnHand",
(SELECT coalesce(SUM("Quantity"), 0)
FROM OSRQ
WHERE "ItemCode" = t0."ItemCode" AND "WhsCode" = T0."WhsCode") AS "OnSerial", (
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode"and
/*
The document which can be canceled and not to need to assign batch/serial when add ITEM of On release Only method
-- GRPO, AP Invoice
If documents of doctype of any of the two above and it is canceled, it should not be counted during the batch quantity calculation.
*/
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)) +
ABS(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode"and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)))) / 2 AS "ForComplete",
t0."OnHand" -
(SELECT coalesce(SUM("Quantity"), 0)
FROM OSRQ
WHERE "ItemCode" = t0."ItemCode" AND "WhsCode" = T0."WhsCode") - ((
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode"and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)) +
ABS(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode"and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)))) / 2) AS "Difference",
CASE
(SELECT COUNT(1)
FROM AITM T5
WHERE T5."ItemCode" = T0."ItemCode" AND T5."ManSerNum" <> 'Y')
WHEN 0 THEN 'N'
ELSE 'Y'
END AS "StatusChanged", T1."MngMethod",
CASE
WHEN t1."EvalSystem" = 'B' THEN 'Actual Costing'
ELSE t1."EvalSystem"
END AS "Valuation Method"
FROM OITW t0
INNER JOIN OITM t1 ON T0."ItemCode" = T1."ItemCode"
INNER JOIN OWHS t3 on t0."WhsCode" = t3."WhsCode" --added MISSING inner join OWHS t3 on t0.WhsCode = t3.WhsCode
WHERE t1."ManSerNum" = 'Y' AND t3."DropShip" <> 'Y' AND t0."OnHand" - --added MISSING and t3.DropShip <> 'Y'
(SELECT coalesce(SUM("Quantity"), 0)
FROM OSRQ
WHERE "ItemCode" = t0."ItemCode" AND "WhsCode" = T0."WhsCode") - ((
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode"and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)) +
ABS(
(SELECT coalesce(SUM(ABS(t2."StockQty")), 0) - coalesce(SUM(ABS(t2."DefinedQty")), 0)
FROM OITL t2
WHERE t2."DocQty" > 0 AND t2."StockEff" = 1 AND t2."ItemCode" = t0."ItemCode" AND t2."LocCode" = t0."WhsCode"and
((select t."MngMethod" from OITM t where t."ItemCode" = t2."ItemCode") <> 'R'
or t2."DocType" not in (18,20)
or (t2."DocType" = 18 and t2."DocEntry" not in (select "DocEntry" from OPCH ap where ap."CANCELED" = 'Y'))
or (t2."DocType" = 20 and t2."DocEntry" not in (select "DocEntry" from OPDN gr where gr."CANCELED" = 'Y'))
)))) / 2) <> 0
ORDER BY t0."ItemCode", t0."WhsCode";
Select * from OSRQ where "Quantity" not in (0, 1);
/*
SELECT
CASE
WHEN
(SELECT COUNT(*)
FROM OSRQ
WHERE "Quantity" NOT IN (0,1)) > 0 THEN 'Serial Number with incorrect quantity found'
ELSE 'No Serial Number with incorrect quanity found'
END
FROM DUMMY;
*/
