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
            );
 INSERT  INTO #tmp
                EXEC dbo.sp_DMS_MDM_GetDistributorWithSalesOrg 3;
SELECT  soo.CompanyID ,
        DistributorID = soo.BranchID ,
        DistributorLocationID = NULL ,
        soo.OrderNbr ,
        soo.OrderType ,
        soo.OrderDate ,
        soo.CustomerOrderNbr ,
        soo.CustomerID ,
        soo.CustomerLocationID ,
        SalesRepsID = soo.UsrSalesReps ,
        SalesForeID = soo.UsrSalesForce ,
        Route = soo.UsrRoute ,
        sol.InventoryID ,
        sol.UnitPrice ,
        sol.LineNbr ,
        OrderQty = ISNULL(sol.UsrOrgQty, sol.BaseOrderQty) ,
        OpenQty = sol.BaseOpenQty ,
        ShipQty = sol.BaseOrderQty ,
        sol.OpenAmt ,
        sol.CuryExtPrice , -- thanh tien = don gia * soluong
        sol.CuryLineAmt ,-- thanh toan = don gia * so luong - chiet khau
        sol.IsFree ,
        sol.ReasonCode ,
        IsDelete = CASE WHEN soo.Cancelled = 1 THEN 1
                        WHEN ISNULL(soo.UsrIsDispose, 0) = 1 THEN 1
                        WHEN soo.OrderType = 'CM'
                             AND ISNULL(soo.UsrIsRevenueDeduction, 0) = 0
                        THEN 1
                   END ,
        Status = CASE WHEN soo.Status = 'N' THEN N'Mở'
                      WHEN soo.Status = 'H' THEN N'Chờ'
                      WHEN soo.Status = 'S' THEN N'Đang giao'
                      WHEN soo.Status = 'C' THEN N'Hoàn tất'
                 END ,
        Checkbox = CASE WHEN pdaorder.ValidDate = 1 THEN N'Đúng tuyến'
                        WHEN pdaorder.ValidDate != 1 THEN N'Trái tuyến'
                        WHEN pdaorder.ValidDate = 1
                             AND pdaorder.ValidDistance != 1
                        THEN N'Đúng tuyến, Sai khoảng cách'
                        WHEN soo.CustomerOrderNbr IS NULL
                        THEN N'Nhà phân phôi bán'
                   END ,
        soo.RequestDate ,
        UsrSourceType = CASE WHEN soo.UsrSourceType = 'RPO'
                             THEN 'Request Purchase Order'
                             WHEN soo.UsrSourceType = 'PDA' THEN 'PDA Order'
                             WHEN soo.UsrSourceType = 'MAN'
                             THEN 'Manual Order'
                             WHEN soo.UsrSourceType = 'TMK'
                             THEN 'Trade Marketing'
                        END ,
        soo.OrderDesc ,
        soo.UsrDiscTot ,-- Tong Chiet Khau - Cac Khoan Giam Tru
        soo.OrderWeight ,
        CuryOrderTotal ,-- Tong Thanh Toan
        soship.ShipmentNbr ,
        soship.InvoiceNbr ,
        soship.InvtRefNbr ,
        sos.ShipDate ,
        arregister.RefNbr ,
        arregister.DocDate ,
        inregister.TranDate ,
        sol.UsrPromotionID
