Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DECLARE @_CompanyID INT = 3;
- DECLARE @_FromDate DATETIME = '2019-05-01';
- DECLARE @_ToDate DATETIME = '2019-05-31';
- SELECT DISTINCT
- bldl.CompanyID,
- bldl.ID,
- bldl.DistributorID,
- DistributorLocationID = bldl.LocationID,
- blst.SalesOrg_0_ValueID,
- blst.SalesOrg_1_ValueID,
- blst.SalesOrg_2_ValueID,
- blst.SalesOrg_3_ValueID,
- blst.SalesOrg_4_ValueID,
- blst.SalesOrg_5_ValueID,
- blst.SalesOrg_6_ValueID,
- blst.SalesOrg_7_ValueID,
- blst.SalesOrg_8_ValueID,
- blst.SalesOrg_9_ValueID,
- SalesForce_0_ID = blsf0.EmployeeID,
- SalesForce_1_ID = blsf1.EmployeeID,
- SalesForce_2_ID = blsf2.EmployeeID,
- SalesForce_3_ID = blsf3.EmployeeID,
- SalesForce_4_ID = blsf4.EmployeeID,
- SalesForce_5_ID = blsf5.EmployeeID,
- SalesForce_6_ID = blsf6.EmployeeID,
- SalesForce_7_ID = blsf7.EmployeeID,
- SalesForce_8_ID = blsf8.EmployeeID,
- SalesForce_9_ID = blsf9.EmployeeID,
- bldl.ExpirationDate
- INTO #tmpDistributorLocation
- FROM dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
- LEFT JOIN dbo.DMSBLSalesTerritory blst WITH (NOLOCK)
- ON blst.CompanyID = bldl.CompanyID
- AND blst.ID = bldl.BLSalesTerritoryID
- LEFT JOIN dbo.DMSBLSalesForce blsf0 WITH (NOLOCK)
- ON blsf0.CompanyID = blst.CompanyID
- AND blsf0.SalesOrgValueID = blst.SalesOrg_0_ValueID
- AND blsf0.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf1 WITH (NOLOCK)
- ON blsf1.CompanyID = blst.CompanyID
- AND blsf1.SalesOrgValueID = blst.SalesOrg_1_ValueID
- AND blsf1.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf2 WITH (NOLOCK)
- ON blsf2.CompanyID = blst.CompanyID
- AND blsf2.SalesOrgValueID = blst.SalesOrg_2_ValueID
- AND blsf2.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf3 WITH (NOLOCK)
- ON blsf3.CompanyID = blst.CompanyID
- AND blsf3.SalesOrgValueID = blst.SalesOrg_3_ValueID
- AND blsf3.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf4 WITH (NOLOCK)
- ON blsf4.CompanyID = blst.CompanyID
- AND blsf4.SalesOrgValueID = blst.SalesOrg_4_ValueID
- AND blsf4.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf5 WITH (NOLOCK)
- ON blsf5.CompanyID = blst.CompanyID
- AND blsf5.SalesOrgValueID = blst.SalesOrg_5_ValueID
- AND blsf5.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf6 WITH (NOLOCK)
- ON blsf6.CompanyID = blst.CompanyID
- AND blsf6.SalesOrgValueID = blst.SalesOrg_6_ValueID
- AND blsf6.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf7 WITH (NOLOCK)
- ON blsf7.CompanyID = blst.CompanyID
- AND blsf7.SalesOrgValueID = blst.SalesOrg_7_ValueID
- AND blsf7.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf8 WITH (NOLOCK)
- ON blsf8.CompanyID = blst.CompanyID
- AND blsf8.SalesOrgValueID = blst.SalesOrg_8_ValueID
- AND blsf8.ExpirationDate IS NULL
- LEFT JOIN dbo.DMSBLSalesForce blsf9 WITH (NOLOCK)
- ON blsf9.CompanyID = blst.CompanyID
- AND blsf9.SalesOrgValueID = blst.SalesOrg_9_ValueID
- AND blsf9.ExpirationDate IS NULL
- WHERE bldl.CompanyID = @_CompanyID;
- SELECT tmp.*,
- dl.DistributorLocationID,
- dl.SalesOrg_0_ValueID,
- dl.SalesOrg_1_ValueID,
- dl.SalesOrg_2_ValueID,
- dl.SalesOrg_3_ValueID,
- dl.SalesOrg_4_ValueID,
- dl.SalesOrg_5_ValueID,
- dl.SalesOrg_6_ValueID,
- dl.SalesOrg_7_ValueID,
- dl.SalesOrg_8_ValueID,
- dl.SalesOrg_9_ValueID,
- dl.SalesForce_0_ID,
- dl.SalesForce_1_ID,
- dl.SalesForce_2_ID,
- dl.SalesForce_3_ID,
- dl.SalesForce_4_ID,
- dl.SalesForce_5_ID,
- dl.SalesForce_6_ID,
- dl.SalesForce_7_ID,
- dl.SalesForce_8_ID,
- dl.SalesForce_9_ID
- INTO #tmpSORawSales
- FROM
- (
- SELECT blrs.CompanyID,
- blrs.BLDistributorLocationID,
- blrs.SalespersonID,
- blrs.OrderType,
- blrs.OrderNbr,
- blrs.OrderDate,
- blrs.SourceType,
- blrs.Route,
- blrs.CustomerID,
- blrs.CustomerLocationID,
- blrs.InventoryID,
- TranAmt = CASE
- WHEN blrs.OrderType = 'CM' THEN
- blrs.TranAmt * -1
- ELSE
- blrs.TranAmt
- END,
- blrs.ShippedQty,
- blrs.IsValidDate,
- blrs.IsValidDistance,
- blrs.IsRevenueDeduction,
- blrs.ReasonCode,
- blrs.OrigOrderNbr,
- blrs.OrigOrderType
- FROM dbo.DMSBLRawSales blrs WITH (NOLOCK)
- WHERE blrs.CompanyID = @_CompanyID
- AND blrs.OrderDate
- BETWEEN @_FromDate AND @_ToDate
- AND blrs.IsDispose = 0
- AND blrs.IsFree = 0
- AND blrs.SourceType != 'TMK'
- AND
- (
- blrs.OrderType <> 'CM'
- OR blrs.IsRevenueDeduction = 1
- )
- UNION ALL
- SELECT blrsh.CompanyID,
- blrsh.BLDistributorLocationID,
- blrsh.SalespersonID,
- blrsh.OrderType,
- blrsh.OrderNbr,
- blrsh.OrderDate,
- blrsh.SourceType,
- blrsh.Route,
- blrsh.CustomerID,
- blrsh.CustomerLocationID,
- blrsh.InventoryID,
- TranAmt = CASE
- WHEN blrsh.OrderType = 'CM' THEN
- blrsh.TranAmt * -1
- ELSE
- blrsh.TranAmt
- END,
- blrsh.ShippedQty,
- blrsh.IsValidDate,
- blrsh.IsValidDistance,
- blrsh.IsRevenueDeduction,
- blrsh.ReasonCode,
- blrsh.OrigOrderNbr,
- blrsh.OrigOrderType
- FROM dbo.DMSBLRawSalesHistory blrsh WITH (NOLOCK)
- JOIN dbo.DMSBLDistributorLocation dl WITH (NOLOCK)
- ON dl.CompanyID = blrsh.CompanyID
- AND dl.ID = blrsh.BLDistributorLocationID
- WHERE blrsh.CompanyID = @_CompanyID
- AND blrsh.OrderDate
- BETWEEN @_FromDate AND @_ToDate
- AND blrsh.IsDispose = 0
- AND blrsh.IsFree = 0
- AND blrsh.SourceType != 'TMK'
- -- đối với PNC có config trừ doanh số giao dịch trả hàng
- -- nên phải lấy tất cả các giao dịch và tùy config để lấy
- AND
- (
- blrsh.OrderType <> 'CM'
- OR blrsh.IsRevenueDeduction = 1
- )
- ) tmp
- JOIN #tmpDistributorLocation dl WITH (NOLOCK)
- ON dl.CompanyID = tmp.CompanyID
- AND dl.ID = tmp.BLDistributorLocationID;
- SELECT rs.CompanyID,
- rs.SalespersonID,
- rs.Route,
- rs.OrderDate,
- rs.SourceType,
- rs.CustomerID,
- rs.CustomerLocationID,
- rs.IsValidDate,
- rs.IsValidDistance,
- Multiply = CASE
- WHEN rs.IsRevenueDeduction = 1 THEN
- -1
- ELSE
- 1
- END,
- PC = 1,
- SKU = COUNT(DISTINCT rs.InventoryID),
- TranAmt = SUM(rs.TranAmt),
- ShippedQty = SUM(rs.ShippedQty),
- rs.SalesOrg_0_ValueID,
- rs.SalesOrg_1_ValueID,
- rs.SalesOrg_2_ValueID,
- rs.SalesOrg_3_ValueID,
- rs.SalesOrg_4_ValueID,
- rs.SalesOrg_5_ValueID,
- rs.SalesOrg_6_ValueID,
- rs.SalesOrg_7_ValueID,
- rs.SalesOrg_8_ValueID,
- rs.SalesOrg_9_ValueID,
- rs.SalesForce_0_ID,
- rs.SalesForce_1_ID,
- rs.SalesForce_2_ID,
- rs.SalesForce_3_ID,
- rs.SalesForce_4_ID,
- rs.SalesForce_5_ID,
- rs.SalesForce_6_ID,
- rs.SalesForce_7_ID,
- rs.SalesForce_8_ID,
- rs.SalesForce_9_ID,
- Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
- FROM #tmpSORawSales rs
- LEFT JOIN MRCDMS..DMSViewInventoryItem inv
- ON rs.CompanyID = inv.CompanyID
- AND rs.InventoryID = inv.InventoryID
- WHERE rs.OrderDate
- BETWEEN @_FromDate AND @_ToDate
- AND rs.ShippedQty >= 1
- AND ABS(rs.TranAmt) >= 1
- AND rs.SalespersonID = 1903
- GROUP BY rs.CompanyID,
- rs.SalespersonID,
- rs.Route,
- rs.OrderDate,
- rs.SourceType,
- rs.CustomerID,
- rs.CustomerLocationID,
- rs.IsValidDate,
- rs.IsValidDistance,
- rs.IsRevenueDeduction,
- rs.SalesOrg_0_ValueID,
- rs.SalesOrg_1_ValueID,
- rs.SalesOrg_2_ValueID,
- rs.SalesOrg_3_ValueID,
- rs.SalesOrg_4_ValueID,
- rs.SalesOrg_5_ValueID,
- rs.SalesOrg_6_ValueID,
- rs.SalesOrg_7_ValueID,
- rs.SalesOrg_8_ValueID,
- rs.SalesOrg_9_ValueID,
- rs.SalesForce_0_ID,
- rs.SalesForce_1_ID,
- rs.SalesForce_2_ID,
- rs.SalesForce_3_ID,
- rs.SalesForce_4_ID,
- rs.SalesForce_5_ID,
- rs.SalesForce_6_ID,
- rs.SalesForce_7_ID,
- rs.SalesForce_8_ID,
- rs.SalesForce_9_ID;
- DROP TABLE #tmpDistributorLocation;
- DROP TABLE #tmpSORawSales;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement