Reports Sales Order to Production Order - Time Out
AnsweredHello,
when it entered the menu of customer orders vs. production orders it takes more than 20 minutes to open despite deleting all customer orders

And If select Storage Related It takes an additional 40 to 60 minutes

executing in sql the query generated in the Bug, I identify that it takes time executing
"
SQL SELECT header."DocEntry", line."LineNum", "OITM"."InvntryUom", "OITM"."U_dispo", "OITM"."U_din", "OITM"."U_match", "OITM"."U_wst_id", line."Project", header."CreateDate", header."CardCode", header."CardName", line."ItemCode", line."Dscription", line."Quantity"+0.0000 as "qquantity", line."OpenCreQty", line."ShipDate", line."VisOrder"+1 as cpos, line."U_beas_vri"+0 as "VRI", line."U_beas_shortvariant"+'' as "SHORTVARIANT", line."U_beas_ver"+'' as "U_beas_ver", line."U_von_lag", line."NumPerMsr", header."DocNum", header."ObjType", 0.000000 as sollmenge,
/* Open Quantity = Order Quantity - Open Workorder Quantity - Receipt via Workorder */
case when Coalesce(s1."INHALT",'N')<>'N' and Coalesce("OITM"."InvntItem",'Y')='Y'
/* use Reservation System */
then line."OpenQty"*line."NumPerMsr" - Coalesce(
(SELECT sum("Quantity")
FROM "BEAS_RESERVATION_LINE"
WHERE "BASE_TYPE"='17' and "BASE_DOCENTRY"=line."DocEntry" and "BASE_LINENUM2"=line."LineNum") ,0) else case when Coalesce("OITM"."InvntItem",'Y')='Y' then
/* Warehouse items - all linked work orders*/
line."OpenQty"*line."NumPerMsr" - Coalesce(line."U_von_lag",0) - Coalesce(
(SELECT sum(f1."MENGE" - coalesce(f1."GEL_MENGE",0))
FROM "BEAS_FTPOS" f1
WHERE f1."BaseType"='K' and f1."DocEntry"=line."DocEntry" and f1."BaseLine"=line."LineNum" and Coalesce(f1."STUFE",0)=0) ,0)
/* Merged work oder: Total quantity is in field Bom.Ausschuss */
+coalesce(
(SELECT sum(r."AUSSCHUSS")
FROM "BEAS_FTSTL" r
WHERE r."BaseType"='K' and r."BASEDOCENTRY"=header."DocEntry" and r."BaseLine"=line."LineNum") ,0) else
/* Non Warehouse Items - ony open work orders*/
line."Quantity"*line."NumPerMsr" - Coalesce(line."U_von_lag",0) - Coalesce(
(SELECT sum(f1."MENGE")
FROM "BEAS_FTPOS" f1
WHERE f1."BaseType"='K' and f1."DocEntry"=line."DocEntry" and f1."BaseLine"=line."LineNum" and Coalesce(f1."STUFE",0)=0) ,0) end end as restmenge,
/* .... */
0 as ww ,0.0000 as "DICHTE", 0.0000 as l,0.0000 as b,0.0000 as h, Coalesce(line."U_beas_znr","OITM"."U_znr") as oitm_U_beas_znr,"OITM"."U_sachb_id", "OITM"."OnHand","OITM"."OnOrder","OITM"."IsCommited", line."NumPerMsr"*line."Quantity" as gesamtmenge,'K' as herkunft,header."Confirmed",line."U_beas_requestdel",line."U_beas_delconfirm", header."NumAtCard", line."WhsCode",
(SELECT "OnHand"
FROM "OITW"
WHERE "OITW"."ItemCode"="OITM"."ItemCode" and "OITW"."WhsCode"=line."WhsCode") as orderonhand ,"OITM"."U_beas_prodrelease",line."LineNum"+0 as "LineNum", "OITM"."ItmsGrpCod",
/*(select "INHALT" from "BEAS_SYS_SETUP" where "VARIABLE_ID" = 'auftr_ordertyp') as ordertyp, */
Coalesce(s2."INHALT",'') as ordertyp, space(50) as templatefield1,space(50) as templatefield2,space(50) as templatefield3,space(50) as templatefield4,space(50) as templatefield5,space(50) as templatefield6, "OWHS"."WhsName","OWHS"."U_beas_bitmap","OWHS"."U_beas_color","OBPL"."BPLName","BEAS_OBPL"."BITMAPNAME", "BEAS_OBPL"."COLORID"
FROM "RDR1" as line
INNER JOIN "ORDR" as header on header."DocEntry"=line."DocEntry" and header."Confirmed"='Y'
INNER JOIN "OITM" on "OITM"."ItemCode"=line."ItemCode" and "OITM"."PrcrmntMtd"='M' and "OITM"."TreeType" not in ('S','T','A')
LEFT OUTER JOIN "OWHS" on "OWHS"."WhsCode"=line."WhsCode"
LEFT OUTER JOIN "OBPL" on "OBPL"."BPLId"="OWHS"."BPLid"
LEFT OUTER JOIN "BEAS_OBPL" on "BEAS_OBPL"."BPLId"="OWHS"."BPLid"
LEFT OUTER JOIN "BEAS_SYS_SETUP" s1 on s1."BENUTZER"='' and s1."VARIABLE_ID"='auftr_link'
LEFT OUTER JOIN "BEAS_SYS_SETUP" s2 on s2."BENUTZER"='' and s2."VARIABLE_ID"='auftr_ordertyp'
WHERE line."LineStatus"='O'
/*filter1*/
/* ------------------ second part ------------------------*/
union all SELECT header."DocEntry", line."LineNum", "OITM"."InvntryUom", "OITM"."U_dispo", "OITM"."U_din", "OITM"."U_match", "OITM"."U_wst_id", line."Project", header."CreateDate", header."CardCode", header."CardName", line."ItemCode", line."Dscription", line."Quantity"+0.0000 as "qquantity", line."OpenQty", line."ShipDate", line."VisOrder"+1 as cpos, line."U_beas_vri"+0 as "VRI", line."U_beas_shortvariant"+'' as "SHORTVARIANT", line."U_beas_ver"+'' as "U_beas_ver", line."U_von_lag", line."NumPerMsr", header."DocNum", header."ObjType", 0.000000 as sollmenge,
/* Open Quantity = Order Quantity - Open Workorder Quantity - Receipt via Workorder */
case when Coalesce(s1."INHALT",'N')<>'N' and Coalesce("OITM"."InvntItem",'Y')='Y'
/* use Reservation System */
then line."OpenQty"*line."NumPerMsr" - Coalesce(
(SELECT sum("Quantity")
FROM "BEAS_RESERVATION_LINE"
WHERE "BASE_TYPE"='17' and "BASE_DOCENTRY"=line."DocEntry" and "BASE_LINENUM2"=line."LineNum") ,0) else case when Coalesce("OITM"."InvntItem",'Y')='Y' then
/* Warehouse items - all linked work orders*/
line."OpenQty"*line."NumPerMsr" - Coalesce(line."U_von_lag",0) - Coalesce(
(SELECT sum(f1."MENGE")
FROM "BEAS_FTPOS" f1
WHERE f1."BaseType"='V' and f1."DocEntry"=line."DocEntry" and f1."BaseLine"=line."LineNum" and Coalesce(f1."STUFE",0)=0) ,0)
/* Merged work oder: Total quantity is in field Bom.Ausschuss */
+coalesce(
(SELECT sum(r."AUSSCHUSS")
FROM "BEAS_FTSTL" r
WHERE r."BaseType"='V' and r."BASEDOCENTRY"=header."DocEntry" and r."BaseLine"=line."LineNum") ,0) else
/* Non Warehouse Items - ony open work orders*/
line."Quantity"*line."NumPerMsr" - Coalesce(line."U_von_lag",0) - Coalesce(
(SELECT sum(f1."MENGE")
FROM "BEAS_FTPOS" f1
WHERE f1."BaseType"='V' and f1."DocEntry"=line."DocEntry" and f1."BaseLine"=line."LineNum" and Coalesce(f1."STUFE",0)=0) ,0) end end as restmenge,
/* .... */
0 as ww, 0.0000 as "DICHTE", 0.0000 as l, 0.0000 as b, 0.0000 as h, Coalesce(line."U_beas_znr","OITM"."U_znr") as oitm_U_beas_znr, "OITM"."U_sachb_id", "OITM"."OnHand", "OITM"."OnOrder", "OITM"."IsCommited", line."NumPerMsr"*line."Quantity" as gesamtmenge, 'V' as herkunft, header."Confirmed", line."U_beas_requestdel", line."U_beas_delconfirm", header."NumAtCard", line."WhsCode",
(SELECT "OnHand"
FROM "OITW"
WHERE "OITW"."ItemCode"="OITM"."ItemCode" and "OITW"."WhsCode"=line."WhsCode") as orderonhand , "OITM"."U_beas_prodrelease",line."LineNum"+0 as "LineNum", "OITM"."ItmsGrpCod",
/* (select "INHALT" from "BEAS_SYS_SETUP" where "VARIABLE_ID" = 'auftr_ordertyp') as ordertyp, */
Coalesce(s2."INHALT",'') as ordertyp, space(50) as templatefield1,space(50) as templatefield2,space(50) as templatefield3,space(50) as templatefield4,space(50) as templatefield5,space(50) as templatefield6, "OWHS"."WhsName","OWHS"."U_beas_bitmap","OWHS"."U_beas_color","OBPL"."BPLName","BEAS_OBPL"."BITMAPNAME","BEAS_OBPL"."COLORID"
FROM "INV1" as line
INNER JOIN "OINV" as header on header."DocEntry"=line."DocEntry" and header."Confirmed"='Y'
INNER JOIN "OITM" on "OITM"."ItemCode"=line."ItemCode" and "OITM"."PrcrmntMtd"='M' and "OITM"."TreeType" not in ('S','T','A')
LEFT OUTER JOIN "OWHS" on "OWHS"."WhsCode"=line."WhsCode"
LEFT OUTER JOIN "OBPL" on "OBPL"."BPLId"="OWHS"."BPLid"
LEFT OUTER JOIN "BEAS_OBPL" on "BEAS_OBPL"."BPLId"="OWHS"."BPLid"
LEFT OUTER JOIN "BEAS_SYS_SETUP" s1 on s1."BENUTZER"='' and s1."VARIABLE_ID"='auftr_link'
LEFT OUTER JOIN "BEAS_SYS_SETUP" s2 on s2."BENUTZER"='' and s2."VARIABLE_ID"='auftr_ordertyp'
WHERE ( header."DocEntry" = line."DocEntry" ) and ( "OITM"."ItemCode"=line."ItemCode" and "OITM"."PrcrmntMtd"='M') and header."UpdInvnt"='C' and line."LineStatus"='O' and line."InvntSttus"<>'C'"
-
Official comment
Hello Luis,
This issue requires more analysis, please create a support ticket so one of our agents can investigate further.
Best regards,
Please sign in to leave a comment.
Comments
1 comment