Get Workflow Information AX using SQL

Hai guys, I want to share how get Workflow Information AX using SQL

USE AXReports

SELECT  A.SALESID,

                           A.CUSTACCOUNT,

                           (SELECT TOP 1 NAME FROM AXABU.DBO.DIRPARTYTABLE AS R WITH (NOLOCK) 

                                  WHERE R.RECID = (SELECT PARTY FROM AXABU.DBO.CUSTTABLE AS X WITH (NOLOCK)  WHERE X.ACCOUNTNUM = A.CUSTACCOUNT))

                           AS CUSTNAME,

                           B.ITEMID,

                           C.PRODUCTNAME,

                           A.SALESUNITID,

                           A.WORKERSALESRESPONSIBLE,

                           B.SALESQTY,

                           B.SALESPRICE,

                           B.REMAININVENTPHYSICAL,

                           A.DELIVERYDATE,

                           B.LINEAMOUNT,

                           A.INVENTLOCATIONID,

                           B.INVENTTRANSID,

                           (SELECT TOP 1 X.PAYMTERMID FROM [AXABU].[DBO].[CUSTTABLE] AS X WITH (NOLOCK)  WHERE X.ACCOUNTNUM = A.CUSTACCOUNT) AS TERMOFPAYMENT,

                           (SELECT SUM(QTY) FROM AXReports.dbo.CUSTPACKINGSLIPTRANS_ AS AA WITH (NOLOCK) WHERE AA.INVENTTRANSID = B.INVENTTRANSID) AS DELIVERED,

                           (SELECT SUM(QTY) FROM AXReports.dbo.CUSTINVOICETRANS_ AS BB WITH (NOLOCK) WHERE BB.INVENTTRANSID = B.INVENTTRANSID) AS INVOICED,

                           (CASE WHEN A.SALESTYPE = 3

                                         THEN (SELECT TOP 1 X.SALESID FROM AXReports.dbo.SALESLINE_ AS X WITH (NOLOCK) WHERE X.INVENTTRANSIDRETURN = B.INVENTTRANSID)

                                    WHEN A.SALESTYPE = 4

                                      THEN (SELECT TOP 1 X.SALESID FROM AXReports.dbo.SALESLINE_ AS X WITH (NOLOCK) WHERE X.INVENTTRANSID = B.INVENTTRANSIDRETURN)

                            END) AS RETURNORDER,

                           (CASE WHEN A.SALESTYPE = 3 THEN 'SALES ORDER'

                                    WHEN A.SALESTYPE = 4 THEN 'RETURN ORDER'

                           END) AS ORDERTYPE,

                           CAST(A.CREATEDDATETIME AS DATE) AS CREATEORDER,

                           (CASE  WHEN B.SALESSTATUS = 1 THEN 'OPEN ORDER'       

                                         WHEN B.SALESSTATUS = 2 THEN 'DELIVERED'

                                         WHEN B.SALESSTATUS = 3 THEN 'INVOICED'

                                         WHEN B.SALESSTATUS = 4 THEN 'CANCELED'

                           END) AS STA,

                           (CASE WHEN A.DOCUMENTSTATUS = 0 THEN 'NONE'

                                         WHEN A.DOCUMENTSTATUS = 3 THEN 'CONFIRMATION'

                                         WHEN A.DOCUMENTSTATUS = 4 THEN 'PICKING LIST'

                                         WHEN A.DOCUMENTSTATUS = 5 THEN 'PACKING SLIP'

                                         WHEN A.DOCUMENTSTATUS = 7 THEN 'INVOICE'

                            END) AS DOCUMENTSTATUS,

                           (SELECT TOP 1 XZ.FIRSTNAME  FROM AXABU.DBO.HCMWORKER AS XY WITH (NOLOCK)

                                  INNER JOIN AXABU.DBO.DIRPERSONNAME AS XZ

                                  ON XZ.PERSON = XY.PERSON

                                  WHERE XY.RECID = A.WORKERSALESRESPONSIBLE) AS NAME,

                                  (SELECT TOP 1 X.NAME FROM AXABU.DBO.DIRPARTYTABLE AS X WHERE X.RECID = (SELECT TOP 1 Y.PERSON FROM AXABU.DBO.HCMWORKER AS Y WITH (NOLOCK)

                           WHERE Y.RECID = (SELECT TOP 1 X.WORKERSALESRESPONSIBLE FROM AXReports.dbo.SALESTABLE_ AS X WITH (NOLOCK) WHERE X.SALESID = A.SALESID))) AS EMPLOYEE,

                           (SELECT TOP 1 BB.TAXGROUP FROM AXReports.dbo.CUSTINVOICETRANS_ AS BB WITH (NOLOCK)  WHERE BB.INVENTTRANSID = B.INVENTTRANSID) AS TAX,

                           (SELECT TOP 1 BB.INVOICEDATE FROM AXReports.dbo.CUSTINVOICETRANS_ AS BB WITH (NOLOCK)  WHERE BB.INVENTTRANSID = B.INVENTTRANSID) AS INVOICEDATE,

                           D.ITEMGROUPID,

                           (SELECT TOP 1 X.[DESCRIPTION] FROM AXABU.DBO.SMMBUSRELSEGMENTGROUP  AS X WITH (NOLOCK)

                                         INNER JOIN AXABU.DBO.CUSTTABLE AS Y

                                                ON Y.SEGMENTID = X.SEGMENTID

                                                AND Y.ACCOUNTNUM = A.CUSTACCOUNT) SEGMENT,                          

(SELECT TOP 1 BB.LINEAMOUNT FROM AXReports.dbo.CUSTINVOICETRANS_ AS BB WITH (NOLOCK)  WHERE BB.INVENTTRANSID = B.INVENTTRANSID) AS LINEINVOICE,

(SELECT TOP 1 (BB.LINEAMOUNT + BB.LINEAMOUNTTAX) FROM AXReports.dbo.CUSTINVOICETRANS_ AS BB WITH (NOLOCK) WHERE BB.INVENTTRANSID = B.INVENTTRANSID) AS LINEINVOICETAX,

A.MODIFIEDBY

              INTO #SALESTEMP

              FROM AXReports.dbo.SALESTABLE_ AS A

                     INNER JOIN AXReports.dbo.SALESLINE_ AS B WITH (NOLOCK)

                           ON B.SALESID = A.SALESID

                           AND B.SALESSTATUS IN (@STA)

                           AND B.DATAAREAID = A.DATAAREAID

                     INNER JOIN AXABU.DBO.ECORESPRODUCTTRANSLATIONS AS C WITH (NOLOCK)

                           ON C.DISPLAYPRODUCTNUMBER = B.ITEMID

                     INNER JOIN AXABU.DBO.INVENTITEMGROUPITEM AS D WITH (NOLOCK)

                           ON D.ITEMID = B.ITEMID

              WHERE CAST(A.CREATEDDATETIME AS DATE) >= @FROMDATE

              AND CAST(A.CREATEDDATETIME AS DATE) <= @TODATE

              AND A.INVENTLOCATIONID IN(@WAREHOUSE)

              SELECT * FROM #SALESTEMP WITH (NOLOCK) ;