Our customer's products must have 5 decimals place in Qty, Unit & Amount. We extends related Qty EDT's and work fine in D365FO.

However, in POS Reports. They wants to see amounts in 5 decimal places too.

E.g. Top 10 products. I change all decimal places in Retail Channel Reports Configuration. It doesn't reflect to MPOS report.

Please make POS reports to show decimal places whatever configure in reports configuration. Otherwise, please allow extensibility for POS Reports section.






OLTP

<br /> DECLARE @STARTDATE DATE<br /> DECLARE @ENDDATE DATE<br /> <br /> SET @STARTDATE = @dt_StartDate<br /> SET @ENDDATE = @dt_EndDate<br /> <br /> -- Get the data area for the specified channel identifier.<br /> DECLARE @nvc_DataAreaId NVARCHAR(4);<br /> SELECT @nvc_DataAreaId = rct.INVENTLOCATIONDATAAREAID FROM [ax].RETAILCHANNELTABLE rct WHERE rct.RECID = @bi_ChannelId;<br /> <br /> DECLARE @t_Result TABLE<br /> (<br /> ITEMID NVARCHAR(60),<br /> PRODUCTNAME NVARCHAR(150),<br /> NOOFTRANSACTIONS INT,<br /> ITEMSPERTRANSACTION DECIMAL(18,5),<br /> NETQUANTITY INT,<br /> SALESAVGAMOUNT DECIMAL(18,5),<br /> NETSALESAMOUNT DECIMAL(18,5),<br /> ISTOTAL BIT<br /> );<br /> <br /> INSERT INTO @t_Result<br /> SELECT TOP 10<br /> RTST.ITEMID,<br /> ISNULL(ERPT.NAME, RTST.ITEMID),<br /> COUNT(DISTINCT RTST.TRANSACTIONID),<br /> -CAST(SUM(RTST.QTY) AS DECIMAL(18,5)) / COUNT(DISTINCT RTST.TRANSACTIONID),<br /> -CAST(SUM(RTST.QTY) AS INT), -- intended to negates the qty to include the returns<br /> -CAST(SUM(RTST.NETAMOUNT + RTST.TAXAMOUNT) AS DECIMAL(18,5)) / COUNT(DISTINCT RTST.TRANSACTIONID),<br /> -CAST(SUM(RTST.NETAMOUNT + RTST.TAXAMOUNT) AS DECIMAL(18,5)) AS NETSALESAMOUNT,<br /> 0 -- ISTOTAL<br /> FROM ax.RETAILTRANSACTIONSALESTRANS RTST WITH (NOLOCK)<br /> INNER JOIN ax.RETAILTRANSACTIONTABLE RTT WITH (NOLOCK)<br /> ON RTT.STORE = RTST.STORE<br /> AND RTT.TERMINAL = RTST.TERMINALID<br /> AND RTT.TRANSACTIONID = RTST.TRANSACTIONID<br /> INNER JOIN ax.INVENTTABLE IT<br /> ON IT.ITEMID = RTST.ITEMID<br /> INNER JOIN crt.CHANNELLANGUAGESVIEW CLV<br /> ON CLV.CHANNEL = RTST.CHANNEL<br /> LEFT OUTER JOIN ax.ECORESPRODUCTTRANSLATION ERPT<br /> ON ERPT.PRODUCT = IT.PRODUCT<br /> AND ERPT.LANGUAGEID = CLV.LANGUAGEID<br /> LEFT OUTER JOIN CRT.CUSTOMERORDERTRANSACTION CO<br /> ON RTT.TRANSACTIONID = CO.TRANSACTIONID<br /> WHERE RTT.ENTRYSTATUS <> 1<br /> AND RTST.TRANSACTIONSTATUS <> 1<br /> AND RTST.QTY <> 0 -- exclude zero quantity entry<br /> AND RTST.GIFTCARD <> 1 -- exclude giftcard entry<br /> AND (RTT.TYPE = 2 OR CO.CUSTOMERORDERMODE = 3 OR CO.CUSTOMERORDERMODE = 4)-- SALES TRANSACTION OR CUSTOMER ORDER<br /> AND CLV.ISDEFAULT = 1<br /> AND IT.DATAAREAID = @nvc_DataAreaId<br /> AND RTST.CHANNEL = @bi_ChannelId<br /> AND RTT.TRANSDATE >= @STARTDATE<br /> AND RTT.TRANSDATE < DATEADD(d, 1, @ENDDATE)<br /> GROUP BY RTST.ITEMID, ERPT.NAME<br /> HAVING SUM(RTST.QTY) < 0<br /> ORDER BY NETSALESAMOUNT DESC<br /> <br /> IF @@ROWCOUNT > 0<br /> BEGIN<br /> SELECT <br /> ITEMID,<br /> PRODUCTNAME,<br /> NOOFTRANSACTIONS,<br /> ITEMSPERTRANSACTION,<br /> NETQUANTITY,<br /> SALESAVGAMOUNT,<br /> NETSALESAMOUNT<br /> FROM @t_Result<br /> ORDER BY NETSALESAMOUNT DESC<br /> END <br />








NETSALESAMOUNT


NETQUANTITY