INTO    #tmpAllOrder
FROM    dbo.SOOrder soo WITH ( NOLOCK )
        JOIN dbo.SOLine sol WITH ( NOLOCK ) ON sol.CompanyID = soo.CompanyID
                                               AND sol.BranchID = soo.BranchID
                                               AND sol.OrderNbr = soo.OrderNbr
                                               AND sol.OrderType = soo.OrderType
        LEFT JOIN DMSPDAOrder pdaorder WITH ( NOLOCK ) ON soo.CompanyID = pdaorder.CompanyID
                                                          AND soo.BranchID = pdaorder.BranchID
                                                          AND soo.CustomerOrderNbr = pdaorder.OrderNbr
        LEFT JOIN dbo.SOShipLine sosl WITH ( NOLOCK ) ON sosl.CompanyID = sol.CompanyID
                                                         AND sosl.OrigOrderNbr = sol.OrderNbr
                                                         AND sosl.OrigOrderType = sol.OrderType
                                                         AND sosl.OrigLineNbr = sol.LineNbr
        LEFT JOIN dbo.SOShipment sos WITH ( NOLOCK ) ON sos.CompanyID = sosl.CompanyID
                                                        AND sos.ShipmentNbr = sosl.ShipmentNbr
        LEFT JOIN SOOrderShipment soship WITH ( NOLOCK ) ON sos.CompanyID = soship.CompanyID
                                                            AND sos.ShipmentNbr = soship.ShipmentNbr
                                                            AND sos.ShipmentType = soship.ShipmentType
        LEFT JOIN dbo.ARRegister arregister WITH ( NOLOCK ) ON soship.CompanyID = arregister.CompanyID
                                                              AND soship.InvoiceNbr = arregister.RefNbr
                                                              AND soship.InvoiceType = arregister.DocType
        LEFT JOIN dbo.INRegister inregister WITH ( NOLOCK ) ON inregister.CompanyID = soship.CompanyID
                                                              AND inregister.RefNbr = soship.InvoiceNbr
                                                              AND inregister.DocType = soship.InvoiceType
															  WHERE soo.OrderNbr='0364-000005';



SELECT  ao.CompanyID ,
        ao.DistributorID ,
        ao.DistributorLocationID ,
        ao.OrderNbr ,
        ao.OrderType ,
        ao.OrderDate ,
        ao.CustomerOrderNbr ,
        ao.CustomerID ,
        ao.CustomerLocationID ,
        ao.SalesRepsID ,
        SalesForeID = MAX(ao.SalesForeID) ,
        ao.Route ,
        ao.InventoryID ,
        UnitPrice = MAX(ao.UnitPrice) ,
        SKU = CONVERT(DECIMAL(18, 5), 1) ,
        TotalOrder = CONVERT(DECIMAL(18, 5), NULL) ,
        OrderQty = SUM(CASE WHEN ao.OrderType != 'CM' THEN ao.OrderQty
                            ELSE 0
                       END) , --Số lượng đặt
        OpenQty = SUM(CASE WHEN ao.OrderType != 'CM' THEN ao.OpenQty
                           ELSE 0
                      END) ,  --Số lượng chưa giao
        OrderAmt = SUM(CASE WHEN ao.OrderType != 'CM' THEN ao.CuryLineAmt
                            ELSE 0
                       END) , --Doanh số đặt
        OpenAmt = SUM(CASE WHEN ao.OrderType != 'CM' THEN ao.OpenAmt
                           ELSE 0
                      END) ,  --Doanh số chưa giao
        ExtPrice = SUM(CASE WHEN ao.OrderType != 'CM' THEN ao.CuryExtPrice
                            ELSE 0
                       END) ,
        Status = MAX(ao.Status) ,
        Checkbox = MAX(ao.Checkbox) ,
        RequestDate = MAX(ao.RequestDate) ,
        UsrSourceType = MAX(ao.UsrSourceType) ,
        OrderDesc = MAX(ao.OrderDesc) ,
        UsrDiscTot = MAX(ao.UsrDiscTot) ,
        OrderWeight = MAX(ao.OrderWeight) ,
        CuryOrderTotal = MAX(CuryOrderTotal) ,
        ActualOrderQty = SUM(ao.ShipQty) ,
        ShipmentNbr = MAX(ao.ShipmentNbr) ,
        InvoiceNbr = MAX(ao.InvoiceNbr) ,
        InvtRefNbr = MAX(ao.InvtRefNbr) ,
        ShipDate = MAX(ao.ShipDate) ,
        RefNbr = MAX(ao.RefNbr) ,
        DocDate = MAX(ao.DocDate) ,
        TranDate = MAX(ao.TranDate) ,
        ReasonCode = MAX(ao.ReasonCode) ,
        UsrPromotionID = MAX(ao.UsrPromotionID)
