Skip to Content

Hi Expert !

Purchase Request Status Report ,,kindly Help Correct The Query

Cancelled PO not in..

SELECT

     T0.”DocNum” as “Indent No.”,

     CASE

            WHEN T0.”DocStatus” = ‘O’ THEN ‘Open’

            WHEN T0.”DocStatus” = ‘C’ THEN ‘Closed’

                            ELSE ‘Unconfirmed’

        END AS “IndentStatus”,

         T0.”DocDate” As “Indent Date”,

   

          T0.”ReqName” as “User Name”,

    T4.”Name” as “Department”,

     T1.”ItemCode”,

     T1.”Dscription” as “Item Name”,

     T1.”Quantity”,

     T1.”unitMsr” as “UOM”,

           CASE

            WHEN T1.”LineStatus” = ‘O’ THEN ‘Open’

            WHEN T1.”LineStatus” = ‘C’ THEN ‘Closed’

               ELSE ‘Unconfirmed’

        END AS “Item Status”,

                    T3.”DocNum” AS “PO Num”,

     t3.”DocDate” AS “PO Date”,

           CASE

            WHEN T3.”DocStatus” = ‘O’ THEN ‘Open’

            WHEN T3.”DocStatus” = ‘C’ THEN ‘Closed’

          

               ELSE ‘Pending’

               END AS “—–“,

                T3.”DocDueDate” as “Expected Date”,

     T6.”DocNum” AS “MRN Num”, T6.”DocDate” AS “MRN Date”

    

FROM OPRQ T0

INNER JOIN PRQ1 T1 ON T0.”DocEntry” = T1.”DocEntry”

LEFT OUTER JOIN POR1 T2 ON T1.”DocEntry” = T2.”BaseEntry” AND T1.”LineNum” = T2.”BaseLine” AND T2.”BaseType” = ‘1470000113’

LEFT OUTER JOIN OPOR T3 ON T3.”DocEntry” = T2.”DocEntry”

LEFT OUTER JOIN PDN1 T5 ON T5.”BaseType” = ’22’ and T5.”TargetType” <>’21’ AND T2.”TrgetEntry”= T5.”DocEntry” AND T2.”LineNum” =t5.”BaseLine” and t5.”ItemCode”=t2.”ItemCode”

LEFT OUTER JOIN OPDN T6 ON T6.”DocEntry” = T5.”DocEntry”

INNER JOIN OUDP T4 ON T0.”Department” = T4.”Code”

where T1.”DocDate”>='[%0]’

and T1.”DocDate” <='[%1]’

and T4.”Name” <>'[%3]’

–and T0.”DocDate”>=’20150401′

and T0.”DocNum” not in (100001)

ORDER BY t0.”DocNum”,

     T1.”LineNum”

    

     Danish S.A.

To report this post you need to login first.

Be the first to leave a comment

You must be Logged on to comment or reply to a post.

Leave a Reply