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) ;
Facebook
