Skip to Content
0
Sep 05, 2023 at 08:31 PM

Sales order and Purchase Order

35 Views

SELECT

T0."BPLName",

T0."DocNum",

T0."DocDate",

T0."DocDueDate",

T0."DocStatus",

T1."LineStatus",

T0."CardName",

T0."CANCELED",

T1."NumPerMsr",

T1."unitMsr",

T0."CardCode",

T1."LineNum",

T1."Quantity" as "SO Qty",

T1."OpenQty" as "Commited",

--T1."OpenInvQty",

T1."Price",

T1."WhsCode",

T1."LineTotal"

FROM ORDR T0

left outer JOIN .RDR1 T1 ON T0."DocEntry" = T1."DocEntry"

where T1."ItemCode"='DCH 170' and T0."DocDate" >= '2023-01-01'

and T1."LineStatus"='O'

and T0."CANCELED"='Y'

union all

SELECT

T0."BPLName",

T0."DocNum",

T0."DocDate",

T0."DocDueDate",

T0."DocStatus",

T1."LineStatus",

T0."CardName",

T0."CANCELED",

T1."NumPerMsr",

T1."unitMsr",

T0."CardCode",

T1."LineNum",

T1."Quantity" as "Ordered",

--T1."OpenQty" as "PO Open Qty",

T1."OpenInvQty",

T1."Price",

T1."WhsCode",

T1."LineTotal"

FROM OPOR T0

left outer JOIN POR1 T1 ON T0."DocEntry" = T1."DocEntry"

where T1."ItemCode"='DCH 170' and T0."DocDate" >= '2023-01-01'

and T1."LineStatus"='O'

and T0."CANCELED"='Y'

i am trying this query .But i want purchase order qty separate column and Sales order qty on saparate column