API tools faq
paste
Advertisement
Guest User

Untitled

a guest
May 21st, 2019
106
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 9.25 KB | None | 0 0
raw download clone embed print report
  1. DECLARE @_CompanyID INT = 3;
  2. DECLARE @_FromDate DATETIME = '2019-05-01';
  3. DECLARE @_ToDate DATETIME = '2019-05-31';
  4.  
  5. SELECT DISTINCT
  6. bldl.CompanyID,
  7. bldl.ID,
  8. bldl.DistributorID,
  9. DistributorLocationID = bldl.LocationID,
  10. blst.SalesOrg_0_ValueID,
  11. blst.SalesOrg_1_ValueID,
  12. blst.SalesOrg_2_ValueID,
  13. blst.SalesOrg_3_ValueID,
  14. blst.SalesOrg_4_ValueID,
  15. blst.SalesOrg_5_ValueID,
  16. blst.SalesOrg_6_ValueID,
  17. blst.SalesOrg_7_ValueID,
  18. blst.SalesOrg_8_ValueID,
  19. blst.SalesOrg_9_ValueID,
  20. SalesForce_0_ID = blsf0.EmployeeID,
  21. SalesForce_1_ID = blsf1.EmployeeID,
  22. SalesForce_2_ID = blsf2.EmployeeID,
  23. SalesForce_3_ID = blsf3.EmployeeID,
  24. SalesForce_4_ID = blsf4.EmployeeID,
  25. SalesForce_5_ID = blsf5.EmployeeID,
  26. SalesForce_6_ID = blsf6.EmployeeID,
  27. SalesForce_7_ID = blsf7.EmployeeID,
  28. SalesForce_8_ID = blsf8.EmployeeID,
  29. SalesForce_9_ID = blsf9.EmployeeID,
  30. bldl.ExpirationDate
  31. INTO #tmpDistributorLocation
  32. FROM dbo.DMSBLDistributorLocation bldl WITH (NOLOCK)
  33. LEFT JOIN dbo.DMSBLSalesTerritory blst WITH (NOLOCK)
  34. ON blst.CompanyID = bldl.CompanyID
  35. AND blst.ID = bldl.BLSalesTerritoryID
  36. LEFT JOIN dbo.DMSBLSalesForce blsf0 WITH (NOLOCK)
  37. ON blsf0.CompanyID = blst.CompanyID
  38. AND blsf0.SalesOrgValueID = blst.SalesOrg_0_ValueID
  39. AND blsf0.ExpirationDate IS NULL
  40. LEFT JOIN dbo.DMSBLSalesForce blsf1 WITH (NOLOCK)
  41. ON blsf1.CompanyID = blst.CompanyID
  42. AND blsf1.SalesOrgValueID = blst.SalesOrg_1_ValueID
  43. AND blsf1.ExpirationDate IS NULL
  44. LEFT JOIN dbo.DMSBLSalesForce blsf2 WITH (NOLOCK)
  45. ON blsf2.CompanyID = blst.CompanyID
  46. AND blsf2.SalesOrgValueID = blst.SalesOrg_2_ValueID
  47. AND blsf2.ExpirationDate IS NULL
  48. LEFT JOIN dbo.DMSBLSalesForce blsf3 WITH (NOLOCK)
  49. ON blsf3.CompanyID = blst.CompanyID
  50. AND blsf3.SalesOrgValueID = blst.SalesOrg_3_ValueID
  51. AND blsf3.ExpirationDate IS NULL
  52. LEFT JOIN dbo.DMSBLSalesForce blsf4 WITH (NOLOCK)
  53. ON blsf4.CompanyID = blst.CompanyID
  54. AND blsf4.SalesOrgValueID = blst.SalesOrg_4_ValueID
  55. AND blsf4.ExpirationDate IS NULL
  56. LEFT JOIN dbo.DMSBLSalesForce blsf5 WITH (NOLOCK)
  57. ON blsf5.CompanyID = blst.CompanyID
  58. AND blsf5.SalesOrgValueID = blst.SalesOrg_5_ValueID
  59. AND blsf5.ExpirationDate IS NULL
  60. LEFT JOIN dbo.DMSBLSalesForce blsf6 WITH (NOLOCK)
  61. ON blsf6.CompanyID = blst.CompanyID
  62. AND blsf6.SalesOrgValueID = blst.SalesOrg_6_ValueID
  63. AND blsf6.ExpirationDate IS NULL
  64. LEFT JOIN dbo.DMSBLSalesForce blsf7 WITH (NOLOCK)
  65. ON blsf7.CompanyID = blst.CompanyID
  66. AND blsf7.SalesOrgValueID = blst.SalesOrg_7_ValueID
  67. AND blsf7.ExpirationDate IS NULL
  68. LEFT JOIN dbo.DMSBLSalesForce blsf8 WITH (NOLOCK)
  69. ON blsf8.CompanyID = blst.CompanyID
  70. AND blsf8.SalesOrgValueID = blst.SalesOrg_8_ValueID
  71. AND blsf8.ExpirationDate IS NULL
  72. LEFT JOIN dbo.DMSBLSalesForce blsf9 WITH (NOLOCK)
  73. ON blsf9.CompanyID = blst.CompanyID
  74. AND blsf9.SalesOrgValueID = blst.SalesOrg_9_ValueID
  75. AND blsf9.ExpirationDate IS NULL
  76. WHERE bldl.CompanyID = @_CompanyID;
  77.  
  78. SELECT tmp.*,
  79. dl.DistributorLocationID,
  80. dl.SalesOrg_0_ValueID,
  81. dl.SalesOrg_1_ValueID,
  82. dl.SalesOrg_2_ValueID,
  83. dl.SalesOrg_3_ValueID,
  84. dl.SalesOrg_4_ValueID,
  85. dl.SalesOrg_5_ValueID,
  86. dl.SalesOrg_6_ValueID,
  87. dl.SalesOrg_7_ValueID,
  88. dl.SalesOrg_8_ValueID,
  89. dl.SalesOrg_9_ValueID,
  90. dl.SalesForce_0_ID,
  91. dl.SalesForce_1_ID,
  92. dl.SalesForce_2_ID,
  93. dl.SalesForce_3_ID,
  94. dl.SalesForce_4_ID,
  95. dl.SalesForce_5_ID,
  96. dl.SalesForce_6_ID,
  97. dl.SalesForce_7_ID,
  98. dl.SalesForce_8_ID,
  99. dl.SalesForce_9_ID
  100. INTO #tmpSORawSales
  101. FROM
  102. (
  103. SELECT blrs.CompanyID,
  104. blrs.BLDistributorLocationID,
  105. blrs.SalespersonID,
  106. blrs.OrderType,
  107. blrs.OrderNbr,
  108. blrs.OrderDate,
  109. blrs.SourceType,
  110. blrs.Route,
  111. blrs.CustomerID,
  112. blrs.CustomerLocationID,
  113. blrs.InventoryID,
  114. TranAmt = CASE
  115. WHEN blrs.OrderType = 'CM' THEN
  116. blrs.TranAmt * -1
  117. ELSE
  118. blrs.TranAmt
  119. END,
  120. blrs.ShippedQty,
  121. blrs.IsValidDate,
  122. blrs.IsValidDistance,
  123. blrs.IsRevenueDeduction,
  124. blrs.ReasonCode,
  125. blrs.OrigOrderNbr,
  126. blrs.OrigOrderType
  127. FROM dbo.DMSBLRawSales blrs WITH (NOLOCK)
  128. WHERE blrs.CompanyID = @_CompanyID
  129. AND blrs.OrderDate
  130. BETWEEN @_FromDate AND @_ToDate
  131. AND blrs.IsDispose = 0
  132. AND blrs.IsFree = 0
  133. AND blrs.SourceType != 'TMK'
  134. AND
  135. (
  136. blrs.OrderType <> 'CM'
  137. OR blrs.IsRevenueDeduction = 1
  138. )
  139. UNION ALL
  140. SELECT blrsh.CompanyID,
  141. blrsh.BLDistributorLocationID,
  142. blrsh.SalespersonID,
  143. blrsh.OrderType,
  144. blrsh.OrderNbr,
  145. blrsh.OrderDate,
  146. blrsh.SourceType,
  147. blrsh.Route,
  148. blrsh.CustomerID,
  149. blrsh.CustomerLocationID,
  150. blrsh.InventoryID,
  151. TranAmt = CASE
  152. WHEN blrsh.OrderType = 'CM' THEN
  153. blrsh.TranAmt * -1
  154. ELSE
  155. blrsh.TranAmt
  156. END,
  157. blrsh.ShippedQty,
  158. blrsh.IsValidDate,
  159. blrsh.IsValidDistance,
  160. blrsh.IsRevenueDeduction,
  161. blrsh.ReasonCode,
  162. blrsh.OrigOrderNbr,
  163. blrsh.OrigOrderType
  164. FROM dbo.DMSBLRawSalesHistory blrsh WITH (NOLOCK)
  165. JOIN dbo.DMSBLDistributorLocation dl WITH (NOLOCK)
  166. ON dl.CompanyID = blrsh.CompanyID
  167. AND dl.ID = blrsh.BLDistributorLocationID
  168. WHERE blrsh.CompanyID = @_CompanyID
  169. AND blrsh.OrderDate
  170. BETWEEN @_FromDate AND @_ToDate
  171. AND blrsh.IsDispose = 0
  172. AND blrsh.IsFree = 0
  173. AND blrsh.SourceType != 'TMK'
  174. -- đối với PNC có config trừ doanh số giao dịch trả hàng
  175. -- nên phải lấy tất cả các giao dịch và tùy config để lấy
  176. AND
  177. (
  178. blrsh.OrderType <> 'CM'
  179. OR blrsh.IsRevenueDeduction = 1
  180. )
  181. ) tmp
  182. JOIN #tmpDistributorLocation dl WITH (NOLOCK)
  183. ON dl.CompanyID = tmp.CompanyID
  184. AND dl.ID = tmp.BLDistributorLocationID;
  185.  
  186. SELECT rs.CompanyID,
  187. rs.SalespersonID,
  188. rs.Route,
  189. rs.OrderNbr,
  190. rs.OrderType,
  191. rs.OrderDate,
  192. rs.SourceType,
  193. rs.CustomerID,
  194. rs.CustomerLocationID,
  195. rs.IsValidDate,
  196. rs.IsValidDistance,
  197. Multiply = CASE
  198. WHEN rs.IsRevenueDeduction = 1 THEN
  199. -1
  200. ELSE
  201. 1
  202. END,
  203. PC = 1,
  204. SKU = COUNT(DISTINCT rs.InventoryID),
  205. TranAmt = SUM(rs.TranAmt),
  206. ShippedQty = SUM(rs.ShippedQty),
  207. rs.SalesOrg_0_ValueID,
  208. rs.SalesOrg_1_ValueID,
  209. rs.SalesOrg_2_ValueID,
  210. rs.SalesOrg_3_ValueID,
  211. rs.SalesOrg_4_ValueID,
  212. rs.SalesOrg_5_ValueID,
  213. rs.SalesOrg_6_ValueID,
  214. rs.SalesOrg_7_ValueID,
  215. rs.SalesOrg_8_ValueID,
  216. rs.SalesOrg_9_ValueID,
  217. rs.SalesForce_0_ID,
  218. rs.SalesForce_1_ID,
  219. rs.SalesForce_2_ID,
  220. rs.SalesForce_3_ID,
  221. rs.SalesForce_4_ID,
  222. rs.SalesForce_5_ID,
  223. rs.SalesForce_6_ID,
  224. rs.SalesForce_7_ID,
  225. rs.SalesForce_8_ID,
  226. rs.SalesForce_9_ID,
  227. Hie3 = COUNT(DISTINCT inv.Hierachy3ID)
  228. FROM #tmpSORawSales rs
  229. LEFT JOIN MRCDMS..DMSViewInventoryItem inv
  230. ON rs.CompanyID = inv.CompanyID
  231. AND rs.InventoryID = inv.InventoryID
  232. WHERE rs.OrderDate
  233. BETWEEN @_FromDate AND @_ToDate
  234. AND rs.ShippedQty >= 1
  235. AND ABS(rs.TranAmt) >= 1
  236. AND rs.SalespersonID = 1903
  237. GROUP BY rs.CompanyID,
  238. rs.SalespersonID,
  239. rs.Route,
  240. rs.OrderNbr,
  241. rs.OrderType,
  242. rs.OrderDate,
  243. rs.SourceType,
  244. rs.CustomerID,
  245. rs.CustomerLocationID,
  246. rs.IsValidDate,
  247. rs.IsValidDistance,
  248. rs.IsRevenueDeduction,
  249. rs.SalesOrg_0_ValueID,
  250. rs.SalesOrg_1_ValueID,
  251. rs.SalesOrg_2_ValueID,
  252. rs.SalesOrg_3_ValueID,
  253. rs.SalesOrg_4_ValueID,
  254. rs.SalesOrg_5_ValueID,
  255. rs.SalesOrg_6_ValueID,
  256. rs.SalesOrg_7_ValueID,
  257. rs.SalesOrg_8_ValueID,
  258. rs.SalesOrg_9_ValueID,
  259. rs.SalesForce_0_ID,
  260. rs.SalesForce_1_ID,
  261. rs.SalesForce_2_ID,
  262. rs.SalesForce_3_ID,
  263. rs.SalesForce_4_ID,
  264. rs.SalesForce_5_ID,
  265. rs.SalesForce_6_ID,
  266. rs.SalesForce_7_ID,
  267. rs.SalesForce_8_ID,
  268. rs.SalesForce_9_ID;
  269.  
  270. DROP TABLE #tmpDistributorLocation;
  271. DROP TABLE #tmpSORawSales;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement
Public Pastes
Advertisement
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy.  OK, I Understand
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!