Hi Experts,
I need to validate the production order through SP.
Partial issue and and receipt qty alone system needs to accept. The below query executing no error but not validating.
IF (:object_type = '202' AND (:transaction_type in ('A','U')) ) THEN
select
Count(*) into error
from
(select A."PlannedQty",A."DocEntry",A."ItemCode",--B."BaseQty",--B."PlannedQty",B."IssuedQty",
Sum(B."IssuedQty"),Sum(B."PlannedQty"),Sum(B."PlannedQty")/A."PlannedQty",
Round(Sum(B."IssuedQty")/(Sum(B."PlannedQty")/A."PlannedQty"),3) as "Issued",c."Quantity"
from OWOR A
inner join WOR1 B on A."DocEntry"=B."DocEntry" and B."BaseQty">0
inner join IGN1 C on C."BaseEntry"=B."DocEntry" and C."IsByPrdct"<>'Y' and C."TranType"='C'
Where A."Status"='L' --and A."DocEntry"<>'855'
group by A."PlannedQty",A."DocEntry",A."ItemCode",C."Quantity") x
where (SELECT SUM(Y."Quantity")
FROM IGN1 Y WHERE Y."BaseEntry"=X."DocEntry" and Y."IsByPrdct"<>'Y' and Y."TranType" IN ('C','R'))>x."Issued"
and X."DocEntry"=:list_of_cols_val_tab_del;
if :error > 0 then error :=0;
error := 1808;
error_message :=N'Receipt Quantity must not greater than issued Quantity Kindly Check the "total Issued Quantity/Receipt quantity"
END IF;
END IF;