open production order report query

[Former Member]

Dear Experts,

I want Open Production order report query which give following information.

Production Order No., Product No. Product Description, order date, planned quantity, completed quantity.

Thanks & Regards,

Akshata

[former_member377094]

Hello,

Try this one

Select T0.Docentry,T0.DocNum,T0.PostDate,T0.DueDate,(Case when T0.Status='L' then 'Closed' when T0.Status='R' then 'Realesed' when T0.Status='C' then 'Cancelled' end) as 'Status',

T0.ItemCode,T2.Itemname,T0.plannedQty,T0.CmpltQty,T0.RjctQty,T1.Itemcode,T3.ItemName,T1.plannedQty,T1.IssuedQty,T1.VisOrder as 'Row'

from OWOR T0

Left Outer Join WOR1 T1 on T1.Docentry=T0.Docentry

Left Outer Join OITM T2 on T0.itemcode=T2.Itemcode

Left Outer Join OITM T3 on T3.Itemcode=T1.ItemCode

Where T0.PostDate>='[%0]' AND T0.PostDate<='[%1]' And T1.VisOrder=0

Union All

Select T0.Docentry,Null as DocNum,Null as PostDate,Null as DueDate,Null as 'Status',

Null as ItemCode,Null as Itemname,Null as plannedQty,Null as CmpltQty,Null as RjctQty,T1.Itemcode,T3.ItemName,T1.plannedQty,T1.IssuedQty,T1.VisOrder as 'Row'

from OWOR T0

Left Outer Join WOR1 T1 on T1.Docentry=T0.Docentry

Left Outer Join OITM T2 on T0.itemcode=T2.Itemcode

Left Outer Join OITM T3 on T3.Itemcode=T1.ItemCode

Where T0.PostDate>='[%0]' AND T0.PostDate<='[%1]' And T1.VisOrder>0

Order by T0.Docentry,T1.visorder

[Former Member]

Dear Akshata,

Try:

SELECT  T0.[DocNum], T0.[ItemCode], T1.[ItemName], T0.[PostDate], T0.[PlannedQty], T0.[CmpltQty]

FROM OWOR T0

INNER JOIN OITM T1 ON T1.ItemCode = T0.ItemCode

Where T0.Status in ('P','R')

Thanks, Gordon

[former_member312729]

Hello ,

Try this

SELECT  Distinct T0.[DocNum],T0.[PostDate], T0.[ItemCode], T2.[ItemName], T0.[PlannedQty], T3.[IssuedQty], T0.[CmpltQty] FROM OWOR T0  INNER JOIN OITT T1 ON T0.ItemCode = T1.Code INNER JOIN OITM T2 ON T1.Code = T2.ItemCode INNER JOIN WOR1 T3 ON T0.DocEntry = T3.DocEntry

[Former Member]

Dear sir,

The given query shows all data whether it is open or close. I want only planned & released status production order means open production order.

Thanks & Regards,

Akshata

[jitin_chawla]

Hi,

Check this :

SELECT  Distinct T0.[DocNum],T0.[PostDate], T0.[ItemCode],

T2.[ItemName], T0.[PlannedQty],

T3.[IssuedQty], T0.[CmpltQty]

FROM OWOR T0  INNER JOIN OITT T1 ON T0.ItemCode = T1.Code

INNER JOIN OITM T2 ON T1.Code = T2.ItemCode

INNER JOIN WOR1 T3 ON T0.DocEntry = T3.DocEntry

WHERE t0.status in ('P','R')

Kind Regards,

Jitin

SAP Business One Forum Team

[Former Member]

Hi Akshata.....

Please try this.....

SELECT  Distinct T0.[DocNum],T0.[PostDate], T0.[ItemCode], T2.[ItemName], T0.[PlannedQty], T3.[IssuedQty], T0.[CmpltQty] FROM OWOR T0  INNER JOIN OITT T1 ON T0.ItemCode = T1.Code INNER JOIN OITM T2 ON T1.Code = T2.ItemCode INNER JOIN WOR1 T3 ON T0.DocEntry = T3.DocEntry
Where T0.Status in ('P','R')

Regards,
Rahul

[former_member312729]

Hello,

Updated

SELECT  Distinct T0.[DocNum],T0.[PostDate], T0.[ItemCode], T2.[ItemName], T0.[PlannedQty], T3.[IssuedQty], T0.[CmpltQty] FROM OWOR T0  INNER JOIN OITT T1 ON T0.ItemCode = T1.Code INNER JOIN OITM T2 ON T1.Code = T2.ItemCode INNER JOIN WOR1 T3 ON T0.DocEntry = T3.DocEntry where WHERE ( T0.[Status]='P') OR (T0.[Status]='R')

Regards:

Balaji

[Former Member]

Hi Akshata

Pls try T Code COOIS where u can get the required details.

Thanks

Sachin

[Former Member]

Hi,

There is standard open production order report in Production\Production report or Open Item List with production order option in sales\purchase reports. You can full out complete qty field by choosing Form settings

Hope this helps,

TVSon

[Former Member]

Dear sir,

The open item list report is not showing PRODUCT DESCRIPTION.

Thanks & Regards,

Akshata

[Former Member]

Hi,

There is standard open production order report in Production\Production report or Open Item List with production order option in sales\purchase reports. You can full out complete qty field by choosing Form settings

Hope this helps,

TVSon

[Mahesh6050]

Here is Error Free Query 


SELECT
T0."DocEntry",
T0."DocNum",
T0."PostDate",
T0."DueDate",
(CASE
WHEN T0."Status" = 'L' THEN 'Closed'
WHEN T0."Status" = 'R' THEN 'Released'
WHEN T0."Status" = 'C' THEN 'Cancelled'
END) AS "Status",
T0."ItemCode",
T2."ItemName",
T0."PlannedQty",
T0."CmpltQty",
T0."RjctQty",
T1."ItemCode",
T3."ItemName",
T1."PlannedQty",
T1."IssuedQty",
T1."VisOrder" AS "Row"
FROM OWOR T0
LEFT OUTER JOIN WOR1 T1 ON T1."DocEntry" = T0."DocEntry"
LEFT OUTER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"
LEFT OUTER JOIN OITM T3 ON T3."ItemCode" = T1."ItemCode"
WHERE T0."PostDate" >= '[%0]'
AND T0."PostDate" <= '[%1]'
AND T1."VisOrder" = 0

UNION ALL

SELECT
T0."DocEntry",
NULL AS "DocNum",
NULL AS "PostDate",
NULL AS "DueDate",
NULL AS "Status",
NULL AS "ItemCode",
NULL AS "ItemName",
NULL AS "PlannedQty",
NULL AS "CmpltQty",
NULL AS "RjctQty",
T1."ItemCode",
T3."ItemName",
T1."PlannedQty",
T1."IssuedQty",
T1."VisOrder" AS "Row"
FROM OWOR T0
LEFT OUTER JOIN WOR1 T1 ON T1."DocEntry" = T0."DocEntry"
LEFT OUTER JOIN OITM T2 ON T0."ItemCode" = T2."ItemCode"
LEFT OUTER JOIN OITM T3 ON T3."ItemCode" = T1."ItemCode"
WHERE T0."PostDate" >= '[%0]'
AND T0."PostDate" <= '[%1]'
AND T1."VisOrder" > 0
ORDER BY 1, 14; -- DocEntry is the 1st column and VisOrder is the 14th column