5
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.




TOP10PRODUCTS

OLTP

DECLARE @STARTDATE DATE
DECLARE @ENDDATE DATE

SET @STARTDATE = @dt_StartDate
SET @ENDDATE = @dt_EndDate

-- Get the data area for the specified channel identifier.
DECLARE @nvc_DataAreaId NVARCHAR(4);
SELECT @nvc_DataAreaId = rct.INVENTLOCATIONDATAAREAID FROM [ax].RETAILCHANNELTABLE rct WHERE rct.RECID = @bi_ChannelId;

DECLARE @t_Result TABLE
(
ITEMID NVARCHAR(60),
PRODUCTNAME NVARCHAR(150),
NOOFTRANSACTIONS INT,
ITEMSPERTRANSACTION DECIMAL(18,5),
NETQUANTITY INT,
SALESAVGAMOUNT DECIMAL(18,5),
NETSALESAMOUNT DECIMAL(18,5),
ISTOTAL BIT
);

INSERT INTO @t_Result
SELECT TOP 10
RTST.ITEMID,
ISNULL(ERPT.NAME, RTST.ITEMID),
COUNT(DISTINCT RTST.TRANSACTIONID),
-CAST(SUM(RTST.QTY) AS DECIMAL(18,5)) / COUNT(DISTINCT RTST.TRANSACTIONID),
-CAST(SUM(RTST.QTY) AS INT), -- intended to negates the qty to include the returns
-CAST(SUM(RTST.NETAMOUNT + RTST.TAXAMOUNT) AS DECIMAL(18,5)) / COUNT(DISTINCT RTST.TRANSACTIONID),
-CAST(SUM(RTST.NETAMOUNT + RTST.TAXAMOUNT) AS DECIMAL(18,5)) AS NETSALESAMOUNT,
0 -- ISTOTAL
FROM ax.RETAILTRANSACTIONSALESTRANS RTST WITH (NOLOCK)
INNER JOIN ax.RETAILTRANSACTIONTABLE RTT WITH (NOLOCK)
ON RTT.STORE = RTST.STORE
AND RTT.TERMINAL = RTST.TERMINALID
AND RTT.TRANSACTIONID = RTST.TRANSACTIONID
INNER JOIN ax.INVENTTABLE IT
ON IT.ITEMID = RTST.ITEMID
INNER JOIN crt.CHANNELLANGUAGESVIEW CLV
ON CLV.CHANNEL = RTST.CHANNEL
LEFT OUTER JOIN ax.ECORESPRODUCTTRANSLATION ERPT
ON ERPT.PRODUCT = IT.PRODUCT
AND ERPT.LANGUAGEID = CLV.LANGUAGEID
LEFT OUTER JOIN CRT.CUSTOMERORDERTRANSACTION CO
ON RTT.TRANSACTIONID = CO.TRANSACTIONID
WHERE RTT.ENTRYSTATUS <> 1
AND RTST.TRANSACTIONSTATUS <> 1
AND RTST.QTY <> 0 -- exclude zero quantity entry
AND RTST.GIFTCARD <> 1 -- exclude giftcard entry
AND (RTT.TYPE = 2 OR CO.CUSTOMERORDERMODE = 3 OR CO.CUSTOMERORDERMODE = 4)-- SALES TRANSACTION OR CUSTOMER ORDER
AND CLV.ISDEFAULT = 1
AND IT.DATAAREAID = @nvc_DataAreaId
AND RTST.CHANNEL = @bi_ChannelId
AND RTT.TRANSDATE >= @STARTDATE
AND RTT.TRANSDATE < DATEADD(d, 1, @ENDDATE)
GROUP BY RTST.ITEMID, ERPT.NAME
HAVING SUM(RTST.QTY) < 0
ORDER BY NETSALESAMOUNT DESC

IF @@ROWCOUNT > 0
BEGIN
SELECT
ITEMID,
PRODUCTNAME,
NOOFTRANSACTIONS,
ITEMSPERTRANSACTION,
NETQUANTITY,
SALESAVGAMOUNT,
NETSALESAMOUNT
FROM @t_Result
ORDER BY NETSALESAMOUNT DESC
END
]]>








NETSALESAMOUNT


NETQUANTITY


STATUS DETAILS
New