API tools faq
paste
Advertisement
Guest User

Untitled

a guest
Dec 11th, 2018
85
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
text 15.88 KB | None | 0 0
raw download clone embed print report
  1.  
  2.  
  3. CREATE VIEW InVoiceView AS
  4. SELECT soLine.CompanyID ,
  5. soLine.BranchID ,
  6. DocType = soLine.OrderType ,
  7. RefNbr = soLine.OrderNbr ,
  8. soLine.CustomerID ,
  9. soorder.CustomerLocationID ,
  10. soorder.UsrSalesReps ,
  11. soorder.OrderDate ,
  12. SOOrderType = soLine.OrderType ,
  13. SOOrderNbr = soLine.OrderNbr ,
  14. soLineSplit.InventoryID ,
  15. UOMSplit = soLineSplit.UOM ,
  16. UOMLine = soLine.UOM ,
  17. --soLine.InventoryID ,
  18. Qty = soLineSplit.BaseQty ,
  19. soLine.CuryUnitPrice ,
  20. LotSerialNbr = ISNULL(ISNULL(soLineSplit.LotSerialNbr,
  21. soLine.LotSerialNbr), 0) ,
  22. ExpireDate = ISNULL(soLineSplit.ExpireDate, soLine.ExpireDate) ,
  23. OrderFreeAmt = 0 ,
  24. 0.0 OrderFreeQty ,
  25. '' PromotionCD ,
  26. '' SchemeID ,
  27. '' DealID ,
  28. '' Descr ,
  29. 'S' AS isPromotion ,
  30. OrderDescr = soorder.OrderDesc ,
  31. sos.InvoiceNbr ,
  32. N'Line Hàng bán' typeLine
  33. -- INTO #tmpdata
  34. FROM dbo.SOLine soLine
  35. LEFT JOIN dbo.SOLineSplit soLineSplit ON soLine.CompanyID = soLineSplit.CompanyID
  36. AND soLine.OrderType = soLineSplit.OrderType
  37. AND soLine.OrderNbr = soLineSplit.OrderNbr
  38. AND soLine.LineNbr = soLineSplit.LineNbr
  39. INNER JOIN SOOrder soorder ON soorder.CompanyID = soLine.CompanyID
  40. AND soorder.OrderNbr = soLine.OrderNbr
  41. AND soorder.OrderType = soLine.OrderType
  42. AND soorder.BranchID = soLine.BranchID
  43. LEFT JOIN dbo.SOOrderShipment sos ON sos.CompanyID = soorder.CompanyID
  44. AND sos.OrderNbr = soorder.OrderNbr
  45. AND sos.OrderType = soorder.OrderType
  46. INNER JOIN dbo.InventoryItem inventory ON soLine.CompanyID = inventory.CompanyID
  47. AND soLine.InventoryID = inventory.InventoryID
  48. LEFT JOIN dbo.INUnit unit ON unit.CompanyID = soLine.CompanyID
  49. AND unit.InventoryID = soLine.InventoryID
  50. --soLineSplit lun lưu là baseUnit
  51. AND unit.FromUnit = inventory.SalesUnit
  52. AND unit.ToUnit = inventory.BaseUnit
  53. WHERE
  54. --ko in đơn trạng thái 'Cancel' và 'Hold'
  55. soorder.Status <> 'L'
  56. AND soorder.Status <> 'H'
  57.  
  58. --AND ( @_InvoiceNBR = ''
  59. -- --OR ( soLine.OrderNbr IN ( SELECT String
  60. -- -- FROM #tmpInvoiceNBR ) )
  61. -- )
  62. AND soLine.OrderNbr IS NOT NULL
  63. --AND soLine.CompanyID = @CompanyID
  64. --AND soLine.BranchID = @BranchID
  65. AND soLine.IsFree = 0
  66. --ko in ra line hàng có qty==0
  67. AND soLine.OrderQty <> 0
  68. --AND ( ( soorder.UsrSalesReps = @_SalesManID )
  69. -- OR @_SalesManID = 0
  70. -- )
  71. AND soLine.SiteID IN ( SELECT SiteID
  72. FROM dbo.DMSViewINSiteWithSecurity
  73. --WHERE CompanyID = @CompanyID
  74. -- AND DistributorID = @BranchID
  75. -- AND Username = @User
  76. )
  77. UNION ALL
  78. --lấy KM hàng tặng
  79. SELECT soLine.CompanyID ,
  80. soLine.BranchID ,
  81. DocType = soLine.OrderType ,
  82. RefNbr = soLine.OrderNbr ,
  83. MAX(soLine.CustomerID) ,
  84. MAX(soorder.CustomerLocationID) ,
  85. MAX(soorder.UsrSalesReps) ,
  86. MAX(soorder.OrderDate) ,
  87. SOOrderType = soLine.OrderType ,
  88. SOOrderNbr = soLine.OrderNbr ,
  89. soLineSplit.InventoryID ,
  90. UOMSplit = '' ,
  91. UOMLine = '' ,
  92. SUM(soLineSplit.BaseQty) AS Qty ,
  93. 0.0 AS CuryUnitPrice ,
  94. CASE WHEN sodd.Type = 'S' THEN NULL
  95. ELSE soLineSplit.LotSerialNbr
  96. END AS LotSerialNbr ,
  97. ExpireDate = MAX(ISNULL(soLineSplit.ExpireDate,
  98. soLine.ExpireDate)) ,
  99. 0.0 AS OrderFreeAmt ,
  100. SUM(sodd.OrderFreeQty) ,
  101. disinfo.PromotionCD ,
  102. MAX(disinfo.SchemeID) ,
  103. MAX(disinfo.DealID) ,
  104. MAX(disinfo.Descr) ,
  105. 'F' isPromotion ,
  106. OrderDescr = MAX(soorder.OrderDesc) ,
  107. -- UsrIsSOInvoicePrint = CAST(MAX(CAST(soorder.UsrIsSOInvoicePrint AS INT)) AS BIT) ,
  108. -- InvoiceDate = MAX(sos.UsrInvoiceDate) ,
  109. MAX(sos.InvoiceNbr) ,
  110. N'line KM hàng' typeLine
  111. FROM dbo.SOLine soLine
  112. LEFT JOIN dbo.SOLineSplit soLineSplit ON soLine.CompanyID = soLineSplit.CompanyID
  113. AND soLine.OrderType = soLineSplit.OrderType
  114. AND soLine.OrderNbr = soLineSplit.OrderNbr
  115. AND soLine.LineNbr = soLineSplit.LineNbr
  116. INNER JOIN SOOrder soorder ON soorder.CompanyID = soLine.CompanyID
  117. AND soorder.OrderNbr = soLine.OrderNbr
  118. AND soorder.OrderType = soLine.OrderType
  119. AND soorder.BranchID = soLine.BranchID
  120. LEFT JOIN dbo.DMSPROSODiscountDetail sodd ON sodd.CompanyID = soLine.CompanyID
  121. AND sodd.OrderNbr = soLine.OrderNbr
  122. AND sodd.OrderType = soLine.OrderType
  123. AND sodd.InventoryID = soLine.InventoryID
  124. AND sodd.LineNbr = soLine.LineNbr
  125. AND ( sodd.Type = 'F' )
  126. LEFT JOIN dbo.DMSPRODiscount disinfo ON disinfo.CompanyID = sodd.CompanyID
  127. AND disinfo.PromotionID = sodd.PromotionID
  128. LEFT JOIN dbo.SOOrderShipment sos ON sos.CompanyID = soorder.CompanyID
  129. AND sos.OrderNbr = soorder.OrderNbr
  130. AND sos.OrderType = soorder.OrderType
  131. WHERE
  132. soLine.OrderType <> 'CM'
  133. --ko in đơn trạng thái 'Cancel' và 'Hold'
  134. AND soorder.Status <> 'L'
  135. AND soorder.Status <> 'H'
  136. --thao:them dk vansale or presale
  137.  
  138. AND ( soLine.OrderNbr IS NOT NULL )
  139.  
  140. AND soLine.IsFree = 1
  141.  
  142. --ko in ra line hàng có qty==0
  143. AND soLine.OrderQty <> 0
  144. AND ISNULL(soLine.UsrIncentiveID, '') = ''
  145. AND soLine.SiteID IN ( SELECT SiteID
  146. FROM dbo.DMSViewINSiteWithSecurity
  147. )
  148. GROUP BY soLine.CompanyID ,
  149. soLine.BranchID ,
  150. soLine.OrderType ,
  151. soLine.OrderNbr ,
  152. soLineSplit.InventoryID ,
  153. CASE WHEN sodd.Type = 'S' THEN NULL
  154. ELSE soLineSplit.LotSerialNbr
  155. END ,
  156. disinfo.PromotionCD
  157. UNION ALL
  158. -- TAN hoang lay them mat hang tra thuong
  159. SELECT soLine.CompanyID ,
  160. soLine.BranchID ,
  161. DocType = soLine.OrderType ,
  162. RefNbr = soLine.OrderNbr ,
  163. MAX(soorder.CustomerID) ,
  164. MAX(soorder.CustomerLocationID) ,
  165. MAX(soorder.UsrSalesReps) ,
  166. MAX(soorder.OrderDate) ,
  167. SOOrderType = soLine.OrderType ,
  168. SOOrderNbr = soLine.OrderNbr ,
  169. soLineSplit.InventoryID ,
  170. UOMSplit = '' ,
  171. UOMLine = '' ,
  172. SUM(soLineSplit.BaseQty) AS Qty ,
  173. 0.0 AS CuryUnitPrice ,
  174. soLineSplit.LotSerialNbr ,
  175. ExpireDate = MAX(ISNULL(soLineSplit.ExpireDate,
  176. soLine.ExpireDate)) ,
  177. 0.0 OrderFreeAmt ,
  178. OrderFreeQty = SUM(soLineSplit.BaseQty) ,
  179. evaluation.RefNbr ,
  180. CONVERT(VARCHAR(10), MAX(evaluation.ProgramID)) ,
  181. CONVERT(VARCHAR(10), MAX(evaluation.ProgramID)) ,
  182. MAX(evaluation.Descr) ,
  183. 'F' isPromotion ,
  184. OrderDescr = MAX(soorder.OrderDesc) ,
  185. MAX(sos.InvoiceNbr) ,
  186. N'Line Trả thưởng hàng ' AS TypeLine
  187. FROM dbo.SOLine soLine
  188. LEFT JOIN dbo.SOLineSplit soLineSplit ON soLine.CompanyID = soLineSplit.CompanyID
  189. AND soLine.OrderType = soLineSplit.OrderType
  190. AND soLine.OrderNbr = soLineSplit.OrderNbr
  191. AND soLine.LineNbr = soLineSplit.LineNbr
  192. INNER JOIN SOOrder soorder ON soorder.CompanyID = soLine.CompanyID
  193. AND soorder.OrderNbr = soLine.OrderNbr
  194. AND soorder.OrderType = soLine.OrderType
  195. AND soorder.BranchID = soLine.BranchID
  196. LEFT JOIN dbo.DMSPROSODiscountDetail sodd ON sodd.CompanyID = soLine.CompanyID
  197. AND sodd.OrderNbr = soLine.OrderNbr
  198. AND sodd.OrderType = soLine.OrderType
  199. AND sodd.InventoryID = soLine.InventoryID
  200. AND sodd.LineNbr = soLine.LineNbr
  201. AND ( sodd.Type = 'F' )
  202. LEFT JOIN DMSEvaluationDefine evaluation ON evaluation.CompanyID = soLine.CompanyID
  203. AND evaluation.RefNbr = soLine.UsrIncentiveID
  204. LEFT JOIN dbo.SOOrderShipment sos ON sos.CompanyID = soorder.CompanyID
  205. AND sos.OrderNbr = soorder.OrderNbr
  206. AND sos.OrderType = soorder.OrderType
  207. WHERE
  208. --ko in đơn trạng thái 'Cancel' và 'Hold'
  209. soorder.Status <> 'L'
  210. AND soorder.Status <> 'H'
  211. AND soLine.OrderType <> 'CM'
  212. --thao:them dk vansale or presale
  213.  
  214. AND soLine.OrderNbr IS NOT NULL
  215.  
  216. --ko in ra line hàng có qty==0
  217. AND soLine.OrderQty <> 0
  218. AND soLine.IsFree = 1
  219. AND ISNULL(soLine.UsrIncentiveID, '') <> ''
  220. AND soLine.SiteID IN ( SELECT SiteID
  221. FROM dbo.DMSViewINSiteWithSecurity
  222. )
  223. GROUP BY soLine.CompanyID ,
  224. soLine.BranchID ,
  225. soLine.OrderType ,
  226. soLine.OrderNbr ,
  227. soLineSplit.InventoryID ,
  228. soLineSplit.LotSerialNbr ,
  229. evaluation.RefNbr
  230. UNION ALL
  231. --#region -- lấy KM tiền
  232. SELECT soLine.CompanyID ,
  233. soLine.BranchID ,
  234. soLine.OrderType ,
  235. soLine.OrderNbr ,
  236. MAX(soorder.CustomerID) ,
  237. MAX(soorder.CustomerLocationID) ,
  238. MAX(soorder.UsrSalesReps) ,
  239. MAX(soorder.OrderDate) ,
  240. SOOrderType = soLine.OrderType ,
  241. SOOrderNbr = soLine.OrderNbr ,
  242. 0 AS InventoryID ,
  243. UOMSplit = '' ,
  244. UOMLine = '' ,
  245. 0.0 AS Qty ,
  246. 0.0 AS CuryUnitPrice ,
  247. '' AS LotSerialNbr ,
  248. '' AS ExpireDate ,
  249. SUM(sodd.OrderFreeAmt) ,
  250. 0 AS OrderFreeQty ,
  251. disinfo.PromotionCD ,
  252. MAX(disinfo.SchemeID) ,
  253. MAX(disinfo.DealID) ,
  254. MAX(disinfo.Descr) ,
  255. 'F' isPromotion ,
  256. OrderDescr = MAX(soorder.OrderDesc) ,
  257. MAX(sos.InvoiceNbr) ,
  258. N'line KM tiền' AS TypeLine
  259. FROM dbo.SOLine soLine
  260. INNER JOIN SOOrder soorder ON soorder.CompanyID = soLine.CompanyID
  261. AND soorder.OrderNbr = soLine.OrderNbr
  262. AND soorder.OrderType = soLine.OrderType
  263. AND soorder.BranchID = soLine.BranchID
  264. JOIN dbo.DMSPROSODiscountDetail sodd ON sodd.CompanyID = soLine.CompanyID
  265. AND sodd.OrderNbr = soLine.OrderNbr
  266. AND sodd.OrderType = soLine.OrderType
  267. AND sodd.InventoryID = soLine.InventoryID
  268. AND sodd.LineNbr = soLine.LineNbr
  269. AND ( sodd.Type = 'S' )
  270. AND sodd.OrderFreeAmt > 0
  271. AND sodd.BranchID = soLine.BranchID
  272. LEFT JOIN dbo.DMSPRODiscount disinfo ON disinfo.CompanyID = sodd.CompanyID
  273. AND disinfo.PromotionID = sodd.PromotionID
  274. LEFT JOIN dbo.SOOrderShipment sos ON sos.CompanyID = soorder.CompanyID
  275. AND sos.OrderNbr = soorder.OrderNbr
  276. AND sos.OrderType = soorder.OrderType
  277. WHERE
  278. --ko in đơn trạng thái 'Cancel' và 'Hold'
  279. soorder.Status <> 'L'
  280. AND soorder.Status <> 'H'
  281. AND soLine.OrderType <> 'CM'
  282. --thao:them dk vansale or presale
  283.  
  284. AND ( soLine.OrderNbr IS NOT NULL )
  285.  
  286. --ko in ra line hàng có qty==0
  287. AND soLine.OrderQty <> 0
  288. AND soLine.IsFree = 0
  289.  
  290. AND ISNULL(soLine.UsrIncentiveID, '') = ''
  291. AND soLine.SiteID IN ( SELECT SiteID
  292. FROM dbo.DMSViewINSiteWithSecurity
  293. )
  294. GROUP BY soLine.CompanyID ,
  295. soLine.BranchID ,
  296. soLine.OrderType ,
  297. soLine.OrderNbr ,
  298. disinfo.PromotionCD;
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!