USE [MRCDMS]
GO
/****** Object:  StoredProcedure [dbo].[sp_DMS_RPT_InventoryRealtime]    Script Date: 7/19/2019 9:34:21 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[sp_DMS_RPT_InventoryRealtime]
    @CompanyID INT = NULL,
    @DistributorID INT = NULL,
    @LoginID VARCHAR(50) = NULL,
	@Role  VARCHAR(50) = NULL,
	@FromDate DATETIME,
	@ToDate DATETIME
AS
    BEGIN
        SET NOCOUNT ON;
        SET FMTONLY OFF;
        SET NO_BROWSETABLE OFF;

        DECLARE @_CompanyID INT = NULLIF(@CompanyID, 0);
        DECLARE @_DistributorID INT = NULLIF(@DistributorID, 0);
        DECLARE @_LoginID VARCHAR(50) = @LoginID;
        DECLARE @_Role VARCHAR(50) = NULLIF(LTRIM(RTRIM(@Role)), '');
        DECLARE @_TerritoryType CHAR(1);
        DECLARE @_CurrentDate DATETIME = GETDATE();
        DECLARE @_FromDate DATETIME = NULLIF(@FromDate, @_CurrentDate);
        DECLARE @_ToDate DATETIME = NULLIF(@ToDate, @_CurrentDate);
        DECLARE @_FirstDayOfMonth DATETIME = CONVERT(VARCHAR(4), YEAR(@ToDate))
            + '/' + CONVERT(VARCHAR(2), MONTH(@ToDate)) + '/1';
        DECLARE @_3MonthAgo DATETIME = DATEADD(mm, -3, @_FirstDayOfMonth);
        DECLARE @_OpenQtyDate DATETIME = DATEADD(dd, -1, @_FromDate);

        SELECT  *
        INTO    #tmpDistributorID
        FROM    dbo.fn_DMS_ConvertIDListToTable(@_DistributorID);

        CREATE TABLE #tmp
            (
              CompanyID INT ,
              DistributorID INT ,
              DistributorLocationID INT ,
              SalesOrgID INT ,
              SalesOrg_0_ValueID INT ,
              SalesOrg_0_ValueCD VARCHAR(15) ,
              SalesOrg_0_ValueName NVARCHAR(50) ,
              SalesForce_0_Name NVARCHAR(50) ,
              SalesOrg_1_ValueID INT ,
              SalesOrg_1_ValueCD VARCHAR(15) ,
              SalesOrg_1_ValueName NVARCHAR(50) ,
              SalesForce_1_Name NVARCHAR(50) ,
              SalesOrg_2_ValueID INT ,
              SalesOrg_2_ValueCD VARCHAR(15) ,
              SalesOrg_2_ValueName NVARCHAR(50) ,
              SalesForce_2_Name NVARCHAR(50) ,
              SalesOrg_3_ValueID INT ,
              SalesOrg_3_ValueCD VARCHAR(15) ,
              SalesOrg_3_ValueName NVARCHAR(50) ,
              SalesForce_3_Name NVARCHAR(50) ,
              SalesOrg_4_ValueID INT ,
              SalesOrg_4_ValueCD VARCHAR(15) ,
              SalesOrg_4_ValueName NVARCHAR(50) ,
              SalesForce_4_Name NVARCHAR(50) ,
              SalesOrg_5_ValueID INT ,
              SalesOrg_5_ValueCD VARCHAR(15) ,
              SalesOrg_5_ValueName NVARCHAR(50) ,
              SalesForce_5_Name NVARCHAR(50) ,
              SalesOrg_6_ValueID INT ,
              SalesOrg_6_ValueCD VARCHAR(15) ,
              SalesOrg_6_ValueName NVARCHAR(50) ,
              SalesForce_6_Name NVARCHAR(50) ,
              SalesOrg_7_ValueID INT ,
              SalesOrg_7_ValueCD VARCHAR(15) ,
              SalesOrg_7_ValueName NVARCHAR(50) ,
              SalesForce_7_Name NVARCHAR(50) ,
              SalesOrg_8_ValueID INT ,
              SalesOrg_8_ValueCD VARCHAR(15) ,
              SalesOrg_8_ValueName NVARCHAR(50) ,
              SalesForce_8_Name NVARCHAR(50) ,
              SalesOrg_9_ValueID INT ,
              SalesOrg_9_ValueCD VARCHAR(15) ,
              SalesOrg_9_ValueName NVARCHAR(50) ,
              SalesForce_9_Name NVARCHAR(50) ,
              ParentID INT ,
              ParentLevel INT
            );

        SELECT  CompanyID ,
                SiteID
        INTO    #tmpSiteAccess
        FROM    dbo.DMSViewSiteByUser
        WHERE   CompanyID = @_CompanyID
                AND Username = @_LoginID;

    ---- Lấy thông tin permission theo user đăng nhập
        SELECT  *
        INTO    #tmpPermission
        FROM    dbo.fn_DMS_GetSFPermission(@_CompanyID, @_DistributorID,
                                           @_LoginID);

    ---- Lấy working position của user đăng nhập
    ---- Đấy là báo cáo NPP nên nếu territory type khác SS thì set kiểu view 1 NPP
        SELECT  @_TerritoryType = ( SELECT TOP 1
                                            TerritoryType
                                    FROM    #tmpPermission
                                  );
        IF @_TerritoryType != 'D'
            OR @_TerritoryType IS NULL
            SET @_TerritoryType = 'X';

    ---- Group by các dữ liệu permission
        SELECT  DistributorID
        INTO    #tmpPrmDistributor
        FROM    #tmpPermission
        GROUP BY DistributorID;

    ---- Lấy thông tin NPP với cây sales org tương ứng
        INSERT  INTO #tmp
                EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg @_CompanyID;

        SELECT  d.CompanyID ,
                d.DistributorID ,
                d.DistributorCD ,
                d.DistributorName ,
                Province = d.ProvinceName ,
                District = d.DistrictName ,
                Channel = dl.Attribute0Descr ,
                t.SalesOrgID ,
                t.SalesOrg_0_ValueName ,
                SalesForce_0_Name = sf0.Descr ,
                t.SalesOrg_1_ValueName ,
                SalesForce_1_Name = sf1.Descr ,
                t.SalesOrg_2_ValueName ,
                SalesForce_2_Name = sf2.Descr ,
                t.SalesOrg_3_ValueName ,
                SalesForce_3_Name = sf3.Descr ,
                t.SalesOrg_4_ValueName ,
                SalesForce_4_Name = sf4.Descr ,
                t.SalesOrg_5_ValueName ,
                SalesForce_5_Name = sf5.Descr ,
                t.SalesOrg_6_ValueName ,
                SalesForce_6_Name = sf6.Descr ,
                t.SalesOrg_7_ValueName ,
                SalesForce_7_Name = sf7.Descr ,
                t.SalesOrg_8_ValueName ,
                SalesForce_8_Name = sf8.Descr ,
                t.SalesOrg_9_ValueName ,
                SalesForce_9_Name = sf9.Descr
        INTO    #tmpDistributor
        FROM    #tmp t
                JOIN dbo.DMSViewDistributorLocation dl WITH ( NOLOCK ) ON dl.CompanyID = t.CompanyID
                                                              AND dl.DistributorID = t.DistributorID
                                                              AND dl.LocationID = t.DistributorLocationID
                JOIN dbo.DMSViewDistributor d WITH ( NOLOCK ) ON d.CompanyID = t.CompanyID
                                                              AND d.DistributorID = t.DistributorID
                LEFT JOIN dbo.DMSSFAssignment sfa0 WITH ( NOLOCK ) ON sfa0.CompanyID = t.CompanyID
                                                              AND sfa0.SalesOrgID = t.SalesOrg_0_ValueID
                                                              AND sfa0.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf0 WITH ( NOLOCK ) ON sf0.CompanyID = sfa0.CompanyID
                                                              AND sf0.EmployeeID = sfa0.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa1 WITH ( NOLOCK ) ON sfa1.CompanyID = t.CompanyID
                                                              AND sfa1.SalesOrgID = t.SalesOrg_1_ValueID
                                                              AND sfa1.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf1 WITH ( NOLOCK ) ON sf1.CompanyID = sfa1.CompanyID
                                                              AND sf1.EmployeeID = sfa1.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa2 WITH ( NOLOCK ) ON sfa2.CompanyID = t.CompanyID
                                                              AND sfa2.SalesOrgID = t.SalesOrg_2_ValueID
                                                              AND sfa2.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf2 WITH ( NOLOCK ) ON sf2.CompanyID = sfa2.CompanyID
                                                              AND sf2.EmployeeID = sfa2.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa3 WITH ( NOLOCK ) ON sfa3.CompanyID = t.CompanyID
                                                              AND sfa3.SalesOrgID = t.SalesOrg_3_ValueID
                                                              AND sfa3.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf3 WITH ( NOLOCK ) ON sf3.CompanyID = sfa3.CompanyID
                                                              AND sf3.EmployeeID = sfa3.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa4 WITH ( NOLOCK ) ON sfa4.CompanyID = t.CompanyID
                                                              AND sfa4.SalesOrgID = t.SalesOrg_4_ValueID
                                                              AND sfa4.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf4 WITH ( NOLOCK ) ON sf4.CompanyID = sfa4.CompanyID
                                                              AND sf4.EmployeeID = sfa4.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa5 WITH ( NOLOCK ) ON sfa5.CompanyID = t.CompanyID
                                                              AND sfa5.SalesOrgID = t.SalesOrg_5_ValueID
                                                              AND sfa5.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf5 WITH ( NOLOCK ) ON sf5.CompanyID = sfa5.CompanyID
                                                              AND sf5.EmployeeID = sfa5.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa6 WITH ( NOLOCK ) ON sfa6.CompanyID = t.CompanyID
                                                              AND sfa6.SalesOrgID = t.SalesOrg_6_ValueID
                                                              AND sfa6.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf6 WITH ( NOLOCK ) ON sf6.CompanyID = sfa6.CompanyID
                                                              AND sf6.EmployeeID = sfa6.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa7 WITH ( NOLOCK ) ON sfa7.CompanyID = t.CompanyID
                                                              AND sfa7.SalesOrgID = t.SalesOrg_7_ValueID
                                                              AND sfa7.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf7 WITH ( NOLOCK ) ON sf7.CompanyID = sfa7.CompanyID
                                                              AND sf7.EmployeeID = sfa7.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa8 WITH ( NOLOCK ) ON sfa8.CompanyID = t.CompanyID
                                                              AND sfa8.SalesOrgID = t.SalesOrg_8_ValueID
                                                              AND sfa8.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf8 WITH ( NOLOCK ) ON sf8.CompanyID = sfa8.CompanyID
                                                              AND sf8.EmployeeID = sfa8.EmployeeID
                LEFT JOIN dbo.DMSSFAssignment sfa9 WITH ( NOLOCK ) ON sfa9.CompanyID = t.CompanyID
                                                              AND sfa9.SalesOrgID = t.SalesOrg_9_ValueID
                                                              AND sfa9.IsBaseAssignment = 1
                LEFT JOIN dbo.DMSSalesForce sf9 WITH ( NOLOCK ) ON sf9.CompanyID = sfa9.CompanyID
                                                              AND sf9.EmployeeID = sfa9.EmployeeID;

   -- Lấy tồn kho trước ngày xem báo cáo
        CREATE TABLE #tmpClosingStock
            (
              BaselineDate DATETIME ,
              MonthID CHAR(6) ,
              CompanyID INT ,
              DistributorID INT ,
              InventoryID INT ,
              IsFree BIT ,
              SiteID INT ,
              LocationID INT ,
              LotSerialNbr VARCHAR(50) ,
              ExpiredDate DATETIME ,
              OpenQty DECIMAL(18, 2) ,
              InQty DECIMAL(18, 2) ,
              OutQty DECIMAL(18, 2) ,
              CloseQty DECIMAL(18, 2)
            );
        INSERT  INTO #tmpClosingStock
            EXEC  dbo.sp_DMS_InventoryStockMonthly @_CompanyID,
                    '2018-01-01', @_OpenQtyDate, NULL;

        SELECT  BaselineDate ,
                MonthID ,
                CompanyID ,
                DistributorID ,
                InventoryID ,
                SiteID ,
                LocationID ,
                LotSerialNbr ,
                OpenQty = SUM(OpenQty) ,
                InQty = SUM(InQty) ,
                OutQty = SUM(OutQty) ,
                CloseQty = SUM(CloseQty)
        INTO    #TotalClosing
        FROM    #tmpClosingStock
        GROUP BY BaselineDate ,
                MonthID ,
                CompanyID ,
                DistributorID ,
                InventoryID ,
                SiteID ,
                LocationID ,
                LotSerialNbr

	---- Lấy dữ liệu rawdata
        SELECT  inr.CompanyID ,
                DistributorID = inr.BranchID ,
                TranDate = COALESCE(soo.OrderDate, por.ReceiptDate,
                                    intr.TranDate) ,
                ints.InventoryID ,
                ints.LotSerialNbr ,
                ints.SiteID ,
                ints.LocationID ,
                InQty =	CASE
							WHEN (ints.TranType = 'RCP' AND inr.OrigModule = 'PO') ---- Nhập mua
								OR (ints.TranType = 'RCP' AND inr.OrigModule = 'IN') ---- Nhập khác
								OR (ints.TranType = 'TRX' AND ints.InvtMult = 1) ---- Chuyển kho - nhập
								OR (ints.TranType = 'ADJ' AND ints.InvtMult = 1) ---- Điều chỉnh tăng kho
								OR (ints.TranType = 'RET' ) ---- Nhập trả
								OR (ints.TranType = 'CRM' AND sol.IsFree = 0) ---- Nhập khách hàng trả
								OR (ints.TranType = 'CRM' AND sol.IsFree = 1) ---- Nhập khách hàng trả KM
								THEN ints.BaseQty
							WHEN (ints.TranType = 'III' AND por.ReceiptNbr IS NOT NULL) ---- Xuất trả ncc
								THEN ints.BaseQty * -1
							ELSE 0
						END,
                OutQty =	CASE
							WHEN (ints.TranType = 'III' AND por.ReceiptNbr IS NULL) ---- Xuất khác
								OR (ints.TranType = 'TRX' AND ints.InvtMult = -1) ---- Chuyển kho - xuất
								OR (ints.TranType = 'ADJ' AND ints.InvtMult = -1) ---- Điều chỉnh giảm kho
								OR (ints.TranType = 'DRM' ) ---- Xuất debit
								OR (ints.TranType = 'INV' AND sol.IsFree = 0) ---- Xuất bán
								OR (ints.TranType = 'INV' AND sol.IsFree = 1) ---- Xuất khuyến mãi
								OR (ints.TranType = 'INV' AND sol.IsFree = 1 AND sol.UsrIncentiveID IS NOT NULL) ---- Xuất trả thưởng
								THEN ints.BaseQty
							ELSE 0
						END ,
                CloseQty = NULL
        INTO    #tmpRawData
        FROM    INRegister inr WITH ( NOLOCK )
                JOIN INTran intr WITH ( NOLOCK ) ON intr.CompanyID = inr.CompanyID
                                                            AND intr.RefNbr = inr.RefNbr
                                                            AND intr.DocType = inr.DocType
                JOIN INTranSplit ints WITH ( NOLOCK ) ON ints.CompanyID = intr.CompanyID
                                                              AND ints.RefNbr = intr.RefNbr
                                                              AND ints.DocType = intr.DocType
                                                              AND ints.LineNbr = intr.LineNbr
                LEFT JOIN SOOrder soo WITH ( NOLOCK ) ON soo.CompanyID = intr.CompanyID
                                                              AND soo.OrderNbr = intr.SOOrderNbr
                                                              AND soo.OrderType = intr.SOOrderType
                LEFT JOIN SOLine sol WITH ( NOLOCK ) ON sol.CompanyID = intr.CompanyID
                                                              AND sol.OrderNbr = intr.SOOrderNbr
                                                              AND sol.OrderType = intr.SOOrderType
                                                              AND sol.LineNbr = intr.SOOrderLineNbr
                LEFT JOIN POReceipt por WITH ( NOLOCK ) ON por.CompanyID = intr.CompanyID
                                                              AND por.ReceiptNbr = intr.POReceiptNbr
                LEFT JOIN POReceiptLine porl WITH ( NOLOCK ) ON porl.CompanyID = intr.CompanyID
                                                              AND porl.ReceiptNbr = intr.POReceiptNbr
                                                              AND porl.LineNbr = intr.POReceiptLineNbr
        WHERE   inr.CompanyID = @_CompanyID
                AND inr.Released = 1
                AND COALESCE(soo.OrderDate, inr.TranDate) BETWEEN @_FromDate
                                                          AND @_ToDate
                AND ( inr.BranchID IN ( SELECT    DistributorID
										FROM      #tmpPermission )
                    );
        SELECT  CompanyID ,
                DistributorID ,
                TranDate ,
                InventoryID ,
                LotSerialNbr ,
                SiteID ,
                LocationID ,
                InQty = SUM(InQty) ,
                OutQty = SUM(OutQty) ,
                CloseQty = NULL
        INTO    #TotalRawData
        FROM    #tmpRawData
        GROUP BY CompanyID ,
                DistributorID ,
                TranDate ,
                InventoryID ,
                LotSerialNbr ,
                SiteID ,
                LocationID;
	-- Insert tồn kho của từng ngày
        CREATE TABLE #tmpInventoryWithLot
            (
              CompanyID INT ,
			  DistributorID INT,
              InventoryID INT ,
              SiteID INT ,
              LocationID INT ,
              LotSerialNbr VARCHAR(50) ,
              ExpireDate DATETIME ,
              TranDate DATETIME ,
              CloseQty DECIMAL(18, 2)
            );

        DECLARE @_From DATETIME = CONVERT(DATETIME, @_FromDate, 120);
        WHILE @_From <= @_ToDate
            BEGIN
                SELECT  *
                INTO    #TotalRawDataTemp
                FROM    #TotalRawData rd
                WHERE   CONVERT(DATETIME, rd.TranDate, 120) = @_From;

                INSERT  INTO #tmpInventoryWithLot
                        ( CompanyID ,
						  DistributorID ,
                          InventoryID ,
                          SiteID ,
                          LocationID ,
                          LotSerialNbr ,
                          ExpireDate ,
                          TranDate ,
                          CloseQty
			            )
                        SELECT  CompanyID = COALESCE(cs.CompanyID,
                                                     rd.CompanyID) ,
							    DistributorID = COALESCE(cs.DistributorID,
                                                     rd.DistributorID),
                                InventoryID = COALESCE(cs.InventoryID,
                                                       rd.InventoryID) ,
                                SiteID = COALESCE(cs.SiteID, rd.SiteID) ,
                                LocationID = COALESCE(cs.LocationID,
                                                      rd.LocationID) ,
                                LotSerialNbr = COALESCE(cs.LotSerialNbr,
                                                        rd.LotSerialNbr) ,
                                ExpireDate = NULL ,
                                TranDate = COALESCE(CONVERT(DATETIME, rd.TranDate, 120), CONVERT(DATETIME, @_From, 120)) ,
                                CloseQty = ISNULL(CASE WHEN SUM(cs.CloseQty) IS NULL
                                                       THEN SUM(ISNULL(rd.InQty,
                                                              0))
                                                            - SUM(ISNULL(rd.OutQty,
                                                              0))
                                                       ELSE SUM(ISNULL(cs.CloseQty,
                                                              0))
                                                            + SUM(ISNULL(rd.InQty,
                                                              0))
                                                            - SUM(ISNULL(rd.OutQty,
                                                              0))
                                                  END, 0)
                        FROM    #TotalClosing cs
                                FULL JOIN #TotalRawDataTemp rd ON rd.CompanyID = cs.CompanyID
                                                              AND rd.DistributorID = cs.DistributorID
                                                              AND rd.InventoryID = cs.InventoryID
                                                              AND rd.SiteID = cs.SiteID
                                                              AND rd.LocationID = cs.LocationID
                        GROUP BY COALESCE(cs.CompanyID, rd.CompanyID) ,
						        COALESCE(cs.DistributorID,rd.DistributorID) ,
                                COALESCE(cs.InventoryID, rd.InventoryID) ,
                                COALESCE(cs.SiteID, rd.SiteID) ,
                                COALESCE(cs.LocationID, rd.LocationID) ,
                                COALESCE(cs.LotSerialNbr, rd.LotSerialNbr) ,
                                COALESCE(CONVERT(DATETIME, rd.TranDate, 120), CONVERT(DATETIME, @_From, 120));

                DELETE  FROM #TotalClosing;

				SELECT  *
                INTO    #TotalClosingTmp
                FROM    #tmpInventoryWithLot rd
                WHERE   CONVERT(DATETIME, rd.TranDate, 120) = @_From;

                INSERT  INTO #TotalClosing
                        ( BaselineDate ,
                          MonthID ,
                          CompanyID ,
						  DistributorID ,
                          InventoryID ,
                          SiteID ,
                          LocationID ,
                          LotSerialNbr ,
                          CloseQty
			            )
                        SELECT  NULL ,
                                NULL ,
                                CompanyID ,
								DistributorID ,
                                InventoryID ,
                                SiteID ,
                                LocationID ,
                                LotSerialNbr ,
                                CloseQty = SUM(ISNULL(CloseQty, 0))
                        FROM    #TotalClosingTmp
                        GROUP BY CompanyID ,
						        DistributorID ,
                                InventoryID ,
                                SiteID ,
                                LocationID ,
                                LotSerialNbr;
				DROP TABLE #TotalClosingTmp
                DROP TABLE #TotalRawDataTemp;
           SET @_From = DATEADD(dd, 1, @_From);
            END;
	---------------------------------------
        SELECT  CompanyID ,
                DistributorID ,
                LocationID ,
                InventoryID ,
                AVG(SumOutQtyOf3MonthAgo) AvgOutQtyOf3MonthAgo
        INTO    #tmpOutOf6MonthAgo
        FROM    ( SELECT    bs.CompanyID ,
                            bs.DistributorID ,
                            dl.LocationID ,
                            InventoryID ,
                            OrderDate ,
                            ( SUM(Revenue1) + SUM(Revenue2) + SUM(Revenue3) ) SumOutQtyOf3MonthAgo
                  FROM      MRCBaseline..DMSBLSales bs
                            JOIN MRCBaseline..DMSBLDistributorLocation dl ON bs.BLDistributorLocationID = dl.ID
                                                              AND bs.CompanyID = dl.CompanyID
                  WHERE     OrderDate >= @_3MonthAgo
                            AND OrderDate < @_FirstDayOfMonth
                            AND bs.CompanyID = @CompanyID
                            AND ( Revenue1 <> 0
                                  OR Revenue2 <> 0
                                  OR Revenue3 <> 0
                                )
                  GROUP BY  bs.CompanyID ,
                            bs.DistributorID ,
                            dl.LocationID ,
                            InventoryID ,
                            OrderDate
                ) r
        GROUP BY CompanyID ,
                DistributorID ,
                LocationID ,
                InventoryID;

	---------------------------------------
        SELECT  iwl.CompanyID ,
                DistributorID = site.BranchID ,
                DistributorLocationID = NULL ,
                iwl.InventoryID ,
                site.SiteCD ,
                SiteName = site.Descr ,
                loc.LocationCD ,
                LocationName = loc.Descr ,
                LotSerialNbr = ISNULL(iwl.LotSerialNbr, '') ,
                ExpiredMonth = CONVERT(VARCHAR(20), YEAR(iwl.ExpireDate))
                + '/' + RIGHT('0' + CONVERT(VARCHAR(20), MONTH(iwl.ExpireDate)),
                              2) ,
                iwl.ExpireDate ,
                TranDate = iwl.TranDate ,
                Quantity = iwl.CloseQty ,
                Amount = sp.SalesPrice/ ISNULL(u.UnitRate, 1) * NULLIF(iwl.CloseQty, 0) ,
                SalesPrice = CONVERT(DECIMAL, sp.SalesPrice / ISNULL(u.UnitRate, 1)) ,
                AvgAmountOf3MonthAgo = ago.AvgOutQtyOf3MonthAgo ,
				Amount_PPR = ppr.Price/ ISNULL(u.UnitRate, 1) * NULLIF(iwl.CloseQty, 0) ,
				SalesPrice_PPR = CONVERT(DECIMAL, ppr.Price / ISNULL(u.UnitRate, 1)) ,
				UOM_PPR = ppr.UOM
        INTO    #tmpResult
        FROM    #tmpInventoryWithLot iwl
                JOIN dbo.INLocation loc WITH ( NOLOCK ) ON loc.CompanyID = iwl.CompanyID
                                                           AND loc.SiteID = iwl.SiteID
                                                           AND loc.LocationID = iwl.LocationID
                JOIN dbo.INSite site WITH ( NOLOCK ) ON site.CompanyID = loc.CompanyID
                                                        AND site.SiteID = loc.SiteID
                LEFT JOIN dbo.ARSalesPrice sp WITH ( NOLOCK ) ON sp.CompanyID = iwl.CompanyID
                                                            AND sp.InventoryID = iwl.InventoryID
                                                            AND ( sp.EffectiveDate <= @_CurrentDate
                                                              AND ( sp.ExpirationDate > @_CurrentDate
                                                              OR sp.ExpirationDate IS NULL
                                                              )
                                                              )
				LEFT JOIN dbo.DMSPrimaryPrice ppr WITH ( NOLOCK ) ON ppr.CompanyID = iwl.CompanyID
                                                            AND ppr.InventoryID = iwl.InventoryID
															AND ppr.DistributorID = iwl.DistributorID
                LEFT JOIN dbo.INUnit u ON u.CompanyID = sp.CompanyID
                                     AND u.InventoryID = sp.InventoryID
                                     AND u.FromUnit = sp.UOM
                LEFT JOIN #tmpOutOf6MonthAgo ago ON ago.CompanyID = iwl.CompanyID
                                                    AND ago.InventoryID = iwl.InventoryID
													AND ago.DistributorID = iwl.DistributorID
			--AND ago.LocationID = iwl.LocationID
			--AND ago.SiteID = iwl.SiteID
        WHERE   site.BranchID in (SELECT    DistributorID
								  FROM      #tmpPermission)
			    and iwl.CompanyID = @_CompanyID

        SELECT  d.DistributorID ,
                d.DistributorCD ,
                d.DistributorName ,
                d.ProvinceName Province ,
                d.DistrictName District ,
                tmpD.Channel ,
                tmpD.SalesOrgID ,
                tmpD.SalesOrg_0_ValueName ,
                tmpD.SalesOrg_1_ValueName ,
                tmpD.SalesOrg_2_ValueName ,
                tmpD.SalesOrg_3_ValueName ,
                tmpD.SalesOrg_4_ValueName ,
                tmpD.SalesOrg_5_ValueName ,
                tmpD.SalesOrg_6_ValueName ,
                tmpD.SalesOrg_7_ValueName ,
                tmpD.SalesOrg_8_ValueName ,
                tmpD.SalesOrg_9_ValueName ,
           --     tmpD.SalesForce_0_Name ,
           --     tmpD.SalesForce_1_Name ,
           --     tmpD.SalesForce_2_Name
           ----, tmpD.SalesForce_3_Name
           --     ,
           --     tmpD.SalesForce_4_Name ,
           --     tmpD.SalesForce_5_Name ,
           --     tmpD.SalesForce_6_Name ,
           --     tmpD.SalesForce_7_Name ,
           --     tmpD.SalesForce_8_Name ,
           --     tmpD.SalesForce_9_Name ,
		   REPLACE(REPLACE(REPLACE(tmpD.SalesForce_0_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_0_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_1_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_1_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_2_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_2_Name,
--REPLACE(REPLACE(REPLACE(tmpD.SalesForce_3_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_3_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_4_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_4_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_5_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_5_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_6_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_6_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_7_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_7_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_8_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_8_Name,
REPLACE(REPLACE(REPLACE(tmpD.SalesForce_0_Name, CHAR(13), ''), CHAR(10), ''),',',' ') as SalesForce_0_Name,
--NamPT add
                item.InventoryCD ,
                item.InventoryName ,
                item.InventoryShortName ,
                InventoryAbbreviationName = item.InventoryAbbrName ,
                item.Hierachy0CD ,
                item.Hierachy0Descr ,
                item.Hierachy1CD ,
                item.Hierachy1Descr ,
                item.Hierachy2CD ,
                item.Hierachy2Descr ,
                item.Hierachy3CD ,
                item.Hierachy3Descr ,
                item.Hierachy4CD ,
                item.Hierachy4Descr ,
                item.Hierachy5CD ,
                item.Hierachy5Descr ,
                item.Hierachy6CD ,
                item.Hierachy6Descr ,
                item.Hierachy7CD ,
                item.Hierachy7Descr ,
                item.Hierachy8CD ,
                item.Hierachy8Descr ,
                item.Hierachy9CD ,
                item.Hierachy9Descr ,
                INAttribute0Descr = item.Attribute0Descr ,
                INAttribute1Descr = item.Attribute1Descr ,
                INAttribute2Descr = item.Attribute2Descr ,
                INAttribute3Descr = item.Attribute3Descr ,
                INAttribute4Descr = item.Attribute4Descr ,
                INAttribute5Descr = item.Attribute5Descr ,
                INAttribute6Descr = item.Attribute6Descr ,
                INAttribute7Descr = item.Attribute7Descr ,
                INAttribute8Descr = item.Attribute8Descr ,
                INAttribute9Descr = item.Attribute9Descr ,
                item.BaseUnit ,
                r.SiteCD ,
                r.SiteName ,
                r.LocationCD ,
                r.LocationName ,
				REPLACE(REPLACE(REPLACE( r.LocationName , CHAR(13), ''), CHAR(10), ''),',',' ') as LocationName,
                r.LotSerialNbr ,
                r.ExpiredMonth ,
                r.ExpireDate ,
                r.Quantity ,
                r.TranDate Date ,
                Amount = CASE WHEN ISNULL(r.Amount, 0.000000) = 0.000000 AND item.BaseUnit = r.UOM_PPR AND ISNULL(r.Amount_PPR, 0) > 0 THEN ISNULL(r.Amount_PPR, 0) ELSE ISNULL(r.Amount, 0) END ,
                SalesPrice = CASE WHEN ISNULL(r.SalesPrice, 0.000000) = 0.000000 AND item.BaseUnit = r.UOM_PPR AND ISNULL(r.SalesPrice_PPR, 0) > 0 THEN ISNULL(r.SalesPrice_PPR, 0) ELSE ISNULL(r.SalesPrice, 0) END ,
                AvgStockValue = AVG(CASE WHEN ISNULL(r.Amount, 0.000000) = 0.000000 AND item.BaseUnit = r.UOM_PPR AND ISNULL(r.Amount_PPR, 0) > 0 THEN ISNULL(r.Amount_PPR, 0) ELSE ISNULL(r.Amount, 0) END) OVER ( PARTITION BY r.CompanyID,
                                                     r.DistributorID,
                                                     r.LocationCD,
                                                     r.InventoryID ) ,
                AvgSecondaryByMonth = r.AvgAmountOf3MonthAgo ,
                InventoryDays = CASE WHEN r.AvgAmountOf3MonthAgo IS NULL
                                          OR r.AvgAmountOf3MonthAgo = 0 THEN 0
                                     ELSE ROUND(AVG(CASE WHEN ISNULL(r.Amount, 0.000000) = 0.000000 AND item.BaseUnit = r.UOM_PPR AND ISNULL(r.Amount_PPR, 0) > 0 THEN ISNULL(r.Amount_PPR, 0) ELSE ISNULL(r.Amount, 0) END) OVER ( PARTITION BY r.CompanyID,
                                                              r.DistributorID,
                                                              r.LocationCD,
                                                              r.InventoryID )
                                                / AvgAmountOf3MonthAgo, 0)
                                END
        FROM    #tmpResult r
                JOIN dbo.DMSViewInventoryItem item WITH ( NOLOCK ) ON item.CompanyID = r.CompanyID
                                                              AND item.InventoryID = r.InventoryID
                                                              AND ( item.ItemStatus != 'IN'
                                                              OR r.Quantity > 0
                                                              )
                LEFT JOIN dbo.INUnit iu ON iu.CompanyID = item.CompanyID
                                           AND iu.InventoryID = item.InventoryID
                                           AND iu.FromUnit = item.PurchaseUnit
                                           AND iu.ToUnit = item.BaseUnit
                                           AND iu.UnitMultDiv = 'M'
                JOIN dbo.DMSViewDistributor d ON d.CompanyID = r.CompanyID
                                                 AND d.DistributorID = r.DistributorID
                JOIN #tmpDistributor tmpD ON tmpD.CompanyID = r.CompanyID
                                             AND tmpD.DistributorID = r.DistributorID;
drop table #tmpResult
drop table #tmpRawData
    END;