INTO    #tmpRawData
FROM    #tmpAllOrder ao
GROUP BY ao.CompanyID ,
        ao.DistributorID ,
        ao.DistributorLocationID ,
        ao.OrderNbr ,
        ao.OrderType ,
        ao.OrderDate ,
        ao.CustomerOrderNbr ,
        ao.CustomerID ,
        ao.CustomerLocationID ,
        ao.SalesRepsID ,
        ao.Route ,
        ao.InventoryID;
		   ---Lấy thông tin distributor
        SELECT  vd.CompanyID ,
                vd.DistributorID ,
                vd.DistributorCD ,
                vd.DistributorName ,
                vd.DistributorFullName ,
                vd.AddressLine1
        INTO    #tmpDistributor
        FROM    dbo.DMSViewDistributor vd WITH ( NOLOCK );

        CREATE NONCLUSTERED INDEX [#tmpDistributor_index]
        ON #tmpDistributor
        (
        CompanyID,
        DistributorID
        );
		SELECT  vii.CompanyID ,
                vii.InventoryID ,
                vii.InventoryCD ,
                vii.InventoryName ,
                vii.InventoryShortName ,
                vii.InventoryAbbrName ,
                vii.BaseUnit ,
                vii.PurchaseUnit ,
				vii.HierarchyCD,
				vii.HierarchyDescr,
                vii.Hierachy0Descr ,
                vii.Hierachy1Descr ,
                vii.Hierachy2Descr ,
                vii.Hierachy3Descr ,
                vii.Hierachy4Descr ,
                vii.Hierachy5Descr ,
                vii.Hierachy6Descr ,
                vii.Hierachy7Descr ,
                vii.Hierachy8Descr ,
                vii.Hierachy9Descr
        INTO    #tmpInventoryItem
        FROM    dbo.DMSViewInventoryItem vii WITH ( NOLOCK )
		   SELECT  d.DistributorCD ,
                d.DistributorName ,
                d.DistributorFullName ,
                d.AddressLine1 ,
                #tmp.SalesOrgID ,
                #tmp.SalesOrg_0_ValueName ,
                #tmp.SalesOrg_1_ValueName ,
                #tmp.SalesOrg_2_ValueName ,
                #tmp.SalesOrg_3_ValueName ,
                #tmp.SalesOrg_4_ValueName ,
                #tmp.SalesOrg_5_ValueName ,
                #tmp.SalesOrg_6_ValueName ,
                #tmp.SalesOrg_7_ValueName ,
                #tmp.SalesOrg_8_ValueName ,
                #tmp.SalesOrg_9_ValueName ,
                cl.CustomerCD ,
                cl.CustomerName ,
                cl.CustomerFullName ,
                cl.CustomerPhone1 ,
                cl.AddressLine1 ,
                cl.Attribute0Descr ,
                cl.Attribute1Descr ,
                cl.Attribute2Descr ,
                cl.Attribute3Descr ,
                cl.Attribute4Descr ,
                cl.Attribute5Descr ,
                cl.Attribute6Descr ,
                cl.Attribute7Descr ,
                cl.Attribute8Descr ,
                cl.Attribute9Descr ,
                rd.OrderNbr ,
                rd.OrderType ,
                rd.OrderDate ,
				rd.ActualOrderQty,
                OrderMonth = CAST(YEAR(rd.OrderDate) AS NVARCHAR(10))
                + RIGHT('0' + CAST(MONTH(rd.OrderDate) AS NVARCHAR(2)), 2) ,
                OrderQuater = CAST(YEAR(rd.OrderDate) AS NVARCHAR(10)) + 'Q'
                + CAST(DATEPART(qq, rd.OrderDate) AS NVARCHAR(1)) ,
                OrderYear = CAST(YEAR(rd.OrderDate) AS NVARCHAR(10)) ,
                rd.CustomerOrderNbr ,
                rd.Route ,
                rd.UnitPrice ,
                rd.SKU ,
                rd.TotalOrder ,
                rd.OrderQty ,
                rd.OrderAmt ,
				rd.ExtPrice,
                rd.OpenQty ,
                rd.OpenAmt ,
                rd.Status ,
                rd.Checkbox ,
                rd.RequestDate ,
                rd.UsrSourceType ,
                rd.OrderDesc ,
                rd.UsrDiscTot ,
                rd.OrderWeight ,
                CuryOrderTotal ,
                SalespersonCD = sf.EmployeeCD ,
                SalespersonName = sf.Descr ,
				SSCD = sf2.EmployeeCD,
				SSName = sf2.Descr,
                RouteDesc = route.Descr ,
                ii.InventoryCD ,
                ii.InventoryName ,
                ii.InventoryShortName ,
                ii.InventoryAbbrName ,
				ii.BaseUnit ,
				ii.HierarchyCD,
				ii.HierarchyDescr,
                ii.Hierachy0Descr ,
                ii.Hierachy1Descr ,
                ii.Hierachy2Descr ,
                ii.Hierachy3Descr ,
                ii.Hierachy4Descr ,
                ii.Hierachy5Descr ,
                ii.Hierachy6Descr ,
                ii.Hierachy7Descr ,
                ii.Hierachy8Descr ,
                ii.Hierachy9Descr,

				rd.ShipmentNbr ,
				rd.InvoiceNbr,
				rd.InvtRefNbr ,

				rd.ShipDate,
				rd.RefNbr,
				rd.DocDate ,
				rd.TranDate,
				reason.ReasonCodeID,
				ReasonDesc = reason.Descr,
				PromotionID = disc.PromotionCD,
				PromotionName = disc.Descr,
				disc.DealID,
				disc.SchemeID

        FROM    #tmpRawData rd
                LEFT JOIN #tmpDistributor d ON d.CompanyID = rd.CompanyID
                                               AND d.DistributorID = rd.DistributorID
                LEFT JOIN #tmp ON #tmp.CompanyID = rd.CompanyID
                                  AND #tmp.DistributorID = rd.DistributorID
                                  AND #tmp.DistributorLocationID = rd.DistributorLocationID
                LEFT JOIN dbo.DMSViewCustomerLocation cl WITH ( NOLOCK ) ON cl.CompanyID = rd.CompanyID
                                                              AND cl.CustomerID = rd.CustomerID
                                                              AND cl.LocationID = rd.CustomerLocationID

				LEFT JOIN dbo.DMSSalesForce sf ON sf.CompanyID = rd.CompanyID
                                                  AND sf.EmployeeID = rd.SalesRepsID

				LEFT JOIN dbo.DMSSalesForce sf2 ON sf2.CompanyID = rd.CompanyID
                                                  AND sf2.EmployeeID = rd.SalesForeID

                LEFT JOIN #tmpInventoryItem ii ON ii.CompanyID = rd.CompanyID
                                                  AND ii.InventoryID = rd.InventoryID
                LEFT JOIN dbo.INUnit iu ON iu.CompanyID = ii.CompanyID
                                           AND iu.InventoryID = ii.InventoryID
                                           AND iu.FromUnit = ii.PurchaseUnit
                                           AND iu.ToUnit = ii.BaseUnit
                                           AND iu.UnitMultDiv = 'M'
                LEFT JOIN dbo.DMSRoute route ON route.CompanyID = rd.CompanyID
                                                AND route.RouteCD = rd.Route
				LEFT JOIN dbo.ReasonCode reason ON rd.CompanyID = reason.CompanyID
												AND rd.ReasonCode =  reason.ReasonCodeID
				LEFT JOIN DMSPRODiscount disc ON disc.CompanyID = rd.CompanyID
												AND disc.PromotionID= rd.UsrPromotionID
		DROP TABLE #tmpRawData
		DROP TABLE #tmpAllOrder
		DROP TABLE #tmpDistributor
		DROP TABLE #tmpInventoryItem
		DROP TABLE #tmp